Importing Data

You have been handed a file called customers.csv and told to find out how many orders came from each region. You do the obvious thing:

import pandas as pddf = pd.read_csv("customers.csv")df.groupby("region")["order_total"].sum()

It runs. You get numbers. You put them in a slide deck. Three days later someone points out that "North" and "north " (with a trailing space) are being counted as different regions, that customer ID 00745 has silently become the integer 745, and that the order_total column is a string because 47 rows contain the text "N/A". Your sums are wrong, and they were wrong from the very first line of code.

Nothing in that snippet was a typo. The bug was that reading a file was treated as a trivial step rather than as the moment where every downstream assumption gets locked in. Importing data is not a formality before the real work. It is the real work, done early, where mistakes are cheap to fix.

This lesson covers where data comes from, how to pull it in from each source, and — most importantly — how to check what you actually got before you build anything on top of it.

The Shape of the Data Landscape

Almost every dataset you will ever load arrives in one of four forms. Knowing which one you are dealing with tells you which failure modes to expect.

Every import is a translation. Something written in one system's rules is being reinterpreted under another's, and translation always loses information unless you say explicitly what to preserve.

Flat Files: CSV, TSV and Excel

A CSV file has no type system. It is text. Every value in it is a string until something decides otherwise, and by default that something is pandas' inference engine, guessing column by column. Guessing is right most of the time, which is exactly what makes it dangerous — you stop checking.

Reading with intent instead of by default

Compare the naive read with a deliberate one:

1import pandas as pd23# Naive: pandas guesses everything4df = pd.read_csv("customers.csv")56# Deliberate: you declare what you expect7df = pd.read_csv(8    "customers.csv",9    dtype={"customer_id": "string", "postcode": "string"},  # keep leading zeros10    parse_dates=["signup_date"],11    na_values=["N/A", "NA", "-", "missing", ""],            # what counts as missing12    encoding="utf-8",13    thousands=",",                                          # "1,250" -> 125014)

The dtype argument is the single most valuable one. Any identifier — customer ID, postcode, product SKU, phone number — should be read as text. These look numeric but are not: you never add two postcodes together, and the leading zeros carry meaning.

Real-world CSV variations

Files in the wild rarely match the textbook shape. The parameters below handle the usual deviations.

If a file fails to parse and you cannot see why, stop guessing and look at the raw bytes:

with open("messy.csv", "rb") as f:    for _ in range(5):        print(f.readline())

Reading in binary mode ("rb") shows you the literal bytes, including the byte-order mark \xef\xbb\xbf that Excel likes to write at the start of files, and the \r\n line endings that come from Windows. Both cause parse errors that look mysterious in text mode.

Files too large for memory

When a file will not fit in RAM, read it in chunks and reduce each chunk as you go. You never hold the whole thing at once.

1totals = []2for chunk in pd.read_csv("huge_orders.csv", chunksize=200_000):3    chunk = chunk[chunk["status"] == "completed"]4    totals.append(chunk.groupby("region")["amount"].sum())56result = pd.concat(totals).groupby(level=0).sum()

The pattern is: filter and aggregate inside the loop, combine afterwards. If you append raw chunks to a list and concatenate at the end, you have simply rebuilt the memory problem with extra steps.

Many files, one table

1from pathlib import Path23frames = []4for path in sorted(Path("data/monthly").glob("*.csv")):5    part = pd.read_csv(path)6    part["source_file"] = path.name   # keep provenance7    frames.append(part)89df = pd.concat(frames, ignore_index=True)

Adding the source_file column costs nothing and repays itself the first time one month's numbers look wrong and you need to know which file they came from.

Excel

Excel files carry formatting, formulas and multiple sheets. Read the sheet you want, explicitly:

1sheets = pd.read_excel("report.xlsx", sheet_name=None)   # dict of all sheets2print(list(sheets.keys()))34df = pd.read_excel(5    "report.xlsx",6    sheet_name="Q3 Sales",7    skiprows=2,            # branded header block8    usecols="B:H",         # spreadsheet column letters9)

