CSV পড়া ও পরিষ্কার করা

বাংলা/Unicode data নিয়ে কাজ করার সময় encoding সবচেয়ে সাধারণ frustration। সঠিক বুঝলে — ১৫ মিনিটের সমস্যা সমাধান হয়, ভুল বুঝলে — ৫ ঘণ্টা গায়েব।

Encoding কী:

• প্রতিটি অক্ষর = একটি বা একাধিক byte। কোন byte কোন অক্ষর — সেটাই encoding।
• ASCII (1963) — শুধু English (1 byte/char, 128 char)।
• Unicode = সব ভাষার character set (১,১১,৪০০+ codepoint)।
• UTF-8/16/32 = Unicode encode করার বিভিন্ন scheme।

UTF-8 (universal default):

• Variable-length: ১-৪ byte per character।
• English (ASCII compat) ১ byte, বাংলা সাধারণত ৩ byte।
• Linux, Mac, Web, Python 3 — সবার default।
• "ক" = E0 A6 95 (3 byte)।

UTF-16:

• ২ বা ৪ byte/char। বাংলা ২ byte।
• Windows internal, Java internal।
• BOM (Byte Order Mark) প্রয়োজন — endianness বোঝাতে।
• Web-এ uncommon।

Windows-1252 (CP-1252):

• ASCII + ১২৮ extra char (European)।
• Windows পুরাতন default — Excel save legacy।
• বাংলা সমর্থন করে না — তাই বাংলা file CP-1252-এ save হলে data lost (replacement char দিয়ে)।

BOM (Byte Order Mark):

• File-এর শুরুতে invisible marker — encoding hint।
• UTF-8 BOM: EF BB BF (ASCII-তে দেখায় )।
• Excel সবসময় BOM যোগ করে — Pandas confused: প্রথম column name "name" হয়ে যায়।
• Solution: encoding='utf-8-sig' — BOM auto-strip।

Detection workflow:

# 1) chardet/charset-normalizer দিয়ে detect
import chardet
with open("data.csv", "rb") as f:
    raw = f.read(100000)            # প্রথম 100KB যথেষ্ট
detected = chardet.detect(raw)
print(detected)
# {'encoding': 'utf-8', 'confidence': 0.99, ...}

# 2) Hex দেখা — প্রথম byte
# EF BB BF = UTF-8 BOM
# FF FE = UTF-16 LE
# FE FF = UTF-16 BE

# 3) Try-fail-fallback
encodings = ["utf-8-sig", "utf-8", "cp1252", "latin-1", "utf-16"]
for enc in encodings:
    try:
        df = pd.read_csv("data.csv", encoding=enc)
        print(f"Worked: {enc}")
        break
    except UnicodeDecodeError:
        continue

Fix strategies:

• Source-এ ঠিক করুন: Excel "Save As → CSV UTF-8" option ব্যবহার।
• Re-encode: file.read().decode('cp1252').encode('utf-8')।
• errors='replace': ভুল byte ? দিয়ে replace (data loss সম্ভব)।
• errors='ignore': ভুল byte skip (সাবধান)।

Real-world advice:

• সবসময় UTF-8 in your pipeline।
• Source data CP-1252 হলে — ingest pipeline-এ একবার convert।
• Excel থেকে এলে — encoding='utf-8-sig' default।
• Document encoding in README — team-এ confusion এড়াতে।

মূল উপলব্ধি: Encoding = byte-to-character mapping। Unicode/UTF-8 আজকের universal standard। বাংলা data-তে UTF-8 + BOM-aware ingest = ৯৯% সমস্যা solved। chardet + try-fallback = robust pipeline।

dtype = data type। Pandas-এ এটাই ৭০% performance optimization। বুঝে dtype দিলে — ১০ GB CSV ১.৩ GB হয়, query ১০× দ্রুত।

Default behavior:

• read_csv first 1000 row sample করে dtype infer করে।
• String → object (Python list-of-strings, slow)।
• Numeric → int64 / float64 (8 byte each)।
• Date string → object — auto-parse করে না (parse_dates না দিলে)।

