Data Analysis Skill

Overview

This skill analyzes user-uploaded Excel/CSV files using DuckDB — an in-process analytical SQL engine. It supports schema inspection, SQL-based querying, statistical summaries, and result export, all through a single Python script.

Environment setup

Prepare the Python environment before running analyses. Install the required packages explicitly using the same interpreter that will run the script:

python -m pip install duckdb openpyxl

If either package is missing, the script exits with a setup instruction; it does not invoke an installer or modify the environment. DuckDB's spatial extension is initialized only for .xlsx input. CSV and legacy .xls analysis do not install or load it, so they can run offline with an empty extension cache.

Legacy Excel 97–2003 (.xls) workbooks additionally require:

python -m pip install pandas xlrd

These optional packages are loaded only for .xls input. A missing package produces an explicit setup instruction. Legacy workbooks are read sheet by sheet with pandas' xlrd engine, preserving cell values (including literal NA strings and leading zeros in text identifiers) and treating empty cells as nulls. Columns with incompatible cell types, such as a date and a number, are stored as text with nulls preserved. Compatible numeric types still produce numeric columns. Completely empty sheets are skipped. The .xlsx reader continues to use openpyxl and DuckDB's spatial extension. A legacy workbook that cannot be opened is reported and skipped so remaining inputs can still be analyzed. The command fails if no input produces a table.

Core Capabilities

• Inspect Excel/CSV file structure (sheets, columns, types, row counts)
• Execute arbitrary SQL queries against uploaded data
• Generate statistical summaries (mean, median, stddev, percentiles, nulls)
• Support multi-sheet Excel workbooks (each sheet becomes a table)
• Export query results to CSV, JSON, or Markdown
• Handle large files efficiently with DuckDB's columnar engine

Workflow

Step 1: Understand Requirements

When a user uploads data files and requests analysis, identify:

• File location: Path(s) to uploaded Excel/CSV files under /mnt/user-data/uploads/
• Analysis goal: What insights the user wants (summary, filtering, aggregation, comparison, etc.)
• Output format: How results should be presented (table, CSV export, JSON, etc.)
• You don't need to check the folder under /mnt/user-data

Step 2: Inspect File Structure

First, inspect the uploaded file to understand its schema:

python /mnt/skills/public/data-analysis/scripts/analyze.py \
  --files /mnt/user-data/uploads/data.xlsx \
  --action inspect

This returns:

• Sheet names (for Excel) or filename (for CSV)
• Column names, data types, and non-null counts
• Row count per sheet/file
• Sample data (first 5 rows)

Step 3: Perform Analysis

Based on the schema, construct SQL queries to answer the user's questions.

Run SQL Query

python /mnt/skills/public/data-analysis/scripts/analyze.py \
  --files /mnt/user-data/uploads/data.xlsx \
  --action query \
  --sql "SELECT category, COUNT(*) as count, AVG(amount) as avg_amount FROM Sheet1 GROUP BY category ORDER BY count DESC"

Generate Statistical Summary

python /mnt/skills/public/data-analysis/scripts/analyze.py \
  --files /mnt/user-data/uploads/data.xlsx \
  --action summary \
  --table Sheet1

This returns for each numeric column: count, mean, std, min, 25%, 50%, 75%, max, null_count. For string columns: count, unique, top value, frequency, null_count.

Export Results

python /mnt/skills/public/data-analysis/scripts/analyze.py \
  --files /mnt/user-data/uploads/data.xlsx \
  --action query \
  --sql "SELECT * FROM Sheet1 WHERE amount > 1000" \
  --output-file /mnt/user-data/outputs/filtered-results.csv

Supported output formats (auto-detected from extension):

• .csv — Comma-separated values
• .json — JSON array of records
• .md — Markdown table

Relative output paths are resolved from the current working directory. A bare filename such as report.csv writes there directly; missing parent directories in paths such as reports/report.csv are created automatically. An existing file at the target path is overwritten without warning.

JSON result column names must be unique. Use explicit SQL aliases for repeated names, for example SELECT a.id AS left_id, b.id AS right_id FROM .... Duplicate names cause an error and leave any existing output file untouched. The error lists the repeated names so you can alias them directly. The CLI prints a short Error: ... message to stderr and exits nonzero for this rejection.

JSON exports also reject non-finite numbers (NaN, Infinity, and -Infinity), including values inside lists or structs, before opening the output file. Filter or replace them in SQL, for example CASE WHEN isfinite(ratio) THEN ratio ELSE NULL END AS ratio. Finite numbers, nulls, and text such as 'NaN' remain valid.

Parameters

[!NOTE] Do NOT read the Python file, just call it with the parameters.

Table Naming Rules

• Excel files: Each sheet becomes a table named after the sheet (e.g., Sheet1, Sales, Revenue)
• CSV files: Table name is the filename without extension (e.g., data.csv → data)
• Multiple files: All tables from all files are available in the same query context, enabling cross-file joins
• Collisions: Repeated names (including case-only differences) receive unique SQL names in input order, such as report, report_1, report_2. This also applies to repeated Excel sheet names. Use the SQL names shown by inspect for queries and summaries. An original-name alias never overrides an earlier SQL name.
• Special characters: Sheet/file names are sanitized into SQL names (for example, sales-2026 → sales_2026). Use the SQL names printed by inspect, or double-quote a unique original name, e.g., SELECT * FROM "sales-2026". Queries execute unchanged: string literals, column aliases, comments, and CTEs retain normal DuckDB semantics. Names containing a double quote escape it by doubling it, e.g., "net""amount"

