Convert pandas dataframe from wide to long

Question

I have a pandas.Dataframe with the following columns:

a_1  ab_1  ac_1    a_2   ab_2   ac_2
2      3     4      5     6      7 

How do I convert it into the following?

a    ab    ac 
2     3     4
5     6     7 

I was trying to use pandas melt to convert from wide to long format, but not sure of the syntax.

Answer 1

You can replace the columns by a multi-index and stack:

df.columns = pd.MultiIndex.from_tuples(df.columns.str.split('_').map(tuple))
df = df.stack()

Answer 2

Here is one way to do that:

Code:

df.columns = pd.MultiIndex.from_tuples(
    [c.split('_') for c in df.columns], names=['col', 'row'])

df.melt().pivot(index='row', columns='col', values='value')

How?

1. Create a pandas.MultiIndex for the columns by splitting on _.

2. melt the data frame and then pivot on the elements from the original column names.

Test Code:

df = pd.DataFrame(
    data=[range(2, 8)],
    columns='a_1 ab_1 ac_1 a_2 ab_2 ac_2'.split()
)
print(df)

df.columns = pd.MultiIndex.from_tuples(
    [c.split('_') for c in df.columns], names=['col', 'row'])

print(df.melt().pivot(index='row', columns='col', values='value'))

Results:

   a_1  ab_1  ac_1  a_2  ab_2  ac_2
0    2     3     4    5     6     7

col  a  ab  ac
row           
1    2   3   4
2    5   6   7

pandas < 0.20.0

If using pandas prior to 0.20.0, melt() like:

print(pd.melt(df).pivot(index='row', columns='col', values='value'))