Vertically Concatenating Timetables of Different Sizes
Mostra commenti meno recenti
As the title states, I wish to do just that. More specifically, I need to combine n timetables, each with one row, but varying column counts (Dimension counts). There is a nonzero intersection between each timetable, and so there are data being shared between each. However, some may also have data that other’s do not.
The goal here is to vertically concatenate each timetable so that all the data within the intersection are aligned by column/dimension name type. If there are data that are missing and so one timetable is smaller than the other, then that dimension/data type will be populated as a 'NaN' within the smaller timetable. In the example I give below, I say there are three timetables of lengths 1x40, 1x36 and 1x30. I wish to get a resulting timetable that is then 3x40.
Method 1:
Obviously doesn’t work as the # of columns are different.
Method 2:
I tried using a solution on another post that does exactly what I want, but with tables instead of timetables. This did not give the correct result as the output is a 3x215, which I don’t understand why. It also combines the dimension labels with “All” which doesn’t make sense to me as I specify “MergeKeys” as “True” which should leave the dimension labels alone according to the documentation. It comes close though in that it does concatenate the datetimes correctly and populates empty data/units with 'NaN's. I tried specifying the “Key” variables to align to, but because some timetables do not contain these variables and the Matlab function outerjoin wasn’t built to create missing variable columns that auto populate with NaNs when it encounters this, this does not work.
Method 3:
I tried using the synchronize function as this is specifically geared towards timetables. I get the exact same resulting timetable as Method 2. The synchronize documentation and "Combine Timetables and Synchronize Their Data" documentation give examples that do the exact same thing (Just that I have more columns and different dimension names) as my example from what I can see, so I am even more confused by why this does not produce the same result that I would like.
So I think I am just not employing Methods 2,3 correctly or I may have to create another for-loop prior to concatenation that creates the new variables in the smaller timetables and populates them with NaNs using indexing of the largest timetable.
%Create 3 timetables of different sizes and random dimension names with nonzero intersections
varTypes = {'string'};
doub = 'double';
str = 'string';
szList = [40,36,30];
ind = {'A','B','C'};
TimeDim = [datetime('2023-01-01 20:14:58'),datetime('2023-02-21 22:13:04'),datetime('2023-03-11 10:12:58')];
for i = 1:length(szList)
for j = 1:szList(i)
varTypes{end+1} = doub;
varTypes{end+1} = str;
end
ArrayList.ind{i} = varTypes;
varTypes = {'string'};
end
clear i j
for i = 1:length(szList)
ttSaved.ind{i} = timetable('Size',[1 2*szList(i)+1],'VariableTypes',ArrayList.ind{1,i},'RowTimes',TimeDim(i));
ttSaved.ind{i} = renamevars(ttSaved.ind{i},'Var1','Exp_ID');
ttSaved.ind{i}.Exp_ID = i;
end
clear i
%Label each dimenion and populate tables with data
for i = 1:length(ttSaved.ind)
choice = randperm(szList(i));
for j = 1:szList(i)
ttSaved.ind{i} = renamevars(ttSaved.ind{i},sprintf('Var%d',2*j),sprintf('Data_%d',choice(j)));
ttSaved.ind{i} = renamevars(ttSaved.ind{i},sprintf('Var%d',2*j+1),sprintf('Units_%d',choice(j)));
end
Names = ttSaved.ind{i}.Properties.VariableNames;
for k = 1:szList(i)
ttSaved.ind{i}.(Names{2*k}) = rand*100; %Add data
ttSaved.ind{i}.(Names{2*k+1}) = char(choice(k)); %Add Units
end
end
clear i j k
%Method 1
ttAll = [];
for i = 1:length(ttSaved.ind)
ttAll = [ttAll ; ttSaved.ind{i}];
end
clear i
%Method 2
ttAll = ttSaved.ind{1};
AllMeas = ttSaved.ind{1}.Properties.VariableNames;
for i = 2:length(ttSaved.ind)
ttAll = outerjoin(ttAll,ttSaved.ind{i},'Keys',AllMeas,'MergeKeys', true);
end
clear i
%Method 3
ttAll2 = ttSaved.ind{1};
for i = 2:length(ttSaved.ind)
ttAll2 = synchronize(ttAll2,ttSaved.ind{i});
end
clear i
Note: This post is somewhat of a continuation of a previous post, but since I marked the last as answered and this one is different enough and contains the same data structures to my actual data, I needed create a new question.
3 Commenti
Jan Kappen
il 16 Gen 2025
Spostato: Stephen23
il 17 Gen 2025
@Jan Kappen: see the user's question (Method 3) as well as my comment here:
SYNCHRONIZE renames variables/columns that occur in more than one input timetable, which means that it does not provide the output required by the OP. We can demonstrate this quite easily using the demo data from my comment:
T = array2timetable(rand(3,7), 'RowTimes',datetime(2023,1:3,1));
C = {...
T(1,[1,3,5,7]),...
T(2,[2,4,5,6,7]),...
T(3,[1,2,3])};
C{:}
T = synchronize(C{:})
As you can see, all duplicated variable/column names have been renamed, which is not what the OP requested or wanted. SYNCHRONIZE currently does not have an option to disable this renaming (it has no concept of keys or similar). It should be possible to merge those duplicated variables with postprocessing e.g. in a FOR loop and some PATTERN-based name matching and iteratively creating new table columns/variables (but ultimately this effort just replicates the functionality of OUTERJOIN, which my answer already uses).
Jan Kappen
il 17 Gen 2025
I see, didn't catch that requirement and for some reasons also not that synchronize was already mentioned in the first post - apologies.
Risposta accettata
"The goal here is to vertically concatenate each timetable so that all the data within the intersection are aligned by column/dimension name type."
First lets create some fake data, here are three tables in a cell array:
T = array2table(rand(3,7));
C = {...
T(1,[1,3,5,7]),...
T(2,[2,4,5,6,7]),...
T(3,[1,2,3])};
C{:}
Now lets concatenate them together using OUTERJOIN. The trick is to ensure that you provide a key (or keys) that uniquely identifies each row (this could probably be a time, row name, etc). Note how the THISROW variable ensures at least one common key, which is required for joining tables:
T = C{1};
T.ThisRow = 1; % uniquely identify 1st row.
T = T(:,[end,1:end-1]); % ensure THISROW is the 1st column, to ensure the correct row order.
for i = 2:numel(C)
V = C{i};
V.ThisRow = i; % uniquely identify ith row.
T = outerjoin(T,V, 'MergeKeys',true);
end
display(T)
OPTIONAL, if you want a particular variable order:
[~,X] = sort(T.Properties.VariableNames);
T = T(:,X)
Use REMOVEVARS if you want to get rid of THISROW.
4 Commenti
We can use the same approach using timetables, if the date/time values uniquely identify each row:
T = array2timetable(rand(3,7), 'RowTimes',datetime(2023,1:3,1));
C = {...
T(1,[1,3,5,7]),...
T(2,[2,4,5,6,7]),...
T(3,[1,2,3])};
C{:}
T = C{1};
for i = 2:numel(C)
V = C{i};
K = intersect(T.Properties.VariableNames,V.Properties.VariableNames);
K{end+1} = 'Time';
T = outerjoin(T,V, 'Keys',K, 'MergeKeys',true);
end
display(T)
Optional, again just for sorting the rows/columns:
[~,X] = sort(T.Properties.VariableNames);
T = sortrows(T(:,X),'Time')
The main difference is actually that I uniquely specified each row. I realized later that using INTERSECT is superfluous (for tables), because by default OUTERJOIN uses all common variable names as keys. So we can simplify the code (see modified answer).
Not sure if that also works for timetables, because of the need(?) to specify the TIME.
"so why did the outerjoin function modify the variable names after the timetables were joined, while in this case, they were unmodified?"
Because OUTERJOIN keeps all variables/columns which are not keys, and makes no attempt to merge them. By specifying keys as being only the variables/column from the first table, you overrode the default behavior of using all common names as keys (or the equivalent using INTERSECT). Because you explicitly told it NOT to use any new variables/columns as keys OUTERJOIN helpfully renamed them and kept them all for you...
Based on your proposals I've created a function that also works with timetables (seems like in timetables the RowTimes are always used as keys?):
%% example data
T1 = timetable(seconds([0 1 2]'), randn(3,2), randn(3,3), VariableNames=["DistinctVar1", "VarToBeMerged"])
T2 = timetable(seconds([2 3 4]'), randn(3,2), randn(3,3), VariableNames=["DistinctVar2", "VarToBeMerged"])
%% merge
Tcat = table_vertcat(T1, T2)
%% helper
function Tcat = table_vertcat(T)
arguments (Repeating)
T tabular
end
mustContainSameClass(T) % can't be used as validation function due to "Repeating" argument
if isscalar(T)
Tcat = T{1};
return
end
% todo: check for nested tables, empty tables etc
if istimetable(T{1}) % due to mustContainSameClass all inputs are either tables or timetables
handleTimetables = true;
rowTimeVarNames = cellfun(@(T)string(T.Properties.DimensionNames{1}), T);
assert(all(rowTimeVarNames(1) == rowTimeVarNames), "All RowTime variable names must be identical")
T = cellfun(@timetable2table, T, UniformOutput=false);
else
handleTimetables = false;
end
Tcat = T{1};
Tcat.UniqueIndexForAllVertcatTables = (1:height(Tcat))';
for k = 2:length(T)
T{k}.UniqueIndexForAllVertcatTables = (1:height(T{k}))' + Tcat.UniqueIndexForAllVertcatTables(end);
% does not work nested tables, need to manually split/un-table them
Tcat = outerjoin(Tcat, T{k}, MergeKeys=true);
end
Tcat.UniqueIndexForAllVertcatTables = [];
if handleTimetables
Tcat = table2timetable(Tcat, RowTimes=rowTimeVarNames(1));
end
end
function mustContainSameClass(in)
arguments
in cell
end
assert(all(class(in{1}) == cellfun(@(in)string(class(in)), in)), "All inputs must be of same class")
end
Più risposte (0)
Categorie
Scopri di più su Creating and Concatenating Matrices in Centro assistenza e File Exchange
Community Treasure Hunt
Find the treasures in MATLAB Central and discover how the community can help you!
Start Hunting!
Translated by 
