1

I have a Pandas DataFrame with a daily DatetimeIndex. I am trying to apply the Resample method to sum the values into a monthly series like this:

>>> aggVols.resample('M',axis=1).sum()

But when I try this I get the error 

TypeError: Only valid with DatetimeIndex or PeriodIndex

I noticed that the frequency of the index of the object is not set (None). 

>>>aggVols.index
<class 'pandas.tseries.index.DatetimeIndex'>
[2016-01-04, ..., 2016-07-01]
Length: 130, Freq: None, Timezone: None

So I first set the frequency to daily (business day) and reset the index so that I can apply resample:

>>> aggVols    = aggVols.reindex(aggVols.asfreq('B').index)
>>> aggVols.index
<class 'pandas.tseries.index.DatetimeIndex'>
[2016-01-04, ..., 2016-07-01]
Length: 130, Freq: B, Timezone: None

But I am still getting the same error our of the resample function:

TypeError: Only valid with DatetimeIndex or PeriodIndex

What is wrong with the index? Why is it not valid? I get the same error if I set the frequency to D.

Thanks!

David
  • 327
  • 1
  • 3
  • 11

2 Answers2

0

Change

aggVols.resample('M',axis=1).sum()

to

aggVols.resample('M',axis=0).sum()

Your DatetimeIndex is on the rows (not the columns).

In general axis 0 is the rows, axis 1 is the columns, axis 2 is the height, and axes 3-N ... well they are thought about more abstractly.

See the "along an axis" section of the NumPy docs.

Alex
  • 18,484
  • 8
  • 60
  • 80
  • Thanks for the reply. You are right, DatetimeIndex is for the rows (not the columns). So I might be indeed specifying the wrong axis. But what you suggest give me a single number per colum. It is not producing a monthly series. '>>> aggVols.resample('M',axis=0).sum() CMX 114237.608293 LME 1425516.887879 SHF 50861180.513820 dtype: float64' – David Jul 22 '16 at 15:57
  • @David you want to sum across the rows of the DataFrame? – Alex Jul 22 '16 at 15:59
  • @David it helps if you post a sample DataFrame and your desired result. – Alex Jul 22 '16 at 15:59
  • Answered my own question above. Thanks anyway for the help. – David Jul 22 '16 at 16:03
0

Got it in the end. Was using the method the wrong way with the operation at the end, as if it was a series. The right code is:

aggVols.resample('M',axis=0,how=sum)
David
  • 327
  • 1
  • 3
  • 11