Merge data based on two columns
显示 更早的评论
Suppose I have two datasets:
A =
1991 1 23
1992 1 32
1993 1 25
1991 2 26
1992 2 68
1993 2 69
1994 2 67
and
B =
1991 1 99
1992 1 100
1991 2 88
1994 2 89
1995 2 87
Suppose the first column is year and the second column is firm id in each matrix. How could I merge the two matrices based on the two columns to get
C =
1991 1 23 99
1992 1 32 100
1993 1 25 NaN
1991 2 26 88
1992 2 68 NaN
1993 2 69 NaN
1994 2 67 89
1995 2 NaN 87
采纳的回答
Andrei Bobrov
2016-6-24
编辑：Andrei Bobrov
2016-6-24
C = sortrows(unique([A(:,1:2);B(:,1:2)],'rows'),[2 1]);
C = [C,nan(size(C,1),2)];
C(ismember(C(:,1:2),A(:,1:2),'rows'),3) = A(:,3);
C(ismember(C(:,1:2),B(:,1:2),'rows'),4) = B(:,3);
or
AB = [A;B];
n = repelem([1;2],[size(A,1);size(B,1)]);
[a,~,c0] = unique(AB(:,1:2),'rows');
C = sortrows([a,accumarray([c0,n],AB(:,3),[],[],nan)],[2,1]);
2 个评论
Shameer Parmar
2016-6-24
The first line giving me error as follows:
.
I replace with following line as it works..
C = [sortrows(unique([A(:,1:2);B(:,1:2)],'rows'),[2 1]),nan(size(unique([A(:,1:2);B(:,1:2)],'rows'),1),2)]
Andrei Bobrov
2016-6-24
corrected
更多回答（0 个）
类别
在 帮助中心 和 File Exchange 中查找有关 NaNs 的更多信息
Community Treasure Hunt
Find the treasures in MATLAB Central and discover how the community can help you!
Start Hunting!
Translated by 
