Inconsistent Categories

A category column should hold a small, fixed set of labels, but free text entry and merged sources break that.

Each system or person spells the same thing slightly differently, and the computer treats every spelling as new.

The result looks like more categories than really exist, each holding part of the true count.

The first check is value_counts on the column, read from the rarest values upwards.

Rare values are often variants of common ones, such as Londn or Lodnon next to London.

The fix is a mapping from each variant to a standard label, kept in a file rather than buried in code.

For example, UK, U.K., uk, Great Britain and United Kingdom might all map to United Kingdom.

Keeping the mapping in a file means a new variant needs one new line, not a code change.

After mapping, the column can be stored with the pandas category type, which refuses later values outside the agreed set.

Some near matches are genuinely different, such as Austria and Australia, so automatic matching needs a human check.

Merging categories also loses detail, so the original column is often kept beside the cleaned one.

Categories drift as new products, regions or teams appear, so the mapping needs regular updates.

In practice the best fix is upstream, replacing a free text box with a fixed list in the form that collects the data.

Where that is impossible, a check that flags any label missing from the mapping catches new variants early.

The common mistake is fixing variants by hand in one report, so the next report shows the same split again.