Data cleaning and preparation
You find dirty, missing and inconsistent data, standardise formats, times and identifiers, document the cleaning and write a repeatable preparation script.
Level 201 · 18 lessons · 15 practice labs
Lessons
Recognising dirty data
- Kinds of dirty data: missing, duplicate, inconsistent, wrong
- Profiling data column by column
- Writing validity rules
- Quick task: profile an asset inventory
Standardising
- Normalising text: whitespace, case, Unicode
- Aligning timestamps and time zones
- IP addresses, hostnames and usernames
- Mapping categories: five spellings of one thing
- Quick task: align names across two inventories
Missing and duplicate
- Patterns and causes of missing data
- Fill in or drop
- Finding duplicates: exact and fuzzy matching
- Same person, different record: entity resolution
- Quick task: deduplicate a user list
Repeatable preparation
- Tidy data: one observation per row
- A cleaning log: what changed and why
- A preparation script with small tests
- Capstone: merge and clean a three-source inventory
Practice labs
- Profile column by column: Write each column's blank ratio, distinct count and most common value from Mavikum Bank's branch device list to profile.csv.
- Write validity rules: Write validity rules for the date, e-mail and phone fields of a subscriber table and pull out the rows that break them.
- Catch logical inconsistencies: Find cross-field inconsistencies such as tickets closed before they opened and users born in the future and report them by type.
- Whitespace and case chaos: Clean extra spaces and case differences in department names and write how far the number of distinct departments drops.
- Turkish characters and Unicode traps: Align usernames that fail to match because of İ/ı and combining-character differences using Unicode normalisation and show the match rate.
- Three sources with mixed time zones: Align timestamps from three sources in local time, UTC and offset ISO format to UTC and show how the order of events changes.
- Map five spellings to one category: Reduce operating-system spellings such as “Win10”, “Windows 10” and “win 10 pro” to one standard value with a mapping table.
- Standardise hostnames: Turn hostnames scattered across short names, fully qualified names and upper-case spellings into one form and count the duplicates.
- Validate and normalise IP addresses: Validate IP values with leading zeros, spaces and port suffixes using the ipaddress module and set aside the invalid ones with a reason.
- The pattern of missing data: Show in a table whether missing values are random or tied to a particular branch or date.
- Fill or drop: Decide, with reasons, whether to fill or drop missing values in three columns, apply it and write how the summary statistics change.
- Duplicate customers with fuzzy matching: Match customers recorded twice with name typos and different address formats using a similarity score and list the merge suggestions.
- Make a wide table tidy: Turn a report table with one column per month into long form with one observation per row.
- Keep a cleaning log: Add a log to a cleaning script that records how many rows each step changed and why, and produce cleaning_log.csv.
- Add tests to a preparation script: Write small tests for three functions in a data preparation script, use them to expose a hidden bug and fix it.
About this course
You find dirty, missing and inconsistent data, standardise formats, times and identifiers, document the cleaning and write a repeatable preparation script.
What you will learn
- Recognising dirty data
- Standardising
- Missing and duplicate
- Repeatable preparation
Prerequisites
Finishing the level 101 courses of the same path, or knowing those topics, is enough.
Lab environment
Lessons run in your own Kali Linux terminal that opens in the browser. Nothing to install; the lab starts clean in every lesson.
Frequently asked questions
- Is this course right for my level?
- The course is at level 201; it assumes you know the topics of the lower-level courses.
- Is there anything to install?
- No. Every lesson runs in your own lab environment that opens in the browser.
- How do lessons work?
- Each lesson has 8 to 10 steps and a short quiz. Early steps show the command to type, later steps give less help; if two attempts fail, the solution opens.
- Can I try it for free?
- Yes. The first three lessons of every course are free.