Pandas: Dataframe pivot result doesn't drill down-CodePudding

i have the bellow dataframe

    Item_code   Type    year-month  Qty
0   TH-32H400M  O   Jan-22-Q    0.000000
1   TH-32H400M  MPO Jan-22-Q    0.000000
2   TH-32H400M  ADJ Jan-22-Q    0.000000
3   TH-32H400M  BP_O    Jan-22-Q    0.000000
4   TH-32H400M  LY_O    Jan-22-Q    0.000000
... ... ... ... ...
1795    TH-75JX660M P   Jun-23-Q    0.000000
1796    TH-75JX660M S   Jun-23-Q    11.538462
1797    TH-75JX660M BP_S    Jun-23-Q    0.000000
1798    TH-75JX660M LY_S    Jun-23-Q    0.000000
1799    TH-75JX660M I   Jun-23-Q    0.769231

When i run the below code i get the desired result but with a few issues,

new_df = new_df.pivot(index=['Item_code','year-month'], columns='Type', values='Qty')

 -------------- ------------ ---------- ------ ------ --- ------------- ------ ----- ----- ----- ----- 
|  Item_code   | year-month |   ADJ    | BP_O | BP_S | I |    LY_O     | LY_S | MPO |  O  |  P  |  S  |
 -------------- ------------ ---------- ------ ------ --- ------------- ------ ----- ----- ----- ----- 
| TH-32GS655M  | Apr-22-Q   |    0     |    0 |    0 | 0 |           0 |    0 |   0 |   0 |   0 |   0 |         
|              | Apr-23-Q   |    0     |    0 | 0    | 0 |           0 |    0 |   0 | 350 | 350 | 350 |
|              | Aug-22-Q   |    0     |    0 | 0    | 0 |           0 |    0 |   0 |   0 |   0 |   0 |
|              | Dec-22-Q   |    0     |    0 | 0    | 0 |           0 |    0 |   0 | 350 |   0 |   0 |
|              | Feb-22-Q   |    0     |    0 | 0    | 0 |           0 |    0 |   0 |   0 |   0 |   0 |
|              | Feb-23-Q   |    0     |    0 | 0    | 0 |           0 |    0 |   0 | 350 | 350 | 350 |
|              | Jan-22-Q   |    0     |    0 | 0    | 0 |           0 |    0 |   0 |   0 |   0 |   0 |
| ------------ |            |          |      |      |   |             |      |     |     |     |     |             |
| TH-75HX750   | Jan-23-Q   |    0     |    0 |    0 | 0 |           0 |    0 |   0 | 350 | 350 | 350 |
|              | Jul-22-Q   |    0     |    0 | 0    | 0 |           0 |    0 |   0 |   0 |   0 |   0 |
|              | Jun-22-Q   |    0     |    0 | 0    | 0 |           0 |    0 |   0 |   0 |   0 |   0 |
|              | Jun-23-Q   |    0     |    0 | 0    | 13|           0 |    0 |   0 |   0 |   0 | 1.9 |
 -------------- ------------ ---------- ------ ------ --- ------------- ------ ----- ----- ----- -----

Why is "Item code" only not repeated on every row
How to get column name on the same row, Basically "Type" should not be there and "Item_code" & "year-month" should be first row witht he rest of column names

Thank you for the help.

CodePudding user response：

Maybe this solution will work.

new_df = new_df.pivot(index=['Item_code','year-month'], columns='Type', values='Qty')

new_df = new_df.reset_index().fillna(0)