Assuming I'm dealing with this dataframe:

where

df['Remaining'] = df['Total'] - df['Retired'] - df['Cancelled']

My desired output should be like this from previous dataframe (to manually revise the negative ones in 'Remaining' to zeros):

I tried these lines, but it returns error. What did I do wrong?

df['Total'] = df.apply(lambda x: (x['Retired']   x['Cancelled']) if x['Remaining'] < 0 else x['Total'])

(and do the previous calculation again for final output)

df['Remaining'] = df['Total'] - df['Retired'] - df['Cancelled']

CodePudding user response：

I tried to create your desired output example.

df = pd.DataFrame({'Total': [100, 120, 137, 210, 111],
                 'Retired': [20, 90, 60, 110,  170],
                 'Cancelled': [70, 10, 80, 50, 0]})

df = df.assign(Remaining = df['Total'] - df['Retired'] - df['Cancelled'])

df['Remaining'] = df['Remaining'].apply(lambda x: 0 if x < 0 else x)
df['Total'] = df.apply(lambda x: x['Total'] if x['Remaining'] > 0 else x['Retired']   x['Cancelled'], axis = 1)
print(df)

CodePudding user response：

You need to pass axis=1 to apply so operation is row-wise. Otherwise, your x is a column of the dataframe.

But you can also do without apply:

df['Total'] = df['Total'].where(df['Remaining'] >= 0, df['Retired']   df['Cancelled'])