Pandas: Timestamp index rounding to the nearest 5th minute
The round_to_5min(t)
solution using timedelta
arithmetic is correct but complicated and very slow. Instead make use of the nice Timstamp
in pandas:
import numpy as np
import pandas as pd
ns5min=5*60*1000000000 # 5 minutes in nanoseconds
pd.to_datetime(((df.index.astype(np.int64) // ns5min + 1 ) * ns5min))
Let's compare the speed:
rng = pd.date_range('1/1/2014', '1/2/2014', freq='S')
print len(rng)
# 86401
# ipython %timeit
%timeit pd.to_datetime(((rng.astype(np.int64) // ns5min + 1 ) * ns5min))
# 1000 loops, best of 3: 1.01 ms per loop
%timeit rng.map(round_to_5min)
# 1 loops, best of 3: 1.03 s per loop
Just about 1000 times faster!
You can try something like this:
def round_to_5min(t):
delta = datetime.timedelta(minutes=t.minute%5,
seconds=t.second,
microseconds=t.microsecond)
t -= delta
if delta > datetime.timedelta(0):
t += datetime.timedelta(minutes=5)
return t
df['new_col'] = df.index.map(round_to_5min)
One could easily use the round function of pandas
df["timestamp_column"].dt.round("5min")
Check here for more details