Standardize Categorical Data in CSV

Standardize Categorical Data in CSV helps you map inconsistent labels within a field to explicitly chosen standard values. A status field might contain done and completed, or a product category might have several agreed spellings. The operation manages that field's vocabulary. It does not prove that two customer names belong to one person, translate arbitrary text, or decide which brand owns a similar-looking product name.

The distinction matters because a small label change can alter later grouping and reporting. Combining categories changes which records share a summary row. A broad substring replacement can accidentally change completed into another word or affect completion in progress. This tool uses exact source-value mappings instead. A mapping for one complete value affects only cells with that original value, and all other source values remain unchanged unless they receive their own confirmed mapping.

Choose files or Paste a table, confirm parsing, and select Category column. The left settings panel lists distinct source values with their frequencies, ordered from most frequent to least frequent. Search distinct values narrows this list, and Previous values and Next values page through it. Paging controls the amount shown on screen; it does not limit the complete distinct-value count or the records processed by a confirmed mapping.

Inspect both common and rare values before deciding on targets. A rare spelling may be a typographical error, but it can also be a legitimate specialist category. Strict source-text comparison preserves capitalization, whitespace, accents, and punctuation. This makes hidden differences visible rather than erasing them before review. Source links in the result's distinct-value report let you inspect the records behind a value, providing context that a frequency count alone cannot supply.

Select one or several listed source values, enter Canonical value, and choose Confirm selected mappings. The button shows how many observations are affected by the selected values. Selecting completed together with done and entering completed creates two explicit mappings, one of which may leave already-standard text unchanged. A different value such as completion in progress is unaffected because it was not selected as a complete source value.

Confirmed mappings appear individually in the settings. You can change a target, remove a mapping, or confirm a revised mapping. Editing a target clears that mapping's confirmation so an earlier approval does not silently authorize the new text. Removing one mapping splits that source value from a previously chosen group. Changes to the mapping configuration make any earlier output stale; use Run and review again before exporting a result based on the revised decisions.

Each source cell is looked up once in the confirmed mapping table. Suppose the original input contains A and B, with mappings A to B and B to C. One run produces B for the original A and C for the original B. It does not apply the second rule again to the newly created B from the first record. This prevents an accidental replacement chain from changing a value beyond the mapping the user actually reviewed.

Repeated runs on the same unchanged input and mapping configuration produce the same result. Applying the output as a new workflow input is a separate operation: if you deliberately run the same mapping against that transformed table, its B values are now source values for the new step. Keep the distinction between rerunning a preview and adding another transformation to the workflow. Undo and the ordered history make it possible to return to the earlier source when a repeated application was unintended.

Vocabulary and previous mappings accepts a list of allowed canonical values, one per line. Leaving it empty imposes no vocabulary restriction. When a list is provided, a confirmed target outside it is blocked rather than silently appended to the standard vocabulary. This is useful when a destination accepts a controlled status list. An allowed vocabulary defines acceptable outputs; it does not infer which input spelling should map to which allowed value.

To reuse a two-column mapping table, first import it through Add files and the shared parsing confirmation. Select it as Imported mapping table, where the first column contains exact source values and the second contains targets. Load mappings for individual confirmation brings the pairs into the settings as unconfirmed mappings. Review each pair before use. Save rules and Load rules provide another way to preserve a complete configuration. A single source value assigned different targets is a conflict regardless of mapping order, and execution is blocked until that conflict is resolved.

Suggest similar values is off by default. When enabled, the tool compares distinct nonempty values using normalized edit distance. Text is normalized to Unicode NFC and compared as code points; insertion, deletion, and substitution each cost one. The score is one hundred times one minus the distance divided by the longer string's code-point length. A score describes textual similarity, not the probability that two labels are semantically equivalent.

Suggestions are independent pairs. Confirm left value maps to right accepts the displayed direction for that pair. You can then adjust its target in the settings if a different canonical label is appropriate. A similarity relationship is not propagated transitively: a suggestion linking A with B and another linking B with C does not authorize combining all three values. Abbreviations, translations, brand relationships, and word meanings are not inferred by the edit-distance algorithm. Review context before accepting a visually convincing pair.

Import records with the Chinese status values 完成, 已完成, and 完成中. Select the status field, choose only 完成 and 已完成, and enter 已完成 as the canonical value. After confirmation and Run and review, the first two records contain 已完成 while 完成中 remains unchanged. The mapping report lists the original and target values with their affected counts. This example shows why whole-value mapping is safer for a controlled vocabulary than replacing a common substring across the column.

Next add a new source file containing a previously unseen status. Reusing the same confirmed mappings leaves that new value unchanged and includes its record in Unmapped records. The tool does not assume that every new value is an error or choose the nearest existing category. Review the new source context, decide whether the vocabulary needs another category, and add a mapping only when its meaning is known. Mapping reuse reduces repetitive work without eliminating the need to inspect new data.

The main result is a new table with only the selected field's confirmed mappings applied. Other fields preserve their original text. All distinct values reports source frequencies and mapped targets. Value mappings reports the confirmed pairs and affected observation counts. Unmapped records includes source records without a confirmed mapping, and Similarity suggestions keeps the candidate pairs separate from the decisions. The change count reports actual changed cells rather than treating an identity mapping as a text change.

Use source links to trace a value back to its logical records. A source record can contain multiline notes or quoted punctuation; those remain text. Search within a result view changes only the visible grid subset, not the exported mapping population. When the output looks correct, Apply to workflow makes it available to profiling, grouping, or pivoting. Recalculate those downstream summaries after standardization rather than comparing an old summary with a newly changed vocabulary.

Export new copy can download the standardized table, value mappings, unmapped records, distinct-value report, suggestions, or settings. A bundle keeps those artifacts together. Generated CSV and TSV are read back before download is enabled. Formula-prefix protection can affect a canonical target beginning with a formula-like prefix, so inspect the impact and select the intended export policy. The tool never executes a category label as HTML, a formula, or a script.

Similarity work is bounded to fifty thousand candidate pairs, five million character-work units, and two hundred fifty-six code points per compared text. These are protective limits, not a measured promise for every device. A very large vocabulary may need prior filtering or manual mapping without similarity suggestions. A sample run is explicitly labeled and describes only its selected records. Cancel task preserves source data and applied steps. Refreshing or closing clears the in-memory session; save the reviewed rules and exported copies you need before leaving.