কেন cast আবশ্যক:

• Math operation: string-এ + = concat, numeric-এ + = sum।
• Comparison: "10" < "9" (string compare!) — wrong।
• Aggregation: mean/sum/median — numeric চাই।
• ML model: sklearn/TF/PyTorch — numpy float চায়।
• Date arithmetic: "যত দিন আগে" — datetime দরকার।

Memory comparison (১ million row):

             default       optimized      saving
int64  →     int8           8MB → 1MB     8×
float64 →    float32        8MB → 4MB     2×
object →     category       50MB → 2MB    25×
object →     string[pyarrow] 50MB → 8MB   6×

Real benchmark:

• NYC Taxi 2GB CSV → optimized dtype → 350MB।
• Read time: 45s → 12s।
• groupby aggregation: 8s → 1s।

Cast strategies:

# Strategy 1 — read_csv-এ dtype dict
df = pd.read_csv("big.csv", dtype={
    "user_id": "int32",
    "rating":  "float32",
    "country": "category",
    "name":    "string"
})

# Strategy 2 — read পরে cast
df["price"] = pd.to_numeric(df["price"], errors="coerce")
df["date"]  = pd.to_datetime(df["date"], errors="coerce")
df["cat"]   = df["cat"].astype("category")

# Strategy 3 — downcast automatic
df["score"] = pd.to_numeric(df["score"], downcast="integer")
# auto picks smallest int that fits

Category dtype magic:

• Repeating string ("M"/"F", "A"/"B"/"C") → integer codes internally।
• ৫০ MB → ২ MB common।
• groupby/sort dramatically faster।
• ML-এ one-hot/label-encoding-এর pre-step।

Nullable types (modern):

• Int64 (capital I) — supports NaN unlike int64।
• string — better than object for text।
• Float32/Float64 — explicit nullable।
• boolean — supports NA।

Arrow backend (modern):

• pd.read_csv("f.csv", dtype_backend="pyarrow")
• Apache Arrow under the hood।
• Memory & speed dramatic improvement।
• String-heavy workload-এ ৫-১০× ভাল।

AI training impact:

• DataLoader speed = training speed bottleneck (often)।
• Wrong dtype → conversion per batch → GPU starvation।
• Tabular ML (XGBoost) — float32 preferred (GPU memory)।
• Embedding lookup — int32 index efficient।

Common mistakes:

• Phone number → int (leading 0 lost)। Use string।
• ID column → int64 default (waste)। Use int32 or string।
• Yes/No → object। Use category বা bool।
• Year → int64। Use int16 (1900-2100 fits in 16-bit)।

মূল উপলব্ধি: dtype = pandas optimization-এর foundation। Default safe কিন্তু wasteful। Production AI pipeline-এ — explicit dtype + category + parquet = ১০-১০০× efficiency। memory_usage(deep=True) দিয়ে measure করুন, then optimize।

Real CSV কখনই tutorial-এর মতো clean না। Strategy data scale-এর সাথে fundamentally পাল্টায় — ১ MB-তে যা trivial, ১০ GB-তে impossible।

Common dirtiness categories:

• Null variants: "", "N/A", "NaN", "null", "-", "?", "missing", "n/a", "NA"।
• Encoding noise: trailing space, BOM, hidden Unicode (zero-width)।
• Spelling variations: "Dhaka"/"dhaka"/"DHAKA"/"ঢাকা"/"Dhka" (typo)।
• Date chaos: "2025-01-15", "15/01/25", "Jan 15", "1/15/25"।
• Number chaos: "1,250", "1.250 BDT", "৳1250", "USD 12"।
• Schema drift: column add/remove between months।
• Swapped columns: source bug — name ও phone কখনো অদলবদল।
• Embedded delimiter: "Dhaka, Bangladesh" comma-cell।
• Multiline cell: address-এ newline।

Small CSV (<100 MB) strategy:

