read_csv / to_csv / Parquet

import pandas as pd
import numpy as np
from sqlalchemy import create_engine

# === CSV ===

# 1) Read
df = pd.read_csv('users.csv')
df = pd.read_csv('users.csv',
    sep            = ',',
    encoding       = 'utf-8',
    parse_dates    = ['created_at', 'last_login'],
    dtype          = {'age': 'Int32', 'name': 'string'},
    na_values      = ['', 'N/A', '?'],
    nrows          = 1000,                    # only first N rows
    usecols        = ['id', 'email', 'name'], # only some columns
    index_col      = 'id',
)

# Read in chunks (memory-safe for huge files)
for chunk in pd.read_csv('big.csv', chunksize=100_000):
    process(chunk)

# 2) Write
df.to_csv('out.csv', index=False)
df.to_csv('out.csv',
    index    = False,
    encoding = 'utf-8',
    sep      = ',',
    quoting  = csv.QUOTE_MINIMAL,
    date_format = '%Y-%m-%d %H:%M:%S',
)

# Compressed
df.to_csv('out.csv.gz', index=False, compression='gzip')

# === JSON ===

# 3) Read
df = pd.read_json('events.json')
df = pd.read_json('events.ndjson', lines=True)        # NDJSON / JSONL — one record per line
df = pd.read_json('https://api.example.com/data')      # URL

# 4) Write
df.to_json('out.json', orient='records', indent=2)
df.to_json('out.ndjson', orient='records', lines=True)

# Orient options:
# 'records'  : [{c1: v, c2: v}, ...]
# 'columns'  : {c1: {i: v}, c2: {i: v}}    (default)
# 'index'    : {i: {c1: v, c2: v}}
# 'split'    : {columns: [...], index: [...], data: [[...]]}
# 'values'   : [[v, v], ...]
# 'table'    : full schema + data (JSON Table Schema)

# === Parquet — best for analytics ===
# pip install pyarrow  (or fastparquet)

# 5) Read / write
df.to_parquet('users.parquet', compression='snappy')
df = pd.read_parquet('users.parquet')

# Only some columns (column-store wins)
df = pd.read_parquet('users.parquet', columns=['id', 'email'])

# Filter at read time (predicate pushdown)
df = pd.read_parquet('events.parquet', filters=[('user_id', '=', 42)])

# Partitioned dataset (Hive-style)
# data/
#   year=2026/
#     month=06/
#       events.parquet
df = pd.read_parquet('data/', filters=[('year', '=', 2026), ('month', '=', 6)])

# Pros:
# - 10-100x smaller than CSV (columnar + compressed)
# - Schema preserved (no need to re-cast dates / ints)
# - Fast column reads
# - Read-only fields skipped from disk
# Cons:
# - Not human-readable
# - Requires a library to inspect

# === Excel ===

# 6) Excel — multi-sheet
xl = pd.ExcelFile('report.xlsx')
print(xl.sheet_names)
df_users  = xl.parse('users')
df_orders = xl.parse('orders', header=2, skiprows=1)

with pd.ExcelWriter('out.xlsx', engine='openpyxl') as w:
    df_users.to_excel(w,  sheet_name='users',  index=False)
    df_orders.to_excel(w, sheet_name='orders', index=False)

# Tip: for read-only, pd.read_excel is fine; for writing styled workbooks, use openpyxl or xlsxwriter directly

# === SQL ===

# 7) Read from database
engine = create_engine('postgresql+psycopg://user:pass@host:5432/dbname')

df = pd.read_sql('SELECT * FROM users WHERE active = true', engine)
df = pd.read_sql_query(
    'SELECT * FROM orders WHERE created_at >= %(since)s',
    engine,
    params={'since': '2026-06-01'},
    parse_dates=['created_at'],
)

# Whole table
df = pd.read_sql_table('users', engine)

# Stream in chunks (don't load 100M rows)
for chunk in pd.read_sql_query('SELECT * FROM events', engine, chunksize=50_000):
    process(chunk)

# 8) Write to DB
df.to_sql('users_staging', engine,
    if_exists = 'replace',     # 'fail' | 'replace' | 'append'
    index     = False,
    method    = 'multi',        # multi-value INSERT
    chunksize = 10_000,
)

# For fastest inserts (Postgres): use psycopg COPY directly
with engine.connect() as conn:
    raw = conn.connection.cursor()
    with raw.copy('COPY users_staging FROM STDIN WITH (FORMAT csv, HEADER true)') as copy:
        copy.write(open('users.csv', 'rb').read())

# === HDF5 ===

# 9) HDF5 — multi-key store, fast random access
store = pd.HDFStore('data.h5')
store.put('users',  df_users,  format='table')
store.put('orders', df_orders, format='table')
store.close()

# Query without loading all
store = pd.HDFStore('data.h5')
recent = store.select('orders', where='created_at > '2026-06-01' & total > 100')

# === Performance tips ===

# 10) Parquet > Feather > CSV for size + speed
# CSV:        500 MB, 30s read
# Feather:     80 MB,  3s read
# Parquet:     60 MB,  5s read (with compression, predicate pushdown)

# 11) Specify dtypes on read — saves memory + speeds parsing
df = pd.read_csv('big.csv', dtype={
    'id':     'int32',
    'amount': 'float32',
    'status': 'category',     # huge win for low-cardinality strings
})

# 12) Use categorical for repeating strings
df['country'] = df['country'].astype('category')

# 13) Date parsing — slow by default
# Specify the format to skip auto-detection:
df = pd.read_csv('events.csv', parse_dates=['ts'], date_format='%Y-%m-%dT%H:%M:%S')

# 14) Big-file alternatives — when pandas struggles
# - Polars      : columnar, lazy, 10x faster on many ops
# - DuckDB      : SQL over Parquet without loading
# - Dask / Modin: distributed pandas-API
# - PyArrow     : low-level, used by pandas under the hood

# DuckDB on a Parquet file (no pandas):
import duckdb
duckdb.query('SELECT country, count(*) FROM "users.parquet" GROUP BY country').to_df()

# === Format choice ===

# CSV     : portability, human-readable, slow + bloated
# Parquet : analytics, large data, fast + small
# Feather : fast IPC, smaller than Parquet for small data
# JSON    : APIs, configs, web data — use NDJSON for streams
# Excel   : business reports — colleagues need to open it
# SQL     : production data, transactional needs
# HDF5    : scientific, hierarchical, mostly legacy

# === Common bugs ===
#   • CSV without dtype hints → pandas guesses wrong (int columns become float when nulls present)
#   • Mixed encodings (utf-8 vs latin-1) → UnicodeDecodeError
#   • Trailing whitespace in CSV → 'a' != 'a ' silently
#   • Excel auto-converting strings to scientific notation (use dtype=str on read)
#   • SQL queries without params → SQL injection (use placeholders)
#   • Writing huge DataFrames to CSV instead of Parquet — slow + huge files

import pandas as pd
df = pd.read_csv('users.csv')
df.to_csv('clean.csv', index=False)
df.to_parquet('clean.parquet')