Search code examples
pythondataframestockyfinance

Dataframe: Stacked Columns, Index to Field

I have a dataframe of stock prices as shown in the attached screenshot. The columns are tiered, with the company being one level of column, and the price category being the next. Also, the date is the index. I would like for the company symbol to be the index, and the date to be a field (one of the columns), but I don't know where to start, and I've been struggling with this for a while. Could someone please point me in the right direction? The arrows in the attached screenshot show the manipulation I'm trying to accomplish.

Screenshot

Solution

  • You should use level argument with stack and reset_index to control the behavior you want.

    example: x=pd.DataFrame(columns=[['a','a','b','b','c','c'],[3,4,3,4,3,4]],data=np.random.rand(3,6))

    enter image description here

    a, b, c are 'company'??( I am not sure
    0, 1, 2 are date.

    use x.stack(level=0) let only the 'company' level stack to index(row)

    enter image description here

    then, x.stack(level=0).reset_index(level=0), the reset_index(level=0) part let only the 'date' from index(row) to columns

    enter image description here

    you can also rename it with x.stack(level=0).reset_index(level=0).rename(columns={'level_0':'date'}) if needed.

    enter image description here