Group consecutive events with same atributes and calculate the cumulative value of other column

Question

I'm trying to solve a problem with pandas that consists of increment the column "duration" but in those rows that the pair "id" and "user_number" on consecutive events. I've tried several ways using groupby or boolean columns with cumsum but it will always accumulate values of events (or rows) that aren't consecutives.

I have a sample of the data:

id      user_num    user_key    start_time              end_time                  type  duration
1185ba  413         221528661   2020-12-13 15:15:00.000 2020-12-13 15:34:09.000   L     1149000.0   
1185ba  403         22251339    2020-12-13 15:34:09.000 2020-12-13 15:49:00.000   L     891000.0    
1185ba  403         22421785    2020-12-13 15:49:00.000 2020-12-13 16:10:00.000   L     1260000.0   
6666uj  403         22621565    2020-12-13 16:10:00.000 2020-12-13 16:37:00.000   L     1620000.0   
1185ba  490         221493747   2020-12-13 16:37:05.000 2020-12-13 16:49:00.000   L     715000.0    
1185ba  490         221063609   2020-12-13 16:49:00.000 2020-12-13 16:50:45.000   L     105000.0    
1185ba  413         221528653   2020-12-13 16:50:45.000 2020-12-13 16:51:06.463   L     21463.0 0   
1185ba  11          12470010    2020-12-13 16:51:06.463 2020-12-13 16:55:00.000   NL    233537.0    
1185ba  413         221528654   2020-12-13 16:55:00.000 2020-12-13 17:00:00.000   L     300000.0    
1185ba  413         562281418   2020-12-13 17:00:00.000 2020-12-13 17:25:00.000   L     1500000.0
1185ba  413         221528638   2020-12-13 17:25:00.000 2020-12-13 17:31:30.013   L     390013.0

And this example is what I want to get:

id      user_num    user_key    start_time              end_time                  type  duration (sum)
1185ba  413         221528661  2020-12-13 15:15:00.000 2020-12-13 15:34:09.000    L     1149000.0   
1185ba  403         22251339   2020-12-13 15:34:09.000 2020-12-13 15:49:00.000    L     891000.0
1185ba  403         22421785   2020-12-13 15:49:00.000 2020-12-13 16:10:00.000    L     2151000.0
6666uj  403         22621565   2020-12-13 16:10:00.000 2020-12-13 16:37:00.000    L     1620000.0   
1185ba  490         221493747  2020-12-13 16:37:05.000 2020-12-13 16:49:00.000    L     715000.0    
1185ba  490         221063609  2020-12-13 16:49:00.000 2020-12-13 16:50:45.000    L     820000.0    
1185ba  413         221528653  2020-12-13 16:50:45.000 2020-12-13 16:51:06.463    L     21463.0 
1185ba   11         2470010    2020-12-13 16:51:06.463 2020-12-13 16:55:00.000    NL    233537.0    
1185ba  413         221528654  2020-12-13 16:55:00.000 2020-12-13 17:00:00.000    L     300000.0    
1185ba  413         562281418  2020-12-13 17:00:00.000 2020-12-13 17:25:00.000    L     1800000.0
1185ba  413         221528638  2020-12-13 17:25:00.000 2020-12-13 17:31:30.013    L     2190013.0

How could I get a table like this? I'm having a hard time in search things like this. we have a user_num with 413 and id and id 1185ba that starts at 15:15 but there are events in the middle until reaches the line 1185ba 413 221528653 2020-12-13 16:50:45.000 2020-12-13 16:51:06.463 L 21463.0 and the sum or cumsum is restarted. But the key points it's id with user_key and the duration which must be summed if the pair of key points are consecutive, when this doesn't happen anymore the count for the pair is reset.

user_14551426 · Answer

The main idea is to identify sequences of consecutive events after grouping by id, user_num and then do cumsum

First we load the data

