I saw many questions about transposition with pandas, but it didnt answer exactly to my problem.
I want transpose one row in column, but keep the others columns. I want make a transposition but not of entire table. I have :
| ID_PRODUCT | ID_PRODUCT2 | CM | NAME | RATE | PRICE |
|---|---|---|---|---|---|
| 002311 | 001 | NAL | humberger | TD300 | 4,5 |
| 990032 | 001 | MNN | Pizza | TD300 | 3,45 |
| 002311 | 002 | NAL | humberger | TD300 | 4,5 |
| 990032 | 002 | MNN | Pizza | TD300 | 3,45 |
| 002311 | 002 | NAL | humberger | TD200 | 4,6 |
| 990032 | 002 | MNN | Pizza | TD200 | 3,47 |
SO i have a primary key on the both id_product (40 000 products) and i want transpose the rate in column after the NAME, and associed the price. Like this
| ID_PRODUCT | ID_PRODUCT2 | CM | NAME | TD300 | TD200 |
|---|---|---|---|---|---|
| 002311 | 001 | NAL | humberger | 4,5 | |
| 990032 | 001 | MNN | Pizza | 3,45 | |
| 002311 | 002 | NAL | humberger | 4,5 | 4,6 |
| 990032 | 002 | MNN | Pizza | 3,45 | 3,47 |
I have tried that:
df = pd.read_sql(query, db)
table = df.pivot_table(df, index=['ID_PRODUCT', 'ID_PRODUCT2'], columns=['RATE'] ,aggfunc='first', sort=False)
But it didnt work well.
Do you have any ideas?
