Luke - 1 year ago 110

Python Question

I have data in long format and am trying to reshape to wide, but there doesn't seem to be a straightforward way to do this using melt/stack/unstack:

`Salesman Height product price`

Knut 6 bat 5

Knut 6 ball 1

Knut 6 wand 3

Steve 5 pen 2

Becomes:

`Salesman Height product_1 price_1 product_2 price_2 product_3 price_3`

Knut 6 bat 5 ball 1 wand 3

Steve 5 pen 2 NA NA NA NA

I think Stata can do something like this with the reshape command.

Answer Source

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)
```