Data Cleaning Exercises

Exercise · beginner · 15 min

Join orders to customers without matching blanks

Look up each order's customer segment. A missing key must never match the customer row whose key is also missing.

The rules

These rules decide what a correct result is. Your answer is checked on this dataset and on hidden datasets that follow the same rules.

Inputs
  orders(id, customer_key) — text. id is unique.
  customers(customer_key, segment) — text. Present keys are trimmed and unique. One row has a missing key.

Rules
  customer_key: remove ASCII whitespace (space, tab, CR, LF) at both ends; an empty result is missing.
  Add segment from customers by customer_key. Keep every order.
  An order whose key is missing, or has no matching customer, gets a missing segment.
  A missing key never matches a missing key.

Output (row order does not matter)
  clean(id text, customer_key text, segment text)

The data

orders · 9 rows

idcustomer_key
oz2w7missing
of1nxC076
okyr4··
olohuC031
olfmkC031
okpom·C021·
ogp5lmissing
oflukC076
oo7yvmissing

customers · 4 rows

customer_keysegment
missingUNASSIGNED
C031partner
C076retail
C021online

What you practise

  • joins
  • missing values
  • trimming

Your answer is also checked on hidden datasets generated from the same rules.

Download this dataset (CSV) with its data dictionary

Sign in to bookmark this and keep private notes.