How to count duration between different overlapping occurrences in a csv file - pandas?

user_18797598 0
Viewed 65

I have a following DataFrame from a csv file.

Time User Description
10/22/2021 10:58:08 AM User A Open document
10/22/2021 11:17:54 AM User B Open document
10/22/2021 11:18:41 AM User C Open document
10/22/2021 11:19:49 AM User C Close document
10/22/2021 11:23:12 AM User B Close document
10/22/2021 11:23:24 AM User A Close document
10/24/2021 3:50:12 PM User A Open document
10/24/2021 3:50:18 PM User B Open document
10/24/2021 3:51:02 PM User A Close document
10/24/2021 3:51:07 PM User A Open document
10/24/2021 3:51:23 PM User C Open document
10/24/2021 3:58:19 PM User C Close document
10/24/2021 3:58:36 PM User A Close document
10/24/2021 3:58:37 PM User A Open document
10/24/2021 4:15:55 PM User B Close document
10/24/2021 4:17:30 PM User A Close document
10/24/2021 4:17:31 PM User C Open document
10/24/2021 4:19:38 PM User C Close document

The desired output would be how long did each user work alone (strictly just by him/herself) and how long did they worked together in different combinations. Work is considered as time between the "Open document" and "Close document". Expected output is as follows:

Time_spent Users
x(hours) : y(minutes) : z(seconds) User A
x(hours) : y(minutes) : z(seconds) User B
x(hours) : y(minutes) : z(seconds) User C
x(hours) : y(minutes) : z(seconds) Users A and B
x(hours) : y(minutes) : z(seconds) Users A and C
x(hours) : y(minutes) : z(seconds) Users B and C
x(hours) : y(minutes) : z(seconds) Users A, B and C

I'm not sure how to approach this problem. Any help is greatly appreciated.

pandas python
1 Answers

This direction is very memory inefficient, and haven't tested the results (it's complicated), but let me know what you think about it:

A. create a df where every row contains the open and close of every user:

df['Time'] = pd.to_datetime(df['Time'])
df.sort_values(['User', 'Time'], inplace=True)

close_and_open_df = \
pd.concat([df[df['Description']=='Open document'].reset_index(drop=True),
           df[df['Description']=='Close document'].reset_index(drop=True)], axis=1) \
           [['Time', 'User']].iloc[:,:-1]
close_and_open_df.columns = ['open', 'close', 'user']

B. add a column of an array of all the seconds between open and close

close_and_open_df['ts_array'] = \
[pd.date_range(start=x[0], end=x[1], freq='s') 
 for x in zip(close_and_open_df['open'], close_and_open_df['close'])]

C. explode that column to create a row per user per second

all_ts_df = close_and_open_df.explode('ts_array')

D. group by second to get all users in that second:

grouped_1 = \
pd.DataFrame(all_ts_df.groupby('ts_array').apply(lambda df: tuple(sorted(list(df['user'].unique()))))).reset_index()

E. group by combination to get number of seconds:

grouped_1.groupby(0).size()
user_49089000
Related