MATLAB Answers

Infil NaN for missing years in time series

1 view (last 30 days)
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.

Accepted Answer

Neuropragmatist
Neuropragmatist on 9 Aug 2019
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

Neuropragmatist
Neuropragmatist on 9 Aug 2019
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.

More Answers (0)

Community Treasure Hunt

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

Start Hunting!