import pandas as pd
from io import StringIO
data = StringIO(
"""    
id      user_num    user_key    start_time              end_time                  type  duration
1185ba  413         221528661   2020-12-13 15:15:00.000  2020-12-13 15:34:09.000   L     1149000.0   
1185ba  403         22251339    2020-12-13 15:34:09.000  2020-12-13 15:49:00.000   L     891000.0    
1185ba  403         22421785    2020-12-13 15:49:00.000  2020-12-13 16:10:00.000   L     1260000.0   
6666uj  403         22621565    2020-12-13 16:10:00.000  2020-12-13 16:37:00.000   L     1620000.0   
1185ba  490         221493747   2020-12-13 16:37:05.000  2020-12-13 16:49:00.000   L     715000.0    
1185ba  490         221063609   2020-12-13 16:49:00.000  2020-12-13 16:50:45.000   L     105000.0    
1185ba  413         221528653   2020-12-13 16:50:45.000  2020-12-13 16:51:06.463   L     21463.0   
1185ba  11          12470010    2020-12-13 16:51:06.463  2020-12-13 16:55:00.000   NL    233537.0    
1185ba  413         221528654   2020-12-13 16:55:00.000  2020-12-13 17:00:00.000   L     300000.0    
1185ba  413         562281418   2020-12-13 17:00:00.000  2020-12-13 17:25:00.000   L     1500000.0
1185ba  413         221528638   2020-12-13 17:25:00.000  2020-12-13 17:31:30.013   L     390013.0
""")
df = pd.read_csv(data, delimiter='\s\s+')
df['start_time'] = pd.to_datetime(df['start_time'])
df['end_time'] = pd.to_datetime(df['end_time'])

Next we add a column that marks (within each id, user_num group) a gap in the time sequence

df2 = (df.groupby(['id','user_num'], sort=False, as_index = False)
    .apply(lambda g: 
        g.assign(new_seq_start = (g['start_time'] != g['end_time'].shift()))
    )
    .reset_index(drop = True)
)
df2

this gives

    id        user_num    user_key  start_time                  end_time                    type      duration  new_seq_start
--  ------  ----------  ----------  --------------------------  --------------------------  ------  ----------  ---------------
 0  1185ba         413   221528661  2020-12-13 15:15:00         2020-12-13 15:34:09         L        1149000.0  True
 1  1185ba         413   221528653  2020-12-13 16:50:45         2020-12-13 16:51:06.463000  L          21463.0  True
 2  1185ba         413   221528654  2020-12-13 16:55:00         2020-12-13 17:00:00         L         300000.0  True
 3  1185ba         413   562281418  2020-12-13 17:00:00         2020-12-13 17:25:00         L        1500000.0  False
 4  1185ba         413   221528638  2020-12-13 17:25:00         2020-12-13 17:31:30.013000  L         390013.0  False
 5  1185ba         403    22251339  2020-12-13 15:34:09         2020-12-13 15:49:00         L         891000.0  True
 6  1185ba         403    22421785  2020-12-13 15:49:00         2020-12-13 16:10:00         L        1260000.0  False
 7  6666uj         403    22621565  2020-12-13 16:10:00         2020-12-13 16:37:00         L        1620000.0  True
 8  1185ba         490   221493747  2020-12-13 16:37:05         2020-12-13 16:49:00         L         715000.0  True
 9  1185ba         490   221063609  2020-12-13 16:49:00         2020-12-13 16:50:45         L         105000.0  False
10  1185ba          11    12470010  2020-12-13 16:51:06.463000  2020-12-13 16:55:00         NL        233537.0  True

Then we use this new column new_seq_start to define a counter group_idx that increments when there is a gap

df2['group_idx'] = df2.groupby(['id','user_num'])['new_seq_start'].cumsum()
df2

so we get

    id        user_num    user_key  start_time                  end_time                    type      duration  new_seq_start      group_idx    duration (sum)
