MATLAB Answers

0

Infil NaN for missing years in time series

Asked by Poulomi Ganguli on 9 Aug 2019
Latest activity Commented on by Neuropragmatist on 9 Aug 2019
Hello:
I have two matrices, A and B. Matrix B has some years missing in it. I want to concatenate horizontally two matrices, A and B to a new matrix C, which can be filled with NaN for the missing years.
A =
1981 0.79 1.56 0.90 1.15
1982 0.62 0.83 0.84 0.74
1983 0.81 0.71 0.71 0.70
1984 1.06 0.74 0.61 0.76
1985 1.23 0.86 0.67 0.61
1986 1.32 0.56 1.11 0.76
1987 0.75 1.06 0.56 1.15
1988 1.76 1.09 0.88 0.67
1989 0.90 0.77 0.94 0.77
1990 0.52 0.52 1.15 0.88
and B =
1981 1.0617
1982 1.0682
1985 1.0149
1986 0.6607
1987 0.5642
1988 0.6194
1989 0.6693
1990 0.6966
Desired output, C =
1981 1.0617 0.79 1.56 0.90 1.15
1982 1.0682 0.62 0.83 0.84 0.74
1983 NaN 0.81 0.71 0.71 0.70
1984 NaN 1.06 0.74 0.61 0.76
1985 1.0149 1.23 0.86 0.67 0.61
1986 0.6607 1.32 0.56 1.11 0.76
1987 0.5642 0.75 1.06 0.56 1.15
1988 0.6194 1.76 1.09 0.88 0.67
1989 0.6693 0.90 0.77 0.94 0.77
1990 0.6966 0.52 0.52 1.15 0.88

  0 Comments

Sign in to comment.

1 Answer

Answer by Neuropragmatist on 9 Aug 2019
 Accepted Answer

If you don't mind converting your data to tables you can use outerjoin:
A = [1981 0.79 1.56 0.90 1.15;
1982 0.62 0.83 0.84 0.74;
1983 0.81 0.71 0.71 0.70;
1984 1.06 0.74 0.61 0.76;
1985 1.23 0.86 0.67 0.61;
1986 1.32 0.56 1.11 0.76;
1987 0.75 1.06 0.56 1.15;
1988 1.76 1.09 0.88 0.67;
1989 0.90 0.77 0.94 0.77;
1990 0.52 0.52 1.15 0.88];
B = [1981 1.0617;
1982 1.0682;
1985 1.0149;
1986 0.6607;
1987 0.5642;
1988 0.6194;
1989 0.6693;
1990 0.6966];
At = array2table(A);
Bt = array2table(B);
C = outerjoin(At,Bt,'Keys',1,'RightVariables',2);
C = C(:,[1 6 2:5])
C =
10×6 table
A1 B2 A2 A3 A4 A5
____ ______ ____ ____ ____ ____
1981 1.0617 0.79 1.56 0.9 1.15
1982 1.0682 0.62 0.83 0.84 0.74
1983 NaN 0.81 0.71 0.71 0.7
1984 NaN 1.06 0.74 0.61 0.76
1985 1.0149 1.23 0.86 0.67 0.61
1986 0.6607 1.32 0.56 1.11 0.76
1987 0.5642 0.75 1.06 0.56 1.15
1988 0.6194 1.76 1.09 0.88 0.67
1989 0.6693 0.9 0.77 0.94 0.77
1990 0.6966 0.52 0.52 1.15 0.88

  1 Comment

Or using indexing:
A = [1981 0.79 1.56 0.90 1.15;
1982 0.62 0.83 0.84 0.74;
1983 0.81 0.71 0.71 0.70;
1984 1.06 0.74 0.61 0.76;
1985 1.23 0.86 0.67 0.61;
1986 1.32 0.56 1.11 0.76;
1987 0.75 1.06 0.56 1.15;
1988 1.76 1.09 0.88 0.67;
1989 0.90 0.77 0.94 0.77;
1990 0.52 0.52 1.15 0.88];
B = [1981 1.0617;
1982 1.0682;
1985 1.0149;
1986 0.6607;
1987 0.5642;
1988 0.6194;
1989 0.6693;
1990 0.6966];
A = [A NaN(size(A(:,1)))];
[~,LOCB] = ismember(B(:,1),A(:,1));
A(LOCB(LOCB>0),6) = B(LOCB>0,2);
A = A(:,[1 6 2 3 4 5])
A =
Columns 1 through 4
1981 1.0617 0.79 1.56
1982 1.0682 0.62 0.83
1983 NaN 0.81 0.71
1984 NaN 1.06 0.74
1985 1.0149 1.23 0.86
1986 0.6607 1.32 0.56
1987 0.5642 0.75 1.06
1988 0.6194 1.76 1.09
1989 0.6693 0.9 0.77
1990 0.6966 0.52 0.52
Columns 5 through 6
0.9 1.15
0.84 0.74
0.71 0.7
0.61 0.76
0.67 0.61
1.11 0.76
0.56 1.15
0.88 0.67
0.94 0.77
1.15 0.88

Sign in to comment.