# Exploring Data - Usage Documentation

## What This Skill Does

Generates comprehensive exploratory data analysis reports using ydata-profiling, a battle-tested library with 12.5k+ GitHub stars.

## Features

**Minimal Mode (default, 5-10s):**
- Dataset overview (rows, columns, types, memory)
- Variable analysis (distributions, statistics)
- Missing value analysis
- Duplicate detection
- Correlation matrices
- Data quality alerts

**Full Mode (10-20s):**
- Everything in minimal +
- Scatter plot matrices
- Sample data display
- Character/script analysis (for text)
- Auto-correlation
- More sophisticated visualizations

## Output Formats

**HTML (default):**
- Interactive, self-contained report (500KB-2MB)
- Tabbed navigation
- Embedded visualizations
- Downloadable via browser

**JSON:**
- Machine-readable format
- For programmatic access
- Can be parsed by other tools

## Installation

**First use only (~19s):**
```bash
bash /mnt/skills/user/exploring-data/scripts/install_ydata.sh
```

Creates isolated venv at `/home/claude/.venvs/exploring-data` with:
- ydata-profiling 4.17.0
- ~50 dependencies
- Total: 559MB

**Subsequent uses:** Instant (venv already exists)

## Performance

| Dataset Size | Minimal Mode | Full Mode |
|-------------|-------------|-----------|
| <10k rows | 2-5s | 5-10s |
| 10-50k rows | 5-10s | 10-15s |
| 50-100k rows | 10-15s | 15-25s |

## Troubleshooting

**If installation fails:**
- Check network access to pypi.org
- Ensure uv is available: `uv --version`
- Try manual install:
  ```bash
  uv venv /home/claude/.venvs/exploring-data
  uv pip install ydata-profiling setuptools --python /home/claude/.venvs/exploring-data
  ```

**If analysis fails:**
- Check file format is supported (.csv, .xlsx, .json, .parquet, .tsv)
- Verify file exists at specified path
- Check file is not corrupted

#!/bin/bash
# Run ydata-profiling analysis
# Usage: analyze.sh <datafile> [minimal|full] [html|json|both]

set -e

DATAFILE="$1"
MODE="${2:-minimal}"  # default: minimal
FORMAT="${3:-html}"   # default: html

VENV_PYTHON="/home/claude/.venvs/exploring-data/bin/python"
OUTPUT_DIR="/mnt/user-data/outputs"

if [ ! -f "$VENV_PYTHON" ]; then
    echo "Error: ydata-profiling not installed"
    echo "Run: bash /mnt/skills/user/exploring-data/scripts/install_ydata.sh"
    exit 1
fi

if [ ! -f "$DATAFILE" ]; then
    echo "Error: File not found: $DATAFILE"
    exit 1
fi

# Set minimal flag
if [ "$MODE" = "minimal" ]; then
    MINIMAL_FLAG="True"
else
    MINIMAL_FLAG="False"
fi

# Determine what to generate
GENERATE_HTML="false"
GENERATE_JSON="false"

if [ "$FORMAT" = "json" ]; then
    GENERATE_JSON="true"
elif [ "$FORMAT" = "both" ]; then
    GENERATE_HTML="true"
    GENERATE_JSON="true"
else
    # Default: HTML + always generate JSON for potential Claude analysis
    GENERATE_HTML="true"
    GENERATE_JSON="true"
fi

# Generate report
"$VENV_PYTHON" << PYEOF
import sys
from pathlib import Path
from ydata_profiling import ProfileReport
import pandas as pd

filepath = Path("$DATAFILE")

# Load data
if filepath.suffix == '.csv':
    df = pd.read_csv(filepath)
elif filepath.suffix == '.xlsx':
    df = pd.read_excel(filepath)
elif filepath.suffix == '.json':
    df = pd.read_json(filepath)
elif filepath.suffix == '.parquet':
    df = pd.read_parquet(filepath)
elif filepath.suffix == '.tsv':
    df = pd.read_csv(filepath, sep='\t')
else:
    print(f"Unsupported format: {filepath.suffix}")
    sys.exit(1)

# Generate profile
minimal = $MINIMAL_FLAG
profile = ProfileReport(df, minimal=minimal, title=f"EDA: {filepath.name}")

# Output files
output_dir = Path("$OUTPUT_DIR")
html_path = output_dir / "eda_report.html"
json_path = output_dir / "eda_report.json"

# Generate requested outputs
if "$GENERATE_HTML" == "true":
    profile.to_file(html_path)
    print(f"✓ HTML report: {html_path}")

if "$GENERATE_JSON" == "true":
    profile.to_file(json_path)
    print(f"✓ JSON report: {json_path}")

# Print summary
print(f"\nDataset: {len(df):,} rows × {len(df.columns)} columns")
print(f"Mode: {'Minimal' if minimal else 'Full'} analysis")
PYEOF

#!/bin/bash
# Quick check if ydata-profiling is installed
VENV_PYTHON="/home/claude/.venvs/exploring-data/bin/python"
[ -f "$VENV_PYTHON" ] && echo "installed" || echo "not_installed"

#!/bin/bash
# Install large-file/sketch deps (system-safe self-contained wheels, ~10s)
set -e
python3 - <<'PY' 2>/dev/null && { echo installed; exit 0; }
import duckdb, datasketches, datasketch
PY
echo "Installing duckdb + datasketches + datasketch..."
pip install --break-system-packages -q duckdb datasketches datasketch
echo "done"

#!/bin/bash
# Install ydata-profiling via uv (runs once, ~19s)
set -e

VENV_PATH="/home/claude/.venvs/exploring-data"

echo "⚙️  Installing ydata-profiling (~19 seconds, one-time only)..."

# Create venv
uv venv "$VENV_PATH" 2>&1 | grep -v "Using Python"

# Install packages.
#  - setuptools<81: ydata-profiling 4.18.x still imports pkg_resources, which
#    was removed from setuptools>=81. Unpinned, uv resolves the latest and the
#    first ProfileReport import dies with ModuleNotFoundError: pkg_resources.
#  - pyarrow: analyze.sh reads .parquet via pandas, which needs an engine.
uv pip install ydata-profiling "setuptools<81" pyarrow --python "$VENV_PATH" 2>&1 | tail -3

