Merge table rows having same values?
17 次查看(过去 30 天)
显示 更早的评论
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.
4 个评论
J. Alex Lee
2019-12-20
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?
回答(3 个)
Adam Danz
2019-12-20
编辑:Adam Danz
2019-12-20
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
0 个评论
J. Alex Lee
2019-12-20
编辑:J. Alex Lee
2019-12-20
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'})
0 个评论
J. Alex Lee
2019-12-20
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,:)
另请参阅
Community Treasure Hunt
Find the treasures in MATLAB Central and discover how the community can help you!
Start Hunting!