Merge two columns with missing values
15 次查看(过去 30 天)
显示 更早的评论
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 个评论
回答(1 个)
Adam Danz
2021-6-12
编辑:Adam Danz
2021-6-12
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)
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
0 个评论
另请参阅
类别
在 Help Center 和 File Exchange 中查找有关 Data Type Identification 的更多信息
Community Treasure Hunt
Find the treasures in MATLAB Central and discover how the community can help you!
Start Hunting!