Python中Pivot Table实现：列值转列并指定列顺序

我有一个包含3000+行数据的Python DataFrame，子集示例如下：

# 注：原代码为R格式，修正为Python pandas写法
import pandas as pd

df = pd.DataFrame({
    "Name": ["John", "Karla", "Sandy", "John", "John", "Sandy"],
    "Course Title": ["Training 2", "Training", "Training 2", "Training 5", "Training 2", "Training 2"],
    "Start Date": ["2022-11-08", "2022-11-25", "2023-02-09", "2023-03-15", "2023-03-19", "2023-03-19"],
    "Completion Date": ["2022-11-09", "2022-11-28", "2023-02-09", "2023-03-20", "2023-03-21", "2023-03-19"]
})

需要按Name分组透视，每个Name对应一行：

• 将Course Title的取值作为列名，列值标记用户是否完成该培训（用1表示完成，NaN表示未完成）
• 每个培训列后跟随对应的Start Date和Completion Date列，列顺序需符合如下期望结果：

# 期望结果的Python写法
df_pivot = pd.DataFrame({
    "Name": ["John", "Karla", "Sandy"],
    "Training": [1, 1, 1],
    "Training_Start Date": ["2022-11-08", "2022-11-25", "2023-03-19"],
    "Training_Completion Date": ["2022-11-09", "2022-11-28", "2023-03-19"],
    "Training 2": [1, pd.NA, 1],
    "Training 2_Start Date": ["2023-03-19", pd.NA, "2023-02-09"],
    "Training 2_Completion Date": ["2023-03-21", pd.NA, "2023-02-09"],
    "Training 5": [1, pd.NA, pd.NA],
    "Training 5_Start Date": ["2023-03-15", pd.NA, pd.NA],
    "Training 5_Completion Date": ["2023-03-20", pd.NA, pd.NA]
})

报错代码及错误信息 #

我尝试了以下代码，但在重新排列列时出现KeyError：

# Pivot the dataframe
df_pivot = df.pivot_table(index='Name', columns='Course Title',
                          values=['Course Title', 'Start Date', 'Completion Date'],
                          aggfunc={'Course Title': 'count', 'Start Date': 'first', 'Completion Date': 'first'})

# Flatten the column names
df_pivot.columns = [f'{col[1]}_{col[0]}' if col[0] != '' else col[1] for col in df_pivot.columns]

# Reset the index
df_pivot = df_pivot.reset_index()

# Reorder the columns
columns = ['Name']
for title in df['Course Title'].unique():
    columns.append(title)
    columns.append(f'{title}_Start Date')
    columns.append(f'{title}_Completion Date')

df_pivot = df_pivot[columns]

错误回溯：

KeyError                                  Traceback (most recent call last)
 in 
----> 1 df_pivot = df_pivot[columns]

C:\ProgramData\Anaconda3\lib\site-packages\pandas\core\frame.py in __getitem__(self, key)
2906             if is_iterator(key):
2907                 key = list(key)
-> 2908             indexer = self.loc._get_listlike_indexer(key, axis=1, raise_missing=True)[1]
2909
2910         # take() does not accept boolean indexers

C:\ProgramData\Anaconda3\lib\site-packages\pandas\core\indexing.py in _get_listlike_indexer(self, key, axis, raise_missing)
1252             keyarr, indexer, new_indexer = ax._reindex_non_unique(keyarr)
1253
-> 1254         self._validate_read_indexer(keyarr, indexer, axis, raise_missing=raise_missing)
1255         return keyarr, indexer
1256

C:\ProgramData\Anaconda3\lib\site-packages\pandas\core\indexing.py in _validate_read_indexer(self, key, indexer, axis, raise_missing)
1302             if raise_missing:
1303                 not_found = list(set(key) - set(ax))
-> 1304                 raise KeyError(f"{not_found} not in index")
1305
1306             # we skip the warning on Categorical

问题原因及解决方案 #

问题原因 #

报错核心是列名不匹配：

1. 透视后扁平化列名时，Course Title对应的列被命名为{Course Title}_Course Title，但后续排序时直接使用了Course Title本身（如Training），导致找不到对应列。
2. df['Course Title'].unique()的返回顺序可能和透视后的列顺序不一致，进一步引发匹配问题。

修正后的代码 #

import pandas as pd

# 构造示例数据
df = pd.DataFrame({
    "Name": ["John", "Karla", "Sandy", "John", "John", "Sandy"],
    "Course Title": ["Training 2", "Training", "Training 2", "Training 5", "Training 2", "Training 2"],
    "Start Date": ["2022-11-08", "2022-11-25", "2023-02-09", "2023-03-15", "2023-03-19", "2023-03-19"],
    "Completion Date": ["2022-11-09", "2022-11-28", "2023-02-09", "2023-03-20", "2023-03-21", "2023-03-19"]
})

# 1. 新增标记列，明确表示是否完成培训
df['Completed'] = 1

# 2. 拆分透视任务，分别处理标记列和日期列
pivot_completed = df.pivot_table(index='Name', columns='Course Title', values='Completed', aggfunc='max').fillna(pd.NA)
pivot_start = df.pivot_table(index='Name', columns='Course Title', values='Start Date', aggfunc='first').fillna(pd.NA)
pivot_completion = df.pivot_table(index='Name', columns='Course Title', values='Completion Date', aggfunc='first').fillna(pd.NA)

# 3. 重命名日期列，添加对应后缀
pivot_start.columns = [f'{col}_Start Date' for col in pivot_start.columns]
pivot_completion.columns = [f'{col}_Completion Date' for col in pivot_completion.columns]

# 4. 合并所有透视表并重置索引
df_pivot = pd.concat([pivot_completed, pivot_start, pivot_completion], axis=1).reset_index()

# 5. 按要求排序列，同时去重避免重复课程导致的列重复
columns = ['Name']
for title in df['Course Title'].unique():
    columns.append(title)
    columns.append(f'{title}_Start Date')
    columns.append(f'{title}_Completion Date')
columns = list(dict.fromkeys(columns))  # 去重并保留顺序
df_pivot = df_pivot[columns]

print(df_pivot)

代码说明 #

• 新增Completed列单独标记培训完成状态，避免和原Course Title列混淆。
• 拆分透视任务，分别处理标记和日期数据，逻辑更清晰，避免列名混乱。
• 合并后按指定顺序排列列，通过dict.fromkeys去重，确保列名唯一。
• 用fillna(pd.NA)统一缺失值标记，匹配期望结果格式。

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