Sunflower Intelligence
Business Analytics & Data Science

B02 · Introductory

Data Cleaning and Preparation

12 lessons

B02 · Introductory · 12 lessons in four modules · about 16.5 hours

Data Cleaning and Preparation

Turn a messy export into data you can trust: profile it, fix records and formats, handle missing and unusual values, and prepare it for analysis without biasing the result.

What you will learn

  • Describe a dataset precisely: its unit of observation, variables, types and keys.
  • Profile a dataset and write validation rules that catch invalid records.
  • Find and resolve duplicates, wrong data types, inconsistent dates and messy text.
  • Choose between deleting and imputing missing values, and explain the bias each can cause.
  • Detect outliers, decide whether they are errors, and treat skewed variables.
  • Scale, encode and reshape data for analysis without leaking information from test data.
  • Document every cleaning decision in a reproducible data quality report.

Prerequisites

How it works

Each lesson teaches the idea from first principles, works examples step by step, lets you explore it in an interactive, then has you practise with feedback. A lesson is complete when you have worked its guided problems and scored 80% on its knowledge check. Mastery is tracked separately and comes back for review before you are likely to forget. Each module ends with a test; the course ends with a project and a final assessment.

Fictional case

Lantern Coffee Co. A fictional coffee business founded by Ana Lim. It starts as a catering and barista-training service, opens its own cafés and an online shop for beans, and plans a canned cold brew. Every name and figure in it is invented for teaching.

Not sure where to start?

The diagnostic has 12 questions across the course. It doesn’t count toward anything; it suggests the module to start from. Take the diagnostic.

Modules and lessons

Module 1 Understanding your data

  1. Datasets: rows, columns, records and variablesBefore you clean or analyse a table, describe it exactly: what one row is, what kind of value each column holds, which column identifies each row, and what every column is supposed to contain. Get this wrong and correct arithmetic gives the wrong answer.75 min · not started
  2. Profiling a dataset and writing validation rulesFind out what a dataset really contains before you change it: profile every column, name each problem by the quality dimension it affects, and turn what you find into precise validation rules and a cleaning plan.75 min · not started
  3. Module 1 test10 questions on this module. 70% passes.not taken

Module 2 Fixing records and formats

  1. Duplicate recordsA record counted twice inflates revenue, customer numbers and averages without any warning. Learn to tell exact copies, conflicting versions and near duplicates apart, decide what counts as “the same” for the business, and remove duplicates without deleting genuine repeat orders.80 min · not started
  2. Incorrect data types and inconsistent datesA price stored as text won’t add up, and a date stored as text won’t sort. Learn to give every column the right data type, convert numbers written in different local formats, keep identifiers as text, and turn a column of mixed dates into unambiguous ISO 8601 dates without guessing.75 min · not started
  3. Text cleaning and consistent categories“North”, “north ”, “N.” and “Nrth” are one region to a person and four to a computer. Learn to normalise whitespace, case and Unicode, consolidate variants with a reviewed mapping table, split and extract fields with simple patterns, and see how consolidation changes counts and averages.70 min · not started
  4. Module 2 test12 questions on this module. 70% passes.not taken

Module 3 Missing and unusual values

  1. Missing valuesA blank cell is a question: why is the value missing? Learn to find every form missing data takes, judge whether the gaps are random, and choose between deleting and imputing without quietly biasing the answer.75 min · not started
  2. Outliers and impossible valuesSome unusual values are mistakes, some are impossible and some are the most important rows in the file. Learn to flag them with quartile fences and robust z-scores, to see why the ordinary z-score can miss them, and to choose and document a treatment that fits the question.90 min · not started
  3. Skewed data and transformationsOrder values, hotel bills and study hours pile up at the low end and stretch out in a long tail. Learn to measure that skew, to see when it distorts summaries, outlier rules and models, and to transform a variable, read the results back in real units, and know when to leave it alone.75 min · not started
  4. Module 3 test12 questions on this module. 70% passes.not taken

