Student Marks CSV Analyst

<!doctype html>
<html lang="en">
<head>
  <meta charset="utf-8">
  <meta name="viewport" content="width=device-width, initial-scale=1">
  <title>{{ agent_name }}</title>
  <style>{{ css_code }}</style>
</head>
<body>
  <main class="app-shell">
    <header class="masthead">
      <div class="eyebrow">📊 CSV DATA WORKBENCH</div>
      <h1>{{ agent_name }}</h1>
      <p>{{ description }}</p>
    </header>
    <div class="workspace">
      <section class="panel input-panel" aria-labelledby="input-title">
        <div class="panel-heading"><div><span class="eyebrow">01 / INPUT</span><h2 id="input-title">Student marks or your CSV</h2></div></div>
        <p class="hint">The included example has eight students. Your CSV needs a header row and comma-separated values.</p>
        <form id="csv-form">
          <label for="csv-input">CSV dataset</label>
          <textarea id="csv-input" spellcheck="false" aria-describedby="csv-hint"></textarea>
          <p id="csv-hint" class="hint">Up to 1,000 data rows and 30 columns. Empty cells count as missing.</p>
          <div class="input-actions">
            <button id="analyze-button" class="primary" type="submit">Analyze CSV</button>
            <button id="sample-button" class="secondary" type="button">Load student sample</button>
          </div>
          <label for="csv-file" class="upload-label">Upload a CSV file</label>
          <input id="csv-file" type="file" accept=".csv,text/csv,text/plain">
        </form>
        <p id="status" class="status" role="status">Initializing the CSV analyst…</p>
        <p id="error" class="error hidden" role="alert"></p>
      </section>
      <section class="panel output-panel" aria-labelledby="output-title">
        <div class="panel-heading"><div><span class="eyebrow">02 / ANALYSIS</span><h2 id="output-title">Calculated results</h2></div></div>
        <div id="results-container" aria-live="polite">
          <div class="empty-state"><div class="empty-icon">📈</div><h3>Discover what your CSV contains</h3><p>Analyze the sample or add your own dataset to see:</p><ul><li>Detected column types</li><li>Calculated numeric summaries</li><li>Missing-value counts</li><li>Three data-backed insights</li></ul></div>
        </div>
      </section>
    </div>
  </main>
  <div id="loading-overlay" class="loading-overlay hidden" role="status" aria-live="polite"><div class="loading-card"><div class="spinner"></div><strong>Calculating CSV results…</strong></div></div>
  <script>{{ js_code }}</script>
</body>
</html>

