Pandas reshape dataframe every N rows to columns

Question

I have a dataframe as follows :

df1=pd.DataFrame(np.arange(24).reshape(6,-1),columns=['a','b','c','d'])

and I want to take 3 set of rows and convert them to columns with following order

Numpy reshape doesn't give intended answer

pd.DataFrame(np.reshape(df1.values,(3,-1)),columns=['a','b','c','d','e','f','g','h'])

You should consider [accepting](https://meta.stackexchange.com/questions/5234/how-does-accepting-an-answer-work?answertab=votes#tab-top) one of the awesome answers below. — Scott Boston, Feb 22 '18 at 22:03

score 8 · Accepted Answer · answered Feb 16 '18 at 14:08

8

In [258]: df = pd.DataFrame(np.hstack(np.split(df1, 2)))

In [259]: df
Out[259]:
   0  1   2   3   4   5   6   7
0  0  1   2   3  12  13  14  15
1  4  5   6   7  16  17  18  19
2  8  9  10  11  20  21  22  23

In [260]: import string

In [261]: df.columns = list(string.ascii_lowercase[:len(df.columns)])

In [262]: df
Out[262]:
   a  b   c   d   e   f   g   h
0  0  1   2   3  12  13  14  15
1  4  5   6   7  16  17  18  19
2  8  9  10  11  20  21  22  23

answered Feb 16 '18 at 14:08

MaxU - stand with Ukraine

205,989
36
386
419

Brilliant, Thanks! – Mehtab Pathan Feb 16 '18 at 14:13
@MehtabPathan, glad I could helpl :) – MaxU - stand with Ukraine Feb 16 '18 at 14:13
Split then stack, it is beautiful and efficient, thank you – Andrew King Jun 29 '22 at 03:38

score 2 · Answer 2 · answered Feb 16 '18 at 14:07

Create 3d array by reshape:

a = np.hstack(np.reshape(df1.values,(-1, 3, len(df1.columns))))
df = pd.DataFrame(a,columns=['a','b','c','d','e','f','g','h'])
print (df)
   a  b   c   d   e   f   g   h
0  0  1   2   3  12  13  14  15
1  4  5   6   7  16  17  18  19
2  8  9  10  11  20  21  22  23

score 2 · Answer 3 · answered Feb 16 '18 at 14:11

This uses the reshape/swapaxes/reshape idiom for rearranging sub-blocks of NumPy arrays.

In [26]: pd.DataFrame(df1.values.reshape(2,3,4).swapaxes(0,1).reshape(3,-1), columns=['a','b','c','d','e','f','g','h'])
Out[26]: 
   a  b   c   d   e   f   g   h
0  0  1   2   3  12  13  14  15
1  4  5   6   7  16  17  18  19
2  8  9  10  11  20  21  22  23

score 2 · Answer 4 · answered Feb 16 '18 at 14:18

If you want a pure pandas solution:

df.set_index([df.index % 3, df.index // 3])\
  .unstack()\
  .sort_index(level=1, axis=1)\
  .set_axis(list('abcdefgh'), axis=1, inplace=False)

Output:

   a  b   c   d   e   f   g   h
0  0  1   2   3  12  13  14  15
1  4  5   6   7  16  17  18  19
2  8  9  10  11  20  21  22  23

Pandas reshape dataframe every N rows to columns

Numpy reshape doesn't give intended answer

4 Answers4

Linked