2

I have a column of datatimes in the format: 2015-06-25 03:29:58 which I believe is in datetime64. I would like to round them to the nearest ten minutes.

i.e

2015-06-25 03:29:58  =   2015-06-25 03:30:00
2015-06-25 03:24:58  =   2015-06-25 03:20:00
2015-06-25 03:59:58  =   2015-06-25 04:00:00

I have looked all over for the answer for this problem, there are a few threads and solutions for rounding time, such as this one: How do I round datetime column to nearest quarter hour

However this method can only round down, not round up.

I have also seen other solutions, but cannot work out how to apply them to a datetime in a dataframe.

I have tried many different versions of:

from pandas import * 
import datetime

rounded_ten = lambda dt: datetime.datetime(dt.year, dt.month, dt.day,(dt.hour), 10*(round(dt.minute/10)))

dataframe1['Device Time'] = dataframe1['Device Time'].apply(rounded_ten)

However this does not work because when you round 55 upwards it gives the answer of 60. Which is not acceptable within this format. I wanted to apply some if statements, however I could not understand how to apply them to this format.

Any help would be greatly appreciated.

Community
  • 1
  • 1
Lboro Vicki
  • 45
  • 1
  • 6

2 Answers2

2

Try this:

rounded_ten = lambda t: t.replace(minute=t.minute/10*10).replace(second=0)+pd.Timedelta('10 minutes')

I guess I misunderstood the question at first. This should work:

import pandas as pd

def rounded_ten(t):
    ''' add 5 minutes to the date first and then apply mathematical floor to the minute part.
    It's easier to apply mathematical floor operation on date parts instead of rounding because for example you cannot round up 58 seconds to 60 seconds but you can always apply floor and stay within the range.
    '''
    t=t+pd.Timedelta('5 minutes') 
    return t.replace(minute=t.minute//10*10).replace(second=0)

or if you want to use a one liner:

rounded_ten = lambda t: (t+pd.Timedelta('5 minutes')).replace(minute=(t+pd.Timedelta('5 minutes')).minute//10*10).replace(second=0)
burhan
  • 924
  • 4
  • 11
  • I am getting an error on pd: NameError: name 'pd' is not defined. Should I have something else imported, or define something else? – Lboro Vicki Dec 02 '15 at 23:10
  • I import pandas like this: `import pandas as pd` I think you can just remove `pd`'s – burhan Dec 02 '15 at 23:30
  • I really appreciate your time writing this but I currently cannot get it to work. I am getting an error: 'integer argument expected, got float'. I have tried to edit the code to create integers but it is not working. Also, would you mind telling me a little more about what the code does for my learning? – Lboro Vicki Dec 03 '15 at 13:56
  • If you get that error, it probably means you are either using Python 3.xx or you import division function i.e `from __future__ import division`. I'm using Python 2.xx. In Python 3 and above, division operator behaves different. See this: https://www.python.org/dev/peps/pep-0238/. I updated my answer, it shouldn't give that error now. – burhan Dec 04 '15 at 16:51
1

How do I round datetime column to nearest quarter hour
However this method can only round down, not round up.

You could combine it with a formula from Rounding up to nearest 30 minutes in python:

from datetime import timedelta

delta = timedelta(minutes=10)
df['<column>'] = df['<column>'].apply(lambda dt: ceil_dt(dt, delta))
Community
  • 1
  • 1
jfs
  • 399,953
  • 195
  • 994
  • 1,670