Pandas behaviour on stack

Question

Lets Suppose I have

ID A1 B1  A2  B2
1  3  4   5   6
2  7  8   9   10

I want to use pandas stack and wants to achieve something like this

ID A B
1  3 4
1  5 6
2  7 8
2  9 10

but what I got is

ID  A   B
1   3   4
2   7   8
1   5   6
2   9   10

this is what i am using

df.stack().reset_index().

Is it possible to achieve something like this using Stack? append() method in pandas does this, but if possible I want to achieve using pandas stack() Any idea ?

Answer 1

You can use pd.wide_to_long:

pd.wide_to_long(df, ['A','B'], 'ID', 'value', sep='', suffix='.+')\
  .reset_index()\
  .sort_values('ID')\
  .drop('value', axis=1)

Output:

   ID  A   B
0   1  3   4
2   1  5   6
1   2  7   8
3   2  9  10

Answer 2

Create a new columns object by splitting up the existing column names. This takes for granted that we have single character letters followed by a single digit.

d = df.set_index('ID')
d.columns = d.columns.map(tuple)
d.stack().reset_index('ID')

   ID  A   B
1   1  3   4
2   1  5   6
1   2  7   8
2   2  9  10

---

One-line

df.set_index('ID').rename(columns=tuple).stack().reset_index('ID')

---

More generalized

d = df.set_index('ID')
s = d.columns.str
d.columns = [
    s.extract('^(\D+)', expand=False),
    s.extract('(\d+)$', expand=False)
]
d.stack().reset_index('ID')