CODEWITHSUNDEEP
pythonpandasgroup-bydataframe
haccks
haccks

Reputation: 106102

How to group a data frame by a time interval in pandas?

I have a data frame df 

            Date        Mobile_No      Amount            Time    .....
121526  2014-12-24        739637       200.00           9:44:00
121529  2014-12-28        199002       500.00           9:49:44
121531  2014-12-10        813770       100.00           9:50:41
121536  2014-12-09        178795       100.00           9:52:15
121537  2014-12-09        178795       100.00           9:52:24

having Date and Time of type datetime64 and object. I need to group this data frame by time interval of 5 minutes and Mobile_No. My expected output is the last two rows should be counted as one (Same Mobile_No and time interval is less than 5 minutes).

Is there any way to achieve this?

First I thought to combine Date and Time column and make timestamp and then use it as index and apply pd.TimeGrouper(), but this doesn't seem to work

>>>import datetime as dt
>>>import pandas as pd
...

>>> df.apply(lambda x: dt.datetime.combine(x['Date'], dt.time(x['Time'])), axis=1)

gives the error 

'an integer is required', u'occurred at index 121526'

Upvotes: 0

Views: 666

Answers (1)

Chris
Chris

Reputation: 967

Can you not convert to string, concat the strings and parse the format in to_datetime if you are having issues:

df['Time']=df['Time'].astype(str)
df['Date']=df['Date'].astype(str)
df['Timestamp'] = df['Date'] +' ' + df['Time']
df.index = pd.to_datetime(df['Timestamp'], format='%Y/%m/%d %H:%M:%S')

from there you can resample or us pd.Grouper as required.

Upvotes: 1

Related Questions