How do I round datetime column to nearest quarter hour
You can use round(freq)
. There is also a shortcut column.dt
for datetime functions access (as @laurens-koppenol suggests).
Here's one-liner:
df['old column'].dt.round('15min')
String aliases for valid frequencies can be found here. Full working example:
In [1]: import pandas as pd
In [2]: df = pd.DataFrame([pd.Timestamp('2015-07-18 13:53:33.280'),
pd.Timestamp('2015-07-18 13:33:33.330')],
columns=['old column'])
In [3]: df['new column']=df['old column'].dt.round('15min')
In [4]: df
Out[4]:
old column new column
0 2015-07-18 13:53:33.280 2015-07-18 14:00:00
1 2015-07-18 13:33:33.330 2015-07-18 13:30:00
This looks a little nicer
column.dt.
allows the datetime functions for datetime columns, like column.str.
does for string-like columns
datetime-like properties API reference
import pandas as pd
# test df
df = pd.DataFrame([{'old_column':pd.Timestamp('2015-07-18 13:53:33.280')}])
df['new_column'] = df['old_column'].dt.round('15min')
df
Assuming that your series is made up of datetime
objects, You need to use Series.apply
. Example -
import datetime
df['<column>'] = df['<column>'].apply(lambda dt: datetime.datetime(dt.year, dt.month, dt.day, dt.hour,15*(dt.minute // 15)))
The above example to always round to the previous quarter hour (behavior similar to floor function).
EDIT
To round to the correct quarter hour (as in , if its 7 mins 30 seconds past previous quarter, to show the next quarter) . We can use the below example -
import datetime
df['<column>'] = df['<column>'].apply(lambda dt: datetime.datetime(dt.year, dt.month, dt.day, dt.hour,15*round((float(dt.minute) + float(dt.second)/60) / 15)))
The above would only take the latest seconds into consideration , if you want the millisecond/microsecond into consideration , you can add that to the above equation as - (float(dt.minute) + float(dt.second)/60 + float(dt.microsecond)/60000000)