Analysis Patterns

Basic Exploration

-- Row count
SELECT COUNT(*) FROM Sheet1

-- Distinct values in a column
SELECT DISTINCT category FROM Sheet1

-- Value distribution
SELECT category, COUNT(*) as cnt FROM Sheet1 GROUP BY category ORDER BY cnt DESC

-- Date range
SELECT MIN(date_col), MAX(date_col) FROM Sheet1

Aggregation & Grouping

-- Revenue by category and month
SELECT category, DATE_TRUNC('month', order_date) as month,
       SUM(revenue) as total_revenue
FROM Sales
GROUP BY category, month
ORDER BY month, total_revenue DESC

-- Top 10 customers by spend
SELECT customer_name, SUM(amount) as total_spend
FROM Orders GROUP BY customer_name
ORDER BY total_spend DESC LIMIT 10

Cross-file Joins

-- Join sales with customer info from different files
SELECT s.order_id, s.amount, c.customer_name, c.region
FROM sales s
JOIN customers c ON s.customer_id = c.id
WHERE s.amount > 500

Window Functions

-- Running total and rank
SELECT order_date, amount,
       SUM(amount) OVER (ORDER BY order_date) as running_total,
       RANK() OVER (ORDER BY amount DESC) as amount_rank
FROM Sales

Pivot-style Analysis

-- Pivot: monthly revenue by category
SELECT category,
       SUM(CASE WHEN MONTH(date) = 1 THEN revenue END) as Jan,
       SUM(CASE WHEN MONTH(date) = 2 THEN revenue END) as Feb,
       SUM(CASE WHEN MONTH(date) = 3 THEN revenue END) as Mar
FROM Sales
GROUP BY category

Complete Example

User uploads sales_2024.xlsx (with sheets: Orders, Products, Customers) and asks: "Analyze my sales data — show top products by revenue and monthly trends."

Step 1: Inspect the file

python /mnt/skills/public/data-analysis/scripts/analyze.py \
  --files /mnt/user-data/uploads/sales_2024.xlsx \
  --action inspect

Step 2: Top products by revenue

python /mnt/skills/public/data-analysis/scripts/analyze.py \
  --files /mnt/user-data/uploads/sales_2024.xlsx \
  --action query \
  --sql "SELECT p.product_name, SUM(o.quantity * o.unit_price) as total_revenue, SUM(o.quantity) as total_units FROM Orders o JOIN Products p ON o.product_id = p.id GROUP BY p.product_name ORDER BY total_revenue DESC LIMIT 10"

Step 3: Monthly revenue trends

python /mnt/skills/public/data-analysis/scripts/analyze.py \
  --files /mnt/user-data/uploads/sales_2024.xlsx \
  --action query \
  --sql "SELECT DATE_TRUNC('month', order_date) as month, SUM(quantity * unit_price) as revenue FROM Orders GROUP BY month ORDER BY month" \
  --output-file /mnt/user-data/outputs/monthly-trends.csv

Step 4: Statistical summary

python /mnt/skills/public/data-analysis/scripts/analyze.py \
  --files /mnt/user-data/uploads/sales_2024.xlsx \
  --action summary \
  --table Orders

Present results to the user with clear explanations of findings, trends, and actionable insights.

Multi-file Example

User uploads orders.csv and customers.xlsx and asks: "Which region has the highest average order value?"

python /mnt/skills/public/data-analysis/scripts/analyze.py \
  --files /mnt/user-data/uploads/orders.csv /mnt/user-data/uploads/customers.xlsx \
  --action query \
  --sql "SELECT c.region, AVG(o.amount) as avg_order_value, COUNT(*) as order_count FROM orders o JOIN Customers c ON o.customer_id = c.id GROUP BY c.region ORDER BY avg_order_value DESC"

Output Handling

After analysis:

• Present query results directly in conversation as formatted tables
• For large results, export to file and share via present_files tool
• Always explain findings in plain language with key takeaways
• Suggest follow-up analyses when patterns are interesting
• Offer to export results if the user wants to keep them

Caching

The script automatically caches loaded data to avoid re-parsing files on every call:

• On first load, files are parsed into a private DuckDB generation under the system temporary directory (.data-analysis-cache/generations-v1/). Its writer closes before a complete generation and table map become visible together through an atomic manifest replacement
• The cache key hashes the ordered input basenames and a separate SHA256 digest of each file. Renaming, reordering, or changing inputs creates a new cache; moving unchanged, identically named inputs to another directory preserves reuse. Older flat caches are ignored and rebuilt from source without deleting the old files
• Subsequent calls open a complete generation read-only. Overlapping readers can reuse it while another analysis is still running; invalid manifests or damaged databases rebuild from source
• Cache is transparent — no extra parameters needed

Concurrent cold calls may build separate complete generations. Failed private builds are removed, while complete generations are retained so active readers remain valid. Stop analyses before manually clearing .data-analysis-cache/; automatic cache garbage collection is not implemented. These are rebuildable local caches, not snapshots of changing input files or a power-loss guarantee. Analysis connections are read-only; export query results with --output-file.

This is especially useful when running multiple queries against the same data files (inspect → query → summary).

Notes

• DuckDB supports full SQL including window functions, CTEs, subqueries, and advanced aggregations
• Excel date columns are automatically parsed; use DuckDB date functions (DATE_TRUNC, EXTRACT, etc.)
• For very large files (100MB+), DuckDB handles them efficiently without loading everything into memory
• Column names with spaces are accessible using double quotes: "Column Name"