我有如下数据
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')
必须出现重复的属性(列)。请问我有什么想法吗
请您参考如下方法:
首先在 pandas 中最好避免重复的列名,因此可能的解决方案是使用 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
