You need preserve index values by reset_index
and parameter id_vars
:
df2 = pd.melt(df.reset_index(), id_vars="index",value_vars=['asset1','asset2'])
print (df2)
index variable value
0 coper1 asset1 1
1 coper2 asset1 3
2 coper3 asset1 5
3 coper1 asset2 2
4 coper2 asset2 4
5 coper3 asset2 6
Then pivot working nice:
print(df2.pivot(index='index',columns="variable", values="value"))
variable asset1 asset2
index
coper1 1 2
coper2 3 4
coper3 5 6
Another possible solution with stack
:
df2 = df.stack().reset_index()
df2.columns = list('abc')
print (df2)
a b c
0 coper1 asset1 1
1 coper1 asset2 2
2 coper2 asset1 3
3 coper2 asset2 4
4 coper3 asset1 5
5 coper3 asset2 6
print(df2.pivot(index='a',columns="b", values="c"))
b asset1 asset2
a
coper1 1 2
coper2 3 4
coper3 5 6