timedelta unit error and No axis named Date for object type DataFrame

user_17388934 0
Viewed 102

I have a dataframe which looks like below:

ID Date Time
1 20210501 15:09:34
1 20210501 15:19:34
2 20210501 17:58:03
2 20210501 17:52:30
2 20210501 17:52:28

I need to get a new column say Duration which should give me the difference of time on a date grouped by ID. e.g.

ID Date Duration
1 20210501 00:10:00
2 20210501 02:06:02

I converted Time to timedelta using below code however, it gives me

"ValueError: unit must not be specified if the input contains a str"

Code to convert Time to timedelta type:

data['Time'] = pd.to_timedelta(data['Creation Time'],unit='h')

Code to get expected dataframe is:

data['duration'] = data.sort_values('ID,'Date','Time').groupby(['ID','Date'])['Time'].diff()

Error:

ValueError: No axis named Creation Date for object type DataFrame

Please let me know how can I solve this and where I'm wrong.

pandas timedelta python
1 Answers

First, we need a datetime dtype column to calculate the durations, so make a datetime column:

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

Then we can sort by ID and datetime to calculate all durations for each ID/Date:

df['Duration'] = df.sort_values(['ID', 'Datetime']).groupby(['ID', 'Date'])['Datetime'].diff()

Now we simply need to groupby ID and Date and sum the durations:

df.groupby(['ID', 'Date']).sum().drop(columns=['Time'])

                   Duration
ID Date                    
1  20210501 0 days 00:10:00
2  20210501 0 days 00:05:35
user_101974180
Related