1. Pandas in-memory load।
2. Manual exploration: head(), info(), value_counts()।
3. Eyeball every column — anomaly হাতে catch।
4. Iterative fix — Jupyter cell-by-cell।
5. Visual check: matplotlib histogram।
6. Export cleaned version।

Large CSV (1-10 GB) strategy:

1. Sample first: nrows=10000 দিয়ে structure বুঝুন।
2. Schema infer ও lock — explicit dtype।
3. Chunked read: chunksize=100000 — process per chunk।
4. Dask/Polars consider: lazy evaluation, parallel।
5. Convert to Parquet — future read ১০× faster।
6. Sampling-based validation — 1% random sample-এ check।

Huge CSV (>10 GB) strategy:

1. Pandas-এ আনার আগে — DuckDB/Polars/SQL।
2. DuckDB: SELECT * FROM 'data.csv' WHERE... — no load।
3. Spark for distributed cluster।
4. Schema validation — Great Expectations, Pandera।
5. Streaming pipeline — Apache Beam, Flink।

Null variant handling:

# All variants একসাথে
NA_VALS = ["", "N/A", "n/a", "NA", "NaN", "nan",
           "null", "NULL", "-", "?", "missing", "."]
df = pd.read_csv("data.csv", na_values=NA_VALS, keep_default_na=True)

Spelling normalization:

# Step 1: lowercase + strip
df["city"] = df["city"].str.strip().str.lower()

# Step 2: known mapping
city_map = {
    "dhaka":  "Dhaka",   "ঢাকা":  "Dhaka",
    "ctg":    "Chittagong", "chittagong": "Chittagong",
    "dhka":   "Dhaka",   # typo
}
df["city"] = df["city"].map(city_map).fillna(df["city"])

# Step 3: fuzzy match for residual
from rapidfuzz import process
unique = df["city"].unique()
for c in unique:
    match, score, _ = process.extractOne(c, list(city_map.values()))
    if score > 85:
        df.loc[df["city"] == c, "city"] = match

Schema drift detection:

EXPECTED_COLS = {"id", "name", "amount", "date"}

cols = set(df.columns)
missing = EXPECTED_COLS - cols
extra   = cols - EXPECTED_COLS

if missing:
    raise ValueError(f"Missing: {missing}")
if extra:
    print(f"Warning — new columns: {extra}")

Swapped columns detection:

• Statistical: phone column-এ all-digit expected; name-এ alphabet — heuristic check।
• Data dictionary cross-validate।
• Sample row eyeball দেখুন।

Modern alternative tools:

• Polars: Rust-backed, ৫-১০× pandas faster।
• DuckDB: SQL on CSV/Parquet without load।
• Dask: pandas API, parallel/distributed।
• Modin: drop-in pandas replacement।

Validation framework:

• Pandera: schema-as-code, fail-fast।
• Great Expectations: data quality test suite।
• cerberus: light-weight schema validator।

মূল উপলব্ধি: Small data — manual + iterative। Large data — schema-first, chunked, alternative engine। Validation = pipeline-এর প্রথম step। "Garbage in, garbage out" — AI-তে এই উক্তি বিশেষ true। ৬০% data scientist-এর সময় cleaning-এ। Tooling discipline = AI productivity।

File format choice = AI pipeline-এর hidden bottleneck। ভুল choice = ১০× slower training, ১০× higher cloud cost। Modern AI engineer তিনটিই জানে — কখন কোনটা।

CSV (Comma-Separated Values):

• Pros:
  • Universal — every tool reads।
  • Human-readable — text editor-এ open।
  • Streaming-friendly — line-by-line।
  • Excel/Google Sheets compatible।
• Cons:
  • Largest size (no compression default)।
  • Slowest read (parse + type infer)।
  • No schema (every read re-infer)।
  • No nested data।
  • Encoding ambiguity।
• Use when: Data exchange, manual review, small data (<100 MB), human consumption।

Parquet (Apache, columnar):

