python pandas melt/pivot on multiple columns in one step

Question

df have

cola  colb  colc cold cole colf  colg
1      x     y     10   15   20   25     
2      x     y     11   16   27   28
3      x     y     12   14   20   30

df want

cola  colb  colc colD colE
1      x     y     10   20        
1      x     y     15   25   
2      x     y     11   27
2      x     y     16   28
3      x     y     12   20
3      x     y     14   30

Looking to pivot df have (cold and cole become colD, colf and colg become colE). In reality I have many more columns than cola and colb (about 50 columns) that will NOT be pivoted, only the last 4 are pivoting into 2 columns.

Have tried: pd.melt(df_have, id_vars=['cola, colb'], var_name='colD')

But this requires me to add all 50 columns to id_vars and I can only do colD creation and colE creation in multiple steps- looking for a faster solution

score 3 · Answer 1 · answered Jun 04 '19 at 15:52

rename the columns, then wide_to_long

import pandas as pd
df = df.rename(columns={'cold': 'colD_1', 'cole': 'colD_2', 'colf': 'colE_1', 'colg': 'colE_2'})

df = (pd.wide_to_long(df, i=['cola', 'colb', 'colc'], j='drop',
                      stubnames=['colD', 'colE'], sep='_')
        .reset_index().drop(columns='drop'))

Output:

   cola colb colc  colD  colE
0     1    x    y    10    20
1     1    x    y    15    25
2     2    x    y    11    27
3     2    x    y    16    28
4     3    x    y    12    20
5     3    x    y    14    30

python pandas melt/pivot on multiple columns in one step

1 Answers1

Output: