Infill missing data with replacement data

I have a timeseries dataframe in daily granularity for 5 columns

            A  B  C  D  E 
31/08/2021  1  4  3  8  9
01/09/2021  8  9  3  1  0
.
.
.
13/09/2021 8 9 0 9 3

And I have a table of monthly ‘normal’ values;

   A  B  C  D  E
1  8  3  3  3  1
2  4  5  6  4  6
3  6  4  6  4  2
.
.
.
12 4 6 6  6 4

Essentially the raw data I’m using is messy and I need a way of infilling any gaps with its respective ‘normal’ value when data is missing.

So if for example there was no data for 21/03/2021 in the B column it would infill ‘4’ as the normal value for B in March from the table.

Really struggling with this so any help is much appreciated!

Answer

Use DataFrame.asfreq for add missing datetimes and then replace missing values with convert datetimes to months, last convert to original datetimes:

df1.index = pd.to_datetime(df1.index, dayfirst=True)

df1 = df1.asfreq('d')
df1 = df1.set_index(df1.index.month).fillna(df2).set_index(df1.index)

Changed sample data for test:

print (df1)
            A  B  C  D  E
30/07/2021  1  4  3  8  9
01/09/2021  8  9  3  1  0
13/09/2021  8  9  0  9  3

print (df2)
   A  B  C  D  E
1  8  3  3  3  1
7  4  5  6  4  6
8  6  4  6  4  2
9  4  6  6  6  4
    
df1.index = pd.to_datetime(df1.index, dayfirst=True)

df1 = df1.asfreq('d')
df1 = df1.set_index(df1.index.month).fillna(df2).set_index(df1.index)

print (df1)
              A    B    C    D    E
2021-07-30  1.0  4.0  3.0  8.0  9.0
2021-07-31  4.0  5.0  6.0  4.0  6.0
2021-08-01  6.0  4.0  6.0  4.0  2.0
2021-08-02  6.0  4.0  6.0  4.0  2.0
2021-08-03  6.0  4.0  6.0  4.0  2.0
2021-08-04  6.0  4.0  6.0  4.0  2.0
2021-08-05  6.0  4.0  6.0  4.0  2.0
2021-08-06  6.0  4.0  6.0  4.0  2.0
2021-08-07  6.0  4.0  6.0  4.0  2.0
2021-08-08  6.0  4.0  6.0  4.0  2.0
2021-08-09  6.0  4.0  6.0  4.0  2.0
2021-08-10  6.0  4.0  6.0  4.0  2.0
2021-08-11  6.0  4.0  6.0  4.0  2.0
2021-08-12  6.0  4.0  6.0  4.0  2.0
2021-08-13  6.0  4.0  6.0  4.0  2.0
2021-08-14  6.0  4.0  6.0  4.0  2.0
2021-08-15  6.0  4.0  6.0  4.0  2.0
2021-08-16  6.0  4.0  6.0  4.0  2.0
2021-08-17  6.0  4.0  6.0  4.0  2.0
2021-08-18  6.0  4.0  6.0  4.0  2.0
2021-08-19  6.0  4.0  6.0  4.0  2.0
2021-08-20  6.0  4.0  6.0  4.0  2.0
2021-08-21  6.0  4.0  6.0  4.0  2.0
2021-08-22  6.0  4.0  6.0  4.0  2.0
2021-08-23  6.0  4.0  6.0  4.0  2.0
2021-08-24  6.0  4.0  6.0  4.0  2.0
2021-08-25  6.0  4.0  6.0  4.0  2.0
2021-08-26  6.0  4.0  6.0  4.0  2.0
2021-08-27  6.0  4.0  6.0  4.0  2.0
2021-08-28  6.0  4.0  6.0  4.0  2.0
2021-08-29  6.0  4.0  6.0  4.0  2.0
2021-08-30  6.0  4.0  6.0  4.0  2.0
2021-08-31  6.0  4.0  6.0  4.0  2.0
2021-09-01  8.0  9.0  3.0  1.0  0.0
2021-09-02  4.0  6.0  6.0  6.0  4.0
2021-09-03  4.0  6.0  6.0  6.0  4.0
2021-09-04  4.0  6.0  6.0  6.0  4.0
2021-09-05  4.0  6.0  6.0  6.0  4.0
2021-09-06  4.0  6.0  6.0  6.0  4.0
2021-09-07  4.0  6.0  6.0  6.0  4.0
2021-09-08  4.0  6.0  6.0  6.0  4.0
2021-09-09  4.0  6.0  6.0  6.0  4.0
2021-09-10  4.0  6.0  6.0  6.0  4.0
2021-09-11  4.0  6.0  6.0  6.0  4.0
2021-09-12  4.0  6.0  6.0  6.0  4.0
2021-09-13  8.0  9.0  0.0  9.0  3.0