Data Cleaning Exercises

Exercise · intermediate · 15 min

Append monthly files under a declared schema

Three files share most columns. Stack the ones that match the schema, record which file each row came from, and reject a file that's missing a required column.

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 (three files, all text)
  jan(id, amount)            feb(id, amount, channel)            bad(id, currency)

Rules
  Schema: required columns id and amount; optional column channel. No other columns are allowed.
  A file missing a required column, or containing a column outside the schema, is rejected as a whole.
  Keep every row of accepted files. A missing optional column gives a missing channel.
  source_file is the name of the file the row came from (jan, feb, …).
  amount is always a whole number in accepted files.

Output (row order does not matter)
  clean(id text, amount int64, channel text, source_file text)

The data

jan · 6 rows

idamount
iz6d8
rna5262
s38t410
pk5w345
r8id426
azjp425

feb · 4 rows

idamountchannel
irsl91web
byjg143store
fate28missing
ujtb207store

bad · 3 rows

idcurrency
o8wvEUR
vmuvEUR
yt74EUR

What you practise

  • combining files
  • schemas
  • lineage

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.