Categories
dask dataframe pandas python

Efficient way to read 15 M lines csv files in python

For my application, I need to read multiple files with 15 M lines each, store them in a DataFrame, and save the DataFrame in HDFS5 format.

I’ve already tried different approaches, notably pandas.read_csv with chunksize and dtype specifications, and dask.dataframe. They both take around 90 seconds to treat 1 file, and so I’d like to know if there’s a way to efficiently treat these files in the described way. In the following, I show some code of the tests I’ve done.

import pandas as pd
import dask.dataframe as dd
import numpy as np
import re
# First approach
store = pd.HDFStore('files_DFs.h5')
chunk_size = 1e6
df_chunk = pd.read_csv(file,
sep="\t",
chunksize=chunk_size,
usecols=['a', 'b'],
converters={"a": lambda x: np.float32(re.sub(r"[^\d.]", "", x)),\
"b": lambda x: np.float32(re.sub(r"[^\d.]", "", x))},
skiprows=15
)
chunk_list = []
for chunk in df_chunk:
chunk_list.append(chunk)
df = pd.concat(chunk_list, ignore_index=True)
store[dfname] = df
store.close()
# Second approach
df = dd.read_csv(
file,
sep="\t",
usecols=['a', 'b'],
converters={"a": lambda x: np.float32(re.sub(r"[^\d.]", "", x)),\
"b": lambda x: np.float32(re.sub(r"[^\d.]", "", x))},
skiprows=15
)
store.put(dfname, df.compute())
store.close()

Here is what the files look like (whitespace consists of a literal tab):

a   b
599.998413 14.142895
599.998413 20.105534
599.998413 6.553850
599.998474 27.116098
599.998474 13.060312
599.998474 13.766775
599.998596 1.826706
599.998596 18.275938
599.998718 20.797491
599.998718 6.132450)
599.998718 41.646194
599.998779 19.145775