Pandas: Create MultiIndex / groupby using existing index and column headers

Viewed 459

I'm trying to take a 2-dimensional data frame and turn it into a (more-or-less) one-dimensional data frame where an existing column is used as the first level index and the column headers are used as the second level index.

I've been trying .melt(), .groupby(), .transpose(), .MultiIndex(), .pivot()... all without any luck - I think mainly to do with the fact that they need to be combined in some way that eludes me.

Setup:

import pandas as pd
from io import StringIO

csv = StringIO(u'''
AXIS    A       B       C       D
X       100     101     102     103 
Y       200     201     202     203
Z       300     301     302     303
''')

df = pd.read_csv(csv, delim_whitespace = True)

Desired Output:

                Num
One     Two     
  X       A     100
  X       B     101
  X       C     102
  X       D     103
  Y       A     200
  Y       B     201
  Y       C     202
  Y       D     203
  Z       A     300
  Z       B     301
  Z       C     302
  Z       D     303

Thanks in advance.

4 Answers
Related