MATLAB Answers

Merge two columns with missing values

5 views (last 30 days)
Amina Ag
Amina Ag on 12 Jun 2021
Edited: Adam Danz on 15 Jun 2021
Hi,
I have a table with two columns that look something like this:
CEO1 CEO2 CEO3
M M M
M <undefined> M
F F F
<undefined> <undefined> <undefined>
F <undefined> F
<undefined> M M
Please see the attached screenshot. These are all a part of a big table. I would like to merge them all so that the missing values are filled, but I want to keep the missing values where there is nothing to be filled. Have tried many things but nothing works, also as they are categorical. Would appreciate your help.
Thank you!
  5 Comments
Adam Danz
Adam Danz on 12 Jun 2021
> How can I get around this problem?
By following step #1 in my comment above. Extract the columns containing categorical values into a separate table, apply the code in my answer, then add the final single-column table to your original table.

Sign in to comment.

Answers (1)

Adam Danz
Adam Danz on 12 Jun 2021
Edited: Adam Danz on 12 Jun 2021
This solution finds the first non-missing value in each row and assigns those values to all columns of the table. If all columns contain missing values within a row, the row will retain the missing value.
I wonder if join | innerjoin | outerjoin functions could be used instead but I haven't dug into that.
Create demo table T
rng 'default' % for reproducibililty
str = 'MF';
v = categorical(cellstr(str(randi(2,10,1))'));
M = [v,v,v];
M(randi(numel(M),1,15)) = missing;
T = array2table(M)
T = 10×3 table
M1 M2 M3 ___________ ___________ ___________ F F <undefined> <undefined> F F M <undefined> M F F <undefined> <undefined> <undefined> <undefined> M M <undefined> M M M F F <undefined> F F <undefined> F <undefined> <undefined>
Get non-missing data for each row
Tidx is an nx2 matrix for n rows of the table containg the first [row, column] numbers of T that do not have missing values for each row. If the whole row is missing, is just uses column 1.
mergedData is a column of non-missing data for each row of T unless the entire row is missing.
isNotMissing = ~ismissing(T);
Tidx = (1:height(T))';
Tidx(:,2) = arrayfun(@(r)find([isNotMissing(r,:),true],1), 1:size(isNotMissing,1))';
Tidx(Tidx(:,2)==width(T)+1,2) = 1;
Tcontent = T{:,:}; % assumes all columns are the same class
ind = sub2ind(size(T),Tidx(:,1), Tidx(:,2));
mergedData = Tcontent(ind); % col vector
for i = 1:width(T)
T{:,i} = mergedData;
end
T
T = 10×3 table
M1 M2 M3 ___________ ___________ ___________ F F F F F F M M M F F F <undefined> <undefined> <undefined> M M M M M M F F F F F F F F F

Community Treasure Hunt

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

Start Hunting!