MATLAB Answers

Get value from table/matrix based on common attribute

3 views (last 30 days)
John Taylor
John Taylor on 26 Feb 2021
Edited: John Taylor on 2 Mar 2021
I have two tables, A with informtions about people and where they live, B informations about cities. Several people can live in the same city. How can I add columns in A with the informations in B (city name, country) based on the Citycode without a loop?
A = table('Name','First','Citycode')
B = table('Citycode','CityName','Country')
A = ['Smith' 'John' '1001';
'Taylor' 'James' '1004';
'Thatcher' 'Mary' '1001']
B = ['1001' 'New York' 'USA';
'1002' 'London' 'UK';
'1004' 'Madrid' 'Spain']
  1 Comment
Rik
Rik on 26 Feb 2021
Your syntax will not result in two tables. Please modify your code to reflect the actual data type of your variables.
I expect ismember will be helpful, but without your actual input data, I can't write the code for you.

Sign in to comment.

Accepted Answer

Hernia Baby
Hernia Baby on 26 Feb 2021
% input data
A = {'Smith' 'John' '1001';
'Taylor' 'James' '1004';
'Thatcher' 'Mary' '1001'};
B = {'1001' 'New York' 'USA';
'1002' 'London' 'UK';
'1004' 'Madrid' 'Spain'};
A_t = cell2table(A,'VariableNames',{'Name','First','Citycode'});
B_t = cell2table(B,'VariableNames',{'Citycode','CityName','Country'});
% join
T = innerjoin(A_t,B_t)
T =
3×5 table
Name First Citycode CityName Country
____________ _________ ________ ____________ _________
{'Smith' } {'John' } {'1001'} {'New York'} {'USA' }
{'Thatcher'} {'Mary' } {'1001'} {'New York'} {'USA' }
{'Taylor' } {'James'} {'1004'} {'Madrid' } {'Spain'}
  1 Comment
John Taylor
John Taylor on 2 Mar 2021
Thanks, in my case, there are people who do not have a citycode assigned and disappear with the innerjoin command (which only keeps the matching citycodes). I rather used:
T = outerjoin(A_t,B_t,'Type','left')
So, my table T has the same number of rows as A_t, and there's no loss of information.

Sign in to comment.

More Answers (0)

Products


Release

R2020a

Community Treasure Hunt

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

Start Hunting!