Pandas Data Frame - Sum all the values in a previous column which match a specific condition and add it to a new column

user_7064974 0
Viewed 75

I'm probably missing something, but I was not able to find a solution for this. Is there a way in python to add values to a new column which satisfy a certain condition. In Excel I would apply the following formula in the new column and paste it below

=SUMIF(A1:C1, ">0")
val1 val2 val3 output
0.5 0.7 -0.9 1.2
0.3 -0.7 0.3
-0.5 -0.7 -0.9 0

Also in my extracts, there are a few blank values. Can you please help me understand what code should be written for this?

df['total'] = df[['A','B']].sum(axis=1).where(df['A'] > 0, 0)

I came across the above code, but it checks only one condition. What I need is a sum of all of those columns which match the given condition.

Thanks!

pandas dataframe python
4 Answers

pandas can handle that quite out of the box, like that:

import pandas as pd
df = pd.DataFrame([[0.5,.7,-.9],[0.3,-.7,None],[-0.5,-.7,-.9]], columns=['val1','val2','val3'])

df['output'] = df[df>0].sum(axis=1)
user_88856620

Use DataFrame.clip before sum:

df['total'] = df[['val1','val2','val3']].clip(lower=0).sum(axis=1)

#solution by Nk03 from comments
cols = ['val1','val2','val3']
df['total'] = df[cols].mask(df[cols]<0).sum(axis=1)

EDIT: For test another mask by another columns convert them to numpy array:

df['total'] = df.loc[:, "D":"F"].mask(df.loc[:, "A":"C"].to_numpy() == 'Y', 0).sum(axis=1)
user_29010020

Another way, somewhat similar to SUMIF:

# this is the "IF"
is_positive = df.loc[:, "val1": "val3"] > 0

# this is selecting the parts where condition holds & sums
df["output"] = df.loc[:, "val1": "val3"][is_positive].sum(axis=1)

where axis=1 in last line is to sum along rows,

to get

>>> df

   val1  val2  val3  output
0   0.5   0.7  -0.9     1.2
1   0.3  -0.7   NaN     0.3
2  -0.5  -0.7  -0.9     0.0
user_93321870

You can do it in the following way:

df["total"] = df.apply(lambda x: sum(x), axis=1).where((df['A'] > 0) & (df['B'] > 0) & (another_condition) & (another_condition), 0)

Note the code will take sum across all columns at once.
For taking sum of specific columns you can do the following:

df['total'] = df[['A','B','C','D','E']].sum(axis=1).where((df['A'] > 0) & (df['B'] > 0) & (another_condition) & (another_condition), 0)
user_134879150
Related