CODEWITHSUNDEEP
pythonpandas
PEREZje
PEREZje

Reputation: 2492

Convert a column to header row

So I've got this dataframe/csv file:

,stock,adj_close
0,GERN,3.59
1,GERN,3.3
2,GERN,3.34
...
4530,CMCSA,35.78
4531,CMCSA,35.46
4532,CMCSA,35.08
...
9060,AAPL,189.63
9061,AAPL,189.25
9062,AAPL,190.31

With a bunch more stocks and datapoints. There's an equal amount of rows per stock, and every row is a day. What I'd like to achieve is that the header row consists of all stock names, and the rows below it to be the value in adj_close. So the result would look like this:

,  GERN, CMCSA, AAPL, ............
0, 3.59, 35.78, 189.63 .. .. .. ..
1, 3.3,  35.46, 189.25 .. .. .. ..
2, 3.34, 35.08, 190.31 .. .. .. ..

Is this possible?

I looked into the pivot method and some for loops but couldn't get it to work.

Upvotes: 3

Views: 77

Answers (1)

Zero
Zero

Reputation: 76917

Use set_index and unstack

In [37]: (df.set_index(['stock', df.groupby('stock').cumcount()])['adj_close']
            .unstack('stock'))
Out[37]:
stock    AAPL  CMCSA  GERN
0      189.63  35.78  3.59
1      189.25  35.46  3.30
2      190.31  35.08  3.34

Or, use pivot

In [47]: df.assign(cc=df.groupby('stock').cumcount()
           ).pivot(columns='stock', values='adj_close' , index='cc')
Out[47]:
stock    AAPL  CMCSA  GERN
cc
0      189.63  35.78  3.59
1      189.25  35.46  3.30
2      190.31  35.08  3.34

Details

In [38]: df
Out[38]:
      stock  adj_close
0      GERN       3.59
1      GERN       3.30
2      GERN       3.34
4530  CMCSA      35.78
4531  CMCSA      35.46
4532  CMCSA      35.08
9060   AAPL     189.63
9061   AAPL     189.25
9062   AAPL     190.31

Upvotes: 2

Related Questions