如何将双索引双表头透视表转为五列结构的Pandas DataFrame？

问题描述 #

我要处理一个100000×100000的超大CSV文件，这个文件是带双索引列（Geo、Code）和双表头行（同样是Geo、Code）的透视表。目标是把它转换成包含geo_1、geo_2、code_1、code_2、value五列的Pandas DataFrame，最终存入SQL表。我写了一段处理代码，运行后得到部分输出，想确认这个实现是否符合需求，或者有没有更高效的处理方案。

现有代码 #

raw_data = pd.read_csv(file_path, header=[0,1], index_col=[0,1], low_memory=False)

print(f"Data melting\n.")

data_reset = raw_data.reset_index()

# Melting data
melted_data = pd.melt(data_reset, id_vars=[('Geo', ''), ('Code', '')])

melted_data['geo_2'], melted_data['code_2'] = zip(*melted_data['variable'])

final_data = melted_data.rename(columns={
    ('Geo', ''): 'geo_1',
    ('Code',''): 'code_1',
    'value': 'value'
})

final_data = final_data[['geo_1', 'geo_2', 'code_1', 'code_2', 'value']]

print(final_data.head())

运行输出 #

Geo                 ABW                                          ...       ZWE
Code                      A01       A02       A03         B   C10-C12  ...       P85         Q       R_S         T         U
Geo       Code                                                       ...
ABW       A01      0.000192  0.000095  0.000153  0.001616  0.709610  ...  0.002305  0.004002  0.002029  0.013534  0.012778
          A02      0.000249  0.000052  0.000084  0.000605  0.002513  ...  0.000461  0.000801  0.000406  0.008072  0.008647
          A03      0.000192  0.000021  0.000033  0.000016  0.028014  ...  0.001869  0.003243  0.001645  0.005666  0.004353
          B        0.007889  0.001219  0.001965  0.467401  0.071695  ...  0.002241  0.003889  0.001972  0.013528  0.012716
          C10-C12  0.118333  0.003355  0.005409  0.003407  1.347371  ...  0.002242  0.003892  0.001974  0.015993  0.015432

一、现有实现验证 #

从逻辑上看，你的代码完全符合需求：

• 读取文件时指定header=[0,1]和index_col=[0,1]，正确识别了双表头和双索引结构
• 通过reset_index把索引转为列，再用melt将宽表转长表，拆分variable列得到geo_2和code_2
• 最后重命名列并调整顺序，精准得到了目标的五列结构

注意：索引列转成普通列后，二级列名为空，所以出现('Geo', '')这类列名是正常现象，不影响最终结果。

二、针对超大文件的优化方案 #

100000×100000的文件内存占用极高（单精度浮点数约80GB），直接全量加载必然内存溢出，必须用分块处理：

1. 分块读取+逐块写入SQL #

import pandas as pd
from sqlalchemy import create_engine

# 初始化SQL连接（按需调整参数）
engine = create_engine('mysql+pymysql://user:password@host/db_name')

# 分块读取，chunksize根据内存情况调整，比如1000行
chunk_iter = pd.read_csv(
    file_path,
    header=[0,1],
    index_col=[0,1],
    low_memory=False,
    chunksize=1000
)

for chunk in chunk_iter:
    # 执行转换逻辑
    data_reset = chunk.reset_index()
    melted_data = pd.melt(data_reset, id_vars=[('Geo', ''), ('Code', '')])
    melted_data['geo_2'], melted_data['code_2'] = zip(*melted_data['variable'])
    final_data = melted_data.rename(columns={
        ('Geo', ''): 'geo_1',
        ('Code',''): 'code_1',
        'value': 'value'
    })[['geo_1', 'geo_2', 'code_1', 'code_2', 'value']]
    
    # 逐块追加写入SQL表
    final_data.to_sql('target_table', engine, if_exists='append', index=False)

2. 更高效的长表转换方式 #

用stack替代melt，一步完成多级列转成行，减少中间步骤：

# 针对单块数据的优化处理
stacked = chunk.stack([0,1]).reset_index()
stacked.columns = ['geo_1', 'code_1', 'geo_2', 'code_2', 'value']

3. 额外内存优化技巧 #

• 指定数值列类型：如果精度允许，把数值列设为float32，内存占用减半

  # 先读取第一行获取列名，再指定dtype
  first_chunk = pd.read_csv(file_path, header=[0,1], index_col=[0,1], nrows=1)
  dtype_dict = {col: 'float32' for col in first_chunk.columns}
  chunk_iter = pd.read_csv(file_path, header=[0,1], index_col=[0,1], dtype=dtype_dict, chunksize=1000)
  

• 及时释放内存：处理完中间变量后用del删除，再调用gc.collect()回收内存

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