I am trying to convert a column of values into separate columns using pandas in python. So I have columns relating to shops and their products and the number of products each shop has could be different. For example:
ShopId ShopCode ShopName ProductName 1 a Shop One x 1 a Shop One y 1 a Shop One z 2 b Shop Two 2x 2 b Shop Two 2y 3 c Shop Three 3x
What I am trying to achieve would look something like this:
ShopId ShopCode ShopName Product1 Product2 Product3 1 a Shop One x y z 2 b Shop Two 2x 2y 3 c Shop Three 3x
If there are any shops that have more than 3 products, I would need more columns to be created for them dynamically. Any ideas or suggestions are appreciated! Thanks!
Advertisement
Answer
Use GroupBy.cumcount
for counter with reshape by Series.unstack
:
c = ['ShopId','ShopCode','ShopName'] g = df.groupby(c).cumcount() df1 = (df.set_index(c + [g])['ProductName'] .unstack(fill_value='') .rename(columns=lambda x: f'Product{x + 1}') .reset_index()) print (df1) ShopId ShopCode ShopName Product1 Product2 Product3 0 a Shop One x y z 1 b Shop Two 2x 2y 2 c Shop Three 3x