echo "✓ Installation complete!"

#!/usr/bin/env python3
"""Out-of-core profiling for files too large for ydata-profiling.

Uses DuckDB streaming scans: approx_count_distinct (HLL) and approx_quantile
(t-digest) run in fixed memory regardless of file size.

Usage: profile_large.py <datafile> [--json /path/out.json]
Output: markdown summary to stdout (Claude reads this directly).
"""
import json
import sys
from pathlib import Path

try:
    import duckdb
except ImportError:
    sys.exit("duckdb not installed — run install_large.sh first")


def reader_sql(path: Path) -> str:
    s = path.suffix.lower()
    p = str(path).replace("'", "''")
    if s in (".csv", ".tsv", ".txt"):
        return f"read_csv_auto('{p}', sample_size=-1)"
    if s == ".parquet":
        return f"read_parquet('{p}')"
    if s in (".json", ".ndjson", ".jsonl"):
        return f"read_json_auto('{p}')"
    sys.exit(f"Unsupported format: {s}")


def main():
    if len(sys.argv) < 2:
        sys.exit(__doc__)
    path = Path(sys.argv[1])
    if not path.is_file():
        sys.exit(f"File not found: {path}")
    json_out = None
    if "--json" in sys.argv:
        json_out = Path(sys.argv[sys.argv.index("--json") + 1])

    con = duckdb.connect()
    src = reader_sql(path)
    cols = con.execute(f"DESCRIBE SELECT * FROM {src}").fetchall()
    n_rows = con.execute(f"SELECT count(*) FROM {src}").fetchone()[0]

    numeric_types = ("TINYINT", "SMALLINT", "INTEGER", "BIGINT", "HUGEINT",
                     "FLOAT", "DOUBLE", "DECIMAL", "UTINYINT", "USMALLINT",
                     "UINTEGER", "UBIGINT")
    report = {"file": str(path), "rows": n_rows,
              "size_bytes": path.stat().st_size, "columns": []}

    for name, dtype, *_ in cols:
        q = f'"{name}"'
        base = con.execute(
            f"SELECT count({q}), approx_count_distinct({q}) FROM {src}"
        ).fetchone()
        non_null, distinct = base
        col = {"name": name, "dtype": dtype,
               "null_frac": round(1 - non_null / n_rows, 4) if n_rows else 0,
               "approx_distinct": distinct}
        if any(dtype.upper().startswith(t) for t in numeric_types):
            stats = con.execute(
                f"SELECT min({q}), max({q}), avg({q}), "
                f"approx_quantile({q}, 0.25), approx_quantile({q}, 0.5), "
                f"approx_quantile({q}, 0.75) FROM {src}"
            ).fetchone()
            col.update(dict(zip(
                ("min", "max", "mean", "q25", "median", "q75"),
                [round(v, 4) if isinstance(v, float) else v for v in stats])))
        elif dtype.upper().startswith(("VARCHAR", "DATE", "TIME")):
            top = con.execute(
                f"SELECT {q}, count(*) c FROM {src} WHERE {q} IS NOT NULL "
                f"GROUP BY {q} ORDER BY c DESC LIMIT 5"
            ).fetchall()
            col["top_values"] = [[str(v)[:60], c] for v, c in top]
        report["columns"].append(col)

    # markdown to stdout
    mb = report["size_bytes"] / 1e6
    print(f"# Profile: {path.name}\n")
    print(f"{n_rows:,} rows x {len(cols)} columns, {mb:,.1f} MB on disk\n")
    print("| column | type | null% | ~distinct | detail |")
    print("|---|---|---|---|---|")
    for c in report["columns"]:
        if "median" in c:
            d = (f"min {c['min']} / q25 {c['q25']} / med {c['median']} / "
                 f"q75 {c['q75']} / max {c['max']}")
        elif "top_values" in c:
            d = "; ".join(f"{v} ({n:,})" for v, n in c["top_values"][:3])
        else:
            d = ""
        print(f"| {c['name']} | {c['dtype']} | {c['null_frac']*100:.1f} "
              f"| {c['approx_distinct']:,} | {d} |")

    # quality flags
    flags = []
    for c in report["columns"]:
        if c["null_frac"] > 0.5:
            flags.append(f"{c['name']}: {c['null_frac']*100:.0f}% null")
        if c["approx_distinct"] <= 1 and n_rows > 1:
            flags.append(f"{c['name']}: constant")
        if c["approx_distinct"] >= 0.95 * n_rows and "top_values" in c:
            flags.append(f"{c['name']}: near-unique string (id-like)")
    if flags:
        print("\n**Flags:** " + "; ".join(flags))

    if json_out:
        json_out.write_text(json.dumps(report, indent=1, default=str))
        print(f"\nJSON: {json_out}")


if __name__ == "__main__":
    main()

#!/usr/bin/env python3
"""Sketch-based exploration ops that profilers miss. Fixed memory, any file size.

Subcommands:
  dups <file> [--threshold 0.9] [--cols a,b] [--unweighted]
                                               near-duplicate row detection (MinHash LSH)
  overlap <fileA> <fileB> --key <col> [--key-b <col>]   key overlap / join feasibility (theta sketch)
  snapshot <file> --out <sketches.json>        persist HLL+KLL sketches per column
  drift <file> --baseline <sketches.json>      compare current file against a snapshot

Files stream through DuckDB in batches; sketches are the only state held.
"""
import base64
import json
import sys
from collections import Counter
from pathlib import Path

try:
    import duckdb
    from datasketch import MinHash, MinHashLSH
    from datasketches import (
        hll_sketch,
        kll_floats_sketch,
        theta_intersection,
        theta_union,
        update_theta_sketch,
    )
except ImportError as e:
    sys.exit(f"Missing dep ({e.name}) — run install_large.sh first")

BATCH = 50_000


