calculate sum of a column based on another col

user_19187820 0
Viewed 47

My df looks like this:

value    type
12       x
34       z
54       x
14       y

I want to create a new column df.sum where I want to do a sum of the value col but only where the type == x. The remaining rows should be empty. So for example, the output should be like this:

value    type    sum
12       x       86
34       z
54       x       86
14       y
pandas dataframe python-3.x numpy python
2 Answers

If you want to handle a single type (only x):

mask = df['type'].eq('x')
df.loc[mask, 'sum'] = df.loc[mask, 'value'].sum()

if you might need to handle several:

types = ['x'] # add others, e.g.: types = ['x', 'y']
df.loc[df['type'].isin(types), 'sum'] = (df.groupby('type')['value']
                                           .transform('sum')
                                         )

output:

   value type   sum
0     12    x  66.0
1     34    z   NaN
2     54    x  66.0
3     14    y   NaN
user_163434640

yeah it looks oddish but still works:

types = ['2','x']  # your keys to sum

df = df.merge(df.query('type in @types').
              groupby('type', as_index=False).
              agg(sum), 
              how='left', on='type', suffixes=(None,'_sum'))
'''
   value type  value_sum
0     12    x       66.0
1     34    z        NaN
2     54    x       66.0
3     14    y        NaN
user_183445120
Related