大规模数据清洗与对账
Data cleaning & reconciliation at scale
Clean, join and reconcile messy datasets to a validated output.
20 条本项目编写的提案 · 分类 v1.1 · 新增基准运行 0 次。下方验证方式与部分得分规则是设计，不是执行结果。
- 典型应用
- pandas · SQL · OpenRefine
- 小类 GUI 需求标签
CL· CLI 足够
任务提案 · 20
展开查看完整原文与建议检查方式；两种界面均保留提案英文原文。
R03.01.02.02-T01 Deduplicate 250k customer records with fuzzy matching
原始指令
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.
- 平台
lin· Linuxmac· macOSwin· Windows- 应用
- pandas · Python
- 交互表面
term· 终端- GUI 需求 · 任务设定
CL· CLI 足够- GUI 需求 · 普通权限
CL· CLI 足够- 交互原语
code· 代码与命令- 工作产物
WP02· 记录 / 表格- 条件
- 未声明
- 提案难度
- 4 / 5
建议验证方式
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.
建议部分得分
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
原始指令
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.
- 平台
lin· Linuxmac· macOSwin· Windows- 应用
- pandas · SQL
- 交互表面
term· 终端- GUI 需求 · 任务设定
CL· CLI 足够- GUI 需求 · 普通权限
CL· CLI 足够- 交互原语
code· 代码与命令- 工作产物
WP02· 记录 / 表格- 条件
- 未声明
- 提案难度
- 4 / 5
建议验证方式
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.
建议部分得分
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
原始指令
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.
- 平台
mac· macOSwin· Windowslin· Linuxweb· 任意系统（网页）- 应用
- OpenRefine
- 交互表面
brow· 浏览器- GUI 需求 · 任务设定
HY· 混合- GUI 需求 · 普通权限
G2· GUI 必要- 交互原语
click· 指向与点击vis· 视觉判断- 工作产物
WP02· 记录 / 表格- 条件
- 未声明
- 提案难度
- 3 / 5
建议验证方式
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.
建议部分得分
50% vendor normalization; 25% address split; 25% history replays to same output
R03.01.02.02-T04 Parse and standardize US postal addresses
原始指令
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.
- 平台
lin· Linuxmac· macOSwin· Windows- 应用
- Python · pandas
- 交互表面
term· 终端- GUI 需求 · 任务设定
CL· CLI 足够- GUI 需求 · 普通权限
CL· CLI 足够- 交互原语
code· 代码与命令- 工作产物
WP02· 记录 / 表格- 条件
- 未声明
- 提案难度
- 3 / 5
建议验证方式
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.
建议部分得分
60% component accuracy; 25% flagging recall; 15% low false flags
R03.01.02.02-T05 Harmonize units and currencies in a product catalog
原始指令
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.
- 平台
lin· Linuxmac· macOSwin· Windows- 应用
- pandas
- 交互表面
term· 终端- GUI 需求 · 任务设定
CL· CLI 足够- GUI 需求 · 普通权限
CL· CLI 足够- 交互原语
code· 代码与命令- 工作产物
WP02· 记录 / 表格- 条件
- 未声明
- 提案难度
- 2 / 5
建议验证方式
Grader checks every converted value against truth (relative error ≤1e-3); _raw columns equal input; rows with missing units flagged not converted.
建议部分得分
40% weights; 30% dimensions; 20% prices; 10% raw columns kept
R03.01.02.02-T06 Normalize mixed date formats and time zones
原始指令
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.
- 平台
lin· Linuxmac· macOSwin· Windows- 应用
- pandas
- 交互表面
term· 终端- GUI 需求 · 任务设定
CL· CLI 足够- GUI 需求 · 普通权限
CL· CLI 足够- 交互原语
code· 代码与命令- 工作产物
WP02· 记录 / 表格- 条件
- 未声明
- 提案难度
- 2 / 5
建议验证方式
Grader: created_at_utc exact for ≥99.5% of unambiguous rows; ambiguous flag equals truth set; DST-gap times handled per docs (shift forward).
建议部分得分
60% exact UTC conversion; 25% ambiguity flags; 15% DST handling
R03.01.02.02-T07 Explain inventory variances between system, counts and movements
原始指令
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.
- 平台
lin· Linuxmac· macOSwin· Windows- 应用
- pandas · SQL
- 交互表面
term· 终端- GUI 需求 · 任务设定
CL· CLI 足够- GUI 需求 · 普通权限
CL· CLI 足够- 交互原语
code· 代码与命令- 工作产物
WP02· 记录 / 表格- 条件
- 未声明
- 提案难度
- 3 / 5
建议验证方式
Grader compares per-SKU variance quantity (exact) and category vs truth: accuracy ≥90% for pass. Total unexplained value must equal truth ±0.01.
建议部分得分
40% variance quantities; 40% category accuracy; 20% unexplained total
R03.01.02.02-T08 Diff two CRM exports after a migration
原始指令
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.
- 平台
lin· Linuxmac· macOSwin· Windows- 应用
- pandas · Python
- 交互表面
term· 终端- GUI 需求 · 任务设定
CL· CLI 足够- GUI 需求 · 普通权限
CL· CLI 足够- 交互原语
code· 代码与命令- 工作产物
WP02· 记录 / 表格- 条件
- 未声明
- 提案难度
- 3 / 5
建议验证方式
Grader compares to truth: missing/extra sets exact; mismatch rows precision and recall ≥0.95; normalization-only differences must not be reported.
建议部分得分
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
原始指令
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.
- 平台
lin· Linux- 应用
- Spark · PySpark · MinIO
- 交互表面
term· 终端- GUI 需求 · 任务设定
CL· CLI 足够- GUI 需求 · 普通权限
CL· CLI 足够- 交互原语
code· 代码与命令- 工作产物
WP02· 记录 / 表格- 条件
long· 长流程- 提案难度
- 5 / 5
建议验证方式
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.
建议部分得分
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
原始指令
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.
- 平台
lin· Linuxmac· macOSwin· Windows- 应用
- pandas · Polars
- 交互表面
term· 终端- GUI 需求 · 任务设定
CL· CLI 足够- GUI 需求 · 普通权限
CL· CLI 足够- 交互原语
code· 代码与命令- 工作产物
WP02· 记录 / 表格- 条件
- 未声明
- 提案难度
- 3 / 5
建议验证方式
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.
建议部分得分
20% row count; 50% sampled values; 30% lineage table
R03.01.02.02-T11 Resolve company entities across three sources with partial IDs
原始指令
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.
- 平台
lin· Linuxmac· macOSwin· Windows- 应用
- Python · pandas
- 交互表面
term· 终端- GUI 需求 · 任务设定
CL· CLI 足够- GUI 需求 · 普通权限
CL· CLI 足够- 交互原语
code· 代码与命令- 工作产物
WP02· 记录 / 表格- 条件
long· 长流程- 提案难度
- 5 / 5
建议验证方式
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).
建议部分得分
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
原始指令
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.
- 平台
lin· Linuxmac· macOSwin· Windows- 应用
- pandas
- 交互表面
term· 终端- GUI 需求 · 任务设定
CL· CLI 足够- GUI 需求 · 普通权限
CL· CLI 足够- 交互原语
code· 代码与命令- 工作产物
WP02· 记录 / 表格- 条件
- 未声明
- 提案难度
- 3 / 5
建议验证方式
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.
建议部分得分
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
原始指令
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.
- 平台
lin· Linuxmac· macOSwin· Windows- 应用
- pandas · Polars
- 交互表面
term· 终端- GUI 需求 · 任务设定
CL· CLI 足够- GUI 需求 · 普通权限
CL· CLI 足够- 交互原语
code· 代码与命令- 工作产物
WP02· 记录 / 表格- 条件
- 未声明
- 提案难度
- 3 / 5
建议验证方式
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.
建议部分得分
35% spike/stuck flags; 35% interpolation RMSE; 20% long gaps null; 10% schema
R03.01.02.02-T14 Reconcile intercompany balances across currencies
原始指令
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.
- 平台
lin· Linuxmac· macOSwin· Windows- 应用
- pandas · SQL
- 交互表面
term· 终端- GUI 需求 · 任务设定
CL· CLI 足够- GUI 需求 · 普通权限
CL· CLI 足够- 交互原语
code· 代码与命令- 工作产物
WP02· 记录 / 表格- 条件
- 未声明
- 提案难度
- 4 / 5
建议验证方式
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.
建议部分得分
40% pairing; 30% exceptions and differences; 30% eliminations balance
R03.01.02.02-T15 Turn a multi-header Excel report into a tidy table
原始指令
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.
- 平台
lin· Linuxmac· macOSwin· Windows- 应用
- pandas · openpyxl
- 交互表面
term· 终端- GUI 需求 · 任务设定
CL· CLI 足够- GUI 需求 · 普通权限
CL· CLI 足够- 交互原语
code· 代码与命令- 工作产物
WP02· 记录 / 表格- 条件
- 未声明
- 提案难度
- 3 / 5
建议验证方式
Grader: tidy.csv equals truth (row set equality, values ±0.01); no subtotal rows; per-sheet sums equal the workbook's grand totals.
建议部分得分
60% row-set equality; 20% subtotals removed; 20% totals verified
R03.01.02.02-T16 Reconcile payroll between HRIS, register and general ledger
原始指令
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.
- 平台
lin· Linuxmac· macOSwin· Windows- 应用
- pandas · Excel
- 交互表面
term· 终端- GUI 需求 · 任务设定
CL· CLI 足够- GUI 需求 · 普通权限
CL· CLI 足够- 交互原语
code· 代码与命令- 工作产物
WP02· 记录 / 表格- 条件
- 未声明
- 提案难度
- 4 / 5
建议验证方式
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.
建议部分得分
30% department totals; 50% discrepancies identified; 20% residual zero
R03.01.02.02-T17 Reconcile entity names against a reference service in OpenRefine
原始指令
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.
- 平台
mac· macOSwin· Windowslin· Linuxweb· 任意系统（网页）- 应用
- OpenRefine
- 交互表面
brow· 浏览器- GUI 需求 · 任务设定
HY· 混合- GUI 需求 · 普通权限
G2· GUI 必要- 交互原语
click· 指向与点击vis· 视觉判断- 工作产物
WP02· 记录 / 表格- 条件
- 未声明
- 提案难度
- 3 / 5
建议验证方式
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.
建议部分得分
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
原始指令
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.
- 平台
lin· Linuxmac· macOSwin· Windows- 应用
- Python · pandas
- 交互表面
term· 终端- GUI 需求 · 任务设定
CL· CLI 足够- GUI 需求 · 普通权限
CL· CLI 足够- 交互原语
code· 代码与命令- 工作产物
WP02· 记录 / 表格- 条件
- 未声明
- 提案难度
- 4 / 5
建议验证方式
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).
建议部分得分
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
原始指令
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.
- 平台
lin· Linuxmac· macOSwin· Windows- 应用
- pandas · pandera
- 交互表面
term· 终端- GUI 需求 · 任务设定
CL· CLI 足够- GUI 需求 · 普通权限
CL· CLI 足够- 交互原语
code· 代码与命令- 工作产物
WP02· 记录 / 表格- 条件
- 未声明
- 提案难度
- 2 / 5
建议验证方式
Grader runs the script on the given and a hidden file: clean and quarantine sets equal truth; check names match the violated rule.
建议部分得分
50% correct split; 30% check names; 20% hidden file
R03.01.02.02-T20 Build an order-to-cash exceptions report across three systems
原始指令
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.
- 平台
lin· Linuxmac· macOSwin· Windows- 应用
- pandas · SQL
- 交互表面
term· 终端- GUI 需求 · 任务设定
CL· CLI 足够- GUI 需求 · 普通权限
CL· CLI 足够- 交互原语
code· 代码与命令- 工作产物
WP02· 记录 / 表格- 条件
long· 长流程- 提案难度
- 5 / 5
建议验证方式
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.
建议部分得分
50% exceptions; 20% deltas; 20% payout-to-bank tie-out; 10% summary
已有研究映射
以下沿用研究目录中的基准与小类映射，不是逐题标注、执行记录或产品背书。