def reader_sql(path: Path) -> str:
    s = path.suffix.lower()
    p = str(path).replace("'", "''")
    return {
        ".csv": f"read_csv_auto('{p}', sample_size=-1)",
        ".tsv": f"read_csv_auto('{p}', sample_size=-1)",
        ".parquet": f"read_parquet('{p}')",
        ".json": f"read_json_auto('{p}')",
        ".ndjson": f"read_json_auto('{p}')",
        ".jsonl": f"read_json_auto('{p}')",
    }.get(s) or sys.exit(f"Unsupported format: {s}")


def batches(con, sql):
    cur = con.execute(sql)
    while rows := cur.fetchmany(BATCH):
        yield rows


def row_minhash(tokens, weighted=True, num_perm=128):
    """MinHash over a row's tokens.

    Weighted (the default) inserts c distinct elements (t, 0) ... (t, c-1) for
    a token t occurring c times, so the signature estimates the weighted
    Jaccard index sum(min(a_i, b_i)) / sum(max(a_i, b_i)). Unweighted collapses
    the tokens to a set: `new york new york` and `york new` then score 1.0
    against each other, because MinHash.update keeps a per-permutation minimum
    and hashing a token twice changes nothing. Rows whose tokens are all
    distinct produce the same signature either way.
    """
    m = MinHash(num_perm=num_perm)
    if weighted:
        for tok, c in Counter(tokens).items():
            for j in range(c):
                m.update(f"{tok}\x00{j}".encode())
    else:
        for tok in tokens:
            m.update(tok.encode())
    return m


def cmd_dups(argv):
    path = Path(argv[0])
    thresh = float(argv[argv.index("--threshold") + 1]) if "--threshold" in argv else 0.9
    con = duckdb.connect()
    src = reader_sql(path)
    cols = ('"' + '", "'.join(argv[argv.index("--cols") + 1].split(",")) + '"'
            ) if "--cols" in argv else "*"
    weighted = "--unweighted" not in argv
    lsh = MinHashLSH(threshold=thresh, num_perm=128)
    exact_seen, exact_dups, clusters, i = set(), 0, [], 0
    for rows in batches(con, f"SELECT {cols} FROM {src}"):
        for row in rows:
            i += 1
            key = tuple(str(v) for v in row)
            if key in exact_seen:
                exact_dups += 1
                continue
            exact_seen.add(key)
            m = row_minhash(" ".join(key).lower().split(), weighted=weighted)
            near = lsh.query(m)
            if near:
                clusters.append((near[0], i))
            lsh.insert(f"row{i}", m)
    print(f"# Near-duplicate scan: {path.name}")
    metric = "weighted Jaccard" if weighted else "Jaccard"
    print(f"{i:,} rows; {exact_dups:,} exact duplicates; "
          f"{len(clusters):,} near-duplicate pairs at {metric}>={thresh}")
    for anchor, dup in clusters[:10]:
        print(f"  {anchor} ~ row{dup}")
    if len(clusters) > 10:
        print(f"  ... {len(clusters) - 10} more")


def _theta_of(path, key):
    con = duckdb.connect()
    sk = update_theta_sketch()
    n = 0
    for rows in batches(con, f'SELECT "{key}" FROM {reader_sql(path)} '
                             f'WHERE "{key}" IS NOT NULL'):
        for (v,) in rows:
            sk.update(str(v))
            n += 1
    return sk, n


def cmd_overlap(argv):
    a, b = Path(argv[0]), Path(argv[1])
    key = argv[argv.index("--key") + 1]
    key_b = argv[argv.index("--key-b") + 1] if "--key-b" in argv else key
    sa, na = _theta_of(a, key)
    sb, nb = _theta_of(b, key_b)
    inter = theta_intersection()
    inter.update(sa)
    inter.update(sb)
    uni = theta_union()
    uni.update(sa)
    uni.update(sb)
    i, u = inter.get_result().get_estimate(), uni.get_result().get_estimate()
    ea, eb = sa.get_estimate(), sb.get_estimate()
    print(f"# Key overlap: {a.name}.{key} vs {b.name}.{key_b}")
    print(f"A: {na:,} values, ~{ea:,.0f} distinct")
    print(f"B: {nb:,} values, ~{eb:,.0f} distinct")
    print(f"Intersection ~{i:,.0f} | Jaccard ~{i/u:.3f}" if u else "empty")
    if ea:
        print(f"~{i/ea*100:.1f}% of A's keys appear in B; "
              f"~{i/eb*100:.1f}% of B's in A" if eb else "")


def _snapshot(path):
    con = duckdb.connect()
    src = reader_sql(path)
    cols = con.execute(f"DESCRIBE SELECT * FROM {src}").fetchall()
    numeric = ("TINYINT", "SMALLINT", "INTEGER", "BIGINT", "FLOAT", "DOUBLE",
               "DECIMAL", "HUGEINT")
    out = {"file": path.name, "rows": 0, "cols": {}}
    hll = {n: hll_sketch(12) for n, *_ in cols}
    kll = {n: kll_floats_sketch(200) for n, t, *_ in cols
           if any(t.upper().startswith(x) for x in numeric)}
    names = [n for n, *_ in cols]
    for rows in batches(con, f"SELECT * FROM {src}"):
        out["rows"] += len(rows)
        for row in rows:
            for n, v in zip(names, row):
                if v is None:
                    continue
                hll[n].update(str(v))
                if n in kll:
                    kll[n].update(float(v))
    for n in names:
        out["cols"][n] = {
            "hll": base64.b64encode(hll[n].serialize_compact()).decode()}
        if n in kll and not kll[n].is_empty():
            out["cols"][n]["kll"] = base64.b64encode(
                kll[n].serialize()).decode()
    return out


def cmd_snapshot(argv):
    path = Path(argv[0])
    out = Path(argv[argv.index("--out") + 1])
    snap = _snapshot(path)
    out.write_text(json.dumps(snap))
    print(f"Snapshot: {snap['rows']:,} rows, {len(snap['cols'])} columns "
          f"-> {out} ({out.stat().st_size/1024:.1f} KB)")


