如何使用Python将宽表特定列转成行并重复其余列数据？

原始数据 #

转换目标 #

将上述宽表转换为长表，保留ID和SCHOOL列的重复值，拆分出每个人的多门科目成绩，最终格式如下：

Python实现方案 #

使用pandas库可以高效完成这类宽表转长表的操作，以下提供两种实现方式：

方式一：针对固定人员分组处理 #

import pandas as pd

# 构造原始数据
data = {
    'ID': [1, 2],
    'SCHOOL': ['S1', 'S2'],
    'Name1': ['Mr. ABC', 'Mr. PQR'],
    'Name1 Subject1': ['Math', 'Math'],
    'Name1 Grade1': [6, 10],
    'Name1 Subject2': ['Science', 'Science'],
    'Name1 Grade2': [7, 11],
    'Name2': ['Mr. XYZ', 'Mr. KLM'],
    'Name2 Subject1': ['Social', 'Social'],
    'Name2 Grade1': [8, 8],
    'Name2 Subject2': ['EVS', 'EVS'],
    'Name2 Grade2': [9, 9]
}

df = pd.DataFrame(data)

# 处理Name1分组
name1_df = df[['ID', 'SCHOOL', 'Name1', 'Name1 Subject1', 'Name1 Grade1', 'Name1 Subject2', 'Name1 Grade2']].rename(
    columns={
        'Name1': 'Name',
        'Name1 Subject1': 'Subject1',
        'Name1 Grade1': 'Grade1',
        'Name1 Subject2': 'Subject2',
        'Name1 Grade2': 'Grade2'
    }
)
name1_long = name1_df.melt(
    id_vars=['ID', 'SCHOOL', 'Name'],
    value_vars=['Subject1', 'Subject2', 'Grade1', 'Grade2'],
    var_name='Type',
    value_name='Value'
)
name1_long['Type'] = name1_long['Type'].str.extract('(Subject|Grade)')
name1_final = name1_long.pivot_table(
    index=['ID', 'SCHOOL', 'Name'],
    columns='Type',
    values='Value',
    aggfunc='first'
).reset_index()

# 处理Name2分组
name2_df = df[['ID', 'SCHOOL', 'Name2', 'Name2 Subject1', 'Name2 Grade1', 'Name2 Subject2', 'Name2 Grade2']].rename(
    columns={
        'Name2': 'Name',
        'Name2 Subject1': 'Subject1',
        'Name2 Grade1': 'Grade1',
        'Name2 Subject2': 'Subject2',
        'Name2 Grade2': 'Grade2'
    }
)
name2_long = name2_df.melt(
    id_vars=['ID', 'SCHOOL', 'Name'],
    value_vars=['Subject1', 'Subject2', 'Grade1', 'Grade2'],
    var_name='Type',
    value_name='Value'
)
name2_long['Type'] = name2_long['Type'].str.extract('(Subject|Grade)')
name2_final = name2_long.pivot_table(
    index=['ID', 'SCHOOL', 'Name'],
    columns='Type',
    values='Value',
    aggfunc='first'
).reset_index()

# 合并结果并转换Grade类型
final_df = pd.concat([name1_final, name2_final], ignore_index=True)
final_df['Grade'] = final_df['Grade'].astype(int)

# 输出Markdown格式结果
print(final_df.to_markdown(index=False))

方式二：批量处理任意数量人员分组 #

如果存在Name1到NameN多组人员，可通过循环批量处理：

import pandas as pd

data = {
    'ID': [1, 2],
    'SCHOOL': ['S1', 'S2'],
    'Name1': ['Mr. ABC', 'Mr. PQR'],
    'Name1 Subject1': ['Math', 'Math'],
    'Name1 Grade1': [6, 10],
    'Name1 Subject2': ['Science', 'Science'],
    'Name1 Grade2': [7, 11],
    'Name2': ['Mr. XYZ', 'Mr. KLM'],
    'Name2 Subject1': ['Social', 'Social'],
    'Name2 Grade1': [8, 8],
    'Name2 Subject2': ['EVS', 'EVS'],
    'Name2 Grade2': [9, 9]
}

df = pd.DataFrame(data)

# 提取所有人员分组（Name1、Name2...）
name_groups = sorted(list(set(col.split()[0] for col in df.columns if col.startswith('Name'))))

final_dfs = []
for group in name_groups:
    # 筛选当前分组的列并重命名
    group_cols = [col for col in df.columns if col.startswith(group)]
    rename_map = {col: col.replace(f"{group} ", "") if " " in col else "Name" for col in group_cols}
    temp_df = df[['ID', 'SCHOOL'] + group_cols].rename(columns=rename_map)
    
    # 宽表转长表并拆分科目/成绩类型
    temp_long = temp_df.melt(
        id_vars=['ID', 'SCHOOL', 'Name'],
        value_vars=[col for col in temp_df.columns if col not in ['ID', 'SCHOOL', 'Name']],
        var_name='Type',
        value_name='Value'
    )
    temp_long['Type'] = temp_long['Type'].str.extract('(Subject|Grade)')
    
    # 合并科目与成绩对应行
    temp_final = temp_long.pivot_table(
        index=['ID', 'SCHOOL', 'Name'],
        columns='Type',
        values='Value',
        aggfunc='first'
    ).reset_index()
    final_dfs.append(temp_final)

# 合并所有分组结果
final_df = pd.concat(final_dfs, ignore_index=True)
final_df['Grade'] = final_df['Grade'].astype(int)

# 输出结果
print(final_df.to_markdown(index=False))

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