清空筛选条件
清空筛选条件

Merge table rows having same values?

25 次查看（过去 30 天）
Giuseppe Antonio
Giuseppe Antonio 2019-12-17
编辑： Adam Danz 2019-12-20
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 个评论
显示 2更早的评论
J. Alex Lee
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?
Giuseppe Antonio
Giuseppe Antonio 2019-12-20
Here is almost the same example, but more complete, regarding all mentioned features in my question.
Original table:
tab1.PNG
Final one:
tab2.PNG

请先登录，再进行评论。

请先登录，再回答此问题。

回答（3 个）

Adam Danz
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
J. Alex Lee
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'})
J. Alex Lee
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,:)
  1 个评论
Adam Danz
Adam Danz 2019-12-20
编辑：Adam Danz 2019-12-20
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'))

请先登录，再进行评论。

类别

在 Help Center 和 File Exchange 中查找有关 Tables 的更多信息

标签

产品


版本

R2016a

Community Treasure Hunt

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

Start Hunting!

Translated by