skip to content

In pandas read_csv, what do engine='pyarrow' and dtype_backend='pyarrow' each change, and what stops working when you switch to the pyarrow engine?

level: seniorimportance: nice to knowfreq 35%

answer

  1. two separate knobs
  2. who parses versus what you get
  3. options the fast parser rejects
  4. names, not positions

basics

~20 s

engine picks the parser that reads the text; dtype_backend picks the dtypes of the resulting columns. They are independent. The pyarrow engine is multithreaded but rejects chunksize, iterator, nrows, comment, thousands, converters and several other options with ValueError.

solid answer

~40 s

`engine` chooses **who parses** the file: `'c'` (the default), `'python'` (slowest, most features) or `'pyarrow'` (multithreaded, usually fastest on large files, still marked experimental). `dtype_backend` chooses **what the columns become**: unset gives the default dtypes (NumPy for numbers), `'numpy_nullable'` gives `Int64`, `Float64` and `boolean`, and `'pyarrow'` gives `ArrowDtype` columns such as `int64[pyarrow]`. Any engine works with any backend. Switching to `engine='pyarrow'` raises `ValueError` for `chunksize`, `iterator`, `nrows`, `skipfooter`, `comment`, `thousands`, `converters`, `dayfirst`, `quoting` and a few more; `usecols` must be column names and `na_values` cannot be a dict. For a 20 GB file that must be streamed, the C engine with `chunksize` stays.

code

python · 15 lines
python
import io

import pandas as pd  # the pyarrow engine and backend need pyarrow installed

raw = "user_id,ms\nu1,120\nu2,\n"

print(pd.read_csv(io.StringIO(raw))["ms"].dtype)  # float64
print(pd.read_csv(io.StringIO(raw), engine="pyarrow")["ms"].dtype)  # float64
print(pd.read_csv(io.StringIO(raw), dtype_backend="numpy_nullable")["ms"].dtype)  # Int64
print(pd.read_csv(io.StringIO(raw), dtype_backend="pyarrow")["ms"].dtype)  # int64[pyarrow]

try:
    pd.read_csv(io.StringIO(raw), engine="pyarrow", nrows=1)
except ValueError as exc:
    print(exc)  # The 'nrows' option is not supported with the 'pyarrow' engine

go deeper

for a junior

Recall that engine and dtype_backend are separate arguments: one chooses the parser, the other the column dtypes of the result.

for a middle

Explain what each engine supports, why a regex sep forces the python engine, and what dtypes numpy_nullable and pyarrow backends produce.

for a senior

Switch a slow read to the pyarrow engine safely by auditing the call for rejected options, and keep chunked reads on the C engine.

for a principal

Decide whether readers across the codebase default to a nullable or Arrow backend, weighing consistency against the experimental status the docs still note.

