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?