Module 4 Preparing data for analysis

  1. Normalisation and standardisationPut numeric variables on comparable scales with min–max normalisation, standardisation and robust scaling, so that no variable dominates an analysis just because of its units. Learn which methods need scaling, what an outlier does to each scaler, and how to fit the scaling on training data only so that no test information leaks into the model.105 min · not started
  2. Encoding categorical variablesTurn categories such as region, room type or roast into numbers a model can use, without inventing an order or a distance the categories don’t have. Learn one-hot and dummy coding, ordinal, frequency and target encoding, and how to handle the dummy-variable trap, rare and unseen categories, and variables with thousands of values.90 min · not started
  3. Transforming and reshaping dataClean data rarely arrives in the shape a question needs. Learn to derive new variables, sort values into bins, aggregate, reshape between wide and long, and join tables, and to check every step so that it adds information rather than errors.105 min · not started
  4. Data quality reporting and a reproducible workflowCleaning is finished only when someone else can see what you did, why, and what it means for the results, and can repeat it. Learn to write a data quality report, keep a cleaning log, and run a reproducible workflow from the raw export to analysis-ready data.70 min · not started
  5. Module 4 test12 questions on this module. 70% passes.not taken

Project and final assessment

  1. Project: Clean Lantern’s order exportTake Lantern Coffee’s raw online-shop export from messy to analysis-ready in the Data Cleaning Lab, meet its validation rules, and write the data quality report.not taken
  2. Final assessment24 questions across the whole course. 70% passes.not taken

The course is complete when every lesson is complete, every module test is passed, the project is finished and the final assessment is passed.

Resources

  • Data Cleaning Lab: Inspect a messy dataset, choose cleaning steps, and see what each one does to the data and the conclusions.
  • Glossary: every term this course defines.
  • Formulas: every formula with its symbols and units.
  • Review: flashcards and the questions you missed, when they are due.

Elsewhere on this site

