How to make a new table from two different table with common value?

1 view (last 30 days)
jeon
jeon on 23 Aug 2021
Commented: Eric Sofen on 23 Aug 2021
Hello. I have 4800960x1 table T1, 77449x1 table T2.
T1 and T2 is like
column 1 column 2
datetime value
I want to make a new table with 3 columns(common datetime of T1 and T2, value of T1, value of T2)
So far, I made 77209x1 datetime C which is the common datetime of T1 and T2.
I tried to make a new 77209x3 table which has 3 columns(datetime of C, values from T1, values from T2).
for example:
C
2010-01-01 01:00:00
2010-01-01 02:00:00
2010-01-01 03:00:00
2010-01-01 04:00:00
T1
2010-01-01 01:00:00 10
2010-01-01 02:00:00 11
2010-01-01 03:00:00 12
2010-01-01 04:00:00 13
2010-01-01 05:00:00 14
2010-01-01 06:00:00 15
2010-01-01 07:00:00 16
2010-01-01 08:00:00 17
T2
2010-01-01 01:00:00 0
2010-01-01 02:00:00 1
2010-01-01 03:00:00 2
2010-01-01 04:00:00 3
result:
2010-01-01 01:00:00 10 0
2010-01-01 02:00:00 11 1
2010-01-01 03:00:00 12 2
2010-01-01 04:00:00 13 3
I tried to use innerjoin, but it didn't work because C was not a table.
How can I get the results I want?

Answers (3)

KSSV
KSSV on 23 Aug 2021
Let T1 and T2 be your tables having two columnn each.
idx = ismember(T.(1),T.(2)) ;
T1 = T1(idx,:) ;
T1.(3) = T2.(2) ;

Wan Ji
Wan Ji on 23 Aug 2021
Edited: Wan Ji on 23 Aug 2021
result has the same row number as T1 or T2?
result = table(C,'variablenames',{'datetime'});
result.value1 = T1.value(1:(size(result,1)),1);
result.value2 = T2.value(1:(size(result,1)),1);

Eric Sofen
Eric Sofen on 23 Aug 2021
You should be able to use innerjoin on T1 and T2 - no need for C. The innerjoin function calculates the equivalent of C as an intermediate step. I think @KSSV's answer will run into trouble if there are times in T2 that aren't in T1, because it's assuming that T1(idx,:) is the same size as T2.
  2 Comments
Eric Sofen
Eric Sofen on 23 Aug 2021
Consider a modified version of @jeon's original sample data.
T1
2010-01-01 01:00:00 10
2010-01-01 03:00:00 12
2010-01-01 05:00:00 14
2010-01-01 07:00:00 16
2010-01-01 09:00:00 17
T2
2010-01-01 01:00:00 0
2010-01-01 02:00:00 1
2010-01-01 03:00:00 2
2010-01-01 04:00:00 3
idx = ismember(T1.(1),T2.(1))
idx =
5×1 logical array
1
1
0
0
0
T1 = T1(idx,:); % table with 2 rows.
T2.(3) = T2.(2) % Error because T2.(2) has 4 rows.
To assign to or create a variable in a table, the number of rows must match the height of the table.

Sign in to comment.

Tags

Community Treasure Hunt

Find the treasures in MATLAB Central and discover how the community can help you!

Start Hunting!