Pandas dataframe, how to create a new totals column containing values based on other column-CodePudding

>>> df

         Dr Name           Type   Total  Fund Total
0        Debtors  Balance Sheet  200.00        0.00
1           Bank  Balance Sheet  352.25      100.00
4   General Fund           Fund -100.00     -252.25
5  Building Fund           Fund    0.00     -300.00

I want a new column to be created, being filled with either Totals or Fund Totals.

If the Type is Balance Sheet I would like the Total column to be used - and if the Type is Fund the Fund Total to be used. To produce this:

>>> df

         Dr Name           Type   Total  Fund Total   Grand Total
0        Debtors  Balance Sheet  200.00        0.00        200.00
1           Bank  Balance Sheet  352.25      100.00        352.25
4   General Fund           Fund -100.00     -252.25       -252.25
5  Building Fund           Fund    0.00     -300.00       -300.00

Thanks

CodePudding user response：

This is a possible solution:

df["Grand Total"] = df.where(df["Type"] == "Fund")["Fund Total"].fillna(df["Total"])

With df.where I extract the column Fund Total only where the type is Fund (other rows will be nan. Then fillna fills those nan using another column (Total in this case)

CodePudding user response：

Another way is to use where from numpy:

import panda as pd

df['Grand Total'] = np.where(df['Type'] == 'Fund', df['Fund Total'], df['Total'])
print(df)

# Output
         Dr Name           Type   Total  Fund Total  Grand Total
0        Debtors  Balance Sheet  200.00        0.00       200.00
1           Bank  Balance Sheet  352.25      100.00       352.25
4   General Fund           Fund -100.00     -252.25      -252.25
5  Building Fund           Fund    0.00     -300.00      -300.00