Watch for merged cells: they produce a value in the first cell and NaN in the rest, which looks like missing data but is really a layout artefact. Forward-filling those columns with df["region"] = df["region"].ffill() usually restores the intended meaning.

JSON and Nested Structures

JSON's problem is the opposite of CSV's. CSV is flat and typeless; JSON has types but arbitrary nesting. A record like this is not a table row:

1{2  "order_id": "A-1041",3  "customer": {"id": 77, "city": "Leeds"},4  "items": [{"sku": "X1", "qty": 2}, {"sku": "Y9", "qty": 1}]5}

pd.json_normalize flattens it. The key decision is what one row should represent.

1import json2import pandas as pd34with open("orders.json") as f:5    orders = json.load(f)67# One row per order; nested customer fields become customer.id, customer.city8per_order = pd.json_normalize(orders)910# One row per item, with order-level fields carried down11per_item = pd.json_normalize(12    orders,13    record_path="items",14    meta=["order_id", ["customer", "city"]],15)

For newline-delimited JSON — one JSON object per line, common in log exports — use pd.read_json("events.jsonl", lines=True).

Before flattening any nested data, decide in one sentence what a single row means. "One row per order item" is a design decision, and getting it wrong is why counts come out inflated.

Databases

A database gives you something files do not: the ability to make the data source do the filtering. Pulling a whole table into Python and then filtering it is the most common waste of time and memory in data work.

1import sqlite32import pandas as pd34conn = sqlite3.connect("shop.db")56# List what is actually in the database7tables = pd.read_sql("SELECT name FROM sqlite_master WHERE type='table'", conn)89# Push the work to the database, not to pandas10df = pd.read_sql(11    """12    SELECT region, DATE(created_at) AS day, SUM(amount) AS revenue13    FROM orders14    WHERE created_at >= ? AND status = 'completed'15    GROUP BY region, day16    """,17    conn,18    params=["2024-01-01"],19    parse_dates=["day"],20)21conn.close()

Note params. Never build SQL by pasting values into an f-string. Apart from the security risk, parameter binding handles quoting and type conversion correctly, which string formatting does not.

For PostgreSQL or MySQL, SQLAlchemy gives a uniform interface:

1from sqlalchemy import create_engine23engine = create_engine("postgresql+psycopg2://user:password@host:5432/shop")4with engine.connect() as conn:5    df = pd.read_sql("SELECT * FROM orders LIMIT 1000", conn)

Credentials belong in environment variables, never in the notebook. os.environ["DB_URL"] keeps them out of version control and out of screenshots.

Web APIs

An API call is a file read that can fail halfway through, be rate-limited, or return only part of the answer. Treat each of those as a certainty, not a possibility.

1import requests23resp = requests.get(4    "https://api.example.com/v1/orders",5    params={"since": "2024-01-01", "limit": 100},6    headers={"Authorization": f"Bearer {token}"},7    timeout=30,8)9resp.raise_for_status()      # turn a 4xx/5xx into a visible exception10data = resp.json()

Two lines there matter more than they look. timeout stops a hung request from freezing your script forever — without it, the default is to wait indefinitely. raise_for_status() converts an HTTP error into a Python exception; without it, a 401 response body ("unauthorised") gets parsed as JSON and flows downstream as though it were data.

Pagination

APIs return results in pages. If you read only the first response, you get the first 100 rows and no warning that 40,000 more exist.

1import time23def fetch_all(url, headers, page_size=100, max_pages=500):4    rows, page = [], 15    while page <= max_pages:6        r = requests.get(url, headers=headers,7                         params={"page": page, "per_page": page_size},8                         timeout=30)9        if r.status_code == 429:            # rate limited10            time.sleep(int(r.headers.get("Retry-After", 5)))11            continue12        r.raise_for_status()13        batch = r.json().get("results", [])14        if not batch:15            break16        rows.extend(batch)17        page += 118        time.sleep(0.2)                     # be a polite client19    return pd.DataFrame(rows)

