spiff spiff - 20 days ago 5
Python Question

Pandas Pivot and Merge not working

My input is:

Col1 Col2 Col3 Col4 Col5
0 1 Y A PQR 56
1 1 Y A XYZ 2
2 1 Y A DEF 20
3 2 Y B PQR 18
4 2 Y B XYZ 24
5 2 Y B DEF 11
6 3 Y C PQR 20
7 3 Y C XYZ 4
8 3 Y C DEF 18
9 3 Y C HHH 23


My output I want is:

Col1 Col2 Col3 PQR XYZ DEF HHH
0 1 Y A 56 2 20 NaN
1 2 Y B 18 24 11 NaN
2 3 Y C 20 4 18 23.0


However, when I do the below:

output = input.pivot_table(index='Col1',columns='Col4',values='Col5')


I get:

Col1 PQR XYZ DEF HHH
1 56 2 20 NaN
2 18 24 11 NaN
3 20 4 18 23.0


Where Col1 is now the index

Which isn't the whole thing - so I try and apply:

output = output.merge(input,how='left',left_index=True,right_on='Col1')


That just gives me absolute garbage

Thoughts pls?

Answer

I think you need add Col2 and Col3 to parameter index:

output = input.pivot_table(index=['Col1', 'Col2','Col3'],columns='Col4',values='Col5')
              .reset_index()
print (output)
Col4  Col1 Col2 Col3   DEF   HHH   PQR   XYZ
0        1    Y    A  20.0   NaN  56.0   2.0
1        2    Y    B  11.0   NaN  18.0  24.0
2        3    Y    C  18.0  23.0  20.0   4.0
Comments