TECH **Python for Data Science: Reading/Writing CSV, JSON, Excel, Parquet with Pandas**

Python for Data Science: Reading/Writing CSV, JSON, Excel, Parquet with Pandas

A hyper-practical, zero-fluff guide for real projects and certifications (PL-300, DP-100, etc.)

1. What This Is & Why It Matters

You’re a data scientist or analyst. Your job isn’t just to build models—it’s to move data efficiently. Whether you’re: - Ingesting a 50GB CSV from a client, - Cleaning messy Excel files from business teams, - Storing processed data in a format that Spark can read in milliseconds, - Sharing results with stakeholders in JSON or Excel,

…you will use Pandas for I/O. If you don’t master this, you’ll waste hours debugging encoding errors, memory crashes, or slow reads/writes.

Real-world scenario:
You inherit a legacy ETL pipeline that reads 100+ CSV files, merges them, and writes to Excel. The script runs for 2 hours and crashes. Your boss says, "Fix it by EOD." This guide is your lifeline.

2. Core Concepts & Components

1. pd.read_csv() / df.to_csv()

• Definition: Read/write tabular data from/to CSV files.
• Production insight: CSV is the "lingua franca" of data exchange, but it’s slow and memory-heavy for large files. Use chunksize or switch to Parquet for big data.

2. pd.read_json() / df.to_json()

• Definition: Read/write JSON (nested, semi-structured data).
• Production insight: JSON is great for APIs and NoSQL, but schema drift (changing field names) will break your pipeline. Validate with json_normalize() or pydantic.

3. pd.read_excel() / df.to_excel()

• Definition: Read/write Excel files (.xlsx, .xls).
• Production insight: Excel is business-critical but fragile. Always use openpyxl or xlrd as engines, and never trust merged cells—they’ll corrupt your data.

4. pd.read_parquet() / df.to_parquet()

• Definition: Read/write Parquet (columnar, compressed, fast for analytics).
• Production insight: Parquet is 10x faster than CSV for big data (Spark, Dask, BigQuery). Use pyarrow or fastparquet as engines.

5. chunksize (CSV/JSON)

• Definition: Process large files in batches to avoid memory crashes.
• Production insight: If your script crashes on a 10GB CSV, chunksize=100_000 is your first fix.

6. dtype (CSV/Parquet)

• Definition: Explicitly set column data types to save memory.
• Production insight: df['id'] = df['id'].astype('int32') can halve memory usage for large datasets.

7. orient (JSON)

• Definition: Controls JSON structure ('records', 'split', 'index').
• Production insight: 'records' is the most common (list of dicts), but 'split' is faster for large DataFrames.

8. engine (Excel/Parquet)

• Definition: Backend library for reading/writing (e.g., openpyxl for Excel, pyarrow for Parquet).
• Production insight: pyarrow is faster than fastparquet for Parquet, but may not support all features.

9. compression (CSV/Parquet)

• Definition: Compress files to save disk space ('gzip', 'bz2', 'zip').
• Production insight: df.to_csv('data.csv.gz', compression='gzip') reduces file size by 70-90%.

10. index_col (CSV/Parquet)

• Definition: Set a column as the DataFrame index.
• Production insight: If you don’t set index_col=False, Pandas will auto-create an index, which can cause merge issues later.

3. Step-by-Step Hands-On

Task: Build a data pipeline that:

1. Reads a 1GB CSV (simulated).
2. Cleans it (drop NA, convert dtypes).
3. Writes to Parquet (for analytics) and Excel (for business users).

Prerequisites:

• Python 3.8+
• Pandas (pip install pandas pyarrow openpyxl)
• A sample CSV (or generate one with df = pd.DataFrame(np.random.rand(10_000_000, 5), columns=list('ABCDE')))

Step 1: Read CSV Efficiently

import pandas as pd

# Read in chunks to avoid memory crash
chunk_iter = pd.read_csv(
    "large_data.csv",
    chunksize=100_000,  # Process 100K rows at a time
    dtype={"id": "int32", "value": "float32"},  # Save memory
    parse_dates=["timestamp"],  # Auto-convert to datetime
    na_values=["NA", "NULL", ""]  # Standardize missing values
)

# Process each chunk
for i, chunk in enumerate(chunk_iter):
    print(f"Processing chunk {i}...")
    # Do something with chunk (e.g., clean, filter)
    chunk = chunk.dropna()

Why this works:
- chunksize prevents memory crashes.
- dtype reduces memory usage.
- na_values standardizes missing data.

Step 2: Write to Parquet (for Analytics)

