从多文件夹JSON提取数据生成DataFrame并绘制合并图表

问题背景 #

我有一个名为data的文件夹，其中包含01、02、03三个子文件夹，每个子文件夹下有多个JSON文件。目前仅能处理单个文件夹，不清楚如何批量处理这三个文件夹，期望为每个文件夹生成对应DataFrame，并基于这些DataFrame绘制合并图表。现有代码如下：

单个文件夹处理代码：

import os
def rename(directory):
os.chdir(directory)
num = 1
for file in [file for file in sorted(os.listdir(), key=os.path.getmtime, reverse=False) if os.path.splitext(file)[1] == ".json"]:
    if os.path.splitext(file)[1] == ".json":
        os.rename(file, f"file{num}.json")
        num += 1


 path = input("Enter path")
 rename(path)
 with open('file1.json', 'r') as f1:              
 data1 = json.load(f1) 
 time_list = []    
 with open("file2.json") as f2:
 data2 = json.load(f2)
 time_string = data2["stop"]
 time_list.append(time_string)

绘图代码片段：

ta1 = [data1['price'], data3['price'], data5['price']....]
    df = pd.DataFrame({'price': ta1 , 'time': time_list})
   # Create a scatter chart object.
    chart1 = workbook.add_chart({'type': 'scatter','subtype': 'smooth_with_markers'})
  # Get the number of rows and column index
    max_row = len(df)
    col_x = df.columns.get_loc('time') + 1
    col_y = df.columns.get_loc('price') + 1
  # Create the scatter plot
    chart1.add_series({
      'name':       "price",
      'categories': [sheet_name, 1, col_x, max_row, col_x],
      'values':     [sheet_name, 1, col_y, max_row, col_y],
      'marker':     {'type': 'square', 'size': 6}
    })
    chart1.set_x_axis({'name': 'time'})
    chart1.set_y_axis({'name': 'price',
                  'major_gridlines': {'visible': False}})

解决方案 #

1. 重构批量处理逻辑 #

遍历data下的所有目标子文件夹，按时间排序并重命名JSON文件，提取price和stop字段生成带文件夹标识的DataFrame：

import os
import json
import pandas as pd

def process_single_folder(folder_path):
    # 获取文件夹内所有JSON文件，按修改时间升序排序
    json_files = sorted(
        [f for f in os.listdir(folder_path) if f.endswith('.json')],
        key=lambda x: os.path.getmtime(os.path.join(folder_path, x))
    )
    
    # 按序重命名文件
    for idx, file in enumerate(json_files, start=1):
        old_path = os.path.join(folder_path, file)
        new_path = os.path.join(folder_path, f"file{idx}.json")
        os.rename(old_path, new_path)
    
    # 提取数据：奇数序号文件取price，偶数序号文件取stop时间
    price_list = []
    time_list = []
    total_files = len(json_files)
    
    for i in range(1, total_files + 1):
        file_path = os.path.join(folder_path, f"file{i}.json")
        with open(file_path, 'r', encoding='utf-8') as f:
            data = json.load(f)
            if i % 2 == 1:
                price_list.append(data.get('price'))
            else:
                time_list.append(data.get('stop'))
    
    # 生成DataFrame并添加文件夹标识
    min_length = min(len(price_list), len(time_list))
    df = pd.DataFrame({
        'price': price_list[:min_length],
        'time': time_list[:min_length],
        'folder': os.path.basename(folder_path)
    })
    return df

# 批量处理三个子文件夹
data_root = "./data"  # 替换为你的data文件夹绝对路径
target_folders = ['01', '02', '03']
folder_dfs = []

for folder_name in target_folders:
    folder_path = os.path.join(data_root, folder_name)
    if os.path.isdir(folder_path):
        folder_df = process_single_folder(folder_path)
        folder_dfs.append(folder_df)

2. 绘制合并散点图 #

基于生成的多个DataFrame，在同一张图表中区分不同文件夹的数据：

import xlsxwriter

# 创建Excel工作簿与工作表
workbook = xlsxwriter.Workbook("merged_price_trend.xlsx")
sheet_name = "price_data"
worksheet = workbook.add_worksheet(sheet_name)

# 写入表头
headers = ["time", "price", "folder"]
worksheet.write_row(0, 0, headers)

# 写入所有文件夹的数据
current_row = 1
color_mapping = {"01": "red", "02": "blue", "03": "green"}  # 为不同文件夹分配颜色

for df in folder_dfs:
    for _, row_data in df.iterrows():
        worksheet.write(current_row, 0, row_data["time"])
        worksheet.write(current_row, 1, row_data["price"])
        worksheet.write(current_row, 2, row_data["folder"])
        current_row += 1

# 创建平滑散点图
chart = workbook.add_chart({"type": "scatter", "subtype": "smooth_with_markers"})

# 为每个文件夹添加数据系列
for folder_name in target_folders:
    # 获取当前文件夹的数据范围
    target_df = next(df for df in folder_dfs if df["folder"].iloc[0] == folder_name)
    row_count = len(target_df)
    start_row = current_row - row_count
    end_row = current_row - 1
    
    chart.add_series({
        "name": f"文件夹{folder_name}",
        "categories": [sheet_name, start_row, 0, end_row, 0],
        "values": [sheet_name, start_row, 1, end_row, 1],
        "marker": {"type": "square", "size": 6},
        "line": {"color": color_mapping[folder_name]}
    })

# 设置图表样式
chart.set_x_axis({"name": "时间"})
chart.set_y_axis({"name": "价格", "major_gridlines": {"visible": False}})
chart.set_title({"name": "各文件夹价格随时间变化趋势"})

# 将图表插入工作表
worksheet.insert_chart("D2", chart)

# 关闭工作簿
workbook.close()

关键优化点 #

• 用绝对路径替代os.chdir，避免路径混乱
• 自动遍历目标子文件夹，无需手动输入路径
• 为每个DataFrame添加folder字段，方便区分数据来源
• 图表中用不同颜色区分不同文件夹的数据，提升可读性

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