Data loading (CSV, JSON, parquet)

What are these formats?

Definition CSV (Comma-Separated Values)

A text-based format where each line represents a row, and values within a row are separated by a delimiter (usually comma). Schema-less: column types must be inferred or specified manually.

Structure:

name,age,score
Alice,25,92.5
Bob,30,87.3

Definition JSON (JavaScript Object Notation)

A hierarchical text format using key-value pairs and arrays. Self-describing: keys carry meaning. Supports nested structures. Human-readable but verbose.

Structure:

[
 {"name": "Alice", "age": 25, "score": 92.5},
 {"name": "Bob", "age": 30, "score": 87.3}
]

Figure — Data loading (CSV, JSON, parquet)

How to load each format

CSV loading — from first principles

Why CSV is row-oriented: When you write Alice,25,92.5, the computer reads left-to-right, one row at a time. Each row is independent. To get "all ages", the parser must scan every row.

Formula Pandas CSV loading

import pandas as pd
 
# Basic load
df = pd.read_csv('data.csv')
# With explicit types (avoids inference overhead)
df = pd.read_csv('data.csv', 
                dtype={'age': int, 'score': float},
                parse_dates=['timestamp'])
# For huge files: chunked reading
for chunk in pd.read_csv('huge.csv', chunksize=1000):
   process(chunk)  # memory stays bounded

Derivation of chunked reading:

Memory usage∝ rows loaded × row size
If file has 1M rows, loading all → 1M × row_size memory
Loading 10k at a time → max 10k × row_size memory
Process each chunk, discard → constant memory footprint

Key parameters WHY:

dtype: Pre-specifying types skips Pandas' inference (which reads a sample, guesses, then reads again)—saves one full pass through data
parse_dates: Strings like "2023-01-01" stay as strings unless parsed—comparisons fail, sorting breaks
chunksize: Returns iterator, not DataFrame—enables processing datasets larger than RAM

Worked example CSV loading with error handling

Scenario: Real-world CSV with missing values and bad rows.

