python - 在 Python 中展平嵌套的 JSON API 字典
问题描述
我收到了使用以下代码收集的距离矩阵的以下 json 响应:
import requests
import json
payload = {
"origins": [{"latitude": 54.6565153, "longitude": -1.6802816}, {"latitude": 54.6365153, "longitude": -1.6202816}], #surgery
"destinations": [{"latitude": 54.6856522, "longitude": -1.2183634}, {"latitude": 54.5393295, "longitude": -1.2623914}, {"latitude": 54.5393295, "longitude": -1.2623914}], #oa - up to 625 entries
"travelMode": "driving",
"startTime": "2014-04-01T11:59:59+01:00",
"timeUnit": "second"
}
headers = {"Content-Length": "497", "Content-Type": "application/json"}
paramtr = {"key": "INSERT_KEY_HERE"}
r = requests.post('https://dev.virtualearth.net/REST/v1/Routes/DistanceMatrix', data = json.dumps(payload), params = paramtr, headers = headers)
data = r.json()["resourceSets"][0]["resources"][0]
并试图变平:
destinations.latitude,destinations.longitude,origins.latitude,origins.longitude,departmentTime,destinationIndex,originIndex,totalWalkDuration,travelDistance,travelDuration
从:
{'__type': 'DistanceMatrix:http://schemas.microsoft.com/search/local/ws/rest/v1',
'destinations': [{'latitude': 54.6856522, 'longitude': -1.2183634},
{'latitude': 54.5393295, 'longitude': -1.2623914},
{'latitude': 54.5393295, 'longitude': -1.2623914}],
'errorMessage': 'Request completed.',
'origins': [{'latitude': 54.6565153, 'longitude': -1.6802816},
{'latitude': 54.6365153, 'longitude': -1.6202816}],
'results': [{'departureTime': '/Date(1396349159000-0700)/',
'destinationIndex': 0,
'originIndex': 0,
'totalWalkDuration': 0,
'travelDistance': 38.209,
'travelDuration': 3082},
{'departureTime': '/Date(1396349159000-0700)/',
'destinationIndex': 1,
'originIndex': 0,
'totalWalkDuration': 0,
'travelDistance': 40.247,
'travelDuration': 2708},
{'departureTime': '/Date(1396349159000-0700)/',
'destinationIndex': 2,
'originIndex': 0,
'totalWalkDuration': 0,
'travelDistance': 40.247,
'travelDuration': 2708},
{'departureTime': '/Date(1396349159000-0700)/',
'destinationIndex': 0,
'originIndex': 1,
'totalWalkDuration': 0,
'travelDistance': 34.857,
'travelDuration': 2745},
{'departureTime': '/Date(1396349159000-0700)/',
'destinationIndex': 1,
'originIndex': 1,
'totalWalkDuration': 0,
'travelDistance': 36.895,
'travelDuration': 2377},
{'departureTime': '/Date(1396349159000-0700)/',
'destinationIndex': 2,
'originIndex': 1,
'totalWalkDuration': 0,
'travelDistance': 36.895,
'travelDuration': 2377}]}
我目前取得的最好成绩是:
json_normalize(outtie, record_path="results", meta="origins")
然而,这包含嵌套的起点和终点拒绝附加。我还尝试删除该类型以查看它是否有所作为,并探索了 max_level= 和 record_prefix='_' 但无济于事。
解决方案
- 我不认为这是一个合适的问题
flatten_json
,但是,它对于构造不那么周到的 JSON 对象可能很有用。- 请参阅如何使用 flatten_json 递归地展平嵌套的 JSON?对于那些情况。
list
indestinations
对应于list
in ,这results
意味着,当它们被规范化时,它们将具有相同的索引。- 数据帧可以正确连接,因为它们将具有相应的索引。
# create a dataframe for results and origins
res_or = pd.json_normalize(data, record_path=['results'], meta=[['origins']])
# create a dataframe for destinations
dest = pd.json_normalize(data, record_path=['destinations'], record_prefix='dest_')
# normalize the origins column in res_or
orig = pd.json_normalize(res_or.origins).rename(columns={'latitude': 'origin_lat', 'longitude': 'origin_long'})
# concat the dataframes
df = pd.concat([res_or, orig, dest], axis=1).drop(columns=['origins'])
# display(df)
departureTime destinationIndex originIndex totalWalkDuration travelDistance travelDuration origin_lat origin_long dest_latitude dest_longitude
0 /Date(1396349159000-0700)/ 0 0 0 38.209 3082 54.656515 -1.680282 54.685652 -1.218363
1 /Date(1396349159000-0700)/ 1 0 0 40.247 2708 54.656515 -1.680282 54.539330 -1.262391
2 /Date(1396349159000-0700)/ 2 0 0 40.247 2708 54.656515 -1.680282 54.539330 -1.262391
更新新的示例数据
- Records 包含 and 的索引
destinations
,origins
因此很容易为每个键创建一个单独的数据帧,然后.merge
是数据帧。orig
和的索引dest
对应于destinationIndex
和。originsIndex
results
# create three separate dataframe
results = pd.json_normalize(data, record_path=['results'])
dest = pd.json_normalize(data, record_path=['destinations'], record_prefix='dest_')
orig = pd.json_normalize(data, record_path=['origins'], record_prefix='orig_')
# merge them at the appropriate location
df = pd.merge(results, dest, left_on='destinationIndex', right_index=True)
df = pd.merge(df, orig, left_on='originIndex', right_index=True)
# display(df)
departureTime destinationIndex originIndex totalWalkDuration travelDistance travelDuration dest_latitude dest_longitude orig_latitude orig_longitude
0 /Date(1396349159000-0700)/ 0 0 0 38.209 3082 54.685652 -1.218363 54.656515 -1.680282
1 /Date(1396349159000-0700)/ 1 0 0 40.247 2708 54.539330 -1.262391 54.656515 -1.680282
2 /Date(1396349159000-0700)/ 2 0 0 40.247 2708 54.539330 -1.262391 54.656515 -1.680282
3 /Date(1396349159000-0700)/ 0 1 0 34.857 2745 54.685652 -1.218363 54.636515 -1.620282
4 /Date(1396349159000-0700)/ 1 1 0 36.895 2377 54.539330 -1.262391 54.636515 -1.620282
5 /Date(1396349159000-0700)/ 2 1 0 36.895 2377 54.539330 -1.262391 54.636515 -1.620282
推荐阅读
- angular - 社交登录 - 权限
- firebase - 在 Unity 中集成 Firebase 后无法在 Android 设备上运行
- python - 在 pandas 数据框中使用 bin
- redis - Redis集群无法添加节点
- selenium - Selenium 节点与网格断开连接
- hybris - Hybris:内容页面、类别页面和产品页面之间的区别
- android - Android/iOS 应用程序未检测到由 iOS/Android 应用程序索引的人脸 - AWS Rekognition
- javascript - 查找 SubMatrix 3x3 JavaScript 的最大总和
- javascript - 以角度获取未定义的 JSON 响应的内部数组
- php - Laravel 得到一个错误未定义的偏移量:3