What is the fastest way to combine 100 CSV files with headers into one?

Viewed 8106

What is the fastest way to combine 100 CSV files with headers into one with the following setup:

  1. The total size of files is 200 MB. (The size is reduced to make the computation time visible)
  2. The files are located on an SSD with a maximum speed of 240 MB/s.
  3. The CPU has 4 cores so multi-threading and multiple processes are allowed.
  4. There exists only one node (important for Spark)
  5. The available memory is 15 GB. So the files easily fit into memory.
  6. The OS is Linux (Debian Jessie)
  7. The computer is actually a n1-standard-4 instance in Google Cloud.

(The detailed setup was included to make the scope of the question more specific. The changes were made according to the feedback here)

File 1.csv:

a,b
1,2

File 2.csv:

a,b
3,4

Final out.csv:

a,b
1,2
3,4

According to my benchmarks the fastest from all the proposed methods is pure python. Is there any faster method?

Benchmarks (Updated with the methods from comments and posts):

Method                      Time
pure python                  0.298s
sed                          1.9s
awk                          2.5s
R data.table                 4.4s
R data.table with colClasses 4.4s
Spark 2                     40.2s
python pandas          1min 11.0s

Versions of tools:

sed 4.2.2
awk: mawk 1.3.3 Nov 1996
Python 3.6.1
Pandas 0.20.1
R 3.4.0
data.table 1.10.4
Spark 2.1.1

Code in Jupyter notebooks:

sed:

%%time
!head temp/in/1.csv > temp/merged_sed.csv
!sed 1d temp/in/*.csv >> temp/merged_sed.csv

Pure Python all binary read-write with undocumented behavior of "next":

%%time
with open("temp/merged_pure_python2.csv","wb") as fout:
    # first file:
    with open("temp/in/1.csv", "rb") as f:
        fout.write(f.read())
    # now the rest:    
    for num in range(2,101):
        with open("temp/in/"+str(num)+".csv", "rb") as f:
            next(f) # skip the header
            fout.write(f.read())

awk:

%%time
!awk 'NR==1; FNR==1{{next}} 1' temp/in/*.csv > temp/merged_awk.csv

R data.table:

%%time
%%R
filenames <- paste0("temp/in/",list.files(path="temp/in/",pattern="*.csv"))
files <- lapply(filenames, fread)
merged_data <- rbindlist(files, use.names=F)
fwrite(merged_data, file="temp/merged_R_fwrite.csv", row.names=FALSE)

R data.table with colClasses:

%%time
%%R
filenames <- paste0("temp/in/",list.files(path="temp/in/",pattern="*.csv"))
files <- lapply(filenames, fread,colClasses=c(
    V1="integer",
    V2="integer",
    V3="integer",
    V4="integer",
    V5="integer",
    V6="integer",
    V7="integer",
    V8="integer",
    V9="integer",
    V10="integer"))
merged_data <- rbindlist(files, use.names=F)
fwrite(merged_data, file="temp/merged_R_fwrite.csv", row.names=FALSE)

Spark (pyspark):

%%time
df = spark.read.format("csv").option("header", "true").load("temp/in/*.csv")
df.coalesce(1).write.option("header", "true").csv("temp/merged_pyspark.csv")

Python pandas:

%%time
import pandas as pd

interesting_files = glob.glob("temp/in/*.csv")
df_list = []
for filename in sorted(interesting_files):
    df_list.append(pd.read_csv(filename))
full_df = pd.concat(df_list)

full_df.to_csv("temp/merged_pandas.csv", index=False)

Data was generated by:

%%R
df=data.table(replicate(10,sample(0:9,100000,rep=TRUE)))
for (i in 1:100){
    write.csv(df,paste0("temp/in/",i,".csv"), row.names=FALSE)
}
2 Answers

According to the benchmarks in the question the fastest method is pure Python with undocumented "next()" function behavior with binary files. The method was proposed by Stefan Pochmann

Benchmarks:

Benchmarks (Updated with the methods from comments and posts):

Method                      Time
pure python                  0.298s
sed                          1.9s
awk                          2.5s
R data.table                 4.4s
R data.table with colClasses 4.4s
Spark 2                     40.2s
python pandas          1min 11.0s

Versions of tools:

sed 4.2.2
awk: mawk 1.3.3 Nov 1996
Python 3.6.1
Pandas 0.20.1
R 3.4.0
data.table 1.10.4
Spark 2.1.1

Pure Python code:

with open("temp/merged_pure_python2.csv","wb") as fout:
    # first file:
    with open("temp/in/1.csv", "rb") as f:
        fout.write(f.read())
    # now the rest:    
    for num in range(2,101):
        with open("temp/in/"+str(num)+".csv", "rb") as f:
            next(f) # skip the header
            fout.write(f.read())
Related