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
- Discipline
- Statistics & Modelling
- Loss type
- Siloed
- Stage
- Discovery, Post-launch
- Data
- Quantitative
- Effort
- Days
Steps
- Assemble data from all sources into one table with one row per observation (user, session, account).
- Characterise every variable: type, scale of measurement, role (descriptor or response) and frequency distribution.
- Clean: merge inconsistent labels, review outliers with subject experts, fix or remove non-numeric values, unify units, deal with missing values and remove duplicates.
- Remove constants, mostly-missing variables and variables that add no information beyond highly correlated ones.
- 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.
- Segment the table into random or problem-driven subsets, noting the criteria used.
- 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