Learn
Lab · B02 Data Cleaning and Preparation
Data Cleaning Lab
Inspect a messy dataset, choose cleaning steps, and see what each one does to the data and to the conclusions you would draw from it.
Inspect a messy dataset, choose cleaning steps, and see what each one does to the data and to the conclusions you would draw from it.
Choose data
Three fictional datasets, each generated clean and then given known problems to find. Or bring your own CSV: it is read in this browser tab and never uploaded.
What each column should contain is in the data dictionary. All data are fictional, generated for teaching.
Use your own data
Comma, semicolon, tab or pipe separated. Files are processed in this browser tab and never uploaded; recipes for your own data are not remembered.
Inspect
Profile every column, read the issues the lab finds and why each matters, then look at the problem cells in the table. Everything here describes the data as it is now, after the steps in your recipe.
Column profiles
Issues
Loading the data…
The data
Clean
Pick an operation, set it up (an issue’s “Set up” button fills in sensible values), preview what it would do, then apply it. Each step joins the recipe. Removing a step rebuilds the table from the raw data, so every result can be reproduced.
Add a step
Recipe
Consequences
Cleaning changes the answers you get from the data. After each step, see the rows and cells changed, the statistics of the columns you watch before and after, and the shares of a category, with what this kind of step can do to your conclusions.
Export
Download the cleaned data, the recipe that produces it and a data quality report in Markdown. All three are generated in this browser.
How the lab works
The definitions behind every number, and what the lab will not do for you.
Summary statistics
The standard deviation is the sample SD, dividing by n − 1. Quartiles use linear interpolation between ordered values: the p-th quantile sits at position (n − 1) × p, counting from 0. This is Hyndman and Fan’s type 7, NumPy’s default and Excel’s QUARTILE.INC. Other software may use another definition and give slightly different quartiles for small samples.
Skewness
The adjusted Fisher–Pearson coefficient G₁ = √(n(n − 1)) ÷ (n − 2) × m₃ ÷ m₂^(3/2), where m₂ and m₃ are the second and third central moments with n in the denominator (the same as Excel’s SKEW). As a rule of thumb, |G₁| above 1 is strong skew; the lab reports skew for columns with at least 20 values and 12 distinct values.
Outlier rules
IQR fences: a value is flagged below Q1 − k × IQR or above Q3 + k × IQR. With k = 1.5 these are the inner fences (mild outliers); k = 3 gives the outer fences (extreme outliers). z-score: z = (x − mean) ÷ SD, flagged when |z| exceeds the threshold (3 by default); the mean and SD include the extreme values themselves, which can hide them in small samples. Modified z-score: M = 0.6745 × (x − median) ÷ MAD, where MAD is the median absolute deviation from the median; the threshold 3.5 is Iglewicz and Hoaglin’s recommendation. Dividing by 0.6745 is the same as multiplying the MAD by 1.4826, which makes it comparable to an SD for normal data. If more than half the values are equal the MAD is 0 and the lab refuses the rule rather than divide by zero.
Types, dates and numbers
A value is a number if it reads as one with the chosen decimal mark; with a decimal point, commas must separate groups of three digits, so “16,50” is never silently read as 1,650. Dates become ISO 8601 (YYYY-MM-DD). In automatic order, a slash date that reads both ways (04/05/2026) is left unchanged and listed; choose day first or month first from evidence. Two-digit years 00–68 mean 2000–2068 and 69–99 mean 1969–1999. Spreadsheet serials use Excel’s 1900 date system, counting days from 30 December 1899, so 46085 is 4 March 2026.
Missing values
Missing completely at random (MCAR): the chance a value is missing is the same for every row. Missing at random (MAR): it depends only on things you observe, such as subscription. Missing not at random (MNAR): it depends on something you don’t observe, often the missing value itself. The lab tests whether missingness differs across the groups of each other column (a chi-square test, reported when p < 0.01 and the rates differ by at least 15 points). A significant result rules out MCAR for that pattern; no test can tell MAR from MNAR.
Leakage
Scaling, imputation and target encoding learn numbers from the data: a mean, a minimum, a group average. For a model, learn them from the training rows only and apply the same numbers to the test rows; otherwise information from the test rows leaks into training and the test score looks better than it should. The lab offers a seeded 70% training subset (or a split column) for this, and its notes say which rows the parameters came from.
Limits. The lab finds problems that break a pattern or a rule; a value can be wrong and still look normal. Spelling suggestions come from spelling alone and must be checked. Issue detection uses rules from each dataset’s data dictionary; for your own data it can only use general checks. It handles one table at a time (no joins) and is built for teaching-sized data: it stays responsive up to a few thousand rows. Every decision is yours, and the report records it.
Sources
- NIST/SEMATECH e-Handbook of Statistical Methods, section 1.3.5.17, Detection of outliers (the modified z-score and the 3.5 threshold).
- NIST/SEMATECH e-Handbook of Statistical Methods, section 7.1.6, What are outliers in the data? (inner and outer fences).
- NIST/SEMATECH e-Handbook of Statistical Methods, section 1.3.5.11, Measures of skewness and kurtosis.
- scikit-learn documentation, Common pitfalls and recommended practices, section 12.2 on data leakage.
- NumPy documentation, numpy.quantile (the default linear method, Hyndman and Fan type 7).
- Microsoft Support, Date systems in Excel.
- Stef van Buuren, Flexible Imputation of Missing Data, second edition, section 1.2, Concepts of MCAR, MAR and MNAR.
Sources checked on 22 October 2026.