I have the following dataframe:
Type Label_1 Label_2 Label_3
A 1 5 3
B 3 2 1
C 2 1 2
I'd like to format it to look like this:
Type Label_type Value
A Label_1 1
A Label_2 5
A Label_3 3
B Label_1 2
B Label_2 1
How can I do this in the most effective way? I failed to do so...
we can use pd.melt method:
In [87]: pd.melt(df, 'Type')
Out[87]:
Type variable value
0 A Label_1 1
1 B Label_1 3
2 C Label_1 2
3 A Label_2 5
4 B Label_2 2
5 C Label_2 1
6 A Label_3 3
7 B Label_3 1
8 C Label_3 2
if the order is important:
In [89]: pd.melt(df, 'Type').sort_values(['Type', 'variable'])
Out[89]:
Type variable value
0 A Label_1 1
3 A Label_2 5
6 A Label_3 3
1 B Label_1 3
4 B Label_2 2
7 B Label_3 1
2 C Label_1 2
5 C Label_2 1
8 C Label_3 2
Use stack:
df=df.set_index('Type').stack().rename_axis(('Type','Label_type')).reset_index(name='Value')
print (df)
Type Label_type Value
0 A Label_1 1
1 A Label_2 5
2 A Label_3 3
3 B Label_1 3
4 B Label_2 2
5 B Label_3 1
6 C Label_1 2
7 C Label_2 1
8 C Label_3 2