How to place values in a matrix with values from another matrix?

1 view (last 30 days)
I have 2 tables A and B:
A B
2018 13 2018 0.2
2019 15 2019 0.3
2020 -76 2020 0.4
2021 -1 2020 0.6
2022 0 2020 0.1
2021 0.7
2021 0.1
I want to expand B by adding an additional column from A which is related to the first column of both tables: wherever the year (=column 1) is the same, the value from A(:,2) should be added in a 3rd column in B.
The new table B should thus look as follows:
A B
2018 13 2018 0.2 13
2019 15 2019 0.3 15
2020 -76 2020 0.4 -76
2021 -1 2020 0.6 -76
2022 0 2020 0.1 -76
2021 0.7 -1
2021 0.1 -1
Thank you in advance!

Answers (2)

Arif Hoq
Arif Hoq on 9 Mar 2022
Edited: Arif Hoq on 9 Mar 2022
Year=[2018;2019;2020;2021;2022];
value1=[13;15;-76;-1;0];
table1=table2array(table(Year,value1))
table1 = 5×2
2018 13 2019 15 2020 -76 2021 -1 2022 0
Year2=[2018;2019;2020;2020;2020;2021;2021;];
value2=[0.2;0.3;0.4;0.6;0.1;0.7;0.1;];
table2=table2array(table(Year2,value2))
table2 = 7×2
1.0e+03 * 2.0180 0.0002 2.0190 0.0003 2.0200 0.0004 2.0200 0.0006 2.0200 0.0001 2.0210 0.0007 2.0210 0.0001
[idx out]=ismember(table2(:,1),table1(:,1));
newcol=table1(out,2);
newtable=table(Year2,value2,newcol)
newtable = 7×3 table
Year2 value2 newcol _____ ______ ______ 2018 0.2 13 2019 0.3 15 2020 0.4 -76 2020 0.6 -76 2020 0.1 -76 2021 0.7 -1 2021 0.1 -1

Peter Perkins
Peter Perkins on 9 Mar 2022
This is a one-liner with join. You have time, so use a timetable:
>> year = datetime([2018;2019;2020;2021;2022],1,1); x = [13;15;-76;-1;0];
A = timetable(year,x)
A =
5×1 timetable
year x
___________ ___
01-Jan-2018 13
01-Jan-2019 15
01-Jan-2020 -76
01-Jan-2021 -1
01-Jan-2022 0
>> year = datetime([2018;2019;2020;2020;2020;2021;2021],1,1); y = [.2;.3;.4;.6;.1;.7;.1];
B = timetable(year,y)
B =
7×1 timetable
year y
___________ ___
01-Jan-2018 0.2
01-Jan-2019 0.3
01-Jan-2020 0.4
01-Jan-2020 0.6
01-Jan-2020 0.1
01-Jan-2021 0.7
01-Jan-2021 0.1
>> B = join(B,A,"Keys","year")
B =
7×2 timetable
year y x
___________ ___ ___
01-Jan-2018 0.2 13
01-Jan-2019 0.3 15
01-Jan-2020 0.4 -76
01-Jan-2020 0.6 -76
01-Jan-2020 0.1 -76
01-Jan-2021 0.7 -1
01-Jan-2021 0.1 -1

Categories

Find more on Tables in Help Center and File Exchange

Community Treasure Hunt

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

Start Hunting!