相同列名的透视表必须在pi之后重复

Question

我有如下数据

user  region  attribute   reading
Jon   Europe  fathername  peter
Jon   Europe  age         50
Jon   Europe  mothername  mary
Jon   Europe  age         44
Jon   Europe  brothername duke
Jon   Europe  age         25

这就是它在sql数据库中的存储方式。我正在把它读入一个数据帧，并试图生成如下所示的数据

attribute             fathername age mothername age brothername age     
User      region
Don       Europe      peter      50   mary      44  duke         25

然而，我无法得到这个

年龄不会重复，只出现一次，并且取其中任何一个值

这就是我试过的-

pd.pivot_table(df_mysql , index=['User'],columns=['attribute'],values=['reading'], aggfunc=lambda x: x,dropna = 'False')

必须出现重复的属性（列）。我能对这个有什么想法吗

Answer 1

首先，最好避免重复的列名，因此可能的解决方案是使用pivot消除重复值：

print (df)
    user  region    attribute reading
0    Jon  Europe   fathername   peter
1    Jon  Europe          age      50
2    Jon  Europe   mothername    mary
3    Jon  Europe          age      44
4    Jon  Europe  brothername    duke
5    Jon  Europe          age      25
6   Jon1  Europe   fathername   peter
7   Jon1  Europe          age      50
8   Jon1  Europe   mothername    mary
9   Jon1  Europe          age      44
10  Jon1  Europe  brothername    duke
11  Jon1  Europe          age      25

m = df.duplicated(['user','region', 'attribute'], keep=False)
df.loc[m, 'attribute'] += df[m].groupby(['user','region', 'attribute']).cumcount().astype(str)

df = df.pivot_table(index=['user','region'],
                    columns='attribute',
                    values='reading',
                    aggfunc='sum').reindex(df['attribute'].unique(), axis=1)
print (df)
attribute   fathername age0 mothername age1 brothername age2
user region                                                 
Jon  Europe      peter   50       mary   44        duke   25
Jon1 Europe      peter   50       mary   44        duke   25