0

I have a multiindex dataframe with indexes date and id and I want to resample it as BM frequency. I am using the following code:

df.set_index('date').groupby('id').resample('BM').asfreq().drop('id',axis=1)

But for a given id it resampled till the last non-nan value and after that for this id I don't have a date, for example if for id A I have value for 2014 and 2019, I have a monthly index from 2014 to 2019 but nothing after 2019. Could you please help?

Ynjxsjmh
  • 28,441
  • 6
  • 34
  • 52
Ксения Цочева
  • 19
  • 6
  • 1
    `but nothing after 2019` - what should be there? What is logic? Without input data/ expected ouput hard to know – jezrael Mar 13 '23 at 11:36
  • Imagine I have one value 2014 and one value 2019 for a given id. When I do the resampling, I have `Nan` from 2014 till 2019 for this id. I expect to have those nans after 2019 till now also the same way but I don't have this index after 2019. – Ксения Цочева Mar 13 '23 at 11:44

1 Answers1

0

If need add missing datetimes up to actual date use DataFrame.reindex:

df1 = df.set_index('date').groupby('id').resample('BM').asfreq().drop('id',axis=1)

mux = pd.MultiIndex.from_product([df['id'].unique(),
                                  pd.date_range(df['date'].min(), pd.Timestamp('now'), 
                                                freq='BM')],
                                  names=['id','date'])


df = df1.reindex(mux)
jezrael
  • 822,522
  • 95
  • 1,334
  • 1,252