# How to load a CSV into a DataFrame with the column types you want > Declare each column's type in pandas, Polars, or DuckDB, so ZIP codes and ids keep their zeros and a renamed or dropped column stops the load. Source: https://voxgig.com/howto/load-a-csv-into-a-dataframe - Audience: data-analyst - Level: beginner - Languages: python - Verified: 2026-10-21 - Published: 2026-10-20 ## 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`](https://duckdb.org/docs/current/data/csv/overview) | 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`](https://pandas.pydata.org/docs/reference/api/pandas.read_csv.html) | 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`](https://docs.pola.rs/api/python/stable/reference/api/polars.scan_csv.html) | 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. ```bash PYTHONPATH=vendor python3 infer.py ``` ```text output inferred 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. ```py title="load.py" # 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 frame ``` pandas 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](https://pandas.pydata.org/docs/reference/api/pandas.read_csv.html) says a date column it cannot parse comes back unaltered, without an error. ```py title="load.py" 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. ```py title="load.py" # 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](https://duckdb.org/docs/current/data/csv/overview) 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`. ```bash PYTHONPATH=vendor python3 declared.py ``` ```text output declared 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 [, 2, ] ``` Both exports now load with the same types in every library. The zeros and Namibia survive, and the empty visits are `` 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`. ```bash PYTHONPATH=vendor python3 changes.py ``` ```text output change 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 date ``` The 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](https://docs.pola.rs/api/python/stable/reference/api/polars.scan_csv.html) 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. ```bash PYTHONPATH=vendor python3 test_load.py ``` ```text output test_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) ... ok ``` Ten 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. ```bash PYTHONPATH=vendor python3 chunks.py ``` ```text output pandas 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 str ``` After `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 - [Decide when lenient parsing is the wrong choice](/howto/decide-when-lenient-parsing-is-wrong) ## 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. [^1]: The closest thing CSV has to a specification is [RFC 4180](https://www.rfc-editor.org/rfc/rfc4180.html), 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/csv` media 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. [^2]: DuckDB's default sample of 20,480 rows is stated on its [type detection page](https://duckdb.org/docs/current/data/csv/auto_detection). DuckDB processes data in vectors, and its [internals documentation](https://duckdb.org/docs/current/internals/vector) 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. [^3]: pandas reads nineteen strings as missing by default, listed in the `STR_NA_VALUES` set in its [parser source](https://github.com/pandas-dev/pandas/blob/v3.0.6/pandas/_libs/parsers.pyx#L1383). 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's `printf` documentation](https://learn.microsoft.com/en-us/cpp/c-runtime-library/format-specification-syntax-printf-and-wprintf-functions?view=msvc-170). The same page records that formatting infinity to two decimal places printed `1.#J`, because the runtime rounded the `#INF` like any other digits. A comment before the pandas set keeps three spellings of infinity that pandas has since stopped counting as missing. `NA` is also the code behind Namibia's top-level domain, [.na](https://www.iana.org/domains/root/db/na.html), which is how a country can drop out of a customer table without an error.