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.
A simple pivot might be sufficient for your needs but this is what I did to reproduce your desired output:
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:
To get closer to your desired output I added the following:
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):
You can but I think you also need a within group counter to get the reshape in stata to get your desired output:
If you add
idx
then you could do reshape instata
:A bit old but I will post this for other people.
What you want can be achieved, but you probably shouldn't want it ;) Pandas supports hierarchical indexes for both rows and columns. In Python 2.7.x ...
Produces a probably more convenient representation than what you were looking for
The advantage of using set_index and unstacking vs a single function as pivot is that you can break the operations down into clear small steps, which simplifies debugging.
Reshaping docs are here
You are looking for
pd.wide_to_long()
(which is the direct analog of the stata command)pg. 192 Python for Data Analysis
Here's another solution more fleshed out, taken from Chris Albon's site.
Create "long" dataframe
Make a "wide" data