Concatenate 10,000 CSV files in a directory using Python - Pandas too slow

Question

I want to concatenate 10,000 single-line CSV files into one 10,000 row CSV file, ideally using Python. The directory contains only the CSV files. I have tried doing this with Pandas however it is way to slow, seems like it'll take about an hour. Is there a faster way of doing this? My current code is:

files = glob.glob("PATH/*.csv")
df = pd.concat(map(pd.read_csv, files))

Answer 1

Here is a simple trivial run of the mill solution which reads one line at a time into memory.

import glob

inputs = glob.glob("*.csv")
with open("output.csv", "w") as output:
  first = True
  for file in inputs:
    with open(file, "r") as inputfile:
      for no, line in enumerate(inputfile, 1):
        if no == 1 and not first:
          continue
        first = False
        output.write(line)

This takes care to drop the first line from each input file except the first, on the assumption that you have CSV files with a single-line header.

(Do yourself a favor and stop using headers in CSV files, though. Then the code can be simplified somewhat.)

This performs no attempt to verify that the CSV files have identical structures. You should make sure that they have the same number of columns and the same data in the same columns.

There could also be problems if your data mixes Windows and Unix line feeds. A simple tweak is to open the files in binary mode, even though they are technically text files.

Answer 2

Use the csv module to skip the headers and create a list of rows.

import csv
import glob
import pandas as pd

rows = []
for filename in glob.glob("*.csv"):
   with open(filename, newline="") as f:
      reader = csv.reader(f)
      columns = next(reader)
      rows.extend(reader)

df = pd.DataFrame(rows, columns=columns)

Answer 3

Try using polars, as the author sells itself: "Lightning-fast DataFrame library for Rust and Python". For my use case, i have 110 files of ~1mb, polars is 21x faster:

%%timeit
#Polars
files = glob.glob(r"U:\Negocios CETIP\*.CSV")
df_pl = pl.read_csv(files[0], sep=";", skip_rows=1, infer_schema_length=0)
for file in files[1:]:
    df_pl = pl.concat([df_pl, pl.read_csv(file, sep=";", skip_rows=1, 
                       infer_schema_length=0)], how="diagonal")

3.2 s ± 57.5 ms per loop (mean ± std. dev. of 7 runs, 1 loop each)

%%timeit 
#Pandas
files = glob.glob(r"U:\Negocios CETIP\*.CSV")
df_pd = pd.read_csv(files[0], delimiter=";")
for file in files[1:]:
    df_pd = pd.concat([df_pd, pd.read_csv(file, delimiter=";")])

1min 8s ± 784 ms per loop (mean ± std. dev. of 7 runs, 1 loop each)

Polars has even another ways to optmize that I didn't use. For example, lazy function (which allows paralelization from the library) and also infering the data types correctly.

Also, try invoking polars (or pandas) concat fewer times. It's best to put all the read files in a list and then concat them all. If your memory can't handle that, try concatening after a few read files. In my test, it's 9x faster:

%%timeit
#Polars adv
files = glob.glob(r"U:\Negocios CETIP\*.CSV")
df_pl2 = []
for file in files:
    df_pl2.append(pl.read_csv(file, sep=";", skip_rows=1, infer_schema_length=0))
df_pl2 = pl.concat(df_pl2, how="diagonal")

362 ms ± 8.6 ms per loop (mean ± std. dev. of 7 runs, 1 loop each)

Just a little explanation on the code: pl.read_csv(file, sep=";", skip_rows=1, infer_schema_length=0)

1. I skipped the first row, because, in my files, the first line was useless, the headers were on the 2nd row. Pandas recognized that
2. infer_schema_length=0 is a bad practice, it forces polars to identify all columns as string. I was just lazy to give the correct dtype. You probably won't need that

pl.concat(df_pl2, how="diagonal") diagonal is important in polars when dataframes don't have the exact same columns (some of my files were missing some columns)

Answer 4

You can use multiprocessing:

import pandas as pd
import pathlib
import multiprocessing as mp


def read_csv(files):
    return pd.concat([pd.read_csv(filename) for filename in files], axis=1)


if __name__ == '__main__':
    batch = 10
    files = sorted(pathlib.Path('PATH').glob('*.csv'))
    with mp.Pool(mp.cpu_count()) as pool:
        dfs = pool.map(read_csv, (files[i:i+batch]
                                  for i in range(0, len(files), batch)))
    df = pd.concat(dfs)

Note: you can replace (files[i:i+batch] for i in range(0, len(files), batch)) by itertools.pairwise(files, batch) if you use Python>=3.10

Answer 5

This is assuming that there is no header and that the csv files a look something like this:

<start of file>
1,test,12.5,hello there,0
<end of file>

If you necessarily want a python solution, here is a plain text version.

import glob

files = glob.glob('*.csv')

with open('output.csv', 'a') as output:

    for fname in files:
        with open(fname, 'r') as input:
            output.write(input.readline())

Keep in mind that this really only works for single lines csv files, because we only call readline() once per input file, i.e., just reading the first line. readline furthermore automatically adds a line break to the end of the line if there is none.

Answer 6

EDIT: I first posted an answer using Dask. But then I saw this answer.

I think you should check it out.