File formats and data interchange
Read and write CSV, Parquet, JSON, and Excel with Polars. Exchange Arrow and pandas data, combine files, and scan partitioned cloud datasets.
Reusable expressions, selectors, and pipe#
Build a column recipe in Python without running one Python function per row.
A function returning Expr is an expression factory. Python calls it once to construct a native calculation. A selector chooses columns by a property such as type or name. pipe passes a whole frame to a function so a named transformation can be reused. None of these inherently invokes Python for every cell.
import polars as pl
import polars.selectors as cs
def clean_id(name: str) -> pl.Expr:
return pl.col(name).str.strip_chars().str.to_uppercase().alias(name)
def clean_export(frame):
return frame.with_columns(clean_id("customer"), cs.numeric().fill_null(0))
df = pl.DataFrame({"customer": [" a ", "b"], "sales": [100, None], "refunds": [5, 10]})
clean = df.pipe(clean_export)
print("clean:", clean.to_dicts())
print("numeric totals:", clean.select(cs.numeric().sum()).to_dicts())
print("same lazy result:", clean.equals(df.lazy().pipe(clean_export).collect()))clean: [{'customer': 'A', 'sales': 100, 'refunds': 5}, {'customer': 'B', 'sales': 0, 'refunds': 10}]
numeric totals: [{'sales': 100, 'refunds': 15}]
same lazy result: TrueA selector can expand to several columns, so give each generated result a unique name with name.suffix where needed. pl.sum_horizontal sums across columns within each row; col.sum sums down a column. pl.fold expresses a custom reduction across columns when built-in horizontal operations do not fit. Ensure a broad numeric selector does not accidentally fill missing identifiers or dates with a meaningless value.
Official reference: Reusable expressions, selectors, and pipe.
Eager execution, lazy execution, read, and scan#
Use read for a table now and scan for a file-backed plan.
read_csv reads and materializes a DataFrame. scan_csv describes reading a file and returns a LazyFrame. A query optimizer rewrites a lazy plan to perform less work without changing the intended result. Starting with read_csv(...).lazy() cannot avoid reading the original file into memory. Calling collect inside every helper also prevents Polars from optimizing one complete pipeline.
from pathlib import Path
from tempfile import TemporaryDirectory
import polars as pl
from polars.testing import assert_frame_equal
with TemporaryDirectory() as folder:
path = Path(folder) / "sales.csv"
path.write_text(
"region,cents,status\nDhaka,100,paid\nDhaka,50,open\nSylhet,200,paid\n",
encoding="utf-8",
)
eager = (
pl.read_csv(path)
.filter(pl.col("status") == "paid")
.group_by("region")
.agg(pl.col("cents").sum())
.sort("region")
)
query = (
pl.scan_csv(path)
.filter(pl.col("status") == "paid")
.group_by("region")
.agg(pl.col("cents").sum())
.sort("region")
)
print("planned schema:", dict(query.collect_schema()))
lazy = query.collect()
assert_frame_equal(eager, lazy)
print("both results:", lazy.to_dicts())planned schema: {'region': String, 'cents': Int64}
both results: [{'region': 'Dhaka', 'cents': 100}, {'region': 'Sylhet', 'cents': 200}]Each collect generally executes the plan again. Reusing a LazyFrame variable does not guarantee a persistent cached table. collect_schema resolves types but does not execute the full result. For several related outputs consider collect_all or lazy sinks after checking memory and execution behavior.
Official reference: Eager execution, lazy execution, read, and scan.
CSV input, schema inference, and malformed records#
CSV stores text, so reading must decide which values and types the text represents.
CSV is comma-separated text with optional quotes around fields. A delimiter separates fields; a header names columns. separator, quote_char, skip_rows, null_values, and encoding must match the export. infer_schema_length controls how many rows are sampled to infer types. A later text value can break an initially numeric column. Use explicit types or read uncertain columns as strings first.
from io import StringIO
import polars as pl
text = "account,qty,note\n001,2,ok\n002,wrong,NA\n003,,missing\n"
raw = pl.read_csv(StringIO(text), infer_schema=False, null_values=["NA", ""])
clean = raw.with_columns(pl.col("qty").cast(pl.Int64, strict=False).alias("quantity"))
print("IDs:", clean["account"].to_list())
print("clean:", clean.to_dicts())
print(
"parse failures:",
clean.filter(pl.col("qty").is_not_null() & pl.col("quantity").is_null())[
"account"
].to_list(),
)
try:
pl.read_csv(StringIO(text), schema_overrides={"qty": pl.Int64})
except pl.exceptions.ComputeError:
print("strict numeric input: rejected")IDs: ['001', '002', '003']
clean: [{'account': '001', 'qty': '2', 'note': 'ok', 'quantity': 2}, {'account': '002', 'qty': 'wrong', 'note': None, 'quantity': None}, {'account': '003', 'qty': None, 'note': 'missing', 'quantity': None}]
parse failures: ['002']
strict numeric input: rejectedfrom io import StringIO
import polars as pl
ragged = "id,amount\nA,100,unexpected\n"
try:
pl.read_csv(StringIO(ragged))
except pl.exceptions.ComputeError:
print("default: extra field rejected")
print(
"explicit truncation:",
pl.read_csv(StringIO(ragged), truncate_ragged_lines=True).to_dicts(),
)
empty = pl.read_csv(
StringIO("id,amount\n"), schema={"id": pl.String, "amount": pl.Int64}
)
print("header-only input:", empty.shape, dict(empty.schema))default: extra field rejected
explicit truncation: [{'id': 'A', 'amount': 100}]
header-only input: (0, 2) {'id': String, 'amount': Int64}ignore_errors attempts to continue after some errors; it is not a complete malformed-record audit. truncate_ragged_lines discards extra fields. Unterminated quotes and structurally broken records may still fail. For quarantine, parse CSV records with a proper CSV parser and validate field counts; never split lines by comma because quoted fields and embedded newlines are valid. A zero-byte file may require raise_if_empty=False and a supplied schema; a header-only file already defines its names.
Official reference: CSV input, schema inference, and malformed records.
Parquet and Arrow IPC file round trips#
Use typed binary files when a pipeline should retain schema and avoid reparsing text.
Parquet is a compressed column-oriented file format with typed columns and statistics. A row group is a block of rows stored by column inside the file. Query filters and selected columns may allow Polars to skip irrelevant data. Arrow IPC is a binary interchange format for Arrow tables; it is also used by Feather v2. CSV does not carry a full schema and does not represent nested columns cleanly.
from decimal import Decimal
from datetime import date
from pathlib import Path
from tempfile import TemporaryDirectory
import polars as pl
from polars.testing import assert_frame_equal
df = pl.DataFrame(
{
"id": ["001", "002"],
"day": [date(2026, 9, 1), date(2026, 9, 2)],
"amount": [Decimal("12.50"), None],
},
schema={"id": pl.String, "day": pl.Date, "amount": pl.Decimal(18, 2)},
)
with TemporaryDirectory() as folder:
pq, ipc = Path(folder) / "sales.parquet", Path(folder) / "sales.arrow"
df.write_parquet(pq, compression="zstd", statistics=True)
df.write_ipc(ipc)
assert_frame_equal(df, pl.read_parquet(pq))
assert_frame_equal(df, pl.read_ipc(ipc, memory_map=False))
print("round trips: identical values and schema")
print(
"selected:",
pl.scan_parquet(pq)
.filter(pl.col("amount").is_not_null())
.select("id", "amount")
.collect()
.to_dicts(),
)round trips: identical values and schema
selected: [{'id': '001', 'amount': Decimal('12.50')}]Parquet can avoid reading unselected column data; CSV still has to parse text records even when fewer columns reach later operations. Arrow memory mapping can help some local IPC reads, but compression and conversions can require allocation. Use scan_parquet or scan_ipc for lazy file plans; test interoperability and decimal/time-zone preservation with the actual downstream reader.
Official reference: Parquet and Arrow IPC file round trips.
JSON, NDJSON, and quarantine for bad records#
A JSON array is one document; NDJSON stores a separate JSON object on each line.
NDJSON (newline-delimited JSON) is useful for event files and streaming scans. Use read_json for regular JSON, read_ndjson for immediate line-based input, and scan_ndjson for a lazy file plan. JSON cannot portably encode NaN or infinity; choose a policy before exporting. Decimal values and date formats also need an explicit interchange agreement.
import json
from pathlib import Path
from tempfile import TemporaryDirectory
import polars as pl
lines = ['{"id":"A","cents":100}', "{bad JSON}", '{"id":"B","cents":200}', '{"id":"C"}']
valid, rejected = [], []
for number, line in enumerate(lines, start=1):
try:
row = json.loads(line)
if (
not isinstance(row, dict)
or not isinstance(row.get("id"), str)
or type(row.get("cents")) is not int
):
raise ValueError("required fields")
valid.append(row)
except (json.JSONDecodeError, ValueError):
rejected.append({"line": number, "raw": line})
with TemporaryDirectory() as folder:
path = Path(folder) / "valid.ndjson"
path.write_text("".join(json.dumps(row) + "\n" for row in valid), encoding="utf-8")
df = pl.scan_ndjson(path, schema={"id": pl.String, "cents": pl.Int64}).collect()
print("accepted:", df.to_dicts())
print("rejected lines:", [row["line"] for row in rejected])
out = Path(folder) / "output.ndjson"
df.write_ndjson(out)
assert df.equals(pl.read_ndjson(out))
print("NDJSON round trip: passed")accepted: [{'id': 'A', 'cents': 100}, {'id': 'B', 'cents': 200}]
rejected lines: [2, 4]
NDJSON round trip: passedParse records before loading typed columns to separate invalid JSON from nullable fields. For large files, write accepted and rejected records incrementally with bounded buffers and a reject count.
Official reference: JSON, NDJSON, and quarantine for bad records.
Excel input with fastexcel#
Read a chosen worksheet and control types instead of relying on spreadsheet formatting.
Excel workbooks contain worksheets, cell values, and formatting. A displayed account number may already have lost leading zeros if Excel stored it as a number. fastexcel is the companion package behind Polars' calamine reader engine. read_excel is eager; convert recurring exports to Parquet before building larger lazy pipelines. Choose the worksheet explicitly and check inferred types.
python -m pip install "fastexcel==0.21.0" "xlsxwriter==3.2.9"from pathlib import Path
from tempfile import TemporaryDirectory
import polars as pl
with TemporaryDirectory() as folder:
path = Path(folder) / "sales.xlsx"
pl.DataFrame({"account": ["001", "002"], "quantity": [2, 3]}).write_excel(
path, worksheet="Sales"
)
df = pl.read_excel(
path,
sheet_name="Sales",
engine="calamine",
schema_overrides={"account": pl.String, "quantity": pl.Int64},
)
print(df.to_dicts())
print("schema:", dict(df.schema))[{'account': '001', 'quantity': 2}, {'account': '002', 'quantity': 3}]
schema: {'account': String, 'quantity': Int64}Install openpyxl and use engine="openpyxl" when a difficult workbook needs that reader; xlsx2csv is another alternative that converts cells through CSV. They have different capabilities and options, so do not blindly reuse read_options across engines. Empty sheets, multiple sheet selections, formulas, merged cells, and mixed text/numeric cells need explicit policies. A reader cannot invent formula results absent from the workbook.
Official reference: Excel input with fastexcel.
Excel reporting with XlsxWriter#
Export a small, reader-friendly summary after doing the computation in Polars.
XlsxWriter is the companion package used by write_excel. It writes worksheets, tables, formats, and charts. A number format changes how a value is displayed, not its stored numerical precision. Export summaries instead of millions of raw records: Excel worksheets have a row limit, and writing a workbook requires materialized data.
from pathlib import Path
from tempfile import TemporaryDirectory
import xlsxwriter
import polars as pl
with TemporaryDirectory() as folder:
path = Path(folder) / "monthly.xlsx"
sales = pl.DataFrame(
{"region": ["Dhaka", "Sylhet"], "revenue_cents": [123450, 67890]}
)
with xlsxwriter.Workbook(
path, {"strings_to_formulas": False, "strings_to_urls": False}
) as workbook:
sales.write_excel(
workbook=workbook,
worksheet="Sales",
table_name="SalesTable",
autofit=True,
column_formats={"revenue_cents": "#,##0"},
)
sales.select(pl.col("revenue_cents").sum().alias("total_cents")).write_excel(
workbook=workbook, worksheet="Summary", table_name="SummaryTable"
)
print("sheets:", sorted(pl.read_excel(path, sheet_id=0).keys()))
print("summary:", pl.read_excel(path, sheet_name="Summary").to_dicts())sheets: ['Sales', 'Summary']
summary: [{'total_cents': 191340}]The workbook options prevent strings starting with = from being interpreted as formulas and prevent automatic hyperlinks. Spreadsheet formula handling also matters in CSV exports because the eventual spreadsheet reader decides how to interpret text. Format integer cents as cents or deliberately convert to display units; do not label 123450 cents as 123450 dollars.
Official reference: Excel reporting with XlsxWriter.
Arrow interchange with PyArrow#
Arrow is a typed in-memory format shared by many analytical tools.
Apache Arrow defines column-oriented buffers and metadata for passing data between tools. PyArrow is its Python implementation and supports table and dataset interfaces. to_arrow and from_arrow exchange typed tables. Some conversions reuse buffers with little copying; others must allocate, especially when type representations differ. Zero-copy means sharing existing memory, not a promise for every conversion.
python -m pip install "pyarrow==25.0.1"from pathlib import Path
from tempfile import TemporaryDirectory
import pyarrow as pa
import pyarrow.dataset as ds
import polars as pl
from polars.testing import assert_frame_equal
df = pl.DataFrame({"account": ["001", "002"], "cents": [100, None]})
table = df.to_arrow()
assert isinstance(table, pa.Table)
assert_frame_equal(df, pl.from_arrow(table))
with TemporaryDirectory() as folder:
df.write_parquet(Path(folder) / "part.parquet")
query = pl.scan_pyarrow_dataset(ds.dataset(folder, format="parquet")).filter(
pl.col("cents").is_not_null()
)
print("Arrow dataset:", query.collect().to_dicts())
print("Arrow round trip: passed")Arrow dataset: [{'account': '001', 'cents': 100}]
Arrow round trip: passedNative scan_parquet exposes the native Polars reader optimizations. A PyArrow dataset scan is useful for an existing Arrow dataset workflow, but it has different filter and streaming behavior and is marked unstable. Do not assume all native scan options or streaming guarantees transfer to this route.
Official reference: Arrow interchange with PyArrow.
NumPy and pandas interoperability#
Convert at a library boundary, after narrowing the data you need.
NumPy provides arrays for numerical calculations and many machine-learning tools. pandas provides another table API used by existing applications. Conversion can change types, missing-value representation, memory layout, and timestamp precision. Keep work in Polars until the other library needs the result; repeatedly converting whole datasets can dominate runtime.
python -m pip install "numpy==2.5.3" "pandas==3.0.6" "pyarrow==25.0.1"import numpy as np
import pandas as pd
import polars as pl
df = pl.DataFrame(
{"spend": [100.0, 200.0], "visits": [2, None]},
schema={"spend": pl.Float64, "visits": pl.Int64},
)
x = df.select("spend").to_numpy()
print("NumPy shape:", x.shape)
print("NumPy values:", x.ravel().tolist())
legacy = df.to_pandas(use_pyarrow_extension_array=True)
print("pandas visits dtype:", str(legacy["visits"].dtype))
back = pl.from_pandas(legacy)
print("back in Polars:", back.to_dicts())
assert back.schema["visits"] == pl.Int64
assert np.array_equal(x, np.array([[100.0], [200.0]]))NumPy shape: (2, 1)
NumPy values: [100.0, 200.0]
pandas visits dtype: int64[pyarrow]
back in Polars: [{'spend': 100.0, 'visits': 2}, {'spend': 200.0, 'visits': None}]NumPy integer arrays cannot represent null and may convert nullable integers to floats with NaN. Arrow-backed pandas columns preserve nullable integers, though later operations may convert them. Include the pandas index explicitly if it is a business key.
Official reference: NumPy and pandas interoperability.
Combine files and handle schema drift#
Schema drift is a change in column names, types, or presence across batches.
Concatenation stacks rows or combines columns. vertical stacks matching schemas. vertical_relaxed can coerce types to a common type. diagonal unions column names and inserts nulls for missing columns; diagonal_relaxed also relaxes types. Relaxed concatenation can silently choose a type inappropriate for your business. Normalize names, units, and types explicitly when combining exports.
from pathlib import Path
from tempfile import TemporaryDirectory
import polars as pl
with TemporaryDirectory() as folder:
root = Path(folder)
(root / "old.csv").write_text("id,cents\n001,100\n", encoding="utf-8")
(root / "new.csv").write_text(
"order_id,cents,channel\n002,200,web\n", encoding="utf-8"
)
old = (
pl.scan_csv(root / "old.csv", infer_schema=False)
.rename({"id": "order_id"})
.with_columns(pl.lit(None, dtype=pl.String).alias("channel"))
)
new = pl.scan_csv(root / "new.csv", infer_schema=False)
def normalize(lf):
return lf.select(
pl.col("order_id").cast(pl.String),
pl.col("cents").cast(pl.Int64),
pl.col("channel").cast(pl.String),
)
combined = (
pl.concat([normalize(old), normalize(new)], how="vertical")
.sort("order_id")
.collect()
)
print(combined.to_dicts())
print("schema:", dict(combined.schema))[{'order_id': '001', 'cents': 100, 'channel': None}, {'order_id': '002', 'cents': 200, 'channel': 'web'}]
schema: {'order_id': String, 'cents': Int64, 'channel': String}import polars as pl
a = pl.DataFrame({"id": ["A"], "qty": pl.Series([2], dtype=pl.Int32)})
b = pl.DataFrame(
{"id": ["B"], "qty": pl.Series([3], dtype=pl.Int64), "source": ["new"]}
)
combined = pl.concat([a, b], how="diagonal_relaxed")
print(combined.to_dicts())
print("qty type:", combined.schema["qty"])
try:
pl.concat([a, b], how="vertical")
except (pl.exceptions.ShapeError, pl.exceptions.SchemaError):
print("strict vertical concat: mismatched schema rejected")[{'id': 'A', 'qty': 2, 'source': None}, {'id': 'B', 'qty': 3, 'source': 'new'}]
qty type: Int64
strict vertical concat: mismatched schema rejectedFor Parquet scans, schema plus missing_columns="insert" can add missing typed fields; extra_columns controls unexpected fields. On-disk types must still match unless an explicit supported cast policy is configured. Missing-column insertion does not fix a string/int mismatch or a cents-versus-dollars change. For files with different CSV schemas, normalize separate scans and then concatenate; one glob scan is not a universal schema merger.
Official reference: Combine files and handle schema drift.
Partitioned datasets and Hive-style folders#
Partitioning splits a dataset into files grouped by a field, often a date or region.
A partitioned dataset is one logical table spread across files. Hive-style paths use folders such as region=Dhaka/day=2026-09-01/part.parquet. A scanner can turn those folder values into columns and skip irrelevant folders when filtering. This is file organization, not a Hive server requirement. Avoid one file per customer when there are millions of customers: many tiny files create overhead.
from pathlib import Path
from tempfile import TemporaryDirectory
import polars as pl
with TemporaryDirectory() as folder:
root = Path(folder)
for region, cents in [("Dhaka", [100, 200]), ("Sylhet", [300])]:
part = root / f"region={region}"
part.mkdir()
pl.DataFrame({"order": list(range(len(cents))), "cents": cents}).write_parquet(
part / "part.parquet"
)
query = pl.scan_parquet(
str(root / "**/*.parquet"),
hive_partitioning=True,
hive_schema={"region": pl.String},
)
result = (
query.filter(pl.col("region") == "Dhaka").select("region", "cents").collect()
)
print("Dhaka:", result.sort("cents").to_dicts())
print("total:", result["cents"].sum())Dhaka: [{'region': 'Dhaka', 'cents': 100}, {'region': 'Dhaka', 'cents': 200}]
total: 300For large lazy datasets, use sink_parquet with PartitionBy. Its API is unstable; pin the version and test file layouts. Define append or replace behavior, keep output paths out of source globs, and validate partition values and types.
Official reference: Partitioned datasets and Hive-style folders.
Cloud storage and credentials#
A cloud object store holds files at addresses such as s3://bucket/path.
Polars can scan supported Amazon S3, Azure, and Google Cloud Storage paths with its native readers. A credential authorizes access; a cloud role can provide temporary credentials without putting secrets in code. storage_options supplies provider settings. Use environment configuration or roles, scope the prefix, select only necessary columns, and filter early to reduce transfers. A wildcard may still require object listing. Remote credentials, cost, region, and permissions must be tested in your environment.
import os
import polars as pl
query = (
pl.scan_parquet(
"s3://YOUR_BUCKET/sales/day=2026-09-*/*.parquet",
hive_partitioning=True,
storage_options={"aws_region": os.environ["AWS_REGION"]},
)
.filter(pl.col("status") == "paid")
.group_by("region")
.agg(pl.col("cents").sum())
)
query.sink_parquet("monthly.parquet", engine="streaming")Replace YOUR_BUCKET and supply the bucket region and authorized credentials. The query writes one total per region to monthly.parquet. Native object-store scans do not need fsspec; use it for an existing filesystem integration or unsupported source.
Official reference: Cloud storage and credentials.
fsspec for Python filesystem workflows#
Use fsspec when surrounding Python code needs a common filesystem interface.
fsspec provides a common Python interface for filesystem implementations. s3fs, gcsfs, and adlfs provide cloud-specific implementations. This is useful for existing file-opening code or non-native sources. Opening a remote file with fsspec and passing it to read_csv is an eager workflow; it does not automatically provide native scan optimizations.
python -m pip install fsspec
# Add the relevant provider only when needed:
python -m pip install s3fs
# Alternatives: gcsfs, adlfsfrom pathlib import Path
from tempfile import TemporaryDirectory
import fsspec
import polars as pl
with TemporaryDirectory() as folder:
path = Path(folder) / "export.csv"
with fsspec.open(str(path), "wt", encoding="utf-8") as stream:
stream.write("id,cents\n001,100\n")
with fsspec.open(str(path), "rb") as stream:
df = pl.read_csv(stream, schema_overrides={"id": pl.String})
print(df.to_dicts())[{'id': '001', 'cents': 100}]Prefer native scan_parquet/scan_csv for supported cloud paths and an optimizable lazy plan. A filesystem file handle can be convenient for integration but may buffer or copy data and may materialize results. Check credentials and implementation-specific options in that filesystem package rather than assuming Polars storage_options and fsspec arguments are interchangeable.
Official reference: fsspec for Python filesystem workflows.
Help improve CodeRef
Allow Google Analytics to measure page visits and use of the guides? It uses cookies after you accept. Your choice won’t affect access to the site. Privacy notice