python - Convert daily data in pandas dataframe to monthly data

Question

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.

Answer 1

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