A file arrives with 20,000 orders
An online shop (made up) sends you a big file of app orders. It looks ready. But one order says the buyer is 250 years old. Another has a spend of minus 8 pounds.
If you add these up as they are, your answer is wrong. Before any chart, the data must be prepared.
By the end of this lesson
Note
I can prepare a data set for analysis by checking, cleansing and coding it.
Key words
| Word | What it means |
|---|---|
| Validity | data follows the rules, such as being in range |
| Integrity | data is complete, consistent and properly linked |
| Coding data | turning values into groups or short codes |
| Outlier | a value far from all the others |
| Cleansing | finding and fixing errors in data |
The cells students fill in are left blank in this preview.
Starter: what you already know
Have a go
Starter: what you already know
4 questionsYour answers are marked here and are not kept. A college account keeps every student’s marks.
Some categories of data need more care
Analysts group data by what it is about. The group tells you what you can learn from it and how careful you must be.
Health data is the most private of all. Money data can be used to cheat people if it leaks. Environmental data, such as rain and wind, is about places and not people, so it needs the least care.
Five categories of data
| Category | Example fields | Care needed |
|---|---|---|
| Medical | Blood group, conditions | Highest: only trained staff, only with permission |
| Personal | Date of birth, address, phone | High: can find one person |
| Financial | Salary, debt, card use | High: can be used for fraud |
| Environmental | Rain, wind, tides, sunlight | Low: about places, not people |
| Retail habits | Favourite shops, spend | Medium: shows what a person does |
The cells students fill in are left blank in this preview.
Check validity and integrity first
Validity asks: does each value follow the rules? An age of 250 is out of range, so it is not valid. A spend of minus 8 is not valid.
Integrity asks: is the data whole and linked properly? Does an order point to a customer who exists? Is any order there twice? A missing town is also an integrity fault.
Try it: find the faults
-- Validity: ages and spends that are out of range.
SELECT order_id, age, spend FROM app_orders
WHERE age NOT BETWEEN 10 AND 100 OR spend <= 0;
-- Integrity: orders that appear more than once.
SELECT order_id, COUNT(*) AS copies FROM app_orders
GROUP BY order_id HAVING COUNT(*) > 1;
-- Integrity: orders whose customer does not exist.
SELECT app_orders.order_id, app_orders.customer_ref FROM app_orders
LEFT JOIN app_customers ON app_orders.customer_ref = app_customers.customer_ref
WHERE app_customers.customer_ref IS NULL;
-- Try: find orders with no town (town IS NULL).