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.