Clear Filters
Clear Filters

How to merge new variables and append new rows at the same time (when synchronizing timetables)?

19 views (last 30 days)
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');

Accepted Answer

Stephen23
Stephen23 on 30 Mar 2023
R1 = array2timetable(randi([36 40],5,2),'RowTimes',dateshift(datetime('today'),'dayofweek','Friday',-5:-1),'VariableNames',["x1","x2"])
R1 = 5×2 timetable
Time x1 x2 ___________ __ __ 24-Feb-2023 36 40 03-Mar-2023 38 40 10-Mar-2023 38 40 17-Mar-2023 40 40 24-Mar-2023 40 39
R2 = array2timetable([41 42],'RowTimes',dateshift(datetime('today'),'dayofweek','Friday',0),'VariableNames',["x2","x3"])
R2 = 1×2 timetable
Time x2 x3 ___________ __ __ 31-Mar-2023 41 42
M = outerjoin(R1,R2, 'Keys',["Time","x2"], 'mergekeys',true)
M = 6×3 timetable
Time x1 x2 x3 ___________ ___ __ ___ 24-Feb-2023 36 40 NaN 03-Mar-2023 38 40 NaN 10-Mar-2023 38 40 NaN 17-Mar-2023 40 40 NaN 24-Mar-2023 40 39 NaN 31-Mar-2023 NaN 41 42
  2 Comments
Peter Perkins
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.

Sign in to comment.

More Answers (1)

Jack
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.
  1 Comment
Haris K.
Haris K. on 30 Mar 2023
Hi Jack, thank you for you answer. However, timetable R2 has a different number of rows to R1. Therefore, addvars(R1, R2.x3,'NewVariableNames','x3') returns an error.

Sign in to comment.

Categories

Find more on Timetables in Help Center and File Exchange

Products


Release

R2020b

Community Treasure Hunt

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

Start Hunting!