data-wrangler

Data Wrangler

Manipulate tabular data (CSV, Excel, JSON, Parquet, TSV) w/ pandas-powered scripts. Two scripts cover all operations: data_wrangler.py for data ops, excel_toolkit.py for Excel-specific features.

When to Use

• User asks to read, edit, filter, sort, or transform CSV/Excel/JSON/Parquet/TSV files
• User asks to merge/join datasets, deduplicate, fill missing values, or validate data
• User asks to create Excel workbooks w/ formatting, dropdowns, freeze panes, or multi-sheet
• User asks to pivot, unpivot, group-by, aggregate, sample, or split datasets
• User asks to add computed columns, rename columns, cast types, or apply formulas
• User asks to convert between data formats (CSV -> Excel, JSON -> Parquet, etc.)
• User asks to inspect/profile data structure, types, nulls, stats

Prerequisites

# Required
pip install pandas openpyxl

# Optional (per feature)
pip install pyarrow          # Parquet support
pip install xlrd             # Legacy .xls read
pip install pandasql         # SQL queries on DataFrames
pip install fastparquet      # Alternative Parquet engine

Quick Routing

| Task | Script | Command | |------|--------|---------| | Inspect/profile data | data_wrangler.py | inspect | | Filter rows | data_wrangler.py | filter --where "expr" | | Sort by columns | data_wrangler.py | sort --by Col --desc | | Group & aggregate | data_wrangler.py | group --by Col --agg "Col:func" | | Merge/join files | data_wrangler.py | merge f2 --on Key --how left | | Pivot / unpivot | data_wrangler.py | pivot --index/--unpivot | | Remove duplicates | data_wrangler.py | dedupe --subset "Col" | | Fill missing values | data_wrangler.py | fill --column Col --strategy mean | | Drop cols/rows | data_wrangler.py | drop --columns "A,B" | | Rename columns | data_wrangler.py | rename --map "old:new" | | Cast types | data_wrangler.py | cast --column Col --dtype datetime | | Computed columns | data_wrangler.py | derive --formula "New = A + B" | | Random sample | data_wrangler.py | sample --n 100 | | Split by values | data_wrangler.py | split --by Region | | Validate rules | data_wrangler.py | validate --rules rules.json | | Apply formulas | data_wrangler.py | formula --expr "C=A+B" | | Convert formats | data_wrangler.py | convert -o data.xlsx | | SQL queries | data_wrangler.py | query --sql "SELECT..." | | List Excel sheets | excel_toolkit.py | sheets | | Extract sheet | excel_toolkit.py | extract --sheet Sales -o sales.csv | | Combine -> xlsx | excel_toolkit.py | combine *.csv -o combined.xlsx | | Format headers | excel_toolkit.py | format --header-style bold,blue --autowidth | | Freeze panes | excel_toolkit.py | freeze --at B2 | | Auto-filter | excel_toolkit.py | autofilter | | Dropdown validation | excel_toolkit.py | validate --column Status --values "Open,Closed" | | Protect sheet | excel_toolkit.py | protect --password secret | | Create workbook | excel_toolkit.py | create --columns "Name,Age" -o template.xlsx |

Usage Patterns

Data Operations (data_wrangler.py)

All operations follow: python3 scripts/data_wrangler.py <op> <input> [options] [-o output]

# Inspect
python3 data_wrangler.py inspect sales.csv
python3 data_wrangler.py inspect data.xlsx --sheet "Q1 Sales" --nrows 1000

# Filter
python3 data_wrangler.py filter data.csv --where "Revenue > 10000" -o high_rev.csv
python3 data_wrangler.py filter data.csv --where 'Status == "active" and Age >= 25' -o active.csv

# Sort
python3 data_wrangler.py sort data.csv --by "Revenue,Name" --desc -o sorted.csv

# Group + Aggregate
python3 data_wrangler.py group data.csv --by Department --agg "Salary:mean,Salary:count,Revenue:sum" -o summary.csv

# Merge
python3 data_wrangler.py merge orders.csv customers.csv --on CustomerID --how left -o joined.csv

# Pivot
python3 data_wrangler.py pivot data.csv --index Name --columns Month --values Sales --aggfunc sum -o pivoted.csv

# Unpivot (melt)
python3 data_wrangler.py pivot wide.csv --index ID --unpivot --var-name Metric --value-name Value -o long.csv

# Deduplicate
python3 data_wrangler.py dedupe data.csv --subset "Email" --keep first -o clean.csv

# Fill nulls
python3 data_wrangler.py fill data.csv --column "Revenue,Profit" --strategy mean -o filled.csv

# Drop columns
python3 data_wrangler.py drop data.csv --columns "TempCol,Notes" -o trimmed.csv
python3 data_wrangler.py drop data.csv --null-threshold 0.5 -o cleaned.csv

# Rename
python3 data_wrangler.py rename data.csv --map "old_name:new_name,col2:Column2" -o renamed.csv
python3 data_wrangler.py rename data.csv --snake -o snake_case.csv

# Cast types
python3 data_wrangler.py cast data.csv --column Date --dtype datetime --date-format "%Y-%m-%d" -o typed.csv

