Worked examples — Data loading (CSV, JSON, parquet)

This page is the "hands dirty" companion to Data loading (CSV, JSON, parquet). The parent told you what CSV, JSON, and Parquet are and why their shapes differ. Here we walk through every kind of situation a data-loading task can throw at you — clean files, dirty files, empty files, nested files, huge files, and the exam-style trick questions — and solve each one from scratch.

Before we start, one plain-words reminder of the three "shipping containers":

If any of those words feels shaky, read the parent first, then come back. Everything below assumes you can read those definitions but have never applied them.

The scenario matrix

Every data-loading problem lives in one of the cells below. Our goal is to hit every single cell with a worked example, so no situation surprises you later.

The "signs and quadrants" of an angle problem become, for data loading, these ten cells: clean vs dirty, small vs huge, flat vs nested, row vs column. We cover them all.

The worked examples

Cell A — Clean CSV

Step 1 — Load the file.

import pandas as pd
df = pd.read_csv('students.csv')

Why this step? read_csv reads the first line as the header (column names) and every following line as a row. It infers types by sampling: it sees 25, 30, 22 and guesses int; it sees 92.5, ... and guesses float.

Step 2 — Compute the mean.

avg = df['score'].mean()

Why this step? df['score'] grabs that one column as a series of numbers; .mean() sums them and divides by the count $n=3$.

The average is the sum of the three scores divided by how many there are: avg = (92.5 + 87.3 + 78.0) / 3 = 257.8 / 3 = 85.933...

The three dots (...) mean the digit 3 repeats forever: 85.9333333... — the division never terminates.

Verify. Units: all three are "score points", so the average is also "score points" — consistent. Sanity: the answer 85.93 sits between the smallest (78.0) and largest (92.5) values. An average must always land inside that range, so we're safe.

Cell B — Type ambiguity

Step 1 — See the failure first.

df = pd.read_csv('addresses.csv')
print(df['zip'].iloc[0])   # -> 1234   (leading zero eaten!)

Why this step? CSV is schema-less (parent definition). Pandas sees digits, guesses int, and the number 01234 becomes plain 1234. The string identity is destroyed silently — no error, no warning. This is the "type ambiguity" trap the parent's mistake callout named.

Step 2 — Force the type.

df = pd.read_csv('addresses.csv', dtype={'zip': str})
print(df['zip'].iloc[0])   # -> '01234'

Why this step? dtype={'zip': str} tells Pandas: do not guess this column, read it as text. This also skips the inference pass for that column (the parent noted pre-specifying types saves a scan).

Verify. Check the character count: len(df['zip'].iloc[0]) == 5. A number can't have a length; a five-character string can. The leading 0 survives. ✅

Cell C — Dirty CSV

Step 1 — Declare what "missing" means.

import pandas as pd
df = pd.read_csv('messy.csv',
                 na_values=['', 'NA', 'null'],
                 on_bad_lines='skip')   # requires pandas >= 1.3

