2
\$\begingroup\$

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

\$\endgroup\$
10
  • \$\begingroup\$ Is there an upper limit on the number of distinct countries? \$\endgroup\$
    – greybeard
    Commented Jul 8, 2023 at 12:07
  • \$\begingroup\$ Can you show some example input rows? \$\endgroup\$
    – Reinderien
    Commented Jul 8, 2023 at 12:56
  • \$\begingroup\$ Does your input CSV file have a header? Can you show it? \$\endgroup\$
    – Reinderien
    Commented Jul 8, 2023 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
    Commented Jul 8, 2023 at 16:23
  • \$\begingroup\$ @greybeard, yes! the countries are maximal distinct 260 \$\endgroup\$
    – Khaled
    Commented Jul 8, 2023 at 16:24

2 Answers 2

3
\$\begingroup\$

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
\$\endgroup\$
1
  • \$\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
    Commented Jul 9, 2023 at 16:11
2
\$\begingroup\$

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.

\$\endgroup\$

Your Answer

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

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