def cmd_drift(argv):
    path = Path(argv[0])
    base = json.loads(Path(argv[argv.index("--baseline") + 1]).read_text())
    cur = _snapshot(path)
    print(f"# Drift: {path.name} vs baseline {base['file']}")
    print(f"Rows: {base['rows']:,} -> {cur['rows']:,}")
    gone = set(base["cols"]) - set(cur["cols"])
    new = set(cur["cols"]) - set(base["cols"])
    if gone:
        print(f"Columns dropped: {sorted(gone)}")
    if new:
        print(f"Columns added: {sorted(new)}")
    for n in sorted(set(base["cols"]) & set(cur["cols"])):
        b, c = base["cols"][n], cur["cols"][n]
        hb = hll_sketch.deserialize(base64.b64decode(b["hll"]))
        hc = hll_sketch.deserialize(base64.b64decode(c["hll"]))
        eb, ec = hb.get_estimate(), hc.get_estimate()
        notes = []
        if eb and abs(ec - eb) / eb > 0.10:
            notes.append(f"distinct ~{eb:,.0f} -> ~{ec:,.0f}")
        if "kll" in b and "kll" in c:
            kb = kll_floats_sketch.deserialize(base64.b64decode(b["kll"]))
            kc = kll_floats_sketch.deserialize(base64.b64decode(c["kll"]))
            qb = [kb.get_quantile(q) for q in (0.25, 0.5, 0.75)]
            qc = [kc.get_quantile(q) for q in (0.25, 0.5, 0.75)]
            span = (qb[2] - qb[0]) or 1.0
            if any(abs(x - y) / abs(span) > 0.10 for x, y in zip(qb, qc)):
                notes.append(f"quantiles {[round(v,2) for v in qb]} -> "
                             f"{[round(v,2) for v in qc]}")
        if notes:
            print(f"  {n}: " + "; ".join(notes))
    print("(threshold: >10% shift in distinct count or IQR-relative quantiles)")


if __name__ == "__main__":
    cmds = {"dups": cmd_dups, "overlap": cmd_overlap,
            "snapshot": cmd_snapshot, "drift": cmd_drift}
    if len(sys.argv) < 3 or sys.argv[1] not in cmds:
        sys.exit(__doc__)
    cmds[sys.argv[1]](sys.argv[2:])

#!/usr/bin/env python3
"""
Extract key insights from ydata-profiling JSON for Claude to analyze.
Focuses on patterns, relationships, and quality issues rather than raw data.
"""
import json
import sys
from pathlib import Path


def extract_insights(profile_json_path):
    """Extract high-level insights optimized for LLM analysis."""
    with open(profile_json_path, 'r') as f:
        data = json.load(f)
    
    insights = {
        "dataset_overview": {},
        "variable_summary": {
            "numeric": [],
            "categorical": [],
            "temporal": [],
            "text": []
        },
        "data_quality": {
            "issues": [],
            "warnings": []
        },
        "patterns": {
            "correlations": [],
            "distributions": [],
            "relationships": []
        },
        "recommendations": []
    }
    
    # Dataset overview
    table = data.get('table', {})
    insights['dataset_overview'] = {
        "n_rows": table.get('n', 0),
        "n_cols": table.get('n_var', 0),
        "memory_size": table.get('memory_size', 0),
        "n_duplicates": table.get('n_duplicates', 0),
        "missing_cells": table.get('n_cells_missing', 0),
        "missing_pct": round(table.get('p_cells_missing', 0) * 100, 2)
    }
    
    variables = data.get('variables', {})
    correlations = data.get('correlations', {})
    
    # Analyze each variable
    for var_name, var in variables.items():
        vtype = var.get('type')
        n_missing = var.get('n_missing', 0)
        n_total = insights['dataset_overview']['n_rows']
        missing_pct = round((n_missing / n_total * 100), 2) if n_total > 0 else 0
        
        var_summary = {
            "name": var_name,
            "missing_pct": missing_pct,
            "n_unique": var.get('n_distinct', var.get('n_unique', 0))
        }
        
        if vtype == 'Numeric':
            # Skip if all missing
            if n_missing == n_total:
                continue
                
            var_summary.update({
                "mean": var.get('mean'),
                "std": var.get('std'),
                "min": var.get('min'),
                "max": var.get('max'),
                "skewness": var.get('skewness'),
                "n_zeros": var.get('n_zeros', 0),
                "n_infinite": var.get('n_infinite', 0)
            })
            
            # Detect patterns
            if var.get('n_zeros', 0) / n_total > 0.5:
                var_summary['note'] = "majority_zeros"
            elif var.get('skewness', 0) and abs(var.get('skewness', 0)) > 2:
                var_summary['note'] = "highly_skewed"
            elif var.get('std', 1) == 0:
                var_summary['note'] = "constant"
                
            insights['variable_summary']['numeric'].append(var_summary)
            
        elif vtype == 'Text':
            if n_missing == n_total:
                continue
                
            cardinality_ratio = var_summary['n_unique'] / n_total if n_total > 0 else 0
            var_summary['cardinality_ratio'] = round(cardinality_ratio, 3)
            
            # Classify
            if var_summary['n_unique'] == 1:
                var_summary['note'] = "constant"
            elif cardinality_ratio > 0.95:
                var_summary['note'] = "unique_identifier"
            elif cardinality_ratio < 0.5:
                var_summary['note'] = "categorical"
            else:
                var_summary['note'] = "high_cardinality"
                
            insights['variable_summary']['categorical'].append(var_summary)
            
        elif vtype == 'DateTime':
            var_summary.update({
                "min": var.get('min'),
                "max": var.get('max')
            })
            insights['variable_summary']['temporal'].append(var_summary)
    
    # Data quality issues
    if insights['dataset_overview']['missing_pct'] > 10:
        insights['data_quality']['issues'].append({
            "type": "high_missingness",
            "severity": "high" if insights['dataset_overview']['missing_pct'] > 30 else "medium",
            "description": f"{insights['dataset_overview']['missing_pct']}% of cells are missing"
        })
    
    if insights['dataset_overview']['n_duplicates'] > 0:
        dup_pct = round((insights['dataset_overview']['n_duplicates'] / insights['dataset_overview']['n_rows'] * 100), 2)
        insights['data_quality']['issues'].append({
            "type": "duplicates",
            "severity": "medium" if dup_pct > 1 else "low",
            "description": f"{insights['dataset_overview']['n_duplicates']} duplicate rows ({dup_pct}%)"
        })
    
    # Check for constant/useless columns
    constant_vars = [v['name'] for v in insights['variable_summary']['numeric'] if v.get('note') == 'constant']
    constant_vars += [v['name'] for v in insights['variable_summary']['categorical'] if v.get('note') == 'constant']
    if constant_vars:
        insights['data_quality']['warnings'].append({
            "type": "constant_columns",
            "count": len(constant_vars),
            "columns": constant_vars[:5]  # Show first 5
        })
    
    # Identify potential ID columns
    id_cols = [v['name'] for v in insights['variable_summary']['categorical'] if v.get('note') == 'unique_identifier']
    if id_cols:
        insights['data_quality']['warnings'].append({
            "type": "identifier_columns",
            "count": len(id_cols),
            "columns": id_cols[:5]
        })
    
    # Extract correlations (if available)
    if correlations and 'pearson' in correlations:
        pearson = correlations['pearson']
        # Find high correlations
        high_corrs = []
        processed_pairs = set()
        
        for var1, corr_dict in pearson.items():
            if isinstance(corr_dict, dict):
                for var2, corr_val in corr_dict.items():
                    if var1 != var2 and isinstance(corr_val, (int, float)):
                        pair = tuple(sorted([var1, var2]))
                        if pair not in processed_pairs and abs(corr_val) > 0.7:
                            high_corrs.append({
                                "var1": var1,
                                "var2": var2,
                                "correlation": round(corr_val, 3)
                            })
                            processed_pairs.add(pair)
        
        if high_corrs:
            insights['patterns']['correlations'] = sorted(high_corrs, key=lambda x: abs(x['correlation']), reverse=True)[:10]
    
    # Distribution patterns
    for var in insights['variable_summary']['numeric']:
        if var.get('note') == 'highly_skewed':
            insights['patterns']['distributions'].append({
                "variable": var['name'],
                "pattern": "skewed",
                "skewness": round(var.get('skewness', 0), 2)
            })
        elif var.get('note') == 'majority_zeros':
            insights['patterns']['distributions'].append({
                "variable": var['name'],
                "pattern": "zero-inflated",
                "zero_pct": round((var['n_zeros'] / insights['dataset_overview']['n_rows'] * 100), 1)
            })
    
    # Generate recommendations
    if insights['dataset_overview']['missing_pct'] > 20:
        insights['recommendations'].append("Consider imputation strategies or investigate the cause of missing data")
    
    if len(insights['patterns']['correlations']) > 5:
        insights['recommendations'].append("High correlations detected - consider dimensionality reduction or feature selection")
    
    if len([v for v in insights['variable_summary']['categorical'] if v.get('note') == 'high_cardinality']) > 0:
        insights['recommendations'].append("High-cardinality categorical variables may need encoding or grouping")
    
    numeric_count = len(insights['variable_summary']['numeric'])
    categorical_count = len([v for v in insights['variable_summary']['categorical'] if v.get('note') == 'categorical'])
    
    if numeric_count > 0 and categorical_count > 0:
        insights['recommendations'].append(f"Dataset has {numeric_count} numeric and {categorical_count} categorical variables suitable for analysis")
    
    return insights

