Session 1: Data Structure & Types

Everything from here to the final project uses one dataset, because that is how analysis actually works — you do not get eight separate clean problems, you get one messy file and eight weeks of dealing with it. It is an order export from a mid-sized distributor of household goods to retailers across Lagos, Ogun and Oyo, produced by their invoicing tool. About 1,200 rows, one row per order line, and twelve columns: OrderID, OrderDate, CustomerName, Phone, State, Product, Category, Quantity, UnitPrice, LineTotal, PaymentMethod and SalesRep.

It is messy in the ordinary way real exports are messy, not in a contrived way. State appears as Lagos, lagos, LAGOS, Lagos State and LG. Dates arrive in three different formats, and one of them is ambiguous. Names carry stray double spaces and inconsistent capitalisation. Phone numbers appear with and without the country code, with dashes, and with spaces. Some Quantity and UnitPrice values are stored as text rather than numbers, several carrying a naira sign and a thousands comma. Fourteen rows are duplicated because the counter staff entered the same sale twice. And missing payment methods are recorded as blank, N/A, a dash, and the word unknown, interchangeably.

This matters because the mess is not decoration. Every one of these defects will produce a wrong answer if you ignore it — LAGOS and Lagos are two states to a pivot table, a text-stored price sums to zero, and a duplicated row doubles the revenue you report to a manager. We are going to spend the whole course finding out exactly how wrong, and then fixing it.

Every value in a spreadsheet has a type, and the type determines what you can do with it. The four that matter here are text, number, date and logical (TRUE/FALSE). Getting the type wrong does not produce an error — it produces a plausible, confident, wrong answer, which is far worse.

The commonest failure in our dataset is a number stored as text. Our UnitPrice column contains values like ₦4,500 and 4,500.00, which Excel reads as text because of the symbol and the comma. Sum a column of text-numbers and you get zero, or only the few genuine numbers, with no warning. The giveaway is that text-numbers are usually left-aligned while real numbers are right-aligned, and a small green triangle often appears in the cell corner.

Dates are the subtlest trap. A spreadsheet date is really a number — days since a fixed starting point — displayed as a date, which is what lets you subtract dates and group by month. A date stored as text cannot be grouped by month at all; the pivot table simply will not offer it. Our OrderDate column mixes 12/03/2025, 2025-03-12 and 12-Mar-25, and the first format is genuinely ambiguous: is it the 12th of March or the 3rd of December? That is not a formatting question, it is a question you must answer before you can trust any monthly figure.

Tidy data has a precise meaning, and it is worth memorising because it settles most structural arguments: one variable per column, one observation per row, one value per cell. Our order export is close to tidy, which is why it is analysable at all.

The violation to watch for is the report layout — the shape people produce when they make a spreadsheet look nice rather than work. A sheet with a title in row 1, a merged header spanning four columns, Lagos down the side and months across the top, with totals in a right-hand column and a grand total at the bottom, is unreadable to any analysis tool. Every one of those features — merged cells, embedded headings, total rows, blank spacer rows — is decoration that breaks a pivot table.

The other common violation is multiple values in one cell, and we have it: some Quantity cells read 2 pcs, and some Product cells read Bucket 20L (blue). A cell holding two facts cannot be counted, averaged or filtered reliably. The fix is to split it — Quantity as 2 and a separate Units column, Product as Bucket 20L with Colour separate — which is more work once and saves it every time afterwards.

Mess is not random, and understanding its source tells you where to look. Free-text entry is the biggest cause: any field a human types will contain variants, which is why State and Product are inconsistent while OrderID, generated by the system, is clean. If a column was typed by a person, assume it needs cleaning; if it was generated, check it anyway but expect less.

System boundaries are the second. Data that has crossed from one tool to another — exported from a POS, emailed as CSV, opened in Excel, re-saved — picks up changes at each hop. Opening a CSV in Excel and saving it can silently convert 08031234567 into a number and destroy the leading zero, or reinterpret a date. Our export has been through at least two tools, and the date formats show it.

Multiple people, no rules is the third, and it explains our fourteen duplicates and our four different spellings of missing. When three counter staff enter orders and nobody has said how to record an unknown payment method, you get blank, N/A, a dash and unknown — four values that all mean the same thing and that Excel treats as four different categories.

The discipline that separates people who get answers from people who get wrong answers is looking before calculating. Concretely: how many rows, and does that match what you expected? How many columns, and does each hold one thing? For every column, what type is it, how many distinct values does it have, and how many are empty? Are there duplicates? What are the minimum and maximum of every number, and do they make sense — a UnitPrice of zero, or of 4,500,000 for a bucket?

Most of this takes five minutes and answers itself. `=COUNTA(A:A)` tells you how many rows have anything; a pivot table with a field in Rows tells you every distinct value in that column instantly, which is how you discover that State has nine spellings for three states. `=MIN()` and `=MAX()` on every numeric column catch the impossible values — the negative quantity, the price entered as 45000 instead of 4500.

And the rule that protects everything else: never work on the original. Copy the export to a working file, keep the original untouched, and do all cleaning on the copy. You will make a mistake at some point — everyone does — and the difference between a five-second recovery and a request to re-export three months of orders is whether you kept the original.