how to melt a dataframe -- get the column name in the field of melt dataframe

Question

I have a df as below

     name       0    1    2    3    4
 0   alex      NaN  NaN  aa   bb  NaN
 1   mike      NaN  rr  NaN  NaN  NaN
 2   rachel    ss  NaN  NaN  NaN  ff
 3   john      NaN  ff  NaN  NaN  NaN

the melt function should return the below

     name      code
 0   alex      2   
 1   alex      3   
 2   mike      1  
 3   rachel    0  
 4   rachel    4
 5   john      1

Any suggestion is helpful. thanks.

Answer 1

df.set_index('name').unstack().reset_index().rename(columns={'level_0':'Code'}).dropna().drop(0,axis =1)[['name','Code']].sort_values('name')

output will be

name    Code
alex    2
alex    3
john    1
mike    1
rachel  0
rachel  4

Answer 2

Just follow these steps: melt, dropna, sort column name, reset index, and finally drop any unwanted columns

In [1171]: df.melt(['name'],var_name='code').dropna().sort_values('name').reset_index().drop(['index', 'value'], 1)
Out[1171]:
     name code
0  alex    2
1  alex    3
2  john    1
3  mike    1
4  rachel  0
5  rachel  4

Answer 3

This should work.

 df.unstack().reset_index().dropna()