Reading messy CSV files with pandas

pd.read_csv reads a CSV file by guessing: it guesses each column’s type from what the values look like, treats a long list of strings as “missing”, assumes the file is UTF-8 and stops at the first line with too many fields. Real exports break those guesses, often without an error. Account numbers lose their leading zeros, amounts written as 1,250.00 stay text, the country code NA becomes a missing value, and day-first dates are either left as text or, worse, read month first.

Reading a CSV robustly means telling read_csv what you already know about the file instead of letting it guess: the encoding, the separator, the markers this export uses for “missing”, how its numbers and dates are written, and which columns are codes rather than numbers. Then you check df.dtypes and count every line that was set aside. This lesson does that on one messy file, one problem at a time, with every run recorded.

A messy export, read the naive way

Here is a short export of fee payments from a college office system. It is twelve receipts long and has most of the problems you will meet in real files:

receipt_no,student,account_no,amount,paid_on,country,mode
R-1001,Asha Rao,004512,"1,250.00",05/04/2026,IN,card
R-1002,Ben Clarke,000981,-,12/04/2026,GB,cash
R-1003,Chen Wei,010220,"12,400.50",01/05/2026,CN,transfer
R-1004,Dara Nkosi,003311,980.00,13/05/2026,NA,card
R-1005,Elif Kaya,000007,"1,000",14/05/2026,TR,nil
R-1006,Okafor, Chidi,002200,75.25,15/05/2026,NG,cash
R-1007,Farah Ali,001010,"2,300.00",16/05/2026,AE,card
R-1008,Gita Thapa,000450,,18/05/2026,NP,transfer
R-1009,Hugo Silva,007070,640.00,19/05/2026,BR,card
R-1010,Ines Moreau,000099,"10,000.00",20/05/2026,FR,transfer
R-1011,Jomo Otieno,003030,"1,150.75",21/05/2026,KE,cash
R-1012,Kai Müller,000808,320.00,22/05/2026,DE,card

The first attempt passes no options at all, and it stops at once:

import pandas as pd

# A first attempt: no options at all.
try:
    fees = pd.read_csv("fees_export.csv")
    print(fees.head())
except pd.errors.ParserError as err:
    print(f"ParserError: {err}")

ParserError: Error tokenizing data. C error: Expected 7 fields in line 7, saw 8

Line 7 is the receipt of Okafor, Chidi. The name contains a comma, and nobody put it in quotes, so that line has 8 fields where the header has 7. CSV’s rule, written down in RFC 4180, is that a field containing a comma should be enclosed in double quotes; this exporter forgot. Stopping is the right default: a parser that silently skipped the line would hand you a total that is wrong by one receipt.

To see what else is wrong, skip that line for a moment and look at what pandas guessed for the rest:

import pandas as pd

# Skip the broken line for now, only to see what pandas guessed for the rest.
fees = pd.read_csv("fees_export.csv", on_bad_lines="skip")

print(fees.dtypes, end="\n\n")
print(fees.head(5))
print(f"\n{len(fees)} rows; the file has 12 receipts")

receipt_no      str
student         str
account_no    int64
amount          str
paid_on         str
country         str
mode            str
dtype: object

  receipt_no     student  account_no     amount     paid_on country      mode
0     R-1001    Asha Rao        4512   1,250.00  05/04/2026      IN      card
1     R-1002  Ben Clarke         981          -  12/04/2026      GB      cash
2     R-1003    Chen Wei       10220  12,400.50  01/05/2026      CN  transfer
3     R-1004  Dara Nkosi        3311     980.00  13/05/2026     NaN      card
4     R-1005   Elif Kaya           7      1,000  14/05/2026      TR       nil

11 rows; the file has 12 receipts

Five problems hide in that output, and none of them raised an error:

