Pandas Transpose using Pivot

Question

I know there are multiple solutions out there but I am not sure if I can't apply to it my case. Little help would make me understand to use it better next time around:

my input data:

OID  V   ED  D   F   SF   SPID   SP   M    A    V
1    V1  E1  D1  F1  SF1  1      SP1  1.6  2.5  3.5
1    V1  E1  D1  F1  SF1  2      SP2  2.6  3.5  4.5

my output data must look like:

OID  V   ED  D   F   SF   SPID   SP   T   H
1    V1  E1  D1  F1  SF1  1      SP1  M   1.6
1    V1  E1  D1  F1  SF1  1      SP1  A   2.5
1    V1  E1  D1  F1  SF1  1      SP1  V   3.5
1    V1  E1  D1  F1  SF1  2      SP2  M   1.6
1    V1  E1  D1  F1  SF1  2      SP2  A   2.5
1    V1  E1  D1  F1  SF1  2      SP2  V   3.5

and my code is:

out=df.pivot_table(values=['M','A','V'], columns=values=['M','A','V'],
index=['OID','V','ED','D','F','SF','SPID','SP']).reset_index()
print(out)

TIA!

You need melt, not pivot. df.melt(['OID','V','ED','D','F','SF','SPID','SP']) — Vaishali, Nov 01 '18 at 00:13

score 2 · Accepted Answer · answered Nov 01 '18 at 00:16

Use melt instead of pivot.

(df.melt(df.columns[:-3], var_name='T', value_name='H')
   .sort_values('SPID'))

   OID   V  ED   D   F   SF  SPID   SP    T    H
0    1  V1  E1  D1  F1  SF1     1  SP1    M  1.6
2    1  V1  E1  D1  F1  SF1     1  SP1    A  2.5
4    1  V1  E1  D1  F1  SF1     1  SP1  V.1  3.5
1    1  V1  E1  D1  F1  SF1     2  SP2    M  2.6
3    1  V1  E1  D1  F1  SF1     2  SP2    A  3.5
5    1  V1  E1  D1  F1  SF1     2  SP2  V.1  4.5

Pandas Transpose using Pivot

1 Answers1