Filling in data based on index

user_12684429 0
Viewed 184

I have a multiindex dataframe with years and months as shown;


            A       B       C       D       E       F       G       H       I
2019    8   15.0    15.0    8.0     9.0     18.0    27.0    56.0    21.0    168.0
        9   20.0    21.0    11.0    12.0    26.0    37.0    73.0    41.0    241.0
        10  25.0    39.0    20.0    19.0    51.0    49.0    133.0   74.0    411.0
        11  32.0    65.0    34.0    26.0    110.0   110.0   193.0   147.0   718.0
        12  36.0    72.0    38.0    31.0    122.0   152.0   205.0   167.0   821.0
2020    1   42.0    73.0    39.0    35.0    131.0   179.0   205.0   173.0   876.0
        2   32.0    71.0    37.0    30.0    113.0   141.0   212.0   151.0   787.0
        3   29.0    60.0    32.0    26.0    99.0    120.0   187.0   145.0   700.0
        4   20.0    32.0    16.0    17.0    45.0    62.0    108.0   82.0    381.0
        5   16.0    28.0    15.0    13.0    37.0    38.0    96.0    71.0    314.0

And I want to append another dataframe to the end for the rest of the year which has a monthly value for each column. 


    A       B       C       D       E       F       G       H       I                               
1   41.0    84.0    41.0    37.0    144.0   183.0   221.0   187.0   952.0
2   35.0    80.0    40.0    34.0    131.0   165.0   219.0   174.0   875.0
3   29.0    65.0    32.0    27.0    102.0   123.0   191.0   145.0   701.0
4   20.0    39.0    20.0    18.0    59.0    64.0    137.0   88.0    432.0
5   15.0    26.0    14.0    13.0    40.0    43.0    96.0    55.0    303.0
6   12.0    18.0    9.0     10.0    24.0    35.0    71.0    26.0    200.0
7   12.0    15.0    7.0     9.0     20.0    32.0    58.0    21.0    174.0
8   12.0    16.0    8.0     9.0     18.0    26.0    59.0    21.0    170.0
9   17.0    22.0    11.0    12.0    27.0    37.0    77.0    40.0    240.0
10  23.0    39.0    19.0    19.0    55.0    54.0    120.0   80.0    408.0
11  31.0    63.0    31.0    28.0    110.0   111.0   180.0   137.0   716.0
12  36.0    71.0    36.0    32.0    131.0   168.0   200.0   161.0   858.0

I need to try and append the dataframes to infill any blank months from the first dataframe with the values from the second.

I suppose there are two questions; - Do I need to add a second index onto the second dataframe in order to join these? - I suppose I need to do some form of 'if' statement to say only infill future blank months?

A lot of attempts at joins/concat/append are giving me the following;

(2019, 12)  36.0    72.0    38.0    31.0    122.0   152.0   205.0   167.0   821.0
(2020, 1)   42.0    73.0    39.0    35.0    131.0   179.0   205.0   173.0   876.0
(2020, 2)   32.0    71.0    37.0    30.0    113.0   141.0   212.0   151.0   787.0
(2020, 3)   29.0    60.0    32.0    26.0    99.0    120.0   187.0   145.0   700.0
(2020, 4)   20.0    32.0    16.0    17.0    45.0    62.0    108.0   82.0    381.0
(2020, 5)   16.0    28.0    15.0    13.0    37.0    38.0    96.0    71.0    314.0
1           41.0    84.0    41.0    37.0    144.0   183.0   221.0   187.0   952.0
2           35.0    80.0    40.0    34.0    131.0   165.0   219.0   174.0   875.0
3           29.0    65.0    32.0    27.0    102.0   123.0   191.0   145.0   701.0
4           20.0    39.0    20.0    18.0    59.0    64.0    137.0   88.0    432.0
5           15.0    26.0    14.0    13.0    40.0    43.0    96.0    55.0    303.0
6           12.0    18.0    9.0 10.0    24.0    35.0    71.0    26.0    200.0
7           12.0    15.0    7.0 9.0 20.0    32.0    58.0    21.0    174.0
8           12.0    16.0    8.0 9.0 18.0    26.0    59.0    21.0    170.0
9           17.0    22.0    11.0    12.0    27.0    37.0    77.0    40.0    240.0
10          23.0    39.0    19.0    19.0    55.0    54.0    120.0   80.0    408.0
11          31.0    63.0    31.0    28.0    110.0   111.0   180.0   137.0   716.0
12          36.0    71.0    36.0    32.0    131.0   168.0   200.0   161.0   858.0

So I would need the new dataframe to infill the first dataframe based on month.

Any help much appreciated, can't seem to figure it out.

multi-index pandas indexing datetime python
1 Answers

Use DataFrame.combine_first with DataFrame.reindex by second DataFrame with values of years from df1:

mux = pd.MultiIndex.from_product([df1.index.levels[0], df2.index])
df = df1.combine_first(df2.reindex(mux, level=1))
print (df)
            A     B     C     D      E      F      G      H      I
2019 1   41.0  84.0  41.0  37.0  144.0  183.0  221.0  187.0  952.0
     2   35.0  80.0  40.0  34.0  131.0  165.0  219.0  174.0  875.0
     3   29.0  65.0  32.0  27.0  102.0  123.0  191.0  145.0  701.0
     4   20.0  39.0  20.0  18.0   59.0   64.0  137.0   88.0  432.0
     5   15.0  26.0  14.0  13.0   40.0   43.0   96.0   55.0  303.0
     6   12.0  18.0   9.0  10.0   24.0   35.0   71.0   26.0  200.0
     7   12.0  15.0   7.0   9.0   20.0   32.0   58.0   21.0  174.0
     8   15.0  15.0   8.0   9.0   18.0   27.0   56.0   21.0  168.0
     9   20.0  21.0  11.0  12.0   26.0   37.0   73.0   41.0  241.0
     10  25.0  39.0  20.0  19.0   51.0   49.0  133.0   74.0  411.0
     11  32.0  65.0  34.0  26.0  110.0  110.0  193.0  147.0  718.0
     12  36.0  72.0  38.0  31.0  122.0  152.0  205.0  167.0  821.0
2020 1   42.0  73.0  39.0  35.0  131.0  179.0  205.0  173.0  876.0
     2   32.0  71.0  37.0  30.0  113.0  141.0  212.0  151.0  787.0
     3   29.0  60.0  32.0  26.0   99.0  120.0  187.0  145.0  700.0
     4   20.0  32.0  16.0  17.0   45.0   62.0  108.0   82.0  381.0
     5   16.0  28.0  15.0  13.0   37.0   38.0   96.0   71.0  314.0
     6   12.0  18.0   9.0  10.0   24.0   35.0   71.0   26.0  200.0
     7   12.0  15.0   7.0   9.0   20.0   32.0   58.0   21.0  174.0
     8   12.0  16.0   8.0   9.0   18.0   26.0   59.0   21.0  170.0
     9   17.0  22.0  11.0  12.0   27.0   37.0   77.0   40.0  240.0
     10  23.0  39.0  19.0  19.0   55.0   54.0  120.0   80.0  408.0
     11  31.0  63.0  31.0  28.0  110.0  111.0  180.0  137.0  716.0
     12  36.0  71.0  36.0  32.0  131.0  168.0  200.0  161.0  858.0
user_29010020
Related