Cfrgtkky

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.

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)

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

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))