pandas dataframe melt with string values

Question

I have a dataframe that looks like this

brand|1 |2 |3
---------------
a    |a1|a2|a3
b    |b1|b2|b3

And I want the result dataframe to look like this

brand|rank|value
----------------
a    |1   |a1
a    |2   |a2
a    |3   |a3
b    |1   |b1
b    |2   |b2
b    |3   |b3

I have tried pandas melt function but it doesn't work for me since a1,a2,···b3 are all characters not numbers. I'm wondering how I can do this in python.

Answer 1

1) Using set_index and stack

In [429]: (df.set_index('brand').stack()
             .reset_index(name='value')
             .rename(columns={'level_1': 'rank'}))
Out[429]:
  brand rank value
0     a    1    a1
1     a    2    a2
2     a    3    a3
3     b    1    b1
4     b    2    b2
5     b    3    b3

2) Using melt

In [430]: df.melt(id_vars=['brand'], var_name='rank')
Out[430]:
  brand rank value
0     a    1    a1
1     b    1    b1
2     a    2    a2
3     b    2    b2
4     a    3    a3
5     b    3    b3

Answer 2

#Create example dataframe
a = {'a':['a1','a2','a3'],'b':['b1','b2','b3']}
df = pd.DataFrame.from_dict(a)
df = df.T
df = df.reset_index()
df.columns = ['brand','1','2','3']

To transform as you demonstrate, try:

pd.melt(df, id_vars =['brand'])