MATLAB Answers

0

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

Asked by Haritha on 14 Mar 2019
Latest activity Edited by 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

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?
Hi,
I will edit that table

Sign in to comment.

3 Answers

Answer by Guillaume
on 14 Mar 2019
 Accepted Answer

%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

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).
Its working for normal tables. I forget to mention the SQL. I accepted my mistake

Sign in to comment.


Answer by 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

ismember which uses strcmp would be a lot more useful for this.

Sign in to comment.


Answer by 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

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.