How can I make a column into rows with pandas with a dynamic number of columns?-CodePudding

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!

CodePudding user response：

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