Mixed units
Values in one column are measured in different units, sometimes with the unit written in the cell and sometimes not.
What it looks like
Before
| weight |
|---|
| 12 kg |
| 26.5 lb |
| 12000 g |
| 12 |
After
| weight |
|---|
| 12.0 kg |
| 12.0 kg |
| 12.0 kg |
| needs a decision |
How it happens
- Different sites, suppliers, or instruments report in their own units.
- A unit changed partway through the life of a system.
- The unit lived in a column header or in someone's head, and was lost when files were combined.
What it breaks
- Totals and averages are meaningless, because they add quantities that are not comparable.
- Outlier checks flag correct values and miss wrong ones.
- Decisions based on thresholds, such as a reorder level or a safety limit, apply to the wrong scale.
How to spot it
- Plot or sort the values. Mixed units often show up as two separate clusters.
- Look for unit text inside cells, and for rows where it is absent.
- Compare ranges by source. One site whose values are all about two times larger is probably using a different unit.
How to fix it
- Choose one unit for the column and record it in the column name.
- Convert each value using the unit it was actually recorded in.
- Do not convert values whose unit is unknown. Find the source or leave them flagged.
- Numbers stored as textA column of quantities contains symbols, separators, or words that stop software reading it as numbers.
- Multiple values in one cellA single cell holds a list, or two different facts, joined by a comma, slash, or line break.
- Case example: UtilitiesEquipment records that could not be tied to what was spent on them
- Case example: E-commerceA catalog where the same product was listed four times
TermsNormalization, Outlier, Standardization