How to Read CSV Files in Pandas (and Fix the Usual Errors)

Almost every data project in Python starts the same way: you have a CSV file and you need to get it into a pandas DataFrame. The function for that, pd.read_csv(), looks simple, but it has more than forty parameters, and the defaults do not always suit real-world files. Dates arrive as text, postcodes lose their leading zeros, European files use semicolons, and a single stray character can trigger a UnicodeDecodeError.

This tutorial walks through reading CSV files with pandas properly: the basic call, the parameters you will actually use, how to check what you have loaded, and how to fix the errors that come up most often. Every example uses a small CSV created inside the code itself, so you can copy and run it without downloading anything.

What you need

• Python 3.9 or later
• pandas (install with pip install pandas)
• A Jupyter notebook, VS Code or any Python editor

Step 1: The basic read_csv call

To keep examples self-contained, we will write a small CSV file first and then read it back. In your own work you would simply point read_csv at your file path.

import pandas as pd

csv_text = """order_id,order_date,customer,region,postcode,amount
1001,2024-01-05,Smith,North,01234,120.50
1002,2024-01-06,Jones,South,04567,89.99
1003,2024-01-06,Patel,North,,45.00
1004,2024-01-07,Brown,East,07890,210.10
"""
with open("orders.csv", "w", encoding="utf-8") as f:
    f.write(csv_text)

df = pd.read_csv("orders.csv")
print(df)
print(df.dtypes)

Run this and look carefully at the output of df.dtypes. You will notice three things that are typical of a first read:

• order_date is object (or str in newer pandas), meaning text, not a real date.
• postcode has become a float: 01234 is now 1234.0. The leading zero has gone, and because one value is missing, pandas used a float so it could store NaN.
• amount is correctly a float.

None of these are bugs. pandas is guessing types from the content. Our job is to tell it what we actually want.

Step 2: Control column types with dtype

Identifiers such as postcodes, phone numbers, account numbers and product codes should almost always be read as text. They are labels, not quantities, and you will never add two postcodes together. Use the dtype parameter:

df = pd.read_csv("orders.csv", dtype={"postcode": "string", "order_id": "string"})
print(df[["order_id", "postcode"]])
print(df.dtypes)

Now the leading zeros are kept and the missing postcode shows as <NA>. Using the "string" dtype rather than plain str gives you pandas’ dedicated string type, which handles missing values cleanly.

Step 3: Parse dates while reading

Dates stored as text cannot be sorted chronologically with confidence, filtered by month or used for time-based grouping. Ask pandas to convert them as it reads:

df = pd.read_csv(
    "orders.csv",
    dtype={"postcode": "string"},
    parse_dates=["order_date"],
)
print(df.dtypes)
print(df["order_date"].dt.day_name())

UK data often uses day-first dates such as 05/01/2024 for 5 January. If you do not tell pandas, it may read that as 1 May. Add dayfirst=True, or better still, convert after loading with an explicit format so there is no ambiguity:

uk = pd.DataFrame({"d": ["05/01/2024", "13/01/2024"]})
uk["d"] = pd.to_datetime(uk["d"], format="%d/%m/%Y")
print(uk)

An explicit format is also faster on large files and fails loudly if a value does not match, which is exactly what you want.

Step 4: Choose only the columns you need

Wide exports from finance or CRM systems can have hundreds of columns. Loading only what you need saves memory and makes the DataFrame easier to inspect:

df = pd.read_csv("orders.csv", usecols=["order_id", "region", "amount"])
print(df.head())

Step 5: Handle different separators and decimal marks

Not every “CSV” uses commas. Files exported from European versions of Excel commonly use semicolons as separators and commas as decimal marks. Tell pandas both:

euro_text = "product;price\nWidget;12,50\nGadget;7,99\n"
with open("euro.csv", "w", encoding="utf-8") as f:
    f.write(euro_text)

euro = pd.read_csv("euro.csv", sep=";", decimal=",")
print(euro, euro.dtypes, sep="\n")

If you are not sure which separator a file uses, open it in a plain text editor (not Excel) and look at the first few lines. Tab-separated files use sep="\t".

Step 6: Treat custom placeholders as missing

Systems use all sorts of codes for “no value”: N/A, -, unknown, 999. pandas recognises some of these automatically (such as empty cells, NA and N/A), but not all. Add your own with na_values:

messy_text = "name,score\nAnna,78\nBen,-\nCara,unknown\nDev,91\n"
with open("scores.csv", "w", encoding="utf-8") as f:
    f.write(messy_text)

