Increasing writing speed to file inside a for-loop for large files

Question

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):
        try:
            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
            record.pop(3)

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

EDITED To those who asked: It looks like this:

col1,col2,country,mod1,code,mod2
\N,\N,Germany,C510,\N,C510
\N,\N,"China, Taipei",C25C,\N,C25C
\N,\N,Italy,GLF5,\N,GLF5
\N,\N,France,C25B,\N,C25B
\N,\N,Germany,C525,\N,C525
\N,\N,Turkey,PC12,\N,PC12
\N,\N,Germany,C680,\N,C680
\N,\N,Germany,GL5T,\N,GL5T
\N,\N,Germany,C25B,\N,C25B
\N,\N,Italy,GLF4,\N,GLF4
\N,\N,France,CRJ2,\N,CRJ2
\N,\N,Palestine,G280,\N,G280
\N,\N,Slovakia,C525,\N,C525
....
\N,\N,United States,E55P,\N,E55P
\N,\N,Russia,C56X,\N,C56X

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

Answer 1

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.

Suggested

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)

col1,col2,country,mod1,code,mod2
,,"China, Taipei",C25C,,C25C

Answer 2

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.