• account_no became int64, so 004512 is now 4512 and 000007 is 7. Account numbers are codes; arithmetic on them means nothing, and the zeros were part of the code.
• amount is str, because 1,250.00 is not a number until something says the comma separates thousands.
• paid_on is str, so dates cannot be compared, sorted or grouped by month.
• country of the fourth row is NaN. NA is Namibia’s two-letter ISO 3166 code, as the United Nations’ table of country codes lists it, but it is also on pandas’ list of strings that mean “missing”.
• The export writes “missing” as - and nil, which pandas does not know, so they stay as text.

The first column’s name is clean, although the file starts with a byte-order mark (the three bytes EF BB BF): pandas removes it when it decodes UTF-8. In pandas 3, text columns get the dedicated str dtype instead of the older object, as the lesson on data types in pandas 3 explains.

Where each option does its work

read_csv works in stages, and every option belongs to one of them. Knowing the stage tells you which option can fix a problem, and why an early mistake cannot be repaired later: no type conversion rescues text that was decoded with the wrong encoding.

The careful read

This call fixes all six problems. Each option is explained in the sections that follow:

import pandas as pd

bad_lines = []


def set_aside(fields):
    """Keep a line that has too many fields, and leave it out of the frame."""
    bad_lines.append(fields)
    return None


fees = pd.read_csv(
    "fees_export.csv",
    dtype={"account_no": "str", "country": "str"},  # codes stay text, zeros and all
    thousands=",",  # "12,400.50" is a number
    na_values=["-", "nil", ""],  # this export's own markers for "missing"
    keep_default_na=False,  # and only those: "NA" is Namibia here
    parse_dates=["paid_on"],
    date_format="%d/%m/%Y",  # day first, stated rather than guessed
    on_bad_lines=set_aside,
    engine="python",  # a function that receives the fields needs this engine
)

print(fees.dtypes, end="\n\n")
print(fees.head(4), end="\n\n")
print(f"{len(fees)} rows read, {len(bad_lines)} line set aside:")
for fields in bad_lines:
    print(fields)
print(f"\nCollected: {fees['amount'].sum():,.2f} from {fees['amount'].notna().sum()} payments")
print(f"Missing amounts: {fees['amount'].isna().sum()}")

receipt_no               str
student                  str
account_no               str
amount               float64
paid_on       datetime64[us]
country                  str
mode                     str
dtype: object

  receipt_no     student account_no   amount    paid_on country      mode
0     R-1001    Asha Rao     004512   1250.0 2026-04-05      IN      card
1     R-1002  Ben Clarke     000981      NaN 2026-04-12      GB      cash
2     R-1003    Chen Wei     010220  12400.5 2026-05-01      CN  transfer
3     R-1004  Dara Nkosi     003311    980.0 2026-05-13      NA      card

11 rows read, 1 line set aside:
['R-1006', 'Okafor', ' Chidi', '002200', '75.25', '15/05/2026', 'NG', 'cash']

Collected: 30,041.25 from 9 payments
Missing amounts: 2

The receipts now have the types their meaning needs: codes as str, money as float64, dates as datetime64[us]. The broken line was not thrown away silently. It is kept in bad_lines, printed, and counted, so the report can say “11 receipts read, 1 set aside for repair” and anyone can check the total of 30,041.25 against the source system. Two amounts are missing (receipt R-1002 says -, receipt R-1008 is empty), and the count says so instead of hiding them inside the sum.

Codes are text: dtype

Any column whose values are identifiers rather than quantities should be read as text: account and roll numbers, postal codes, phone numbers, product codes, invoice numbers. A quick test is to ask whether adding two values would mean anything. dtype={"account_no": "str"} keeps the value exactly as written, leading zeros and all, and dtype="str" reads every column as text when you want to inspect a file before deciding anything.

dtype also fixes types you would otherwise convert later: "Int64" (capital I) is pandas’ integer type that allows missing values, "category" suits a column with a few repeated labels, and "float64" makes a numeric column fail loudly if a stray word appears in it. For yes/no columns, true_values=["yes"] and false_values=["no"] give real booleans. One trap: when you pass converters for a column, pandas uses the converter instead of the dtype for that column.

