How to combine 2 tables with different variables into 1 table?

I would like to combine Table1 and Table2 into Table3 as shown below. Any advice on how it can be done?

I have used the below commands, but not successful.

Method 1

Table3 = join(Table1,Table2,'Keys','SysTime')

The above is not successful as the key variable for B must contain all values in the key variable for A.

Method 2

Table3 = [Table1 Table2]

The above is not successful due to the duplicate name 'SysTime'.

[Stephen23]

To anyone reading this in the future: this is very simple to do with just one command.

Doing this by converting the tables to numeric matrix, sorting, identifying the matching elements via ISMEMBER and then creating a new table... is indirect, complex, obfuscated, and inefficient. Much better is to read the MATLAB documentation:

https://www.mathworks.com/help/matlab/tables.html

and read about the functions listed in the section "Join and Set Operations".

[Jayden Yeo]

This is a great method, and it works too. Simpler and have learnt something new today. Thanks a lot for your help, Stephen.

Answer 1

[KSSV]

Convert both the tables into arrays using table2array. And then you need to use below:

T1 = [1 2 3 4 5; 10 20 30 40 50]' ;
T2 = [1 1.5 2 2.5 3 ; 2 4 5 6 7]' ;

SysTime = [T1(:,1) ; T2(:,1)] ;
EDF1 = T1(:,2) ; 
MDF2 = T2(:,2) ; 

[SysTime,idx] = sort(unique(SysTime)) ;
EDF = NaN(size(SysTime)) ; 
MDF = NaN(size(SysTime)) ; 

[c,ia] = ismember(T1(:,1),SysTime) ; 
EDF(ia) = EDF1 ; 

[c,ia] = ismember(T2(:,1),SysTime) ; 
MDF(ia) = MDF2 ;

T = table(SysTime,EDF,MDF)

T = 7×3 table
    SysTime    EDF    MDF
    _______    ___    ___

        1       10      2
      1.5      NaN      4
        2       20      5
      2.5      NaN      6
        3       30      7
        4       40    NaN
        5       50    NaN

[Jayden Yeo]

Thanks a lot. it works.

Answer 2

[Stephen23]

The actual MATLAB solution just takes one simple line of code:

SysTime = [1;2;3;4;5];
EDF = [10;20;30;40;50];
T1 = table(SysTime,EDF)

T1 = 5×2 table
    SysTime    EDF
    _______    ___

       1       10 
       2       20 
       3       30 
       4       40 
       5       50 

SysTime = [1;1.5;2;2.5;5];
MDF = [2;4;5;6;7];
T2 = table(SysTime,MDF)

T2 = 5×2 table
    SysTime    MDF
    _______    ___

        1       2 
      1.5       4 
        2       5 
      2.5       6 
        5       7 

T3 = outerjoin(T1,T2, 'MergeKeys',true) % this is all you need.

T3 = 7×3 table
    SysTime    EDF    MDF
    _______    ___    ___

        1       10      2
      1.5      NaN      4
        2       20      5
      2.5      NaN      6
        3       30    NaN
        4       40    NaN
        5       50      7

[Jayden Yeo]

Thanks a lot, Stephen. run the code, ad it works very well.

[Stephen23]

@Yeo Swee Huei: you can vote for my answer too, if it helped you.

[Jayden Yeo]

Hi Stephen. I just voted. Thanks.

Answer 3

[Sulaymon Eshkabilov]

Note that join() works very well, but you should correctly define the two table to be joined. See this example:

A = (1:5)'; B = A*10; C = A+B; D = C/2; Item = (1:5)';  
ABC = [Item, A, B]; CBD = [Item, C, D];
T1 = array2table(ABC, 'VariableNames', {'Item', 'A', 'B'}); 
T2 = array2table(CBD, 'VariableNames', {'Item', 'C', 'D'});
TT = join(T1, T2)

TT = 5×5 table
    Item    A    B     C      D  
    ____    _    __    __    ____

     1      1    10    11     5.5
     2      2    20    22      11
     3      3    30    33    16.5
     4      4    40    44      22
     5      5    50    55    27.5

[Jayden Yeo]

Thanks a lot.