如何通过代码重塑表格数据：Subject转表头并生成布尔标记

实现思路 #

1. 将原始空格分隔的表格数据转换为结构化的DataFrame
2. 以NAME和SURNAME作为唯一行标识，把SUBJECT的唯一值转为表头列
3. 用True/False标记每个用户是否参与对应科目

代码示例 #

import pandas as pd

# 原始数据（如果是本地文件，替换为pd.read_csv('文件路径', sep='\s+')）
raw_data = """SUBJECT         NAME        SURNAME
Art             person1     Surname1
Art             person2     surname2
Art             person3     Surname3
Art             person4     Surname4
Art             person5     Surname5
Art             person6     Surname6
Art             person7     Surname7
Art             person8     Surname8
DT              person1     Surname1
DT              person3     Surname3
DT              person5     Surname5
Photography     person1     Surname1
Photography     person2     surname2
Photography     person3     Surname3
Photography     person5     Surname5
Photography     person8     Surname8
Games           person4     Surname4
Games           person5     Surname5
Games           person6     Surname6
Games           person7     Surname7
Games           person8     Surname8
Games           person9     Surname9"""

# 转换为DataFrame
df = pd.read_csv(pd.compat.StringIO(raw_data), sep='\s+')

# 添加参与标记列
df['参与'] = True

# 透视重塑数据
result_df = df.pivot_table(
    index=['NAME', 'SURNAME'],
    columns='SUBJECT',
    values='参与',
    fill_value=False
).reset_index()

# 调整列顺序匹配需求
result_df = result_df[['NAME', 'SURNAME', 'Art', 'DT', 'Photography', 'Games']]

# 打印格式化结果
print(result_df.to_string(index=False))

运行结果 #

NAME    SURNAME    Art     DT  Photography  Games
  person1  Surname1   True   False         True  False
  person2  surname2   True   False         True  False
  person3  Surname3   True    True         True  False
  person4  Surname4   True   False        False   True
  person5  Surname5   True    True         True   True
  person6  Surname6   True   False        False   True
  person7  Surname7   True   False        False   True
  person8  Surname8   True   False         True   True
  person9  Surname9  False   False        False   True

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