背景信息-
我有一个来自API调用的JSON响应,我试图将其保存在pandas数据帧中,同时保持相同的结构,就像我在系统中查看我调用的数据一样。
调用JSON响应的函数-
def api_call():
调用API(
注:
url_list
当前仅包含1x url)并将响应保存在
api_response
变量,使用
json.loads(response.text)
def api_call():
url_list = url_constructor()
for url in url_list:
response = requests.get(url_list[0], auth = HTTPBasicAuth(key, secret), headers={"Firm":"583"})
api_response = json.loads(response.text)
return api_response
函数,用于将响应保存到文件并返回:
def response_writer():
拯救
api_回应
作为JSON文件。它也会回来
api_回应
.
def response_writer():
api_response = api_call()
timestr = datetime.datetime.now().strftime("%Y-%m-%d-%H:%M")
filename = 'api_response_'+timestr+'.json'
with open(filename, 'w') as output_data:
json.dump(api_response, output_data)
print("-------------------------------------------------------\n",
"API RESPONSE SAVED:", filename, "\n-------------------------------------------------------")
return api_response
JSON响应-
{
"meta": {
"columns": [
{
"key": "node_id",
"display_name": "Entity ID",
"output_type": "Word"
},
{
"key": "bottom_level_holding_account_number",
"display_name": "Holding Account Number",
"output_type": "Word"
},
{
"key": "value",
"display_name": "Adjusted Value (USD)",
"output_type": "Number",
"currency": "USD"
},
{
"key": "node_ownership",
"display_name": "% Ownership",
"output_type": "Percent"
},
{
"key": "model_type",
"display_name": "Model Type",
"output_type": "Word"
},
{
"key": "valuation",
"display_name": "Valuation (USD)",
"output_type": "Number",
"currency": "USD"
},
{
"key": "_custom_jb_custodian_305769",
"display_name": "JB Custodian",
"output_type": "Word"
},
{
"key": "top_level_owner",
"display_name": "Top Level Owner",
"output_type": "Word"
},
{
"key": "top_level_legal_entity",
"display_name": "Top Level Legal Entity",
"output_type": "Word"
},
{
"key": "direct_owner",
"display_name": "Direct Owner",
"output_type": "Word"
},
{
"key": "online_status",
"display_name": "Online Status",
"output_type": "Word"
},
{
"key": "financial_service",
"display_name": "Financial Service",
"output_type": "Word"
},
{
"key": "_custom_placeholder_461415",
"display_name": "Placeholder or Fee Basis",
"output_type": "Boolean"
},
{
"key": "_custom_close_date_411160",
"display_name": "Account Close Date",
"output_type": "Date"
},
{
"key": "_custom_ownership_audit_note_425843",
"display_name": "Ownership Audit Note",
"output_type": "Word"
}
],
"groupings": [
{
"key": "holding_account",
"display_name": "Holding Account"
}
]
},
"data": {
"type": "portfolio_views",
"attributes": {
"total": {
"name": "Total",
"columns": {
"direct_owner": null,
"node_ownership": null,
"online_status": null,
"_custom_ownership_audit_note_425843": null,
"model_type": null,
"_custom_placeholder_461415": null,
"top_level_owner": null,
"_custom_close_date_411160": null,
"valuation": null,
"bottom_level_holding_account_number": null,
"_custom_jb_custodian_305769": null,
"financial_service": null,
"top_level_legal_entity": null,
"value": null,
"node_id": null
},
"children": [
{
"entity_id": 4754837,
"name": "Apple Holdings Adv (748374923)",
"grouping": "holding_account",
"columns": {
"direct_owner": "Apple Holdings LLC",
"node_ownership": 1,
"online_status": "Online",
"_custom_ownership_audit_note_425843": null,
"model_type": "Holding Account",
"_custom_placeholder_461415": false,
"top_level_owner": "Forsyth Family",
"_custom_close_date_411160": null,
"valuation": 10423695.609450001,
"bottom_level_holding_account_number": "748374923",
"_custom_jb_custodian_305769": "Laverockbank",
"financial_service": "laverockbankcustodianservice",
"top_level_legal_entity": "Apple Holdings LLC",
"value": 10423695.609450001,
"node_id": "4754837"
},
}
]
}
}
},
"included": []
}
数据帧中JSON的预期结构-
这就是我试图在熊猫数据框架中传达的结构-
| Holding Account | Entity ID | Holding Account Number | Adjusted Value (USD) | % Ownership | Model Type | Valuation (USD) | JB Custodian | Top Level Owner | Top Level Legal Entity | Direct Owner | Online Status | Financial Service | Placeholder or Fee Basis | Account Close Date | Ownership Audit Note |
|---------------------------------|-----------|------------------------|----------------------|-------------|-----------------|-----------------|--------------|-----------------|---------------------------------|---------------------------------|---------------|---------------------|--------------------------|--------------------|----------------------|
| Apple Holdings Adv (748374923) | 4754837 | 748374923 | $10,423,695.06 | 100.00% | Holding Account | $10,423,695.06 | BRF | Forsyth Family | Apple Holdings Partners LLC | Apple Holdings Partners LLC | Online | custodianservice | No | - | - |
我对JSON结构的解释-
看来我需要集中精力
{'columns:
(其中包含列标题)和
'children'
(在我的例子中,它只代表一行数据)的
'data':
.我可以忽略
'groupings': [{'key': 'holding_account', 'display_name': 'Holding Account'}]},
,因为这最终是系统中数据的排序方式。
有人建议我如何使用JSON并将其加载到具有演示结构的数据帧中吗?
我的解释是我需要设定
display_names
[
columns
]作为标题,然后映射相应的
children
每一项下的值
显示你的名字
/标题。
注:
通常会有更多
儿童
(代表我的数据帧中的每一行数据),但是我去掉了除1x之外的所有数据,以便于解释。
