how to complement Pandas daily time series with NAN value until the end of the year?

user_12131472 0
Viewed 52

This should be a common operation but I can't find any existing discussion

I have a dataframe having daily data from start of 2021 till today, looks like this

     From Country    XXX Day  Quantity
1461      Austria 2021-01-01     34.44
1462      Austria 2021-01-02     34.05
1463      Austria 2021-01-03     33.34
1464      Austria 2021-01-04     36.08
1465      Austria 2021-01-05     37.38
          ...        ...       ...
1746      Austria 2021-10-13     26.09
1747      Austria 2021-10-14     25.14
1748      Austria 2021-10-15     22.22
1749      Austria 2021-10-16     19.97
3211      Belgium 2021-01-01     40.10

I wish to complement the TS till year-end with empty value or NaN for each country to reach this view

     From Country    XXX Day  Quantity
1461      Austria 2021-01-01     34.44
1462      Austria 2021-01-02     34.05
1463      Austria 2021-01-03     33.34
1464      Austria 2021-01-04     36.08
1465      Austria 2021-01-05     37.38
          ...        ...       ...
1746      Austria 2021-10-13     26.09
1747      Austria 2021-10-14     25.14
1748      Austria 2021-10-15     22.22
1749      Austria 2021-10-16     19.97
          ...        ...       ...
1748      Austria 2021-10-17     
1749      Austria 2021-10-18     
          ...        ...       ...
1748      Austria 2021-12-30     
1749      Austria 2021-12-31
3211      Belgium 2021-01-01     40.10

How would you do?

Thanks

pandas time-series dataframe python
1 Answers

Use DataFrame.reindex by all combinations of Country column with dates:

df['XXX Day'] = pd.to_datetime(df['XXX Day'])

mux = pd.MultiIndex.from_product([df['From Country'].unique(),
                                 pd.date_range('2021-01-01', '2021-12-31')], 
                                 names=['From Country','XXX Day'])

df = df.set_index(['From Country', 'XXX Day']).reindex(mux).reset_index()
user_29010020
Related