91

I cannot figure out how to do "reverse melt" using Pandas in python. This is my starting data

import pandas as pd

from StringIO import StringIO

origin = pd.read_table(StringIO('''label    type    value
x   a   1
x   b   2
x   c   3
y   a   4
y   b   5
y   c   6
z   a   7
z   b   8
z   c   9'''))

origin
Out[5]: 
  label type  value
0     x    a      1
1     x    b      2
2     x    c      3
3     y    a      4
4     y    b      5
5     y    c      6
6     z    a      7
7     z    b      8
8     z    c      9

This is the output I would like to have:

    label   a   b   c
        x   1   2   3
        y   4   5   6
        z   7   8   9

I'm sure there is an easy way to do this, but I don't know how.

smci
  • 29,564
  • 18
  • 109
  • 144
Boris Gorelik
  • 27,385
  • 36
  • 123
  • 169
  • 8
    [Docstring of melt](http://pandas.pydata.org/pandas-docs/stable/generated/pandas.melt.html): "Unpivots" a DataFrame... :) – Andy Hayden Mar 02 '14 at 19:42
  • 1
    StringIO has moved to `io` in python3. use `from io import StringIO` python3. – Daniel Mar 10 '17 at 06:59
  • 1
    I've provided several detailed examples and alternative approaches in this [**Q&A**](https://stackoverflow.com/q/47152691/2336654) – piRSquared Nov 11 '17 at 22:17

2 Answers2

119

there are a few ways;
using .pivot:

>>> origin.pivot(index='label', columns='type')['value']
type   a  b  c
label         
x      1  2  3
y      4  5  6
z      7  8  9

[3 rows x 3 columns]

using pivot_table:

>>> origin.pivot_table(values='value', index='label', columns='type')
       value      
type       a  b  c
label             
x          1  2  3
y          4  5  6
z          7  8  9

[3 rows x 3 columns]

or .groupby followed by .unstack:

>>> origin.groupby(['label', 'type'])['value'].aggregate('mean').unstack()
type   a  b  c
label         
x      1  2  3
y      4  5  6
z      7  8  9

[3 rows x 3 columns]
behzad.nouri
  • 69,003
  • 18
  • 120
  • 118
  • Great! I want to turn this into a simple dict now, with the index column also coming. How to do that? – Nikhil VJ Mar 16 '18 at 08:14
  • 1
    which of the above is most general? If, instead of there being a single value column, there had been many - which would typically be used? (pivot?) – baxx Feb 01 '20 at 17:29
  • Nice to see there are different ways of doing this, but based on what should one decide which one to use? – np8 Feb 03 '22 at 08:04
4

DataFrame.set_index + DataFrame.unstack

df.set_index(['label','type'])['value'].unstack()

type   a  b  c
label         
x      1  2  3
y      4  5  6
z      7  8  9

simplifying the passing of pivot arguments

df.pivot(*df)

type   a  b  c
label         
x      1  2  3
y      4  5  6
z      7  8  9

[*df]
#['label', 'type', 'value']

For expected output we need DataFrame.reset_index and DataFrame.rename_axis

df.pivot(*df).rename_axis(columns = None).reset_index()

  label  a  b  c
0     x  1  2  3
1     y  4  5  6
2     z  7  8  9

if there are duplicates in a,b columns we could lose information so we need GroupBy.cumcount

print(df)

  label type  value
0     x    a      1
1     x    b      2
2     x    c      3
3     y    a      4
4     y    b      5
5     y    c      6
6     z    a      7
7     z    b      8
8     z    c      9
0     x    a      1
1     x    b      2
2     x    c      3
3     y    a      4
4     y    b      5
5     y    c      6
6     z    a      7
7     z    b      8
8     z    c      9

df.pivot_table(index = ['label',
                        df.groupby(['label','type']).cumcount()],
               columns = 'type',
               values = 'value')


type     a  b  c
label           
x     0  1  2  3
      1  1  2  3
y     0  4  5  6
      1  4  5  6
z     0  7  8  9
      1  7  8  9

Or:

(df.assign(type_2 = df.groupby(['label','type']).cumcount())
   .set_index(['label','type','type_2'])['value']
   .unstack('type'))
Community
  • 1
  • 1
ansev
  • 28,746
  • 5
  • 11
  • 29