R转Python开发者：如何将API返回数据读取为pandas DataFrame？

步骤1：解码字节数据并解析为Python字典 #

API返回的是字节串（b'...'格式），首先需要将其解码为UTF-8字符串，再通过json模块解析成字典：

import json
import pandas as pd

# 替换为你的API返回数据
api_response = b'{"mk_id":"1200011617609","doc_type":"sales_order","opr_code":"0","count_code":"1051885/2022","doc_date":"2022-08-23+02:00","partner":{"mk_id":"400020633177","business_entity":"false","taxpayer":"false","foreign_county":"true","customer":"Emilia Chabadova","street":"Gaštanová 2915/13","street_number":"2915/13","post_number":"92101","place":"Piešťany","country":"Slovakia","count_code":"5770789334526546744","partner_contact":{"gsm":"+421949340254","email":"emily.chabadova@gmail.com"},"mk_address_id":"400020530565","country_iso_2":"SK","buyer":"true","supplier":"false"},"receiver":{"mk_id":"400020633177","business_entity":"false","taxpayer":"false","foreign_county":"true","customer":"Emilia Chabadova","street":"Gaštanová 2915/13","street_number":"2915/13","post_number":"92101","place":"Piešťany","country":"Slovakia","count_code":"5770789334526546744","partner_contact":{"gsm":"+421949340254","email":"emily.chabadova@gmail.com"},"mk_address_id":"400020530565","country_iso_2":"SK","buyer":"true","supplier":"false"},"currency_code":"EUR","status_code":"Zaključena","doc_created_email":"stifter.rok@gmail.com","buyer_order":"SK-956103","warehouse":"glavno","delivery_type":"Gls_sk","product_list":[{"count_code":"54","mk_id":"266405022384","code":"MSS","name":"Mousse","unit":"kos","amount":"1","price":"16.66","price_with_tax":"19.99","tax":"200"},{"count_code":"53","mk_id":"266405022383","code":"MIT","name":"Mitt","unit":"kos","amount":"1","price":"0","tax":"200"},{"count_code":"48","mk_id":"266404892511","code":"TM","name":"Tanning mist","name_desc":"TM","unit":"kos","amount":"1","price":"0","tax":"200"}],"extra_column":[{"name":"tracking_number","value":"91114278162"}],"sum_basic":"16.66","sum_tax_200":"3.33","sum_all":"19.99","sum_paid":"19.99","profit_center":"SHINE BROWN, PROIZVODNJA, TRGOVINA IN STORITVE, D.O.O.","bank_ref_number":"10518852022","method_of_payment":"Plačilo po povzetju","order_create_ts":"2022-08-23T09:43:00+02:00","created_ts":"2022-08-23T11:59:14+02:00","shipped_date":"2022-08-24+02:00","doc_link_list":[{"mk_id":"266412181173","count_code":"SK-MK-36044","doc_type":"sales_bill_foreign"},{"mk_id":"400015161112","count_code":"1043748/2022","doc_type":"warehouse_packing_list"},{"mk_id":"1200011617609","count_code":"1051885/2022","doc_type":"sales_order"}}}'

# 解码并解析为字典
parsed_data = json.loads(api_response.decode('utf-8'))

步骤2：转为DataFrame（两种常用方式） #

方式一：完全展开嵌套结构（生成扁平表）

使用pd.json_normalize()可以自动展开所有嵌套的字典和列表，适合需要单张平表的场景：

# sep参数指定嵌套字段的连接符，避免列名混乱
flat_df = pd.json_normalize(parsed_data, sep='_')
print(flat_df.head())

生成的DataFrame会将partner下的字段转为partner_mk_id、partner_customer等，product_list的多个条目会展开为多行，同时保留订单主数据。

方式二：拆分主数据与明细数据

如果需要将订单主信息和商品明细分开存储（类似关系型数据库的主表+明细表）：

# 提取订单主数据（过滤掉列表类型的字段）
order_main_df = pd.DataFrame([{k: v for k, v in parsed_data.items() if not isinstance(v, list)}])

# 提取商品明细数据，并关联订单ID
product_df = pd.DataFrame(parsed_data['product_list'])
product_df['order_mk_id'] = parsed_data['mk_id']

# 查看结果
print("订单主数据：")
print(order_main_df)
print("\n商品明细：")
print(product_df)

补充说明 #

• 若API返回的是多条订单数据（列表格式），只需将parsed_data直接传入pd.json_normalize()或pd.DataFrame()即可，无需额外处理。
• 字段中的数值（如amount、price）默认是字符串类型，可通过df.astype()转换为数值类型，例如：product_df['amount'] = product_df['amount'].astype(float)

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