Reading and writing files

Intermediate

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="")
Open in compiler

Line by line

  1. csv.reader with delimiter=";" splits the lines as sep=";" would; one field contains a comma, which is fine because the separator is a semicolon.
  2. A set of markers plays the part of the default missing-value list.
  3. csv.writer uses minimal quoting like to_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

1. A file uses semicolons between fields. Which argument reads it correctly?
2. How do you keep product codes such as 00731 as text?
3. Why pass index=False to to_csv?
4. What does chunksize make read_csv return?

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.