如何将含文本响应的Pandas长表高效转换为宽表？

步骤说明 #

1. 提取基础信息：每个ID对应的基础字段（S.No、Name、User Function等）在原始数据中重复出现，仅保留每个ID的首行有效数据即可。
2. 分别转换三类问题-回答组：对Primary、Secondary、Feedback这三组的问题和回答，通过pivot将问题转为列名，回答作为对应列的值，并添加前缀区分类别。
3. 合并数据：将基础信息表与三个转换后的问题表按ID合并，得到最终的单行结构数据。

完整代码 #

import pandas as pd

# 加载数据集
df = pd.DataFrame({'S.No': {0: 63.0, 1: pd.NA, 2: pd.NA, 3: pd.NA, 4: 204.0, 5: pd.NA, 6: pd.NA, 7: pd.NA, 8: 238.0, 9: pd.NA, 10: pd.NA, 11: pd.NA, 12: 292.0, 13: pd.NA, 14: pd.NA, 15: pd.NA, 16: 332.0, 17: pd.NA, 18: pd.NA, 19: pd.NA}, 'ID': {0: 251, 1: 251, 2: 251, 3: 251, 4: 252, 5: 252, 6: 252, 7: 252, 8: 253, 9: 253, 10: 253, 11: 253, 12: 254, 13: 254, 14: 254, 15: 254, 16: 255, 17: 255, 18: 255, 19: 255}, 'Name': {0: 'Bob', 1: 'Bob', 2: 'Bob', 3: 'Bob', 4: 'Foo', 5: 'Foo', 6: 'Foo', 7: 'Foo', 8: 'Mike', 9: 'Mike', 10: 'Mike', 11: 'Mike', 12: 'Mary', 13: 'Mary', 14: 'Mary', 15: 'Mary', 16: 'Bar', 17: 'Bar', 18: 'Bar', 19: 'Bar'}, 'User Function': {0: 'Sales', 1: 'Sales', 2: 'Sales', 3: 'Sales', 4: 'Mktg', 5: 'Mktg', 6: 'Mktg', 7: 'Mktg', 8: 'Finance', 9: 'Finance', 10: 'Finance', 11: 'Finance', 12: 'Sales', 13: 'Sales', 14: 'Sales', 15: 'Sales', 16: 'Mktg', 17: 'Mktg', 18: 'Mktg', 19: 'Mktg'}, 'Business Unit': {0: 'BU1', 1: 'BU1', 2: 'BU1', 3: 'BU1', 4: 'BU2', 5: 'BU2', 6: 'BU2', 7: 'BU2', 8: 'BU3', 9: 'BU3', 10: 'BU3', 11: 'BU3', 12: 'BU1', 13: 'BU1', 14: 'BU1', 15: 'BU1', 16: 'BU2', 17: 'BU2', 18: 'BU2', 19: 'BU2'}, 'Gender': {0: 'Male', 1: 'Male', 2: 'Male', 3: 'Male', 4: 'Male', 5: 'Male', 6: 'Male', 7: 'Male', 8: 'Male', 9: 'Male', 10: 'Male', 11: 'Male', 12: 'Female', 13: 'Female', 14: 'Female', 15: 'Female', 16: 'Male', 17: 'Male', 18: 'Male', 19: 'Male'}, 'Primary Exit Reason': {0: 'Policy', 1: 'Policy', 2: 'Policy', 3: 'Policy', 4: 'Team', 5: 'Team', 6: 'Team', 7: 'Team', 8: 'Navigation', 9: 'Navigation', 10: 'Navigation', 11: 'Navigation', 12: 'Others', 13: 'Others', 14: 'Others', 15: 'Others', 16: 'Policy', 17: 'Policy', 18: 'Policy', 19: 'Policy'}, 'Primary Question': {0: 'A', 1: 'B', 2: 'C', 3: 'D', 4: 'E', 5: 'F', 6: 'G', 7: 'H', 8: 'I', 9: 'J', 10: 'K', 11: 'L', 12: 'M', 13: 'N', 14: 'O', 15: 'P', 16: 'A', 17: 'B', 18: 'C', 19: 'D'}, 'Primary Response': {0: pd.NA, 1: pd.NA, 2: pd.NA, 3: pd.NA, 4: 'Agree', 5: 'Agree', 6: 'No', 7: pd.NA, 8: 'Agree', 9: 'Agree', 10: 'No', 11: pd.NA, 12: pd.NA, 13: pd.NA, 14: pd.NA, 15: pd.NA, 16: pd.NA, 17: pd.NA, 18: pd.NA, 19: pd.NA}, 'Secondary Exit Reason': {0: 'Policy', 1: 'Policy', 2: 'Policy', 3: 'Policy', 4: 'Others', 5: 'Others', 6: 'Others', 7: 'Others', 8: 'Transport', 9: 'Transport', 10: 'Transport', 11: 'Transport', 12: 'Policy', 13: 'Policy', 14: 'Policy', 15: 'Policy', 16: 'Policy', 17: 'Policy', 18: 'Policy', 19: 'Policy'}, 'Secondary Question': {0: 'A', 1: 'B', 2: 'C', 3: 'D', 4: 'M', 5: 'N', 6: 'O', 7: 'P', 8: 'Q', 9: 'R', 10: 'S', 11: 'T', 12: 'A', 13: 'B', 14: 'C', 15: 'D', 16: 'A', 17: 'B', 18: 'C', 19: 'D'}, 'Secondary Response': {0: 'Agree', 1: 'Agree', 2: 'Yes', 3: pd.NA, 4: pd.NA, 5: pd.NA, 6: pd.NA, 7: pd.NA, 8: pd.NA, 9: pd.NA, 10: pd.NA, 11: pd.NA, 12: 'Agree', 13: 'Agree', 14: 'No', 15: pd.NA, 16: 'Highly Agree', 17: 'Agree', 18: "I'm unaware", 19: pd.NA}, 'Feedback Question': {0: 'A', 1: 'B', 2: 'C', 3: 'D', 4: 'A', 5: 'B', 6: 'C', 7: 'D', 8: 'A', 9: 'B', 10: 'C', 11: 'D', 12: 'A', 13: 'B', 14: 'C', 15: 'D', 16: 'A', 17: 'B', 18: 'C', 19: 'D'}, 'Feedback Reason': {0: '4', 1: '8', 2: pd.NA, 3: pd.NA, 4: '4', 5: '7', 6: 'NO ALL GOOD', 7: 'NO\n', 8: '4', 9: '6', 10: 'No', 11: 'No', 12: '5', 13: '6', 14: 'No', 15: 'No', 16: '5', 17: '10', 18: 'YES GOOD XP', 19: 'Yes'}})

