stacking columns from pandas dataframe

Question

Apologies if this has been asked in a different way, I'm not sure how else to word it to get what I want to do.

Say I have some dataframe with two columns of values:

import pandas as pd

data = {'col1':[0.6, 0.8, 0.9, 0.87, 0.69, 0.88],
        'col2':[0.72, 0.91, 0.83, 0.85, 0.96, 0.76]}

df = pd.DataFrame(data)

df
Out[1]: 
   col1  col2
0  0.60  0.72
1  0.80  0.91
2  0.90  0.83
3  0.87  0.85
4  0.69  0.96
5  0.88  0.76

I want to take column 2 and 'append' it underneath column 1, continuing the index from 6 to 11. I also would like a new 'identifier' column to be created to have the column name to which each datapoint belongs. The closest I can get to this without lots of spaghetti code is the following:

pd.DataFrame(df.stack()).reset_index()

Out[34]: 
    level_0 level_1     0
0         0    col1  0.60
1         0    col2  0.72
2         1    col1  0.80
3         1    col2  0.91
4         2    col1  0.90
5         2    col2  0.83
6         3    col1  0.87
7         3    col2  0.85
8         4    col1  0.69
9         4    col2  0.96
10        5    col1  0.88
11        5    col2  0.76

I don't want this to be labeled 'level_0', 'level_1', etc. as I have column names in mind that I would like to use.

I'm sure there is an easy and professional way to accomplish this but I don't know enough about it.

jezrael · Accepted Answer · 2017-12-16 13:04:46Z

3

You are really close, not necessary call DataFrame constructor.

Also for set columns names is possible rename levels of MultiIndex by rename_axis:

df = df.stack().rename_axis(('a', 'b')).reset_index(name='val')
print (df)
    a     b   val
0   0  col1  0.60
1   0  col2  0.72
2   1  col1  0.80
3   1  col2  0.91
4   2  col1  0.90
5   2  col2  0.83
6   3  col1  0.87
7   3  col2  0.85
8   4  col1  0.69
9   4  col2  0.96
10  5  col1  0.88
11  5  col2  0.76

Numpy alternative:

a = np.repeat(df.index, len(df.columns))
b = np.tile(df.columns, len(df.index))
c = df.values.ravel()

df = pd.DataFrame(np.column_stack([a,b,c]), columns=['a','b','c'])
print (df)
    a     b     c
0   0  col1   0.6
1   0  col2  0.72
2   1  col1   0.8
3   1  col2  0.91
4   2  col1   0.9
5   2  col2  0.83
6   3  col1  0.87
7   3  col2  0.85
8   4  col1  0.69
9   4  col2  0.96
10  5  col1  0.88
11  5  col2  0.76

edited Dec 16, 2017 at 13:04

answered Dec 16, 2017 at 12:56

jezrael

868k103 gold badges1.4k silver badges1.3k bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

stacking columns from pandas dataframe

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related