unstack/pivot/melt, which one to use?-CodePudding

df=pd.read_csv('https://raw.githubusercontent.com/amanaroratc/hello-world/master/ask_pivot.csv')

I have a dataframe of the format:

Every date has maximum of two unique Offer_Description.

I want to create new columns "Offer_1" and "Offer_2" and create single row for each date.

I am confused on using pivot_table or unstack or melt?

CodePudding user response：

Create counter by GroupBy.cumcount by both columns, set to MultiIndex by DataFrame.set_index, reshape by Series.unstack and rename columns by DataFrame.add_prefix:

g = df.groupby(['Product_ID','Date']).cumcount().add(1)
df = (df.set_index(['Product_ID','Date', g])['Offer_Description']
        .unstack()
        .add_prefix('Offer_')
        .reset_index())
print (df)

     Product_ID        Date  \
0       1067995  2021-12-16   
1       1067995  2021-12-17   
2       1067995  2021-12-19   
3       1067995  2021-12-20   
4       1067995  2021-12-22   
..          ...         ...   
345     4524075  2022-02-15   
346     4524075  2022-02-16   
347     4524075  2022-02-19   
348     4524075  2022-02-20   
349     4524075  2022-02-21   

                                               Offer_1  \
0      Additional 10% off on purchase worth Rs. 1000/-   
1      Additional 10% off on purchase worth Rs. 1000/-   
2      Additional 15% off on purchase worth Rs. 1500/-   
3      Additional 15% off on purchase worth Rs. 1500/-   
4         Additional 5% Off on purchase of 2  products   
..                                                 ...   
345                           Buy 2 Get 10% Off on MRP   
346                           Buy 2 Get 10% Off on MRP   
347  Free Autographed Pouch By Janhvi Kapoor On Ord...   
348  Free Autographed Pouch By Janhvi Kapoor On Ord...   
349  Buy 2 Get 1 Free! *(Add 3 singles to the cart ...   

                                               Offer_2  
0      Additional 15% off on purchase worth Rs. 1500/-  
1      Additional 15% off on purchase worth Rs. 1500/-  
2                                                  NaN  
3                                                  NaN  
4                                                  NaN  
..                                                 ...  
345                                                NaN  
346                                                NaN  
347                                                NaN  
348                                                NaN  
349  Free Autographed Pouch By Janhvi Kapoor On Ord...  

[350 rows x 4 columns]