def format_for_claude(insights):
    """Format insights in a Claude-friendly markdown format."""
    lines = []
    
    lines.append("# EDA Insights Summary")
    lines.append("")
    
    # Dataset overview
    overview = insights['dataset_overview']
    lines.append("## Dataset Overview")
    lines.append(f"- **Shape**: {overview['n_rows']:,} rows × {overview['n_cols']} columns")
    lines.append(f"- **Memory**: {overview['memory_size']:,} bytes")
    lines.append(f"- **Missing data**: {overview['missing_pct']}% of cells")
    if overview['n_duplicates'] > 0:
        lines.append(f"- **Duplicates**: {overview['n_duplicates']:,} rows")
    lines.append("")
    
    # Variable summary
    lines.append("## Variable Summary")
    
    numeric_vars = insights['variable_summary']['numeric']
    if numeric_vars:
        lines.append(f"\n**Numeric Variables** ({len(numeric_vars)}):")
        for var in numeric_vars[:10]:  # Show top 10
            range_str = f"[{var['min']:.2f}, {var['max']:.2f}]" if var['min'] is not None else "N/A"
            note = f" ({var['note']})" if var.get('note') else ""
            lines.append(f"- **{var['name']}**: range {range_str}, μ={var.get('mean', 'N/A'):.2f}, σ={var.get('std', 'N/A'):.2f}{note}")
    
    categorical_vars = [v for v in insights['variable_summary']['categorical'] if v.get('note') == 'categorical']
    if categorical_vars:
        lines.append(f"\n**Categorical Variables** ({len(categorical_vars)}):")
        for var in categorical_vars[:10]:
            lines.append(f"- **{var['name']}**: {var['n_unique']} unique values")
    
    # Data quality
    if insights['data_quality']['issues']:
        lines.append("\n## Data Quality Issues")
        for issue in insights['data_quality']['issues']:
            severity = issue['severity'].upper()
            lines.append(f"- **[{severity}]** {issue['type']}: {issue['description']}")
    
    if insights['data_quality']['warnings']:
        lines.append("\n## Warnings")
        for warning in insights['data_quality']['warnings']:
            cols_display = ", ".join(warning['columns']) if len(warning['columns']) <= 5 else f"{', '.join(warning['columns'][:5])}... ({warning['count']} total)"
            lines.append(f"- **{warning['type']}**: {cols_display}")
    
    # Patterns
    if insights['patterns']['correlations']:
        lines.append("\n## Strong Correlations (|r| > 0.7)")
        for corr in insights['patterns']['correlations'][:5]:
            lines.append(f"- **{corr['var1']}** ↔ **{corr['var2']}**: r={corr['correlation']:.3f}")
    
    if insights['patterns']['distributions']:
        lines.append("\n## Distribution Patterns")
        for dist in insights['patterns']['distributions'][:5]:
            if dist['pattern'] == 'skewed':
                lines.append(f"- **{dist['variable']}**: Highly skewed (skewness={dist['skewness']})")
            elif dist['pattern'] == 'zero-inflated':
                lines.append(f"- **{dist['variable']}**: Zero-inflated ({dist['zero_pct']}% zeros)")
    
    # Recommendations
    if insights['recommendations']:
        lines.append("\n## Recommendations")
        for rec in insights['recommendations']:
            lines.append(f"- {rec}")
    
    return "\n".join(lines)

