ismember: Find matching entries in table columns (and add entry from matching row)

60 views (last 30 days)
I have a column A with some participant numbers, column B with a complete list of participant numbers and column C with a complete list of their ages. What I want to do is simply add a column D that contains the ages of the participants in A, i.e. to find each participant number from A in column B and to copy the age from the corresponding row (since B has more rows than A, the rows with the same participant numbers will not match - it's not like B just has additional rows added in the end). However, nothing I tried worked so I'm happy about any tip!
age = readtable('Age.csv','TreatAsEmpty',{'NA'});
for row_number = 1:1000
ismember(age(a,1),age(:,2))
%age(a,4) = age(a,3) %wrong because the corresponding age will not be in row a, but I don't know how to do this
end
The error I get is: Error using tabular/ismember (line 37). A and B must contain the same variables.
...and at that point I did not even try to copy and enter the age, I did not even manage to find matching participant numbers. My attempt at copying and entering the age:
age = readtable('Age.csv','TreatAsEmpty',{'NA'});
for row_number = 1:1000
if ismember(age(a,1),age(:,2))
age(a,4) = age(a,3) %wrong because the corresponding age will not be in row a, but I don't know how else to do this
end
end
I also tried using a structure instead of a table but apparently then I cannot access the different columns. I also considered "containts" and "intersect" but these don't seem right.

Accepted Answer

Peter Perkins
Peter Perkins on 9 Aug 2021
This is a one-liner with tables:
>> A = table([1;3;5])
A =
3×1 table
Var1
____
1
3
5
>> B = table([1;2;3;4;5],[11;12;13;14;15])
B =
5×2 table
Var1 Var2
____ ____
1 11
2 12
3 13
4 14
5 15
>> join(A,B,"Key","Var1")
ans =
3×2 table
Var1 Var2
____ ____
1 11
3 13
5 15
  1 Comment
RP
RP on 10 Aug 2021
Thanks a lot, this is exactly what I was looking for! In the documentation it says "the key variables of Tright must contain all values in the key variables of Tleft" - do you (or someone else) know if there is a way to make it work if some values are missing in the right table? Or is the only way to actually find and remove these values before with another piece of code?

Sign in to comment.

More Answers (1)

Walter Roberson
Walter Roberson on 9 Aug 2021
ismember(age{a,1},age{:,2})

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!