python - filters the row data and summs it to see if it can give the result with reduce() combined with filter()

Question

Figure out the sum of "Dwell Time" between yellow rows, and the result is: https://i.stack.imgur.com/9G6zF.png

Is there a better way

question from:https://stackoverflow.com/questions/66058417/filters-the-row-data-and-summs-it-to-see-if-it-can-give-the-result-with-reduce

Answer 1

If need sum values per each 2 values of level column create groups by compare level by Series.eq, add cumulative sum by Series.cumsum and replace rows with 2 by missing values in Series.mask. Then pass it to GroupBy.transform and for last rows only add another mask with Series.duplicated:

df = pd.DataFrame({'level':[2,3,2,1,3,2,1,3,5,3,1,2],
                    'dwell time':[1,1,1,1,2,1,2,0,2,3,4,1]})

m = df['level'].eq(2)
g = m.cumsum().mask(m)

df['dwell time 1 + 3'] = (df.groupby(g)['dwell time']
                            .transform('sum')
                            .mask(g.duplicated(keep='last')))

print (df)
    level  dwell time  dwell time 1 + 3
0       2           1               NaN
1       3           1               1.0
2       2           1               NaN
3       1           1               NaN
4       3           2               3.0
5       2           1               NaN
6       1           2               NaN
7       3           0               NaN
8       5           2               NaN
9       3           3               NaN
10      1           4              11.0
11      2           1               NaN