if __name__ == "__main__":
    if len(sys.argv) != 2:
        print("Usage: python summarize_insights.py <eda_report.json>")
        sys.exit(1)
    
    json_path = Path(sys.argv[1])
    if not json_path.exists():
        print(f"Error: File not found: {json_path}")
        sys.exit(1)
    
    # Extract insights
    insights = extract_insights(json_path)
    
    # Format for Claude
    markdown = format_for_claude(insights)
    
    # Save markdown summary
    summary_path = json_path.parent / "eda_insights_summary.md"
    with open(summary_path, 'w') as f:
        f.write(markdown)
    
    # Also save JSON for programmatic access
    json_summary_path = json_path.parent / "eda_insights_summary.json"
    with open(json_summary_path, 'w') as f:
        json.dump(insights, f, indent=2)
    
    # Print to stdout for immediate use
    print(markdown)
    print(f"\n---\n✓ Summary saved to: {summary_path}")
    print(f"✓ JSON saved to: {json_summary_path}")

#!/usr/bin/env python3
"""Turn an EDA profile into a clean, self-contained visual dashboard.

The ydata HTML report is exhaustive but visually noisy; the DuckDB large-file
path emits only text/JSON. This script reads either JSON shape and renders a
single standalone HTML file with a handful of Chart.js charts that carry the
findings worth seeing: missingness by column, the most skewed / zero-inflated
distributions, and the largest categorical breakdowns.

Usage:
  python3 visualize_findings.py <report.json> [--out findings.html] [--top N]

Accepts:
  - ydata-profiling JSON (has top-level "variables" + "table")
  - profile_large.py --json output (has top-level "columns")

Output: one HTML file, no external data, Chart.js from cdnjs. Open directly or
hand to the user. Charts are flat, dark-mode aware, and each has an aria-label.
"""
import argparse
import html
import json
import sys
from pathlib import Path

CDN = "https://cdnjs.cloudflare.com/ajax/libs/Chart.js/4.4.1/chart.umd.js"
# Cove categorical order (light-mode hexes); charts pick from these by slot.
SERIES = ["#2a78d6", "#eb6834", "#1baf7a", "#eda100",
          "#e87ba4", "#008300", "#4a3aa7", "#e34948"]
GOOD, BAD, NEUTRAL = "#1baf7a", "#e34948", "#378add"


def _pct(x):
    try:
        return round(float(x) * 100, 1)
    except (TypeError, ValueError):
        return None


def load(path):
    d = json.loads(Path(path).read_text())
    if "variables" in d and "table" in d:
        return _from_ydata(d)
    if "columns" in d:
        return _from_large(d)
    sys.exit("Unrecognized JSON: expected ydata ('variables') or profile_large ('columns') shape.")


def _from_ydata(d):
    t = d["table"]
    n = t.get("n")
    vars_ = d["variables"]
    cols = []
    for name, v in vars_.items():
        typ = v.get("type")
        rec = {
            "name": name,
            "type": typ,
            "p_missing": _pct(v.get("p_missing")),
            "n_distinct": v.get("n_distinct"),
        }
        if typ == "Numeric":
            rec["skewness"] = v.get("skewness")
            rec["p_zeros"] = _pct(v.get("p_zeros"))
            h = v.get("histogram") or {}
            counts = h.get("counts")
            edges = h.get("bin_edges")
            if counts and edges and len(edges) == len(counts) + 1:
                rec["hist"] = {
                    "counts": counts,
                    "labels": [f"{edges[i]:.2g}" for i in range(len(counts))],
                }
        elif typ in ("Categorical", "Text", "Boolean"):
            vc = v.get("value_counts_without_nan") or {}
            rec["value_counts"] = list(vc.items())[:12]
        cols.append(rec)
    return {
        "title": d.get("analysis", {}).get("title", "Dataset"),
        "n_rows": n,
        "n_cols": t.get("n_var"),
        "p_cells_missing": _pct(t.get("p_cells_missing")),
        "alerts": d.get("alerts", []),
        "columns": cols,
    }


def _from_large(d):
    cols = []
    for c in d["columns"]:
        rec = {
            "name": c.get("name"),
            "type": c.get("type", ""),
            "p_missing": _pct(c.get("null_frac")) if c.get("null_frac") is not None
            else c.get("null_pct"),
            "n_distinct": c.get("approx_distinct") or c.get("n_distinct"),
        }
        top = c.get("top_values") or c.get("top")
        if top:
            rec["value_counts"] = [(str(k), val) for k, val in
                                   (top.items() if isinstance(top, dict) else top)][:12]
        cols.append(rec)
    return {
        "title": d.get("file", "Dataset"),
        "n_rows": d.get("n_rows"),
        "n_cols": len(cols),
        "p_cells_missing": None,
        "alerts": d.get("flags", []),
        "columns": cols,
    }