# 1. 提取基础信息：每个ID取首行的非问题类字段
base_cols = ['S.No', 'ID', 'Name', 'User Function', 'Business Unit', 'Gender', 'Primary Exit Reason', 'Secondary Exit Reason']
base_df = df.groupby('ID')[base_cols].first().reset_index()

# 2. 处理Primary问题-回答组
primary_pivot = df.pivot(index='ID', columns='Primary Question', values='Primary Response')
primary_pivot.columns = [f'Primary_{col}' for col in primary_pivot.columns]
primary_pivot = primary_pivot.reset_index()

# 3. 处理Secondary问题-回答组
secondary_pivot = df.pivot(index='ID', columns='Secondary Question', values='Secondary Response')
secondary_pivot.columns = [f'Secondary_{col}' for col in secondary_pivot.columns]
secondary_pivot = secondary_pivot.reset_index()

# 4. 处理Feedback问题-回答组
feedback_pivot = df.pivot(index='ID', columns='Feedback Question', values='Feedback Reason')
feedback_pivot.columns = [f'Feedback_{col}' for col in feedback_pivot.columns]
feedback_pivot = feedback_pivot.reset_index()

# 5. 合并所有表
final_df = base_df.merge(primary_pivot, on='ID') \
                  .merge(secondary_pivot, on='ID') \
                  .merge(feedback_pivot, on='ID')

# 查看结果
print(final_df)

关键细节 #

• 基础字段提取用groupby('ID').first()，自动保留每个ID的首行有效数据（比如S.No的非空值）。
• 每个问题组的pivot操作将问题转为列，确保每个ID对应一行，回答填充到对应问题列下。
• 给转换后的列添加前缀（如Primary_A），避免不同类别问题重名导致的冲突。

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