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?
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)