103

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

  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.

wjandrea
  • 28,235
  • 9
  • 60
  • 81
Boris Gorelik
  • 29,945
  • 39
  • 128
  • 170
  • 9
    [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
    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

3 Answers3

133

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]
wjandrea
  • 28,235
  • 9
  • 60
  • 81
behzad.nouri
  • 74,723
  • 18
  • 126
  • 124
  • 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
  • 2
    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? – Niko Föhr Feb 03 '22 at 08:04
8

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
  • 30,322
  • 5
  • 17
  • 31
0
 (
df.pivot(index="label",columns="type",values="value").
reset_index(level=0,drop=True)
)

Another method:

df.groupby(["label","type"])["value"].sum().unstack()

Another:

pd.crosstab(index=df.label,columns=df.type,values=df.value,aggfunc="su

Sajil Alakkalakath
  • 171
  • 1
  • 6
  • 2
    While this code may solve the question, [including an explanation](https://meta.stackexchange.com/q/114762) of how and why this solves the problem would really help to improve the quality of your post, and probably result in more up-votes. Remember that you are answering the question for readers in the future, not just the person asking now. Please [edit] your answer to add explanations and give an indication of what limitations and assumptions apply – jmoerdyk Mar 31 '23 at 20:42