df = pd.read_csv('mesy.csv',
                na_values=['', 'NA', 'null '?'],  # what counts as missing
                keep_default_na=True,
                on_bad_lines='skip')  # or 'warn' or 'error'

Why this step?

na_values: Different systems encode missing data differently—standardize on NaN
on_bad_lines='skip': A single malformed row (wrong column count) would crash entire load—skip allows recovery

JSON loading — hierarchical access

Why JSON is different: JSON can nest: {"user": {"name": "Alice", "prefs": {"theme": "dark"}}. This isn't a flat table. Pandas must flatten or preserve structure.

Formula Pandas JSON loading

# Flat JSON (list of records)
df = pd.read_json('data.json', orient='records')
 
# Nested JSON → normalize (flatten)
import json
with open('nested.json') as f:
   data = json.load(f)
df = pd.json_normalize(data, sep='_')
# For huge files: line-delimited JSON (JSONL/NDJSON)
df = pd.read_json('data.jsonl', lines=True)

Derivation of normalization:

Input: {"user": {"name": "Alice", "age": 25}, "score": 92}

Flatten with sep='_':
- Walk nested keys: user.name, user.age, score
- Replace '.' with '_': user_name, user_age, score
- Result: flat dict → DataFrame row

Orient options WHY:

orient='records': Each JSON object → one row (default for arrays of objects)
orient='index': Keys are row labels, values are row data
lines=True: Each line is a separate JSON object (JSONL format—streaming friendly, append-able)

Worked example JSON normalization

Scenario: API returns nested user data.

data = [
   {"name": "Alice", "address": {"city": "NYC", "zip": "10001"},
   {"name": "Bob", "address": {"city": "LA", "zip": "9001"}}
]
df = pd.json_normalize(data)
# Result columns: name, address.city, address.zip
# Rename for clarity
df.columns = df.columns.str.replace('.', '_')

Why this step?

json_normalize creates multi-level column names with dots—valid but awkward
Replacing with underscores makes df.address_city work (dot notation fails with dots in names)

Parquet loading — columnar efficiency

Why parquet is columnar: Traditional row format: [Alice|25|92.5][Bob|30|87.3]—reading "all ages" means parsing full rows. Columnar: [names: Alice,Bob][ages: 25,30][scores: 92.5,87.3]—reading "all ages" touches only the ages block.

Compression benefit: Column data is homogeneous (all ints, all floats)—compresses better than mixed-type rows.

Formula Pandas Parquet loading

# Requires pyarrow or fastparquet engine
df = pd.read_parquet('data.parquet')
 
# Load specific columns (columnar win!)
df = pd.read_parquet('data.parquet', columns=['age', 'score'])
 
# Filter during read (predicate pushdown)
df = pd.read_parquet('data.parquet', 
                    filters=[('age', '>', 25)])

Derivation of columnar read savings:

Row format:
- To read 1 column from N rows: read N × row_size bytes, discard N-1 columns
Columnar format:
- To read 1 column from N rows: read 1 × column_size bytes
- Saving = (N × row_size - column_size) / (N × row_size)
- For wide tables (many columns): savings → ~90%+

Filters WHY:

filters=[('age', '>', 25)]: Parquet stores min/max stats per row group—can skip entire row groups without reading data (predicate pushdown)
Reading filtered data from CSV/JSON: must read entire file, then filter in memory

Worked example Parquet vs CSV speed comparison

Scenario: Load 1 column from 10GB dataset with 100 columns.

import time
 
# CSV: must read entire file
t0 = time.time()
df_csv = pd.read_csv('data.csv', usecols=['age'])
csv_time = time.time() - t0  # ~120 seconds
 
# Parquet: read only 'age' column
t0 = time.time()
df_pq = pd.read_parquet('data.parquet', columns=['age'])
pq_time = time.time() - t0  # ~3 seconds
 
print(f"Speedup: {csv_time / pq_time:.1f}x")  # ~40x

Why this step?

CSV is text: must parse all100 columns to find 'age', even with usecols—file format has no index
Parquet stores column locations in metadata—direct seek to 'age' block, decompress only that

Format comparison — when to use what

| Format | Read Speed | Write Speed | Size | Nested Data | Type Safety | Use Case | |---------|------------|-------------|------|-------------|-------------| | CSV | Slow | Fast | Large| No | No (infer) | Interchange, human-readable | | JSON | Slow | Medium | Largest| Yes | Partial | APIs, hierarchical configs | | Parquet | Fast | Slow | Smallest| Partial | Yes | Analytics, archival, ML pipelines |

Derivation of size differences (example: 1M rows, 10 columns):

CSV: 
- Each value as text: "25" = 2 bytes + delimiter
- 1M × 10 × 3 bytes avg≈ 30MB uncompressed

JSON:
- Includes keys every row: {"age": 25} vs25
- Overhead ≈ 2-3× CSV
- 1M × 10 × 9 bytes ≈ 90MB uncompressed

Parquet:
- Columnar compression: RLE, dictionary encoding, bit-packing
- Typical: 5-10× smaller than CSV
- Same data≈ 3-6MB

Common mistake Common mistake: Loading entire file when you need one column

The wrong approach:

df = pd.read_csv('huge.csv')  # loads all 100 columns
ages = df['age']  # use only 1 column

Why it feels right: Simple, clear intent.

The actual problem: Memory usage100× larger than needed. For 10GB file, this allocates 10GB RAM.

The fix:

# CSV: specify columns upfront
df = pd.read_csv('huge.csv', usecols=['age'])  # still reads full file, but loads only 1 column
# Parquet: columnar read (best)
df = pd.read_parquet('huge.parquet', columns=['age'])  # reads only 'age' from disk

Writing data

Formula Writing each format

# CSV: simple, portable
df.to_csv('output.csv', index=False)  # index=False avoids unnamed column
 
# JSON: records orient for tabular data
df.to_json('output.json', orient='records', indent=2)
 
# Parquet: best for archival / reuse
df.to_parquet('output.parquet', 
             engine='pyarrow',
             compression='snappy')  # fast compression

Compression options WHY:

snappy: Fast compression/decompression, moderate ratio (~2-3×)
gzip: Slower, better ratio (~5-10×)—use for archival
None: No compression—only if downstream tool doesn't support compressed Parquet

Worked example Partitioned Parquet for huge datasets

Scenario: 100GB dataset, want to read only data from 2024.

# Write partitioned by year
df.to_parquet('data/', 
             partition_cols=['year'],
             engine='pyarrow')
 
# Creates: data/year=2023/.., data/year=2024/...
# Read only 2024 (massive I/O savings)
df_2024 = pd.read_parquet('data/', filters=[('year', '==', 2024)])

Why this step?

Partitioning stores different values in separate directories
Reading with filter → only opens 2024 directory → skips 99% of data
Essential for data lakes, time-series analysis

Performance tips — derived from format internals

CSV: Use dtype parameter—inference scans twice, explicit types scan once
JSON: Prefer JSONL (lines=True) for streaming—allows processing without loading entire array
Parquet: Use columns parameter always—columnar format makes this nearly free
Large files: Chunk CSV, use Parquet row groups, or switch to Dask/Polars for parallel loading
Repeated access: Convert CSV → Parquet once, save 10-50× on subsequent reads

Derivation of CSV dtype speedup:

Without dtype:
1. Read 1000 sample rows → infer types (string? int? float?)
2. Seek to start
3. Read all rows, parse with inferred types
Total: 1 + N passes

With dtype={'age': int}:
1. Read all rows, parse with known types
Total: N passes

Speedup = (1 + N) / N → ~2× for large N (one less full read)

Recall Feynman explanation

Imagine you're moving to a new house. You have three ways to pack: CSV is like throwing everything into cardboard boxes with a label on top. Easy to pack, anyone can open a box and see what's inside by reading the label. But if you want to find all your books, you have to open every single box and dig through.

JSON is like using clear plastic bins with detailed labels on each item inside. You can see the structure—"this bin has books, and inside the book section there's a Harry Potter subsection." Great if things have natural groups, but takes more space because of all the labels.

Parquet is like a professional moving company that sorts everything by type into a warehouse. All books in one section, all clothes in another, all compressed into vacuum bags. Finding "all books" is instant—you just go to the book section. But you need special tools to pack and unpack it (can't just open it with a notepad like CSV).

When loading data, you're unpacking these boxes. CSV makes you unpack everything even if you want one thing. Parquet lets you grab just the section you need.

Connections

1.4.05-NumPy-arrays-and-operations — Parquet stores NumPy arrays efficiently
1.4.06-Pandas-DataFrames-and-operations — All loading produces DataFrames
1.5.01-Handling-missing-data — CSV/JSON require explicit NA handling
1.4.08-Data-cleaning-and-preprocessing — Loading is step1, cleaning is step 2
2.3.05-Feature-engineeringtechniques — Parquet preserves feature types across pipeline steps
3.1.02-TensorFlow-and-Keras-basics — TensorFlow can read Parquet directly with tf.data

#flashcards/ai-ml

What is the key structural difference between CSV and Parquet? :: CSV is row-oriented (each line = one record), Parquet is columnar (each column stored together). Reading one column from Parquet is fast; from CSV requires scanning all rows.

Why does Parquet load faster than CSV for specific columns?

Parquet stores separately with metadata indicating location. Reading one column = seeking to that column's blocks. CSV must parse entire rows to extract one column.

What does pd.read_csv('file.csv', dtype={'age': int}) optimize?

Skips Pandas' type inference step (which reads a sample, guesses types, then re-reads). Explicitly providing types eliminates one full file scan, ~2× speedup.

When should you use chunksize parameter in read_csv?

When the file is too large to fit in memory. chunksize=10000 loads 10k rows at a time, process each chunk, memory stays bounded regardless of file size.

What does pd.json_normalize(data) do?

Flattens nested JSON into a flat DataFrame. Converts {"user": {"name": "Alice"}} into columns like user.name or user_name (with sep parameter).

Why is JSON larger than CSV for tabular data?

JSON includes key names in every record: {"age": 25} vs CSV's 25. Overhead is ~2-3× for key repetition plus bracket/brace syntax.

What is predicate pushdown in Parquet?

Using filters like filters=[('age', '>', 25)] during read. Parquet row groups store min/max stats—can skip entire groups without reading data. Not possible in CSV (must read all, then filter).

Why use orient='records' for JSON?

Matches the common API format: array of objects [{...}, {...}]. Each object becomes one DataFrame row. Default for tabular JSON.

What does lines=True mean in read_json?

JSON Lines format (JSONL/NDJSON): each line is a separate JSON object, not wrapped in array. Enables streaming (don't load entire array), append-able (just add lines).

When should you convert CSV to Parquet?

When you'll read the data multiple times, especially selecting specific columns. Initial conversion takes time, but subsequent reads are 10-50× faster. Essential for ML pipelines.

What compression should you use for Parquet archival?

compression='gzip' for maximum compression (~5-10× smaller) when read speed is not critical. Use compression='snappy' (default) for balanced speed and size (~2-3× smaller).

Why does Parquet preserve data types but CSV doesn't?

Parquet stores schema in file metadata (column names, types, encoding). CSV is plain text—types must be inferred from content (ambiguous: "01234" could be int or string).

What is partitioned Parquet?

Splitting data into directories by column values: data/year=2024/, data/year=2025/. Reading with filters=[('year', '==', 2024)] only opens 2024 directory, skips rest—massive I/O savings for time-series or categorical grouping.

Why does usecols in read_csv still read the whole file?

CSV has no index or metadata. Parser must scan every row to find column positions, even if discarding most columns. Only memory savings, not I/O savings. Parquet's columnar structure enables true I/O skipping.

Concept Map

Hinglish (regional understanding)

Intuition Hinglish mein samjho

Data loading ka matlab hai ki ap apne analysis ya ML model ke liye external files se data ko memoryein late hain. Teen popular formats hain: CSV (sabse common, text-based), JSON (nested structure ke liye, APIs se ata hai), aur Parquet (analytics ke liye best, binary columnar format).

CSV simple hai—har line ek row, comma separate values. Lekin iska problem hai: types preserve nahi hote (sab kuch string jaisa dikhta hai initially), aur badi files ke liye slow hai kyunki row-by-row parse karna padta hai. Agar aapko sirf ek column chahiye (jaise sirf "age"), tab bhi puri file scan hogi. JSON hierarchical data handle kar sakta hai (nested objects, arrays), but size bahut bada ho jata hai kyunki har record mein keys repeat hotiain. APIs se data aata hai toh JSON natural choice hai.

Parquet ekdum different approach leta hai—yeh data ko columns mein store karta hai, rows mein nahi. Matlab agar 100 columns hain aur apko sirf "age" chahiye, toh sirf "age" wala block disk se read hoga, baki 99 skip. Isse speed10-50× badh jati hai badi files ke liye. Compression bhi bahut better hai (5-10× chhoti file). ML pipelines ya repeated analysis ke liye Parquet perfect hai—ek baar CSV se convert karo, phir hamesha fast load hoga.

Practical tip: Data aaye CSV mein (interchange easy hai), butagar baar-baar analysis karna hai toh turant Parquet mein convert kar lo. JSON use karo jab structure complex ho (config files, nested API responses). Parquet use karo jab performance critical ho—especially large datasets ya cloud storage (S3, GCS) pe, kyunki yeh selective column reads

What are these formats?

How to load each format

CSV loading — from first principles

JSON loading — hierarchical access

Parquet loading — columnar efficiency

Format comparison — when to use what

Writing data

Performance tips — derived from format internals

Connections

Concept Map

Hinglish (regional understanding)

Go deeper — visual, from zero

Test yourself — Python & Scientific Computing

Connections