# Computed columns
python3 data_wrangler.py derive data.csv --formula "Profit = Revenue - Cost" -o enriched.csv

# Sample
python3 data_wrangler.py sample large.csv --n 500 --seed 42 -o sample.csv

# Split by value
python3 data_wrangler.py split data.csv --by Region --output-dir ./by_region/

# Validate
python3 data_wrangler.py validate data.csv --rules validation_rules.json -o report.json

# Formula
python3 data_wrangler.py formula data.xlsx --expr "Total=Price*Quantity" -o calculated.xlsx

# Convert
python3 data_wrangler.py convert data.csv -o data.xlsx
python3 data_wrangler.py convert data.xlsx -o data.json
python3 data_wrangler.py convert data.json -o data.parquet

# SQL query
python3 data_wrangler.py query data.csv --sql "SELECT Name, AVG(Salary) FROM df WHERE Dept='Eng' GROUP BY Name"

Excel Operations (excel_toolkit.py)

All operations follow: python3 scripts/excel_toolkit.py <op> <input> [options] [-o output]

# List sheets
python3 excel_toolkit.py sheets workbook.xlsx

# Extract sheet
python3 excel_toolkit.py extract workbook.xlsx --sheet "Sales Q1" -o sales_q1.csv

# Combine multiple files into multi-sheet xlsx
python3 excel_toolkit.py combine sales.csv inventory.csv orders.csv -o report.xlsx

# Format
python3 excel_toolkit.py format data.xlsx --header-style bold,blue --autowidth --zebra -o styled.xlsx

# Freeze panes
python3 excel_toolkit.py freeze data.xlsx --at B2 -o frozen.xlsx

# Auto-filter
python3 excel_toolkit.py autofilter data.xlsx -o filtered.xlsx

# Dropdown validation
python3 excel_toolkit.py validate data.xlsx --column Status --values "Open,Closed,Pending" -o validated.xlsx

# Protect
python3 excel_toolkit.py protect data.xlsx --password mypass -o protected.xlsx

# Create template
python3 excel_toolkit.py create --columns "Name,Email,Department,Start Date,Salary" -o template.xlsx

Validation Rules Format

Create a JSON rules file for validate:

{
  "rules": [
    {"column": "Email", "type": "not_null"},
    {"column": "Email", "type": "pattern", "regex": "^[^@]+@[^@]+\\.[^@]+$"},
    {"column": "ID", "type": "unique"},
    {"column": "Age", "type": "range", "min": 0, "max": 150},
    {"column": "Status", "type": "enum", "values": ["active", "inactive", "pending"]}
  ]
}

Rule types: not_null, unique, range (min/max), pattern (regex), enum (allowed values).

Fill Strategies

| Strategy | Behavior | |----------|----------| | mean | Fill w/ column mean (numeric) | | median | Fill w/ column median (numeric) | | mode | Fill w/ most frequent value | | zero | Fill w/ 0 | | empty | Fill w/ empty string | | ffill | Forward fill (carry last value) | | bfill | Backward fill | | drop | Drop rows w/ nulls in column | | value:<v> | Fill w/ specific value |

Supported Formats

| Format | Read | Write | Dependency | |--------|------|-------|------------| | CSV | Y | Y | (builtin) | | TSV | Y | Y | (builtin) | | XLSX | Y | Y | openpyxl | | XLS | Y | N | xlrd | | JSON | Y | Y | (builtin) | | JSONL | Y | Y | (builtin) | | Parquet | Y | Y | pyarrow |

Integration w/ file-converter

Pipeline data between skills:

# 1. Convert YAML -> CSV (file-converter), then wrangle
python3 .claude/skills/file-converter/scripts/csv_json_yaml.py data.yaml data.csv
python3 .claude/skills/data-wrangler/scripts/data_wrangler.py filter data.csv --where "Status == 'active'" -o filtered.csv

# 2. Wrangle, then convert to PDF report
python3 data_wrangler.py group data.csv --by Dept --agg "Salary:mean,count" -o summary.csv
# (Use file-converter to render summary as markdown -> PDF)

# 3. Excel -> JSON -> YAML pipeline
python3 data_wrangler.py convert data.xlsx -o data.json
python3 .claude/skills/file-converter/scripts/csv_json_yaml.py data.json data.yaml

Pandas Query Syntax Reference

Filter expressions use pandas query syntax:

| Pattern | Example | |---------|---------| | Comparison | Age > 30, Revenue >= 10000 | | Equality | Status == "active", Region != "East" | | String contains | Name.str.contains("Smith") | | Multiple conditions | Age > 25 and Status == "active" | | OR conditions | Region == "East" or Region == "West" | | IN list | Status in ["active", "pending"] | | NOT IN | Status not in ["closed", "archived"] | | Null check | Revenue.notna(), Email.isna() | | Between | Age >= 18 and Age <= 65 |

Aggregation Functions

Available for group --agg and pivot --aggfunc:

sum, mean, median, min, max, count, std, var, first, last, nunique

Spec format: "Column:function" — multiple: "Salary:mean,Salary:count,Revenue:sum"