Adding values of a column based on values in another column in pandas

Question

I have a dataframe with two columns a and b, I want a column c in the output dataframe whose values are the sum of values in the column b corresponding to 1's in the column a and store that sum one index below in the c column

This is the input data that I have :

and I want the output to be something like this:

    a    b    c
0   0  0.1  0.0
1   0  0.4  0.0
2   0  0.2  0.0
3   1  0.4  0.0
4   1  0.8  0.0
5   0  0.1  1.2
6   0  1.3  0.0
7   1  2.4  0.0
8   1  1.2  0.0
9   1  1.7  0.0
10  1  0.9  0.0
11  0  3.2  6.2

This is my first question, i'm sorry if my question is not aesthetic enough,any help would be appreciated,Thanks in advance

jezrael · Accepted Answer · 2019-01-23 06:36:18Z

2

Use:

#compare by 1 with equal
m1 = df['a'].eq(1) 
#create unique groups 
s = df['a'].ne(df['a'].shift()).cumsum()
#get sums with transform for new column filled aggregate values, shift one value
df['c'] = df['b'].groupby(s).transform('sum').shift().fillna(0)
#set 0 to all values with not first 0 groups
df.loc[m1 | s.duplicated(), 'c'] = 0
print (df)
    a    b    c
0   0  0.1  0.0
1   0  0.4  0.0
2   0  0.2  0.0
3   1  0.4  0.0
4   1  0.8  0.0
5   0  0.1  1.2
6   0  1.3  0.0
7   1  2.4  0.0
8   1  1.2  0.0
9   1  1.7  0.0
10  1  0.9  0.0
11  0  3.2  6.2

edited Jan 23, 2019 at 6:36

answered Jan 23, 2019 at 6:28

jezrael

868k103 gold badges1.4k silver badges1.3k bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Adding values of a column based on values in another column in pandas

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related