How to merge new variables and append new rows at the same time (when synchronizing timetables)?
19 views (last 30 days)
Show older comments
Hi. Below, I have 2 timetables (R1, R2) that I am trying to combine into a single timetable (M).
What I am trying to achieve is to merge the new variables (i.e. column x3) and append the new row (for x2), at the same time. I tried both synchronize() and outerjoin() but I do not get the desired output. I need to have a single 'x2' column. Any help would be appreciated. Thanks.
R1 = array2timetable(randi([36 40],5,2),'RowTimes',dateshift(datetime('today'),'dayofweek','Friday',-5:-1),'VariableNames',["x1","x2"]);
R2 = array2timetable([41 42],'RowTimes',dateshift(datetime('today'),'dayofweek','Friday',0),'VariableNames',["x2","x3"]);
%The following do not give the desired output as they do not append x2:
M = synchronize(R1,R2, 'union','fillwithmissing');
M = outerjoin(R1,R2,'mergekeys',true,'Keys','Time');
0 Comments
Accepted Answer
Stephen23
on 30 Mar 2023
R1 = array2timetable(randi([36 40],5,2),'RowTimes',dateshift(datetime('today'),'dayofweek','Friday',-5:-1),'VariableNames',["x1","x2"])
R2 = array2timetable([41 42],'RowTimes',dateshift(datetime('today'),'dayofweek','Friday',0),'VariableNames',["x2","x3"])
M = outerjoin(R1,R2, 'Keys',["Time","x2"], 'mergekeys',true)
2 Comments
Peter Perkins
on 5 Apr 2023
As Stephen23 shows, outerjoin can do many of the things that horzcat and vertcat do, or even both at the same time. But the trick works because the x2 values are different. In general, not true, but happy that it worked here.
Really, you are looking for a way to vertcat two timetables with some variables in common, but also some not in common. Currently, the way to do that in the simplest cases would be
>> R1.x3(:) = NaN;
>> R2.x1(:) = NaN;
>> [R1; R2]
ans =
6×3 timetable
Time x1 x2 x3
___________ ___ __ ___
03-Mar-2023 39 36 NaN
10-Mar-2023 36 39 NaN
17-Mar-2023 37 36 NaN
24-Mar-2023 37 39 NaN
31-Mar-2023 39 38 NaN
07-Apr-2023 NaN 41 42
but this assumes the row times are distinct.
More Answers (1)
Jack
on 30 Mar 2023
To achieve the desired output, you can use the addvars function to add the variable x3 from R2 to R1, and then add a new row with the values of x2 and x3 using the addrows function. Here is an example code:
% Add x3 variable to R1
R1 = addvars(R1, R2.x3, 'NewVariableNames', 'x3');
% Add new row with x2 and x3 values
newRow = array2timetable([R2.x2, R2.x3], 'RowTimes', R2.Properties.RowTimes, 'VariableNames', ["x2", "x3"]);
M = addrows(R1, newRow);
This code first adds the x3 variable from R2 to R1 using addvars. Then it creates a new timetable newRow with the values of x2 and x3 from R2 and with the same row time as R2. Finally, it adds the new row to R1 using addrows, and assigns the result to M. The resulting timetable M should have the desired output.
See Also
Categories
Find more on Timetables in Help Center and File Exchange
Community Treasure Hunt
Find the treasures in MATLAB Central and discover how the community can help you!
Start Hunting!