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?
CodePudding user response：
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
whereit is > 0; put 0 tootherplaces
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
CodePudding user response：
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