Best way to iterate over rows in large csv file in python, write to new one

user_10162929 0
Viewed 355

I am a relative python newbie trying to efficiently look through a large csv file of ~8 million rows.

I have a csv of 6 columns:

+-------+-------+--------+-------+--------+----------+
| Gene1 | Start |  End   | Gene2 | Start  |   End    |
+-------+-------+--------+-------+--------+----------+
| gyrA  |    33 |    193 | dnaB  |    844 |      965 |
| rpoS  |   152 |    190 | ldh   |    200 |      264 |
| gbpC  |   456 |    500 | bgl   |   1222 |    14567 |
+-------+-------+--------+-------+--------+----------+

.....etc for 8,000,000 rows

What I want to do is compare if the start and end of Gene2 is within a certain range +/- the start and end of Gene1. For example, I want to see if the end of Gene1 is within 20 of the start of Gene2. I then want to write all rows where this is true to a new csv file.

Code: If the end of Gene1 is within +/- 20 of the start of Gene2, append that entire row to a new file

Output:

+-------+-------+--------+-------+-------+--------+
| Gene1 | Start |  End   | Gene2 | Start |  End   |
+-------+-------+--------+-------+-------+--------+
| rpoS  |   152 |    190 | ldh   |   200 |    264 |
+-------+-------+--------+-------+-------+--------+

What would be the most efficient way to do this that is comprehendible for a python novice? I will have to run the code multiple times on multiple csv's so I'm concerned about speed. I tried messing around with pandas a bit but it seems like the itterrows() function is not the suggested way to go for efficiency. I think I can accomplish this pretty easily with csv reader, but I'm not sure about how long that might take to run.

Thanks for your help in solving this problem!

pandas csv python loops
2 Answers

Many Pandas operations are vectorized. It would be difficult to write something yourself, quickly, that would be more performant:

df = pd.read_csv('large.csv')

  Gene1  Start  End Gene2  Start.1  End.1
0  gyrA     33  193  dnaB      844    965
1  rpoS    152  190   ldh      200    264
2  gbpC    456  500   bgl     1222  14567

A condition that returns a boolean series:

abs(df.End - df['Start.1']) <= 20

0    False
1     True
2    False

Filtering the dataframe by a boolean series:

df[abs(df.End - df['Start.1']) <= 20]

  Gene1  Start  End Gene2  Start.1  End.1
1  rpoS    152  190   ldh      200    264

Check out the Getting Started page for more.

user_60309510

We can use chunksize to use a generator expression and deal with a number of rows at a time and write it to a csv. doing this in chunks will save you from using up all your ram.

Another option is using Dask which can you read up on.

First, we create a blank csv with your target headers to write to.

header_df = pd.read_csv(yourfile,nrows=1)

header_df.iloc[:0].to_csv('target_file',index=False)

chunksize = 5 * 10000 #50k rows.

for chunk in pd.read_csv(your_file,chunksize=chunksize):
#your etl logic.
#assuming your final variable is called target df.
    target_df.to_csv(target_file, mode='a', header=False,index=False)
user_93751020
Related