Data cleaning & reconciliation at scale
Clean, join and reconcile messy datasets to a validated output.
20 project-authored proposals · taxonomy v1.1 · 0 new benchmark runs. Verifiers and partial-credit rules below are designs, not executed results.
- Typical applications
- pandas · SQL · OpenRefine
- Class GUI-need label
CL· CLI-sufficient
Task proposals · 20
Expand to inspect the complete original wording and proposed checks. Original proposal text remains English in both interfaces.
R03.01.02.02-T01 Deduplicate 250k customer records with fuzzy matching
Original instruction
data/customers.parquet has 250k records from 3 CRMs with typos, nicknames, swapped name order and partial addresses. Assign a cluster_id so each real person gets one cluster, and write output/clusters.parquet (record_id, cluster_id) plus a golden record per cluster in output/golden.parquet using the survivorship rules in docs/survivorship.md.
- Platforms
lin· Linuxmac· macOSwin· Windows- Applications
- pandas · Python
- Interfaces
term· Terminal- GUI need · task setting
CL· CLI-sufficient- GUI need · ordinary access
CL· CLI-sufficient- Interaction primitives
code· Code & commands- Work product
WP02· Records/table- Conditions
- None declared
- Proposed difficulty
- 4 / 5
Proposed verifier
Grader computes pairwise precision/recall vs hidden truth clusters: pass at F1 ≥0.92. Golden records checked field by field against survivorship rules on 500 sampled clusters. Runtime ≤30 min on 8 cores.
Proposed partial credit
60% pairwise F1 (linear from 0.80); 30% golden-record rules; 10% runtime
R03.01.02.02-T02 Match bank transactions to ledger entries with tolerances
Original instruction
Reconcile data/bank_2025.csv (12k transactions) against data/ledger_2025.csv for account 1010: match on amount and date within 3 business days, allow one bank line to many ledger lines (batched deposits), and produce output/matches.csv, output/unmatched_bank.csv and output/unmatched_ledger.csv.
- Platforms
lin· Linuxmac· macOSwin· Windows- Applications
- pandas · SQL
- Interfaces
term· Terminal- GUI need · task setting
CL· CLI-sufficient- GUI need · ordinary access
CL· CLI-sufficient- Interaction primitives
code· Code & commands- Work product
WP02· Records/table- Conditions
- None declared
- Proposed difficulty
- 4 / 5
Proposed verifier
Grader compares matches to truth: precision ≥0.98 and recall ≥0.95 on matched pairs; unmatched sets equal truth (Jaccard ≥0.95). Many-to-one groups must sum exactly. Reconciling balance (bank − ledger − unmatched) must be 0.00.
Proposed partial credit
45% match precision/recall; 25% many-to-one groups; 20% unmatched sets; 10% balance check
R03.01.02.02-T03 Cluster and normalize vendor names in OpenRefine
Original instruction
Open data/vendors_raw.csv (18k rows) in OpenRefine. Use clustering (key collision and nearest neighbor) and transforms to normalize vendor_name to the canonical spellings, split 'City, ST ZIP' into three columns, and export output/vendors_clean.csv plus the operation history as output/history.json.
- Platforms
mac· macOSwin· Windowslin· Linuxweb· Any OS (web)- Applications
- OpenRefine
- Interfaces
brow· Browser- GUI need · task setting
HY· Hybrid- GUI need · ordinary access
G2· GUI-essential- Interaction primitives
click· Point & clickvis· Visual judgment- Work product
WP02· Records/table- Conditions
- None declared
- Proposed difficulty
- 3 / 5
Proposed verifier
Grader: vendor_name accuracy vs canonical truth ≥98% of rows; address columns exact on ≥99%. history.json must be a valid OpenRefine operation list that reproduces vendors_clean.csv when replayed on the raw file through the OpenRefine API.
Proposed partial credit
50% vendor normalization; 25% address split; 25% history replays to same output
R03.01.02.02-T04 Parse and standardize US postal addresses
Original instruction
data/addresses.csv holds 80k free-text US addresses. Parse each into number, street, unit, city, state, ZIP5 and standardize abbreviations (Street→ST, Apartment→APT) per USPS Publication 28 conventions summarized in docs/usps_rules.md. Flag unparseable rows instead of guessing.
- Platforms
lin· Linuxmac· macOSwin· Windows- Applications
- Python · pandas
- Interfaces
term· Terminal- GUI need · task setting
CL· CLI-sufficient- GUI need · ordinary access
CL· CLI-sufficient- Interaction primitives
code· Code & commands- Work product
WP02· Records/table- Conditions
- None declared
- Proposed difficulty
- 3 / 5
Proposed verifier
Grader compares components to a truth file: per-field exact-match accuracy ≥97%; flagged rows must include all 312 seeded unparseable ones and flag ≤1% of valid rows.
Proposed partial credit
60% component accuracy; 25% flagging recall; 15% low false flags
R03.01.02.02-T05 Harmonize units and currencies in a product catalog
Original instruction
data/catalog.xlsx mixes weights in lb/oz/kg/g, dimensions in in/cm/mm, and prices in USD/EUR/GBP. Convert to kg, cm and USD (rates in data/fx_2025-10-01.csv), keep 3 significant decimals, and write output/catalog_clean.csv with the original values preserved in _raw columns.
- Platforms
lin· Linuxmac· macOSwin· Windows- Applications
- pandas
- Interfaces
term· Terminal- GUI need · task setting
CL· CLI-sufficient- GUI need · ordinary access
CL· CLI-sufficient- Interaction primitives
code· Code & commands- Work product
WP02· Records/table- Conditions
- None declared
- Proposed difficulty
- 2 / 5
Proposed verifier
Grader checks every converted value against truth (relative error ≤1e-3); _raw columns equal input; rows with missing units flagged not converted.
Proposed partial credit
40% weights; 30% dimensions; 20% prices; 10% raw columns kept
R03.01.02.02-T06 Normalize mixed date formats and time zones
Original instruction
data/tickets.csv has created_at in 9 formats ('03/04/25 9:15 PM', ISO with offsets, epoch ms, 'Mar 4, 2025') and a site column mapping to time zones in data/sites.csv. Convert all to UTC ISO 8601 and flag ambiguous day-month rows using the per-site locale rules in docs/locale.md.
- Platforms
lin· Linuxmac· macOSwin· Windows- Applications
- pandas
- Interfaces
term· Terminal- GUI need · task setting
CL· CLI-sufficient- GUI need · ordinary access
CL· CLI-sufficient- Interaction primitives
code· Code & commands- Work product
WP02· Records/table- Conditions
- None declared
- Proposed difficulty
- 2 / 5
Proposed verifier
Grader: created_at_utc exact for ≥99.5% of unambiguous rows; ambiguous flag equals truth set; DST-gap times handled per docs (shift forward).
Proposed partial credit
60% exact UTC conversion; 25% ambiguity flags; 15% DST handling
R03.01.02.02-T07 Explain inventory variances between system, counts and movements
Original instruction
For warehouse WH2, data/system_onhand.csv, data/physical_count.csv and data/movements_oct.csv disagree. Reconcile per SKU: opening + movements vs system vs count, classify each variance as timing (posted after count), unit-of-measure error, missing movement or unexplained, and write output/variances.csv.
- Platforms
lin· Linuxmac· macOSwin· Windows- Applications
- pandas · SQL
- Interfaces
term· Terminal- GUI need · task setting
CL· CLI-sufficient- GUI need · ordinary access
CL· CLI-sufficient- Interaction primitives
code· Code & commands- Work product
WP02· Records/table- Conditions
- None declared
- Proposed difficulty
- 3 / 5
Proposed verifier
Grader compares per-SKU variance quantity (exact) and category vs truth: accuracy ≥90% for pass. Total unexplained value must equal truth ±0.01.
Proposed partial credit
40% variance quantities; 40% category accuracy; 20% unexplained total
R03.01.02.02-T08 Diff two CRM exports after a migration
Original instruction
data/old_crm.csv and data/new_crm.json (nested) should hold the same 64k accounts after a migration. Map fields per docs/field_map.md and report missing accounts, extra accounts and per-field mismatches (after normalization of phone and country formats) in output/diff_report.csv.
- Platforms
lin· Linuxmac· macOSwin· Windows- Applications
- pandas · Python
- Interfaces
term· Terminal- GUI need · task setting
CL· CLI-sufficient- GUI need · ordinary access
CL· CLI-sufficient- Interaction primitives
code· Code & commands- Work product
WP02· Records/table- Conditions
- None declared
- Proposed difficulty
- 3 / 5
Proposed verifier
Grader compares to truth: missing/extra sets exact; mismatch rows precision and recall ≥0.95; normalization-only differences must not be reported.
Proposed partial credit
30% missing/extra; 50% field mismatches; 20% no normalization false positives
R03.01.02.02-T09 Clean and sessionize two billion clickstream rows with Spark
Original instruction
On the local Spark cluster, process s3a://lake/clicks/2025/ (2.1B rows) into clean sessions: drop bots per docs/bot_rules.md, dedupe retried events, repair clock skew using server receipt time, sessionize with 30-minute gaps, and write partitioned Parquet with a row-count reconciliation table.
- Platforms
lin· Linux- Applications
- Spark · PySpark · MinIO
- Interfaces
term· Terminal- GUI need · task setting
CL· CLI-sufficient- GUI need · ordinary access
CL· CLI-sufficient- Interaction primitives
code· Code & commands- Work product
WP02· Records/table- Conditions
long· Long-horizon- Proposed difficulty
- 5 / 5
Proposed verifier
Grader samples 50 users and recomputes sessions exactly; global counts (raw, bots, dupes, clean, sessions) must reconcile to raw total; output partitioning by date; job finishes within 90 min on the provided cluster.
Proposed partial credit
30% bot/dup rules; 35% session accuracy on sample; 25% reconciliation table balances; 10% runtime
R03.01.02.02-T10 Merge 60 monthly extracts with drifting schemas
Original instruction
data/monthly/ contains 60 CSV extracts (2020–2024) whose columns were renamed, split and reordered over time (see docs/changelog.md). Produce one Parquet dataset with the 2024 schema, a source_file column, and a lineage table mapping each output column to its source column per file.
- Platforms
lin· Linuxmac· macOSwin· Windows- Applications
- pandas · Polars
- Interfaces
term· Terminal- GUI need · task setting
CL· CLI-sufficient- GUI need · ordinary access
CL· CLI-sufficient- Interaction primitives
code· Code & commands- Work product
WP02· Records/table- Conditions
- None declared
- Proposed difficulty
- 3 / 5
Proposed verifier
Grader checks row count equals sum of inputs, values on 1,000 sampled rows equal truth after mapping, and lineage table matches the changelog for all 60 files.
Proposed partial credit
20% row count; 50% sampled values; 30% lineage table
R03.01.02.02-T11 Resolve company entities across three sources with partial IDs
Original instruction
Link companies across data/suppliers.csv, data/registry.parquet and data/news_mentions.jsonl. LEI and DUNS are present for only some rows; names vary (legal suffixes, translations, abbreviations). Output output/links.csv with a global entity id per row and a confidence score.
- Platforms
lin· Linuxmac· macOSwin· Windows- Applications
- Python · pandas
- Interfaces
term· Terminal- GUI need · task setting
CL· CLI-sufficient- GUI need · ordinary access
CL· CLI-sufficient- Interaction primitives
code· Code & commands- Work product
WP02· Records/table- Conditions
long· Long-horizon- Proposed difficulty
- 5 / 5
Proposed verifier
Grader: pairwise F1 vs truth ≥0.90 overall and ≥0.80 on the no-ID subset; confidence must be calibrated (Brier score ≤0.12 on links). No conflicting IDs merged (LEI mismatch is a hard violation).
Proposed partial credit
50% overall F1; 25% no-ID subset F1; 15% calibration; 10% no ID conflicts
R03.01.02.02-T12 Validate and repair ICD-10 codes in a claims extract
Original instruction
data/claims.csv has 300k diagnosis codes with missing dots, lowercase letters, trailing spaces, ICD-9 codes and invalid codes. Normalize valid ICD-10-CM codes, map ICD-9 codes with the provided GEM file, and route invalid rows to output/quarantine.csv with a reason code.
- Platforms
lin· Linuxmac· macOSwin· Windows- Applications
- pandas
- Interfaces
term· Terminal- GUI need · task setting
CL· CLI-sufficient- GUI need · ordinary access
CL· CLI-sufficient- Interaction primitives
code· Code & commands- Work product
WP02· Records/table- Conditions
- None declared
- Proposed difficulty
- 3 / 5
Proposed verifier
Grader: normalized codes exact vs truth; ICD-9 mappings follow GEM (one-to-many choose flagged per docs); quarantine set and reason codes equal truth. No valid code altered.
Proposed partial credit
40% normalization; 30% ICD-9 mapping; 20% quarantine; 10% no valid code changed
R03.01.02.02-T13 Clean a gappy sensor series with documented rules
Original instruction
data/plant_sensors.parquet (5 s samples, 40 sensors, 3 months) has dropouts, stuck values and spikes. Apply docs/cleaning_rules.md: flag stuck runs over 10 min, remove spikes beyond 6 MAD, interpolate gaps under 2 min, leave longer gaps null, and write output/clean.parquet with a quality flag column.
- Platforms
lin· Linuxmac· macOSwin· Windows- Applications
- pandas · Polars
- Interfaces
term· Terminal- GUI need · task setting
CL· CLI-sufficient- GUI need · ordinary access
CL· CLI-sufficient- Interaction primitives
code· Code & commands- Work product
WP02· Records/table- Conditions
- None declared
- Proposed difficulty
- 3 / 5
Proposed verifier
Grader compares to the hidden true signal: RMSE on interpolated points ≤ reference ×1.1; flag classes match truth with F1 ≥0.95; long gaps remain null.
Proposed partial credit
35% spike/stuck flags; 35% interpolation RMSE; 20% long gaps null; 10% schema
R03.01.02.02-T14 Reconcile intercompany balances across currencies
Original instruction
Eleven subsidiaries' intercompany ledgers (data/ic/*.csv, 6 currencies) must net to zero after conversion at month-end rates. Pair the transactions, convert with data/fx_month_end.csv, list unmatched or mismatched items with the difference in group currency (EUR), and produce elimination entries in output/eliminations.csv.
- Platforms
lin· Linuxmac· macOSwin· Windows- Applications
- pandas · SQL
- Interfaces
term· Terminal- GUI need · task setting
CL· CLI-sufficient- GUI need · ordinary access
CL· CLI-sufficient- Interaction primitives
code· Code & commands- Work product
WP02· Records/table- Conditions
- None declared
- Proposed difficulty
- 4 / 5
Proposed verifier
Grader: pairs match truth (F1 ≥0.97); exception list equals truth with differences ±0.01 EUR; elimination entries balance to 0 and net each pair.
Proposed partial credit
40% pairing; 30% exceptions and differences; 30% eliminations balance
R03.01.02.02-T15 Turn a multi-header Excel report into a tidy table
Original instruction
data/regional_report.xlsx has 12 sheets with merged cells, two-row headers, subtotal rows and notes. Convert all sheets into one tidy table (region, store, month, metric, value) without subtotals, saved as output/tidy.csv, and verify totals against the sheets' grand totals.
- Platforms
lin· Linuxmac· macOSwin· Windows- Applications
- pandas · openpyxl
- Interfaces
term· Terminal- GUI need · task setting
CL· CLI-sufficient- GUI need · ordinary access
CL· CLI-sufficient- Interaction primitives
code· Code & commands- Work product
WP02· Records/table- Conditions
- None declared
- Proposed difficulty
- 3 / 5
Proposed verifier
Grader: tidy.csv equals truth (row set equality, values ±0.01); no subtotal rows; per-sheet sums equal the workbook's grand totals.
Proposed partial credit
60% row-set equality; 20% subtotals removed; 20% totals verified
R03.01.02.02-T16 Reconcile payroll between HRIS, register and general ledger
Original instruction
October payroll does not tie out: data/hris_employees.csv, data/payroll_register.csv and data/gl_payroll.csv disagree. Reconcile headcount, gross pay by department and employer taxes; identify each discrepancy cause (terminated but paid, missing cost center, wrong account) in output/payroll_recon.xlsx.
- Platforms
lin· Linuxmac· macOSwin· Windows- Applications
- pandas · Excel
- Interfaces
term· Terminal- GUI need · task setting
CL· CLI-sufficient- GUI need · ordinary access
CL· CLI-sufficient- Interaction primitives
code· Code & commands- Work product
WP02· Records/table- Conditions
- None declared
- Proposed difficulty
- 4 / 5
Proposed verifier
Grader reads the workbook: department totals per source exact; discrepancy list equals the 14 seeded issues by employee id and cause class; residual unexplained difference 0.00.
Proposed partial credit
30% department totals; 50% discrepancies identified; 20% residual zero
R03.01.02.02-T17 Reconcile entity names against a reference service in OpenRefine
Original instruction
In OpenRefine, reconcile the 'institution' column of data/grants.csv (9k rows) against the sandbox reconciliation service (local ROR mirror), accept high-confidence matches, manually resolve the ambiguous ones using country and city columns, and add the ROR id column. Export output/grants_ror.csv.
- Platforms
mac· macOSwin· Windowslin· Linuxweb· Any OS (web)- Applications
- OpenRefine
- Interfaces
brow· Browser- GUI need · task setting
HY· Hybrid- GUI need · ordinary access
G2· GUI-essential- Interaction primitives
click· Point & clickvis· Visual judgment- Work product
WP02· Records/table- Conditions
- None declared
- Proposed difficulty
- 3 / 5
Proposed verifier
Grader: ROR id accuracy ≥97% vs truth; ambiguous seeded cases (120) resolved correctly ≥90%; unmatched rows left blank, not wrongly matched (precision ≥0.99). Export columns preserved.
Proposed partial credit
50% overall id accuracy; 30% ambiguous cases; 20% precision of matches
R03.01.02.02-T18 Mask PII in free-text notes while preserving format
Original instruction
data/support_notes.parquet (500k notes) contains names, emails, phone numbers, card numbers and addresses. Replace each PII span with a format-preserving token (same length class, consistent per entity within a note) and write output/masked.parquet plus output/pii_spans.csv.
- Platforms
lin· Linuxmac· macOSwin· Windows- Applications
- Python · pandas
- Interfaces
term· Terminal- GUI need · task setting
CL· CLI-sufficient- GUI need · ordinary access
CL· CLI-sufficient- Interaction primitives
code· Code & commands- Work product
WP02· Records/table- Conditions
- None declared
- Proposed difficulty
- 4 / 5
Proposed verifier
Grader compares spans to labeled truth on a 5k-note sample: recall ≥0.97 for emails/phones/cards, ≥0.90 for names/addresses; precision ≥0.90. Non-PII text unchanged (character diff outside spans = 0).
Proposed partial credit
50% PII recall; 25% precision; 15% consistent tokens; 10% non-PII unchanged
R03.01.02.02-T19 Write a schema contract and split clean and quarantined rows
Original instruction
Write a pandera schema for data/orders.csv per docs/contract.md (types, ranges, enumerations, cross-field rules like ship_date ≥ order_date) and a script that writes valid rows to output/clean.parquet and invalid rows to output/quarantine.csv with the failed check name.
- Platforms
lin· Linuxmac· macOSwin· Windows- Applications
- pandas · pandera
- Interfaces
term· Terminal- GUI need · task setting
CL· CLI-sufficient- GUI need · ordinary access
CL· CLI-sufficient- Interaction primitives
code· Code & commands- Work product
WP02· Records/table- Conditions
- None declared
- Proposed difficulty
- 2 / 5
Proposed verifier
Grader runs the script on the given and a hidden file: clean and quarantine sets equal truth; check names match the violated rule.
Proposed partial credit
50% correct split; 30% check names; 20% hidden file
R03.01.02.02-T20 Build an order-to-cash exceptions report across three systems
Original instruction
Reconcile October orders across data/shop_orders.csv (storefront export), data/payments_payouts.csv (payment processor payouts with fees and refunds) and data/3pl_shipments.xlsx. Every order should be paid, settled and shipped; produce output/exceptions.csv (order_id, exception_type, amount_delta) and a summary of fees and timing differences.
- Platforms
lin· Linuxmac· macOSwin· Windows- Applications
- pandas · SQL
- Interfaces
term· Terminal- GUI need · task setting
CL· CLI-sufficient- GUI need · ordinary access
CL· CLI-sufficient- Interaction primitives
code· Code & commands- Work product
WP02· Records/table- Conditions
long· Long-horizon- Proposed difficulty
- 5 / 5
Proposed verifier
Grader: exception list equals the 47 seeded exceptions by order and type (F1 ≥0.95); amount_delta ±0.01; payout-level totals reconcile to bank deposits in data/bank.csv; summary figures exact.
Proposed partial credit
50% exceptions; 20% deltas; 20% payout-to-bank tie-out; 10% summary
Mapped research references
Existing benchmark-to-class links from the research catalog. They are not record-level annotations, executions or product endorsements.