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

You can try this link which uses Series.reindex: https://stackoverflow.com/questions/19324453/add-missing-dates-to-pandas-dataframe — blutab, Jul 24 '20 at 08:18
I think in the output the date after `2020-07-13` should be `2020-07-14`, Right? — Shubham Sharma, Jul 24 '20 at 08:18
Yes, right after `2020-07-13` there should be also `2020-07-14 kabel None None None` — Niviral, Jul 24 '20 at 08:20

score 1 · Accepted Answer · answered Jul 24 '20 at 08:22

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

1 Answers1