Join two table variables into one. == does not works of table type

Hello everybody,

I have two table variables, and would like to join them.

After comparing 1st column to 7th column of df3 and df4 variables,

if the row from 1st to 7th column is exactly same, then I hope to add the test2 column of df4

into dfcombine variable.

Please give me some helps.... == does not works of table type.

load input03.mat

dfcombine = df3;

for i=1:height(df3)

ind = find(df3(i,1:7)==df4(:,1:7));

dfcombine(i,9) = df4(ind,8);

end

I hope to make the dfcombine variable as below picture.

Answer 1

[Karim]

I think one of the issues is the nan in the data, the result of nan == nan is something for philosophers :)

Anyhow, see below for a procedure to merge the tables as desired. The idea is to ignore the nan and empty values during the comparison.

load(websave('myFile', "https://www.mathworks.com/matlabcentral/answers/uploaded_files/1132800/input03.mat"))

% have a look at the content of df3
df3

df3 = 5×8 table
     OBJ_ID         EQ_NO        EQ_REVISION     EQ_USER     EQ_USER_ID    SUBJECT     T_GRP      test1
    _________    ____________    ___________    _________    __________    _______    ________    _____

    {'0F850'}    {'T070002F'}       {'-'}       {'uo yu'}       2254         NaN      {'R002'}    0.777
    {'0F850'}    {'T070002F'}       {'-'}       {'uo yu'}       2255         NaN      {'R002'}    1.222
    {'0F850'}    {'T070002F'}       {'-'}       {'Jo yu'}       2256         NaN      {'R002'}    1.022
    {'0F850'}    {'T070002F'}       {'-'}       {'Jo yu'}       2257         NaN      {'R002'}    1.122
    {'0F850'}    {'T070002F'}       {'-'}       {'uo yu'}       2258         NaN      {'R002'}    1.222

% have a look at the content of df4
df4

df4 = 3×8 table
     OBJ_ID         EQ_NO        EQ_REVISION     EQ_USER     EQ_USER_ID    SUBJECT     T_GRP      test2
    _________    ____________    ___________    _________    __________    _______    ________    _____

    {'0F850'}    {'T070002F'}       {'-'}       {'uo yu'}       2255         NaN      {'R002'}    0.877
    {'0F850'}    {'T070002F'}       {'-'}       {'uo yu'}       2254         NaN      {'R002'}    0.222
    {'0F850'}    {'T070002F'}       {'-'}       {'Jo yu'}       2257         NaN      {'R002'}    0.777

% use ismember function to check if the 
[df4_check, df3_idx] = ismember( df4(:,[ 1 2 3 4 5]) , df3(:,[ 1 2 3 4 5]) );
% create a copy of the table, add a variable 'test2' filled with nan
test2 = nan(size(df3,1),1);
dfcombine = addvars(df3, test2);


% fill in the test values from df3 
dfcombine(df3_idx,end) = df4(df4_check,end);

% have a look at the combined table
dfcombine

dfcombine = 5×9 table
     OBJ_ID         EQ_NO        EQ_REVISION     EQ_USER     EQ_USER_ID    SUBJECT     T_GRP      test1    test2
    _________    ____________    ___________    _________    __________    _______    ________    _____    _____

    {'0F850'}    {'T070002F'}       {'-'}       {'uo yu'}       2254         NaN      {'R002'}    0.777    0.222
    {'0F850'}    {'T070002F'}       {'-'}       {'uo yu'}       2255         NaN      {'R002'}    1.222    0.877
    {'0F850'}    {'T070002F'}       {'-'}       {'Jo yu'}       2256         NaN      {'R002'}    1.022      NaN
    {'0F850'}    {'T070002F'}       {'-'}       {'Jo yu'}       2257         NaN      {'R002'}    1.122    0.777
    {'0F850'}    {'T070002F'}       {'-'}       {'uo yu'}       2258         NaN      {'R002'}    1.222      NaN

[Smithy]

Wonderful... thank you very mcuh. I really really appreciate with it. It works perfectly.

Answer 2

[Walter Roberson]

You are doing () indexing on a table. The result would be a table row. You compare that to something else that is probably a table. But two tables can only compare if the variable names are the same... which we do not know to be true.

[Smithy]

Thank you very much for your comments. Yes, you are right. It is the table and I was only possible to compare the variable name.

Answer 3

[HYEONSEOK SEOK]

For comparing something with logical, table need to change to array

see as below:

clear all ; close all; clc;

load("input03.mat")

for i = 1:height(df3)

for j = 1:7

for k = 1:height(df4)

if sum(j == [5,6])

checker(k,j)=sum((df3{i,j}==df4{k,j}),1);

else

checker(k,j)=sum(strcmp(df3{i,j},df4{k,j}),1);

end

end

end

if sum(sum(checker,2) > 5) % if no nan value - change to 6

test2(i,1) = df4((sum(checker,2) > 5),8);

else

test2(i,1)= {NaN};

end

end

dfcombin = [df3,test2];

[Smithy]

Wow.. thank you very much for your huge help. It works really well. Really wonderful.