Missing markers: na_values and keep_default_na

By default read_csv treats about twenty strings as missing, among them the empty string, NA, N/A, NULL, null, NaN and None. na_values adds your file’s own markers to that list, and keep_default_na=False throws the default list away, so only the markers you name count. When you switch the defaults off, remember to name the empty string too if empty cells mean “missing”, as the careful read above does.

import io

import pandas as pd

text = "student,country,amount\nDara,NA,980\nElif,TR,-\nHugo,BR,\n"

default = pd.read_csv(io.StringIO(text))
print(default)
print("amount is", default["amount"].dtype, end="\n\n")

own = pd.read_csv(io.StringIO(text), na_values=["-", ""], keep_default_na=False)
print(own)
print("amount is", own["amount"].dtype)
print("missing per column:", own.isna().sum().to_dict())

  student country amount
0    Dara     NaN    980
1    Elif      TR      -
2    Hugo      BR    NaN
amount is str

  student country  amount
0    Dara      NA   980.0
1    Elif      TR     NaN
2    Hugo      BR     NaN
amount is float64
missing per column: {'student': 0, 'country': 0, 'amount': 2}

With the defaults, Namibia disappears and the - keeps the whole amount column as text. With the file’s own markers, NA stays a country, and the two missing amounts are real missing values in a numeric column. The defaults are reasonable for many files; switch them off whenever a column holds codes, names or abbreviations that could collide with the list.

Numbers with separators: thousands and decimal

A number such as 1,234.50 is text to read_csv until you say what the comma is. thousands="," removes the separator inside numeric fields, and in files written with a decimal comma, 1.234,50, you swap the roles with thousands="." and decimal="," (such files often separate fields with ;, so sep=";" as well):

import io

import pandas as pd

comma_thousands = 'item,amount\nlaptop,"1,234.50"\nmouse,12.5\n'
dot_thousands = "item;amount\nlaptop;1.234,50\nmouse;12,5\n"

print(pd.read_csv(io.StringIO(comma_thousands))["amount"].tolist())
print(pd.read_csv(io.StringIO(comma_thousands), thousands=",")["amount"].tolist())
print(pd.read_csv(io.StringIO(dot_thousands), sep=";", thousands=".", decimal=",")["amount"].tolist())

# One stray character keeps the whole column as text. Find the rows that do not convert.
messy = 'item,amount\nlaptop,"1,234.50"\nbag,"~1,100"\npen,4.75\n'
prices = pd.read_csv(io.StringIO(messy), thousands=",")
print("\ndtype:", prices["amount"].dtype)
as_number = pd.to_numeric(prices["amount"].str.replace(",", ""), errors="coerce")
print(prices.loc[as_number.isna()])

['1,234.50', '12.5']
[1234.5, 12.5]
[1234.5, 12.5]

dtype: str
  item  amount
1  bag  ~1,100

The last part is the habit that saves the most time. One value that does not parse, here ~1,100, keeps the whole column as str, and nothing tells you which row is to blame. pd.to_numeric(..., errors="coerce") turns every value it cannot convert into NaN, so the rows that became NaN are exactly the culprits. Fix the source or decide a rule for them; do not let them vanish into a sum.

import io

import pandas as pd

# Indian grouping puts commas after the thousands and then after every two digits.
text = 'student,amount\nAsha,"1,23,456.78"\nRavi,"12,34,567"\nMeena,"₹2,500"\n'

fees = pd.read_csv(io.StringIO(text), thousands=",")
print(fees["amount"].dtype, fees["amount"].tolist())

# The rupee sign keeps the column as text: strip the sign and the commas, then convert.
amount = pd.to_numeric(fees["amount"].str.replace("[₹,]", "", regex=True))
print(amount.dtype, amount.tolist())

