DataBrighten

    Data cleaning practice dataset

    8 questionswith checked answers ยท messy data for cleaning practice

    Find and fix duplicates, blanks, mixed date formats and text in number columns. Updated 3 November 2026.

    textnumberdateyes/no

    Steps

    1. Download messy-customers-1000.csv (or the .xlsx).
    2. Count each problem first (the questions below), then fix it: trim spaces, fix case, standardise state codes, parse the three date formats, strip $ and turn N/A into a blank.
    3. Remove duplicate customer_id rows, keeping the first.
    4. Compare with the clean customers file: each row here is a copy of the customers row with the same customer_id, before the problems were added.

    Questions

    try first, then open the answer

    How many customer_id values appear more than once?

    40 duplicated IDs

    SQL

    SELECT COUNT(*) FROM (SELECT customer_id FROM messy_customers GROUP BY customer_id HAVING COUNT(*)>1)

    File: messy-customers-1000

    How many rows have a blank email?

    44 rows

    SQL

    SELECT COUNT(*) FROM messy_customers WHERE email IS NULL OR email=''

    File: messy-customers-1000

    How many signup dates are not in YYYY-MM-DD form?

    225 rows

    SQL

    SELECT COUNT(*) FROM messy_customers WHERE signup_date NOT GLOB '[0-9][0-9][0-9][0-9]-[0-9][0-9]-[0-9][0-9]'

    File: messy-customers-1000

    How many total_spent values are not plain numbers?

    160 rows ($-prefixed text or N/A)

    SQL

    SELECT COUNT(*) FROM messy_customers WHERE typeof(total_spent) NOT IN ('integer','real')

    File: messy-customers-1000

    Count duplicated customer IDs

    Code

    import pandas as pd
m = pd.read_csv("messy-customers-1000.csv")
print((m["customer_id"].value_counts() > 1).sum())

    Output (run on our file)

    40

    File: messy-customers-1000.csv

    Count blank emails

    Code

    import pandas as pd
m = pd.read_csv("messy-customers-1000.csv")
print(m["email"].isna().sum())

    Output (run on our file)

    44

    File: messy-customers-1000.csv

    Parse the three date formats

    Code

    import pandas as pd
m = pd.read_csv("messy-customers-1000.csv")
d = pd.to_datetime(m["signup_date"], format="mixed")
print(d.isna().sum(), d.min().date(), d.max().date())

    Output (run on our file)

    0 2019-01-03 2024-12-28

    File: messy-customers-1000.csv

    Clean total_spent and drop duplicates

    Code

    import pandas as pd
m = pd.read_csv("messy-customers-1000.csv")
m["total_spent"] = pd.to_numeric(m["total_spent"].astype(str).str.replace("$", "", regex=False), errors="coerce")
m = m.drop_duplicates(subset="customer_id", keep="first")
print(len(m), m["total_spent"].isna().sum())

    Output (run on our file)

    960 40

    File: messy-customers-1000.csv