Pivote duplica filas en nuevas columnas Pandas

Tengo un dataframe como este y estoy tratando de remodelar mi dataframe usando Pivot de Pandas de manera que pueda mantener algunos valores de las filas originales mientras hago las filas de duplicados en columnas y les cambio el nombre. A veces tengo filas con 5 duplicados

Lo he estado intentando, pero no lo consigo.

import pandas as pd df = pd.read_csv("C:dummy") df = df.pivot(index=["ID"], columns=["Zone","PTC"], values=["Zone","PTC"]) # Rename columns and reset the index. df.columns = [["PTC{}","Zone{}"],.format(c) for c in df.columns] df.reset_index(inplace=True) # Drop duplicates df.drop(["PTC","Zone"], axis=1, inplace=True) 

Entrada

 ID Agent OV Zone Value PTC 1 10 26 M1 10 100 2 26.5 8 M2 50 95 2 26.5 8 M1 6 5 3 4.5 6 M3 4 40 3 4.5 6 M4 6 60 4 1.2 0.8 M1 8 100 5 2 0.4 M1 6 10 5 2 0.4 M2 41 86 5 2 0.4 M4 2 4 

Salida

 ID Agent OV Zone1 Value1 PTC1 Zone2 Value2 PTC2 Zone3 Value3 PTC3 1 10 26 M_1 10 100 0 0 0 0 0 0 2 26.5 8 M_2 50 95 M_1 6 5 0 0 0 3 4.5 6 M_3 4 40 M_4 6 60 0 0 0 4 1.2 0.8 M_1 8 100 0 0 0 0 0 0 5 2 0.4 M_1 6 10 M_2 41 86 M_4 2 4 

Utilice cumcount para los grupos de recuento, cree MultiIndex por set_index con valores de unstack y aplanar la última columna

 g = df.groupby(["ID","Agent", "OV"]).cumcount().add(1) df = df.set_index(["ID","Agent","OV", g]).unstack(fill_value=0).sort_index(axis=1, level=1) df.columns = ["{}{}".format(a, b) for a, b in df.columns] df = df.reset_index() print (df) ID Agent OV Zone1 Value1 PTC1 Zone2 Value2 PTC2 Zone3 Value3 PTC3 0 1 10.0 26.0 M1 10 100 0 0 0 0 0 0 1 2 26.5 8.0 M2 50 95 M1 6 5 0 0 0 2 3 4.5 6.0 M3 4 40 M4 6 60 0 0 0 3 4 1.2 0.8 M1 8 100 0 0 0 0 0 0 4 5 2.0 0.4 M1 6 10 M2 41 86 M4 2 4 

Si quiere reemplazar solo a 0 columnas numéricas:

 g = df.groupby(["ID","Agent"]).cumcount().add(1) df = df.set_index(["ID","Agent","OV", g]).unstack().sort_index(axis=1, level=1) idx = pd.IndexSlice df.loc[:, idx[['Value','PTC']]] = df.loc[:, idx[['Value','PTC']]].fillna(0).astype(int) df.columns = ["{}{}".format(a, b) for a, b in df.columns] df = df.fillna('').reset_index() print (df) ID Agent OV Zone1 Value1 PTC1 Zone2 Value2 PTC2 Zone3 Value3 PTC3 0 1 10.0 26.0 M1 10 100 0 0 0 0 1 2 26.5 8.0 M2 50 95 M1 6 5 0 0 2 3 4.5 6.0 M3 4 40 M4 6 60 0 0 3 4 1.2 0.8 M1 8 100 0 0 0 0 4 5 2.0 0.4 M1 6 10 M2 41 86 M4 2 4 

Puede usar cumcount crear la clave de ayuda, luego unstack con un índice múltiple (PS: puede agregar fillna (0) al final, no lo agregué porque no creo que el valor de Zona 0 sea correcto)

 df['New']=df.groupby(['ID','Agent','OV']).cumcount()+1 new_df=df.set_index(['ID','Agent','OV','New']).unstack('New').sort_index(axis=1 , level=1) new_df.columns=new_df.columns.map('{0[0]}{0[1]}'.format) new_df Out[40]: Zone1 Value1 PTC1 Zone2 Value2 PTC2 Zone3 Value3 PTC3 ID Agent OV 1 10.0 26.0 M1 10.0 100.0 None NaN NaN None NaN NaN 2 26.5 8.0 M2 50.0 95.0 M1 6.0 5.0 None NaN NaN 3 4.5 6.0 M3 4.0 40.0 M4 6.0 60.0 None NaN NaN 4 1.2 0.8 M1 8.0 100.0 None NaN NaN None NaN NaN 5 2.0 0.4 M1 6.0 10.0 M2 41.0 86.0 M4 2.0 4.0