## Two different questions `pd.read_csv` exposes two arguments whose values include `'pyarrow'`, and they answer different questions: - **`engine`** decides which parser turns bytes into columns. - **`dtype_backend`** decides which dtypes those columns have in the returned DataFrame. The pandas user guide on PyArrow says it directly: a reader does not need `engine='pyarrow'` to return PyArrow-backed data. All combinations are valid. | Call | Parser | Integer column with a blank | |---|---|---| | `read_csv(path)` | C | `float64` (NaN forces a float) | | `read_csv(path, engine='pyarrow')` | PyArrow | `float64` | | `read_csv(path, dtype_backend='numpy_nullable')` | C | `Int64` with `<NA>` | | `read_csv(path, dtype_backend='pyarrow')` | C | `int64[pyarrow]` with a null | | `read_csv(path, engine='pyarrow', dtype_backend='pyarrow')` | PyArrow | `int64[pyarrow]` | ## The three engines 1. **`'c'`**, the default: fast, supports `chunksize`, `low_memory` and almost every option. 2. **`'python'`**: the most complete and the slowest. It is the only engine for a regex or multi-character `sep` (other than `\s+`), for `sep=None` (delimiter sniffing with `csv.Sniffer`) and for `skipfooter`. If you pass such a `sep` without naming an engine, pandas falls back to `'python'` and emits a `ParserWarning`; if you named `engine='c'` explicitly, it raises `ValueError` instead. 3. **`'pyarrow'`**: parses with multiple threads (the docstring notes it is the only engine that does), which pays off on large files. The user guide calls it experimental and less robust than the C engine. ## What the PyArrow engine rejects The reader source keeps an explicit list of options the PyArrow engine does not support; setting any of them to a non-default value raises `ValueError` naming the option: - `chunksize` and `iterator`, so no streaming - `nrows`, `skipfooter`, `comment`, `thousands`, `dayfirst` - `converters`, `quoting`, `lineterminator`, `dialect`, `skipinitialspace` - `memory_map`, `float_precision`, `low_memory` The PyArrow wrapper adds a few narrower rules: - **`usecols` must be names.** Integer positions or a callable raise `ValueError`. - **`na_values` cannot be a dict**, and every marker must be a string. - **A callable `on_bad_lines`** gets PyArrow's invalid-row handler signature, not the list-of-strings signature the Python engine uses. The string values `'error'`, `'warn'` and `'skip'` are supported. Note that the user guide's list of unsupported options is older than the source and still names `on_bad_lines`; the 3.0.6 source accepts it. ## What `dtype_backend` changes `dtype_backend` (described in the docstring as still experimental) only affects the **result**: - **Unset**: NumPy dtypes for numbers, so an integer column with a missing value becomes `float64` to hold NaN. - **`'numpy_nullable'`**: nullable extension dtypes (`Int64`, `Float64`, `boolean`) that hold `pd.NA`, so integers stay integers. - **`'pyarrow'`**: every column becomes an `ArrowDtype`, for example `int64[pyarrow]` or `double[pyarrow]`, with Arrow nulls. What these dtypes mean downstream, and converting an existing frame with `convert_dtypes`, belong to the lesson on pandas casting and Arrow-backed dtypes; here the point is that the argument is a read-time choice independent of the parser. ## Checking a switch before it ships Changing the engine of an existing read is a small diff with more surface than it looks. A careful switch goes like this: 1. Grep the call for options on the rejected list; each one either goes or keeps the old engine. 2. Rewrite positional `usecols` as names and split a per-column `na_values` dict into a single list, or keep the C engine. 3. Read a sample with both engines and compare `df.dtypes` and `df.isna().sum()`: the two parsers infer types and missing values independently, and a column that was `float64` under one can differ under the other. 4. Time the read on a realistic file, since the gain comes from threads and shows on large inputs, not on a test fixture. ## Choosing for the 20 GB clickstream file - **Streaming is a C-engine job.** The PyArrow engine reads the whole table at once and refuses `chunksize`, so a file that does not fit stays on `engine='c'` with `chunksize`, `usecols` and `dtype`. - **For a file that fits**, `engine='pyarrow'` often cuts read time; check that none of the rejected options are in the call, and switch `usecols` to names. - **Pick `dtype_backend` for what comes next**, not for parsing speed: `'pyarrow'` when the frame will be written to a columnar format or handed to Arrow-aware code, `'numpy_nullable'` when integer columns with gaps must stay integers.

  • You pass sep=';|,' without an engine. What happens, and what if you had written engine='c'?
    A regex separator is not supported by the C engine, so with no engine named pandas falls back to the Python engine and emits a `ParserWarning` saying so; naming `engine='python'` avoids the warning. With `engine='c'` given explicitly, pandas does not override your choice and raises `ValueError` instead.
  • Does dtype_backend='pyarrow' make the read itself faster?
    Not by itself: the parser is still whatever `engine` says, the C engine by default. `dtype_backend` changes the dtypes of the result, which can help later steps such as writing to a columnar file or Arrow-aware code. For a faster parse of a file that fits in memory, set `engine='pyarrow'`; the two can be combined.

  • dtype_backend='pyarrow' only works together with engine='pyarrow'
  • engine='pyarrow' on its own returns ArrowDtype columns
  • The pyarrow engine supports every option the C engine does
  • A multi-character sep runs on the C engine like a single comma
  • engine='pyarrow' is the way to stream a file larger than memory