Grouping dataframe rows by start/end timestamps

Question

I have an application log I am pulling into a pandas dataframe, parsing various pieces of each event into separate columns, that is similar to this:

data1 = {'timestamp': ['01-01-2021 12:00:00','01-01-2021 12:01:00','01-01-2021 12:02:00','01-01-2021 12:03:00','01-01-2021 12:04:00','01-01-2021 12:05:00','01-01-2021 12:06:00','01-01-2021 12:07:00','01-01-2021 12:08:00','01-01-2021 12:09:00','01-01-2021 12:10:00','01-01-2021 12:11:00','01-01-2021 12:12:00','01-01-2021 12:13:00','01-01-2021 12:14:00'],
    'event':     ['start','x','y','start','z','end','x','end','start','x','end','start','q','end','start'],
    'account':   ['bob','bob','bob','jane','bob','bob','jane','jane','todd','todd','todd','bob','bob','bob','ned'] }
df1 = pd.DataFrame(data1)
print(df1)

    timestamp            event     account
0   01-01-2021 12:00:00  start     bob
1   01-01-2021 12:01:00      x     bob
2   01-01-2021 12:02:00      y     bob
3   01-01-2021 12:03:00  start    jane
4   01-01-2021 12:04:00      z     bob
5   01-01-2021 12:05:00    end     bob
6   01-01-2021 12:06:00      x    jane
7   01-01-2021 12:07:00    end    jane
8   01-01-2021 12:08:00  start    todd
9   01-01-2021 12:09:00      x    todd
10  01-01-2021 12:10:00    end    todd
11  01-01-2021 12:11:00  start     bob
12  01-01-2021 12:12:00      q     bob
13  01-01-2021 12:13:00    end     bob
14  01-01-2021 12:14:00  start     ned

Pretty striaght forward log. The timestamps are strings but can easily be converted to datetime objects if needed. The log lists various actions performed by the user, but what I am interested in doing is generating a report of the account sessions by user. Something like this:

     account  start                end
0    bob      01-01-2021 12:00:00  01-01-2021 12:05:00
1    jane     01-01-2021 12:03:00  01-01-2021 12:07:00
2    todd     01-01-2021 12:08:00  01-01-2021 12:10:00
3    bob      01-01-2021 12:11:00  01-01-2021 12:13:00
4    ned      01-01-2021 12:14:00                  NaN

It's easy enough to group by start and end times, the problem thing I can't figure out is how to do this when a user has multiple sessions in the time period I am looking at. In the pseudo log data above Bob has 2 sessions opened, but could in theory have opened 100 sessions depending on the date range of the data I'm looking at. The application in question does restrict users to 1 session at a time, so I shouldn't see the same account with 2 sessions open at the same time.

How do I group the session start/end times together into 2 columns like this by account when the account could have multiple sessions in the data?

Answer 1

Try:

(df1.assign(idx=df1.event.eq('start').groupby(df1.account).cumsum())  # enumerate the `start` event by account
    .loc[lambda x: x['event'].isin(['start','end'])]                  # only keep `start` and `end` events
    .set_index(['idx','account','event'])                             # set index and unstack
    ['timestamp'].unstack()                               # then unstack
    .reset_index(level=1).reset_index(drop=True)          # tidying up
)

Output:

event account                  end                start
0         bob  01-01-2021 12:05:00  01-01-2021 12:00:00
1        jane  01-01-2021 12:07:00  01-01-2021 12:03:00
2         ned                  NaN  01-01-2021 12:14:00
3        todd  01-01-2021 12:10:00  01-01-2021 12:08:00
4         bob  01-01-2021 12:13:00  01-01-2021 12:11:00