DataBrighten

    Messy data for cleaning practice

    1,000 × 12rows × columns in the 1,000-row file

    Updated 3 November 2026. Fictional U.S. businesses and people, generated with a fixed seed.

    CSV XLSX JSON SQL
    textnumberdateyes/no
    Largest file10,000rows; also 100 and 1,000
    Columns12with a data dictionary below
    Dates2019-01-10 – 2024-12-28range in the file
    LicenceCC0synthetic, free for any use

    What is in it

    The customer table with problems added on purpose: repeated customer IDs, names in the wrong case, trailing spaces, blank emails, phone numbers in three formats, state names mixed with codes, dates in three formats, and $ signs or N/A in the spend column.

    Practise first, then check: the data cleaning exercise counts every problem in this file. The clean version is the customer data set.

    First rows

    point at a column to light it up

    customer_idtextfirst_nametextlast_nametextemailtextphonetextcitytextstatetextsignup_datedatesegmenttexttotal_ordersnumbertotal_spentnumbernotestext
    CU10001RyanJacksonRYAN.JACKSON0@EXAMPLE.COM(212) 555-0190OmahaNE2019-08-30Small Business71353
    CU10002MichaelChenmichael.chen@example.com(617) 555-0184HartfordCT05/21/2019Consumer2174.9
    CU10003DavidNelsondavid.nelson@example.com(206) 555-0147ChicagoIL2024-12-26Consumer3245.05
    CU10004sofiaRamirezsofia.ramirez@example.com(617) 555-0195NashvilleTN2024-04-26consumer2115.31
    CU10005LauraTorreslaura.torres@example.com305-555-0176CharlotteNC12/26/2023Consumer3259.43
    CU10006RyanDavisryan.davis@example.com(206) 555-0158MinneapolisMN3 Jul 2021Consumer$264.53duplicate?
    CU10007RebeccaSanchezrebecca.sanchez@example.com(305) 555-0179AtlantaGA2024-04-03Consumer191.5duplicate?
    CU10008BarbaraRamirezbarbara.ramirez7@example.com(512) 555-0113AustinTX14 Apr 2024Enterprise2214129.96
    CU10009MichaelWhitemichael.white@example.com(702) 555-0188DenverCO2019-02-13Consumer7651.35
    CU10010JenniferHilljennifer.hill@example.com2125550167Los AngelesCA2022-05-21Consumer3144.81call back

    Data dictionary

    ColumnTypeMeaning
    customer_idtextCustomer number
    first_nametextFictional first name
    last_nametextFictional last name
    emailtextEmail (example.com, not real)
    phonetextFictional number in the 555-01xx range reserved for fiction
    citytextCity
    statetextTwo-letter state code
    signup_datedateAccount created (2019-2024)
    segmenttextConsumer, Small Business or Enterprise
    total_ordersintegerOrders placed so far
    total_spentdecimalLifetime spend (USD)
    notestextFree text, often blank

    Practice questions

    answers checked in SQL and in Python

    How many customer_id values appear more than once?

    40 duplicated IDs

    Excel

    =SUMPRODUCT(--(COUNTIF(A2:A1001,A2:A1001)>1))/2  (each duplicate pair counted once)

    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

    Excel

    =COUNTBLANK(D2:D1001)

    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