Pandas: Stack columns with similar names-CodePudding

I have a table as following:

id	a	b	a	b
123	1	6	7	3
456	2	8	9	7

What function in python can I use to stack the columns with the name names on top of each other like the following?

id	a	b
123	1	6
123	7	3
456	2	8
456	9	7

CodePudding user response：

One option is with pivot_longer from pyjanitor:

# pip install pyjanitor
import pandas as pd
import janitor

df.pivot_longer(index = 'id', 
                names_to = '.value', 
                names_pattern = '(.)', 
                sort_by_appearance=True) 
    id  a  b
0  123  1  6
1  123  7  3
2  456  2  8
3  456  9  7

CodePudding user response：

here is one way to do it

# melt and create a new DF
df2=df.melt(['id']) 

# create a temp column, seq, and count the repeating column (to split values along id)
# pivot, and drop the temp column

out=(df2.assign(seq=df2.groupby(['variable']).cumcount())
 .pivot(index=['id','seq'], columns='variable', values='value' ) 
 .reset_index()
 .drop(columns='seq')
 .rename_axis(columns=None)
)

out

     id     a   b
0   123     1   6
1   123     7   3
2   456     2   8
3   456     9   7