Skip to content

MODULE 8 · LESSON 46 · Data tools and workflows

Data transformations

Apply documented transformations while preserving original values and checking their consequences.

By Laurence Svekis · Self-paced · All worked data is synthetic unless you supply your own source.

Try the practice ↓

PredictInspectCalculateCheckExplainTransfer

Your starting point

Prerequisites: Use the ideas and vocabulary from lesson 45; revisit its worked examples if needed.

Definition: A transformation changes the representation or derives a new form of data, such as trimming whitespace, converting units, calculating a field or regrouping categories. A transformation can be mathematically correct yet inappropriate for the intended meaning.

Preserve the original and the rule

Keep a source copy and record each operation in order. Trimming spaces can repair accidental category differences, while changing capitalization may merge labels that have distinct meanings in a particular system. Unit conversion is different from changing a display label: seconds divided by sixty becomes minutes, but renaming seconds to minutes does not change the values.

Check information loss

Rounding, binning and collapsing categories discard detail. That may be useful for a report, but preserve the more detailed source when authorized. Turning a continuous measure into high and low categories can hide differences within each group. Choosing a threshold after seeing the desired result can also distort interpretation. State the reason for a transformation before using its result as evidence.

Verify invariants and changed quantities

Some properties should remain unchanged. Trimming text should not change row count; unit conversion should preserve the represented duration; confirmed deduplication intentionally changes the count and total. Define a before/after check for each operation. The workbench’s transformation record supports this audit, but it does not decide whether the rule is appropriate for your context.

Two worked examples

Unit conversion

Durations 120 and 180 seconds become 2 and 3 minutes after division by 60. The mean changes numerically from 150 seconds to 2.5 minutes, but the represented average duration is the same. Keep the unit in the output field name and verify a known example.

Whitespace cleanup

Labels “North” and “ North ” differ as raw strings. Trimming can combine them when the spaces are confirmed accidental. The number of records remains unchanged, but grouped totals may now appear under one label. Document the normalization instead of claiming the business’s regional distribution changed.

Where this becomes useful

A team prepares an export for comparison across systems. A transformation log should distinguish unit conversions, category normalization and record exclusions so another analyst can understand why the final table differs from the source.

Make an attempt

Use Cleaning Workbench with Small business operations. Trim whitespace and inspect the grouped labels without assuming capitalization differences are resolved. Then create a numerical conversion on a small imported table and verify one row and the aggregate before and after.

Open the accompanying interactive lab →

Misconception check

Claim: Any transformation that makes analysis easier is harmless.

Inspect the correction

Convenience can conceal information loss, changed group definitions or unsupported assumptions. Verify meaning as well as arithmetic.

Independent application

Design a three-step transformation workflow for a synthetic table. Preserve the source, state a reason for each operation, specify a before/after check and identify any lost information. Export the cleaned table with its transformation notes.

Completion records practice and self-review. It is not certification of statistical competence.