how to split single row into multiple columns with a specific column numbers in python?

2
votes

I have data in a single row as mentioned below, but I want to convert row into 8 columns and column index be like var1,var2,var3,var4,var5,var6,var7,var8

0   0.008985
1   0.486024
2   0.800637
3   0.006264
4   5.030551
5   0.001959
6   0.002210
7   0.005949
8   0.008985
9   0.689814
10  0.889638
11  0.007462
12  5.589760
13  0.005739
14  0.006474
15  0.004770
16  0.008985
17  0.202327
18  0.152252
19  0.004041

I want to have dataframe in this formate: 

 var1 |  var2  |  var3  |  var4  |  var5  |  var6  |  var7  |var8
0.008985|0.486024|0.800637|0.006264|5.030551|0.001959|0.005949|0.002210
0.008985|0.689814|0.889638|0.007462|5.589760|0.005739|0.006474|0.004770

please suggest how to convert this dataframe in python?

thank you in advance

3
pythonpandasdataframearraylist

3 Answers

1
votes

Use:

cols=['var'+str(i+1) for i in range(8)]
#['var1', 'var2', 'var3', 'var4', 'var5', 'var6', 'var7', 'var8']
df=pd.DataFrame(df.groupby(df.index//8)[1].apply(list).values.tolist(),columns=cols)
print(df)

       var1      var2      var3      var4      var5      var6      var7  \
0  0.008985  0.486024  0.800637  0.006264  5.030551  0.001959  0.002210   
1  0.008985  0.689814  0.889638  0.007462  5.589760  0.005739  0.006474   
2  0.008985  0.202327  0.152252  0.004041       NaN       NaN       NaN   

       var8  
0  0.005949  
1  0.004770  
2       NaN

Note Replace 1 by your original column name.

1
votes

You can convert the data to a numpy array and reshape that. Then make a DataFrame from that numpy array:

import pandas as pd
import numpy as np

n = 20
df = pd.DataFrame({'row': range(n)})
df = pd.DataFrame(np.array(df.row).reshape(2, -1))
df.columns = [f'var{i}' for i in range(1, n//2 + 1)]
1
votes

Use numpy solution - changed unutbu answer a bit - inicialize empty array filled by NaNs:

arr = np.full(((len(df) - 1)//8 + 1)*8, np.nan)
arr[:len(df)] = df['col']
df = pd.DataFrame(arr.reshape((-1, 8)), columns=[f'var{i}' for i in range(1,9)])
print (df)
       var1      var2      var3      var4      var5      var6      var7  \
0  0.008985  0.486024  0.800637  0.006264  5.030551  0.001959  0.002210   
1  0.008985  0.689814  0.889638  0.007462  5.589760  0.005739  0.006474   
2  0.008985  0.202327  0.152252  0.004041       NaN       NaN       NaN   

       var8  
0  0.005949  
1  0.004770  
2       NaN