Exercises — Data loading (CSV, JSON, parquet)

• (a) CSV. It is plain text, opens in any spreadsheet with no library, and 200 rows is tiny so speed/size do not matter. Human-readability wins.
• (b) JSON. APIs speak JSON natively and it is the only listed format that stores nested key/value structure without flattening first.
• (c) Parquet. Columnar layout means column-by-column reads touch only the needed block, and its compression makes 50 GB far smaller on disk. This is the classic analytics/ML workload.

False. CSV is schema-less — every value is stored as text with no type tag. Pandas must infer types by sampling rows and guessing, or you must pass dtype={...} yourself. Parquet, by contrast, stores the schema inside the file.

df = pd.read_csv(
    'data.csv',
    na_values=['', 'NA', 'missing'],  # standardise missing markers to NaN
    on_bad_lines='skip',              # malformed row -> dropped, load survives
    dtype={'zip': str}                # keep leading zeros, no numeric coercion
)

Why each piece: na_values unifies the many ways systems write "no data" into one NaN (see handling missing data); on_bad_lines='skip' prevents a single broken line from aborting a multi-GB load; dtype={'zip': str} stops "01234" from silently becoming the integer 1234.

df = pd.json_normalize(data, sep='_')
# columns are now: name, address_city, address_zip
df = df.rename(columns={'address_city': 'city', 'address_zip': 'zip'})

json_normalize walks the nested keys address -> city and joins them with sep. Using sep='_' (instead of the default .) means the resulting names work with dot-attribute access like df.city. The final rename just drops the address_ prefix for readability. This is a common first step before data cleaning.

total = 0.0
for chunk in pd.read_csv('big.csv', chunksize=100_000):
    total += chunk['score'].sum()  # add this chunk, then discard it

chunksize makes read_csv return an iterator of DataFrames, not one giant DataFrame. Each chunk is loaded, its partial sum added, then garbage-collected before the next chunk arrives — so peak memory is bounded by one chunk, not the whole file.

CSV. Bytes $=\text{rows}\times \text{cols}\times \text{bytes/value}$: $1,000,000\times 10\times 3=3\times 10^7\text{ bytes}=30\text{ MB}.$ JSON. Each value now costs $3+6=9$ bytes: $1,000,000\times 10\times 9=9\times 10^7\text{ bytes}=90\text{ MB}.$ Ratio $=90/30=3\times$. JSON is 3× larger because it repeats the key text ("age":) on every single row, whereas CSV writes column names only once in the header. Compare these numbers to the orange and violet bars in Figure 2 — they are the same 30 MB and 90 MB. This matches the parent note's "JSON overhead ≈ 2–3× CSV."

• (a) $30/6=5$ MB.
• (b) One of ten columns of the compressed file: $5/10=0.5$ MB.
• (c) CSV must scan the whole file to find the column (see the L2 trap and Figure 1): $30$ MB.
• (d) Saving $=\frac{30-0.5}{30}=\frac{29.5}{30}=0.98\overline{3}=98.3\%$.

The lesson: the win comes from two multiplied effects — 6× from compression and 10× from touching one column of ten. That is why Parquet dominates wide analytics tables.

Only 1 — the last group $[2020,2024]$.

For the first three groups the stored max values are $2009,2014,2019$, all $<2020$, so no row in them can satisfy year >= 2020. The reader compares your predicate against the min/max stats and skips those groups entirely — this is predicate pushdown. CSV/JSON have no such stats, so they must read everything and filter afterward in memory.

import pandas as pd
import pyarrow as pa
import pyarrow.parquet as pq
 
writer = None
reader = pd.read_json('events.jsonl', lines=True, chunksize=200_000)
for chunk in reader:                       # (1) bounded memory: JSONL streams line-by-line
    flat = pd.json_normalize(chunk.to_dict('records'), sep='_')  # (2) flatten address_city, ...
    adults = flat[flat['age'] >= 18]       # (3) filter early -> less data downstream
    table = pa.Table.from_pandas(adults)
    if writer is None:
        writer = pq.ParquetWriter('clean.parquet', table.schema)
    writer.write_table(table)              # (4) append each chunk to one Parquet file
if writer is not None:
    writer.close()

Why these choices:

• JSONL, not plain JSON, is essential: a single [...] JSON array must be parsed whole (can't stream), but line-delimited JSON lets chunksize read a bounded number of lines at a time — this is what keeps us under 2 GB.
• Filter inside the loop, so only adults are ever materialised and written; filtering after writing would waste I/O.
• Write Parquet, because the cleaned output feeds an ML pipeline that will read columns repeatedly — the columnar + compressed format pays off on every future read. This output is exactly what feature engineering and TensorFlow/Keras want to consume.

Parquet preserves whatever type it was given at write time. The corruption happened upstream: the data was first read from a CSV with type inference, which saw "007823", guessed "integer," and stored it as 7823 — then it was written to Parquet, faithfully preserving the now-wrong int type.

Fix at the source (the CSV read), not the Parquet read:

df = pd.read_csv('users.csv', dtype={'user_id': str})   # keep it text from the start
df.to_parquet('users.parquet')                          # Parquet now stores str, zeros safe

Moral: Parquet guarantees type persistence, not type correctness. Garbage in, faithfully-preserved garbage out.

The conflict: appends favour a row-oriented, append-friendly format (CSV or JSONL — you just tack a line onto the end). Analytics favours columnar Parquet (reads only 3 of 40 columns, ~$1-3/40=92.5\%$ of column I/O avoided, plus compression). Parquet is write-slow / append-hostile because appending means rewriting or adding row groups; CSV is read-slow for column scans.

Defensible two-tier design:

1. Ingest tier — JSONL (append-only). Each device appends a line; cheap, streaming, no rewrite. This is the "hot" landing zone.
2. Compaction job (once per hour or day) — convert JSONL → Parquet, batching the accumulated lines into row groups. Filter/clean during compaction.
3. Analytics reads Parquet, getting columnar + predicate-pushdown speed on its 3 columns.

This is the standard append-fast, then compact-to-columnar pattern: use each format where its cost model wins. A single-format answer is wrong because no listed format is simultaneously append-cheap and column-read-cheap.

CSV read volume: entire file $=20$ GB. Time $=\frac{20,000\text{ MB}}{170\text{ MB/s}}\approx 117.65$ s.

Parquet read volume: compress $20$ GB by 8× $\Rightarrow 2.5$ GB total; read $3/40$ of it $\Rightarrow 2.5\times \frac{3}{40}=0.1875$ GB. Time $=\frac{187.5\text{ MB}}{170\text{ MB/s}}\approx 1.103$ s.

Speedup $=\frac{117.65}{1.103}\approx 106.7\times$.

Reading 20 GB where you needed 0.19 GB is the tax of the wrong format — over 100× wasted work on a table read every day. The two multiplied effects again: 8× (compression) and 40/3 ≈ 13.3× (column selection), giving roughly $8\times 13.3\approx 106.7\times$.

One-format-fits-all is always wrong here ::: True — each format wins a different cost (append, size, or column-read); real systems combine them. Why usecols on CSV saves memory but not I/O ::: CSV has no file index, so the parser must scan and split every line to find the column, reading all columns off disk regardless. Why JSON is ~3× larger than CSV ::: JSON repeats the key text on every row; CSV writes column names only once in the header. Where do you fix leading-zero corruption ::: At the CSV read with dtype={'col': str} — upstream of Parquet, which only preserves the type it was handed. The two multiplied effects behind Parquet's column-read speed ::: compression factor × (total columns / columns needed), plus predicate pushdown skipping row groups.