Pandas Re-fetch error: valid only with DatetimeIndex or PeriodIndex

Question

When using the panda resample function in a DataFrame to convert tick data to OHLCV, an oversampling error occurs.

How should we solve the error?

 data = pd.read_csv('tickdata.csv', header=None, names=['Timestamp','Price','Volume']).set_index('Timestamp') data.head() 

 # Resample data into 30min bins ticks = data.ix[:, ['Price', 'Volume']] bars = ticks.Price.resample('30min', how='ohlc') volumes = ticks.Volume.resample('30min', how='sum') 

This gives an error:

 TypeError: Only valid with DatetimeIndex or PeriodIndex 

Answer 1

Convert integer timestamps to index in DatetimeIndex:

 data.index = pd.to_datetime(data.index, unit='s') 

This interprets integers in seconds since the Age.

---

For example, given

 data = pd.DataFrame( {'Timestamp':[1313331280, 1313334917, 1313334917, 1313340309, 1313340309], 'Price': [10.4]*3 + [10.5]*2, 'Volume': [0.779, 0.101, 0.316, 0.150, 1.8]}) data = data.set_index(['Timestamp']) # Price Volume # Timestamp # 1313331280 10.4 0.779 # 1313334917 10.4 0.101 # 1313334917 10.4 0.316 # 1313340309 10.5 0.150 # 1313340309 10.5 1.800 data.index = pd.to_datetime(data.index, unit='s') 

gives

  Price Volume 2011-08-14 14:14:40 10.4 0.779 2011-08-14 15:15:17 10.4 0.101 2011-08-14 15:15:17 10.4 0.316 2011-08-14 16:45:09 10.5 0.150 2011-08-14 16:45:09 10.5 1.800 

Then

 ticks = data.ix[:, ['Price', 'Volume']] bars = ticks.Price.resample('30min', how='ohlc') volumes = ticks.Volume.resample('30min', how='sum') 

can calculate:

 In [368]: bars Out[368]: open high low close 2011-08-14 14:00:00 10.4 10.4 10.4 10.4 2011-08-14 14:30:00 NaN NaN NaN NaN 2011-08-14 15:00:00 10.4 10.4 10.4 10.4 2011-08-14 15:30:00 NaN NaN NaN NaN 2011-08-14 16:00:00 NaN NaN NaN NaN 2011-08-14 16:30:00 10.5 10.5 10.5 10.5 In [369]: volumes Out[369]: 2011-08-14 14:00:00 0.779 2011-08-14 14:30:00 NaN 2011-08-14 15:00:00 0.417 2011-08-14 15:30:00 NaN 2011-08-14 16:00:00 NaN 2011-08-14 16:30:00 1.950 Freq: 30T, Name: Volume, dtype: float64