Merge two columns with missing values

8 次查看（过去 30 天）
Amina Ag
Amina Ag 2021-6-12
编辑： Adam Danz 2021-6-15
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 个评论
显示 3更早的评论
Amina Ag
Amina Ag 2021-6-12
When I try the code below i get an error message regarding it being a categorical varibale. How can I get around this problem?
>> Tcontent = T{:,:}; % assumes all columns are the same class
Error using categorical/subsref (line 35)
Cell contents reference from a non-cell array object.
Adam Danz
Adam Danz 2021-6-12
编辑：Adam Danz 2021-6-15
> 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.

请先登录，再进行评论。

请先登录，再回答此问题。

回答（1 个）

Adam Danz
Adam Danz 2021-6-12
编辑：Adam Danz 2021-6-12
Ran in:
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

类别

在 Help Center 和 File Exchange 中查找有关 Whos 的更多信息

标签

Community Treasure Hunt

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

Start Hunting!

Translated by