Pandas reindex to fill date index

Question

How to reindex this df to fill missing dates.
           query    min max mean
DATE                
2020-07-04  kabel   573 838 666.547619
2020-07-13  kabel   681 681 681.000000
2020-07-15  kabel   583 738 654.565217

As output I want to get
               query    min max mean
DATE                
2020-07-04  kabel   573 838 666.547619
2020-07-05  kabel   None None None
...         ...     ...  ...  ...
2020-07-12  kabel   None None None
2020-07-13  kabel   681 681 681.000000
2020-07-15  kabel   583 738 654.565217

Shubham Sharma · Answer

Using pd.to_datetime convert the index of dataframe to DatetimeIndex, then use DataFrame.asfreq to reindex the dataframe with daily frequency and use Series.ffill to forward fill the values in query column:
df.index = pd.to_datetime(df.index)
df = df.asfreq('D').assign(query=lambda x: x['query'].ffill())

Result:
print(df)
            query    min    max        mean
DATE                                       
2020-07-04  kabel  573.0  838.0  666.547619
2020-07-05  kabel    NaN    NaN         NaN
2020-07-06  kabel    NaN    NaN         NaN
2020-07-07  kabel    NaN    NaN         NaN
2020-07-08  kabel    NaN    NaN         NaN
2020-07-09  kabel    NaN    NaN         NaN
2020-07-10  kabel    NaN    NaN         NaN
2020-07-11  kabel    NaN    NaN         NaN
2020-07-12  kabel    NaN    NaN         NaN
2020-07-13  kabel  681.0  681.0  681.000000
2020-07-14  kabel    NaN    NaN         NaN
2020-07-15  kabel  583.0  738.0  654.565217

Pandas reindex to fill date index

One Answer

Add your own answers!

Ask a Question