CODEWITHSUNDEEP
pythonpandas
Eksana Stasis
Eksana Stasis

Reputation: 183

Convert daily data in pandas dataframe to monthly data

My dataframe has daily stock data in it:

       Date       AAPL      NFLX       INTC  
0 2008-01-02  27.834286  3.764286  25.350000    
1 2008-01-03  27.847143  3.724286  24.670000    
2 2008-01-04  25.721428  3.515714  22.670000   
3 2008-01-07  25.377142  3.554286  22.879999    
4 2008-01-08  24.464285  3.328571  22.260000

I'd like to calculate monthly returns using the last day of each month in my df above. I'm guessing (after googling) that resample is the best way to select the last trading day of the month. But this doesn't seem to work:

df.set_index('Date')  
m1= df.resample('M')
print(m1)

get this error:

TypeError: Only valid with DatetimeIndex, TimedeltaIndex or PeriodIndex, but got an instance of 'Index'

So I think that means the set_index isn't working?

I've also tried:

df= df.reset_index().set_index('Date')  
m1= df.resample('M')
print(m1)

But I get the same error message as above. Thanks much for your help.

Upvotes: 6

Views: 33103

Answers (1)

DYZ
DYZ

Reputation: 57033

Your index is not a DatetimeIndex. But you can make it a DatetimeIndex:

df.set_index('Date', inplace=True)
df.index = pd.to_datetime(df.index)
df.resample('1M').mean()
#                 AAPL      NFLX    INTC
#Date                                   
#2008-01-31  26.248857  3.577429  23.566

Upvotes: 15

Related Questions