python - Pandas dataset: get time spent

Question

I'm stuck at wrapping my head around a puzzle I'm trying to solve in pandas. Normally I use Excel to fix this in a very manual way, but I wanted to find a pandas way.

What I have is a table containing start and stop times from workers (small excerpt):

Name	action	date	time
Adam	in	day1	08:00
Bert	in	day1	08:09
Chrissy	in	day1	09:00
Bert	out	day1	11:30
Adam	out	day1	12:00
Bert	in	day1	12:00
Chrissy	out	day1	18:00
Earl	in	day1	18:00
Earl	out	day1	23:59
Earl	in	day2	09:00
Bert	in	day2	09:01
Chrissy	in	day2	10:00
Bert	out	day2	10:11
Earl	out	day2	10:12
Bert	in	day2	10:15
Chrissy	out	day2	19:00

question from:https://stackoverflow.com/questions/66067839/pandas-dataset-get-time-spent

Answer 1

Something along this line:

# we work with timedelta type
df['time'] = pd.to_timedelta(df['time']+':00')

# we want to label the `in` and `out` in pairs
df['no_action'] = df.sort_values('time').groupby(['Name','date','action']).cumcount()

# pivot table so `in`, `out` pairs are on the same row
actions = df.pivot(index=['Name','date', 'no_action'], columns='action', values='time')

# differences between `in` and `out`
actions['diff'] = actions['out'] - actions['in']

# finally sum over `Name` and `date` then pivot
actions['diff'].sum(level=['Name','date']).unstack('date')

Output:

date               day1            day2 
Name                                    
Adam     0 days 04:00:00             NaT
Bert     0 days 03:21:00 0 days 01:10:00
Chrissy  0 days 09:00:00 0 days 09:00:00
Earl     0 days 05:59:00 0 days 01:12:00

Note: this doesn't count the in and out pairs that span over midnight.