Combine data from 3 tables based on common datetime?

1 view (last 30 days)
Michiel Smit
Michiel Smit on 10 Nov 2021
Answered: Steven Lord on 11 Nov 2021
Dear reader,
I am currently working with three tables of measurement at different location. However, the time intervals of the measurements differ. For a proper analysis of the data, I only want to look at the dates and times at which all 3 measurements show a measurement. That is, the rows made bold in the example data below
The data from the different measurements is stored as follows (an example but not the entire table)
time_ERA5 Var2
____________________ _______
01-Jan-1990 00:00:00 0.39602
01-Jan-1990 01:00:00 0.39663
01-Jan-1990 02:00:00 0.3954
01-Jan-1990 03:00:00 0.39438
01-Jan-1990 04:00:00 0.3952
01-Jan-1990 05:00:00 0.3954
01-Jan-1990 06:00:00 0.3954
01-Jan-1990 07:00:00 0.39643
01-Jan-1990 08:00:00 0.40318
01-Jan-1990 09:00:00 0.41667
01-Jan-1990 10:00:00 0.40788
time_IFREMER Var2
____________________ _____
01-Jan-1990 00:00:00 0.448
01-Jan-1990 03:00:00 0.426
01-Jan-1990 06:00:00 0.392
01-Jan-1990 09:00:00 0.374
How can I obtain a table that only keeps the data for the datetime values that both tables have in common. The measured data (Var2 in both tables) is preferably stored in 2 seperate columns
Many thanks in advance!

Answers (3)

Kevin Holly
Kevin Holly on 10 Nov 2021

Sulaymon Eshkabilov
Sulaymon Eshkabilov on 11 Nov 2021
Here innerjoin does not work. It is better to employ a logical indexing approach, e.g.:
% D1 is a first table array variable and D2 is a second one
V1 = D1.Var2(D1.time_ERA5==D2.time_ERA5); % Gives only matching data points for Var2 in D1
V2 = D2.Var3(D1.time_ERA5==D2.time_ERA5); % Gives only matching data points for Var2 in D2
% Makes up a new table variables
DD1 = D1(D1.Var1==D2.Var1,:);
DD2 = D2(D1.Var1==D2.Var1,:);
% if you want to put the selected data into DD1 or DD2, then
DD1.Var3 =[];
DD1.VarSel_1 = V1;
DD1.VarSel_2 = V2;
DD2.Var3 =[];
DD2.VarSel_1 = V1;
DD2.VarSel_2 = V2;

Steven Lord
Steven Lord on 11 Nov 2021
Are you storing your data as table arrays or as timetable arrays? If the former, consider converting the table arrays to timetable arrays using table2timetable. Once you have your data stored as timetable arrays, you could synchronize those timetable arrays.

Community Treasure Hunt

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

Start Hunting!