OpenRefine looks like a spreadsheet, but its useful mental model is different: it is a local transformation workspace with column profiling, batch operations, and replayable history. The best workflow separates exploration from irreversible-looking mass edits.
A safe working loop
- Import a copy and confirm encoding, separator, header, and row count.
- Create facets to understand missingness, distributions, and variants.
- State the intended canonical representation.
- Preview a transformation on representative rows.
- Apply it, inspect the changed count, and spot-check failures.
- Export the operation history as JSON.
- Reapply the recipe to a fresh import and compare outputs.
In plain terms: clicking is not the problem. Unrecorded clicking is the problem.
Facets are lightweight profiles
Text facets reveal distinct values and frequency. Numeric and timeline facets reveal range and density. Custom facets can test a condition:
value == null || value.trim() == ""Use facets before transformation to define scope, and after transformation to check whether expected variants disappeared. A facet selection acts as a temporary predicate: operations affect only matching rows, so the selection itself is part of the logic.
Clustering proposes; a human decides
Clustering groups values that may be variants. Key-collision methods normalize features and group equal keys; nearest-neighbor methods use a distance threshold. Different methods expose different risks:
| Method idea | Finds well | Main risk |
|---|---|---|
| Fingerprint | Case, punctuation, token-order variants | Distinct phrases collapse |
| Phonetic key | Similar pronunciation | Language dependence |
| Edit distance | Typos | Short strings over-merge |
Never accept an entire cluster solely because the tool grouped it. Frequency, surrounding columns, and domain knowledge decide whether values mean the same thing.
GREL makes transformations explicit
OpenRefine’s expression language can trim, split, replace, parse, and combine values:
value.trim().toUppercase()if(value.match(/^\d{4}-\d{2}-\d{2}$/), value, null)Build transformations from small steps. A long expression that cleans five concepts at once is hard to validate and harder to explain.
Reconciliation is not string replacement
Reconciliation links a value to an entity in an external vocabulary. A candidate match should preserve the source value, chosen identifier, service, confidence, and review status. Automatic matching is safest when identifiers or strong contextual fields exist; ambiguous names need review.
Operation history is necessary, not sufficient
Exported history records operations, but reproducibility also needs:
- the original input or a stable checksum;
- OpenRefine version and relevant extensions;
- assumptions behind manual cluster choices;
- row counts and quality metrics before and after;
- unresolved and rejected cases.
The recipe should run successfully on a fresh project. If manual choices cannot be explained, history records what happened without explaining why.
Review card
- Confirm import settings and baseline counts first.
- Facets define both evidence and operation scope.
- Clustering produces candidates, not truth.
- Keep transformations small and previewable.
- Reconciliation should preserve identifiers, confidence, and review state.
- Export history and verify it by replaying against a fresh import.
OpenRefine 看起來像 spreadsheet，但更適合把它理解成 local transformation workspace：它有 column profiling、batch operation 與可 replay 的 history。好的流程會把探索與大量修改分開。
安全的工作循環
- Import 副本，確認 encoding、separator、header 與 row count。
- 用 facet 理解 missingness、distribution 與 variant。
- 先寫出目標 canonical representation。
- 在代表性 row 上 preview transformation。
- Apply 後檢查 changed count，抽查 failure。
- 將 operation history 匯出為 JSON。
- 對 fresh import 重播 recipe，比較 output。
白話來說： 問題不是 click；問題是沒有紀錄的 click。
Facet 是輕量 profile
Text facet 顯示 distinct value 與 frequency；numeric／timeline facet 顯示 range 與 density。Custom facet 可以檢查條件：
value == null || value.trim() == ""Transformation 前用 facet 定義 scope，之後再確認預期 variant 是否消失。Facet selection 相當於暫時 predicate；operation 只作用在 match row，因此 selection 本身也是 logic。
Clustering 提議，人類決定
Clustering 將可能是 variant 的 value 分組。Key-collision 先 normalize feature，再按相同 key grouping；nearest-neighbor 則使用 distance threshold。
| 方法概念 | 擅長找到 | 主要風險 |
|---|---|---|
| Fingerprint | Case、punctuation、token-order variant | 不同 phrase 被合併 |
| Phonetic key | 發音相近 | 依賴語言 |
| Edit distance | Typo | 短字串過度 merge |
不要因為工具把它們放在同一 cluster 就全部 accept。Frequency、周邊 column 與 domain knowledge 才能判斷是否同義。
GREL 讓 transformation 明確
OpenRefine expression language 可以 trim、split、replace、parse 與 combine：
value.trim().toUppercase()if(value.match(/^\d{4}-\d{2}-\d{2}$/), value, null)用小步驟組 transformation。一條 expression 同時清五種概念，很難驗證，也很難解釋。
Reconciliation 不是 string replacement
Reconciliation 是把 value 連到 external vocabulary 的 entity。應保留 source value、chosen identifier、service、confidence 與 review status。有 identifier 或強 contextual field 時才適合 automatic match；ambiguous name 需要 review。
Operation history 必要，但不充分
除了 exported history，reproducibility 還需要：
- original input 或 stable checksum；
- OpenRefine version 與 extension；
- manual cluster choice 背後的假設；
- before／after row count 與 quality metric；
- unresolved 與 rejected case。
Recipe 應能在 fresh project 成功重跑。如果人工決策無法解釋，history 只能記錄「做了什麼」，不能回答「為什麼」。
複習卡
- 先確認 import setting 與 baseline count。
- Facet 同時定義 evidence 與 operation scope。
- Clustering 產生 candidate，不產生真相。
- Transformation 保持小、可 preview。
- Reconciliation 保留 ID、confidence 與 review state。
- 匯出 history，並在 fresh import 驗證 replay。