How do I merge a column from one table into the column of another table that has missing entries?

41 views (last 30 days)
I have two tables with different column names and different number of rows. Column 3 of table A for example is missing some entries that are present in column 2 of table B. I want to add the missing entries from B into A based on the values in another column, say column 2 of table A. However, not all the same entries are present in the two tables for the key.
For example, the two tables look like this:
 
A = table({'John','Jane','Jim','Jerry','Jill'}',[1;2;1;3;1], {'', '', '', 'Mat',''}', ...
    'VariableNames',{'Employee' 'Department', 'Manager'})
 
B = table([1 2]',{'Mary' 'Mike'}',...
    'VariableNames',{'Department' 'Manager'})
 
If I try using the "join" function, I get an error because there are more Departments in A than in B.
  >> join(A,B)
Error using table/join (line 128)
The key variables for A and B cannot contain any missing values.
  How can I do this?

Accepted Answer

MathWorks Support Team
MathWorks Support Team on 30 Oct 2014
There are two possible solutions depending on the output you expect to see:
1. Using "outerjoin"
Using the "outerjoin" function and specifying the variable "Keys" to use to merge the tables produces the output:
 
C = outerjoin(A,B,'Keys','Department','MergeKeys', 1)
 
2. Using "ismember"
Using the "ismember" function to determine the matching rows in A and B and adding the missing entries in A from B produces the output:
  [idxA, idxB] = ismember(A.Department, B.Department);
A(idxA,'Manager') = B(idxB(idxA),'Manager');
 

More Answers (0)

Categories

Find more on Tables in Help Center and File Exchange

Tags

No tags entered yet.

Products


Release

R2014b

Community Treasure Hunt

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

Start Hunting!