Using pandas read_csv for Files That Do Not Behave

pandas has a good CSV reader, and most files load with pd.read_csv("file.csv"). This article is about the other files: the ones with a title on top, numbers written the European way, five different spellings of "missing", rows that are too long or too short and bytes that are not UTF-8. For each, there is a parameter, and the work is in knowing which symptom points at which one. I use the order below when a load fails: fix the structure first, then the values, then the types. The full picture of reading and writing CSV in Python, including the standard library, is in the guide, and every parameter is described in the read_csv reference and the IO guide.

Start with the structure

A finance report, exported from a spreadsheet, with a title line, a comment line, semicolons and European numbers:

Quarterly report
# comment line
region;q1;q2;note
EMEA;1.234,50;2.000,00;ok
APAC;900,00;n/a;"late; revised"
AMER;-;1.500,25;ok

The plain call fails, and the message is worth reading closely:

import pandas as pd

try:
    pd.read_csv("quirky.csv")
except pd.errors.ParserError as e:
    print("ParserError:", e)
print("---")
df = pd.read_csv(
    "quirky.csv",
    skiprows=1,
    comment="#",
    sep=";",
    decimal=",",
    thousands=".",
    na_values=["-", "n/a"],
)
print(df)
print(df.dtypes)

python /batches/csv-0001-0200/lab/py/pd_quirky.py

ParserError: Error tokenizing data. C error: Expected 1 fields in line 4, saw 3

---
  region      q1       q2           note
0   EMEA  1234.5  2000.00             ok
1   APAC   900.0      NaN  late; revised
2   AMER     NaN  1500.25             ok
region        str
q1        float64
q2        float64
note          str
dtype: object

ParserError: Error tokenizing data. C error: Expected 1 fields in line 4, saw 3. pandas took the first line, Quarterly report, as the header, which has one field, and then met a line with three. The error names a line number, and the useful reading of it is: the number of fields it expected comes from the first line it parsed, so a wrong first line is the first suspect. skiprows=1 drops the title, comment="#" ignores the whole comment line, and sep=";" tells it the delimiter. With those the shape is right, a frame with four columns and three rows.

The same output shows the values: 1.234,50 came through as 1234.5 because of decimal="," and thousands="."; the two together say which character separates the thousands and which the fraction. The dashes and n/a are NaN because na_values=["-", "n/a"] listed them. And the note value late; revised stayed in one piece, since the semicolon in it is inside quotes. The dtypes at the bottom: region and note are str, q1 and q2 are float64.

If the first row is not the header, say so explicitly with header=None and names=[...], rather than letting pandas promote a data row to column names.

Missing values

pandas decides what is missing from a built in list of strings, and that list is not what most people think. This file has five spellings in the code column and an empty cell:

import io
import pandas as pd

text = "code,qty\nNA,1\nnull,2\nN/A,3\nnone,4\n,5\n"
df = pd.read_csv(io.StringIO(text))
print(df["code"].tolist(), df["code"].isna().tolist())
df = pd.read_csv(io.StringIO(text), keep_default_na=False)
print(df["code"].tolist())
df = pd.read_csv(io.StringIO(text), keep_default_na=False, na_values=[""])
print(df["code"].tolist())

python /batches/csv-0001-0200/lab/py/pd_na.py

[nan, nan, nan, 'none', nan] [True, True, True, False, True]
['NA', 'null', 'N/A', 'none', '']
['NA', 'null', 'N/A', 'none', nan]

The first line is the default read. NA, null, N/A and the empty cell are all NaN, but none is a real string. The list is defined in the reference, and it is a list of exact spellings, not a pattern. I fed fourteen strings through the default read to see which ones counted:

import io
import pandas as pd

vals = ["NULL", "Null", "null", "NA", "na", "N/A", "n/a", "nan", "NaN", "None", "none", "-", "#N/A", "<NA>"]
text = "v\n" + "\n".join(vals) + "\n"
df = pd.read_csv(io.StringIO(text), keep_default_na=True)
for v, m in zip(vals, df["v"].isna()):
    print(f"{v:6s} -> {'missing' if m else 'text'}")

python /batches/csv-0001-0200/lab/py/pd_na_case.py

NULL   -> missing
Null   -> text
null   -> missing
NA     -> missing
na     -> text
N/A    -> missing
n/a    -> missing
nan    -> missing
NaN    -> missing
None   -> missing
none   -> text
-      -> text
#N/A   -> missing
<NA>   -> missing

NULL, null, NA, N/A, n/a, nan, NaN, None, #N/A and <NA> are missing. Null, na, none and the dash are text. So the answer to "does pandas know my marker" depends on the exact capitalization, and the only safe habit is to print df["code"].isna() for the column and look.

keep_default_na=False turns the built in list off, and everything stays as text, including the empty cell, which becomes ''. That is the right setting for an identifier column where the string NA is a real value; there are real countries, people and products called NA, and a Namibia lookup that silently turns into NaN is a classic bug. The third line combines keep_default_na=False with na_values=[""], so only the empty cell is missing. When you turn the defaults off, add back the markers you do want.

