Appending 2 Tables with different column order and different number of columns

Hi,

I need to append tables with different column orders but have the same header for the respecitve columns. For example

A.Properties.VariableNames={'org01','org02','org03','org04',SQ001','SQ002',SQ003'};

B. Properties.VariableNames={'org01','org03','org02',SQ002','SQ003',SQ001'};

How can I append B columns to A columns for the columns that have the same header while assigning for the variables in A that don't have equivalent in B such as org04 NaN values.

Answer 1

[Cris LaPierre]

MATLAB can use variable names in a table to concatenate two tables even if the order is different. When all the variables are in all tables, use the normal vertical concatenation technique.

It's more challenging if all tables do not have all the variables. MATLAB will fill the unassigned variable(s) with the default value for the datatype. That is 0 for doubles, not NaN (see the warning message below)

% Create 2 tables
org01=(1:5)';
org02=org01;
SQ001=org01;
T1 = table(org01,org02,SQ001)

T1 = 5x3 table
    org01    org02    SQ001
    _____    _____    _____

      1        1        1  
      2        2        2  
      3        3        3  
      4        4        4  
      5        5        5  

% Table 2 is a subset of the variable of T1
T2 = flip(T1(:,["SQ001","org01"]))

T2 = 5x2 table
    SQ001    org01
    _____    _____

      5        5  
      4        4  
      3        3  
      2        2  
      1        1  

% Add T2 variable values to T1
T1(end+1:end+height(T2),T2.Properties.VariableNames)=T2

Warning: The assignment added rows to the table, but did not assign values to all of the table's existing variables. Those variables are extended with rows containing default values.

T1 = 10x3 table
    org01    org02    SQ001
    _____    _____    _____

      1        1        1  
      2        2        2  
      3        3        3  
      4        4        4  
      5        5        5  
      5        0        5  
      4        0        4  
      3        0        3  
      2        0        2  
      1        0        1  

If you really want them to be NaN, there are a couple options. If you want to make all 0s NaN, look into the standardizemissing function. If there will be zeros elsewhere you don't want to change to NaN, you can use a comparison of variable names along with some indexing to set just the extended variables to NaN. Here's an eample.

% Identify the new rows, and just the variables that were not in T2
T1{end-height(T2)+1:end,~ismember(T1.Properties.VariableNames,T2.Properties.VariableNames)}=NaN

T1 = 10x3 table
    org01    org02    SQ001
    _____    _____    _____

      1         1       1  
      2         2       2  
      3         3       3  
      4         4       4  
      5         5       5  
      5       NaN       5  
      4       NaN       4  
      3       NaN       3  
      2       NaN       2  
      1       NaN       1  

[Wesso]

I am afraid it is a bit more complicated since some cvariables have the same name but different data type. I am receiving the following error when running the command:

T1(end+1:end+height(T2),T2.Properties.VariableNames)=T2

Right hand side of an assignment to an categorical array must be a categorical or text representing a category

name

[Wesso]

Please find attached the data in case it might help

[Cris LaPierre]

I would think this has more to do with how you load the data into MATLAB. I would look into using the options input to specify what data type to use for your data. You can use the setvartype function for that.

[Wesso]

isn't there an option to transform variable types of T2 to become identical to those that match in T1.

I have many tables with too many columns. It will be very difficult to make the import option consistent

[Cris LaPierre]

I'm not aware of a way to do this all at once.

You might find the following aswers helpful.

• https://www.mathworks.com/matlabcentral/answers/153538-how-can-i-typecast-a-column-in-a-table-to-a-particular-data-type-in-matlab
• https://www.mathworks.com/matlabcentral/answers/347783-change-table-variable-data-type
• https://www.mathworks.com/matlabcentral/answers/429348-how-to-change-data-type-of-table-in-matlab

[Marcus Glover]

Just wanted to add this helped me out and add my last line to anyone looking to remove a row that does not appear in the new table

% Remove rows that were not in T2
T1(:,~ismember(T1.Properties.VariableNames,T2.Properties.VariableNames))=[]

T1 = 10x2 table
    org01    SQ001
    _____    _____

      1        1  
      2        2  
      3        3  
      4        4  
      5        5  
      5        5  
      4        4  
      3        3  
      2        2  
      1        1