Pandas Method chaining and adding a new column

user_10159466 0
Viewed 239

I am trying to make a new column in Pandas called "Depreciation" like below:

(omc_e.query('GL_account == "740190" and Posting_date > "2021-06-30"')
    .groupby(['Business_segment_item'], as_index=False)
    ['Amount_DKK']
    .sum()
    .assign( Depreciation = lambda x: 0 if x.Business_segment_item == "" else x.Amount_DKK)
)

But I get an error when trying to run it:

ValueError: The truth value of a Series is ambiguous. Use a.empty, a.bool(), a.item(), a.any() or a.all().

I can of course make a new dataframe "test" and run the same method and then it works:

test[['Business_segment_item', 'Amount_DKK']].apply(lambda x: 0 if x.Business_segment_item == "" else x.Amount_DKK, axis = 1)

So it must be my method chaining that is causing the trouble, but can I avoid that and still use method chaining in order to avoid a lot of temporary dataframes?

pandas lambda python
2 Answers

Use numpy.where:

(omc_e.query('GL_account == "740190" and Posting_date > "2021-06-30"')
    .groupby(['Business_segment_item'], as_index=False)
    ['Amount_DKK']
    .sum()
    .assign( Depreciation = lambda x: np.where(x.Business_segment_item == "" , 0, x.Amount_DKK))
)
user_29010020

An alternative, that may offer a cleaner abstraction, is to use the case_when function from pyjanitor, which replicates the case when function from SQL:

# pip install pyjanitor
import pandas as pd
import janitor
(omc_e.query('GL_account == "740190" and Posting_date > "2021-06-30"')
    .groupby(['Business_segment_item'], as_index=False)
    ['Amount_DKK']
    .sum()
    .case_when(lambda x: x.Business_segment_item == "", # condition
               0, # result if True
               lambda x: x.Amount_DKK, # result if False
               column_name = 'Depreciation')
)

You can have a look at more examples here

user_71757130
Related