Rows with the wrong number of fields

A row with too few fields is padded with NaN. A row with too many is an error, by default. The test file has both:

import pandas as pd

try:
    pd.read_csv("ragged.csv")
except Exception as e:
    print(type(e).__name__ + ":", e)
print(pd.read_csv("ragged.csv", on_bad_lines="skip"))
print(pd.read_csv("ragged.csv", on_bad_lines="warn"))
bad = []
df = pd.read_csv("ragged.csv", engine="python", on_bad_lines=lambda row: bad.append(row) or None)
print(df)
print("collected:", bad)

python /batches/csv-0001-0200/lab/py/pd_badlines.py

/batches/csv-0001-0200/lab/py/pd_badlines.py:8: ParserWarning: Skipping line 4: expected 3 fields, saw 4

  print(pd.read_csv("ragged.csv", on_bad_lines="warn"))
ParserError: Error tokenizing data. C error: Expected 3 fields in line 4, saw 4

   id name  qty
0   1  Ana  3.0
1   2   Bo  NaN
2   4   Di  7.0
   id name  qty
0   1  Ana  3.0
1   2   Bo  NaN
2   4   Di  7.0
   id name  qty
0   1  Ana  3.0
1   2   Bo  NaN
2   4   Di  7.0
collected: [['3', 'Cy', '5', 'extra']]

The default read raises ParserError: Error tokenizing data. C error: Expected 3 fields in line 4, saw 4. on_bad_lines="skip" drops the long row, which is Cy in the file, and keeps Bo with the short row padded: qty is NaN. on_bad_lines="warn" does the same and prints ParserWarning: Skipping line 4: expected 3 fields, saw 4 to standard error. Notice that the qty column changed from integers to float64 (3.0, NaN, 7.0) because NaN cannot live in an integer column; it is a small trap, and the dtype warning article shows how to handle it.

The third form takes a function. With engine="python", on_bad_lines can be a callable that receives each bad row as a list and returns either a fixed row or None to drop it. Mine appended the row to a list and returned None, and the list at the end held ['3', 'Cy', '5', 'extra']. That gives you the audit trail that skip throws away. The C engine does not support a callable here, so you pay the speed cost of the Python engine; for a large file, count the lines first and use the callable only if the count is small. Dropping rows without recording them is acceptable for a quick look and wrong for anything you will report on. The causes of ragged rows, and how to find them, are in the inconsistent column counts article.

Bytes that are not UTF-8

pandas assumes UTF-8. A file in a Windows or Western European code page fails on the first byte that is not valid:

import pandas as pd

path = "/batches/csv-0001-0200/lab/data/enc/latin1.csv"
try:
    pd.read_csv(path)
except UnicodeDecodeError as e:
    print("UnicodeDecodeError:", e)
print(pd.read_csv(path, encoding="latin-1").head(2))

python /batches/csv-0001-0200/lab/py/pd_encoding.py

UnicodeDecodeError: 'utf-8' codec can't decode byte 0xe9 in position 3: invalid continuation byte
   id         name       city
0   1  José Müller  São Paulo
1   2    Zoë Dupré     Zürich

The error is UnicodeDecodeError: 'utf-8' codec can't decode byte 0xe9 in position 3: invalid continuation byte. Byte 0xe9 is é in Latin-1 and in Windows 1252. With encoding="latin-1" the file loads and the names read José Müller and São Paulo. A caution: latin-1 can decode any byte, so it never raises, which means it will also "succeed" on a file that is really something else and give you wrong characters. Check the result by eye, and use the tools in the encoding detection article before guessing; the Latin-1 and Windows conversion article explains the difference between the two code pages.

Types come last

All of the above was about getting rows and columns right. The types come from inference, column by column, and they have their own traps. A column of ZIP codes becomes integers and loses its leading zeros (why and how to stop it). A column that is mostly numbers with a few stray strings raises a DtypeWarning (that article). Dates stay text unless you ask (parsing dates). My rule is to pass dtype=str for any column that is an identifier and to convert the rest after loading, so that every conversion is a line of code I can read.

How to tell it worked

Print df.shape and compare the row count with a count from another tool. Print df.dtypes and read each one against what you know. Print df.isna().sum() and compare it with the number of missing markers you expect; a count that is higher than the number of blank cells means the NA list took something you wanted. Look at df.head() and df.tail(), since a footer line such as Total shows up at the bottom. And check one row that you know has an awkward value against the source file.

When to stop adding options

A call with ten parameters is a sign that the file wants cleaning once, with a script you can read, rather than interpreting every time it is loaded. If you find yourself writing skiprows, comment, sep, decimal, thousands, na_values and encoding for every file from the same source, write a small function with those arguments and a docstring that says which system produces the file, and call that. If the file's problems are structural, such as rows that are cut off or quotes that never close, no option fixes them, and the repair belongs before the load. And if the data is big, consider that DuckDB can read the same file with the sniffer and options you have seen, and hand the result to pandas with .df().