How to transpose pandas dataframe to cross-tabulate dataframe keeping all values

Question

Let's suppose we have such dataframe:

df = pd.DataFrame({'key' : ['one', 'two', 'three', 'four'] * 3,
                   'col' : ['A', 'B', 'C'] * 4,
                   'val1' : np.random.randn(12),
                   'val2' : np.random.randn(12),
                   'val3' : np.random.randn(12)})

key + col is unique key

I want to make col values to become columns split or to cross-tabulate on them and finally to looks something like this:

First naive approach pd.crosstab(df.key,df.col) didn't work here well:

This code pd.crosstab(df.key,df.col,values = df[['val1', 'val2', 'val3']], aggfunc = np.max) failed to run with ValueError: Wrong number of items passed 3, placement implies 1

How get it work?

I think you might find [this question](https://stackoverflow.com/questions/37835508/how-to-do-multi-column-from-tuples) useful. It looks like you want to use a `MultiIndex` approach. — Jack Moody, Dec 13 '18 at 14:56

jezrael · Accepted Answer · 2018-12-13T15:05:01.153

Use pivot_table with swaplevel and sort_index with aggregate function np.max:

df = (df.pivot_table(index='key', columns='col', aggfunc=np.max)
       .swaplevel(0,1,axis=1)
       .sort_index(axis=1))

Alternative is aggregate by GroupBy.max:

df = (df.groupby(['key', 'col'])
        .max()
        .unstack()
        .swaplevel(0,1,axis=1)
        .sort_index(axis=1))

print (df)
col           A                             B                             C  \
           val1      val2      val3      val1      val2      val3      val1   
key                                                                           
four  -0.225967  0.362041  0.040915 -1.227718 -0.879248 -1.279912 -1.577218   
one   -0.187167  1.530731 -1.112116 -0.871077 -2.099876 -0.069297 -0.351971   
three -0.165375 -0.378049 -0.390724  0.484519 -0.408990 -1.496042  0.590083   
two    1.923084 -0.688284  1.702659 -0.159921  0.635245  0.623821 -1.503893   

col                        
           val2      val3  
key                        
four  -1.135872  0.645371  
one    2.347472  0.129252  
three  0.402825  0.883710  
two   -0.132847  0.179476

I like the simplicity of the `pivot_table` – Rocketq Dec 13 '18 at 15:30 — Rocketq, Dec 13 '18 at 15:30

Scott Boston · Answer 2 · 2018-12-13T15:15:28.040

Use melt, set_index, and unstack, this will only work if you expected on value per cell, otherwise you can use second option to aggregate values:

df.melt(['key','col'])\
  .set_index(['key','col','variable'])['value']\
  .unstack([1,2])\
  .sort_index(axis=1)

Output:

col              A                             B                             C                    
variable      val1      val2      val3      val1      val2      val3      val1      val2      val3
key                                                                                               
four     -1.964246  0.958854 -0.605128  0.055120 -1.144306 -0.800712 -0.917324 -0.581882 -0.152399
one       0.513347 -1.689448 -2.434481  0.990924 -1.014848  0.713703  1.344299  0.052877  1.174183
three    -0.156336 -0.156157 -2.253689  0.877726 -0.686758 -0.407892  0.816636  1.008870 -0.390872
two       1.942495  1.811712 -0.762283 -2.169613 -1.073372  0.201996 -1.073370 -0.902032 -0.168796

Another option using melt and pd.crosstab:

df1 = df.melt(['key','col'])
pd.crosstab(df1.key, [df1.col, df1.variable], df1.value, aggfunc=np.max)

Output:

col              A                             B                             C                    
variable      val1      val2      val3      val1      val2      val3      val1      val2      val3
key                                                                                               
four     -1.964246  0.958854 -0.605128  0.055120 -1.144306 -0.800712 -0.917324 -0.581882 -0.152399
one       0.513347 -1.689448 -2.434481  0.990924 -1.014848  0.713703  1.344299  0.052877  1.174183
three    -0.156336 -0.156157 -2.253689  0.877726 -0.686758 -0.407892  0.816636  1.008870 -0.390872
two       1.942495  1.811712 -0.762283 -2.169613 -1.073372  0.201996 -1.073370 -0.902032 -0.168796

How to transpose pandas dataframe to cross-tabulate dataframe keeping all values

2 Answers2