使用pandas读取JSON转存CSV，Colab正常本地PyCharm报错如何解决？

场景说明 #

需要将JSON文件转换为DataFrame格式，最终导出保存为CSV文件。该逻辑在Jupyter或Colab环境中可正常运行，但在本地PyCharm中运行相同逻辑的代码时出现多处错误。

Colab环境可正常运行的代码 #

import pandas as pd
import datetime

# reading json file
df = pd.read_json(path_or_buf=CONFIG_PROPERTIES)

# normalizing json file
df_items_normalized = pd.json_normalize(data=df.orders, sep='_', record_path='items', meta=['error', 'file','order_id'])

# define parameters to save in csv
today = datetime.datetime.today().strftime('%Y_%m_%d')
path = "/output/pedidos_weedu_" + today + ".csv"

# saving to csv
df_items_normalized.to_csv(path, index=False)

PyCharm本地运行的错误代码 #

import pandas as pd
import datetime
import json
import os

CWD = os.getcwd()
JSON_CONFIG_FILE_PATH = '%s/%s' % (CWD, '12-11-2021.json')

CONFIG_PROPERTIES = {}

try:
    with open(JSON_CONFIG_FILE_PATH) as data_file:
        CONFIG_PROPERTIES = json.load(data_file)
except IOError as e:
    print(e)
    print('IOError: Unable to open config.json.')
    exit(1)

print(CONFIG_PROPERTIES)

# reading json file
df = pd.read_json(path_or_buf=CONFIG_PROPERTIES)

# normalizing json file
df_items_normalized = pd.json_normalize(data=df.orders, sep='_', record_path='items', meta=['error', 'file','order_id'])

# define parameters to save in csv
today = datetime.datetime.today().strftime('%Y_%m_%d')
path = "/output/pedidos_weedu_" + today + ".csv"

# saving to csv
df_items_normalized.to_csv(path, index=False)

错误原因 #

• 入参类型错误：本地代码已经通过json.load()将JSON文件读取为Python字典，而pd.read_json()仅接收JSON字符串、文件路径/类文件对象作为入参，传入已解析的字典会直接报错
• 路径适配问题：输出路径使用了类Unix系统的绝对路径写法，Windows系统不支持该格式，且未提前创建output目录会触发路径不存在报错
• 冗余转换逻辑：已加载为字典的JSON数据无需再通过pd.read_json()转换为DataFrame，可直接提取对应字段传给pd.json_normalize()处理

修复后可在本地运行的代码 #

import pandas as pd
import datetime
import json
import os

CWD = os.getcwd()
JSON_CONFIG_FILE_PATH = os.path.join(CWD, '12-11-2021.json')
OUTPUT_DIR = os.path.join(CWD, 'output')
# 提前创建输出目录，避免不存在报错
os.makedirs(OUTPUT_DIR, exist_ok=True)

CONFIG_PROPERTIES = {}

try:
    with open(JSON_CONFIG_FILE_PATH, encoding='utf-8') as data_file:
        CONFIG_PROPERTIES = json.load(data_file)
except IOError as e:
    print(e)
    print('IOError: Unable to open JSON file.')
    exit(1)

# 直接取已加载字典的orders字段进行标准化，无需多余的read_json步骤
df_items_normalized = pd.json_normalize(
    data=CONFIG_PROPERTIES['orders'], 
    sep='_', 
    record_path='items', 
    meta=['error', 'file','order_id']
)

# 拼接输出路径
today = datetime.datetime.today().strftime('%Y_%m_%d')
file_name = f"pedidos_weedu_{today}.csv"
path = os.path.join(OUTPUT_DIR, file_name)

# 保存为CSV，编码适配Windows系统避免乱码
df_items_normalized.to_csv(path, index=False, encoding='utf_8_sig')

内容的提问来源于stack exchange，提问作者Kleiton Marques Dos Reis