How to append multiple columns to the first 3 columns and repeat the index values using pandas?

Question

I have a data set in which the columns are in multiples of 3 (excluding index column[0]). I am new to python.

Here there are 9 columns excluding index. So I want to append 4th column to the 1st,5th column to 2nd,6th to 3rd, again 7th to 1st, 8th to 2nd, 9th to 3rd, and so on for large data set. My large data set will always be in multiples of 3 (excl.index col.).

Also I want the index values to repeat in same order. In this case 6,9,4,3 to repeat 3 times.

import pandas as pd
import io
data =io.StringIO("""
6,5.6,4.6,8.2,2.5,9.4,7.6,9.3,4.1,1.9
9,2.3,7.8,1,4.8,6.7,8.4,45.2,8.9,1.5
4,4.8,9.1,0,7.1,5.6,3.6,63.7,7.6,4
3,9.4,10.6,7.5,1.5,4.3,14.3,36.1,6.3,0
""")

df = pd.read_csv(data,index_col=[0],header = None)

Expected Output: df

6,5.6,4.6,8.2
9,2.3,7.8,1
4,4.8,9.1,0
3,9.4,10.6,7.5
6,2.5,9.4,7.6
9,4.8,6.7,8.4
4,7.1,5.6,3.6
3,1.5,4.3,14.3
6,9.3,4.1,1.9
9,45.2,8.9,1.5
4,63.7,7.6,4
3,36.1,6.3,0

Answer 1

Idea is reshape by stack with sorting second level of MultiIndex and also for correct ordering create ordered CategoricalIndex:

a = np.arange(len(df.columns))

df.index = pd.CategoricalIndex(df.index, ordered=True, categories=df.index.unique())
df.columns = [a // 3, a % 3]
df = df.stack(0).sort_index(level=1).reset_index(level=1, drop=True)
print (df)
      0     1     2
0                  
6   5.6   4.6   8.2
9   2.3   7.8   1.0
4   4.8   9.1   0.0
3   9.4  10.6   7.5
6   2.5   9.4   7.6
9   4.8   6.7   8.4
4   7.1   5.6   3.6
3   1.5   4.3  14.3
6   9.3   4.1   1.9
9  45.2   8.9   1.5
4  63.7   7.6   4.0
3  36.1   6.3   0.0

Answer 2

Split the data frame horizontally and concatenate the components vertically:

df.columns=[1,2,3]*(len(df.columns)//3) 
rslt= pd.concat( [ df.iloc[:,i:i+3] for i in range(0,len(df.columns),3) ])                                                

      1     2     3
0                  
6   5.6   4.6   8.2
9   2.3   7.8   1.0
4   4.8   9.1   0.0
3   9.4  10.6   7.5
6   2.5   9.4   7.6
9   4.8   6.7   8.4
4   7.1   5.6   3.6
3   1.5   4.3  14.3
6   9.3   4.1   1.9
9  45.2   8.9   1.5
4  63.7   7.6   4.0
3  36.1   6.3   0.0