背景:
我正在尝试规范一个json文件,并将其保存到pandas数据帧中,但是我在导航json结构时遇到了问题,并且我的代码无法按预期工作。
预期的数据帧输出:
给定以下示例
json
文件(使用随机化的数据,但格式与实际数据完全相同),这是我试图产生的输出-
新建实体组
|
实体ID
|
调整后的价值
(2022年1月31日,无折扣,美元)
|
调整后的TWR
(本季度无分区,美元))
|
调整后的TWR
(本年迄今,无分区,美元)
|
年度调整后TWR
(自《盗梦空间》以来,无潜水,美元)
|
起始日期
|
风险目标
|
投资组合_1
|
|
260786美元
|
(44.55%)
|
(44.55%)
|
(44.55%)*
|
2021年4月7日
|
不适用
|
FW Irrev家族Tr
|
9552252
|
260786美元
|
0.00%
|
0.00%
|
0.00%*
|
2022年1月11日
|
不适用
|
投资组合_2
|
|
18396664美元
|
(5.78%)
|
(5.78%)
|
(5.47%)*
|
2021年9月3日
|
发育
|
FW-DAF
|
10946585
|
18396664美元
|
(5.78%)
|
(5.78%)
|
(5.47%)*
|
2021年9月3日
|
发育
|
投资组合_3
|
|
60143818美元
|
(4.42%)
|
(4.42%)
|
7.75%*
|
2020年12月17日
|
-
|
FW家族信托
|
13014080
|
475356美元
|
(6.10%)
|
(6.10%)
|
(3.97%)*
|
2021年4月9日
|
侵略性的
|
FW流动基金LP
|
13396796
|
52899527美元
|
(4.15%)
|
(4.15%)
|
(4.15%)*
|
2021年12月30日
|
侵略性的
|
FW控股第二有限责任公司
|
8413655
|
6768937美元
|
(0.77%)
|
(0.77%)
|
11.84%*
|
2021年3月5日
|
不适用
|
FW和FR接头
|
9957007
|
(1美元)
|
-
|
-
|
-*
|
2021年12月21日
|
不适用
|
实际数据帧输出:
尽管我尽了最大的努力,我还是只能得到粗体的行来映射到数据帧中:
新建实体组
|
实体ID
|
调整后的价值
(2022年1月31日,无折扣,美元)
|
调整后的TWR
(本季度无分区,美元))
|
调整后的TWR
(本年迄今,无分区,美元)
|
年度调整后TWR
(自《盗梦空间》以来,无潜水,美元)
|
起始日期
|
风险目标
|
投资组合_1
|
|
260786美元
|
(44.55%)
|
(44.55%)
|
(44.55%)*
|
2021年4月7日
|
不适用
|
投资组合_2
|
|
18396664美元
|
(5.78%)
|
(5.78%)
|
(5.47%)*
|
2021年9月3日
|
发育
|
投资组合_3
|
|
60143818美元
|
(4.42%)
|
(4.42%)
|
7.75%*
|
2020年12月17日
|
-
|
JSON文件:
这是我试图规范化并映射到数据帧中的文件:
{
"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": []
}
我的代码:
这是我构建的函数,用于尝试规范JSON响应并保存在pandas数据帧中-
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()
关于我的代码的评论:
-
投资组合_1、投资组合_2、投资组合_3
-这些粗体行是
children
属于
data
并且似乎是唯一保存到
df
。我想这是因为我的代码引用
df = pd.json_normalize(api_response['data']['attributes']['total']['children'])
只看这些列表也是如此。我试着只是附加
['children']['children']
到代码片段的末尾(给定
儿童
,但收到
TypeError: list indices must be integers or slices, not str
。
如果有任何关于如何改进或添加我的功能的建议,我将不胜感激,这样我就可以利用关键:对值,这是
儿童
水平。