# Without the stray sign, thousands="," reads both groupings as numbers.
clean = pd.read_csv(io.StringIO(text.replace("₹", "")), thousands=",")
print(clean["amount"].dtype, clean["amount"].tolist())

str ['1,23,456.78', '12,34,567', '₹2,500']
float64 [123456.78, 1234567.0, 2500.0]
float64 [123456.78, 1234567.0, 2500.0]

Dates: state the format, never let pandas guess

parse_dates=["paid_on"] asks pandas to turn the column into dates. Without a format, pandas looks at the first value, guesses a format from it and applies that guess to the whole column. For a value such as 05/04/2026 the guess is month first, which is wrong for every export that writes the day first, as much of the world does:

import io

import pandas as pd

# Three payment dates written day first; every day is 12 or less.
text = "paid_on\n05/04/2026\n12/04/2026\n01/05/2026\n"

guessed = pd.read_csv(io.StringIO(text), parse_dates=["paid_on"])
stated = pd.read_csv(io.StringIO(text), parse_dates=["paid_on"], date_format="%d/%m/%Y")
print("guessed:", guessed["paid_on"].dt.strftime("%d %b %Y").tolist())
print("stated: ", stated["paid_on"].dt.strftime("%d %b %Y").tolist())

# In the full export a day of 13 breaks the guess, and the column quietly stays text.
fees = pd.read_csv("fees_export.csv", on_bad_lines="skip", parse_dates=["paid_on"])
print("export without a format:", fees["paid_on"].dtype)

# One impossible date also leaves the whole column as text. Convert after reading to count the bad ones.
raw = pd.Series(["05/04/2026", "31/04/2026", "12/04/2026"])
dates = pd.to_datetime(raw, format="%d/%m/%Y", errors="coerce")
print(dates.dt.strftime("%Y-%m-%d").tolist())
print("could not parse:", raw[dates.isna()].tolist())

guessed: ['04 May 2026', '04 Dec 2026', '05 Jan 2026']
stated:  ['05 Apr 2026', '12 Apr 2026', '01 May 2026']
export without a format: str
['2026-04-05', nan, '2026-04-12']
could not parse: ['31/04/2026']

Read the four results carefully:

• When every day happens to be 12 or less, the guess “works” and every date is wrong: the fifth of April became the fourth of May, with no warning at all. This is the most expensive kind of bug, because the output looks right.
• date_format="%d/%m/%Y" states the order, using Python’s strftime codes, and the same text gives the right dates.
• In the full export, the day 13 contradicts the month-first guess, and pandas leaves the column as text, again without an error. Check df.dtypes after every read.
• One impossible date such as 31/04/2026 makes read_csv keep the whole column as text even with a format. Reading the column as text and converting it with pd.to_datetime(..., format=..., errors="coerce") turns only the bad value into NaT (missing), so you can list and count the failures.

For files whose dates follow ISO 8601 (2026-04-05), date_format="ISO8601" accepts any valid ISO form. The pandas documentation also offers "mixed", which guesses per value; it is risky for the reason shown above, so use it only when you have checked the result. In pandas 3, these parsed dates have microsecond resolution, datetime64[us].

Encodings and the byte-order mark

A CSV file is bytes; read_csv must decode them into text, and it assumes UTF-8. Files saved by older Windows programs often use a Windows code page such as cp1252 instead, and some programs write UTF-16. Both fail loudly as UTF-8, which is good, because the error tells you to choose the encoding:

import pandas as pd

text = "city,owner,amount\nZürich,José,120\nKöln,Zoë,80\n"
with open("legacy.csv", "wb") as f:
    f.write(text.encode("cp1252"))  # a Windows code page, common in older exports
with open("utf8_bom.csv", "wb") as f:
    f.write(text.encode("utf-8-sig"))  # UTF-8 with a byte-order mark

