pivot_table on multi-indexed dataframe

Question

How can I apply pandas.pivot_table to the dataframe:

df = pd.DataFrame(
[
    {'o1_pkid': 645, 'o2_pkid': 897, 'colname': 'col1', 'colvalue': 'sfjdka'},
    {'o1_pkid': 645, 'o2_pkid': 897, 'colname': 'col2', 'colvalue': 25},
    {'o1_pkid': 645, 'o2_pkid': 159, 'colname': 'col1', 'colvalue': 'laksjd'},
    {'o1_pkid': 645, 'o2_pkid': 159, 'colname': 'col2', 'colvalue': 26}
]

)

to get a multi-indexed result (indexed by o1_pkid and o2_pkid), where the columns come from colname and the values come from colvalue? I am looking to get a result something like:

colname                 col1          col2
o1_pkid      o2_pkid 
645          897      'sfjdka'      25
             159      'laksjd'      26

jezrael · Accepted Answer · 2017-05-09 15:28:26Z

Use set_index + unstack:

df = df.set_index(['o1_pkid', 'o2_pkid', 'colname'])['colvalue'].unstack()

print (df)
colname            col1 col2
o1_pkid o2_pkid             
645     159      laksjd   26
        897      sfjdka   25

But if get error:

ValueError: Index contains duplicate entries, cannot reshape

need:

pivot_table with some aggregate function like sum:

df = pd.DataFrame(
[
    {'o1_pkid': 645, 'o2_pkid': 897, 'colname': 'col1', 'colvalue': 'sfjdka'},
    {'o1_pkid': 645, 'o2_pkid': 897, 'colname': 'col2', 'colvalue': 25},
    {'o1_pkid': 645, 'o2_pkid': 159, 'colname': 'col1', 'colvalue': 'laksjd'},
    {'o1_pkid': 645, 'o2_pkid': 159, 'colname': 'col2', 'colvalue': 10},
    {'o1_pkid': 645, 'o2_pkid': 159, 'colname': 'col2', 'colvalue': 26}
])


df = df.pivot_table(index=['o1_pkid', 'o2_pkid'], 
                        columns='colname', 
                        values='colvalue', 
                        aggfunc='sum')
print (df)
colname            col1 col2
o1_pkid o2_pkid             
645     159      laksjd   36
        897      sfjdka   25

or groupby + aggregate function + unstack:

df = df.groupby(['o1_pkid', 'o2_pkid', 'colname'])['colvalue'].sum().unstack()

print (df)
colname            col1 col2
o1_pkid o2_pkid             
645     159      laksjd   36
        897      sfjdka   25

Collectives™ on Stack Overflow

pivot_table on multi-indexed dataframe

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related