MATLAB: Merge data based on two columns

merge data

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

Best Answer

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]);
Related Question