Your customer export loads without an error, but the DataFrame is wrong. ZIP code 02134 comes back as 2134, customer 007731 as the integer 7731, and a visits column with a few empty cells as floats. Namibia’s country code, NA, turns into a missing value. The same table exported a day earlier gave some of those columns different types.
What you get
You will end up with one declared schema per library, pandas, Polars, and DuckDB, that loads every export with the same types and raises when a column is renamed or dropped. This is for you if a CSV export feeds an analysis you rerun.
Short answer
Declare every column’s type when pandas, Polars, or DuckDB reads a CSV, so ids keep their leading
zeros and every export loads with the same types. In pandas, pass dtype with str and Int64,
usecols, and keep_default_na=False with na_values=['']. In Polars, give scan_csv a full
schema. In DuckDB, declare types by name in read_csv, because columns applies by position.
You will need
Python 3.12 or later and a CSV export with mixed column types. Verified 2026-10-21 against Python
3.13.16, pandas 3.0.6, Polars 2.0.0, and DuckDB 1.5.6. The sample installs the three libraries
from a pinned requirements.txt into vendor/. It reads two exports of one customer table,
monday.csv and tuesday.csv, plus six copies changed the way upstream exports change. It reads
files only; pulling records from an API into a notebook is a separate task.
Approaches compared
| Approach | When it fits | What it costs you | When to pick something else |
|---|---|---|---|
DuckDB read_csv | You want SQL over the file before any of it reaches a DataFrame | Its columns option applies names by position, and .df() picks int64 or Int64 by whether NULLs appear | The pipeline is pandas or Polars from end to end and needs no SQL |
pandas read_csv | The analysis already lives in pandas and the file fits in memory | Declaring takes a dtype map, usecols, two NA settings, and a parse for each date, and each one you omit falls back to guessing | Exports arrive often and every change in shape must stop the load |
Polars scan_csv | You want one schema object, a lazy scan, and an error for any column it does not name | An added upstream column stops every load until you add it to the schema | Your consumers need pandas objects and you want no conversion step |
pandas takes the most settings to pin down, and anything you leave out goes back to being guessed.
Polars needs one schema and refuses whatever it does not describe, so an extra column upstream
becomes an outage you have to fix. DuckDB declares types in SQL and samples far more rows before it
guesses, but its columns option applies names by position.
See what each reader guesses
CSV has no types for a reader to find, only text between commas, so each library samples rows and
guesses.1 pandas decides per read, or per chunk when you read in chunks. Polars looks at the
first 100 rows, and DuckDB at a sample of 20,480.2 infer.py asks all three about two exports of
the same table, a day apart.
PYTHONPATH=vendor python3 infer.pyinferred customer_id zip visits
pandas monday.csv int64 int64 int64
pandas tuesday.csv int64 str float64
polars monday.csv Int64 Int64 Int64
polars tuesday.csv Int64 String Int64
duckdb monday.csv BIGINT VARCHAR BIGINT
duckdb tuesday.csv VARCHAR VARCHAR BIGINT
what pandas made of the values
monday.csv zip [10001, 2134, 60601]
tuesday.csv customer_id [120048, 7731, 120050]
tuesday.csv country ['US', 'CA', nan]
tuesday.csv visits [nan, 2.0, nan]Every library gives at least one column a different type on Tuesday. In pandas and Polars, a
Canadian postal code turns the ZIP column into text. In DuckDB, one zero-padded id turns
customer_id into VARCHAR. The values are worse than the types. pandas drops the zero from 02134, reads 007731 as 7731, reads
Namibia’s NA as missing, and makes visits a float column because two cells are empty.
Declare the columns in each library
A declared type replaces the guess. Each loader in load.py reads both exports the same way,
whatever rows they hold.
# pandas: dtype names each column's type, and usecols makes a missing column an error.
PANDAS_DTYPES = {
'customer_id': 'str',
'zip': 'str',
'country': 'str',
'signup': 'str', # parsed below, with one format
'visits': 'Int64', # nullable, so an empty cell leaves it an integer column
'amount': 'float64',
}
def with_pandas(path):
frame = pd.read_csv(
path,
usecols=[*PANDAS_DTYPES],
dtype=PANDAS_DTYPES,
keep_default_na=False, # 'NA' is Namibia, not a missing value
na_values=[''], # only an empty field is missing
)
# parse_dates would guess the format, and keep a date it cannot parse as text.
# to_datetime with a format raises on any other spelling.
frame['signup'] = pd.to_datetime(frame['signup'], format='%Y-%m-%d')
return framepandas needs three arguments beyond dtype. usecols names the expected columns, so a renamed or
missing one raises. keep_default_na=False with na_values=[''] leaves the empty field as the only
missing value,3 and Int64 is the pandas integer type that can hold one. The date is read as
text and parsed with one format, because parse_dates guesses the format, and the
read_csv reference says a
date column it cannot parse comes back unaltered, without an error.
def with_polars(path):
text_date = {**POLARS_SCHEMA, 'signup': pl.String}
return (
pl.scan_csv(path, schema=text_date)
.with_columns(pl.col('signup').str.to_date('%Y-%m-%d'))
.collect()
)POLARS_SCHEMA maps the same six columns to pl.String, pl.Date, pl.Int64, and pl.Float64.
A full schema switches inference off, and scan_csv builds a lazy query that reads the file when
collect() runs, so a filter placed before it reads less. The date arrives as text and
str.to_date parses it with one format, which raises on any other.
# DuckDB: a types struct is matched by column name, the SELECT names what to keep, and
# dateformat replaces the guess at how a date is written.
DUCKDB_QUERY = """
SELECT customer_id, zip, country, signup, visits, amount
FROM read_csv($path, dateformat = '%Y-%m-%d', types = {
'customer_id': 'VARCHAR', 'zip': 'VARCHAR', 'country': 'VARCHAR',
'signup': 'DATE', 'visits': 'BIGINT', 'amount': 'DOUBLE'
})
"""
def with_duckdb(path):
frame = duckdb.sql(DUCKDB_QUERY, params={'path': str(path)}).df()
# .df() turns a BIGINT column into int64 when it has no NULLs and Int64 when it has some.
return frame.astype({'visits': 'Int64'})DuckDB’s CSV reference describes types as a
struct by name or a list by position, and the struct is the safe form. A declared DATE still
guesses how the date is written, so dateformat names it. The cast at the end matters: without it, Monday’s visits arrived in pandas as int64 and Tuesday’s as Int64.
PYTHONPATH=vendor python3 declared.pydeclared customer_id zip visits
pandas monday.csv str str Int64
pandas tuesday.csv str str Int64
polars monday.csv String String Int64
polars tuesday.csv String String Int64
duckdb monday.csv str str Int64
duckdb tuesday.csv str str Int64
what with_pandas made of the values
monday.csv zip ['10001', '02134', '60601']
tuesday.csv customer_id ['120048', '007731', '120050']
tuesday.csv country ['US', 'CA', 'NA']
tuesday.csv visits [<NA>, 2, <NA>]Both exports now load with the same types in every library. The zeros and Namibia survive, and the
empty visits are <NA> in an integer column instead of nan in a float one.
Make a changed export fail
An export that changes shape should stop the load, not slip through it. changes.py feeds six
altered copies of Monday’s first customer to each declared loader, and to a DuckDB loader that
uses columns instead of types.
PYTHONPATH=vendor python3 changes.pychange pandas polars duckdb types duckdb columns
renamed column raises raises raises loads
reordered columns loads loads loads loads, swapped
added column loads raises loads raises
dropped column raises raises raises raises
bad value raises raises raises raises
date format raises raises raises loads, wrong dateThe three declared loaders raise on a renamed column, a dropped column, a value that does not
parse, and a date written as 03/04/2024. They read a reordered file correctly by name. They differ
on an added column: pandas and DuckDB load the columns they were told about and ignore the new one,
while Polars refuses the file. The columns loader accepted the renamed file under the old name,
put the ZIP code in customer_id on the reordered file, and guessed the wrong month for the date.
Polars matched the reordered columns by name, although its
scan_csv documentation
says the schema’s order must match the file’s. Keep the order the same anyway.
Check it worked
Run the tests. They check the column types of both exports in all three loaders, the zeros and
NA that must survive, and the shape and date changes that must raise. A round trip writes each frame to
CSV and reads it back with the same loader, which must return an equal frame.
PYTHONPATH=vendor python3 test_load.pytest_declared_dtypes_hold_in_every_chunk (__main__.ChunkedReads.test_declared_dtypes_hold_in_every_chunk) ... ok
test_duckdb_df_picks_int64_or_Int64_by_whether_nulls_are_present (__main__.DeclaredTypes.test_duckdb_df_picks_int64_or_Int64_by_whether_nulls_are_present) ... ok
test_duckdb_gives_both_exports_the_same_dtypes (__main__.DeclaredTypes.test_duckdb_gives_both_exports_the_same_dtypes) ... ok
test_leading_zeros_and_namibia_survive_every_loader (__main__.DeclaredTypes.test_leading_zeros_and_namibia_survive_every_loader) ... ok
test_pandas_gives_both_exports_the_same_dtypes (__main__.DeclaredTypes.test_pandas_gives_both_exports_the_same_dtypes) ... ok
test_polars_gives_both_exports_the_same_schema (__main__.DeclaredTypes.test_polars_gives_both_exports_the_same_schema) ... ok
test_each_loader_reads_back_what_it_wrote (__main__.RoundTrip.test_each_loader_reads_back_what_it_wrote) ... ok
test_a_date_in_another_format_or_not_a_date_raises_in_every_loader (__main__.ShapeChanges.test_a_date_in_another_format_or_not_a_date_raises_in_every_loader) ... ok
test_a_renamed_or_dropped_column_or_a_bad_value_raises_in_every_loader (__main__.ShapeChanges.test_a_renamed_or_dropped_column_or_a_bad_value_raises_in_every_loader) ... ok
test_a_reordered_export_loads_by_name (__main__.ShapeChanges.test_a_reordered_export_loads_by_name) ... okTen tests print ok, and the runner ends with OK. The round trip catches what a type check
cannot. A ZIP code that passed through an integer on its way out does not come back equal.
When it goes wrong
A chunked read hands you one column with two types. pandas types each chunk on its own, so a ZIP
column can be int64 in the early chunks and str in a later one.
PYTHONPATH=vendor python3 chunks.pypandas read_csv with chunksize=1000 on a 2,500 row export
chunk 1 zip int64 visits int64
chunk 2 zip int64 visits int64
chunk 3 zip str visits float64
after pd.concat: zip object, holding int and str
row 1 10001
row 2002 '02002'
with dtype declared: chunk 1 zip str, chunk 2 zip str, chunk 3 zip strAfter pd.concat, row 1 holds the integer 10001 and row 2002 the string '02002' in one
object column. Pass the same dtype to the chunked read, and every chunk agrees.
Namibia still disappears after you set na_values. The pandas reference for read_csv says
na_values is added to the default list unless keep_default_na is False, and NA is on that
list. Set both.
A DuckDB result passes a type check on one file and fails it on the next. .df() picks int64 or
Int64 by whether the column holds a NULL, so cast after converting, as with_duckdb does.
When not to do this
Do not declare a schema for a file you open once to look at. Inference is quick, and when you read the frame yourself, a wrong guess shows up in seconds. Declare when an export arrives on a schedule and feeds a report or a model that nobody inspects row by row.
Do not turn ids and ZIP codes back into numbers downstream. A declared string survives this load
and loses its zero again in the first astype(int) someone adds for a join.
Do not use DuckDB’s columns option on an export whose producer can rename or reorder columns.
It fits a file with no header row, where position is all there is.
Do not pick Polars for its strictness if the upstream team adds columns every month. Each addition stops your load until the schema catches up, which is the price of hearing about every change.
Related how-tos
Last verified
Verified 2026-10-21 against Python 3.13.16, pandas 3.0.6, Polars 2.0.0, DuckDB 1.5.6, and NumPy
2.5.4. Every output block is what the command preceding it printed, run in the page’s code
directory after npm ci installed the pinned packages into vendor/. The exports are small
fixtures written for the page, not a real customer file.
Footnotes
-
The closest thing CSV has to a specification is RFC 4180, an Informational RFC from October 2005. In its own words, it “does not specify an Internet standard of any kind.” It defines records, fields, quoting, and an optional header line, and it registers the
text/csvmedia type. It says nothing about types. Whether 02134 is a number is a question the format never asks, so every reader on this page answers it on the format’s behalf. ↩︎ Back to text -
DuckDB’s default sample of 20,480 rows is stated on its type detection page. DuckDB processes data in vectors, and its internals documentation gives the default vector size as 2,048, so the sample is exactly ten vectors long. Polars settles the same question after 100 rows unless it is told to read more. ↩︎ Back to text
-
pandas reads nineteen strings as missing by default, listed in the
STR_NA_VALUESset in its parser source. Most are what anyone would expect:NA,NULL,None,n/a, and the empty string. Four of them,1.#IND,-1.#IND,1.#QNAN, and-1.#QNAN, look like nothing a person would type. The first two are how the Microsoft C runtime printed an indefinite value before Visual Studio 2015, according to Microsoft’sprintfdocumentation. The same page records that formatting infinity to two decimal places printed1.#J, because the runtime rounded the#INFlike any other digits. A comment before the pandas set keeps three spellings of infinity that pandas has since stopped counting as missing.NAis also the code behind Namibia’s top-level domain, .na, which is how a country can drop out of a customer table without an error. ↩︎ Back to text