Reading and Writing CSV files#
Arrow supports reading and writing columnar data from/to CSV files. The features currently offered are the following:
multi-threaded or single-threaded reading
automatic decompression of input files (based on the filename extension, such as
my_data.csv.gz
)fetching column names from the first row in the CSV file
column-wise type inference and conversion to one of
null
,int64
,float64
,date32
,time32[s]
,timestamp[s]
,timestamp[ns]
,string
orbinary
dataopportunistic dictionary encoding of
string
andbinary
columns (disabled by default)detecting various spellings of null values such as
NaN
or#N/A
writing CSV files with options to configure the exact output format
Usage#
CSV reading and writing functionality is available through the
pyarrow.csv
module. In many cases, you will simply call the
read_csv()
function with the file path you want to read from:
>>> from pyarrow import csv
>>> fn = 'tips.csv.gz'
>>> table = csv.read_csv(fn)
>>> table
pyarrow.Table
total_bill: double
tip: double
sex: string
smoker: string
day: string
time: string
size: int64
>>> len(table)
244
>>> df = table.to_pandas()
>>> df.head()
total_bill tip sex smoker day time size
0 16.99 1.01 Female No Sun Dinner 2
1 10.34 1.66 Male No Sun Dinner 3
2 21.01 3.50 Male No Sun Dinner 3
3 23.68 3.31 Male No Sun Dinner 2
4 24.59 3.61 Female No Sun Dinner 4
To write CSV files, just call write_csv()
with a
pyarrow.RecordBatch
or pyarrow.Table
and a path or
file-like object:
>>> import pyarrow as pa
>>> import pyarrow.csv as csv
>>> csv.write_csv(table, "tips.csv")
>>> with pa.CompressedOutputStream("tips.csv.gz", "gzip") as out:
... csv.write_csv(table, out)
Note
The writer does not yet support all Arrow types.
Customized parsing#
To alter the default parsing settings in case of reading CSV files with an
unusual structure, you should create a ParseOptions
instance
and pass it to read_csv()
:
import pyarrow as pa
import pyarrow.csv as csv
table = csv.read_csv('tips.csv.gz', parse_options=csv.ParseOptions(
delimiter=";",
invalid_row_handler=skip_handler
))
Available parsing options are:
The character delimiting individual cells in the CSV data. |
|
The character used optionally for quoting CSV values (False if quoting is not allowed). |
|
Whether two quotes in a quoted CSV value denote a single quote in the data. |
|
The character used optionally for escaping special characters (False if escaping is not allowed). |
|
Whether newline characters are allowed in CSV values. |
|
Whether empty lines are ignored in CSV input. |
|
Optional handler for invalid rows. |
See also
For more examples see ParseOptions
.
Customized conversion#
To alter how CSV data is converted to Arrow types and data, you should create
a ConvertOptions
instance and pass it to read_csv()
:
import pyarrow as pa
import pyarrow.csv as csv
table = csv.read_csv('tips.csv.gz', convert_options=csv.ConvertOptions(
column_types={
'total_bill': pa.decimal128(precision=10, scale=2),
'tip': pa.decimal128(precision=10, scale=2),
}
))
Available convert options are:
Whether to check UTF8 validity of string columns. |
|
Explicitly map column names to column types. |
|
A sequence of strings that denote nulls in the data. |
|
A sequence of strings that denote true booleans in the data. |
|
A sequence of strings that denote false booleans in the data. |
|
The character used as decimal point in floating-point and decimal data. |
|
A sequence of strptime()-compatible format strings, tried in order when attempting to infer or convert timestamp values (the special value ISO8601() can also be given). |
|
Whether string / binary columns can have null values. |
|
Whether quoted values can be null. |
|
Whether to try to automatically dict-encode string / binary data. |
|
The maximum dictionary cardinality for auto_dict_encode. |
|
The names of columns to include in the Table. |
|
If false, columns in include_columns but not in the CSV file will error out. |
See also
For more examples see ConvertOptions
.
Incremental reading#
For memory-constrained environments, it is also possible to read a CSV file
one batch at a time, using open_csv()
.
There are a few caveats:
For now, the incremental reader is always single-threaded (regardless of
ReadOptions.use_threads
)Type inference is done on the first block and types are frozen afterwards; to make sure the right data types are inferred, either set
ReadOptions.block_size
to a large enough value, or useConvertOptions.column_types
to set the desired data types explicitly.
Character encoding#
By default, CSV files are expected to be encoded in UTF8. Non-UTF8 data
is accepted for binary
columns. The encoding can be changed using
the ReadOptions
class:
import pyarrow as pa
import pyarrow.csv as csv
table = csv.read_csv('tips.csv.gz', read_options=csv.ReadOptions(
column_names=["animals", "n_legs", "entry"],
skip_rows=1
))
Available read options are:
Whether to use multiple threads to accelerate reading. |
|
How much bytes to process at a time from the input stream. |
|
The number of rows to skip before the column names (if any) and the CSV data. |
|
The number of rows to skip after the column names. |
|
The column names of the target table. |
|
Whether to autogenerate column names if column_names is empty. |
|
encoding: object |
See also
For more examples see ReadOptions
.
Customized writing#
To alter the default write settings in case of writing CSV files with
different conventions, you can create a WriteOptions
instance and
pass it to write_csv()
:
>>> import pyarrow as pa
>>> import pyarrow.csv as csv
>>> # Omit the header row (include_header=True is the default)
>>> options = csv.WriteOptions(include_header=False)
>>> csv.write_csv(table, "data.csv", options)
Incremental writing#
To write CSV files one batch at a time, create a CSVWriter
. This
requires the output (a path or file-like object), the schema of the data to
be written, and optionally write options as described above:
>>> import pyarrow as pa
>>> import pyarrow.csv as csv
>>> with csv.CSVWriter("data.csv", table.schema) as writer:
>>> writer.write_table(table)
Performance#
Due to the structure of CSV files, one cannot expect the same levels of performance as when reading dedicated binary formats like Parquet. Nevertheless, Arrow strives to reduce the overhead of reading CSV files. A reasonable expectation is at least 100 MB/s per core on a performant desktop or laptop computer (measured in source CSV bytes, not target Arrow data bytes).
Performance options can be controlled through the ReadOptions
class.
Multi-threaded reading is the default for highest performance, distributing
the workload efficiently over all available cores.
Note
The number of concurrent threads is automatically inferred by Arrow.
You can inspect and change it using the cpu_count()
and set_cpu_count()
functions, respectively.