Sources used in this course

  1. Wickham, H. (2014). Tidy Data. Journal of Statistical Software, 59(10). doi:10.18637/jss.v059.i10. jstatsoft.org/article/view/v059i10
  2. Stevens, S. S. (1946). On the theory of scales of measurement. Science, 103(2684), 677–680. doi.org/10.1126/science.103.2684.677
  3. Velleman, P. F. & Wilkinson, L. (1993). Nominal, ordinal, interval, and ratio typologies are misleading. The American Statistician, 47(1), 65–72.
  4. Illowsky, B. & Dean, S. (2023). Introductory Statistics 2e, sections 1.2 “Data, Sampling, and Variation in Data and Sampling” and 1.3 “Frequency, Frequency Tables, and Levels of Measurement”. OpenStax. openstax.org/books/introductory-statistics-2e/pages/1-3-freq…
  5. U.S. Geological Survey. “Data Dictionaries”. USGS Data Management. usgs.gov/data-management/data-dictionaries
  6. The PostgreSQL Global Development Group. PostgreSQL 18 Documentation, section 5.5, “Constraints”. postgresql.org/docs/current/ddl-constraints.html
  7. DAMA UK Working Group on “Data Quality Dimensions” (2013). The Six Primary Dimensions for Data Quality Assessment. White paper, October 2013.
  8. Government Data Quality Hub (2020). The Government Data Quality Framework. GOV.UK, published 3 December 2020. gov.uk/government/publications/the-government-data-quality-f…
  9. Abedjan, Z., Golab, L. & Naumann, F. (2015). Profiling relational data: a survey. The VLDB Journal, 24(4), 557–581. dspace.mit.edu/handle/1721.1/106176
  10. Eurostat, CROS portal. Validation in the ESS: “Definitions” (drawing on the ESS Handbook on Methodology for Data Validation). cros.ec.europa.eu/book-page/definitions
  11. ISO/IEC 25012:2008. Software engineering — Software product Quality Requirements and Evaluation (SQuaRE) — Data quality model. International Organization for Standardization. iso.org/standard/35736.html
  12. Levenshtein, V. I. (1966). Binary codes capable of correcting deletions, insertions, and reversals. Soviet Physics Doklady, 10(8), 707–710. Russian original: Doklady Akademii Nauk SSSR, 163(4), 845–848, 1965. mathnet.ru/eng/dan31411
  13. Wagner, R. A. & Fischer, M. J. (1974). The string-to-string correction problem. Journal of the ACM, 21(1), 168–173. projects.csail.mit.edu/jacm/References/wagnerf1974:168.html
  14. Fellegi, I. P. & Sunter, A. B. (1969). A theory for record linkage. Journal of the American Statistical Association, 64(328), 1183–1210.
  15. pandas documentation. pandas.DataFrame.duplicated and pandas.DataFrame.drop_duplicates (the subset and keep arguments). pandas.pydata.org/docs/reference/api/pandas.DataFrame.drop_d…
  16. PostgreSQL Global Development Group. PostgreSQL Documentation: SELECT (the DISTINCT and HAVING clauses). postgresql.org/docs/current/sql-select.html
  17. Microsoft Support. Filter for or remove duplicate values (Excel). support.microsoft.com/en-us/office/filter-for-or-remove-dupl…
  18. International Organization for Standardization. ISO 8601: Date and time format. Describes ISO 8601-1:2019 and ISO 8601-2:2019. iso.org/iso-8601-date-and-time-format.html
  19. Klyne, G. & Newman, C. (2002). RFC 3339: Date and Time on the Internet: Timestamps. Internet Engineering Task Force. rfc-editor.org/rfc/rfc3339
  20. Microsoft Support. Date systems in Excel. support.microsoft.com/en-us/office/date-systems-in-excel-e7f…
  21. Microsoft Learn. Excel incorrectly assumes that the year 1900 is a leap year. learn.microsoft.com/en-us/office/troubleshoot/excel/wrongly-…
  22. Microsoft Support. Set automatic data conversions (Excel). support.microsoft.com/en-us/Excel/set-automatic-data-convers…
  23. Ziemann, M., Eren, Y. & El-Osta, A. (2016). Gene name errors are widespread in the scientific literature. Genome Biology, 17, article 177. dro.deakin.edu.au/articles/journal_contribution/Gene_name_er…
  24. Unicode Consortium. Unicode Standard Annex #15: Unicode Normalization Forms. unicode.org/reports/tr15
  25. Microsoft Support. TRIM function (Excel). support.microsoft.com/en-us/office/trim-function-410388fa-c5…
  26. Python Software Foundation. Built-in Types: str.casefold(). The Python Standard Library documentation. docs.python.org/3/library/stdtypes.html
  27. WHATWG. HTML Living Standard, section on the email input type (definition of a valid email address). html.spec.whatwg.org/dev/input.html
  28. Wickham, H. (2014). Tidy data. Journal of Statistical Software, 59(10). jstatsoft.org/article/view/v059i10
  29. International Organization for Standardization. ISO 3166: Country codes. iso.org/iso-3166-country-codes.html
  30. Rubin, D. B. (1976). Inference and missing data. Biometrika, 63(3), 581–592. doi:10.1093/biomet/63.3.581. dash.harvard.edu/entities/publication/73120378-8764-6bd4-e05…
  31. Rubin, D. B. (1987). Multiple Imputation for Nonresponse in Surveys. New York: Wiley. ci.nii.ac.jp/ncid/BA00677174
  32. van Buuren, S. (2018). Flexible Imputation of Missing Data, 2nd edition, sections 1.2 “Concepts of MCAR, MAR and MNAR” and 1.3 “Ad-hoc solutions”. Boca Raton: CRC/Chapman & Hall. Free online. stefvanbuuren.name/fimd/sec-simplesolutions.html
  33. Molenberghs, G., Beunckens, C., Sotto, C. & Kenward, M. G. (2008). Every missingness not at random model has a missingness at random counterpart with equal fit. Journal of the Royal Statistical Society, Series B, 70(2), 371–388. researchonline.lshtm.ac.uk/id/eprint/7959
  34. scikit-learn developers. User Guide: “Imputation of missing values” (SimpleImputer, IterativeImputer, MissingIndicator). scikit-learn.org/stable/modules/impute
  35. scikit-learn developers. User Guide: “Common pitfalls and recommended practices”, section on data leakage during pre-processing. scikit-learn.org/stable/common_pitfalls.html
  36. NIST/SEMATECH e-Handbook of Statistical Methods, section 7.1.6, “What are outliers in the data?”. itl.nist.gov/div898/handbook/prc/section1/prc16.htm
  37. NIST/SEMATECH e-Handbook of Statistical Methods, section 1.3.5.17, “Detection of Outliers” (modified z-score of Iglewicz and Hoaglin; masking and swamping). itl.nist.gov/div898/handbook/eda/section3/eda35h.htm
  38. Shiffler, R. E. (1988). Maximum z scores and outliers. The American Statistician, 42(1), 79–80. doi:10.1080/00031305.1988.10475530.
  39. Illowsky, B., Dean, S. et al. (2023). Introductory Statistics 2e, section 2.3, “Measures of the Location of the Data”. OpenStax. openstax.org/books/introductory-statistics/pages/2-3-measure…
  40. Hyndman, R. J. & Fan, Y. (1996). Sample quantiles in statistical packages. The American Statistician, 50, 361–365; summarised in the R documentation for quantile. rdocumentation.org/packages/stats/topics/quantile
  41. NumPy documentation: numpy.quantile (default method “linear”, Hyndman and Fan’s method 7). numpy.org/devdocs/reference/generated/numpy.quantile.html
  42. NIST/SEMATECH e-Handbook of Statistical Methods, section 1.3.5.11, “Measures of Skewness and Kurtosis”. itl.nist.gov/div898/handbook/eda/section3/eda35b.htm
  43. NIST/SEMATECH e-Handbook of Statistical Methods, section 1.3.3.6, “Box-Cox Normality Plot”. itl.nist.gov/div898/handbook/eda/section3/boxcoxno.htm
  44. Box, G. E. P. & Cox, D. R. (1964). An analysis of transformations. Journal of the Royal Statistical Society, Series B, 26(2), 211–252. doi:10.1111/j.2517-6161.1964.tb00553.x.
  45. Illowsky, B., Dean, S. et al. (2023). Introductory Statistics 2e, section 2.6, “Skewness and the Mean, Median, and Mode”. OpenStax. openstax.org/books/introductory-statistics/pages/2-6-skewnes…
  46. scikit-learn developers. API reference: sklearn.preprocessing.PowerTransformer (Box–Cox and Yeo–Johnson; cites Yeo, I.-K. & Johnson, R. A. (2000), Biometrika, 87(4), 954–959). scikit-learn.org/stable/modules/generated/sklearn.preprocess…
  47. NumPy documentation: numpy.log1p. numpy.org/doc/2.0/reference/generated/numpy.log1p.html
  48. scikit-learn developers. Preprocessing data, user guide section 8.3 (standardisation, scaling to a range, scaling data with outliers, non-linear transformation, normalisation). scikit-learn 1.9.1 documentation. scikit-learn.org/stable/modules/preprocessing.html
  49. scikit-learn developers. StandardScaler, API reference (notes on the biased standard-deviation estimator). scikit-learn 1.9.1 documentation. scikit-learn.org/stable/modules/generated/sklearn.preprocess…
  50. scikit-learn developers. MinMaxScaler, API reference (formula, feature range and the clip option). scikit-learn 1.9.1 documentation. scikit-learn.org/stable/modules/generated/sklearn.preprocess…
  51. scikit-learn developers. RobustScaler, API reference (median and interquartile-range scaling). scikit-learn 1.9.1 documentation. scikit-learn.org/stable/modules/generated/sklearn.preprocess…
  52. scikit-learn developers. Common pitfalls and recommended practices, user guide section 12 (inconsistent preprocessing and data leakage). scikit-learn 1.9.1 documentation. scikit-learn.org/stable/common_pitfalls.html
  53. scikit-learn developers. Importance of Feature Scaling, example gallery (k-nearest neighbours and PCA with and without standardisation; tree-based models almost unaffected). scikit-learn 1.9.1 documentation. scikit-learn.org/stable/auto_examples/preprocessing/plot_sca…
  54. scikit-learn developers. Preprocessing data, user guide section 8.3.4, “Encoding categorical features” (one-hot and ordinal encoding, infrequent categories, target encoder with cross fitting and smoothing). scikit-learn 1.9.1 documentation. scikit-learn.org/stable/modules/preprocessing.html
  55. scikit-learn developers. OneHotEncoder, API reference (the drop and handle_unknown options); see also the OrdinalEncoder, LabelEncoder and TargetEncoder pages. scikit-learn 1.9.1 documentation. scikit-learn.org/stable/modules/generated/sklearn.preprocess…
  56. pandas development team. pandas.get_dummies, API reference. pandas 3.1.0 documentation. pandas.pydata.org/docs/reference/api/pandas.get_dummies.html
  57. Minitab. Coding schemes for categorical predictors (1, 0 coding with a reference level). Minitab support. support.minitab.com/en-us/minitab/help-and-how-to/statistica…
  58. Micci-Barreca, D. (2001). A preprocessing scheme for high-cardinality categorical attributes in classification and prediction problems. ACM SIGKDD Explorations Newsletter, 3(1), 27–32. doi.org/10.1145/507533.507538
  59. Pargent, F., Pfisterer, F., Thomas, J. & Bischl, B. (2022). Regularized target encoding outperforms traditional methods in supervised machine learning with high cardinality features. Computational Statistics, 37, 2671–2692. Free preprint: arXiv:2104.00629. arxiv.org/abs/2104.00629
  60. Wickham, H. (2014). Tidy data. Journal of Statistical Software, 59(10), 1–23. jstatsoft.org/article/view/v059i10
  61. pandas development team. pandas.merge (API reference: join types, the validate and indicator options, and the matching of missing keys). pandas documentation. pandas.pydata.org/docs/reference/api/pandas.merge.html
  62. pandas development team. Reshaping and pivot tables (user guide: pivot, pivot_table and melt). pandas documentation. pandas.pydata.org/docs/user_guide/reshaping.html
  63. pandas development team. Group by: split-apply-combine (user guide, including the handling of missing group keys). pandas documentation. pandas.pydata.org/docs/user_guide/groupby.html
  64. PostgreSQL Global Development Group. PostgreSQL 18 Documentation, section 7.2.1.1, “Joined Tables”. postgresql.org/docs/current/queries-table-expressions.html
  65. Weinberger, N. Simpson’s paradox. The Stanford Encyclopedia of Philosophy, first published 24 March 2021, substantive revision 6 June 2026. plato.stanford.edu/entries/paradox-simpson
  66. Broman, K. W. & Woo, K. H. (2018). Data organization in spreadsheets. The American Statistician, 72(1), 2–10. Free preprint: PeerJ Preprints 6:e3183v2. peerj.com/preprints/3183
  67. Wilson, G., Bryan, J., Cranston, K., Kitzes, J., Nederbragt, L. & Teal, T. K. (2017). Good enough practices in scientific computing. PLOS Computational Biology, 13(6), e1005510. journals.plos.org/ploscompbiol/article?id=10.1371/journal.pc…
  68. The Turing Way Community (2025). The Turing Way: A handbook for reproducible, ethical and collaborative research, “Definitions for reproducibility”. Zenodo, doi:10.5281/zenodo.3233853. book.the-turing-way.org/reproducible-research/overview/overv…
  69. Gebru, T., Morgenstern, J., Vecchione, B., Vaughan, J. W., Wallach, H., Daumé III, H. & Crawford, K. (2021). Datasheets for datasets. Communications of the ACM, 64(12), 86–92. Free version: arXiv:1803.09010. arxiv.org/abs/1803.09010
  70. Government Data Quality Hub (2020). The Government Data Quality Framework. UK Government, published 3 December 2020. gov.uk/government/publications/the-government-data-quality-f…
  71. Information Commissioner’s Office. Principle (c): Data minimisation (UK GDPR guidance). ico.org.uk/for-organisations/uk-gdpr-guidance-and-resources/…