--  ------  ----------  ----------  --------------------------  --------------------------  ------  ----------  ---------------  -----------  ----------------
 0  1185ba         413   221528661  2020-12-13 15:15:00         2020-12-13 15:34:09         L        1149000.0  True                       1         1149000.0
 1  1185ba         413   221528653  2020-12-13 16:50:45         2020-12-13 16:51:06.463000  L          21463.0  True                       2           21463.0
 2  1185ba         413   221528654  2020-12-13 16:55:00         2020-12-13 17:00:00         L         300000.0  True                       3          300000.0
 3  1185ba         413   562281418  2020-12-13 17:00:00         2020-12-13 17:25:00         L        1500000.0  False                      3         1800000.0
 4  1185ba         413   221528638  2020-12-13 17:25:00         2020-12-13 17:31:30.013000  L         390013.0  False                      3         2190013.0
 5  1185ba         403    22251339  2020-12-13 15:34:09         2020-12-13 15:49:00         L         891000.0  True                       1          891000.0
 6  1185ba         403    22421785  2020-12-13 15:49:00         2020-12-13 16:10:00         L        1260000.0  False                      1         2151000.0
 7  6666uj         403    22621565  2020-12-13 16:10:00         2020-12-13 16:37:00         L        1620000.0  True                       1         1620000.0
 8  1185ba         490   221493747  2020-12-13 16:37:05         2020-12-13 16:49:00         L         715000.0  True                       1          715000.0
 9  1185ba         490   221063609  2020-12-13 16:49:00         2020-12-13 16:50:45         L         105000.0  False                      1          820000.0
10  1185ba          11    12470010  2020-12-13 16:51:06.463000  2020-12-13 16:55:00         NL        233537.0  True                       1          233537.0

now we can groupby on the original columns plus group_idx, apply cumsum, and sort in the original order

df2['duration (sum)'] = df2.groupby(['id','user_num','group_idx'])['duration'].cumsum()
df2.sort_values('start_time')

done:

    id        user_num    user_key  start_time                  end_time                    type      duration  new_seq_start      group_idx    duration (sum)
--  ------  ----------  ----------  --------------------------  --------------------------  ------  ----------  ---------------  -----------  ----------------
 0  1185ba         413   221528661  2020-12-13 15:15:00         2020-12-13 15:34:09         L        1149000.0  True                       1         1149000.0
 5  1185ba         403    22251339  2020-12-13 15:34:09         2020-12-13 15:49:00         L         891000.0  True                       1          891000.0
 6  1185ba         403    22421785  2020-12-13 15:49:00         2020-12-13 16:10:00         L        1260000.0  False                      1         2151000.0
 7  6666uj         403    22621565  2020-12-13 16:10:00         2020-12-13 16:37:00         L        1620000.0  True                       1         1620000.0
 8  1185ba         490   221493747  2020-12-13 16:37:05         2020-12-13 16:49:00         L         715000.0  True                       1          715000.0
 9  1185ba         490   221063609  2020-12-13 16:49:00         2020-12-13 16:50:45         L         105000.0  False                      1          820000.0
 1  1185ba         413   221528653  2020-12-13 16:50:45         2020-12-13 16:51:06.463000  L          21463.0  True                       2           21463.0
10  1185ba          11    12470010  2020-12-13 16:51:06.463000  2020-12-13 16:55:00         NL        233537.0  True                       1          233537.0
 2  1185ba         413   221528654  2020-12-13 16:55:00         2020-12-13 17:00:00         L         300000.0  True                       3          300000.0
 3  1185ba         413   562281418  2020-12-13 17:00:00         2020-12-13 17:25:00         L        1500000.0  False                      3         1800000.0
 4  1185ba         413   221528638  2020-12-13 17:25:00         2020-12-13 17:31:30.013000  L         390013.0  False                      3         2190013.0

Group consecutive events with same atributes and calculate the cumulative value of other column

1 Answers