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?
>Solution :
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]