try:
    pd.read_csv("legacy.csv")
except UnicodeDecodeError as err:
    print("legacy.csv:", err)
print("cp1252: ", pd.read_csv("legacy.csv", encoding="cp1252")["owner"].tolist())
print("replace:", pd.read_csv("legacy.csv", encoding_errors="replace")["owner"].tolist())
print("BOM:    ", pd.read_csv("utf8_bom.csv").columns.tolist())

# A wrong single-byte encoding never fails: every byte maps to some character.
wrong = pd.read_csv("utf8_bom.csv", encoding="latin-1")
print("latin-1:", wrong.columns.tolist(), wrong["owner"].tolist())

legacy.csv: 'utf-8' codec can't decode byte 0xfc in position 19: invalid start byte
cp1252:  ['José', 'Zoë']
replace: ['Jos�', 'Zo�']
BOM:     ['city', 'owner', 'amount']
latin-1: ['city', 'owner', 'amount'] ['José', 'Zoë']

• encoding="cp1252" decodes the legacy file correctly. Python’s documentation lists every encoding name it accepts.
• encoding_errors="replace" swaps undecodable bytes for �. Use it only to look at a file you cannot decode yet, and count the replacement characters before you trust anything else in it.
• A UTF-8 file with a byte-order mark reads cleanly; the mark is removed.
• The dangerous case is the last one. latin-1 maps every possible byte to some character, so it never fails, and a UTF-8 file read as latin-1 turns José into José and the mark into . Strings such as é in your data almost always mean UTF-8 text was decoded with a single-byte encoding.

Lines with too many fields: on_bad_lines

on_bad_lines decides what happens to a line that has more fields than the header:

• "error", the default, raises ParserError and names the line. Keep it while you are exploring a new file.
• "warn" drops the line and prints a warning; "skip" drops it silently. Neither tells your report how many receipts were lost.
• A function, with engine="python", receives each bad line as a list of fields. Return None to drop it, or return a repaired list with the right number of fields. The careful read keeps every dropped line in a list, so the number of dropped lines is printed and the lines can be fixed at the source.

Lines with too few fields are not “bad”: pandas fills the missing fields with NaN, which a later count of missing values will reveal.

Big files: peek, prune and read in chunks

Most files are small enough to read whole. When one is not, three options keep memory under control. nrows reads only the first rows, which is enough to choose dtypes and columns. usecols reads only the columns you name, which saves both time and memory. chunksize turns read_csv into an iterator that yields one DataFrame of that many rows at a time, so you can summarise each chunk and combine the summaries:

import numpy as np
import pandas as pd

# Make a 600,000-row file of card transactions (seeded, so every run writes the same file).
rng = np.random.default_rng(7)
n = 600_000
cities = np.array(["Lagos", "Leeds", "Lima", "Osaka", "Perth", "Pune", "Quito", "Riga"])
pd.DataFrame({
    "txn_id": np.arange(1, n + 1),
    "city": cities[rng.integers(0, len(cities), n)],
    "amount": rng.integers(50, 5_000, n),
    "channel": np.array(["app", "web", "shop"])[rng.integers(0, 3, n)],
    "note": np.array(["", "repeat", "promo", "refund-check"])[rng.integers(0, 4, n)],
}).to_csv("transactions.csv", index=False)

# Peek first: five rows are enough to choose dtypes and columns.
print(pd.read_csv("transactions.csv", nrows=5), end="\n\n")

full = pd.read_csv("transactions.csv")
by_city_full = full.groupby("city")["amount"].sum()

parts, largest = [], 0
with pd.read_csv("transactions.csv", usecols=["city", "amount"], chunksize=100_000) as reader:
    for chunk in reader:
        largest = max(largest, chunk.memory_usage(deep=True).sum())
        parts.append(chunk.groupby("city")["amount"].sum())
by_city_chunks = pd.concat(parts).groupby(level=0).sum()