• Pros:
  • ৫-১০× smaller than CSV (Snappy/Gzip default)।
  • ৫-১০× faster read (binary, columnar)।
  • Schema preserved (dtype safe)।
  • Column pruning — শুধু needed columns load।
  • Predicate pushdown — filter at file level।
  • Nested data support (struct, list, map)।
• Cons:
  • Binary — text editor-এ খোলা যায় না।
  • Excel direct support নেই।
  • Append-friendly নয় (immutable)।
  • PyArrow/Fastparquet dependency।
• Use when: Big data, AI training, data lake, repeated read, cloud storage (S3)।

JSON / JSONL:

• Pros:
  • Nested data natural (dict, list)।
  • Web API standard।
  • Schema flexible — fields vary per row।
  • Human-readable।
  • JSONL (line-delimited) — streaming friendly।
• Cons:
  • Verbose (key repeat per row)।
  • Slower than Parquet, similar to CSV।
  • Tabular data inefficient।
• Use when: API response, semi-structured (LLM outputs), nested record, log data, NoSQL export।

Benchmark — 10M row tabular dataset:

Format       Size    Write    Read    Compressed
─────────────────────────────────────────────────
CSV          850 MB  120 s    45 s    No
CSV.gz       180 MB  140 s    55 s    Yes
JSON         1.2 GB  150 s    65 s    No
JSONL        1.1 GB  130 s    50 s    No
Parquet       95 MB   18 s     8 s    Snappy
Parquet+zstd  60 MB   25 s     9 s    Zstd
Feather      105 MB    9 s     5 s    LZ4

AI pipeline stages — কোনটা কখন:

• Ingest (raw): CSV/JSON থেকে আসে — clients এতে দেয়।
• Bronze layer (raw clean): Parquet — schema lock, compress।
• Silver layer (feature): Parquet — partitioned by date।
• Gold layer (model-ready): Parquet/Feather — fast load।
• Training: Parquet → DataLoader → batch।
• Export to client: CSV (universal)।

Modern alternatives:

• Feather (Arrow IPC): Fastest, no compression default. Memory-mapped। Short-term cache।
• HDF5: Scientific computing, large arrays. Hierarchical।
• Avro: Schema-evolution friendly, Kafka standard।
• ORC: Hadoop ecosystem, similar to Parquet।
• Delta Lake / Iceberg: Parquet + ACID transactions, version control।

Compression options:

• Snappy: fast compress/decompress, moderate ratio (Parquet default)।
• Gzip: better compression, slower।
• Zstd: best of both — recommended modern।
• LZ4: fastest, lower ratio।
• Brotli: best for text, slow।

Schema preservation matters:

# CSV — dtype lost
df.to_csv("f.csv")
df2 = pd.read_csv("f.csv")        # all columns object/inferred again

# Parquet — dtype preserved
df.to_parquet("f.parquet")
df2 = pd.read_parquet("f.parquet")  # exactly same dtypes
assert df.dtypes.equals(df2.dtypes)

Cloud cost reality:

• S3 storage: $0.023/GB/month — Parquet ১০× কম storage।
• S3 read: $0.0004/1000 GET — column pruning ১০× কম read।
• BigQuery/Athena query: byte-scanned billing — Parquet ১০-১০০× কম bill।
• 1 TB CSV → 100 GB Parquet → $২০/month → $২/month।

Decision flowchart:

• Human review করবে? → CSV।
• Nested data? → JSON।
• API response? → JSON।
• Big tabular + read repeated? → Parquet।
• Streaming append? → JSONL বা Avro।
• Need ACID? → Delta Lake।
• Quick interim cache? → Feather।

মূল উপলব্ধি: CSV = lingua franca (universal but inefficient)। Parquet = AI workhorse (binary, columnar, schema-safe)। JSON = nested/API data। ভাল engineer pipeline-এ multi-format flow design করেন — ingest CSV, store Parquet, export CSV। Format = silent performance lever। ১০× efficiency শুধু format change-এ পাওয়া যায়।