Merge table rows having same values?

Suppose the first two columns of my table are used as row identifiers. Let's define two rows as "duplicates" if they have same identifiers.

Any missing value is marked with "NaN". I want to merge all duplicates in such a way to fill missing values, and in case of conflicts (i.e. the duplicates has some different non-NaN values in the same positions), the bottom value must be taken.

[Adam Danz]

Could you provide a small example table and indicate which rows should be merged?

[Giuseppe Antonio]

For example such a table:

Must be transformed into this:

[J. Alex Lee]

and if the NaN value in row 1 of the first table was X instead, and there was NaN in the Var3 of row 3, would the first row of the result table be 1,2,9,9,X?

[Giuseppe Antonio]

Here is almost the same example, but more complete, regarding all mentioned features in my question.

Original table:

Final one:

Answer 1

[Adam Danz]

This solution uses fillmissing() to identify NaN values and replace them using the rules described in the question. Since the example provided by OP was very small, it is recommended to verify your results with the actual data.

% Create demo table
T = table([1 3 1]', [2 4 2]', [1 4 nan]', [2 5 9]', [nan 6 9]','VariableNames',{'ID1','ID2','Var1','Var2','Var3'}); 

% Descend order of [ID1,ID2], to use the fillmissing/previous method
Ts = sortrows(T,{'ID1','ID2'},'descend'); 

% Identify row group
IDgroups = unique([Ts.ID1,Ts.ID2],'rows');  % each row is a unique [ID1,ID2] in Ts

% Loop through groups
for i = 1:size(IDgroups,1)
    idx = all([Ts.ID1,Ts.ID2] == IDgroups(i,:),2); % find matching rows 
    % Replace NaN with previous value; if NaN is in row 1, replace with nearest value.
    Ts(idx,:) = fillmissing(Ts(idx,:),'previous','EndValues','nearest'); 
    % If there are duplicate [ID1,ID2] rows, choose last one
    idx(find(idx,1,'last')) = false; 
    Ts(idx,:) = [];
end

% Resort Ts 
Ts = sortrows(Ts,{'ID1','ID2'}); % back to ascending order

Answer 2

[J. Alex Lee]

Updated based on Adam Danz's better use of fillmissing().

I think you want to keep the 'stable' keyword rather than 'sort', if I understand correctly what you mean by "bottom value". New solution based on fillmissing(). I am not sure you need ('EndValues','nearest') since you wouldn't care about NaN values in the top row. Also, avoiding reshaping the target table on-the-fly (maybe matter of taste).

T0 = table([1 3 1]', [2 4 2]', [1 4 nan]', [2 5 9]', [nan 6 9]','VariableNames',{'ID1','ID2','Var1','Var2','Var3'}); 

% isolate the tags
TagMat = [T0.ID1,T0.ID2];

% find the unique tag pairs, and rows corresponding to each pair
[unqPairs,~,RowIDs] = unique(TagMat,'rows','stable')

% Loop through groups
TCell = cell(size(unqPairs,1),1)
for i = 1:size(unqPairs,1)
    
    % create a temporary table
    t = T0(RowIDs==i,:)
    
    % Replace NaN with previous value; if NaN is in row 1, replace with nearest value.
    t = fillmissing(t,'previous');
    
    % fill in a new cell array to concatenate into a new table later
    % just always use last row, no need to check for duplicates
    TCell{i} = t(end,:);
end

T1 = vertcat(TCell{:})

Old logic:

% loop through the unique pairs
newrows = nan(size(unqPairs,1),3)
for i = 1:size(unqPairs,1)
    % data for all rows corresponding to current unqPair
    data = T0{RowIDs==i,'Var1','Var2','Var3'}
    
    % find all non-nan instances
    notnanmask = ~isnan(data)
    
    % find the last row-wise instance for each column
    for j = size(data,2):-1:1
        newrows(j) = data(find(data(:,j),1,'last'),j);
    end
    
end

matrx = [unqPairs,newrows]
T1 = array2table(matrx,'VariableNames',{'ID1','ID2','Var1','Var2','Var3'})

Answer 3

[J. Alex Lee]

Also, how about this

T0 = table([1 3 1]', [2 4 2]', [1 4 nan]', [2 5 9]', [nan 6 9]','VariableNames',{'ID1','ID2','Var1','Var2','Var3'}); 

% apply fillmissing() in one shot
TFilled = grouptransform(T0,{'ID1','ID2'},@(t)fillmissing(t,'previous'))

% select the last rows with unique ID pairs
[~,idx] = unique(G{:,{'ID1','ID2'}},'rows','last')

T = TFilled(idx,:)

[Adam Danz]

Good idea to use grouptransform! However, it's slightly incomplete since it doesn't deal with NaNs in the first row. This will fix that.

TFilled = grouptransform(T0,{'ID1','ID2'},@(t)fillmissing(t,'previous','EndValues','nearest'))