The max_pages guard exists because a malformed API that always returns the same page will otherwise loop until your disk fills. Bound every loop that talks to a network.

Validating What You Actually Got

Here is the discipline that separates careful work from the story at the top of this lesson. Immediately after loading, before any analysis, run a fixed set of checks. Not to explore the data — to confirm the import did what you expected.

1def import_report(df, name="dataset"):2    print(f"{name}: {df.shape[0]:,} rows x {df.shape[1]} columns")3    print(f"memory: {df.memory_usage(deep=True).sum() / 1e6:.1f} MB")4    print(f"duplicate rows: {df.duplicated().sum():,}")56    summary = pd.DataFrame({7        "dtype": df.dtypes.astype(str),8        "missing": df.isna().sum(),9        "missing_pct": (df.isna().mean() * 100).round(1),10        "unique": df.nunique(),11        "sample": [df[c].dropna().iloc[0] if df[c].notna().any() else None12                   for c in df.columns],13    })14    return summary1516print(import_report(df, "customers"))

Read the output against these questions:

For categorical columns specifically, print the value counts. It takes seconds and catches the trailing-space class of bug immediately:

1for col in df.select_dtypes(include=["object", "string"]).columns:2    if df[col].nunique() < 25:3        print(f"\n{col}:")4        print(df[col].value_counts(dropna=False))

Encoding expectations as assertions

Printing a report only helps if a human reads it. When an import runs on a schedule, encode the checks so failure is loud:

1def validate(df):2    problems = []3    if df["customer_id"].duplicated().any():4        problems.append("duplicate customer_id values")5    if df["amount"].lt(0).any():6        problems.append("negative amounts")7    if df["signup_date"].max() > pd.Timestamp.today():8        problems.append("signup dates in the future")9    expected = {"customer_id", "region", "amount", "signup_date"}10    missing = expected - set(df.columns)11    if missing:12        problems.append(f"missing columns: {sorted(missing)}")1314    if problems:15        raise ValueError("Import validation failed: " + "; ".join(problems))16    return df

A pipeline that fails loudly on bad input is far more valuable than one that always produces a number. Silent success on corrupt data is the expensive outcome.

Automated Profiling

For a first look at an unfamiliar dataset, generated profile reports save time. ydata-profiling produces distributions, correlations, missing-value patterns and warnings in one HTML page:

from ydata_profiling import ProfileReportProfileReport(df, title="Customers", minimal=True).to_file("profile.html")

At the time of writing (September 2026) ydata-profiling 4.18 still requires pandas 2 and Python 3.13 or older, so if your project is on pandas 3, install it in a separate environment and profile a saved copy of the data there.

Use minimal=True on anything above roughly 100,000 rows; the full report computes pairwise correlations and interaction plots, and that scales badly. Treat the report as a way to generate questions, not as an analysis. It will tell you that a column is 62% missing; it cannot tell you whether that is because the field was added last March.

What This Means When You Build Something

Write the import as a function, not as loose cells. Give it explicit dtypes, explicit missing-value markers, explicit date parsing, and a validation step that raises on the conditions you know would invalidate the analysis. Keep the raw file untouched on disk and never edit it by hand — every transformation lives in code, so that re-running the script from the original source reproduces exactly what you had.

1def load_customers(path):2    df = pd.read_csv(3        path,4        dtype={"customer_id": "string", "postcode": "string"},5        parse_dates=["signup_date"],6        na_values=["N/A", "NA", "-", ""],7    )8    df.columns = df.columns.str.strip().str.lower().str.replace(" ", "_")9    for col in df.select_dtypes(include=["object", "string"]).columns:10        df[col] = df[col].str.strip()11    return validate(df)

Those two normalisation lines — stripping and lower-casing column names, stripping whitespace from text values — would have prevented the "North" versus "north " problem entirely. That is the general pattern: most import bugs are not exotic. They are whitespace, types, and assumptions no one wrote down. The fix is to write them down, in code, at the point of entry, where a wrong assumption costs one minute instead of three days.