python - Stacking dataframe columns (Pandas)

Question

I am looking for a way to pivot a dataframe in reverse direction. To the best of my knowledge, pandas provides a pivot or pivot_table method to transform an EAV df to a "normal" one. However, is there also a way to do the inverse?

So given the dataframe:

$df userid A B C 0 1 1 0 1 1 3 1 2 1 5 0

I would like to transform it to (a EAV model):

$df E A V
0 A 1 0 B 1 0 C 0 1 A 1 1 B 3 1 C 1 2 A 1 2 B 5 2 C 0

What would be the most efficient way to do so?

Answer 1

Assuming userid is the index, df.stack will do it:

In [133]: df.stack().reset_index().rename(columns={'userid' : 'E', 'level_1' : 'A', 0 : 'V'})
Out[133]: 
   E  A  V
0  0  A  1
1  0  B  1
2  0  C  0
3  1  A  1
4  1  B  3
5  1  C  1
6  2  A  1
7  2  B  5
8  2  C  0

If userid is not the index, set it like this:

df.set_index('userid', inplace=True)