Why this step? Different systems write missing data differently — empty string, NA, null. na_values says "any of these becomes a real NaN." on_bad_lines='skip' tells Pandas: if a row has the wrong number of columns (Carol's 4 fields), drop that row instead of throwing an error that kills the whole load.

Step 2 — Count who survived.

• Alice: clean → kept.
• Bob: blank age → NaN, NA score → NaN. Row is kept (missing values are fine, just empty).
• Carol: 4 fields vs 3 expected → skipped by on_bad_lines.
• Dave: null age → NaN. Kept.

So 3 rows survive: Alice, Bob, Dave.

Why this step? We separate two different failures: bad values (fixable → NaN) versus bad structure (wrong field count → skip the row). Only structural errors cost you a whole row.

Verify. len(df) == 3. Count the NaNs: Bob contributes 2 (age, score), Dave contributes 1 (age) → 3 missing cells total. df.isna().sum().sum() == 3. Handing this cleaned frame onward is exactly the job of 1.5.01-Handling-missing-data.

Cell D — Degenerate inputs (zero data)

Step 1 — Header-only file.

import pandas as pd
df = pd.read_csv('header_only.csv')
print(df.shape)   # -> (0, 3)

Why this step? One line exists, and it's the header. Pandas records 3 columns but 0 rows. This is a valid, usable DataFrame — like an empty table with labelled columns. .mean() on it returns NaN (dividing by zero rows), not a crash.

Step 2 — Truly empty file.

try:
    df = pd.read_csv('truly_empty.csv')
except pd.errors.EmptyDataError:
    df = pd.DataFrame()   # fall back to an empty frame
print(df.shape)   # -> (0, 0)

Why this step? With zero bytes there is no header to name columns, so Pandas raises EmptyDataError. The safe pattern is to catch it and substitute an empty frame with shape (0, 0).

Verify. Header-only → shape (0, 3): rows = 0, columns = 3. Truly empty → shape (0, 0). The distinction matters for concatenation: a (0, 3) frame already has the right column names, so pd.concat([header_df, real_df]) lines up cleanly with no extra work. A (0, 0) frame has no columns, so a plain concat would leave you with mismatched columns — you'd need ignore_index=True or a reindex to the target columns first. Both can be concatenated; the (0, 3) case just needs no fixing, while the (0, 0) case needs one extra alignment step.

Cell E — Limiting case: file bigger than RAM

Step 1 — Show that a full load is impossible. Using the decimal MB from the note above, full memory = 2,000,000,000 rows * 100 bytes = 2e11 bytes = 200,000 MB = 200 GB. The machine has 500 MB free. Since 200,000 MB is 400x larger than 500 MB, a plain pd.read_csv('huge.csv') would run out of memory and crash. This is a genuine "file bigger than RAM" scenario — we have no choice but to stream it. (The parent's rule memory ∝ rows loaded is exactly what forces this.)

Step 2 — Read in chunks and accumulate.

import pandas as pd
total = 0.0
for chunk in pd.read_csv('huge.csv', chunksize=100_000):
    total += chunk['amount'].sum()   # add this piece, then discard chunk

Why this step? chunksize=100_000 returns an iterator: each loop grabs 100k rows, we add their sum to a running total, then Python throws the chunk away before the next loop. Peak memory is one chunk: 100,000 * 100 bytes = 10,000,000 bytes = 10 MB at any instant. 10 MB fits easily inside 500 MB, so the two-billion-row job runs on a laptop.

Step 3 — Why a sum survives chunking. Summing is associative: (a + b) + c = a + (b + c). So splitting the file into groups and adding the group-totals gives the exact same answer as adding everything at once. That's why we can stream it.

Figure — read left to right. The diagram below has two halves. On the left stands the whole huge.csv file as a tall cyan stack labelled "2,000,000,000 rows"; the amber arrow reminds you that loading it all at once would demand ~200 GB — far beyond the 500 MB of RAM. On the right, five small white boxes labelled "chunk 1 … chunk 5" feed one at a time (cyan arrow, "one at a time") into a single amber box labelled "buffer 10 MB". Only one chunk is ever inside that buffer: the caption "sum += chunk, then discard" shows each chunk is added to the running total and immediately dropped before the next arrives. The whole picture is the claim in one image — a 200 GB job done inside 10 MB of working memory.

Verify. Suppose every amount is 1.0. Then the chunked total = 2,000,000,000 * 1.0 = 2,000,000,000, and 20,000 chunks of 100,000 each contribute 100,000: 20,000 * 100,000 = 2,000,000,000. ✅ Peak memory 10 MB << 200,000 MB full load — the file never has to fit in RAM at all.

Cell F — Nested JSON

Step 1 — Load the JSON text into Python.

import json, pandas as pd
with open('users.json') as f:
    data = json.load(f)

Why this step? json.load turns the text into a Python list of dicts. The address value is itself a dict — that nesting is exactly what a flat table can't hold directly.

Step 2 — Normalize (flatten).

df = pd.json_normalize(data, sep='_')
# columns: name, address_city, address_zip

Why this step? json_normalize walks each nested key path — name, address.city, address.zip — and lays them side by side as columns. sep='_' joins the path with underscores so we get address_city (not address.city).

Step 3 — Why the separator matters (precisely). Pandas gives you two ways to reach a column:

• Bracket access — df['address.city'] — always works, whatever the name.
• Attribute access — df.address_city — a convenience shortcut where the column name is written after a dot, like a property.

The danger is only with attribute access. If the column were literally named address.city and you wrote df.address.city, Python parses that as two dotted steps: first df.address (Pandas looks for a column or attribute named exactly address — which does not exist here, raising AttributeError), then .city on whatever that returned. Python has no way to know the dot was "part of the name" versus "a new access step." Renaming with an underscore removes the ambiguity: df.address_city is one unbroken identifier, so attribute access reads it as a single column name. (Bracket access df['address.city'] never had this problem — but the readable df.address_city shortcut only exists once the dot is gone.)

Verify. Count columns: name (1) + address_city (2) + address_zip (3) = 3 columns, 2 rows → shape (2, 3). The nesting depth of 2 became a flat width of 3. This flattened frame feeds naturally into 1.4.06-Pandas-DataFrames-and-operations.

Cell G — Streaming JSON (JSONL / NDJSON)

Step 1 — Load line-delimited JSON.

import pandas as pd
df = pd.read_json('events.jsonl', lines=True)

Why this step? lines=True tells Pandas each line is its own object, not that the file is one giant array. A normal [ ... ] array must be complete and closed to be valid; JSONL never needs a closing bracket, so you can append a new line at any time and it stays valid.

Step 2 — Sum the clicks.

total = df['clicks'].sum()   # 3 + 7 + 2

Why this step? Same associative-sum logic as chunking (Ex 5) — JSONL is streaming friendly precisely because you can process one line at a time.

Verify. 3 + 7 + 2 = 12. Sanity: total must be >= the largest single value (7) and <= 3 * max = 21. 12 sits in that band. ✅

Cell H — Columnar win (Parquet size & speed)

Step 1 — Row-store cost. In a row format the bytes of one row sit together, so to reach every age value you must pass through the whole row: bytes_row = 10,000,000 rows * 100 cols * 8 bytes = 8e9 bytes = 8 GB. Why this step? You physically read all 100 columns even though 99 get discarded — that's the parent's "read N * row_size, discard N-1 columns."

Step 2 — Columnar cost. Parquet stores the age column as one contiguous block, so you read only it: bytes_col = 10,000,000 rows * 1 col * 8 bytes = 8e7 bytes = 0.08 GB.

Step 3 — The saving. fraction read = bytes_col / bytes_row = 1 / 100 = 0.01 = 1%. saving = 1 - 0.01 = 0.99 = 99%.

Figure — read left to right. The diagram shows the two layouts side by side. On the left is the row store: a grid of cells where each row's values sit together. The age column is the single cyan-filled column; the other 99 columns are white outlines. To collect every age value you must sweep across all rows and step over the white cells you do not want — the amber note "read ALL 100 cols (discard 99)" marks this waste. On the right is the columnar store: the age block is one contiguous tall cyan strip you read directly, while the other column blocks (white outlines) are never touched — the amber note "read only 1 col = 1% of bytes (99% saved)" states the payoff. For wide tables this is the parent's "savings → 90%+" made concrete.

Verify. With 100 columns and needing 1, the fraction must be 1/100 by symmetry — that's the whole point of "wide tables → savings → 90%+" in the parent. 8 GB * 0.01 = 0.08 GB matches Step 2. ✅ This is why analytics and 3.1.02-TensorFlow-and-Keras-basics pipelines store features in Parquet.

Cell I — Predicate pushdown

Step 1 — Ask the metadata first.

import pandas as pd
df = pd.read_parquet('data.parquet', filters=[('age', '>', 25)])

Why this step? Before touching data, Parquet checks each row group's stored max. If a group's max age = 25, then no row in it can satisfy age > 25. The engine skips that whole group without decompressing it — this is predicate pushdown: the filter (the "predicate") is pushed down into the read, instead of applied afterwards in memory.

Step 2 — Count the reads.

• 6 groups have max = 25 → all skipped → 0 rows read from them.
• The remaining 4 groups might contain matches → they get read: 4 * 100,000 = 400,000 rows scanned.

rows read = 4 * 100,000 = 400,000.

Step 3 — Contrast with CSV. A CSV has no per-block statistics. To apply age > 25 you must read all 1,000,000 rows, then filter in memory. Parquet read 400,000; CSV read 1,000,000.

Verify. Fraction skipped = 6/10 = 0.6; fraction read = 4/10 = 0.4; 0.4 * 1,000,000 = 400,000. ✅ Note we still read the 4 candidate groups fully — pushdown skips whole groups, it does not skip individual rows inside a kept group. Cleaning what remains is the job of 1.4.08-Data-cleaning-and-preprocessing.

Cell J — Exam twist: choose the format

Step 1 — Case 1 → CSV. Why? Tiny, must be human-readable, must open in Excel with no special library. Type safety and size are irrelevant at 50 rows. CSV's weaknesses (slow, big, type-ambiguous) don't bite at this scale, and its strengths (universal, plain text) win.

Step 2 — Case 2 → JSON. Why? The data is hierarchical — nested settings and a list of friends. Only JSON natively represents boxes-inside-boxes (the parent's comparison table: JSON is the only "Yes" for nested data). CSV would need painful flattening; Parquet is overkill for a single request.

Step 3 — Case 3 → Parquet. Why? Huge (500 GB), read-heavy, and touches only 3/200 = 1.5% of columns per read. Columnar layout + compression = the smallest size and fastest column reads (Ex 8's 99% saving logic). This is the exact use case in the parent's comparison row.

Verify. Cross-check against the parent's decision table:

• Human-readable interchange → CSV. ✅
• Nested / API data → JSON. ✅
• Analytics / ML, read-heavy, wide → Parquet. ✅ The fraction 3/200 = 0.015 = 1.5% confirms Case 3 is column-sparse, exactly where Parquet dominates. Whatever format you pick then flows into 1.4.08-Data-cleaning-and-preprocessing and 2.3.05-Feature-engineeringtechniques.

Recap

Prefer to read this in Hindi-English? See the Hinglish version →. For the array and DataFrame mechanics underneath, revisit 1.4.05-NumPy-arrays-and-operations and 1.4.06-Pandas-DataFrames-and-operations.