I need to get sum of positive values as one value and sum of negative values as one values of a column in dataframe. for eg:-
date | amount
2021-09-02 | 98.3
2021-08-25 | -23.4
2021-08-14 | 34.57
2021-07-30 | -87.9
then i need (98.3+34.57) and (-23.4-87.9) as output
Use Series.clip with sum:
pos = df['amount'].clip(lower=0).sum()
neg = df['amount'].clip(upper=0).sum()
print (pos)
132.87
print (neg)
-111.3
Or DataFrame.loc with sum and filtering with Series.gt for greater, ~ is used for invert mask for negative values:
m = df["amount"].gt(0)
pos = df.loc[m, "amount"].sum()
neg = df.loc[~m, "amount"].sum()
print (pos)
132.87
print (neg)
-111.3
You could select your values based on the greater/lower than zero condition:
df.loc[df["amount"] > 0, "amount"].sum() # sum of greater than 0
df.loc[df["amount"] < 0, "amount"].sum() # sum of lower than 0
In each line you're using .loc to first select the rows where the value is positive/negative, and then selecting the "amount" column. Finally, we're adding those values using .sum().
A nice way to compute both sums in one shot is to define a group, this avoids having to read twice the dataset during the summation:
import numpy as np
out = df['amount'].groupby(np.where(df['amount'].ge(0), 'Positive', 'Negative')).sum()
output:
Negative -111.30
Positive 132.87
