Multi-index resampling in pandas

Question

I have a datasets of objects identified by IDs that move every ones in a while: id, date of when it moved, place where it moved. I have to resample this to a monthly level. This can be done with the following code:

           place
id date
0  2019-01    CH
   2019-06    IT
   2019-07    US
1  2017-03    US
   2018-12    IT
   2019-07    US
...

df.set_index('date',inplace=True)
df2 = df.groupby('id').resample('M').ffill()  

The result is the following:

           place
id date
0  2019-01   0    CH
   2019-02   0    CH
   2019-03   0    CH
   2019-04   0    CH
   2019-05   0    CH
   2019-06   0    IT
   2019-07   0    US
1  2017-03   1    US
   2017-04   1    US
   2017-05   1    US
   2017-06   1    US
   2017-07   1    US
...

This is almost what I need! With the exception that, as you can see, since the resampling is done by groups the start and end date are not the same for every ID while I'd need to add the missing months from and up to given dates (say the beginning and end of the year). Any clue how to achieve this?

Sorry if this is a duplicate. I've seens many similar questions but none that answers this variant in specific.

Answer 1

You can do with

df.unstack().ffill(1).stack()