scores = pd.read_csv("scores.csv", na_values=["-", "unknown"])
print(scores)
print(scores["score"].mean())

Without na_values, the score column would be text and .mean() would fail. With it, the column is numeric and the mean (84.5) is calculated from the two valid scores.

Step 7: Read large files in chunks

If a file is larger than your available memory, read it in pieces and aggregate as you go:

totals = {}
for chunk in pd.read_csv("orders.csv", chunksize=2):
    for region, amount in chunk.groupby("region")["amount"].sum().items():
        totals[region] = totals.get(region, 0) + amount
print(totals)

Here chunksize=2 is tiny for demonstration; in practice values such as 100,000 rows are common. For quick exploration you can also use nrows=1000 to load just the first rows.

Step 8: Always inspect what you loaded

Make these checks a habit immediately after every read:

df = pd.read_csv("orders.csv", dtype={"postcode": "string"}, parse_dates=["order_date"])
print(df.shape)          # rows, columns
print(df.dtypes)         # are the types what you expect?
print(df.isna().sum())   # missing values per column
print(df.head())         # eyeball the first rows

Comparing df.shape with the number of rows you expected (for example from the source system) catches truncated files and broken quoting early, before they quietly distort your analysis.

Fixing common read_csv errors

UnicodeDecodeError

This means the file is not encoded in UTF-8, which is pandas’ default. Files saved from older versions of Excel on Windows are often in cp1252. Try:

with open("latin.csv", "w", encoding="cp1252") as f:
    f.write("city,price\nCafé Town,£5\n")

latin = pd.read_csv("latin.csv", encoding="cp1252")
print(latin)

If a file starts with an odd character such as  in the first column name, it has a byte-order mark; use encoding="utf-8-sig".

ParserError: Error tokenizing data

A row has more fields than the header. Usually a text field contains an unquoted comma, or the file has notes at the top. Check the line number in the error message, open the file in a text editor, and fix the source if you can. If there are a few junk lines at the top, use skiprows. As a last resort, on_bad_lines="warn" skips broken rows and tells you which ones – but never ignore those warnings silently.

FileNotFoundError

Your working directory is not where you think it is. Check with:

from pathlib import Path
print(Path.cwd())
print(Path("orders.csv").exists())

Using pathlib.Path also avoids backslash problems in Windows paths.

Common mistakes

• Letting IDs become numbers. Leading zeros disappear and long numbers turn into scientific notation. Read identifiers as strings.
• Ignoring date formats. Day-first UK dates can be silently misread as month-first. Use an explicit format.
• Opening the CSV in Excel and saving it again. Excel may reformat dates and strip zeros before pandas ever sees the file. Inspect raw files in a text editor.
• Not checking the row count. A quoting issue can merge or drop rows without an error.
• Using on_bad_lines="skip" to make errors go away. You may be throwing away real data.

A reusable loading template

Once you know a file’s quirks, put them in one function so every analysis loads the data the same way:

def load_orders(path):
    df = pd.read_csv(
        path,
        dtype={"order_id": "string", "postcode": "string", "region": "category"},
        parse_dates=["order_date"],
        na_values=["-", "unknown"],
    )
    assert df["order_id"].is_unique, "Duplicate order IDs found"
    return df

orders = load_orders("orders.csv")
print(orders.dtypes)

The assert line is a simple data-quality check: if the source ever starts sending duplicate orders, you find out immediately.

FAQ

How do I read a CSV from a URL?

Pass the URL directly: pd.read_csv("https://example.com/data.csv"). The same parameters apply.

How do I read an Excel file instead?

Use pd.read_excel("file.xlsx", sheet_name="Sheet1"). You will need the openpyxl package installed.

Why does my first column look like “Unnamed: 0”?

The file was probably saved from pandas with its index. Read it with index_col=0, and when saving your own files use df.to_csv("file.csv", index=False).

Is read_csv slow on big files?

For files of several gigabytes, specifying dtype and usecols helps a lot. You can also try engine="pyarrow" (requires the pyarrow package) or consider storing data as Parquet.

Next steps

Now that your data is loaded correctly, the next job is usually cleaning it. Work through Cleaning Messy Data with Pandas, then learn to summarise it with pandas groupby. If your dates need more work, see Working with Dates and Times in Pandas, and if your data actually lives in a database, Using SQLite with Python and Pandas shows how to query it straight into a DataFrame.