如何将API返回的JSON数据转换为带正确数据类型的Pandas DataFrame

通过r = requests.get(url)获取到的JSON响应如下：

{"data": {"rows": [["2016-09-06T21:41:38-04:00", "The Zebra"], ["2018-10-29T21:41:38-04:00", "The Dog"]], "cols": [{"display_name": "CreatedDate", "source": "native", "field_ref": ["field", "created_at", {"base-type": "type/DateTime"}], "name": "created_at", "base_type": "type/DateTime", "effective_type": "type/DateTime"}, {"display_name": "name", "source": "native", "field_ref": ["field", "created_at", {"base-type": "type/text"}], "name": "created_at", "base_type": "type/text", "effective_type": "type/text"}]}}

当前尝试的代码存在列处理错误，直接将cols数组传给columns参数，导致列名异常且未处理数据类型：

data = json.loads(r)
rows = data["data"]["rows"]
cols = data["data"]["cols"]
df = pd.DataFrame(data= rows, columns = cols)

期望输出的DataFrame格式如下：

+---------------------------+-------------+
| CreatedDate               | Name        |  
+---------------------------+-------------+
|2016-09-06T21:41:38-04:00  |  The Zebra  | 
|2018-10-29T21:41:38-04:00  |  The Dog    | 
+---------------------------+-------------+

解决步骤 #

1. 提取列名和数据类型映射 #

从cols中提取显示名称作为DataFrame列名，同时将API返回的effective_type映射为pandas支持的数据类型：

# 提取列名
col_names = [col["display_name"] for col in cols]
# 建立类型映射规则
type_mapping = {
    "type/DateTime": "datetime64[ns]",
    "type/text": str
}
# 获取每列对应的目标数据类型
col_types = [type_mapping[col["effective_type"]] for col in cols]

2. 创建DataFrame并设置列名 #

用rows数据创建DataFrame，传入提取好的列名：

import pandas as pd
import json
import requests

# 请求API
r = requests.get(url)
data = json.loads(r.text)  # 注意需传入响应文本r.text，而非响应对象r
rows = data["data"]["rows"]
cols = data["data"]["cols"]

# 创建DataFrame
df = pd.DataFrame(rows, columns=col_names)

3. 批量转换数据类型 #

根据类型映射规则，将各列转换为对应的数据类型：

for col_name, col_type in zip(col_names, col_types):
    df[col_name] = df[col_name].astype(col_type)

完整代码 #

import pandas as pd
import json
import requests

url = "你的API地址"
r = requests.get(url)
data = json.loads(r.text)

rows = data["data"]["rows"]
cols = data["data"]["cols"]

# 处理列名和数据类型映射
col_names = [col["display_name"] for col in cols]
type_mapping = {
    "type/DateTime": "datetime64[ns]",
    "type/text": str
}
col_types = [type_mapping[col["effective_type"]] for col in cols]

# 创建并转换DataFrame
df = pd.DataFrame(rows, columns=col_names)
for col, dtype in zip(col_names, col_types):
    df[col] = df[col].astype(dtype)

# 查看最终结果
print(df)

运行后得到的DataFrame会自动识别正确的列名和数据类型，输出格式与预期一致。

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