Summarize two tables of different data and dates into exact matches of one of the two datasets

1 次查看（过去 30 天）
B
B 2021-8-17
编辑： B 2021-9-1
So I have two tables; one a chemistry data table with dates and the other a discharge data with dates.
The discharge data is mostly daily while chemistry is taken mostly once a week or every two to three weeks.
I want to select the discharge data that matches the chemistry dates but I am stuck.
I used isbetween
intervalStartTime = '09-Aug-2019 15:50:00';
intervalEndTime = '09-Aug-2019 16:00:00';
idx = isbetween(data.time,intervalStartTime,intervalEndTime);
selectedRows = data(idx,:);
and outerjoin, but not getting the output I want.
Here is my code:
clear ;close all;
VV=readtable('ERM', 'PreserveVariableNames',true);
T_mu10left=VV(298:440,1:3);%Chem data +date sum for MU10
TT=VV(:,4:5);%discharge data +date for discharge station MU10
%sort discharge data to match chemistry dates
intervalStartTime = '01-May-2015 ';
intervalEndTime = '31-Dec-2020 ';
idx = isbetween(TT.DateTime_1,intervalStartTime,intervalEndTime);
%sR = TT(idx,:)%Stores only the rows with the desired time instances
T_mu10right=idx
%combine chemistry and discharge in one Table with only matching data and dates
% T_mu10 = outerjoin(T_mu10left,T_mu10right,'Type','left')
  5 个评论
显示 3更早的评论
B
B 2021-8-18
编辑：B 2021-8-18
I actually don't need that extract lines with isbetween and the date conversions...this works well. I resolved it beautifully here
clear ;close all;
%Separate data in ERM into two separet tables to keep "Date" as Key 1 for both data and note position of data you required and remove the first column.
VV=readtable('ERM.csv');%chemistry read
V1=readtable('ERM1.csv')% Discharge read
T=VV(298:440,1:2); % separate chemistry data and date you need (note the table contains several stations)
TT=V1(:,1:2);%discharge data +date for discharge station MU10
%select data based only on the dates in chemistry (T)
T_10 = outerjoin(T,TT,'Type','left');

请先登录，再进行评论。

请先登录，再回答此问题。

采纳的回答

B
B 2021-8-18
编辑：B 2021-8-18
clear ;close all;
%Separate data in ERM into two separet tables to keep "Date" as Key 1 for both data and note position of data you required.
VV=readtable('ERM.csv');%chemistry read
V1=readtable('ERM1.csv')% Discharge read
T_left=VV(298:440,1:2); % separate chemistry data and date you need (note the table contains several stations)
TT_right=V1(:,1:2);%discharge data +date for discharge station MU10
%select data based only on the dates in chemistry (T)
T_10 = outerjoin(T_left,TT_right,'Type','left');

更多回答（1 个）

Eric Sofen
Eric Sofen 2021-8-19
编辑：Eric Sofen 2021-8-19
Another way to solve this is to synchronize the lower-resolution chemistry timetable to the higher-resolution discharge data. My comment in this post illustrates how it works in a slightly more complicated, but similar, example.
% first, you'd need to convert the data in your
% code snippet to timetables using table2timetable
T_10 = synchronize(V1, VV, 'first','previous')
  3 个评论
显示 1更早的评论
Peter Perkins
Peter Perkins 2021-9-1
Kity, I don't have access to your data, so it's hard to say for sure what will work for you, but just for the record:
outerjoin is only going to work when there are exact matches between the dates in the chemistry and discharge data. That may indeed be the case if everything is pure dates (although "mostly daily" would seem to be problematic). But more generally, you may want to "match" the chemistry data with the nearest discharge data. Eric's suggestion of using synchronize is the way to do that, using "previous". In other words, synchronize can sometimes be used as a form of join where you need to match time intervals (lower freq data) to time points (higher freq data).
B
B 2021-9-1
编辑：B 2021-9-1
Peter, What I want is the exact matches between the dates in the chemistry and discharge data. I know with synchronize, I will have the opportunity to interpolate as well but tha's is not my need now. I am modifying the question to suit what I want
Thanks.

请先登录，再进行评论。

类别

在 Help Center 和 File Exchange 中查找有关 Electromagnetism 的更多信息

标签

产品


版本

R2020a

Community Treasure Hunt

Find the treasures in MATLAB Central and discover how the community can help you!

Start Hunting!

Translated by