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

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')

[Yazan]

TT does not have a variable called DateTime_1. Check again the code and data you provided.

[B]

Did you check the results for the TT at the workspace? DateTime_1 is the assigned heading by Matlab instead DateTIme as in the data (I believe because headings change). You haven't helped fix the problem you are just echoing my sentiment.

[Eric Sofen]

It looks like VV.Date and VV.Date_1 are serial date numbers (datenum). isbetween is a function that works with datetime. datetime is a newer type for working with dates and times and has a number of benefits over datenum (time zones, locales, better text parsing), and I'd encourage you to use them.

To convert to datetime, you can do

VV.Date = datetime(VV.Date,'convertFrom','datenum')

Then you can use isbetween. Similarly, you could convert VV or TT to a timetable and then use the timerange subscriptor to return a subset of a table based on the range of times you specify.

To combine two tables using this non-exact-match time comparison, I think you're on the right track to find idx using isbetween and add index to each of the tables.

[B]

Thank you, Eric, I'll try your suggestion and revert. Much appreciated!

[B]

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');

Answer 1

[B]

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');

Answer 2

[Eric Sofen]

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')

[B]

Thank you, Eric and this works from the quetsion.

But what I really want is best done with outerjoin- in whcih case, I am able to match elements in table 2 with elements in table 1.

sychronize merges the entire data but outerjoin selects and matches the dataset to the elements in a specified table.

[Peter Perkins]

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]

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.