:root{--ink:#173042;--muted:#61778a;--navy:#122b3b;--teal:#087d77;--teal-dark:#075f5c;--mint:#e9f6f2;--paper:#f5f8fa;--surface:#fff;--line:#dbe5ea;--danger:#aa3442;--radius:18px;--shadow:0 16px 40px rgba(20,49,67,.09);--font:Inter,ui-sans-serif,system-ui,-apple-system,BlinkMacSystemFont,"Segoe UI",sans-serif}*{box-sizing:border-box}body{margin:0;background:var(--paper);color:var(--ink);font-family:var(--font);line-height:1.5}.hidden{display:none!important}.app-shell{max-width:1500px;margin:0 auto;padding:32px clamp(16px,3vw,46px) 64px}.masthead{padding:20px 4px 28px}.masthead h1{font-size:clamp(2rem,3.5vw,3.3rem);letter-spacing:-.045em;margin:7px 0}.masthead p{color:var(--muted);max-width:780px;margin:0}.eyebrow{font-size:.73rem;font-weight:800;letter-spacing:.13em;color:var(--teal)}.workspace{display:grid;grid-template-columns:minmax(330px,.85fr) minmax(0,1.45fr);gap:22px;align-items:start}.panel{background:var(--surface);border:1px solid var(--line);border-radius:var(--radius);box-shadow:var(--shadow);padding:26px;min-width:0}.panel-heading{border-bottom:1px solid var(--line);padding-bottom:15px;margin-bottom:17px}h2{font-size:1.35rem;letter-spacing:-.025em;margin:5px 0 0}h3{margin:0 0 8px}.hint{font-size:.86rem;color:var(--muted);margin:8px 0 16px}label{display:block;font-weight:700;font-size:.87rem;margin:16px 0 8px}textarea{width:100%;min-height:360px;resize:vertical;padding:15px;border:1px solid var(--line);border-radius:12px;background:#f9fbfc;color:var(--ink);font:13px/1.6 ui-monospace,SFMono-Regular,Consolas,monospace}input[type=file]{width:100%;font-size:.85rem;color:var(--muted)}.input-actions{display:flex;flex-wrap:wrap;gap:10px;margin-top:18px}button{border:1px solid transparent;border-radius:11px;padding:11px 17px;font:700 .9rem var(--font);cursor:pointer;transition:background .15s,transform .15s}button:hover:not(:disabled){transform:translateY(-1px)}button:disabled{opacity:.52;cursor:not-allowed}.primary{background:var(--teal);color:white}.primary:hover:not(:disabled){background:var(--teal-dark)}.secondary{background:var(--mint);color:var(--teal-dark);border-color:#c5e6de}:focus-visible{outline:3px solid #e3a64e;outline-offset:3px}.status{color:var(--muted);font-size:.85rem;margin:18px 0 0}.error{background:#fff0f1;border:1px solid #f0c3c8;color:var(--danger);padding:11px 13px;border-radius:10px;font-size:.88rem}.empty-state{padding:55px 25px;text-align:center;color:var(--muted)}.empty-state h3{color:var(--ink)}.empty-icon{font-size:2.6rem;margin-bottom:13px}.empty-state ul{text-align:left;display:inline-block;line-height:1.9;padding-left:20px}.metric-grid{display:grid;grid-template-columns:repeat(3,minmax(0,1fr));gap:11px;margin-bottom:25px}.metric{border-radius:12px;background:var(--mint);padding:16px}.metric b{display:block;font-size:1.55rem;color:var(--navy)}.metric span{font-size:.77rem;color:var(--teal-dark);font-weight:700}.result-section{margin-top:25px}.result-section h3{font-size:1rem;letter-spacing:-.01em}.insight-grid{display:grid;grid-template-columns:repeat(3,minmax(0,1fr));gap:11px}.insight{background:#f4f9fa;border-left:3px solid var(--teal);padding:14px;border-radius:9px;font-size:.86rem}.insight strong{display:block;color:var(--teal-dark);margin-bottom:6px}.table-wrap{overflow-x:auto;border:1px solid var(--line);border-radius:11px}table{width:100%;border-collapse:collapse;font-size:.82rem;white-space:nowrap;text-align:left}th,td{padding:10px 12px;border-bottom:1px solid var(--line)}th{background:#eef5f7;color:var(--navy);font-weight:750}tr:last-child td{border-bottom:0}.type-pill{display:inline-block;border-radius:20px;padding:3px 9px;background:#e8f2ef;color:var(--teal-dark);font-weight:700;font-size:.74rem}.missing{color:var(--danger);font-weight:700}.loading-overlay{position:fixed;inset:0;background:rgba(18,43,59,.35);display:grid;place-items:center;z-index:20}.loading-card{display:flex;align-items:center;gap:13px;background:white;padding:20px 25px;border-radius:14px;box-shadow:var(--shadow)}.spinner{width:22px;height:22px;border:3px solid #c8e5df;border-top-color:var(--teal);border-radius:50%;animation:spin .7s linear infinite}@keyframes spin{to{transform:rotate(360deg)}}@media(max-width:1200px){.workspace{grid-template-columns:minmax(300px,1fr) minmax(0,1.2fr)}.insight-grid{grid-template-columns:1fr}}@media(max-width:768px){.app-shell{padding:20px 14px 45px}.workspace{grid-template-columns:1fr}.panel{padding:18px}.metric-grid{grid-template-columns:repeat(3,minmax(0,1fr))}.metric{padding:10px}.metric b{font-size:1.2rem}textarea{min-height:270px}}

(() => {
  const sample = 'Student,Math,Science,English,Attendance,Section\nAva,88,92,85,96,A\nNoah,76,81,79,90,B\nMia,95,89,93,98,A\nLiam,68,,72,82,B\nZoe,84,78,88,94,A\nEthan,72,75,70,85,B\nIvy,91,94,90,97,A\nOmar,79,83,,88,B';
  const $ = id => document.getElementById(id);
  const input = $('csv-input'), form = $('csv-form'), button = $('analyze-button'), sampleButton = $('sample-button'), file = $('csv-file'), results = $('results-container'), status = $('status'), error = $('error'), overlay = $('loading-overlay');
  let ready = false, busy = false;
  input.value = sample;
  function escapeHtml(value) { return String(value ?? '').replace(/[&<>"']/g, c => ({'&':'&amp;','<':'&lt;','>':'&gt;','"':'&quot;',"'":'&#39;'}[c])); }
  function isLocal() { const provider = String(window.PROVIDER || '').toLowerCase(); return provider === 'local' || provider.includes('wllama'); }
  function modelLoaded() { return !isLocal() || !!(window.agentManager && window.agentManager.isLoaded); }
  function setError(message) { error.textContent = message; error.classList.toggle('hidden', !message); }
  function updateStatus() {
    if (!ready) status.textContent = 'Initializing the CSV analyst…';
    else if (!modelLoaded()) status.textContent = 'Load a model from the top bar before analyzing your CSV.';
    else status.textContent = 'Ready to calculate results from your CSV.';
    button.disabled = busy || !ready || !modelLoaded();
  }
  function fmt(value) { return typeof value === 'number' ? Number(value).toLocaleString(undefined, {maximumFractionDigits:2}) : escapeHtml(value); }
  function render(data) {
    const metric = (label, value) => `<div class="metric"><b>${escapeHtml(value)}</b><span>${escapeHtml(label)}</span></div>`;
    const insights = data.insights.map((text, i) => `<div class="insight"><strong>Insight ${i + 1}</strong>${escapeHtml(text)}</div>`).join('');
    const colRows = data.columns.map(c => `<tr><td>${escapeHtml(c.name)}</td><td><span class="type-pill">${escapeHtml(c.type)}</span></td><td>${fmt(c.unique)}</td><td class="${c.missing ? 'missing' : ''}">${fmt(c.missing)} (${fmt(c.missing_pct)}%)</td></tr>`).join('');
    const numeric = data.columns.filter(c => c.type === 'numeric');
    const numericRows = numeric.map(c => `<tr><td>${escapeHtml(c.name)}</td><td>${fmt(c.stats.count)}</td><td>${fmt(c.stats.mean)}</td><td>${fmt(c.stats.median)}</td><td>${fmt(c.stats.min)}</td><td>${fmt(c.stats.max)}</td><td>${fmt(c.stats.range)}</td></tr>`).join('');
    const previewHead = data.columns.map(c => `<th>${escapeHtml(c.name)}</th>`).join('');
    const previewRows = data.preview.map(row => `<tr>${row.map(cell => `<td>${cell === '' ? '<span class="missing">—</span>' : escapeHtml(cell)}</td>`).join('')}</tr>`).join('');
    results.innerHTML = `<div class="metric-grid">${metric('Data rows', data.row_count)}${metric('Columns', data.column_count)}${metric('Missing cells', data.missing_cells)}</div><section class="result-section"><h3>Three data-backed insights</h3><div class="insight-grid">${insights}</div></section><section class="result-section"><h3>Column types & missing values</h3><div class="table-wrap"><table><thead><tr><th>Column</th><th>Detected type</th><th>Distinct values</th><th>Missing</th></tr></thead><tbody>${colRows}</tbody></table></div></section>${numeric.length ? `<section class="result-section"><h3>Numeric summary statistics</h3><div class="table-wrap"><table><thead><tr><th>Column</th><th>Present</th><th>Mean</th><th>Median</th><th>Min</th><th>Max</th><th>Range</th></tr></thead><tbody>${numericRows}</tbody></table></div><p class="hint">Statistics exclude empty cells. Values displayed to two decimal places.</p></section>` : ''}<section class="result-section"><h3>CSV data preview · first ${data.preview.length} rows</h3><div class="table-wrap"><table><thead><tr>${previewHead}</tr></thead><tbody>${previewRows}</tbody></table></div></section>`;
  }
  async function analyze(event) {
    event.preventDefault();
    const csv = input.value.trim();
    setError('');
    if (!csv) { setError('Paste a CSV dataset or load the student sample first.'); return; }
    if (csv.length > 100000) { setError('This CSV is too large. Use a dataset under 100,000 characters.'); return; }
    if (!ready) { setError('The CSV analyst is still initializing. Please try again shortly.'); return; }
    if (!modelLoaded()) { setError('Load a model from the top bar before analyzing your CSV.'); updateStatus(); return; }
    busy = true; button.disabled = true; sampleButton.disabled = true; file.disabled = true; overlay.classList.remove('hidden'); status.textContent = 'Calculating column summaries and insights…';
    try {
      window.pyodide.globals.set('csv_payload', csv);
      const raw = await window.pyodide.runPythonAsync('analyze_csv(csv_payload)');
      const data = JSON.parse(String(raw));
      render(data);
      status.textContent = `Calculated results for ${data.row_count} CSV rows.`;
    } catch (e) {
      setError('Unable to analyze this CSV: ' + (e && e.message ? e.message : String(e)));
      status.textContent = 'CSV analysis failed. Check the dataset and try again.';
    } finally {
      busy = false; sampleButton.disabled = false; file.disabled = false; overlay.classList.add('hidden'); button.disabled = !ready || !modelLoaded();
    }
  }
  form.addEventListener('submit', analyze);
  sampleButton.addEventListener('click', () => { input.value = sample; file.value = ''; setError(''); status.textContent = 'Student-marks sample loaded. Select Analyze CSV to calculate its results.'; input.focus(); });
  file.addEventListener('change', async () => {
    const selected = file.files && file.files[0]; if (!selected) return;
    setError('');
    if (selected.size > 100000) { setError('The CSV file is too large. Choose one under 100 KB.'); file.value = ''; return; }
    try { input.value = (await selected.text()).replace(/^\uFEFF/, ''); status.textContent = 'CSV file loaded. Select Analyze CSV to calculate its results.'; }
    catch (e) { setError('Could not read the CSV file. Please paste its contents instead.'); }
  });
  document.addEventListener('pyodide-ready', () => { ready = true; updateStatus(); });
  if (window.pyodideReady) { ready = true; updateStatus(); }
  const poll = setInterval(() => { if (window.pyodideReady && !ready) ready = true; updateStatus(); }, 1200);
  window.addEventListener('pagehide', () => clearInterval(poll));
  updateStatus();
})();

import csv
import io
import json
import math
import statistics
from datetime import date


def _kind(values: list[str], row_count: int) -> str:
    if not values:
        return 'empty'
    try:
        numbers = [float(value) for value in values]
        if all(math.isfinite(number) for number in numbers):
            return 'numeric'
    except ValueError:
        pass
    try:
        for value in values:
            date.fromisoformat(value)
        return 'date'
    except ValueError:
        pass
    distinct = len(set(values))
    if distinct < row_count and distinct <= min(12, max(2, row_count // 2)):
        return 'categorical'
    return 'text'


def analyze_csv(csv_text: str) -> str:
    """Calculate CSV column types, statistics, missing values, and three grounded insights.

    Args:
        csv_text: Comma-separated dataset with a header row and up to 1000 data rows.
    """
    if len(csv_text) > 100000:
        raise ValueError('CSV exceeds the 100,000-character limit.')
    try:
        records = list(csv.reader(io.StringIO(csv_text.lstrip('\ufeff'), newline=''), strict=True))
    except csv.Error as exc:
        raise ValueError('CSV syntax error: ' + str(exc)) from exc
    records = [row for row in records if any(cell.strip() for cell in row)]
    if len(records) < 2:
        raise ValueError('Provide a header row and at least one data row.')
    names = [cell.strip() for cell in records[0]]
    if not names or len(names) > 30 or any(not name for name in names):
        raise ValueError('Use 1–30 columns, each with a nonempty header.')
    if len(set(names)) != len(names):
        raise ValueError('CSV column headers must be unique.')
    rows = [[cell.strip() for cell in row] for row in records[1:]]
    if len(rows) > 1000:
        raise ValueError('Use no more than 1,000 data rows.')
    if any(len(row) != len(names) for row in rows):
        raise ValueError('Every data row must have the same number of cells as the header.')
    columns = []
    for index, name in enumerate(names):
        all_values = [row[index] for row in rows]
        present = [value for value in all_values if value != '']
        missing = len(rows) - len(present)
        column = {'name': name, 'type': _kind(present, len(rows)),
                  'missing': missing, 'missing_pct': round(100 * missing / len(rows), 2),
                  'unique': len(set(present))}
        if column['type'] == 'numeric':
            numbers = [float(value) for value in present]
            low, high = min(numbers), max(numbers)
            column['stats'] = {'count': len(numbers), 'mean': round(statistics.mean(numbers), 2),
                               'median': round(statistics.median(numbers), 2),
                               'min': round(low, 2), 'max': round(high, 2),
                               'range': round(high - low, 2)}
        columns.append(column)
    missing_total = sum(col['missing'] for col in columns)
    if missing_total:
        most_missing = max(columns, key=lambda col: col['missing'])
        first = (f"{missing_total} of {len(rows) * len(names)} cells are missing "
                 f"({100 * missing_total / (len(rows) * len(names)):.1f}%). "
                 f"{most_missing['name']} has the most: {most_missing['missing']} "
                 f"({most_missing['missing_pct']:.1f}% of rows).")
    else:
        first = f"No missing values: all {len(rows) * len(names)} cells are filled."
    numeric = [col for col in columns if col['type'] == 'numeric']
    if len(numeric) >= 2:
        top = max(numeric, key=lambda col: col['stats']['mean'])
        bottom = min(numeric, key=lambda col: col['stats']['mean'])
        second = (f"Among numeric columns, {top['name']} has the highest observed mean "
                  f"({top['stats']['mean']:.2f}); {bottom['name']} has the lowest "
                  f"({bottom['stats']['mean']:.2f}). Compare means only if their units match.")
    elif numeric:
        col = numeric[0]
        second = (f"{col['name']} averages {col['stats']['mean']:.2f} across "
                  f"{col['stats']['count']} nonempty values (median {col['stats']['median']:.2f}).")
    else:
        second = f"This dataset has {len(rows)} rows and no fully numeric columns for numeric summaries."
    if numeric:
        widest = max(numeric, key=lambda col: col['stats']['range'])
        third = (f"{widest['name']} has the widest observed numeric range: "
                 f"{widest['stats']['min']:.2f} to {widest['stats']['max']:.2f} "
                 f"(difference {widest['stats']['range']:.2f}).")
    else:
        varied = max(columns, key=lambda col: col['unique'])
        third = (f"{varied['name']} has {varied['unique']} distinct nonempty values, "
                 f"the highest distinct-value count among the columns.")
    result = {'row_count': len(rows), 'column_count': len(names),
              'missing_cells': missing_total, 'columns': columns,
              'insights': [first, second, third], 'preview': rows[:8]}
    return json.dumps(result, ensure_ascii=False, allow_nan=False)