A simple pivot might be sufficient for your needs but this is what I did to reproduce your desired output:
df['idx'] = df.groupby('Salesman').cumcount()
Just adding a within group counter/index will get you most of the way there but the column labels will not be as you desired:
print df.pivot(index='Salesman',columns='idx')[['product','price']] product price idx 0 1 2 0 1 2 Salesman Knut bat ball wand 5 1 3 Steve pen NaN NaN 2 NaN NaN
To get closer to your desired output I added the following:
df['prod_idx'] = 'product_' + df.idx.astype(str) df['prc_idx'] = 'price_' + df.idx.astype(str) product = df.pivot(index='Salesman',columns='prod_idx',values='product') prc = df.pivot(index='Salesman',columns='prc_idx',values='price') reshape = pd.concat([product,prc],axis=1) reshape['Height'] = df.set_index('Salesman')['Height'].drop_duplicates() print reshape product_0 product_1 product_2 price_0 price_1 price_2 Height Salesman Knut bat ball wand 5 1 3 6 Steve pen NaN NaN 2 NaN NaN 5
Edit: if you want to generalize the procedure to more variables I think you could do something like the following (although it might not be efficient enough):
df['idx'] = df.groupby('Salesman').cumcount() tmp = [] for var in ['product','price']: df['tmp_idx'] = var + '_' + df.idx.astype(str) tmp.append(df.pivot(index='Salesman',columns='tmp_idx',values=var)) reshape = pd.concat(tmp,axis=1)
@Luke said:
I think Stata can do something like this with the reshape command.
You can but I think you also need a within group counter to get the reshape in stata to get your desired output:
+-------------------------------------------+ | salesman idx height product price | |-------------------------------------------| 1. | Knut 0 6 bat 5 | 2. | Knut 1 6 ball 1 | 3. | Knut 2 6 wand 3 | 4. | Steve 0 5 pen 2 | +-------------------------------------------+
If you add idx
then you could do reshape in stata
:
reshape wide product price, i(salesman) j(idx)