Dataframe: Rowwise calculations

userJAb91 0
Viewed 29

I want to import an excel file into a dataframe. My dataframe (without the excel calculations) looks like this:

Jan Feb Mar Apr KPI (IF(SUM(JAN:APR)<0;0);SUM(JAN:APR))
5 -25 -20 5 0
15 24 11 -20 30

What is the best way to calculate the "KPI" column rowwise?

pandas numpy python
2 Answers

Calculate row-wise sum, then call mask and pass a lambda function for x>0 finally fill NaN values by zero.

>>> df['KPI']=df.sum(1).mask(lambda x: x<0, 0)

   Jan  Feb  Mar  Apr   KPI
0    5  -25  -20    5   0.0
1   15   24   11  -20  30.0

Better solution: call sum then Series.clip:

df['KPI']=df.sum(1).clip(0)

   Jan  Feb  Mar  Apr   KPI
0    5  -25  -20    5   0.0
1   15   24   11  -20  30.0
ThePyGuy0 
total     = df.loc[:, "Jan": "Apr"].sum(axis=1)
df["KPI"] = total.where(total > 0, other=0)
  • get the total across needed columns
  • keep it as is where it is > 0; put 0 to other places

or

df["KPI"] = np.where(total > 0, total, 0)

or

df["KPI"] = total * (total > 0)

to get

In [162]: df
Out[162]:
   Jan  Feb  Mar  Apr  KPI
0    5  -25  -20    5    0
1   15   24   11  -20   30
Mustafa Aydın0
Related