Merge two columns with missing values

10 visualizzazioni (ultimi 30 giorni)
Amina Ag
Amina Ag il 12 Giu 2021
Modificato: Adam Danz il 15 Giu 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 Commenti
Amina Ag
Amina Ag il 12 Giu 2021
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 il 12 Giu 2021
Modificato: Adam Danz il 15 Giu 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.

Accedi per commentare.

Risposte (1)

Adam Danz
Adam Danz il 12 Giu 2021
Modificato: Adam Danz il 12 Giu 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

Categorie

Scopri di più su Data Type Identification in Help Center e File Exchange

Community Treasure Hunt

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

Start Hunting!

Translated by