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

2 views (last 30 days)
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 Comments
B
B on 18 Aug 2021
Edited: B on 18 Aug 2021
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');

Sign in to comment.

Accepted Answer

B
B on 18 Aug 2021
Edited: B on 18 Aug 2021
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');

More Answers (1)

Eric Sofen
Eric Sofen on 19 Aug 2021
Edited: Eric Sofen on 19 Aug 2021
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 Comments
Peter Perkins
Peter Perkins on 1 Sep 2021
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 on 1 Sep 2021
Edited: B on 1 Sep 2021
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.

Sign in to comment.

Categories

Find more on MATLAB in Help Center and File Exchange

Products


Release

R2020a

Community Treasure Hunt

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

Start Hunting!