My current data frame is comprised of 10 rows and thousands of columns. The setup currently looks similar to this:

A   B   A   B
1   2   3   4
5   6   7   8

But I desire something more like below, where essentially I would transpose the columns into rows once the headers start repeating themselves.

A   B
1   2
5   6
3   4
7   8

I've been trying df.reshape but perhaps can't get the syntax right. Any suggestions on how best to transpose the data like this?

CodePudding user response：

Try with stack, groupby and pivot:

stacked = df.T.stack().to_frame().assign(idx=df.T.stack().groupby(level=0).cumcount()).reset_index()
output = stacked.pivot("idx", "level_0", 0).rename_axis(None, axis=1).rename_axis(None, axis=0)

>>> output
   A  B
0  1  2
1  5  6
2  3  4
3  7  8

CodePudding user response：

I'd probably go for stacking, grouping and then building a new DataFrame from scratch, eg:

pd.DataFrame({col: vals for col, vals in df.stack().groupby(level=1).agg(list).items()})

That'll also give you:

   A  B
0  1  2
1  3  4
2  5  6
3  7  8