.. currentmodule:: pyarrow.csv

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], duration (from numeric strings), string or binary data
• opportunistic dictionary encoding of string and binary 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

CSV reading and writing functionality is available through the :mod:`pyarrow.csv` module. In many cases, you will simply call the :func:`read_csv` function with the file path you want to read from:

>>> from pyarrow import csv
>>> import pyarrow as pa
>>> import pandas as pd
>>> fn = 'tips.csv.gz'  # doctest: +SKIP
>>> table = csv.read_csv(fn)  # doctest: +SKIP
>>> table  # doctest: +SKIP
pyarrow.Table
total_bill: double
tip: double
sex: string
smoker: string
day: string
time: string
size: int64
>>> len(table)  # doctest: +SKIP
244
>>> df = table.to_pandas()  # doctest: +SKIP
>>> df.head()  # doctest: +SKIP
   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 :func:`write_csv` with a :class:`pyarrow.RecordBatch` or :class:`pyarrow.Table` and a path or file-like object:

>>> table = pa.table({'col1': [1, 2, 3], 'col2': ['a', 'b', 'c']})
>>> csv.write_csv(table, "tips.csv")
>>> with pa.CompressedOutputStream("tips.csv.gz", "gzip") as out:
...     csv.write_csv(table, out)

To alter the default parsing settings in case of reading CSV files with an unusual structure, you should create a :class:`ParseOptions` instance and pass it to :func:`read_csv`:

>>> def skip_handler(row):
...     pass
>>> table = csv.read_csv('tips.csv.gz', parse_options=csv.ParseOptions(
...    delimiter=";",
...    invalid_row_handler=skip_handler
... ))
>>> table
pyarrow.Table
col1,"col2": string
----
col1,"col2": [["1,"a"","2,"b"","3,"c""]]

Available parsing options are:

.. autosummary::

  ~ParseOptions.delimiter
  ~ParseOptions.quote_char
  ~ParseOptions.double_quote
  ~ParseOptions.escape_char
  ~ParseOptions.newlines_in_values
  ~ParseOptions.ignore_empty_lines
  ~ParseOptions.invalid_row_handler

.. seealso::

   For more examples see :class:`ParseOptions`.

To alter how CSV data is converted to Arrow types and data, you should create a :class:`ConvertOptions` instance and pass it to :func:`read_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),
...     }
... ))
>>> table
pyarrow.Table
col1: int64
col2: string
----
col1: [[1,2,3]]
col2: [["a","b","c"]]

Available convert options are:

.. autosummary::

  ~ConvertOptions.check_utf8
  ~ConvertOptions.column_types
  ~ConvertOptions.default_column_type
  ~ConvertOptions.null_values
  ~ConvertOptions.true_values
  ~ConvertOptions.false_values
  ~ConvertOptions.decimal_point
  ~ConvertOptions.timestamp_parsers
  ~ConvertOptions.strings_can_be_null
  ~ConvertOptions.quoted_strings_can_be_null
  ~ConvertOptions.auto_dict_encode
  ~ConvertOptions.auto_dict_max_cardinality
  ~ConvertOptions.include_columns
  ~ConvertOptions.include_missing_columns

.. seealso::

   For more examples see :class:`ConvertOptions`.

For memory-constrained environments, it is also possible to read a CSV file one batch at a time, using :func:`open_csv`.

There are a few caveats:

1. For now, the incremental reader is always single-threaded (regardless of :attr:`ReadOptions.use_threads`)
2. Type inference is done on the first block and types are frozen afterwards; to make sure the right data types are inferred, either set :attr:`ReadOptions.block_size` to a large enough value, or use :attr:`ConvertOptions.column_types` to set the desired data types explicitly.

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 :class:`ReadOptions` class:

>>> table = csv.read_csv('tips.csv.gz', read_options=csv.ReadOptions(
...    column_names=["n_legs", "entry"],
...    skip_rows=1
... ))
>>> table
pyarrow.Table
n_legs: int64
entry: string
----
n_legs: [[1,2,3]]
entry: [["a","b","c"]]

Available read options are:

.. autosummary::

  ~ReadOptions.use_threads
  ~ReadOptions.block_size
  ~ReadOptions.skip_rows
  ~ReadOptions.skip_rows_after_names
  ~ReadOptions.column_names
  ~ReadOptions.autogenerate_column_names
  ~ReadOptions.encoding

.. seealso::

   For more examples see :class:`ReadOptions`.

To alter the default write settings in case of writing CSV files with different conventions, you can create a :class:`WriteOptions` instance and pass it to :func:`write_csv`:

>>> # Omit the header row (include_header=True is the default)
>>> options = csv.WriteOptions(include_header=False)
>>> csv.write_csv(table, "data.csv", options)

To write CSV files one batch at a time, create a :class:`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:

>>> with csv.CSVWriter("data.csv", table.schema) as writer:
...     writer.write_table(table)

Due to the structure of CSV files, one cannot expect the same levels of performance as when reading dedicated binary formats like :ref:`Parquet <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 :class:`ReadOptions` class. Multi-threaded reading is the default for highest performance, distributing the workload efficiently over all available cores.