Pandas · Module 3 of 22 · 5 concepts · about 6 min

Reading Real-World Data

Getting data in from the formats it actually lives in — and declaring what you expect rather than letting Pandas guess.

At work: Loading the exports, spreadsheets, logs and database tables a business actually runs on, correctly the first time.

read_csv, and the arguments that matter

What it is

Beyond the path: dtype declares column types, usecols limits what is read, parse_dates converts date columns, na_values names the strings that mean missing, and chunksize streams the file.

Why it matters

Declaring dtypes up front avoids the upcast-to-object problem entirely, often halves memory, and makes the load reproducible instead of dependent on what happened to be in the first rows.

In the real world: Loading a nightly export of 5 million orders: telling Pandas which columns are dates and which codes mean “missing” avoids a wrong report the next morning.

df = pd.read_csv(
    "orders.csv",
    usecols=["order_id", "customer_id", "total", "ordered_at"],
    dtype={"order_id": "int64", "customer_id": "string"},
    parse_dates=["ordered_at"],
    na_values=["", "N/A", "unknown", "-1"],
)

Reading the whole file to discover the schema, then reading it again with the right dtypes. On a file that barely fits in memory, the first read is the thing that fails.

Practise it: Revenue Per Customer From a Messy CSV

read_excel and its costs

What it is

read_excel reads a sheet by name or position via sheet_name. It needs an engine such as openpyxl and is far slower than CSV or Parquet.

Why it matters

Business data arrives as spreadsheets whether or not you want it to, and knowing sheet_name=None returns every sheet as a dict saves a lot of fumbling.

In the real world: Finance sends the monthly budget as an Excel file with one sheet per department; reading the right sheet is the first step of comparing budget with actual spend.

sheets = pd.read_excel("report.xlsx", sheet_name=None)   # dict of DataFrames
q1 = pd.read_excel("report.xlsx", sheet_name="Q1", dtype={"sku": "string"})

Excel silently coerces things — leading zeros stripped from identifiers, dates reinterpreted by locale. Read identifier columns as strings explicitly rather than trusting what the file claims.

JSON, nested and flat

What it is

read_json handles tabular JSON; pd.json_normalize flattens nested objects into columns, with record_path and meta for lists inside records.

Why it matters

API payloads are nested by default, and normalising them is the step between 'I have the response' and 'I have a table'.

In the real world: A ride-sharing app logs each trip as JSON with a nested driver and a list of stops. Flattening it gives one row per trip for analysis.

payload = [{"id": 1, "user": {"name": "a"}, "events": [{"kind": "click"}]}]
pd.json_normalize(payload, record_path="events", meta=["id", ["user", "name"]])
#     kind  id user.name
# 0  click   1         a

Normalising only the top level, leaving columns that contain dicts or lists. Those become object columns that cannot be grouped, joined or written to Parquet.

Parquet, and why to prefer it

What it is

A columnar binary format that stores dtypes with the data. to_parquet and read_parquet round-trip a frame without a schema guess.

Why it matters

It is dramatically faster and smaller than CSV, reads individual columns without scanning the file, and — most importantly — does not lose types, so a datetime stays a datetime.

In the real world: Data teams store event data as Parquet in cloud storage: files are smaller, load faster and keep their column types, which cuts both time and cost.

df.to_parquet("orders.parquet")
back = pd.read_parquet("orders.parquet", columns=["order_id", "total"])
back.dtypes                # identical to what was written

Using CSV as an intermediate format between pipeline steps. Every hop re-parses and re-guesses types, which is where 'the dates became strings' comes from.

Reading from a database

What it is

pd.read_sql(query, connection) runs a query and returns a frame, with chunksize for results too large to hold at once.

Why it matters

Pushing filters and aggregations into SQL and returning a small frame is nearly always faster than pulling a whole table and doing it in Pandas.

In the real world: Pulling last week's subscriptions straight from the company database for a churn analysis, a chunk at a time so a laptop doesn't run out of memory.

q = (
    "SELECT customer_id, SUM(total) AS spend FROM orders "
    "WHERE status = 'completed' GROUP BY customer_id"
)
spend = pd.read_sql(q, conn)   # aggregate in the database, not in memory

SELECT * on a large table because filtering in Pandas feels more familiar. The transfer is the bottleneck, not the computation.