MATLAB Answers

Join several timetables with different variables and times and duplicates

34 views (last 30 days)
bk
bk on 25 Feb 2020
Commented: Guillaume on 26 Feb 2020
I'm trying to read a bunch of csv files that each have one variable timeseries. Many files can hold the same variable and there are overlaps in the timestamps (duplicates).
My problem is when trying to merge these csv files, which are read to timetables, into a single timetable.
Example code:
times_TT_A = [datetime(2019,01,01) datetime(2019,01,02) datetime(2019,01,03)]';
data_TT_A = [1 2 3]';
TT_A = timetable(times_TT_A, data_TT_A,'VariableNames',{'Variable_ONE'});
times_TT_B = [datetime(2019,01,01) datetime(2019,01,02)]';
data_TT_B = [10 20]';
TT_B = timetable(times_TT_B, data_TT_B,'VariableNames',{'Variable_TWO'});
times_TT_C = [datetime(2019,01,02) datetime(2019,01,03)]';
data_TT_C = [20 30]';
TT_C = timetable(times_TT_C, data_TT_C,'VariableNames',{'Variable_TWO'});
So here there are two different variables and one variable, Variable_TWO, has overlap in timestamps.
I would like to have this result:
times_TT_A Variable_ONE Variable_TWO
__________ ____________ _________________
2019-01-01 1 10
2019-01-02 2 20
2019-01-03 3 30
I don't mind a solution with duplicate entries, I can sort those out later, for example this is also fine:
times_TT_A Variable_ONE Variable_TWO
__________ ____________ _________________
2019-01-01 1 10
2019-01-02 2 20
2019-01-03 3 NaN
2019-01-02 NaN 20
2019-01-03 NaN 30
The csv files are read in a loop and should then one by one be added to the common timetable like this in pseudocode
timetable_alldata = timetable()
for csvfile in csvfiles
timetable_csv = readtable(csvfile)
timetable_alldata = join(timetable_alldata, timetable_csv)
end
This is then the same as calling this using the example data - here using outerjoin:
outerjoin(outerjoin(TT_A,TT_B), TT_C)
ans =
3×3 timetable
times_TT_A Variable_ONE Variable_TWO_left Variable_TWO_TT_C
__________ ____________ _________________ _________________
2019-01-01 1 10 NaN
2019-01-02 2 20 20
2019-01-03 3 NaN 30
I have tried stacking ([A; B]), vertcat, outerjoin and synchronize...but I seem to be stuck here.
Any suggestions how to solve this?
  3 Comments
bk
bk on 25 Feb 2020
Yes, the variable names need to match - they are not random.
And I only want to keep one row. The duplicated rows are always "true" duplicates with same timestamp and value. Sorting duplicates I guess will be relatively easy, so that can be done afterwards if the merging results in duplicates.

Sign in to comment.

Accepted Answer

Lei Hou
Lei Hou on 26 Feb 2020
Hi,
I came up with two solutions.
Solution 1: use outerjoint. It is easy to understand but have duplicated rows that need to be merged.
% Make the header of row times of input timetables to be the same
TT_A.Properties.DimensionNames{1} = 'Time';
TT_B.Properties.DimensionNames{1} = 'Time';
TT_C.Properties.DimensionNames{1} = 'Time';
% call outerjoin and merge keys
TT_AB = outerjoin(TT_A,TT_B, ...
'Keys',['Time' intersect(TT_A.Properties.VariableNames,TT_B.Properties.VariableNames)], ...
'MergeKeys',true)
TT_ABC = outerjoin(TT_AB,TT_C,...
'Keys',['Time' intersect(TT_AB.Properties.VariableNames,TT_C.Properties.VariableNames)], ...
'MergeKeys',true)
TT_ABC =
4x2 timetable
Time Variable_ONE Variable_TWO
____________________ ____________ ____________
01-Jan-2019 00:00:00 1 10
02-Jan-2019 00:00:00 2 20
03-Jan-2019 00:00:00 NaN 30
03-Jan-2019 00:00:00 3 NaN
Solution 2: Make use of assignment. This makes the strong assumption that the duplicate rows are really duplicates. So no need to post-process the result.
TTcell = {TT_A TT_B TT_C};
TT = timetable();
for i = 1:length(TTcell)
TT_i = TTcell{i};
TT(TT_i.Time,TT_i.Properties.VariableNames) = TT_i;
end
TT =
3x2 timetable
Time Variable_ONE Variable_TWO
___________ ____________ ____________
01-Jan-2019 1 10
02-Jan-2019 2 20
03-Jan-2019 3 30
  2 Comments
Guillaume
Guillaume on 26 Feb 2020
Any idea how to set default fill value to NaN, without prepopulating the table?
It's not possible. The default fill value is the default fill value for the datatype, which for numeric types is 0. You get the same behaviour with plain arrays:
x = [];
x(5) = 8; %x(1:4) gets filled with 0

Sign in to comment.

More Answers (1)

Guillaume
Guillaume on 26 Feb 2020
With your example tables, this is trivially achieved with:
synchronize(TT_A, [TT_B; TT_C])
  3 Comments
Guillaume
Guillaume on 26 Feb 2020
I must admit I didn't read the whole question. Yes, this won't work for what you want. If Lei's solution doesn't work, then you can always do it the 'old fashioned way' (before timetables and tables existed) with intersect and co. A bit more work but still fairly simple. I can work out the code if it's needed.

Sign in to comment.

Products


Release

R2019b

Community Treasure Hunt

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

Start Hunting!

Translated by