CODEWITHSUNDEEP
pythonpandas
Suhas Mucherla
Suhas Mucherla

Reputation: 1413

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

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 : 

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

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

Upvotes: 1

Views: 60

Answers (1)

jezrael
jezrael

Reputation: 863531

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

Upvotes: 2

Related Questions