Pandas stack column pairs

Question

I have a pandas dataframe with about 100 columns of following type:

X1       Y1      X2      Y2      X3      Y3
 0.78    0.22    0.19    0.42    0.04    0.65 
 0.43    0.29    0.43    0.84    0.14    0.42 
 0.57    0.70    0.59    0.86    0.11    0.40 
 0.92    0.52    0.81    0.33    0.54    1.00

w1here (X,Y) are basically pairs of values

I need to create the following from above.

   X     Y
 0.78    0.22 
 0.43    0.29 
 0.57    0.70 
 0.92    0.52 
 0.19    0.42 
 0.43    0.84 
 0.59    0.86 
 0.81    0.33 
 0.04    0.65 
 0.14    0.42 
 0.11    0.40 
 0.54    1.00

i.e. stack all the X columns which are odd numbered and then stack all the Y columns which are even numbered.

I have no clue where to even start. For small number of columns I could easily have use the column names.

jezrael · Accepted Answer · 2017-03-10T14:54:02.477

4

You can use lreshape, for column names use list comprehension:

x = [col for col in df.columns if 'X' in col]
y = [col for col in df.columns if 'Y' in col]

df = pd.lreshape(df, {'X': x,'Y': y})
print (df)
       X     Y
0   0.78  0.22
1   0.43  0.29
2   0.57  0.70
3   0.92  0.52
4   0.19  0.42
5   0.43  0.84
6   0.59  0.86
7   0.81  0.33
8   0.04  0.65
9   0.14  0.42
10  0.11  0.40
11  0.54  1.00

Solution with MultiIndex and stack:

df.columns = [np.arange(len(df.columns)) % 2, np.arange(len(df.columns)) // 2]
df = df.stack().reset_index(drop=True)
df.columns = ['X','Y']
print (df)
       X     Y
0   0.78  0.22
1   0.19  0.42
2   0.04  0.65
3   0.43  0.29
4   0.43  0.84
5   0.14  0.42
6   0.57  0.70
7   0.59  0.86
8   0.11  0.40
9   0.92  0.52
10  0.81  0.33
11  0.54  1.00

edited Mar 10 '17 at 14:54

answered Mar 10 '17 at 14:47

jezrael

822,522
95
1,334
1,252

1

This is beyond good. Is lreshape implemented and can be used without importing any modules? – Vaishali Mar 10 '17 at 14:53
1

yes, it is implemented long time ago, but still not documented only with source code. – jezrael Mar 10 '17 at 14:55

miradulo · Answer 2 · 2017-03-10T15:26:35.460

It may also be worth noting that you could just construct a new DataFrame explicitly with the X-Y values. This will most likely be quicker, but it assumes that the X-Y column pairs are the entirety of your DataFrame.

pd.DataFrame(dict(X=df.values[:,::2].reshape(-1),
                  Y=df.values[:,1::2].reshape(-1)))

Demo

>>> pd.DataFrame(dict(X=df.values[:,::2].reshape(-1),
                      Y=df.values[:,1::2].reshape(-1)))

       X     Y
0   0.78  0.22
1   0.19  0.42
2   0.04  0.65
3   0.43  0.29
4   0.43  0.84
5   0.14  0.42
6   0.57  0.70
7   0.59  0.86
8   0.11  0.40
9   0.92  0.52
10  0.81  0.33
11  0.54  1.00

score 1 · Answer 3 · answered Aug 22 '17 at 23:01

You can use the documented pd.wide_to_long but you will need to use a 'dummy' column to uniquely identify each row. You can drop this column later.

pd.wide_to_long(df.reset_index(), 
                stubnames=['X', 'Y'], 
                i='index', 
                j='dropme').reset_index(drop=True)

       X     Y
0   0.78  0.22
1   0.43  0.29
2   0.57  0.70
3   0.92  0.52
4   0.19  0.42
5   0.43  0.84
6   0.59  0.86
7   0.81  0.33
8   0.04  0.65
9   0.14  0.42
10  0.11  0.40
11  0.54  1.00

Pandas stack column pairs

3 Answers3