How do I round datetime column to nearest quarter hour

Question

I have loaded a data file into a Python pandas dataframe. I has a datetime column of the format 2015-07-18 13:53:33.280.

What I need to do is create a new column that rounds this out to its nearest quarter hour. So, the date above will be rounded to 2015-07-18 13:45:00.000.

How do I do this in pandas? I tried using the solution from here, but get an 'Series' object has no attribute 'year' error.

Hairy

Answer 1

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)

Granuloma

I believe this answer is actually incorrect - since it will always round down to the quarter hour before it, instead of to the nearest quarter hour. – Beet

@ThomasMo updated the answer for that as well. Previous behavior would have been similar to floor behavior – Granuloma

Can We pass that to a function, like, def secondBackAdjuster(numSec): if numSec > 30: numSec = 30 else: numSec = 0 numSec , # Applog['TimeCreatedAdj'] = secondBackAdjuster(Applog['TimeCreatedAdj']) – Utter

The answer is still incorrect. When the time is 7:59, this will try to do datetime(y,m,d,7,60), and fail because 60 is invalid value for minute. @Begrime has a much better answer and that should be accepted. – Belford

Answer 2

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

Begrime

If one want to round to the closest time below or above it is possible to use floor and ceil, respectively. See code – Yu

Answer 3

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

Renettarenew

Answer 4

Anand S Kumar's answer doesn't round to the nearest quarter hour, it cuts off the minutes to the nearest 15 minutes below it.

Actually, in your example 2015-07-18 13:53:33.280 should round to 2015-07-18 14:00:00.000 since 53:33.280 is closer to 60 minutes than 45 minutes.

I found an more robust answer for rounding in this post.

For your situation this should work:

import datetime

def round_time(time, round_to):
    """roundTo is the number of minutes to round to"""
    rounded = time + datetime.timedelta(minutes=round_to/2.)
    rounded -= datetime.timedelta(minutes=rounded.minute % round_to,
                                  seconds=rounded.second,
                                  microseconds=rounded.microsecond)
    return rounded

dt['dtcolumn'] = df['dtcolumn'].apply(lambda x: round_time(x))

Posthaste

Answer 5

If you're fine with rounding down to the nearest quarter-hour, doing .dt.to_freq('15min') would also work. Note that it would change the dtype, which might be desired.

Better