print(by_city_chunks, end="\n\n")
print("same totals:", by_city_full.equals(by_city_chunks))
print(f"chunks: {len(parts)}")
print(f"whole file in memory: {full.memory_usage(deep=True).sum() / 1e6:.1f} MB")
print(f"largest chunk of 2 columns: {largest / 1e6:.1f} MB")

   txn_id   city  amount channel          note
0       1   Riga    2971     web         promo
1       2   Pune    3881     web  refund-check
2       3   Pune    2974     app  refund-check
3       4   Riga     194    shop         promo
4       5  Perth     562     app        repeat

city
Lagos    188136781
Leeds    189447916
Lima     189201189
Osaka    188802855
Perth    190071163
Pune     190151305
Quito    189940357
Riga     188775887
Name: amount, dtype: int64

same totals: True
chunks: 6
whole file in memory: 32.3 MB
largest chunk of 2 columns: 2.1 MB

The chunked run reads six chunks of 100,000 rows and two columns, and it gets exactly the same totals as the full read. The whole file needed 32.3 MB in memory, while the largest chunk needed 2.1 MB, so the same method works for a file many times bigger than your memory. It works because a sum of sums is a sum; a median or a count of distinct values cannot be combined that simply, and needs a tool that sees all the data, such as DuckDB or polars, which later modules cover.

Choosing an engine

read_csv has three parsers. The default C engine is fast and supports almost every option. The python engine is much slower, but it is the one for an on_bad_lines function that receives the fields, for regular-expression separators and for skipfooter. The pyarrow engine, which needs the PyArrow package, reads with several threads and is the fastest on large files, but it accepts fewer options. This run timed all three on the same 200,000-row file:

import time

import numpy as np
import pandas as pd

rng = np.random.default_rng(7)
n = 200_000
pd.DataFrame({
    "txn_id": np.arange(1, n + 1),
    "city": np.array(["Lagos", "Leeds", "Lima", "Osaka"])[rng.integers(0, 4, n)],
    "amount": rng.integers(50, 5_000, n),
}).to_csv("transactions.csv", index=False)

for engine in ["c", "python", "pyarrow"]:
    best = float("inf")
    for _ in range(3):  # the best of three runs, to smooth out a busy computer
        start = time.perf_counter()
        frame = pd.read_csv("transactions.csv", engine=engine)
        best = min(best, time.perf_counter() - start)
    print(f"{engine:<8} {best:.2f} s for {len(frame):,} rows")

c        0.14 s for 200,000 rows
python   2.57 s for 200,000 rows
pyarrow  0.01 s for 200,000 rows

And this run shows which options the pyarrow engine refused with a ValueError:

import io

import pandas as pd

data = b"city,amount\nLeeds,120\nLima,80\n"
options = {
    "thousands": ",",
    "decimal": ",",
    "nrows": 1,
    "chunksize": 1,
    "comment": "#",
    "dayfirst": True,
    "skipfooter": 1,
    "on_bad_lines": "skip",
    "usecols": ["amount"],
    "na_values": ["-"],
}
for name, value in options.items():
    try:
        pd.read_csv(io.BytesIO(data), engine="pyarrow", **{name: value})
        print(f"accepted  {name}")
    except ValueError:
        print(f"refused   {name}")

refused   thousands
accepted  decimal
refused   nrows
refused   chunksize
refused   comment
refused   dayfirst
refused   skipfooter
accepted  on_bad_lines
accepted  usecols
accepted  na_values

Your turn: load a mess-bills export

A hostel kitchen exports its monthly mess bills. Write load_bills(path) so that every column arrives in its proper type. Look at the file first; it has six quirks, and the lesson has covered each of them:

