Group by columns and show two values in one cell pandas-CodePudding

Suppose we have this dataframe

rows = [['credit', 1, 1, 2, 3]]
columns = ['Type', 'amount', 'active', 'amount', 'active']

df = pd.DataFrame(rows, columns=columns)
df = df.set_index('Type')


        amount  active  amount  active
Type                                   
credit       1       1       2       3

I need to modify it so it will group columns and show both values for index Like this

        amount  active
Type                                   
credit       1       1
             2       3

How can i achieve that?

CodePudding user response：

For a programmatic version based on column names you can use:

(df
 .reset_index()
 .melt('Type')
 .assign(idx= lambda d: d.groupby('variable').cumcount())
 .pivot(index=['Type', 'idx'], columns='variable', values='value')
 .droplevel('idx')
)

output:

variable  active  amount
Type                    
credit         1       1
credit         3       2

Alternative with stack:

(df
 .stack().to_frame()
 .assign(idx=lambda d: d.groupby(level=1).cumcount())
 .set_index('idx', append=True)[0]
 .unstack(1)
 .droplevel(1)
)

CodePudding user response：

One option is to dump it into numpy:

pd.DataFrame(np.reshape(df.to_numpy(), (-1, 2)), 
             columns = df.columns.unique(), 
             index = df.index.repeat(2))

        amount  active
Type
credit       1       1
credit       2       3

Another option is with concat - it brings up a warning which does not make sense in this case:

 pd.concat([df[name].melt(ignore_index = False, value_name = name)
                    .drop(columns='variable') 
            for name in df.columns.unique()], axis = 1)

        amount  active
Type
credit       1       1
credit       2       3