I have a dataframe like that currently

How do I add stack the columns such that I obtain this instead?

Appreciate any advice. Thanks!

CodePudding user response：

Might be a more elegant way to do this, but this should work:

import pandas as pd

data = {'Type': {0: 'None'}, 'a_PORT': {0: 20}, 'a_BENCH': {0: 25}, 
        'a_ACTIVE': {0: 18}, 'b_PORT': {0: 30}, 'b_BENCH': {0: 50}}

df = pd.DataFrame(data)
     
res = df.set_index('Type').stack()
res.index = res.index.droplevel(0).str.split('_', expand=True)

res = res.reset_index(drop=False).pivot(index='level_0', 
                                        columns='level_1', values=0)

# cosmetic
res = res.loc[:, ['PORT', 'BENCH', 'ACTIVE']].reset_index(drop=False)
res = res.rename(columns={'level_0':'Type'})
res.columns.name = None

print(res)

  Type  PORT  BENCH  ACTIVE
0    a  20.0   25.0    18.0
1    b  30.0   50.0     NaN

Explanation:

• Set Type as the index, and apply df.stack to move all column names to (Multi)Index.
• Now, use Index.droplevel to drop empty level 0 (i.e. Type), and apply str.split to get a MultiIndex with ('a', 'Port'), ('a', 'BENCH'), etc.
• Reset the index again to get this MultiIndex back as columns (i.e. 'level_0' and 'level_1'). Now, apply df.pivot.
• Finally, apply some cosmetic modifications.

CodePudding user response：

here is one way to do it

# drop the Type, we don't need that when melting
df2=df.drop(columns='Type')

#melt to make the DF vertical
df2=df2.melt(var_name='Type')

df2[['Type','col']]=df2['Type'].str.split('_', expand=True)

#split the Type on "_", and create addl columns
df2[['Type','col']]=df2['Type'].str.split('_', expand=True)

#finally, pivot to re order
df2.pivot(index='Type', columns='col', values='value').reset_index().rename_axis(columns=None)

    Type    ACTIVE  BENCH   PORT
0      a    18.0    25.0    20.0
1      b    NaN     50.0    30.0