Reverse row and column transformation within Python

Question

I have a dataframe where I would like to maintain all columns in my original dataset and create a new pivoted column based on existing dataset.

Data

 stat1 stat2    id  q122con q122av  q122con q122av  q222con q222av  q222con q222av
 50    1000     aa  40      10      900     100     50      0       1000    0   
 100   2000     bb  50      50      1500    500     75      25      1900    100 
                

                        
                                        

Desired

stat1   stat2   id  date    con         av          con         av      
50      1000    aa  q122    40          10          900         100             
50      1000    aa  q222    50          0           1000        0               
100     2000    bb  q122    50          50          1500        500             
100     2000    bb  q222    75          25          1900        100     

    

Doing

df.pivot(index="id", columns="date", values=["con", "av"])

However, I am not obtaining the full columns within my dataset. Any suggestion is appreciated.

Answer 1

Some long one line by wide_to_long

pd.wide_to_long(df.set_index(['stat1','stat2','id']).stack().groupby(level=[0,1,2,3]).agg(list).apply(pd.Series).unstack().stack(level=0).reset_index(),
                stubnames = ['q122','q222'], i = ['stat1','stat2','id','level_3'],j = 'date',suffix='\\w+').stack().unstack(level=[-3,-2])
Out[140]: 
level_3               0        1      
date                 av con   av   con
stat1 stat2 id                        
50    1000  aa q122  10  40  100   900
               q222   0  50    0  1000
100   2000  bb q122  50  50  500  1500
               q222  25  75  100  1900