How do I read a CSV file with pandas and avoid the common problems?
Short answer
Use pd.read_csv, but set dtype, parse_dates and the separator explicitly when the defaults guess wrong. The usual pain points are leading zeros stripped from codes and dates left as text.
The short version
pd.read_csv("file.csv") reads a comma separated file into a DataFrame. It works out of the box for clean files, but the defaults guess types, so codes with leading zeros can lose them, dates arrive as text, and unusual separators break the parse. Set dtype, parse_dates and sep when needed rather than fixing the mess afterwards.
The basic call
import pandas as pd
df = pd.read_csv("orders.csv")
Leading zeros vanish
A column of codes like 007 is read as the number 7, dropping the zeros. Force it to stay as text.
df = pd.read_csv("orders.csv", dtype={"code": "string"})
More generally, any identifier (order numbers, postcodes, phone numbers) should be read as text, because it is a label, not a quantity you will do arithmetic on.
Dates stay as strings
By default date columns are read as text. Parse them at read time so they are ready to use.
df = pd.read_csv("orders.csv", parse_dates=["order_date"])
The separator is not a comma
European exports often use a semicolon, and some files are tab separated. Set sep explicitly.
df = pd.read_csv("export.csv", sep=";")
df = pd.read_csv("export.tsv", sep="\t")
Encoding errors
If you get a UnicodeDecodeError, the file is probably not UTF-8. A common culprit is Windows exports.
df = pd.read_csv("export.csv", encoding="latin-1")
Rubbish rows at the top
Some files begin with title rows before the header. Skip them.
df = pd.read_csv("report.csv", skiprows=3)
A sensible habit
After reading, always run df.dtypes and df.head() to check that numbers are numeric, dates are datetimes, and identifiers are text. Catching a type problem at the read stage is far cheaper than discovering, three steps later, that a join failed because one side stored codes as numbers and the other as text.