-2

rows_2_columns

Col C to create new columns based on Col A and Col B. The original file is in excel if solutions is not possible in excel python/pandas will also be great. Thanks in Advance

Ashfaq Hussain
  • 3
  • 2
  • The question does not show enough effort. First a [complete and reproducible](https://stackoverflow.com/help/minimal-reproducible-example) is missing for which you can refer [this](https://stackoverflow.com/questions/20109391/how-to-make-good-reproducible-pandas-examples) , also you have not included what you have tried. You should edit the question to make it clearer for future readers. – anky Mar 28 '20 at 16:15

1 Answers1

1

Use GroupBy.cumcount for counter, create MultiIndex by DataFrame.set_index, reshape by Series.unstack, DataFrame.add_prefix and last DataFrame.reset_index:

print (df)
  Location  ID Item
0        A   1    X
1        A   1    Y
2        B   1    X
3        B   1    Y
4        B   1    Z

g = df.groupby(['Location','ID']).cumcount().add(1)
df1 = (df.set_index(['Location','ID', g])['Item']
         .unstack(fill_value='')
         .add_prefix('Item')
         .reset_index())
print (df1)    
  Location  ID Item1 Item2 Item3
0        A   1     X     Y      
1        B   1     X     Y     Z
jezrael
  • 822,522
  • 95
  • 1,334
  • 1,252