0

I have a data frame like this:

Factory Serial_number year  month   LT  Order  Minimum  Lot   Stock  Value 
    xxx       123     2020   8      20   123     456    10     2000    3000
    xxx       123     2020   9      ..   ...    ...     ..      ...    4000

Desired output is:

Factory Serial_number year  month  LT   Order   Minimum  Lot    Stock   8.2020  9.2020
  xxx       123       2020   8     20     123     456     10     2000    3000    4000

I tried different method like stacking, pivoting but that did not give the exact result

 pd.pivot_table(df, values='Value', index=['Factory', 'Serial_number','Order','Minimum Lot','Stock'],
                    columns=['year','month']).reset_index()

    Ouput I get: 


                                               

                                                        year           2020    
                   
                                                        month            8        9       10        11  12  1   2   3   4   5   6   7
                Factory 12NC    Order   Minimum Lot   Stock                                             
                xxx   1233     8430.0   305.0          10675.200  35295.86   38735.9    29546.91    23004.1 25433.2 0.0 13547.6 27095.2 0.0 2686             0.0    0.0 0.0
                      1234       660.0  330.0          108.000   0.00   0.0 0.00    0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0
Rocky
  • 141
  • 2
  • 10
  • 1
    Do you try pivot by `set_index` and `unstack` ? Of by `pivot_table` ? – jezrael Aug 18 '20 at 12:12
  • I tried by pivot table... I did not try by setting index and then unstack. @jezrael – Rocky Aug 18 '20 at 12:16
  • can you add your pivot_table code to question? It should working well – jezrael Aug 18 '20 at 12:16
  • 1
    can you add output from sample data of your code? All columns used for index parameter are duplicated like `Factory`, `Serial_number` ? – jezrael Aug 18 '20 at 12:26
  • try remove `.reset_index()` first, then `df.columns = f'{a}.{b}' for b, a in df.columns]` and last `df = df.reset_index()` – jezrael Aug 18 '20 at 13:10
  • Could you please elaborate more? I could not get exactly the desired output @jezrael – Rocky Aug 18 '20 at 13:23
  • whats wrong here? Without data after your code not easy. – jezrael Aug 18 '20 at 13:24
  • so `df = pd.pivot_table(df, values='Value', index=['Factory', 'Serial_number','Order','Minimum Lot','Stock'], columns=['year','month'])`, then `df.columns = f'{a}.{b}' for b, a in df.columns]` and last `df = df.reset_index()` not working? – jezrael Aug 18 '20 at 13:40
  • So first I run this df = pd.pivot_table(df, values='Value', index=['Factory', 'Serial_number','Order','Minimum Lot','Stock'],columns=['year','month']) Then in a separate line df.columns = f'{a}.{b}' for b, a in df.columns] here it tells invalid syntax – Rocky Aug 18 '20 at 13:52
  • 1
    opps, sorry, misisng one `[` need instead `df.columns = f'{a}.{b}' for b, a in df.columns]` using `df.columns = [f'{a}.{b}' for b, a in df.columns]` – jezrael Aug 18 '20 at 13:53
  • Let us [continue this discussion in chat](https://chat.stackoverflow.com/rooms/220040/discussion-between-rocky-and-jezrael). – Rocky Aug 18 '20 at 14:14

0 Answers0