Unmelt Pandas DataFrame

Question

I have a pandas dataframe with two id variables:

df = pd.DataFrame({'id': [1,1,1,2,2,3], 
               'num': [10,10,12,13,14,15],
               'q': ['a', 'b', 'd', 'a', 'b', 'z'],
               'v': [2,4,6,8,10,12]})

   id  num  q   v
0   1   10  a   2
1   1   10  b   4
2   1   12  d   6
3   2   13  a   8
4   2   14  b  10
5   3   15  z  12

I can pivot the table with:

df.pivot('id','q','v')

And end up with something close:

q    a   b   d   z
id                
1    2   4   6 NaN
2    8  10 NaN NaN
3  NaN NaN NaN  12

However, what I really want is (the original unmelted form):

id   num   a   b   d   z               
1    10   2   4 NaN NaN
1    12 NaN NaN   6 NaN  
2    13   8 NaN NaN NaN
2    14 NaN  10 NaN NaN
3    15 NaN NaN NaN  12

In other words:

'id' and 'num' my indices (normally, I've only seen either 'id' or 'num' being the index but I need both since I'm trying to retrieve the original unmelted form)
'q' are my columns
'v' are my values in the table

Update

I found a close solution from Wes McKinney's blog:

df.pivot_table(index=['id','num'], columns='q')

         v            
q        a   b   d   z
id num                
1  10    2   4 NaN NaN
   12  NaN NaN   6 NaN
2  13    8 NaN NaN NaN
   14  NaN  10 NaN NaN
3  15  NaN NaN NaN  12

However, the format is not quite the same as what I want above.

score 42 · Answer 1 · answered Oct 14 '17 at 13:49

42

You could use set_index and unstack

In [18]: df.set_index(['id', 'num', 'q'])['v'].unstack().reset_index()
Out[18]:
q  id  num    a     b    d     z
0   1   10  2.0   4.0  NaN   NaN
1   1   12  NaN   NaN  6.0   NaN
2   2   13  8.0   NaN  NaN   NaN
3   2   14  NaN  10.0  NaN   NaN
4   3   15  NaN   NaN  NaN  12.0

answered Oct 14 '17 at 13:49

Zero

74,117
18
147
154

2

This much cleaner than the accepted answer. Thanks! – kev8484 Nov 08 '17 at 20:51
Thank you. pandas.pivot() with multiple index columns is not as straightforward as I hoped and this solution worked instead. – Dan Jarratt Jan 16 '18 at 14:58
pivot_table was converting ints to floats. This did not do that. Thanks! – ProGirlXOXO Mar 01 '19 at 08:50
1

You can get rid of the "q" with something like this: `del df2.columns.name`. – Walker Hale IV Jul 30 '19 at 02:02

score 25 · Accepted Answer · edited May 23 '17 at 11:46

25

You're really close slaw. Just rename your column index to None and you've got what you want.

df2 = df.pivot_table(index=['id','num'], columns='q')
df2.columns = df2.columns.droplevel().rename(None)
df2.reset_index().fillna("null").to_csv("test.csv", sep="\t", index=None)

Note that the the 'v' column is expected to be numeric by default so that it can be aggregated. Otherwise, Pandas will error out with:

DataError: No numeric types to aggregate

To resolve this, you can specify your own aggregation function by using a custom lambda function:

df2 = df.pivot_table(index=['id','num'], columns='q', aggfunc= lambda x: x)

edited May 23 '17 at 11:46

Community

1
1

answered Jul 09 '15 at 12:32

khammel

2,047
1
14
18

Ahhh, I was so close! I knew that it had to do with `df.columns.name` but couldn't figure out how to use `rename` appropriately. Thanks for your help! – slaw Jul 09 '15 at 14:18
11

Important note: "close slaw" sounds like "coleslaw". – Dance Party2 May 25 '17 at 22:07

score 4 · Answer 3 · answered Nov 13 '17 at 14:25

you can remove name q.

df1.columns=df1.columns.tolist()

Zero's answer + remove q =

df1 = df.set_index(['id', 'num', 'q'])['v'].unstack().reset_index()
df1.columns=df1.columns.tolist()

   id  num    a     b    d     z
0   1   10  2.0   4.0  NaN   NaN
1   1   12  NaN   NaN  6.0   NaN
2   2   13  8.0   NaN  NaN   NaN
3   2   14  NaN  10.0  NaN   NaN
4   3   15  NaN   NaN  NaN  12.0

score 2 · Answer 4 · edited Mar 22 '18 at 13:20

2

This might work just fine:

Pivot

df2 = (df.pivot_table(index=['id', 'num'], columns='q', values='v')).reset_index())

Concatinate the 1st level column names with the 2nd

df2.columns =[s1 + str(s2) for (s1,s2) in df2.columns.tolist()]

edited Mar 22 '18 at 13:20

Adirio

5,040
1
14
26

answered Mar 22 '18 at 12:08

Hillary Murefu

66
3

slaw · Answer 5 · 2015-07-09T14:15:28.863

1

Came up with a close solution

df2 = df.pivot_table(index=['id','num'], columns='q')
df2.columns = df2.columns.droplevel()
df2.reset_index().fillna("null").to_csv("test.csv", sep="\t", index=None)

Still can't figure out how to drop 'q' from the dataframe

edited Jul 09 '15 at 14:15

answered Jul 09 '15 at 01:55

slaw

6,591
16
56
109

score 0 · Answer 6 · answered Oct 12 '19 at 19:42

It can be done in three steps:

#1: Prepare auxilary column 'id_num': 
df['id_num'] = df[['id', 'num']].apply(tuple, axis=1)
df = df.drop(columns=['id', 'num'])

#2: 'pivot' is almost an inverse of melt:
df, df.columns.name = df.pivot(index='id_num', columns='q', values='v').reset_index(), ''

#3: Bring back 'id' and 'num' columns:
df['id'], df['num'] = zip(*df['id_num'])
df = df.drop(columns=['id_num'])

This is a result, but with different order of columns:

     a     b    d     z  id  num
0  2.0   4.0  NaN   NaN   1   10
1  NaN   NaN  6.0   NaN   1   12
2  8.0   NaN  NaN   NaN   2   13
3  NaN  10.0  NaN   NaN   2   14
4  NaN   NaN  NaN  12.0   3   15

Alternatively with proper order:

def multiindex_pivot(df, columns=None, values=None):
    #inspired by: https://github.com/pandas-dev/pandas/issues/23955
    names = list(df.index.names)
    df = df.reset_index()
    list_index = df[names].values
    tuples_index = [tuple(i) for i in list_index] # hashable
    df = df.assign(tuples_index=tuples_index)
    df = df.pivot(index="tuples_index", columns=columns, values=values)
    tuples_index = df.index  # reduced
    index = pd.MultiIndex.from_tuples(tuples_index, names=names)
    df.index = index
    df = df.reset_index() #me
    df.columns.name = ''  #me
    return df

df = df.set_index(['id', 'num'])
df = multiindex_pivot(df, columns='q', values='v')

Unmelt Pandas DataFrame

6 Answers6

Linked