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

Given the following pandas DataFrame -

json_path Reporting Group Entity/Grouping Entity ID Adjusted Value (Today, No Div, USD) Adjusted TWR (Current Quarter, No Div, USD) Adjusted TWR (YTD, No Div, USD) Annualized Adjusted TWR (Since Inception, No Div, USD) Adjusted Value (No Div, USD) TWR Audit Note
data.attributes.total.children.[0].children.[0].children.[0] Barrack Family William and Rupert Trust 9957007 -1.44 -1.44
data.attributes.total.children.[0].children.[0].children.[0].children.[0] Barrack Family Cash - -1.44 -1.44
data.attributes.total.children.[0].children.[0].children.[1] Barrack Family Gratia Holdings No. 2 LLC 8413655 55491732.66 -0.971018847 -0.971018847 11.52490309 55491732.66
data.attributes.total.children.[0].children.[0].children.[1].children.[0] Barrack Family Investment Grade Fixed Income - 18469768.6 18469768.6
data.attributes.total.children.[0].children.[0].children.[1].children.[1] Barrack Family High Yield Fixed Income - 3668982.44 -0.205356545 -0.205356545 4.441190127 3668982.44

I am trying to filter on rows that != Cash ('Entity/Grouping' column) and that have a blank value in 'Adjusted TWR (Current Quarter, No Div, USD)' column, 'Adjusted TWR (YTD, No Div, USD)' column or 'Annualized Adjusted TWR (Since Inception, No Div, USD)' column.

Code: I am trying to achieve this by the following code -

perf_asset_class_df = df[df['json_path'].str.contains(r'(?:\.children\.\[\d+\]){4}')]
perf_asset_class_df.loc[(perf_asset_class_df['Entity/Grouping']!= 'Cash') & 
                        (perf_asset_class_df['Adjusted TWR (Current Quarter, No Div, USD)'] == '') or
                        (perf_asset_class_df['Adjusted TWR (YTD, No Div, USD)'] == '') or
                        (perf_asset_class_df['Annualized Adjusted TWR (Since Inception, No Div, USD)'] == '')]
return perf_asset_class_df

Issue: I am receiving the following error, which points to an issue with perf_asset_class_df.loc[(perf_asset_class_df['Entity/Grouping']!= 'Cash')...

ValueError                                Traceback (most recent call last)
C:\Users\WILLIA~1.FOR\AppData\Local\Temp/ipykernel_18756/2689024934.py in <module>
     48     writer.save()
     49 
---> 50 xlsx_writer()

C:\Users\WILLIA~1.FOR\AppData\Local\Temp/ipykernel_18756/2689024934.py in xlsx_writer()
      1 # Function that writes Exceptions Report and API Response as a consolidated .xlsx file.
      2 def xlsx_writer():
----> 3     reporting_group_df, unknown_df, perf_asset_class_df, perf_entity_df, perf_entity_group_df = twr_exceptions_logic()
      4 
      5 #   Creating and defining filename for exceptions report

C:\Users\WILLIA~1.FOR\AppData\Local\Temp/ipykernel_18756/1522996685.py in twr_exceptions_logic()
      4 #   [LOGIC] FOR PERF. BY ASSET CLASS (EX. ILLIQUID) - STANDARD REPORT PG.4
      5     perf_asset_class_df = df[df['json_path'].str.contains(r'(?:\.children\.\[\d+\]){4}')]
----> 6     perf_asset_class_df.loc[(perf_asset_class_df['Entity/Grouping']!= 'Cash') & 
      7                             (perf_asset_class_df['Adjusted TWR (Current Quarter, No Div, USD)'] == '') or
      8                             (perf_asset_class_df['Adjusted TWR (YTD, No Div, USD)'] == '') or

~\.conda\envs\JPDevelopment\lib\site-packages\pandas\core\generic.py in __nonzero__(self)
   1533     @final
   1534     def __nonzero__(self):
-> 1535         raise ValueError(
   1536             f"The truth value of a {type(self).__name__} is ambiguous. "
   1537             "Use a.empty, a.bool(), a.item(), a.any() or a.all()."

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

I am having a terrible run of making some amateur mistakes, and wondered if someone can give me a hint where I'm going wrong?



Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source