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

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');

Answer 1

[Stephen23]

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

[Haris K.]

Thanks!

[Peter Perkins]

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.

Answer 2

[Jack]

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.

[Haris K.]

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.