Pivot -将具有重复行的垂直数据转置为每个ID具有一行的水平数据

Question

我有作为垂直数据框架导出的调查数据。意思是一个人每次回答调查中的3个问题，他们的行将重复3次，除了问题的内容和他们的答案。我试图转置/枢轴此数据，以便所有3个问题都显示在一个唯一的列中，以便他们的回答也显示在每列中，而不是另一行，以及他们的详细信息，如ID，全名，位置等…

现在是这样的:

ID      Full Name   Location   Question                               Multiple Choice Question Answers 
12345   John Smith     UK    1. It was easy to report my sickness.             Agree
12345   John Smith     UK    2. I felt ready to return from Quarantine.        Neutral
12345   John Smith     UK    3. I am satisfied with the adjustments made.      Disagree
..          ...          ...                   ...         ...   
67891  Jane Smith      UK    1. It was easy to report my sickness.             Agree
67891  Jane Smith      UK    2. I felt ready to return from Quarantine.        Agree
67891  Jane Smith      UK    3. I am satisfied with the adjustments made.      Agree      

，这是我想要的:

ID      Full Name   Location  1. It was easy to report my sickness. 2. I was satisfied with the support I received.  3. I felt ready to return from Quarantine.
12345   John Smith     UK         Agree                                  Neutral                                          Disagree
67891   Jane Smith     UK         Agree                                  Agree                                            Disagree

目前我正试图使用此代码来获得我想要的输出，但我只能获得id和全名隔离而不复制和其他列只是显示为单独的行。

column_indices1 = [2,3,4]
df5 = df4.pivot_table(index = ['ID', 'Full Name'], columns = df4.iloc[:, column_indices1], 
values = 'Multiple Choice Question Answer', 
fill_value = 0)

Answer 1

概念

在这个场景中，我们应该考虑使用:

pivot():主没有聚合可以处理非数字数据.

---

练习

准备数据

data = {'ID':[12345,12345,12345,67891,67891,67891],
'Full Name':['John Smith','John Smith','John Smith','Jane Smith','Jane Smith','Jane Smith'],
'Location':['UK','UK','UK','UK','UK','UK'],
'Question':['Q1','Q2','Q3','Q1','Q2','Q3'],
'Answers':['Agree','Neutral','Disagree','Agree','Agree','Agree']}
df = pd.DataFrame(data=data)
df

输出

<表类>ID全名位置问题答案tbody><<tr>012345约翰·史密斯道明>Q1同意112345约翰·史密斯英国Q2中性212345约翰·史密斯英国第三季度不同意367891简·史密斯道明>Q1同意467891简·史密斯道明>Q2同意567891简·史密斯道明>第三季度同意