如何用Pandas将K-V列对转换为宽表并处理空单元格？

需求说明 #

原数据是按K1/V1、K2/V2...分组的宽格式，需要转换为以COL1/COL2/COL3/COL4为列名、对应TEST值填充的结构，无对应值的位置填NULL。

Excel解决方案（Power Query实现） #

1. 选中原始数据区域，点击「数据」→「从表格/区域」，导入Power Query编辑器。
2. 选中所有K开头的列（K1、K2、K3...），点击「转换」→「逆透视列」→「逆透视其他列」，生成包含属性（原K列名）、值（COLx）的列。
3. 再选中所有V开头的列（V1、V2、V3...），重复逆透视操作，生成属性.1（原V列名）、值.1（TEST值）的列。
4. 添加两个自定义列，提取属性和属性.1中的数字：
   • 自定义列1：=Text.After([属性], "K")
   • 自定义列2：=Text.After([属性.1], "V")
5. 筛选出两个自定义列值相等的行，删除多余的属性、属性.1及自定义列，保留Id、值（COLx）、值.1（TEST值）。
6. 点击「转换」→「透视列」，设置：
   • 值列：值.1
   • 列名：值
   • 缺失值：填入NULL
7. 关闭并上载到Excel，即可得到目标格式。

Python Pandas解决方案 #

直接用代码实现数据重塑：

import pandas as pd

# 构造样本数据（实际使用时可替换为pd.read_excel/pd.read_csv读取数据）
df = pd.DataFrame({
    'Id': [1, 2, 3, 4],
    'K1': ['COL1', 'COL2', 'COL4', 'COL3'],
    'V1': ['TEST1', 'TEST4', 'TEST2', 'TEST5'],
    'K2': ['COL2', 'COL1', 'COL2', 'COL2'],
    'V2': ['TEST2', 'TEST2', 'TEST1', 'TEST5'],
    'K3': ['COL3', 'COL3', 'COL1', 'COL4'],
    'V3': ['TEST3', 'TEST4', 'TEST2', 'TEST1']
})

# 将每组K/V列转换为长格式
long_data = []
for num in range(1, 4):  # 按实际K/V组数调整范围
    temp = df[['Id', f'K{num}', f'V{num}']].rename(columns={f'K{num}': 'COL', f'V{num}': 'VALUE'})
    long_data.append(temp)
long_df = pd.concat(long_data, ignore_index=True)

# 透视转换为目标宽格式，空值填充为NULL
result = long_df.pivot(index='Id', columns='COL', values='VALUE').reset_index().fillna('NULL')

# 按COL1-COL4排序列（可选）
result = result[['Id', 'COL1', 'COL2', 'COL3', 'COL4']]
print(result)

运行后输出结果完全匹配需求格式。

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