group by python DataFrameGroupBy and sum partly column variables before and after the threshold

user_10407899 0
Viewed 78

I have 3 column one is categorical values one is numeric and another yes and no include like this :

Column1 Column2 Column3
A       2       yes
A       1       yes
A       3       no
A       2       yes
A       5       yes
B       3       yes
B       1       no
B       2       yes
B       5       yes

I want to create new column to sum of column2 values until column3 equal to no. Then continue restart to sum of column2 elements until see any 'no' with group by column1. I want to have:

Column1 Column2 Column3 Column4
A       2       yes     3       ( sum of column2 elements until no for group A)
A       1       yes     3
A       3       no      no
A       2       yes     7
A       5       yes     7
B       3       yes     3
B       1       no      no
B       2       yes     7
B       5       yes     7

how can create this new column for python data frame?

Thank you so much your timing!

dataframe python
1 Answers

First find consecutive yes in groups by Column1 with cumsum of nos. Then sum Column2 by groups of this new grouping and Column1 for the rows with yes.

s = df.Column3.groupby(df.Column1).apply(lambda x: x.eq('no').cumsum())
df['Column4'] = df[df.Column3 == 'yes'].groupby(['Column1', s])['Column2'].transform('sum')
df.fillna('no')

Result

  Column1  Column2 Column3 Column4
0       A        2     yes       3
1       A        1     yes       3
2       A        3      no      no
3       A        2     yes       7
4       A        5     yes       7
5       B        3     yes       3
6       B        1      no      no
7       B        2     yes       7
8       B        5     yes       7

To better see how this is working: these are the groups created in s

df[['Column3','Column1']].assign(newGroups = s)

Output

  Column3 Column1  newGroups
0     yes       A          0
1     yes       A          0
2      no       A          1
3     yes       A          1
4     yes       A          1
5     yes       B          0
6      no       B          1
7     yes       B          1
8     yes       B          1
user_142777220
Related