Joining tables with different number of coloums

Hello!

i have a problem joining tables with the "join(Tleft,Tright, 'Keys', 'keyvar')" command.

merge_T1_T2 = join(T1, T2, 'Keys','SER_AKTUATOR');

Left and right key variables 'SER_AKTUATOR' and 'SER_AKTUATOR' do not have the same number of columns.

From my understanding there is no need for the number of coloums to be the same (the ML examples don't have this aswell).

I have already performed a sequence to allign the data types of the Keyvar (SER_AKTUATOR) and they are both 'Char'

Does anyone know how what the problem is/ ho to fix it?

Cheers Hannes

[Johannes Dechow]

If i use double (original datatype of SER_AKTUATOR) i get a different Error:

The key variable for the right table must have unique values.

[Arif Hoq]

attch your data and share the code that you have already tried and found the error

Answer 1

[Stephen23]

"From my understanding there is no need for the number of coloums to be the same"

If the number of columns in the key variables are not the same then there a no matches and the whole exercise is moot.

"the ML examples don't have this aswell"

Yes they do: every single example on this page: https://www.mathworks.com/help/matlab/ref/table.join.html

uses key variables with exactly one column. The only variable on the entire page that consists of multiple columns is "BloodPressure", which is not used as a key variable.

As far as I can tell, you are confusing the number of columns in the tables (which is not important) with the number of columns in the key variable (which is what your error message is about).

Your error is easy to demonstrate:

X = ['A';'B';'C']; % one column
A = [111;123;456];
T1 = table(X,A)

T1 = 3×2 table
    X     A 
    _    ___

    A    111
    B    123
    C    456

X = ['C';'B']; % one column
B = [222;444];
T2 = table(X,B)

T2 = 2×2 table
    X     B 
    _    ___

    C    222
    B    444

X = ['AA';'BB';'CC']; % two columns
C = [333;666;999];
T3 = table(X,C)

T3 = 3×2 table
    X      C 
    __    ___

    AA    333
    BB    666
    CC    999

join(T2,T1,'keys','X') % no problem: A and B have the same number of columns

ans = 2×3 table
    X     B      A 
    _    ___    ___

    C    222    456
    B    444    123

join(T3,T1,'keys','X') % error because B and C have different number of columns

Error using tabular/join
Left and right key variables 'X' and 'X' do not have the same number of columns.

"I have already performed a sequence to allign the data types of the Keyvar (SER_AKTUATOR) and they are both 'Char'"

And that is the cause of the problem.

"Does anyone know how what the problem is/ ho to fix it?"

Easy, simply replace that variable (a character array) with either of these:

• a cell array of character vectors
• a string array

with one column.

[Stephen23]

For my own curiosity, does JOIN work using key variables consisting of multiple columns? Lets try:

X = [11,12;21,22;31,32]; % two columns
A = ["test";"world";"hello"];
T1 = table(X,A)

T1 = 3×2 table
       X           A   
    ________    _______

    11    12    "test" 
    21    22    "world"
    31    32    "hello"

X = [31,32;21,22]; % two columns
B = [23;64];
T2 = table(X,B)

T2 = 2×2 table
       X        B 
    ________    __

    31    32    23
    21    22    64

join(T2,T1,'keys','X') % not problem!

ans = 2×3 table
       X        B        A   
    ________    __    _______

    31    32    23    "hello"
    21    22    64    "world"

So clearly the number of columns in the key variables must be the same. Matching makes no sense otherwise.

[Johannes Dechow]

Ah thank you, that helps a lot! I will try! I had a mussunderstanding in what coloums where ment!

I was curious what the size(T1.SER_AKTUATOR) and size(T2.SER_AKTUATOR) would be and there seems to be a space ' ' before or after some Data in some of my datapoints, since one has 6 coloums and one has 7 (even tho both should have 6). Will need to filter those out then.

I tried a workarround, since the Values of SER_AKTUATOR could also be represented in 'double' that worked for innerjoin() only. When i try join() I get the following Error:

"The key variable for the right table must have unique values."

I don't really understand what is meant by that. Since my understanding is that the whole point of joining tables is that they are common and not unique.

Thank you for your help so far!

[Stephen23]

"Since my understanding is that the whole point of joining tables is that they are common and not unique."

The key variable values must be unique in the right-table: "Each value must occur only once in the key variables of Tright", source: https://www.mathworks.com/help/matlab/ref/table.join.html

Writing "common" is insufficient to actually describe the behavior of JOIN. The definition of JOIN is actually "A table join appends rows from the right table where its key variables match values in the key variables of the left table".

It is not clear how joining would be defined if the values repeated in the right-table, because ultimately JOIN picks the the output rows depending on the value matching: if there are mulitple matches (as you propose), how would JOIN know which rows to pick from the right-table?

[Johannes Dechow]

Ah ok, thanks alot got it! you really helped me!