0

I tried to convert yearly data to monthly, starting from 2001 to 2020, however 2001 data was not converted to monthly.

The original dataset looks like:

0 2001-12-31 16.5648 4.9887 11.1706 ... 6.4568 7.9525 7.7998 11.2796

1 2002-12-31 15.3589 14.1851 11.4080 ... 7.7080 7.6465 8.1067 11.6526

2 2003-12-31 10.0559 15.1041 11.5154 ... 8.6358 9.0530 9.4951 11.8169

3 2004-12-31 13.4166 15.6389 12.9674 ... 9.9278 10.7078 11.1832 13.0509

4 2005-12-31 13.3857 16.7083 8.9411 ... 11.0563 12.7320 11.9903 13.0580

What I tried:

df4['Date']=pd.to_datetime(df4['Date'])

df4 = df4.set_index('Date').resample('M').bfill().reset_index()

The output:

2001-12-31  16.5648   4.9887  11.1706  ...   7.9525   7.7998  11.2796

2002-01-31  15.3589  14.1851  11.4080  ...   7.6465   8.1067  11.6526

2002-02-28  15.3589  14.1851  11.4080  ...   7.6465   8.1067  11.6526

2002-03-31  15.3589  14.1851  11.4080  ...   7.6465   8.1067  11.6526

2002-04-30  15.3589  14.1851  11.4080  ...   7.6465   8.1067  11.6526

Can anybody tell me what I missed? Thank you

Puckpicker
  • 13
  • 5
  • Please try to provide a brief statement that puts your problem in context. Something like "I am working with data obtained from a climate data base, and now need to extract information using the Python "date time" string. Here is what I tried : – Donna Apr 09 '22 at 07:17
  • Looks like the output is monthly data. Now I'm wondering what I've missed :D – Niko Föhr Apr 09 '22 at 08:51
  • sorry for the confusion, the df4 is yearly book value of stocks and I tried to converted it to monthly, for each month, the book value is same as the yearly book value. – Puckpicker Apr 09 '22 at 13:51

1 Answers1

0

What you're doing is resampling the data from yearly to monthly and then calling bfill to backwards fill the data gaps with constant value.

If you would like to interpolate the gaps, you should call resample('M').interpolate() instead.

Niko Föhr
  • 28,336
  • 10
  • 93
  • 96
  • thank you very much, I guess I need set the starting date to 2001-1-31 and then backwards fill – Puckpicker Apr 11 '22 at 00:58
  • If you do `bfill` or `fillna`, all of the months will have the same value that the year has. You probably want to just `interpolate`. – Niko Föhr Apr 11 '22 at 07:18