如何用Pandas规范化Google Maps Distance Matrix API的嵌套JSON数据

我正在使用Google Maps Distance Matrix API获取多个起点到多个终点的距离，API返回的JSON结构如下：

{
    "destination_addresses": [
        "Destination 1",
        "Destination 2",
        "Destination 3"
    ],
    "origin_addresses": [
        "Origin 1",
        "Origin 2"
    ],
    "rows": [
        {
            "elements": [
                {
                    "distance": {
                        "text": "8.7 km",
                        "value": 8687
                    },
                    "duration": {
                        "text": "19 mins",
                        "value": 1129
                    },
                    "status": "OK"
                },
                {
                    "distance": {
                        "text": "223 km",
                        "value": 222709
                    },
                    "duration": {
                        "text": "2 hours 42 mins",
                        "value": 9704
                    },
                    "status": "OK"
                },
                {
                    "distance": {
                        "text": "299 km",
                        "value": 299156
                    },
                    "duration": {
                        "text": "4 hours 17 mins",
                        "value": 15400
                    },
                    "status": "OK"
                }
            ]
        },
        {
            "elements": [
                {
                    "distance": {
                        "text": "216 km",
                        "value": 215788
                    },
                    "duration": {
                        "text": "2 hours 44 mins",
                        "value": 9851
                    },
                    "status": "OK"
                },
                {
                    "distance": {
                        "text": "20.3 km",
                        "value": 20285
                    },
                    "duration": {
                        "text": "21 mins",
                        "value": 1283
                    },
                    "status": "OK"
                },
                {
                    "distance": {
                        "text": "210 km",
                        "value": 210299
                    },
                    "duration": {
                        "text": "2 hours 45 mins",
                        "value": 9879
                    },
                    "status": "OK"
                }
            ]
        }
    ],
    "status": "OK"
}

注：rows数组元素数量与origin_addresses一致（2个），每个rows中的elements数组元素数量与destination_addresses一致（3个）。

需要将rows内的所有数据规范化，同时关联对应的origin_addresses和destination_addresses数据，期望输出格式如下：

status distance.text  distance.value    duration.text  duration.value origin_addresses destination_addresses
0     OK        8.7 km            8687          19 mins            1129         Origin 1         Destination 1
1     OK        223 km          222709  2 hours 42 mins            9704         Origin 1         Destination 2
2     OK        299 km          299156  4 hours 17 mins           15400         Origin 1         Destination 3
3     OK        216 km          215788  2 hours 44 mins            9851         Origin 2         Destination 1
4     OK       20.3 km           20285          21 mins            1283         Origin 2         Destination 2
5     OK        210 km          210299  2 hours 45 mins            9879         Origin 2         Destination 3

若Pandas没有简便实现方式，该如何完成此操作？

一、使用Pandas实现（简便方法） #

利用Pandas的json_normalize扁平化嵌套JSON，再关联起点和终点地址：

import pandas as pd

# 替换为实际API返回的JSON数据
api_response = {
    # 此处放入上述完整JSON内容
}

# 1. 扁平化rows中的elements数据
df = pd.json_normalize(api_response['rows'], record_path='elements')

# 2. 生成对应起点和终点的地址列表
origins = []
destinations = []
dest_count = len(api_response['destination_addresses'])
for origin in api_response['origin_addresses']:
    origins.extend([origin] * dest_count)
    destinations.extend(api_response['destination_addresses'])

# 3. 添加地址列并调整列顺序
df['origin_addresses'] = origins
df['destination_addresses'] = destinations
cols_order = ['status', 'distance.text', 'distance.value', 'duration.text', 'duration.value', 'origin_addresses', 'destination_addresses']
df = df[cols_order]

print(df)

运行后即可得到符合要求的结构化表格。

二、原生Python实现（不依赖Pandas） #

通过双层循环遍历数据结构，手动构建结构化列表：

# 替换为实际API返回的JSON数据
api_response = {
    # 此处放入上述完整JSON内容
}

result = []
# 遍历每个起点及对应行数据
for origin, row in zip(api_response['origin_addresses'], api_response['rows']):
    # 遍历每个终点及对应元素数据
    for destination, element in zip(api_response['destination_addresses'], row['elements']):
        result.append({
            'status': element['status'],
            'distance.text': element['distance']['text'],
            'distance.value': element['distance']['value'],
            'duration.text': element['duration']['text'],
            'duration.value': element['duration']['value'],
            'origin_addresses': origin,
            'destination_addresses': destination
        })

# 模拟表格格式输出
header = list(result[0].keys())
print('  '.join(f'{col:<20}' for col in header))
for idx, row in enumerate(result):
    print(f'{idx:<2}' + '  '.join(f'{str(row[col]):<20}' for col in header))

此方法无需依赖第三方库，直接通过循环关联所有数据，最终生成符合需求的结构化结果。

内容的提问来源于stack exchange，提问作者manoelpqueiroz