CODEWITHSUNDEEP
pythonpandasdatetimepandas-groupbypython-datetime
Nick Duddy
Nick Duddy

Reputation: 1000

Datetime dtype is Object not Datetime

I'm trying to mean groupby a timestamp. First I've had to convert the time (string) that I've got into a datetime. After converting it datetime I've noticed that despite giving the specific format that pandas adds a date, I don't need a date. I'm working to remove this and keep only time object but I've not be successful. Anything that I do to remove the date returns the dtype to an Object which I can't preform a groupby on.

Example Data:

https://miratrix.co.uk/          00:01:55
https://miratrix.co.uk/          00:02:02
https://miratrix.co.uk/          00:02:45
https://miratrix.co.uk/          00:01:22
https://miratrix.co.uk/          00:02:02
https://miratrix.co.uk/app-marketing-agency/          00:02:23
https://miratrix.co.uk/get-in-touch/          00:02:26
https://miratrix.co.uk/get-in-touch/          00:00:18
https://miratrix.co.uk/get-in-touch/          00:02:37
https://miratrix.co.uk/          00:00:31
https://miratrix.co.uk/          00:02:00
https://miratrix.co.uk/app-store-optimization-...          00:02:25
https://miratrix.co.uk/          00:03:36
https://miratrix.co.uk/app-marketing-agency/          00:02:09
https://miratrix.co.uk/get-in-touch/          00:02:14
https://?page_id=16198/          00:00:15
https://videos/channel/UCAQfRNzXGD4BQICkO1KQZUA/          00:09:07
https://miratrix.co.uk/get-in-touch/          00:01:39
https://miratrix.co.uk/app-marketing-agency/          00:01:07

What I've tried so far

*Returned Object*
ga_organic['NEW Avg. Time on Page'] = pd.to_datetime(ga_organic['Avg. Time on Page'], format="%H:%M:%S").dt.time

*Returned Datetime but when trying to sample only time it returned an object*
ga_organic['NEW Avg. Time on Page'] = ga_organic['Avg. Time on Page'].astype('datetime64[ns]')

ga_organic['NEW Avg. Time on Page'].dt.time

I have a feeling there is something about datetime that I'm not aware of and that's why I'm having this problem. Any help or direction is welcome.

####Update####

Thanks for ALollz for providing the solution to the timestamp.

ga_organic['NEW Avg. Time on Page'] = pd.to_timedelta(ga_organic['Avg. Time on Page'])

However I'm still getting the same errror when using GroupBy using this method:

avg_time = ga_organic.groupby(ga_organic.index)['NEW Avg. Time on Page'].mean()

ERROR: "DataError: No numeric types to aggregate"

Is there a specific function for dealing with grouping datetimes?

Upvotes: 1

Views: 1235

Answers (1)

ALollz
ALollz

Reputation: 59569

seems groupby doesn't recognize timedelta64 as a numeric type. There are several workarounds, either with numeric_only=False or working with total_seconds.

import pandas as pd

#df = pd.read_clipboard(header=None)
#df[1] = pd.to_timedelta(df[1])

df.groupby(df.index//2)[1].mean()
#DataError: No numeric types to aggregate

# To fix pass `numeric_only=False`
df.groupby(df.index//2)[1].mean(numeric_only=False)
#0   00:01:58.500000
#1   00:02:03.500000
#2   00:02:12.500000
#3          00:01:22
#4          00:01:34
#5   00:02:12.500000
#6   00:02:52.500000
#7   00:01:14.500000
#8          00:05:23
#9          00:01:07
#Name: 1, dtype: timedelta64[ns]

Using simple float values with .total_seconds:

df[1] = df[1].dt.total_seconds()

df.groupby(df.index//2)[1].mean()
#0    118.5
#1    123.5
#2    132.5
#3     82.0
#4     94.0
#5    132.5
#6    172.5
#7     74.5
#8    323.0
#9     67.0
#Name: 1, dtype: float64

This can be converted back with pd.to_timedelta specifying unit='s'

Upvotes: 2

Related Questions