Pandas reshape sets of columns as rows

Question

I have a dataframe that looks roughly like this:

A1 B1 C1 A4 B4 C4 A7 B7 C7
A2 B2 C2 A5 B5 C5 A8 B8 C8
A3 B3 C3 A6 B6 C6 A9 B9 C9

that I would like to get looking like this:

A1 B1 C1
A2 B2 C2
A3 B3 C3
A4 B4 C4
A5 B5 C5
A6 B6 C6
A7 B7 C7
A8 B8 C8
A9 B9 C9

Is there anything built into pandas or another data processing library that can easily do this without manually traversing the rows 3 (in this example) times for each "column set"? This would essentially be a 3-column pivot.

Answer 1

reshape + swapaxes + reshape

---

df.values.reshape(-1, 3, 3).swapaxes(1, 0).reshape(-1, 3)

array([['A1', 'B1', 'C1'],
       ['A2', 'B2', 'C2'],
       ['A3', 'B3', 'C3'],
       ['A4', 'B4', 'C4'],
       ['A5', 'B5', 'C5'],
       ['A6', 'B6', 'C6'],
       ['A7', 'B7', 'C7'],
       ['A8', 'B8', 'C8'],
       ['A9', 'B9', 'C9']], dtype=object)

---

To expand this and make it more general, you can calculate your offsets based on your grouping, For example, let's say group every 4 columns in the following Frame:

A1 B1 C1 D1 A4 B4 C4 D4 A7 B7 C7 D7
A2 B2 C2 D2 A5 B5 C5 D5 A8 B8 C8 D8
A3 B3 C3 D3 A6 B6 C6 D6 A9 B9 C9 D9

n = 4
f = df.shape[1] // n

df.values.reshape(-1, f, n).swapaxes(1, 0).reshape(-1, n)

array([['A1', 'B1', 'C1', 'D1'],
       ['A2', 'B2', 'C2', 'D2'],
       ['A3', 'B3', 'C3', 'D3'],
       ['A4', 'B4', 'C4', 'D4'],
       ['A5', 'B5', 'C5', 'D5'],
       ['A6', 'B6', 'C6', 'D6'],
       ['A7', 'B7', 'C7', 'D7'],
       ['A8', 'B8', 'C8', 'D8'],
       ['A9', 'B9', 'C9', 'D9']], dtype=object)

---

Using the underlying array is going to be quite a fast approach.

df = pd.concat([df]*500)

In [128]: %%timeit
     ...: n = 3
     ...: f = df.shape[1] // n
     ...: df.values.reshape(-1, f, n).swapaxes(1, 0).reshape(-1, n)
     ...:
77.4 µs ± 417 ns per loop (mean ± std. dev. of 7 runs, 10000 loops each)

In [129]: %%timeit
     ...: c = np.arange(len(df.columns))
     ...: df.columns = [c // 3, c % 3]
     ...: df1 = df.stack(0).sort_index(level=1).reset_index(drop=True)
     ...:
     ...:
12.2 ms ± 326 µs per loop (mean ± std. dev. of 7 runs, 100 loops each)