Data Preparation and Transformation for Analysis

Description

A structured sequence for turning raw, multi-source data into a clean analysis table: characterise, clean, remove, transform and segment, while recording every change.

Analyses of product data usually start with data collected for another purpose, and Myatt argues this step is often the most time-consuming and decides the quality of everything after it. The procedure first builds a single data table and characterises each variable (continuous or discrete, scale of measurement, role, frequency distribution). Cleaning then consolidates inconsistent category labels, inspects outliers with histograms, box plots and z-scores, converts or removes non-numeric entries in numeric fields, puts measurements on a single unit, handles missing values and removes duplicate records. Variables that are constant, mostly missing or redundant with others are dropped. Transformations follow: normalisation (min-max, z-score, decimal scaling) so variables with large ranges do not dominate distance-based methods; log, exponential or Box-Cox transforms towards normality; value mapping of ordinal text to numbers and nominal values to dummy variables; discretisation (binning) of continuous values or generalising many categories into fewer; and aggregation into derived variables. Finally the table may be segmented into subsets. Each transformation is recorded so results can be translated back to original units before presentation.

Attributes

Loss type
Siloed
Effort
Days

Steps

  1. Assemble data from all sources into one table with one row per observation (user, session, account).
  2. Characterise every variable: type, scale of measurement, role (descriptor or response) and frequency distribution.
  3. Clean: merge inconsistent labels, review outliers with subject experts, fix or remove non-numeric values, unify units, deal with missing values and remove duplicates.
  4. Remove constants, mostly-missing variables and variables that add no information beyond highly correlated ones.
  5. Transform as needed: normalise, apply log or Box-Cox transforms, map ordinal and nominal values to numbers or dummy variables, bin continuous values, and derive aggregate variables.
  6. Segment the table into random or problem-driven subsets, noting the criteria used.
  7. Document every step and keep the inverse of each transformation for reporting results.

Sources

  • Myatt, G., Making Sense of Data: A Practical Guide to Exploratory Data Analysis and Data Mining (2007)

Also known as

  • Data cleaning and transformation
  • Data wrangling
  • Data pre-processing
  • Normalisation, value mapping, discretisation and aggregation
  • Box-Cox transformation

Categories

  • Analytics & Behaviour
  • Statistics & Modelling