Reindex with multiindex and create empty dates

user_14114654 0
Viewed 34

How can I create empty rows from 7 days before 2016-01-01 going to January 2015 for each country? I tried reindexing. I need to retain the multiindex df

                   value
       date         
uk   2016-01-01       4.0
     2016-01-08       5.0
us   2016-01-01       1.0
     2016-01-08       1.5

Expected Output

                   value
       date   
uk   2015-02-02       NaN  
... 
     2016-01-01       4.0
     2016-01-08       5.0
us   2015-02-02       NaN
...
     2016-01-01       1.0
     2016-01-08       1.5
pandas python-3.x datetime python
1 Answers

I try modify previous answer for working with MultiIndex by MultiIndex.from_product for get same datetimes for each category in first level:

rng = (pd.date_range('2015-01-02', df.index.levels[1].min(), freq='7d')
         .union(df.index.get_level_values(1).unique()))

cat = df.index.levels[0]
df = df.reindex(pd.MultiIndex.from_product([cat, rng]))

print (df)
               value
a                   
uk 2015-01-02    NaN
   2015-01-09    NaN
   2015-01-16    NaN
   2015-01-23    NaN
   2015-01-30    NaN
             ...
us 2015-12-11    NaN
   2015-12-18    NaN
   2015-12-25    NaN
   2016-01-01    1.0
   2016-01-08    1.5

[108 rows x 1 columns]
user_29010020
Related