Background: I am trying to normalize a json file, and save into a pandas dataframe, however I am having issues navigating the json structure and my code isn't working as expected.
Expected dataframe output:给定以下示例json
文件(使用随机数据,但格式与真实数据完全相同),这是我试图生成的输出-
New Entity Group | Entity ID | Adjusted Value (1/31/2022, No Div, USD) |
Adjusted TWR (Current Quarter No Div, USD)) |
Adjusted TWR (YTD, No Div, USD) |
Annualized Adjusted TWR (Since Inception, No Div, USD) |
Inception Date | Risk Target |
---|---|---|---|---|---|---|---|
Portfolio_1 | $260,786 | (44.55%) | (44.55%) | (44.55%) * | Apr 7, 2021 | N/A | |
The FW Irrev Family Tr | 9552252 | $260,786 | 0.00% | 0.00% | 0.00% * | Jan 11, 2022 | N/A |
Portfolio_2 | $18,396,664 | (5.78%) | (5.78%) | (5.47%) * | Sep 3, 2021 | Growth | |
FW DAF | 10946585 | $18,396,664 | (5.78%) | (5.78%) | (5.47%) * | Sep 3, 2021 | Growth |
Portfolio_3 | $60,143,818 | (4.42%) | (4.42%) | 7.75% * | Dec 17, 2020 | - | |
The FW Family Trust | 13014080 | $475,356 | (6.10%) | (6.10%) | (3.97%) * | Apr 9, 2021 | Aggressive |
FW Liquid Fund LP | 13396796 | $52,899,527 | (4.15%) | (4.15%) | (4.15%) * | Dec 30, 2021 | Aggressive |
FW Holdings No. 2 LLC | 8413655 | $6,768,937 | (0.77%) | (0.77%) | 11.84% * | Mar 5, 2021 | N/A |
FW and FR Joint | 9957007 | ($1) | - | - | - * | Dec 21, 2021 | N/A |
Actual dataframe output: despite my best efforts, I have only been able to get bolded rows to map into the dataframe:
New Entity Group | Entity ID | Adjusted Value (1/31/2022, No Div, USD) |
Adjusted TWR (Current Quarter No Div, USD)) |
Adjusted TWR (YTD, No Div, USD) |
Annualized Adjusted TWR (Since Inception, No Div, USD) |
Inception Date | Risk Target |
---|---|---|---|---|---|---|---|
Portfolio_1 | $260,786 | (44.55%) | (44.55%) | (44.55%) * | Apr 7, 2021 | N/A | |
Portfolio_2 | $18,396,664 | (5.78%) | (5.78%) | (5.47%) * | Sep 3, 2021 | Growth | |
Portfolio_3 | $60,143,818 | (4.42%) | (4.42%) | 7.75% * | Dec 17, 2020 | - |
JSON file:这是我试图规范化并映射到数据帧的文件:
{
"meta": {
"columns": [
{
"key": "node_id",
"display_name": "Entity ID",
"output_type": "Word"
},
{
"key": "value",
"display_name": "Adjusted Value (1/31/2022, No Div, USD)",
"output_type": "Number",
"currency": "USD"
},
{
"key": "time_weighted_return",
"display_name": "Adjusted TWR (Current Quarter, No Div, USD)",
"output_type": "Percent",
"currency": "USD"
},
{
"key": "time_weighted_return_2",
"display_name": "Adjusted TWR (YTD, No Div, USD)",
"output_type": "Percent",
"currency": "USD"
},
{
"key": "time_weighted_return_3",
"display_name": "Annualized Adjusted TWR (Since Inception, No Div, USD)",
"output_type": "Percent",
"currency": "USD"
},
{
"key": "inception_event_date",
"display_name": "Inception Date",
"output_type": "Date"
},
{
"key": "_custom_portfolio_target_347209",
"display_name": "Risk Target",
"output_type": "Word"
}
],
"groupings": [
{
"key": "_custom_new_entity_group_453577",
"display_name": "NEW Entity Group"
},
{
"key": "top_level_legal_entity",
"display_name": "Top Level Legal Entity"
}
]
},
"data": {
"type": "portfolio_views",
"attributes": {
"total": {
"name": "Total",
"columns": {
"time_weighted_return": -0.05001974888806926,
"inception_event_date": "2020-12-17",
"_custom_portfolio_target_347209": null,
"time_weighted_return_3": 0.0678647066340392,
"time_weighted_return_2": -0.05001974888806926,
"value": 7.880126780581851E7,
"node_id": null
},
"children": [
{
"name": "Portfolio_3",
"grouping": "_custom_new_entity_group_453577",
"columns": {
"time_weighted_return": -0.04420061615233983,
"inception_event_date": "2020-12-17",
"_custom_portfolio_target_347209": null,
"time_weighted_return_3": 0.07748325432684622,
"time_weighted_return_2": -0.04420061615233983,
"value": 6.014381761929752E7,
"node_id": null
},
"children": [
{
"entity_id": 9957007,
"name": "FW and FR Joint",
"grouping": "top_level_legal_entity",
"columns": {
"time_weighted_return": null,
"inception_event_date": "2021-12-21",
"_custom_portfolio_target_347209": "N/A",
"time_weighted_return_3": null,
"time_weighted_return_2": null,
"value": -1.44,
"node_id": "9957007"
},
"children": []
},
{
"entity_id": 8413655,
"name": "FW Holdings No. 2 LLC",
"grouping": "top_level_legal_entity",
"columns": {
"time_weighted_return": -0.0077309266066708515,
"inception_event_date": "2021-03-05",
"_custom_portfolio_target_347209": "N/A",
"time_weighted_return_3": 0.11844843557716445,
"time_weighted_return_2": -0.0077309266066708515,
"value": 6768936.74,
"node_id": "8413655"
},
"children": []
},
{
"entity_id": 13396796,
"name": "FW Liquid Fund LP",
"grouping": "top_level_legal_entity",
"columns": {
"time_weighted_return": -0.04149769229150746,
"inception_event_date": "2021-12-30",
"_custom_portfolio_target_347209": "Aggressive",
"time_weighted_return_3": -0.041497430478377395,
"time_weighted_return_2": -0.04149769229150746,
"value": 5.289952672686747E7,
"node_id": "13396796"
},
"children": []
},
{
"entity_id": 13014080,
"name": "The FW Family Trust",
"grouping": "top_level_legal_entity",
"columns": {
"time_weighted_return": -0.06102013456998856,
"inception_event_date": "2021-04-09",
"_custom_portfolio_target_347209": "Aggressive",
"time_weighted_return_3": -0.039685671858585514,
"time_weighted_return_2": -0.06102013456998856,
"value": 475355.59242999996,
"node_id": "13014080"
},
"children": []
}
]
},
{
"name": "Portfolio_1",
"grouping": "_custom_new_entity_group_453577",
"columns": {
"time_weighted_return": -0.44554958179309,
"inception_event_date": "2021-04-07",
"_custom_portfolio_target_347209": "N/A",
"time_weighted_return_3": -0.44554958179309,
"time_weighted_return_2": -0.44554958179309,
"value": 260786.03,
"node_id": null
},
"children": [
{
"entity_id": 9552252,
"name": "The FW Irrev Family Tr",
"grouping": "top_level_legal_entity",
"columns": {
"time_weighted_return": 0.0,
"inception_event_date": "2022-01-11",
"_custom_portfolio_target_347209": "N/A",
"time_weighted_return_3": 0.0,
"time_weighted_return_2": 0.0,
"value": 260786.03,
"node_id": "9552252"
},
"children": []
}
]
},
{
"name": "Portfolio_2",
"grouping": "_custom_new_entity_group_453577",
"columns": {
"time_weighted_return": -0.05780354507057972,
"inception_event_date": "2021-09-03",
"_custom_portfolio_target_347209": "Growth",
"time_weighted_return_3": -0.05470214863844658,
"time_weighted_return_2": -0.05780354507057972,
"value": 1.8396664156520825E7,
"node_id": null
},
"children": [
{
"entity_id": 10946585,
"name": "FW DAF",
"grouping": "top_level_legal_entity",
"columns": {
"time_weighted_return": -0.05780354507057972,
"inception_event_date": "2021-09-03",
"_custom_portfolio_target_347209": "Growth",
"time_weighted_return_3": -0.05470214863844658,
"time_weighted_return_2": -0.05780354507057972,
"value": 1.8396664156520832E7,
"node_id": "10946585"
},
"children": []
}
]
}
]
}
}
},
"included": []
}
My code: this is the function, which I built to try and normalize the JSON response and save in a pandas dataframe -
def unpack_response():
while True:
try:
api_response = response_writer()
df = pd.json_normalize(api_response['data']['attributes']['total']['children'])
df.columns = df.columns.str.replace(r'columns.', '', regex=False)
column_name_mapper = {column['key']: column['display_name'] for column in api_response['meta']['columns']}
df.rename(columns=column_name_mapper, inplace=True)
break
except KeyError:
print("-----------------------------------\n","API TIMEOUT ERROR: TRYING AGAIN...", "\n-----------------------------------\n")
df.rename(columns={'name': 'New Entity Group'}, inplace=True)
column_names = ["New Entity Group", "Entity ID", "Adjusted Value (1/31/2022, No Div, USD)", "Adjusted TWR (Current Quarter, No Div, USD)", "Adjusted TWR (YTD, No Div, USD)", "Annualized Adjusted TWR (Since Inception, No Div, USD)", "Inception Date"]
df = df.reindex(columns=column_names)
return df
unpack_response()
Comment about my code:
-
Portfolio_1, Portfolio_2, Portfolio_3-这些粗体行是
data
的第一级children
,似乎是唯一保存到df
的行.我想这是因为我的代码引用了df = pd.json_normalize(api_response['data']['attributes']['total']['children'])
,所以只查看这些列表.我try 将['children']['children']
添加到该代码片段的末尾(假设有3倍级别的children
,但收到的是TypeError: list indices must be integers or slices, not str
).
I would be grateful for any suggestions on how I can improve or add to my function, so I can tap into the key:pair values, which are the 2x lower of the children
levels.