# MessDesk export, kitchen 2
roll_no;name;hostel;billed_on;meals;amount;paid
0042;Anil;NB;20260403;31;2.480,00;yes
0107;Bea;NA;20260403;28;2.240,00;no
0215;Chidi;SB;20260403;n/a;n/a;no
0009;Divya;NA;20260404;30;2.400,50;yes
0311;Emre;NB;20260404;29;?;yes
0050;Fatima;SB;20260405;31;2.480,00;yes
1200;Gopal;NB;20260405;12;960,25;no

import pandas as pd


def load_bills(path):
    """Read the mess-bills export with every column in its proper type."""
    return pd.read_csv(path)  # replace this call with one that handles the file's quirks

The checker reads the file with your function and tests seven things. With the starting point, every check fails:

import pandas as pd

from load_bills import load_bills

bills = load_bills("mess_bills.csv")


def check(name, test):
    try:
        ok = bool(test())
    except Exception:  # a missing column, for example
        ok = False
    print("PASS" if ok else "FAIL", name)
    return ok


results = [
    check("7 rows and 7 columns", lambda: bills.shape == (7, 7)),
    check("roll_no keeps its leading zeros", lambda: bills["roll_no"].tolist()[:2] == ["0042", "0107"]),
    check('hostel "NA" stays a hostel code', lambda: (bills["hostel"] == "NA").sum() == 2),
    check("billed_on holds dates", lambda: bills["billed_on"].iloc[0] == pd.Timestamp("2026-04-03")),
    check("amount is a number with 2 missing", lambda: bills["amount"].dtype == "float64" and bills["amount"].isna().sum() == 2),
    check("amounts add up to 10,560.75", lambda: round(bills["amount"].sum(), 2) == 10560.75),
    check("paid is True or False", lambda: bills["paid"].dtype == bool and bills["paid"].sum() == 4),
]
print(f"{sum(results)} of {len(results)} checks pass")

FAIL 7 rows and 7 columns
FAIL roll_no keeps its leading zeros
FAIL hostel "NA" stays a hostel code
FAIL billed_on holds dates
FAIL amount is a number with 2 missing
FAIL amounts add up to 10,560.75
FAIL paid is True or False
0 of 7 checks pass

Put the three files in a folder of your own and edit load_bills.py alone. Rerun python check_bills.py after each change; the goal is 7 of 7 checks pass. Hints: the first line is not part of the table (comment or skiprows), the separator is a semicolon, numbers use a decimal comma, and the dates are written as 20260403 (%Y%m%d). One hostel code collides with pandas’ default missing markers.

Interview questions

Fresher · Why does read_csv turn the account number 004512 into 4512, and how do you stop it?

read_csv infers each column’s type from its values. Every value in that column consists of digits, so pandas converts the column to int64, and an integer has no leading zeros: 004512 is stored as the number 4512. The fix is to say the column is text when reading, with dtype={"account_no": "str"}; then the value is kept exactly as written. Converting back after the read does not work, because the zeros are already gone, and padding with zfill only works if every code has the same length. The rule behind it: identifiers such as account numbers, postal codes and phone numbers are labels, not quantities, so they belong in text columns even when they contain only digits.

Key takeaways

• read_csv guesses types, missing markers, the encoding and date formats; most wrong guesses raise no error, so check df.dtypes after every read.
• Read codes (accounts, postal codes, phones, product codes) with dtype="str" to keep their leading zeros.
• na_values adds your file’s markers for “missing”; keep_default_na=False drops pandas’ own list, which contains NA, a real country code.
• thousands and decimal parse 1,234.50 and 1.234,50; pd.to_numeric(errors="coerce") finds the row that keeps a column as text.
• State date formats with date_format: a month-first guess on day-first dates can be silently wrong.
• encoding must match the bytes. A wrong single-byte encoding such as latin-1 never fails and produces é.
• Keep on_bad_lines="error" while exploring; in a pipeline, use a function that keeps and counts every dropped line.
• nrows, usecols and chunksize keep memory low; chunked sums equal whole-file sums, chunked medians do not.