read_csv's arguments earn their keep - and the ones that prevent a whole class of bug.
Overview
Inference is convenient and lossy
read_csv guesses a dtype per column. That is why it is pleasant to use and why the same three problems appear in almost every project.
Leading zeros disappear. An id column of 007, 008 is read as integers, and the zeros are gone permanently. No error, and the values still look like ids.
Dates stay text. Nothing is parsed as a date unless you ask. The column sorts lexically, .dt does not work, and date arithmetic is unavailable.
One gap makes a column float. NumPy integers cannot hold NaN, so a single missing value promotes the whole column, and ids print as 1001.0.
All three are fixed at read time by saying what you know.
Worth knowing
read_csv infers types, and the three usual casualties are leading zeros, unparsed dates and an integer column turned float by one gap.
dtype=, parse_dates= and Int64 fix all three at read time, which is faster than converting afterwards.
usecols and nrows matter as soon as the file is large — read a sample before committing to the whole thing.
na_values= adds your own missing markers; pandas recognises N/A and friends but not missing or -999.
to_csv() writes the index by default, producing a stray unnamed column on the round trip. Pass index=False.
CSV is text: dtypes, categories and dates do not survive. Use parquet when they must.
Reading and Writing Files
read_csv's arguments, and the ones that prevent a class of bug.
read_csv infers, and inference is where bugs start
Three columns, three wrong guesses, no errors.
example_01.pypandas
Output
Steering it with dtype and parse_dates
Saying what you know is faster and safer than fixing it afterwards.
example_02.pypandas
Output
Only reading what you need
usecols and nrows matter as soon as the file is large.
example_03.pypandas
Output
Missing-value markers
pandas knows the common ones and not yours.
example_04.pypandas
Output
Writing it back
The index is written by default, which surprises people on the round trip.
example_05.pypandas
Output
CSV loses everything the frame knew
Types, categories and the index are all just text on the way out.
example_06.pypandas
Output
The arguments that matter
dtype={"id": str} keeps identifiers as text. This is the fix for leading zeros, and it should be the default for anything that is a code rather than a quantity — postcodes, account numbers, phone numbers.
dtype={"amount": "Int64"} uses the nullable integer type, so the column holds gaps without becoming float.
parse_dates=["joined"] converts during the read, which is faster than converting after. Add date_format= when the format is known and unambiguous.
usecols=["a", "d"] reads only the columns you name. On a wide file this saves both time and memory, and it is the single most effective argument for large data.
nrows=1000 reads a sample. Always look at a sample of an unfamiliar file before loading all of it — it is how you find out that the delimiter is wrong, or that there are three header rows, or that the file is not what you were told.
index_col="id" sets the index during the read.
na_values=["missing", "-999"] adds markers. pandas recognises a standard list — empty, NA, N/A, null, NaN and a few more — but it cannot know that this dataset writes missing, or that -999 is a sentinel. Left alone, the text markers make the column object and the numeric sentinel is treated as a real value, which quietly poisons every average.
skiprows, header=None and names=[...] handle files whose top is not a clean header row.
chunksize=n returns an iterator of frames rather than one frame, which is how you process a file larger than memory.
Writing
to_csv() writes the index as an unnamed first column by default.
Read that file back and you get a spurious Unnamed: 0 column. It is harmless, ubiquitous, and entirely avoidable: pass index=False unless the index is meaningful data.
Other arguments worth knowing: float_format="%.2f" controls precision, columns= selects what to write, sep= changes the delimiter, and encoding="utf-8" is worth being explicit about when the data leaves your machine.
CSV loses what the frame knew
A CSV is text. Everything pandas knew about the data is discarded on the way out:
Int64 becomes plain int64 on the way back, or float64 if there were gaps.
category becomes object, and the category order is gone.
Datetimes become strings again, unless the reader is told to parse them.
The index becomes a column, or vanishes.
That round-trip loss is the argument for a binary format when the data is going to be read back by pandas:
Parquet (to_parquet / read_parquet) preserves dtypes, is columnar, compresses well, and is readable by many tools. It needs pyarrow installed — which is why the editors here use CSV.
Pickle (to_pickle) preserves everything exactly, including categories and custom objects, but is Python-specific, version-fragile, and executes code on load. Never unpickle a file you did not create.
HDF5 suits very large numeric data with partial reads.
For anything you will read back yourself, Parquet is usually the right answer. For anything a human or another tool must read, CSV is worth its losses — but write down the dtypes somewhere, because the reader will have to guess exactly as you did.
A loading routine
Read a sample with nrows. Look at it. Decide the dtypes, the date columns, the missing markers and the columns you actually need. Then read the whole file with those arguments set.
That takes a minute and removes most of what the cleaning modules exist to fix.
Files that are not quite CSV
Real exports are rarely clean, and read_csv has an argument for each common defect.
sep=" " for tab-separated; sep=None with engine="python" sniffs the delimiter.
skiprows=3 skips a preamble; skiprows=lambda i: i % 2 skips selectively.
header=None with names=[...] handles a file with no header row.
skipfooter=2 drops trailing summary lines, and requires engine="python".
thousands="," parses 1,234 as a number rather than leaving it as text. Without it, a single formatted number makes the whole column object.
decimal="," handles European decimal commas.
encoding="latin-1" when UTF-8 fails, which is the usual cause of a UnicodeDecodeError on files from older Windows tools.
quotechar and escapechar for embedded delimiters.
comment="#" ignores comment lines.
When a file will not parse, working through that list is faster than writing a custom reader.
Reading in chunks
chunksize=n returns an iterator of frames rather than one frame:
totals = []
for chunk in pd.read_csv(path, chunksize=100_000):
totals.append(chunk.groupby("city")["sales"].sum())
result = pd.concat(totals).groupby(level=0).sum()
That processes a file larger than memory, provided the operation can be done piecewise. Sums, counts and group sums can; a median cannot, without more work.
The pattern is: reduce each chunk, collect the small results, combine at the end. Collecting the raw chunks and concatenating them defeats the purpose.
Excel
pd.read_excel(path, sheet_name="Sheet1") needs openpyxl for .xlsx.
sheet_name=None reads every sheet into a dict of frames, which is the quickest way to see what a workbook contains.
Excel files carry types, so dates usually arrive parsed — but they also carry merged cells, hidden rows and formatting that mean nothing to pandas, and a sheet that looks tabular on screen is often not.
header=, usecols="B:D" and skiprows= do the same jobs as in read_csv.
Writing several frames to one workbook uses pd.ExcelWriter as a context manager, with one to_excel call per sheet.
Compression and paths
read_csv and to_csv handle compression transparently from the extension: .gz, .bz2, .zip, .xz. A gzipped CSV is often a third of the size and costs little to read.
Both accept URLs as well as paths, which is convenient for public datasets and a bad idea in production code, where the file should be fetched and cached deliberately.
pathlib.Path objects work anywhere a path string does.
Choosing a format
CSV — universal, human-readable, lossy about types, slow to parse. Right when something other than pandas must read it.
Parquet — typed, columnar, compressed, fast, readable by many tools. The right default for data pandas will read back. Needs pyarrow.
Pickle — preserves everything exactly, Python-only, version-fragile, and executes code on load. Fine for a short-lived cache you created; never for input you did not.
JSON — good for nested data, verbose for tabular, and json_normalize handles the flattening.
HDF5 — large numeric arrays with partial reads.
SQL — when several processes need the data, or it outgrows one machine.
The decision is mostly: who reads this next? If the answer is pandas, use Parquet. If it is a person or another tool, use CSV and accept the losses. If it is a system, use a database.
Writing for a round trip
If pandas will read the file back, the goal is to lose nothing.
Parquet does that: dtypes, categories and datetimes all survive, and it is smaller and faster than CSV.
If Parquet is unavailable, CSV plus an explicit read specification is the workaround — record the dtypes alongside the file, and pass them on read:
That is clumsy and it works. What does not work is assuming the reader will infer the same types you had, because inference depends on the data and the data changes.
Validating on load
The most useful place for checks is immediately after reading, where a problem is closest to its cause:
Column-name checks are the highest value of these. An upstream export that renames or reorders columns is common, and without a check the failure appears much later as a KeyError or, worse, as a column of the wrong data under the right name.
Large files
The order to try, as a file grows:
usecols and dtype — often enough on its own, and always worth doing first.
nrows for development, so the edit-run cycle stays fast.
chunksize with per-chunk reduction, when the whole file cannot be held.
Parquet, which reads only the columns requested and is far faster to parse.
A database or DuckDB, when the data outgrows one machine's memory even column-wise.
Reaching for the last option first is a common mistake; usecols and dtype frequently make a "too large" file comfortable.
A summary
read_csv infers, and inference loses leading zeros, leaves dates as text, and turns integers with gaps into floats.
dtype, parse_dates, usecols and na_values prevent all of that at read time.
Read a sample with nrows before committing to a large file.
to_csv(index=False) unless the index is data.
CSV loses types; Parquet does not.
allow_pickle-style trust applies to read_pickle too — never load one you did not create.
Validate immediately after loading, especially the column names.
And for a large file, usecols and dtype are the first thing to try, not the last.
A closing note
The boundary between a file and a DataFrame is where most silent damage happens, and it is the cheapest place to prevent it.
read_csv guesses, and its guesses lose leading zeros, leave dates as text, and turn integers with a single gap into floats. Every one of those is fixed by an argument, and every one of them is much harder to fix later — leading zeros in particular are gone for good.
The habit worth building is to read a sample first, look at it, decide the types, and then read the file properly. That takes a minute and replaces an afternoon of confused debugging.
On the way out, index=False unless the index is data, and Parquet rather than CSV whenever pandas will be the one reading it back. CSV is a text interchange format, and treating it as a storage format means accepting that everything the frame knew about its own types is discarded each time.
One more thing
read_csv accepts a converters dict mapping column names to functions, applied during the read. It is slower than dtype and handles cases dtype cannot — stripping a currency symbol, parsing a bespoke format — without a separate cleaning pass afterwards.
And to_csv with no path returns the CSV as a string rather than writing a file, which is how you round-trip through io.StringIO in a test, or hand the text to something that wants a string rather than a filename.
In summary
The read is where types are decided, and inference makes three predictable mistakes: leading zeros lost, dates left as text, integers with a gap turned into floats.
dtype, parse_dates, usecols and na_values prevent all three, cost nothing, and are far cheaper than repairing the damage afterwards.
Read a sample first and look at it. Validate the column names on load. Write with index=False. And when pandas will be the one reading the file back, use Parquet, because CSV discards everything the frame knew about itself.
Check yourself
0 of 4
Answer without scrolling back up.
A CSV id column contains 007. What does `read_csv` do by default?
dtype={'id': str} at read time is the fix, and should be the default for anything that is a code rather than a quantity.
Which argument stops one missing value turning an integer column into float?
NumPy integers cannot hold NaN, so a single gap promotes the whole column and ids start printing as 1001.0
Why does a round-tripped CSV often gain an `Unnamed: 0` column?
Harmless, ubiquitous and entirely avoidable. Pass index=False unless the index is meaningful data.
What is lost when a DataFrame round-trips through CSV?
Use parquet when the data will be read back by pandas. CSV is worth its losses when a human or another tool must read it.
Cheat sheet
Reading and Writing Files
Leading zeros disappear. An id column of 007, 008 is read as integers, and the zeros are gone permanently. No error, and the values still look like ids.
Ashish Jangra builds and maintains VizLearn. Every module here is written and the visualisation behind it hand-built, so the numbers in a readout come from the same code that draws the picture. Corrections are genuinely welcome and get priority over everything else — if a page states something wrong, or an animation misrepresents what the algorithm does, get in touch.