Chapter 5 · 8 levels

Cleaning an export

Find duplicate customers, fix stray spaces and casing, turn numbers and dates stored as text back into real values, fill missing costs with a stated reason, flag outliers, then hand back a cleaned copy.

Start

Every export lies a little. A customer signs up twice with the same email typed in capitals. A city arrives as ' dallas '. A refund amount comes out of a portal as text, so SUM quietly skips it. Three systems write the same date three ways. A cost is blank, and one quantity reads 100000. None of this raises an alarm; it just makes the numbers wrong.

In this chapter you clean real slices of the Harbor & Pine data, mess included: the duplicate customer accounts, the padded and mis-cased cities and states, refunds stored as text, order dates in three formats, products with no cost on file, and order lines with impossible quantities. You will flag duplicates with COUNTIF on a cleaned key, rebuild text with TRIM, PROPER and UPPER, prove what changed with EXACT, convert with VALUE, DATEVALUE and DATE, count gaps with COUNTBLANK and fill them from a MEDIAN, and fence off outliers with QUARTILE.INC. The boss level hands back a cleaned customer export with a “problems found” column.

The rule for the whole chapter: never overwrite the raw data. Clean into new columns next to it, and write down why each value changed, so anyone can check your work.

Levels

  1. Duplicate customers by email

    from Jennifer Castillo, Support Team Lead

    duplicates, countif, trim, lower, helper columns

    Code

  2. Fix spaces and casing in city and state

    from Helen Parker, Support Specialist

    trim, proper, upper, exact, data cleaning

    Code

  3. Numbers stored as text

    from Nicole Cox, Support Specialist

    numbers as text, value, sum, count, counta, data types

    Code

  4. Quiz: SUM vs + on numbers stored as text

    from Laura Ross, Inventory Specialist

    numbers as text, sum, arithmetic coercion, data types

    Quiz

  5. Dates stored as text, in three formats

    from Edward Jones, Logistics Coordinator

    dates as text, datevalue, date, isnumber, data cleaning

    Code

  6. Missing values, and a reason for each fix

    from Charles Ruiz, Data Analyst

    missing values, countblank, median, imputation, if, data cleaning

    Code

  7. Outliers in a quantity column

    from Debra Marshall, Warehouse Manager

    outliers, quartile, iqr, median, averageif, if, or

    Code

  8. Boss: hand back a cleaned customer export

    from Angela Lewis, Senior Data Analyst

    data cleaning, trim, proper, countif, exact, textjoin

    Boss · 3 steps

Cookie settings

Pick what may run on this site. You can change this any time from "Cookie settings" at the bottom of every page.