Center datetimes of resampled time series

user_912307 0
Viewed 1414

When I resample a Pandas time series to reduce the number of data points, the timestamp of each resulting datapoint is at the start of each resampling bin. When overplotting graphs with different resampling rates, this causes an apparent shift of the data. How can I "center" the timestamp of the resampled data in its bin, whatever the resample rate?

What I get now is (when resampling to one hour):

In [12]: d_r.head()
Out[12]: 
2017-01-01 00:00:00    0.330567
2017-01-01 01:00:00    0.846968
2017-01-01 02:00:00    0.965027
2017-01-01 03:00:00    0.629218
2017-01-01 04:00:00   -0.002522
Freq: H, dtype: float64

what I want is:

In [12]: d_r.head()
Out[12]: 
2017-01-01 00:30:00    0.330567
2017-01-01 01:30:00    0.846968
2017-01-01 02:30:00    0.965027
2017-01-01 03:30:00    0.629218
2017-01-01 04:30:00   -0.002522
Freq: H, dtype: float64

MWE showing apprarent shift:

#!/usr/bin/env python3
Minimal working example:

import pandas as pd
from matplotlib import pyplot as plt
import numpy as np
import seaborn
seaborn.set()

plt.ion()

# sample data
t = pd.date_range('2017-01-01 00:00', '2017-01-01 10:00', freq='1min')
d = pd.Series(np.sin(np.linspace(0, 7, len(t))), index=t)


d_r = d.resample('1h').mean()

d.plot()
d_r.plot()

Apparent shift of resampled data

pandas python
4 Answers

I don't know how to use the midpoint in general. There is the label-parameter, but that only has the options right and left. However, in a concrete case as this you can explicitly offset the resampled timestamp with the loffset-parameter:

d.resample('1h', loffset='30min').mean()

(edit: Use 30min instead of 30T as that is more readable: http://pandas.pydata.org/pandas-docs/stable/timeseries.html#offset-aliases)

user_54412770

The loffset keyword argument seems to be deprecated soon.

In my opinion, the nicest way to do it that I know of is the following:

d_r = d.shift(0.5, freq='1h').resample('1h').mean()

Compared to using the loffset keyword this has the advantage that the resulting timestamps are at full hours.

user_134761680

How about just adding 30 minutes with timedelta to index?

df.index = df.index + datetime.timedelta(minutes=30)
user_73863320

matthme's solution is a good one, but it doesn't solve the main problem with resample, i.e. the time tags are always separated by the rule that is chosen for resampling (1h in your case), and this can lead to a wrong time tag at the beginning and/or end of your time series if the duration is not an integer multiple of the rule.

The best thing you can do is average your time series and use the result as the time tag (i.e. the index of your DataFrame). Unfortunately, the resample method can't operate on datetime objects, so you have to transform it to timestamp, apply the average to the resampled DataFrame, and convert the timestamp back to datetime. This will automatically center the time tags inside your time binning interval and it will adjust the tags at the beginning and end of the time series. No need for shifting in this case.

A working example:

import pandas as pd
from matplotlib import pyplot as plt
import numpy as np


t = pd.date_range('2017-01-01 00:00', '2017-01-01 10:00', freq='1min')
timestamp = t.astype('int64') // 10**9  # covert datetime to timestamp in seconds
d = pd.DataFrame({'datetime': t,
                  'timestamp': timestamp,
                  'd': np.sin(np.linspace(0, 7, len(t)))}, 
                 index=t)
t_avg = '1h'

d_r = d.shift(0.5, freq=t_avg).resample(t_avg).mean()

d_r2 = d.resample(t_avg).mean()
d_r2.index = pd.to_datetime(d_r2['timestamp'], unit='s')


fig, ax = plt.subplots()
ax.plot(d['datetime'], d['d'], label='unsampled')
ax.plot(d_r.index, d_r['d'], 'o', label='shifted resample')
ax.plot(d_r2.index, d_r2['d'], 'D', label='time average resample')
plt.legend()
fig.autofmt_xdate()

enter image description here

user_104305350
Related