read_csv's arguments earn their keep - and the ones that prevent a whole class of bug.
Overview
Inference is convenient and lossy
Reading data from files is the starting point of most pandas work. The reader functions, especially read_csv, are designed to be convenient. They guess column types, parse values, and produce a usable frame with minimal configuration. That convenience comes at a cost: the guesses are often wrong, and the errors are silent.
Three problems appear in almost every project that uses read_csv without explicit type instructions. Leading zeros in identifier columns disappear because the column is read as integers. Date columns remain as text because the reader does not guess date formats. A single missing value in an integer column promotes it to float64, and every id prints with a trailing decimal point.
Each of these problems is fixed by passing the right argument at read time. dtype forces a column to a specific type. parse_dates names the columns that should be converted to datetime. na_values specifies which strings represent missing data. These arguments cost a few lines, and they prevent hours of debugging.
The read step is also where you control memory usage. A file with ten million rows and fifty columns might be too large to load in full. usecols reads only the columns you need. nrows reads only the first N rows for testing. dtype with smaller numeric types cuts memory by half or more.
Writing data back to files has its own set of choices. to_csv is the most portable format but the slowest and the most lossy. to_parquet preserves dtypes, handles nested data, and is much faster for large files. The choice depends on who will read the file. If the reader is another pandas script, Parquet is better in every way. If the reader is Excel or a person, CSV is the right choice.
This module covers read_csv with the arguments that prevent the common problems, read_excel and read_parquet for other formats, and the matching to_ methods for writing. The emphasis is on the arguments that matter, not on listing every parameter.
read_csv guesses a dtype per column. That is why it's pleasant to use and why the same three problems appear in almost each project.
Leading zeros disappear. An id column of 007, 008 is read as ints. The zeros are gone permanently. No error. The values still look like ids.
Dates stay text. Nothing is parsed as a date unless you ask. The column sorts lexically, .dt doesn't work, and date math is down.
One gap makes a column float. NumPy ints can't hold NaN. 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 args, 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
import pandas as pd
import io
csv = """id,joined,amount
007,2024-01-05,1000
008,2024-02-11,
009,2024-03-30,3000
"""
df = pd.read_csv(io.StringIO(csv))
print(df)
print()
print(df.dtypes)
print()
print("id -> int64 : leading zeros gone, 007 is now 7")
print("joined -> object : still text, so it will sort lexically")
print("amount -> float64: one gap made the whole column float")
Output
Steering it with dtype and parse_dates
Saying what you know is faster and safer than fixing it afterwards.
example_02.pypandas
import pandas as pd
import io
csv = """id,joined,amount
007,2024-01-05,1000
008,2024-02-11,
009,2024-03-30,3000
"""
df = pd.read_csv(io.StringIO(csv),
dtype={"id": str, "amount": "Int64"},
parse_dates=["joined"])
print(df)
print()
print(df.dtypes)
print()
print("id stayed '007', joined is a real timestamp, and amount is a")
print("nullable integer that holds the gap without becoming float.")
Output
Only reading what you need
usecols and nrows matter as soon as the file is large.
example_03.pypandas
import pandas as pd
import io
csv = "a,b,c,d\n" + "\n".join("%d,%d,%d,%d" % (i, i, i, i) for i in range(1000))
full = pd.read_csv(io.StringIO(csv))
part = pd.read_csv(io.StringIO(csv), usecols=["a", "d"])
print("all columns :", full.shape, "%.1f KB" % (full.memory_usage(deep=True).sum() / 1e3))
print("two columns :", part.shape, "%.1f KB" % (part.memory_usage(deep=True).sum() / 1e3))
print()
print("peek at a big file before committing to it:")
print(pd.read_csv(io.StringIO(csv), nrows=3))
Output
Missing-value markers
pandas knows the common ones and not yours.
example_04.pypandas
import pandas as pd
import io
csv = """name,score
ana,10
raj,N/A
kim,missing
sam,-999
"""
df = pd.read_csv(io.StringIO(csv))
print(df)
print("dtype:", df["score"].dtype, "<- object, because of the text markers")
print()
better = pd.read_csv(io.StringIO(csv), na_values=["missing", "-999"])
print(better)
print("dtype:", better["score"].dtype)
print()
print("'N/A' was recognised automatically; 'missing' and -999 were not.")
Output
Writing it back
The index is written by default, which surprises people on the round trip.
example_05.pypandas
import pandas as pd
import io
df = pd.DataFrame({"a": [1, 2], "b": ["x", "y"]})
print("to_csv() writes the index as an unnamed first column:")
print(df.to_csv())
print("reading that back gives a stray column:")
print(list(pd.read_csv(io.StringIO(df.to_csv())).columns))
print()
print("index=False is almost always what you want:")
print(df.to_csv(index=False))
print(" round trip:", list(pd.read_csv(io.StringIO(df.to_csv(index=False))).columns))
Output
CSV loses everything the frame knew
Types, categories and the index are all just text on the way out.
example_06.pypandas
import pandas as pd
import io
df = pd.DataFrame({
"n": pd.Series([1, 2], dtype="Int64"),
"c": pd.Series(["a", "b"], dtype="category"),
"d": pd.to_datetime(["2024-01-01", "2024-02-01"]),
})
print("before:"); print(df.dtypes)
print()
back = pd.read_csv(io.StringIO(df.to_csv(index=False)))
print("after a CSV round trip:"); print(back.dtypes)
print()
print("Int64 became int64, category became object, the date became text.")
print("For anything that must survive, use parquet or pickle - or write")
print("the dtypes down and restore them on read.")
Output
The arguments that matter
dtype={"id": str} keeps identifiers as text. This is the fix for leading zeros. 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 int type. So The column holds gaps without turning float.
parse_dates=["joined"] converts during the read. This 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. It's the single most good arg for large data.
nrows=1000 reads a sample. Always look at a sample of an unseen file before loading all of it. It's how you find out that the delimiter is wrong, or that there are three header rows, or that the file isn't 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 can't 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. This Quietly poisons each mean.
skiprows, header=None and names=[...] handle files whose top isn't a clean header row.
chunksize=n returns an iterator of frames rather than one frame. This 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's harmless, ubiquitous. Fully avoidable: **pass index=False** unless the index is real data.
Other args worth knowing: float_format="%.2f" controls precision, columns= selects what to write, sep= changes the delimiter. encoding="utf-8" is worth being clear about when the data leaves your machine.
CSV loses what the frame knew
A CSV is text. all pandas knew about the data is discarded on the way out:
Int64 turns plain int64 on the way back, or float64 if there were gaps.
group turns object, and the group order is gone.
Datetimes become strings again, unless the reader is told to parse them.
The index turns a column, or vanishes.
That round-trip loss is the arg 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. Is readable by many tools. It needs pyarrow installed. Which is why the editors here use CSV.
Pickle (to_pickle) preserves all just, including groups and custom objects. Is Python-exact, version-fragile. runs code on load. Never unpickle a file you didn't create.
HDF5 suits very large numeric data with partial reads.
For anything you'll read back yourself, Parquet is often the right answer. For anything a human or another tool must read, CSV is worth its losses. But write down the dtypes one spot. The reader will have to guess just 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 really need. Then read the whole file with those args set.
That takes a minute and drops 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 arg 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 needs 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. This Is the usual cause of a UnicodeDecodeError on files from older Windows tools.
quotechar and escapechar for embedded delimiters.
comment="#" skips comment lines.
When a file won't 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, given the step can be done piecewise. Sums, counts and group sums can. A median can't, 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 each sheet into a dict of frames. This Is the quickest way to see what a workbook holds.
Excel files carry types, so dates often arrive parsed. But they also carry merged cells, hidden rows and formatting that mean nothing to pandas. 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 a few 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 packing 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 and paths. This Is convenient for public datasets and a bad idea in production code. Here The file should be fetched and cached on purpose.
pathlib.Path objects work anywhere a path string does.
Choosing a format
CSV — universal, human-readable, lossy about types, slow to parse. Right when a thing 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 all just, Python-only, version-fragile. runs code on load. Fine for a short-lived cache you created. Never for input you didn't.
JSON — good for nested data, verbose for tabular, and json_normalize handles the flattening.
HDF5 — large numeric arrays with partial reads.
SQL — when a few processes need the data, or it outgrows one machine.
The choice is mostly: who reads this next? If the answer is pandas, use Parquet. If it's a person or another tool, use CSV and accept the losses. If it's a system, use a DB.
Writing for a round trip
If pandas will read the file back, the goal is to lose nothing.
Parquet does that: dtypes, groups and datetimes all live. It's smaller and faster than CSV.
If Parquet is down, CSV plus an clear read specification is the workaround. Record the dtypes alongside the file, and pass them on read:
That is clumsy and it works. What doesn't work is given the reader will infer the same types you had. Inference depends on the data and the data changes.
Validating on load
The most handy place for checks is at once after reading. Here 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. 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 can't be held.
Parquet, which reads only the columns requested and is far faster to parse.
A DB 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 often make a "too large" file comfortable.
A summary
read_csv infers, and inference loses leading zeros, leaves dates as text, and turns ints 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 doesn't.
allow_pickle-style trust applies to read_pickle too. Never load one you didn't create.
Validate at once after loading, above all the column names.
And for a large file, usecols and dtype are the first thing to try, not the last.
A closing note on Reading and Writing Files
The boundary between a file and a DataFrame is where most silent damage happens. It's the cheapest place to prevent it.
read_csv guesses, and its guesses lose leading zeros, leave dates as text, and turn ints with a single gap into floats. each one of those is fixed by an arg, and each one of them is much harder to fix later. Leading zeros in given are gone for good.
The habit worth building is to read a sample first, look at it, decide the types. 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. Parquet rather than CSV when pandas will be the one reading it back. CSV is a text interchange format. Treating it as a storage format means accepting that all 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's slower than dtype and handles cases dtype can't. Stripping a currency symbol, parsing a bespoke format. Without a split cleaning pass later.
And to_csv with no path returns the CSV as a string rather than writing a file. This Is how you round-trip through io.StringIO in a test, or hand the text to a thing that wants a string rather than a filename.
Questions people ask about Reading and Writing Files
How do I read a CSV file? Use pd.read_csv('file.csv'). Common arguments are sep, usecols, dtype, parse_dates and nrows.
Why do my leading zeros or ID numbers change on loading? pandas infers numeric types, which drops leading zeros. Pass dtype={'zip': str} for those columns.
How do I read a file that is too large for memory? Use chunksize=100000 to iterate over pieces, or usecols and dtype to load less. Parquet is also faster and smaller than CSV.
Why does my CSV have an extra 'Unnamed: 0' column? The file was written with the index included. Save with index=False, or read with index_col=0.
Which format should I save in? CSV is portable but loses dtypes. Parquet keeps types and is fast and compact, and pickle is only suitable for temporary, trusted use.
How do I fix encoding errors on read? Pass encoding='utf-8' or try 'latin-1' or 'cp1252' if the file came from Windows or older systems.
Reading and Writing Files, in summary
The read is where types are decided. Inference makes three predictable mistakes. Leading zeros lost, dates left as text, ints with a gap turned into floats.
dtype, parse_dates, usecols and na_values prevent all three, cost nothing. Are far cheaper than repairing the damage later.
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. CSV discards all 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
Read CSV files into DataFrames with the right arguments and avoid the silent bugs that wrong defaults let through.
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.