Python every three rows to columns using pandas

user_9530105 0
Viewed 233

I have a text file with data that repeoates every 3 rows. Lets say it is hash, directory, sub directory. The data looks like the following:

a3s2d1f32a1sdf321asdf
Dir_321321
Dir2_asdf
s21a3s21d3f21as32d1f
Dir_65465
Dir2_werq
asd21231asdfa3s21d
Dir_76541
Dir2_wbzxc
....

I have created a python script that takes the data and every 3 rows creates columns:

import pandas as pd

df1 = pd.read_csv('RogTest/RogTest.txt', delimiter = "\t", header=None)
df2 = df1[df1.index % 3 == 0]
df2 = df2.reset_index(drop=True) 
df3 = df1[df1.index % 3 == 1]
df3 = df3.reset_index(drop=True)
df4 = df1[df1.index % 3 == 2]
df4 = df4.reset_index(drop=True)
df5 = pd.concat([df2, df3], axis=1)
df6 = pd.concat([df5, df4], axis=1)

#Rename columns
df6.columns = ['Hash', 'Dir_1', 'Dir_2']
#Write to csv
df6.to_csv('RogTest/RogTest.csv', index=False, header=True)

This works fine but I am curious if there is a more efficient way to do this aka less code?

pandas python
1 Answers

You can use:

df_final = pd.DataFrame(np.reshape(df.values,(3, df.shape[0]/3)))
df_final.columns = ['Hash', 'Dir_1', 'Dir_2']

Output:

                    Hash       Dir_1       Dir_2
0  a3s2d1f32a1sdf321asdf  Dir_321321   Dir2_asdf
1   s21a3s21d3f21as32d1f   Dir_65465   Dir2_werq
2     asd21231asdfa3s21d   Dir_76541  Dir2_wbzxc
user_51789050
Related