Rearranging DataFrame into two columns with column names in the first column and values in the second column

Question

I would like to convert a Pandas DataFrame in Python with multiple columns and time as index so that column names are transformed in the new index, while time and values appear as two columns. See the example below.

Original DataFrame

    p1  p2  p3
1   1   4   7
6   2   5   8
11  3   6   9

Resulting DataFrame

    time    vals
p1  1   1
p1  6   2
p1  11  3
p2  1   4
p2  6   5
p2  11  6
p3  1   7
p3  6   8
p3  11  9

I am looking for a relatively fast solution as my dataframe is large and I have to redo the transformation many times.

Answer 1

You can use index manipulation to get expected result:

>>> df.rename_axis('time').T.stack().rename('vals').reset_index('time')

    time  vals
p1     1     1
p1     6     2
p1    11     3
p2     1     4
p2     6     5
p2    11     6
p3     1     7
p3     6     8
p3    11     9

Step by step for a better understanding:

# Step 1. Rename index
>>> out = df.rename_axis('time')
      p1  p2  p3
time               # <- rename index  
1      1   4   7
6      2   5   8
11     3   6   9

# Step 2. Transpose your dataframe
>>> out = out.T
time  1   6   11
p1     1   2   3
p2     4   5   6
p3     7   8   9

# Step 3. Stack dataframe (transform to series)
>>> out = out.stack()
    time
p1  1       1
    6       2
    11      3
p2  1       4
    6       5
    11      6
p3  1       7
    6       8
    11      9
dtype: int64

# Step 4. Rename series
>>> out = out.rename('vals')
    time
p1  1       1
    6       2
    11      3
p2  1       4
    6       5
    11      6
p3  1       7
    6       8
    11      9
Name: vals, dtype: int64  # <- rename series

# Step 5. Reset second index level ('time')
>>> out = out.reset_index('time')
    time  vals
p1     1     1
p1     6     2
p1    11     3
p2     1     4
p2     6     5
p2    11     6
p3     1     7
p3     6     8
p3    11     9