# After processing all chunks, combine into one DataFrame
df = pd.concat([chunk for chunk in chunk_iter])

# Write to Parquet (fast, compressed, columnar)
df.to_parquet(
    "processed_data.parquet",
    engine="pyarrow",  # Faster than fastparquet
    compression="snappy",  # Good balance of speed/size
    index=False  # Don't write index (saves space)
)

Verify:

ls -lh processed_data.parquet  # Check file size (should be ~10-20% of CSV)

Production insight:
- Parquet is 10x faster to read than CSV in Spark/BigQuery.
- snappy compression is faster than gzip (but slightly larger).

Step 3: Write to Excel (for Business Users)

# Write to Excel (with multiple sheets)
with pd.ExcelWriter(
    "report.xlsx",
    engine="openpyxl",  # Required for .xlsx
    mode="w"  # Overwrite if exists
) as writer:
    df.head(1000).to_excel(writer, sheet_name="Summary", index=False)  # First 1K rows
    df.describe().to_excel(writer, sheet_name="Stats")  # Summary stats

Verify:

open report.xlsx  # Check sheets

Production insight:
- Excel is not for big data (max ~1M rows).
- openpyxl is slower than xlsxwriter but supports more features.

Step 4: Read JSON (API Data)

# Simulate API response
api_data = [
    {"id": 1, "name": "Alice", "scores": [85, 90]},
    {"id": 2, "name": "Bob", "scores": [75, 80]}
]

# Write to JSON
pd.DataFrame(api_data).to_json(
    "api_data.json",
    orient="records",  # List of dicts (common for APIs)
    date_format="iso"  # ISO 8601 timestamps
)

# Read back
df_json = pd.read_json("api_data.json", orient="records")
print(df_json)

Output:

   id   name    scores
0   1  Alice  [85, 90]
1   2    Bob  [75, 80]

Production insight:
- orient="records" is most common for APIs.
- Use json_normalize() to flatten nested JSON.

4. ? Production-Ready Best Practices

Performance

• For large CSVs: Use chunksize + dtype to avoid memory crashes.
• For big data: Always use Parquet (not CSV/Excel).
• For Excel: Use xlsxwriter for writing, openpyxl for reading/modifying.

Reliability

• Validate schemas: Use pydantic or df.dtypes to check column types.
• Handle missing data: Use na_values in read_csv() to standardize NAs.
• Compress files: Use compression="gzip" for CSVs, snappy for Parquet.

Maintainability

• Document dtypes: Store dtype mappings in a config file.
• Use index=False: Avoid auto-generated indices in output files.
• Log progress: Print chunk numbers when processing large files.

Security

• Sanitize inputs: Never trust user-uploaded files (validate extensions, scan for malware).
• Avoid Excel macros: .xlsx is safe; .xlsm can contain malicious VBA.

5. ⚠️ Common Mistakes & Traps

6. ? Exam/Certification Focus

Typical Question Patterns

1. "Which format is best for big data?"
2. Answer: Parquet (columnar, compressed, fast for analytics).

3. Trap: CSV is not scalable.

4. "How do you read a 10GB CSV without crashing?"

5. Answer: pd.read_csv(..., chunksize=100_000).

6. Trap: df = pd.read_csv("big.csv") will crash.

7. "What’s the fastest way to write a DataFrame to disk?"

8. Answer: df.to_parquet(..., engine="pyarrow").

9. Trap: Excel is slow and limited to ~1M rows.

10. "How do you handle nested JSON?"

11. Answer: pd.json_normalize().
12. Trap: pd.read_json() alone won’t flatten nested data.

7. ? Hands-On Challenge

Challenge:
You have a 500MB CSV with 10M rows. Your script crashes when reading it. Fix it using chunksize and dtype, then write the cleaned data to Parquet.

Solution:

import pandas as pd

# Read in chunks
chunk_iter = pd.read_csv(
    "big_data.csv",
    chunksize=500_000,
    dtype={"id": "int32", "value": "float32"}
)

# Process and combine
df = pd.concat([chunk.dropna() for chunk in chunk_iter])

# Write to Parquet
df.to_parquet("clean_data.parquet", engine="pyarrow")

Why it works:
- chunksize prevents memory crashes.
- dtype reduces memory usage.
- Parquet is faster and smaller than CSV.

8. ? Rapid-Reference Crib Sheet

9. ? Where to Go Next

1. Pandas I/O Documentation (Official)
2. Parquet vs. CSV Benchmark (Towards Data Science)
3. Excel with Pandas Guide (Real Python)
4. Handling Large Data in Pandas (LearnDataSci)