In a table with duplicate ids, how do I merge rows with values with rows with NaNs?

I have a table like this:

   id  var_1  var_2  var_3 var_4
  3101  6  NaN  6       NaN
  3101  NaN     NaN     NaN     1    
  3101  NaN     4       NaN     NaN               
  8701  5  5  5       NaN
  8701  NaN     NaN     NaN     3

I want to collapse the rows so there's only one row per id with all the information.

I am lost on how to do it.

Any help would be highly appreciated.

Sincerely,

Answer 1

[Walter Roberson]

min() of a vector that contains NaN will take the min of the non-NaN values, returning NaN only if all of the values in the vector are NaN.

[Victoria]

Thanks for the suggestion I am working with tables, and I will have tons of columns and tons of rows. Just one variable is an identifier and has duplicates. How do I manage the table in order to merge the rest of the values regarding that variable? I am confused. Thanks!

Answer 2

[Thorsten]

X = [3101  6  NaN  6       NaN
  3101  NaN     NaN     NaN     1
  3101  NaN     4       NaN     NaN
  8701  5  5  5       NaN
  8701  NaN     NaN     NaN     3]

ids = unique(X(:,1));
Y = nan(numel(ids), size(X,2));

for i = 1:numel(ids)

    ind_idi = X(:,1) == ids(i);
    Xidi = X(ind_idi, 2:end);
    ind_notnan = ~isnan(Xidi);

    % check that there is only one non-NaN in each row for the ith id
    assert(all(sum(ind_notnan) == ones(1, size(X,2)-1)), 'error')

    Y(i,:) = [ids(i) Xidi(ind_notnan)'];
  end

Y

Answer 3

[Guillaume]

As per Walter's answer, you can use min (or max) to get the non-nan value in each row. There is a very handy function, rowfun which allows you to apply the same function to each row of the table.

t = array2table([
    3101  6  NaN  6       NaN
    3101  NaN     NaN     NaN     1    
    3101  NaN     4       NaN     NaN               
    8701  5  5  5       NaN
    8701  NaN     NaN     NaN     3], 'VariableNames', {'id', 'var_1', 'var_2', 'var_3', 'var_4'});

t2 = [t(:, 1), rowfun(@min, t(:, 2:end), 'SeparateInputs', false)]

Alternatively, convert your table to a matrix, apply min and convert back to table. This may actually be faster as the min function is only called once:

t = array2table([
    3101  6  NaN  6       NaN
    3101  NaN     NaN     NaN     1    
    3101  NaN     4       NaN     NaN               
    8701  5  5  5       NaN
    8701  NaN     NaN     NaN     3], 'VariableNames', {'id', 'var_1', 'var_2', 'var_3', 'var_4'});

t2 = [t(:, 1), array2table(min(table2array(t(:, 2:end)), [], 2))]