Tricky shift values from specific rows to another row using Pandas with multi-index columns

Question

I am looking to shift values from specific rows to another row using Pandas.

Data

        cn_positions    cn_positions    cn_positions
Date    Q1.22           Q1.23           Q1.24   
ID              
AA      73              87              104 
BB      0               0               13  
CC      0               20              62  
CC      0               0               11  
                
            

Desired

        cn_positions_Q1.22  cn_positions_Q1.23  cn_positions_Q1.24
Date                
ID              
AA      73                  87                  104 
BB      0                   0                   13  
CC      0                   20                  62  
CC      0                   0                   11

Doing

df_pivot = pd.pivot_table(df.unstack().reset_index(), values=0, index='ID', columns='Date').rename_axis(None, axis=1)

However, the above seems to eliminate the Q1.22,Q1.23 values. Any suggestion is appreciated.

Answer 1

first it looks to me like the daterow is just part of a multi index column name

not an actual row

if that is the case df.columns = [f"{x}_{y}" for x,y in df.columns] should flatten it

for example

df.columns = pandas.MultiIndex.from_tuples([("cn_positions","Q1.22"),("cn_positions","Q1.23"),("cn_positions","Q1.24")])
df
Out[7]: 
  cn_positions            
         Q1.22 Q1.23 Q1.24
0            1     2     3
1            4     5     6
2            7     8     9
df.columns = [f'{a}_{b}' for a,b in df.columns]
df
Out[9]: 
   cn_positions_Q1.22  cn_positions_Q1.23  cn_positions_Q1.24
0                   1                   2                   3
1                   4                   5                   6
2                   7                   8                   9