def build_charts(data, top):
    """Return list of chart dicts: {id, kind, aria, cfg, note}."""
    charts = []
    cols = data["columns"]

    # 1) Missingness by column (only columns that have any). Sorted desc, capped.
    miss = [(c["name"], c["p_missing"]) for c in cols
            if c.get("p_missing")]
    miss.sort(key=lambda x: -x[1])
    if miss:
        miss = miss[:max(top, 12)]
        charts.append({
            "id": "miss",
            "aria": "Missing data percentage by column.",
            "note": "Columns with any missing values, worst first. Red ≥ 50%.",
            "cfg": {
                "type": "bar",
                "data": {"labels": [m[0] for m in miss],
                         "datasets": [{"data": [m[1] for m in miss],
                                       "backgroundColor": [BAD if m[1] >= 50 else GOOD for m in miss],
                                       "borderRadius": 4, "borderSkipped": False}]},
                "options": _hbar_opts("% missing", 0, 100, pct=True,
                                      height=max(200, 26 * len(miss) + 60)),
            },
        })

    # 2) Most skewed / zero-inflated numeric distributions — small-multiple histograms.
    nums = [c for c in cols if c.get("hist")]
    def _severity(c):
        return max(abs(c.get("skewness") or 0), (c.get("p_zeros") or 0) / 10)
    nums.sort(key=_severity, reverse=True)
    for i, c in enumerate(nums[:min(top, 6)]):
        tags = []
        if c.get("skewness") is not None and abs(c["skewness"]) >= 2:
            tags.append(f"skew {c['skewness']:.1f}")
        if c.get("p_zeros") and c["p_zeros"] >= 40:
            tags.append(f"{c['p_zeros']:.0f}% zeros")
        charts.append({
            "id": f"hist{i}",
            "aria": f"Distribution histogram of {c['name']}.",
            "note": f"{c['name']}" + (f" — {', '.join(tags)}" if tags else ""),
            "half": True,
            "cfg": {
                "type": "bar",
                "data": {"labels": c["hist"]["labels"],
                         "datasets": [{"data": c["hist"]["counts"],
                                       "backgroundColor": NEUTRAL,
                                       "borderRadius": 2, "borderSkipped": False,
                                       "barPercentage": 1.0, "categoryPercentage": 1.0}]},
                "options": _vbar_opts(height=220),
            },
        })

    # 3) Largest categorical breakdowns (excluding near-unique id-like columns).
    cats = [c for c in cols if c.get("value_counts")
            and c.get("n_distinct") and 1 < c["n_distinct"] <= 15]
    cats.sort(key=lambda c: c["n_distinct"])
    for i, c in enumerate(cats[:min(top, 4)]):
        vc = c["value_counts"]
        charts.append({
            "id": f"cat{i}",
            "aria": f"Value counts for {c['name']}.",
            "note": f"{c['name']} — {c['n_distinct']} distinct",
            "half": True,
            "cfg": {
                "type": "bar",
                "data": {"labels": [str(k) for k, _ in vc],
                         "datasets": [{"data": [val for _, val in vc],
                                       "backgroundColor": [SERIES[j % len(SERIES)] for j in range(len(vc))],
                                       "borderRadius": 4, "borderSkipped": False}]},
                "options": _hbar_opts("count", height=max(160, 24 * len(vc) + 50)),
            },
        })
    return charts


def _hbar_opts(x_title, xmin=None, xmax=None, pct=False, height=300):
    x = {"title": {"display": True, "text": x_title, "color": "#898781",
                   "font": {"size": 11}},
         "ticks": {"color": "#898781"}, "grid": {"color": "#e1e0d9"}}
    if xmin is not None:
        x["min"] = xmin
    if xmax is not None:
        x["max"] = xmax
    if pct:
        x["ticks"]["callback"] = "__PCT__"
    return {"indexAxis": "y", "responsive": True, "maintainAspectRatio": False,
            "plugins": {"legend": {"display": False}},
            "scales": {"x": x,
                       "y": {"ticks": {"color": "#52514e", "font": {"size": 12}},
                             "grid": {"display": False}}},
            "__height__": height}


def _vbar_opts(height=220):
    return {"responsive": True, "maintainAspectRatio": False,
            "plugins": {"legend": {"display": False}},
            "scales": {"x": {"ticks": {"color": "#898781", "maxTicksLimit": 6,
                                       "maxRotation": 0, "font": {"size": 10}},
                             "grid": {"display": False}},
                       "y": {"ticks": {"color": "#898781"}, "grid": {"color": "#e1e0d9"},
                             "type": "logarithmic"}},
            "__height__": height}


