以Pythonic方式规整格式特殊的成对关联列DataFrame

问题背景 #

我有一个结构特殊的DataFrame：列两两配对关联，第一列（删除原第0列后）包含对应相邻列值的标签和编码。当前工作表约有15000列，通过pd.read_excel(file, header=None, sheet_name="sheet1")读取后已删除第0列；另有一个工作表存储着对应letter_1、letter_2等列名的受访者答案。我已用循环代码实现转换，但想找到更符合Python风格的Pandas实现方式。

示例数据 #

原DataFrame结构（简化版） #

目标转换结构 #

现有循环实现代码 #

import pandas as pd

# 读取数据
df = pd.read_excel("data.xlsx", header=None, sheet_name="sheet1")
df = df.drop(columns=0)  # 删除第0列

# 初始化结果DataFrame
result = pd.DataFrame()

# 遍历每一对列（标签列+值列）
for i in range(0, len(df.columns), 2):
    label_col = df.iloc[:, i]
    value_col = df.iloc[:, i+1]
    
    # 拆分标签和编码
    label_split = label_col.str.split(":", expand=True)
    label = label_split[0]
    code = label_split[1]
    
    # 命名列并添加到结果
    pair_num = i//2 + 1
    result[f"letter_{pair_num}"] = value_col
    result[f"label_{pair_num}"] = label
    result[f"code_{pair_num}"] = code

Pythonic的Pandas优化方案 #

针对15000列的大规模数据，推荐两种无显式循环的高效实现：

方法1：列分组+向量化拆分+合并 #

代码可读性高，逻辑直观：

import pandas as pd

df = pd.read_excel("data.xlsx", header=None, sheet_name="sheet1")
df = df.drop(columns=0)

# 将列按两两一组拆分
pair_groups = [df.iloc[:, i:i+2] for i in range(0, len(df.columns), 2)]

processed_pairs = []
for idx, group in enumerate(pair_groups, 1):
    # 拆分标签列的标签与编码
    group[["label", "code"]] = group.iloc[:, 0].str.split(":", expand=True)
    # 重命名列并丢弃原标签列
    group = group.rename(columns={
        group.columns[1]: f"letter_{idx}",
        "label": f"label_{idx}",
        "code": f"code_{idx}"
    }).drop(columns=group.columns[0])
    processed_pairs.append(group)

# 合并所有处理后的列组
result = pd.concat(processed_pairs, axis=1)

方法2：melt+pivot重塑（大规模数据更高效） #

利用Pandas内置的C级重塑操作，避免Python循环开销：

import pandas as pd

df = pd.read_excel("data.xlsx", header=None, sheet_name="sheet1")
df = df.drop(columns=0)

# 添加行索引用于后续还原
df["row_id"] = df.index

# 转换为长格式，标记每个列组的序号和类型
melted = df.melt(id_vars="row_id", var_name="col_idx", value_name="value")
melted["pair_num"] = melted["col_idx"].apply(lambda x: x//2 + 1)
melted["col_type"] = melted["col_idx"].apply(lambda x: "label_code" if x % 2 == 0 else "letter")

# 拆分标签编码列
label_code_df = melted[melted["col_type"] == "label_code"].copy()
label_code_df[["label", "code"]] = label_code_df["value"].str.split(":", expand=True)
label_code_df = label_code_df.drop(columns=["value", "col_type"])

# 提取字母列并合并
letter_df = melted[melted["col_type"] == "letter"].rename(columns={"value": "letter"}).drop(columns=["col_type"])
merged = pd.merge(label_code_df, letter_df, on=["row_id", "pair_num"])

# 转换回宽格式并重命名列
result = merged.pivot(
    index="row_id",
    columns="pair_num",
    values=["letter", "label", "code"]
).sort_index(axis=1, level=1)

result.columns = [f"{col[0]}_{col[1]}" for col in result.columns]
result = result.reset_index(drop=True)

方案说明 #

• 方法1适合中等规模数据，代码逻辑清晰易维护
• 方法2针对15000列的超大规模数据性能更优，因为Pandas的melt/pivot是底层优化的操作，比Python循环快数倍

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