How to switch columns attributes to rows attributes in pandas-CodePudding

Here is my Input


a.age        829        1030    829_zscore  1030_zscore             
15-20   0.973257    0.943649    1.547537    0.980226
21-34   0.960501    0.921949    -0.242228   0.017680

My expected output


a.age                    829        1030        
15-20                0.973257   0.943649
15_20_zscore         1.547537   0.980226
21-34                0.960501   0.921949
21-34_zscore        -0.242228   0.017680

CodePudding user response：

Use:

#convert a.age column to index and columnsnames to strings
df1 = df.set_index('a.age').rename(columns=str)
#split columnsnames by _ for MultiIndex
df1.columns = df1.columns.str.split('_', expand=True)
#reshape and convert MultiIndex to columns
df1 = df1.stack().reset_index()
#join columns with drop level_1 column with separator _
df1['a.age'] = df1['a.age']   df1.pop('level_1').radd('_').fillna('')
print (df1)
          a.age      1030       829
0         15-20  0.943649  0.973257
1  15-20_zscore  0.980226  1.547537
2         21-34  0.921949  0.960501
3  21-34_zscore  0.017680 -0.242228

Similar idea with integers columns:

df1 = df.set_index('a.age').rename(columns=str)
df1.columns = df1.columns.str.split('_', expand=True)
df1 = df1.rename(columns=int, level=0).stack().reset_index()
df1['a.age'] = df1['a.age']   df1.pop('level_1').radd('_').fillna('')
print (df1)
          a.age       829      1030
0         15-20  0.973257  0.943649
1  15-20_zscore  1.547537  0.980226
2         21-34  0.960501  0.921949
3  21-34_zscore -0.242228  0.017680

CodePudding user response：

Just for comparison, solution not using multi-index

df1 = df.set_index('a.age').rename(columns=str)
pd.concat([
   df1[filter(lambda c: '_' not in c,df1.columns)], # cols w/o _
   df1[filter(lambda c: '_' in c,df1.columns)]. # cols w/_
              rename(columns=lambda c: c.split('_')[0]). # rm zscore from cols
              rename(lambda r: str(r) '_zscore') # add _score to index
]).sort_index()