How to transpose (stack) arbitrary columns in Dataframe?

Question

I'll use this Dataframe as example:

import numpy as np
import pandas as pd
df = pd.DataFrame(np.random.randn(3, 6), 
                  columns=['a', 'b', 'c', '2010', '2011', '2012'])

which results in this data:

          a         b         c      2010      2011      2012
0 -2.161845 -0.995818 -0.225338  0.107255 -1.114179  0.701679
1  1.083428 -1.473900  0.890769 -0.937312  0.781201 -0.043237
2 -1.187588  0.241896  0.465302 -0.194004  0.921763 -1.359859

Now I want to transpose (stack) columns '2010', '2011' and '2012' into rows to be able to get:

        a         b         c 
-2.161845 -0.995818 -0.225338 2010  0.107255
 1.083428 -1.473900  0.890769 2010 -0.937312
-1.187588  0.241896  0.465302 2010 -0.194004
-2.161845 -0.995818 -0.225338 2011 -1.114179
 1.083428 -1.473900  0.890769 2011  0.781201
-1.187588  0.241896  0.465302 2011  0.921763
-2.161845 -0.995818 -0.225338 2012  0.701679
 1.083428 -1.473900  0.890769 2012 -0.043237
-1.187588  0.241896  0.465302 2012 -1.359859

By using df.stack() pandas "stacks" all columns into rows, while I want to stack just those pointed. So my qestion is how to transpose arbitrary columns to rows in pandas Dataframe?

Ffisegydd · Accepted Answer · 2014-09-24 10:08:13Z

3

You should use pandas.melt for this.

import numpy as np
import pandas as pd

# Note I've changed it from random numbers to integers as I 
# find it easier to read and see the differences :)
df = pd.DataFrame(np.arange(18).reshape((3,6)), 
                  columns=['a', 'b', 'c', '2010', '2011', '2012'])

var = ['a', 'b', 'c']
melted = pd.melt(df, id_vars=var)

print(melted)
#     a   b   c variable  value
# 0   0   1   2     2010      3
# 1   6   7   8     2010      9
# 2  12  13  14     2010     15
# 3   0   1   2     2011      4
# 4   6   7   8     2011     10
# 5  12  13  14     2011     16
# 6   0   1   2     2012      5
# 7   6   7   8     2012     11
# 8  12  13  14     2012     17

answered Sep 24, 2014 at 10:08

Ffisegydd

54k16 gold badges151 silver badges125 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How to transpose (stack) arbitrary columns in 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