Introduction
Every analysis starts by loading data and usually ends by saving results. pd.read_csv looks simple, but most "pandas is wrong" moments are really reading problems: a semicolon-separated file read as one column, "N/A" kept as text, product codes that lose their leading zeros, or a 5 GB file that does not fit in memory. This lesson covers the options that fix them.
How it works
Separators and missing values. pd.read_csv("data.csv", sep=";") reads files that use semicolons, common in countries where the comma is the decimal mark (decimal="," handles that too). By default, pandas treats empty fields and strings such as NA, N/A, NaN, null, NULL, None and nan as missing; na_values=["-", "missing"] adds more, and keep_default_na=False turns the default list off.
Choosing what to read. usecols=["date", "amount"] reads only some columns, nrows=1000 only the first rows, and skiprows=2 skips header junk. dtype={"zip": str} keeps codes as text so "02134" keeps its zero. parse_dates=["date"] converts date columns while reading, and index_col="id" makes a column the index.
df = pd.read_csv("sales.csv", sep=";", usecols=["date", "region", "amount"],
dtype={"region": "category"}, parse_dates=["date"])
Large files. chunksize=100_000 returns an iterator of DataFrames instead of one big table. You process each chunk and combine the partial results, for example by adding up group totals:
totals = None
for chunk in pd.read_csv("big.csv", chunksize=100_000):
part = chunk.groupby("category")["amount"].sum()
totals = part if totals is None else totals.add(part, fill_value=0)
Writing. df.to_csv("out.csv", index=False) writes a CSV; without index=False, the row labels become an extra unnamed column. Fields that contain the separator, a double quote or a newline are wrapped in double quotes, and quotes inside them are doubled, so Smith, John is written as "Smith, John". Missing values are written as empty fields unless na_rep says otherwise.
Other formats. read_excel and to_excel need an extra package (openpyxl); read_json, read_parquet and read_sql read other sources. Parquet keeps column types and is much faster and smaller than CSV for large data.
Example
# read_csv and to_csv modelled with the csv module: separators, missing markers, quoting.
import csv
import io
text = "name;city;age\nAna;Paris;31\nSmith, John;NA;\nBen;Rome;25\n"
rows = list(csv.reader(io.StringIO(text), delimiter=";"))
header, data = rows[0], rows[1:]
missing = {"", "NA", "N/A", "NaN", "null"}
print("shape:", (len(data), len(header)))
print("missing per column:", [sum(1 for r in data if r[i] in missing) for i in range(len(header))])
out = io.StringIO()
writer = csv.writer(out, lineterminator="\n")
writer.writerow(header)
for r in data:
writer.writerow(["" if v in missing else v for v in r])
print(out.getvalue(), end="")Line by line
csv.readerwithdelimiter=";"splits the lines assep=";"would; one field contains a comma, which is fine because the separator is a semicolon.- A set of markers plays the part of the default missing-value list.
csv.writeruses minimal quoting liketo_csv: only the field with a comma gets quotes, and missing values become empty fields.
Expected result
shape: (3, 3)
missing per column: [0, 1, 1]
name,city,age
Ana,Paris,31
"Smith, John",,
Ben,Rome,25
In the real world
Exports from banks, shops and government portals often use semicolons, odd missing markers and codes with leading zeros. Data engineers read huge logs in chunks, and teams store cleaned data as Parquet so the next analysis loads in seconds with the right types.
Common mistakes
- Reading a semicolon file with the default comma and getting one column.
- Losing leading zeros in codes because pandas guessed a number type.
- Writing with the index and finding an "Unnamed: 0" column on the next read.
- Reading a whole huge file when a few columns or chunks would do.
Best practices
- Open a new file in a text editor first, or read it with nrows=5, to see its separator and markers.
- Pass dtype for identifiers and parse_dates for dates.
- Write with index=False unless the index carries information.
Summary
read_csv's sep, na_values, usecols, dtype, parse_dates, nrows and chunksize control what is read and how. Chunks let you aggregate files larger than memory. to_csv(index=False) writes clean files, quoting fields that contain the separator or quotes, and pandas reads and writes Excel, JSON, Parquet and SQL as well.
Quick check
Key takeaways
- Check sep, na_values and dtype when a file loads with wrong columns or types.
- Use usecols, nrows and chunksize to read only what you need.
- to_csv(index=False) avoids an extra index column; fields with commas or quotes are quoted.
Practice problems for this lesson
My notes
Write down what you want to remember from this lesson. Notes are saved on this device as you type, and appear on your dashboard.