pandas处理JSON转CSV超出Excel字符限制致错位问题求解

安全阿岳

2026-8-27

问题描述

从Pushshift Reddit JSON数据转储中提取指定子版块数据时，使用如下pandas分块读取逻辑处理后写入CSV：

with pd.read_json(filename+".json", lines=True, chunksize=100000) as reader:
        for chunk in reader: 
            df = pd.DataFrame(chunk)
            df.columns = df.columns.str.lower()
            df = df.reindex(columns=df_titles)
            df['subreddit'] = df['subreddit'].str.lower()
            df = df[df['subreddit'].isin(subs)]                
            if os.path.exists("/data/"+year+"/"+filename+".csv"):
                df.to_csv("data"+year+"/"+filename+".csv", mode='a', header=False, index=False)
            else:
                df.to_csv("data"+year+"/"+filename+".csv", mode='w', header=True, index=False)

生成的文件存在两类异常：

部分超长文本/嵌套对象长度超出Excel单单元格字符上限，内容溢出错位到后续行单元格，示例：
帖子文本自带的换行符被识别为行分隔符，导致更多行列错位，示例：

要求保留完整长文本不截断，需要两类可行方案：

初始数据处理阶段规避问题的方案，包括超长内容拆分多列存储的实现方式
无需重跑原始JSON，直接修复已生成CSV的方案

解决方案

前置说明

首先排除一个常见误判：如果从未用Excel保存过生成的CSV，仅在Excel中打开查看时发现错位，文件本身大概率是完好的。pandas默认to_csv逻辑会自动为含换行、逗号的字段添加双引号包裹，标准CSV解析器（pandas、Python csv模块等）读取时可正确识别字段边界，不会出现错位。直接用pd.read_csv()读取文件验证即可，不需要额外修复。
只有当你需要用Excel正常打开查看、或曾用Excel保存过文件导致内容实际损坏时，才需要使用以下方案处理。

方案一：初始JSON处理阶段规避（推荐，一劳永逸）

1. 修正CSV写入参数，解决换行符错位问题

写入CSV时强制对所有文本字段加引用标记，指定统一转义规则，生成完全符合RFC4180标准的CSV文件，可解决90%以上的换行错位问题。需要先导入标准库csv，修改to_csv参数如下：

# 顶部导入csv模块
import csv

# to_csv时添加以下参数
df.to_csv(
    "data"+year+"/"+filename+".csv",
    mode='a' if os.path.exists("/data/"+year+"/"+filename+".csv") else 'w',
    header=not os.path.exists("/data/"+year+"/"+filename+".csv"),
    index=False,
    quoting=csv.QUOTE_ALL, # 所有字段加双引号包裹
    escapechar='\\', # 指定转义字符
    encoding='utf-8-sig' # 兼容Excel中文识别
)

注意：该方式无法解决Excel单单元格32767字符的硬上限问题，长文本在Excel中依然会显示溢出，但文件内容本身完整，程序读取无异常。

2. 超长字段拆分多列，适配Excel查看需求

如果必须用Excel打开查看且不能截断长文本，可将超过字符上限的字段按固定长度拆分为多列，后续分析时拼接即可还原完整内容。实现代码如下：

MAX_EXCEL_CELL_LEN = 32000 # 留余量避免边界触发上限

def split_long_text_col(df: pd.DataFrame, col_name: str, max_len: int=MAX_EXCEL_CELL_LEN) -> pd.DataFrame:
    # 计算当前列需要拆分的段数
    col_data = df[col_name].fillna('').astype(str)
    max_split_num = (col_data.str.len() // max_len + 1).max()
    if max_split_num <= 1:
        return df
    # 逐段生成拆分列
    for idx in range(max_split_num):
        df[f"{col_name}_part{idx+1}"] = col_data.str[idx*max_len : (idx+1)*max_len]
    # 原始列可按需保留或删除，分析时用df[[所有part列]].agg(''.join, axis=1)即可还原完整文本
    return df

# 处理时对所有长文本列（如selftext、body、title等）调用该函数即可
for text_col in ['selftext', 'body', 'title']:
    df = split_long_text_col(df, text_col)

3. 换用分析友好的存储格式（最适合后续数据处理）

如果后续分析主要基于Python/pandas完成，不需要频繁用Excel打开文件，直接放弃CSV格式，改用Parquet/Feather二进制列式存储：

无单字段长度限制，自动处理换行、特殊字符，不存在转义错位问题
读写速度是CSV的5_{10倍，压缩后占用空间仅为CSV的1/5}1/10
完整保留所有字段类型信息，不需要每次读取时重新指定格式

分块写入Parquet示例代码：

import pyarrow as pa
import pyarrow.parquet as pq

# 首次写入时创建ParquetWriter，后续循环追加
parquet_path = f"/data/{year}/{filename}.parquet"
writer = None
with pd.read_json(filename+".json", lines=True, chunksize=100000) as reader:
    for chunk in reader:
        # 原有字段清洗逻辑
        df = pd.DataFrame(chunk)
        df.columns = df.columns.str.lower()
        df = df.reindex(columns=df_titles)
        df['subreddit'] = df['subreddit'].str.lower()
        df = df[df['subreddit'].isin(subs)]
        
        # 转成PyArrow Table写入
        table = pa.Table.from_pandas(df)
        if writer is None:
            writer = pq.ParquetWriter(parquet_path, table.schema)
        writer.write_table(table)
if writer:
    writer.close()

后续读取直接用pd.read_parquet(parquet_path)即可，无任何解析问题。

方案二：修复已生成的损坏CSV，无需重跑原始数据

如果之前生成的CSV已经因为Excel保存、写入参数错误出现实际内容错位，可基于Pushshift数据固定列数的特征做行对齐修复，对换行导致的错位修复准确率接近100%。修复代码如下：

import csv
import pandas as pd
import os

# 替换为你处理时使用的固定列名列表
FIXED_COLUMNS = df_titles
EXPECTED_COL_NUM = len(FIXED_COLUMNS)
csv_path = f"/data/{year}/{filename}.csv"

fixed_rows = []
current_buffer = []

with open(csv_path, 'r', encoding='utf-8') as f:
    reader = csv.reader(f, quoting=csv.QUOTE_NONE, escapechar='\\')
    next(reader) # 跳过原表头
    for raw_row in reader:
        current_buffer.extend(raw_row)
        # 累计字段数达到预期列数时，切出完整行
        while len(current_buffer) >= EXPECTED_COL_NUM:
            valid_row = current_buffer[:EXPECTED_COL_NUM]
            fixed_rows.append(valid_row)
            current_buffer = current_buffer[EXPECTED_COL_NUM:]

# 转为修复后的DataFrame
df_fixed = pd.DataFrame(fixed_rows, columns=FIXED_COLUMNS)

# 修复后可按方案一的逻辑拆分长列、转存为标准CSV/Parquet

注意：如果之前用Excel打开文件时触发了字符截断且保存了文件，被截断丢失的字符无法通过该方法找回；如果只是打开查看未保存，文件内容完整，修复后可拿到全部数据。

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

pandas处理JSON转CSV超出Excel字符限制致错位问题求解

前置说明 #

方案一：初始JSON处理阶段规避（推荐，一劳永逸） #

1. 修正CSV写入参数，解决换行符错位问题 #

2. 超长字段拆分多列，适配Excel查看需求 #

3. 换用分析友好的存储格式（最适合后续数据处理） #