Merge two columns with missing values

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!

[Adam Danz]

So the results will be a table with 3 identical columns or a table with 1 column?

[Amina Ag]

I have a table with 4 000 000x24 variables. These are the last 3. I would like to merge them into 1 in the same table.

[Adam Danz]

Then you can follow the example in my answer with 2 modification.

1. Copy those 3 columns into a separate table using T=TBL(:,n:m) where TBL is the original table and n,m are the column indices.
2. At the end of my answer, instead of assigning the value to all 3 columns, just assign them to 1 column and add that to your original table. You can remove the redundant columns if you'd like.

[Amina Ag]

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]

> 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.

Answer 1

[Adam Danz]

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