I have a .csv file containing 100 millions records, I would to create multiple files from this file, after certain condition satisfied in each line, I came to this code below, but it is slow, I think, it is due the fact the a file is opened and closed in each iteration. The result is many files renamed after countries, each file has inside the correspondant line from the large file. Is there any approach or method that can get rid of this problem and increase the speed?

import csv

with open('stats.csv','r', encoding = 'utf-8') as inf, open('stats_failed.txt', 'a', encoding = 'utf-8', newline = '') as failed_ouf:
    csr = csv.reader(inf)
    for i, record in enumerate(csr):
            country = record[3]
            #because some country have these characters, invalid for file renaming
            if '/' in country:
                country = country.replace('/','_SLASH_')
            elif '\\' in country:
                country = country.replace('\\','_BACKSLASH_')
            #pop the country because irrelevant since the file has its name

            with open(country, 'a', encoding = 'utf-8', newline = '') as output:
                csw = csv.writer(output)
                #just for tracking
                if i % 100_000 == 0:
            failed_csw = csv.writer(failed_ouf)

EDITED To those who asked: It looks like this:

\N,\N,"China, Taipei",C25C,\N,C25C
\N,\N,United States,E55P,\N,E55P

EDITED2 If it is relevant, the size is about 4 GB

  • \$\begingroup\$ Is there an upper limit on the number of distinct countries? \$\endgroup\$
    – greybeard
    Jul 8 at 12:07
  • \$\begingroup\$ Can you show some example input rows? \$\endgroup\$
    – Reinderien
    Jul 8 at 12:56
  • \$\begingroup\$ Does your input CSV file have a header? Can you show it? \$\endgroup\$
    – Reinderien
    Jul 8 at 14:05
  • \$\begingroup\$ @Reinderien, yes but I can skip it with i > 0 then do something, or letting it wrote to a file then delete it, it is only one line \$\endgroup\$
    – Khaled
    Jul 8 at 16:23
  • \$\begingroup\$ @greybeard, yes! the countries are maximal distinct 260 \$\endgroup\$
    – Khaled
    Jul 8 at 16:24

2 Answers 2


This is slow for a lot of reasons:

  • It's Python
  • The data-processing operations are non-vectorized
  • There's one file open/write/close per row!

So delete your code and use Pandas, which is written in part in C and has Python FFI. 4 GB should fit into the working memory of most modern computers so you don't need to be clever with segmentation, etc.

I'm sure most other programs would sooner expect a blank ,, CSV record to mean "nothing" than \N, so tell Pandas to load the latter as a NaN.

Your list of incompatible characters (at least on Windows) for filesystems is not complete. Also: since you're removing those characters from the filename, are you sure it's such a good idea to drop the original country string from the data?

Never bare try/except.


import pandas as pd

df = pd.read_csv('stats.csv', na_values=r'\N')

df['filename'] = df.country.str.replace(r'[^,\w_.)( -]+', '_', regex=True) + '.csv'

for filename, group in df.groupby('filename'):
    group.drop('filename', axis=1).to_csv(filename, index=False)
,,"China, Taipei",C25C,,C25C
  • \$\begingroup\$ Thanks., with pandas it was a good idea since it is in C, but I splited my large file into four parts, then with pandas and at the end, merging same name files with pandas. \$\endgroup\$
    – Khaled
    Jul 9 at 16:11

Non-trivial source code deserves documentation.
Define a function, including a docstring.

My crystal ball shows killing speed is not (yet) writing, but opening files and creating CSV writers.

Create failed_csv upfront, once, using with.

If csvfile is a file object, it should be opened with newline='' [1]

If you don't need to keep the order of records for any given country, you can handle spelling errors (or multiple names for the same country - UK, GB?) later concatenating files.
Alternatively, canonicalise country names before,
or create a dict from name to canonicalised name/file to use here:

Create a dict from country name to open CSV writer.


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.