python pandas .asfreq() function giving back NaN

user_18526979 0
Viewed 308

So i was trying to change my data to a frequency from monthly for better plot. my code is:

ice_cream_interest = pd.read_excel('book1.xlsx')
ice_cream_interest.set_index('Month', inplace= True)
ice_cream_interest = ice_cream_interest.asfreq(pd.infer_freq(ice_cream_interest.index))

with the data from ice_cream_interest itself are like this:

Month   interest
0   2004-01 20
1   2004-02 21
2   2004-03 22
3   2004-04 25
4   2004-05 29

But after i ran my code, it gives :

    interest
Month   
2004-01-01  NaN
2004-02-01  NaN
2004-03-01  NaN
2004-04-01  NaN
2004-05-01  NaN

so i was wondering why the asfreq giving back a NaN, and how to fix it. Thank you!

pandas dataframe nan python-3.x python
1 Answers

The asfreq documentation specifies :

The values corresponding to any timesteps in the new index which were not present in the original index will be null (NaN), unless a method for filling such unknowns is provided (see the method parameter below).

As you change from month periods to first day of month, you change the indices and lose all the data.

If you need to resample, rather use the resample method.

However, here you could just convert your months to dates:

ice_cream_interest = (ice_cream_interest
 .assign(Date=lambda d: pd.to_datetime(d['Month']))
 .set_index('Date')
 .drop(columns='Month')
 )

Output:

            interest
Date                
2004-01-01        20
2004-02-01        21
2004-03-01        22
2004-04-01        25
2004-05-01        29
user_163434640
Related