Pandas merging rows / Dataframe Transformation

Question

I have this example DataFrame:

e   col1    col2    col3
1   238.4   238.7   238.2
2   238.45  238.75  238.2
3   238.2   238.25  237.95
4   238.1   238.15  238.05
5   238.1   238.1   238
6   229.1   229.05  229.05
7   229.35  229.35  229.1
8   229.1   229.15  229
9   229.05  229.05  229

How would I be able to convert it to this:

                1                      2            3   
    col1    col2    col3    col1    col2    col3    col1    col2    col3
1   238.4   238.7   238.2   238.45  238.75  238.2   238.2   238.25  237.95
2   238.1   238.15  238.05  238.1   238.1   238     229.1   229.05  229.05
3   229.35  229.35  229.1   229.1   229.15  229    229.05   229.05  229

I am thinking maybe I should pivot by counting with lens or assigning a index that could be multiple of 3, but I really am not sure what would be the most efficient way.

Answer 1

Create a grouping series g, this we will be needed to group the dataframe so that every third element (taking a step size of 3) belongs to the same group, use np.unique to get the unique grouping keys, next use DataFrame.groupby to group the dataframe on g and use set_index to set the index of every grouped frame to k, finally use pd.concat to concat all the grouped dataframes along axis=1 and pass the optional parameter keys=k to create MultiLevel columns :

g, k = df.pop('e').sub(1) % 3 + 1, np.unique(g)
df1 = pd.concat([g.set_index(k) for _, g in df.groupby(g)], keys=k, axis=1)

Details:

print(g.tolist())
[1, 2, 3, 1, 2, 3, 1, 2, 3]

print(k)
array([1, 2, 3])

Result:

print(df1)

        1                       2                      3                
     col1    col2    col3    col1    col2   col3    col1    col2    col3
1  238.40  238.70  238.20  238.45  238.75  238.2  238.20  238.25  237.95
2  238.10  238.15  238.05  238.10  238.10  238.0  229.10  229.05  229.05
3  229.35  229.35  229.10  229.10  229.15  229.0  229.05  229.05  229.00