Pivot_table for same column names - Must repeat after pivot

Question

I have data as below

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

This is how it is stored in sql database. I am reading this into a dataframe and trying to generate the data as below

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

However, I am unable to get this

The age does not repeat and appears only once and takes any one of those values

This is what I have tried -

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

Repeated attribute(column ) must appear. May I have any ideas on this please

jezrael · Accepted Answer · 2019-09-11T12:36:32.240

1

First in pandas is best avoid duplicated columns names, so possible solution is deduplicate repeated values with 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

edited Sep 11 '19 at 12:36

answered Sep 11 '19 at 07:48

jezrael

822,522
95
1,334
1,252

Thanks @jezrael. I will try this. However, out of curiosity can I not do this in pivot_table() – Rathi Rao Sep 11 '19 at 09:03
@RathiRao - because `pivot_table` is used for aggregation, if not possible use `pivot` – jezrael Sep 11 '19 at 09:04
@RathiRao - better it is explain [here](https://stackoverflow.com/questions/47152691/how-to-pivot-a-dataframe) – jezrael Sep 11 '19 at 09:11
Thank you @jezrael. I used the pivot_table with lambda. So, I am curious to know if a lambda can be written to suffice my requirement? – Rathi Rao Sep 11 '19 at 09:15
@RathiRao - Not possible, here lambda not working for your expcted output. – jezrael Sep 11 '19 at 09:19
in your solution can I have multple value for index? User, firstname, lastname and then the rest of the columns – Rathi Rao Sep 11 '19 at 09:36
@RathiRao - Answer is edited for new column - here is used `pivot_table` with `sum`. – jezrael Sep 11 '19 at 12:40

Pivot_table for same column names - Must repeat after pivot

1 Answers1