MATLAB Answers

Comparing two tables and copy elements of table 2 into table 1

44 views (last 30 days)
Haritha
Haritha on 14 Mar 2019
Edited: Guillaume on 20 Mar 2019
Hi,I have two tables as given below.
Table 1:
Table 2
If column1 in table 2 is equal to column 1 in table1 T & W values in table 2 has to copy in table 1, Similar strings has to follow one after the other. If the stings are unequal T & W must be empty. I want the output as below table;
image.PNG

  2 Comments

dpb
dpb on 14 Mar 2019
Your new table now has three 'hi' records instead of original two but only two 'hello'.
What's the difference and what's the logic behind such that could be written?

Sign in to comment.

Accepted Answer

Guillaume
Guillaume on 14 Mar 2019
%create demo tables
ID = strsplit('hello new world hi hello hi')';
T = cell(6, 1); W = T;
V = [1; 2; 3; 5; 8; 7];
T1 = table(ID, T, W, V)
T2 = table(ID(4:5), {2;4}, {6;7}, [1;2], 'VariableNames', {'ID', 'T', 'W', 'V'})
%merge the two tables
[found, where] = ismember(T1.ID, T2.ID);
T1(found, {'T', 'W'}) = T2(where(found), {'T', 'W'})

  2 Comments

Guillaume
Guillaume on 20 Mar 2019
My answer applies to matlab tables (which are not really the same thing as SQL tables). Your question never mentioned anything about a database so it's no wonder that it's not applicable.
In any case, you haven't told us how you're importing the data in matlab (or as what, you probably could import it as matlab table, in which case, my answer still apply).
If you are asking how to do this in SQL, then you are in the wrong forum. Go ask SQL gurus. It's been a while since I've written SQL query but I've got the feeling that it wouldn't be very hard to do what you want with a fairly simple SELECT query (maybe with an UNION).
Haritha
Haritha on 20 Mar 2019
Its working for normal tables. I forget to mention the SQL. I accepted my mistake

Sign in to comment.

More Answers (2)

Pruthvi Raj Gampalwar
Pruthvi Raj Gampalwar on 14 Mar 2019
Use
strcmp
if the values are matching then add the values to the T and W column

  1 Comment

Guillaume
Guillaume on 14 Mar 2019
ismember which uses strcmp would be a lot more useful for this.

Sign in to comment.


Peter Perkins
Peter Perkins on 20 Mar 2019
This is just an outer join:
>> T1 = table(["hello";"new";"world";"hi";"hello";"hi"],[1;2;3;5;8;7],'VariableNames',["Word" "V"])
T1 =
6×2 table
Word V
_______ _
"hello" 1
"new" 2
"world" 3
"hi" 5
"hello" 8
"hi" 7
>> T2 = table(["hi";"hello"],[2;4],[6;7],'VariableNames',["Word" "T" "W"])
T2 =
2×3 table
Word T W
_______ _ _
"hi" 2 6
"hello" 4 7
>> outerjoin(T1,T2,'MergeKeys',true)
ans =
6×4 table
Word V T W
_______ _ ___ ___
"hello" 1 4 7
"hello" 8 4 7
"hi" 5 2 6
"hi" 7 2 6
"new" 2 NaN NaN
"world" 3 NaN NaN

  1 Comment

Guillaume
Guillaume on 20 Mar 2019
Do'h! I did try using an outerjoin but using only Word as the key hence got duplicate V, T and W in the result, which I couldn't merge. Of course, If you use all columns as keys, then you get the correct outerjoin.
Note that since the data is stored in a database you don't need matlab to do the join. In SQL it'd be something like:
SELECT * FROM table1 FULL OUTERJOIN table2 ON table.Word = table2.Word

Sign in to comment.