Reading CSV Files with pandas

Reading a CSV is easy; knowing that it was read correctly is the real engineering task. A successful read_csv call can still produce string dates, mixed identifiers, unexpected nulls, duplicated records, or silently inferred types that change when tomorrow’s file arrives.

Problem

You received a customer extract that will later join to orders and website events. Before building transformations, you need a reproducible load step and a small set of checks that establish what entered memory. This matters because every filter, join, and aggregation inherits the decisions made during ingestion.

Dataset used

This tutorial uses the Customer Dataset: 100 synthetic rows containing identifiers, countries, signup dates, customer segments, and emails. It deliberately includes three blank emails, inconsistent country casing, and one duplicate row.

Download customers.csv and place it in a data directory beside your notebook or script. The URL is also suitable for a short learning exercise, but production pipelines should normally download or land source files explicitly so retries, checksums, and retention are controlled.

Code: the smallest useful load

from pathlib import Path

import pandas as pd

data_path = Path("data") / "customers.csv"
customers = pd.read_csv(data_path)

print(customers.shape)
print(customers.head(3))

Path keeps the example portable across Windows, macOS, and Linux. It avoids hardcoded drive letters and makes the expected directory visible. The load produces a DataFrame, a two-dimensional labeled table whose columns may have different data types.

Output

(100, 6)
   customer_id  customer_name        country signup_date         segment                       email
0            1    Avery Atlas  United States  2023-01-01      Enterprise  customer001@example.test
1            2    Blake Atlas         Canada  2023-02-08  Small Business  customer002@example.test
2            3    Casey Atlas United Kingdom  2023-03-15        Consumer  customer003@example.test

Shape is an ingestion contract check, not proof of correctness. It confirms 100 rows and six columns, including the intentional duplicate. If the source unexpectedly produces zero rows, seven columns, or one giant column because the delimiter changed, stop before transforming it.

Code: make important types explicit

Pandas can parse the date and preserve the identifier as a nullable integer. Explicit choices reduce surprises when a blank value appears in a later delivery.

customers = pd.read_csv(
    data_path,
    dtype={"customer_id": "Int64", "segment": "category"},
    parse_dates=["signup_date"],
    usecols=[
        "customer_id",
        "customer_name",
        "country",
        "signup_date",
        "segment",
        "email",
    ],
)

required = {"customer_id", "customer_name", "country", "signup_date", "segment", "email"}
missing_columns = required.difference(customers.columns)
if missing_columns:
    raise ValueError(f"Missing required columns: {sorted(missing_columns)}")

Int64 is pandas’ nullable integer type; unlike the NumPy int64 type, it can represent missing values without converting the whole column to floating point. category can be efficient for repeated labels and makes the intended domain visible. parse_dates turns the ISO text into a datetime-like column, enabling reliable comparisons and date features.

usecols also acts as a boundary: the transformation reads only the fields it expects. That can reduce memory for wide files, but it should not replace schema-drift monitoring. If an upstream team adds a useful column, an explicit contract tells you it happened instead of accidentally changing downstream behavior.

Explanation: paths, URLs, and reproducibility

A relative path is resolved from the process working directory, not necessarily from the script file. In notebooks, print Path.cwd() when a file cannot be found. For packaged pipelines, pass the input location as configuration and log the resolved path.

For a quick exercise, pandas can read the published URL directly:

customers_url = "https://javi-on-data.com/resources/datasets/customers.csv"
customers = pd.read_csv(customers_url, parse_dates=["signup_date"])

Use the actual deployed domain for online reading. A local download is better for repeatable practice and works offline. In production, direct HTTP ingestion also needs timeouts, authentication, retries, immutable landing, and observability—concerns that read_csv alone does not solve.

Common mistakes

• Trusting inferred types without inspecting them.
• Hardcoding C:\... paths that work on one machine only.
• Treating blank strings, NA, NULL, and domain-specific sentinels as equivalent without a rule.
• Parsing dates only when they are first needed, which spreads conversion failures downstream.
• Assuming a successful load means the expected file or delimiter was used.
• Reading every column from a very wide extract when only a stable subset is required.
• Passing low_memory=False as a generic fix instead of defining ambiguous types.

Try it yourself

Load the file from a local data directory. Confirm the row and column counts, print customers.dtypes, and count missing values with customers.isna().sum(). Then parse signup_date, find the earliest and latest signup, and verify that three emails are missing.

Finally, compare len(customers) with len(customers.drop_duplicates()). Do not remove the duplicate yet. Record what you found so the next transformation can make a deliberate cleaning decision rather than changing the raw input invisibly.

Production notes

Treat ingestion metadata as part of the dataset. Record the source name, arrival time, byte size, row count, and a checksum when files must be auditable. Land the original bytes before applying corrections so a failed transformation can be replayed without requesting the source again. If files arrive repeatedly, decide whether a filename represents an immutable delivery or a location whose contents can change.

Encoding and delimiter assumptions also belong in the contract. UTF-8 is a good default, but source systems may emit byte-order marks or legacy encodings. Fail with a useful message rather than silently replacing unknown characters. For recurring feeds, test the header before loading the full file and capture rejected rows separately when policy allows partial acceptance.

Finally, remember that pandas runs inside one Python process. Estimate expanded memory, not just compressed file size, and monitor the process during representative loads. A successful tutorial-sized read does not prove that a multi-gigabyte delivery is safe in production.