def render(data, charts):
    def js(cfg):
        h = cfg["options"].pop("__height__", 300)
        s = json.dumps(cfg)
        s = s.replace('"__PCT__"', "(v)=>v+'%'")
        return s, h

    cards = []
    specs = []
    for c in charts:
        cfg_json, h = js(c["cfg"])
        cls = "card half" if c.get("half") else "card"
        cards.append(f"""<div class="{cls}">
<div class="note">{html.escape(c['note'])}</div>
<div class="wrap" style="height:{h}px"><canvas id="{c['id']}" role="img" aria-label="{html.escape(c['aria'])}"></canvas></div>
</div>""")
        specs.append(f'["{c["id"]}",{cfg_json}]')
    init = ("<script>function __draw(){if(typeof Chart===\"undefined\"){"
            "document.querySelectorAll('.wrap').forEach(w=>w.innerHTML="
            "'<div style=\\'color:var(--mut);font-size:13px;padding:20px 0\\'>chart library did not load</div>');return;}"
            "[" + ",".join(specs) + "].forEach(([id,cfg])=>"
            "new Chart(document.getElementById(id),cfg));}"
            "if(window.Chart)__draw();else window.addEventListener('load',__draw);</script>")

    alerts = "".join(f"<li>{html.escape(str(a))}</li>" for a in data["alerts"][:12])
    miss_line = (f' · {data["p_cells_missing"]}% cells missing'
                 if data.get("p_cells_missing") else "")
    rows = f'{data["n_rows"]:,}' if data.get("n_rows") else "?"

    return f"""<!DOCTYPE html><html lang="en"><head><meta charset="utf-8">
<meta name="viewport" content="width=device-width, initial-scale=1">
<title>Findings — {html.escape(str(data['title']))}</title>
<script src="{CDN}"></script>
<style>
:root{{--fg:#0b0b0b;--fg2:#52514e;--mut:#898781;--bg:#fcfcfb;--card:#fff;--bd:#e1e0d9}}
@media(prefers-color-scheme:dark){{:root{{--fg:#fff;--fg2:#c3c2b7;--mut:#898781;--bg:#161615;--card:#1e1e1c;--bd:#2c2c2a}}}}
*{{box-sizing:border-box}}
body{{margin:0;padding:28px;background:var(--bg);color:var(--fg);
font-family:-apple-system,BlinkMacSystemFont,"Segoe UI",Roboto,sans-serif;line-height:1.5}}
.head{{max-width:1080px;margin:0 auto 20px}}
h1{{font-size:22px;font-weight:500;margin:0 0 4px}}
.sub{{color:var(--mut);font-size:13px}}
.grid{{max-width:1080px;margin:0 auto;display:grid;grid-template-columns:repeat(2,minmax(0,1fr));gap:16px}}
.card{{grid-column:span 2;background:var(--card);border:.5px solid var(--bd);border-radius:12px;padding:14px 16px}}
.card.half{{grid-column:span 1}}
.note{{font-size:13px;color:var(--fg2);margin-bottom:8px}}
.wrap{{position:relative;width:100%}}
.alerts{{max-width:1080px;margin:20px auto 0;font-size:13px;color:var(--fg2)}}
.alerts h2{{font-size:14px;font-weight:500;color:var(--fg)}}
.alerts ul{{margin:6px 0 0;padding-left:18px}}
@media(max-width:640px){{.card.half{{grid-column:span 2}}}}
</style></head><body>
<div class="head"><h1>{html.escape(str(data['title']))}</h1>
<div class="sub">{rows} rows · {data.get('n_cols','?')} columns{miss_line}</div></div>
<div class="grid">
{''.join(cards)}
</div>
{f'<div class="alerts"><h2>Quality alerts</h2><ul>{alerts}</ul></div>' if alerts else ''}
{init}
</body></html>"""


def main():
    ap = argparse.ArgumentParser()
    ap.add_argument("report", help="eda_report.json or profile_large --json output")
    ap.add_argument("--out", default="/mnt/user-data/outputs/eda_findings.html")
    ap.add_argument("--top", type=int, default=6,
                    help="max charts per category (default 6)")
    a = ap.parse_args()
    data = load(a.report)
    charts = build_charts(data, a.top)
    if not charts:
        sys.exit("No chartable findings in report.")
    Path(a.out).write_text(render(data, charts))
    print(f"\u2713 Findings dashboard: {a.out}")
    print(f"  {len(charts)} charts from {data.get('n_cols','?')} columns, "
          f"{data.get('n_rows','?'):,} rows" if data.get('n_rows')
          else f"  {len(charts)} charts")


if __name__ == "__main__":
    main()

"""Tests for sketch_ops MinHash duplicate detection.

Coverage:
- row_minhash weighted vs unweighted on rows that share a token set but
  differ in token counts (the case #787 reported)
- rows whose tokens are all distinct: both metrics agree
- cmd_dups end to end over a CSV, both metrics, including the printed label
"""

import contextlib
import io
import os
import sys
import tempfile
import unittest
from pathlib import Path

sys.path.insert(0, os.path.join(os.path.dirname(__file__), "..", "scripts"))

from sketch_ops import cmd_dups, row_minhash

# Same token set {new, york}; counts 2:2 vs 1:1.
# Weighted Jaccard = sum(min)/sum(max) = (1+1)/(2+2) = 0.5.
REPEATED = ["new", "york", "new", "york"]
DISTINCT = ["york", "new"]


class TestRowMinhash(unittest.TestCase):

    def test_unweighted_collapses_repeats(self):
        a = row_minhash(REPEATED, weighted=False)
        b = row_minhash(DISTINCT, weighted=False)
        self.assertEqual(a.jaccard(b), 1.0)

    def test_weighted_separates_by_count(self):
        a = row_minhash(REPEATED, weighted=True)
        b = row_minhash(DISTINCT, weighted=True)
        est = a.jaccard(b)
        self.assertLess(est, 0.9)
        # 128 permutations; ±0.15 is well outside the sampling error here.
        self.assertAlmostEqual(est, 0.5, delta=0.15)

    def test_distinct_tokens_unaffected(self):
        toks = ["alpha", "beta", "gamma", "delta"]
        for weighted in (True, False):
            a = row_minhash(toks, weighted=weighted)
            b = row_minhash(list(reversed(toks)), weighted=weighted)
            self.assertEqual(a.jaccard(b), 1.0, f"weighted={weighted}")

    def test_weighted_default(self):
        self.assertEqual(
            row_minhash(REPEATED).digest().tolist(),
            row_minhash(REPEATED, weighted=True).digest().tolist(),
        )


class TestCmdDups(unittest.TestCase):
    """The two rows are not exact duplicates, so only the sketch separates them."""

    def setUp(self):
        self.tmp = tempfile.TemporaryDirectory()
        self.csv = Path(self.tmp.name) / "counts.csv"
        self.csv.write_text("city\nnew york new york\nyork new\n")

    def tearDown(self):
        self.tmp.cleanup()

    def _run(self, argv):
        buf = io.StringIO()
        with contextlib.redirect_stdout(buf):
            cmd_dups(argv)
        return buf.getvalue()

    def test_weighted_rejects_the_pair(self):
        out = self._run([str(self.csv), "--threshold", "0.9"])
        self.assertIn("0 exact duplicates", out)
        self.assertIn("0 near-duplicate pairs at weighted Jaccard>=0.9", out)

    def test_unweighted_matches_the_pair(self):
        out = self._run([str(self.csv), "--threshold", "0.9", "--unweighted"])
        self.assertIn("1 near-duplicate pairs at Jaccard>=0.9", out)

    def test_exact_duplicates_still_counted(self):
        csv = Path(self.tmp.name) / "exact.csv"
        csv.write_text("city\nnew york new york\nnew york new york\n")
        out = self._run([str(csv)])
        self.assertIn("1 exact duplicates", out)


if __name__ == "__main__":
    unittest.main()