将某些元素从JSON导出到XLS

Question

我目前从API读取JSON数据结构。这个结构有点嵌套:

my_json={
"data": {
"allSites": {
"activeLicenses": 0,
"totalLicenses": 1100
},
"sites": [
{
"accountId": "12345",
"accountName": "ACME INC",
"activeLicenses": 0,
"createdAt": "2021-01-12T20:04:12.166693Z",
"creator": null,
"creatorId": null,
"expiration": null,
"externalId": null,
"healthStatus": true,
"id": "12345",
"isDefault": true,
"name": "Default site",
"registrationToken": "rznwzrsnbwrn==",
"siteType": "Paid",
"sku": "Core",
"state": "active",
"suite": "Core",
"totalLicenses": 0,
"unlimitedExpiration": true,
"unlimitedLicenses": true,
"updatedAt": "2021-01-12T20:04:12.165504Z"
},
{
"accountId": "67890",
"accountName": "DE | C12345 | ACME Inc",
"activeLicenses": 0,
"createdAt": "2021-01-15T12:53:05.363922Z",
"creator": "John Doe",
"creatorId": "567837837",
"expiration": "2021-01-31T02:00:00Z",
"externalId": "C12345",
"healthStatus": true,
"id": "3268726578",
"isDefault": true,
"name": "Default site",
"registrationToken": "dghmntzme6umeum==",
"siteType": "Paid",
"sku": "Core",
"state": "active",
"suite": "Core",
"totalLicenses": 1000,
"unlimitedExpiration": false,
"unlimitedLicenses": false,
"updatedAt": "2021-01-15T12:53:05.878138Z"
},
{
"accountId": "769i376586256",
"accountName": "ACME Inc 2",
"activeLicenses": 0,
"createdAt": "2021-01-16T10:48:55.629903Z",
"creator": "Jon Doe",
"creatorId": "267267",
"expiration": null,
"externalId": null,
"healthStatus": true,
"id": "467267267",
"isDefault": false,
"name": "IT PoC",
"registrationToken": "sthmetuzmstmwsu==",
"siteType": "Trial",
"sku": "Complete",
"state": "active",
"suite": "Complete",
"totalLicenses": 100,
"unlimitedExpiration": true,
"unlimitedLicenses": false,
"updatedAt": "2021-01-16T10:48:55.940332Z"
}
]
},
"pagination": {
"nextCursor": null,
"totalItems": 3
}

}

我只对"站点"中的元素感兴趣，其他的都可以暂时忽略。在相应的XLS中，列名应该是项目名称，如accoutID、accountName等。

我可以用Pandas导出所有内容为CSV或XLSX，但如何将导出限制为"站点"?只获取列标题?

感谢——编辑在尝试以下建议时，我得到关键字错误。

我的代码如下:

r = requests.get("https://URL/web/api/v2.1/sites?limit=999&sortBy=name&states=active", headers={"Authorization":token})
data = r.json()
print(data)
sites = data['allSites']['sites']
result = pd.DataFrame(sites)
print(result)

"data"以上内容

当我现在做

sites = data['allSites']['sites']
result = pd.DataFrame(sites)
print(result)

返回错误:

Traceback(最近一次调用):文件"，第一行，在File "/Applications/PyCharm CE.app/Contents/plugins/python-ce/helpers/pydev/_pydev_bundle/pydev_umd.py"，第197行，在runfile中pydev_imports。Execfile (filename, global_vars, local_vars) #执行脚本File "/Applications/PyCharm CE.app/Contents/plugins/python-ce/helpers/pydev/_pydev_imps/_pydev_execfile.py"，第18行，execfile.py&quotExec (compile(contents+"n"， file， ' Exec ')， glob, loc)文件"/Users/adieball/Dropbox/Multiverse/Programming/S1MSSPProvisioning/getStats.py"，第45行main ()文件"/Users/adieball/Dropbox/Multiverse/Programming/S1MSSPProvisioning/getStats.py"，第29行，在main中sites = data['allSites']['sites']KeyError:"allSites">

Answer 1

您可以使用以下命令获得所需的数据:

sites= my_json['data']['sites']

并创建一个数据框架:

res=pd.DataFrame(sites)

输出将是:

>>>print(res)
accountId             accountName  ...  unlimitedLicenses                    updatedAt
0          12345                ACME INC  ...               True  2021-01-12T20:04:12.165504Z
1          67890  DE | C12345 | ACME Inc  ...              False  2021-01-15T12:53:05.878138Z
2  769i376586256              ACME Inc 2  ...              False  2021-01-16T10:48:55.940332Z
[3 rows x 21 columns]