Types of data

01The problem

You open Kolanut's orders.csv for the first time. Some columns hold numbers you can add up (quantity). Some hold numbers you must never add up (customer_id). Some hold dates. Before you calculate anything, you need to know what kind of data each column is, and what a single row stands for.

02The concept

Before you calculate anything, you need to know two things about a table: what kind of data each column holds, and what one row stands for. Get either wrong and the numbers you produce will look fine and be wrong.

Structured, semi-structured and unstructured

Most business analysis works on structured data, and so does this course. But much of a company's knowledge sits in unstructured data: the reason a customer left is in an email, not a column.

Quantitative and qualitative

Quantitative data is numbers that measure an amount. Qualitative (or categorical) data puts things into groups.

What you can do with each type

The type decides which calculations make sense. This table is worth remembering:

So you can say "34 Lagos customers" but never "the average region"; "Senior is above Mid" but not "Senior minus Junior equals Mid".

Identifiers: numbers that aren't numbers

customer_id 42 is not "twice" customer 21. An identifier is a label that happens to use digits. Adding or averaging IDs gives a meaningless number, though a spreadsheet will happily calculate it.

What you can do with an identifier:

• Count them: how many order lines?
• Count distinct values: how many different customers ordered?
• Use them to link tables: find customer 27 in customers.csv (next lesson).

Phone numbers, account numbers, postcodes and staff numbers are identifiers too. Store them as text, so a leading zero survives: 08089165939 as a number becomes 8089165939.

Dates and times

Dates deserve their own type, because they let you:

• group by day, month, quarter or year;
• measure time between events, such as days from order to delivery or hire to exit;
• compare periods, such as January to June this year against last year.

A spreadsheet stores a date as a number of days, which is why subtraction works. But a date stored as text can't be grouped or subtracted, and a date read in the wrong format (month-first instead of day-first) is silently wrong. You'll fix both in lesson 5.

Grain: what one row represents

The grain of a table is the answer to "one row = one what?".

Getting the grain wrong causes real errors. Each order line has its own order_id, and a shop that buys three products on the same day appears as three rows. So "how many orders?" depends on what you mean:

Neither 4,266 nor 3,982 is "wrong"; they answer different questions. The mistake is reporting one while the reader thinks it's the other. Say which you counted.

03Example

The first rows of orders.csv:

• order_id, customer_id, product_id: identifiers (labels, not quantities).
• order_date: a date.
• quantity: quantitative, discrete.
• unit_price: quantitative, continuous: the price actually charged for one unit (one pack).
• discount_pct: quantitative, the percentage taken off this line: 0, 5 or 10.

Reading the first row: order line 10001 was 14 packs of product 3 at ₦18,600 a pack, with no discount.

And customers.csv:

04Walkthrough

Open orders.csv in Google Sheets (File → Import → Upload) or Excel (File → Open).

1. Look at the header row. Each column name tells you what the column holds.
2. Press Ctrl + ↓ (Cmd + ↓ on a Mac) in column A to jump to the last row. The row number tells you how many rows there are. Remember that row 1 is the header: 4,267 − 1 = 4,266 rows.
3. For each column, decide: identifier, number, category or date? Write it down, as in the tables above.
4. Check numbers are numbers: they sit on the right of the cell. Text sits on the left.
5. Ask the grain question and write the sentence: "Each row is one order line."
6. Do the same for customers.csv and products.csv.

Summary

05Practice

06More practice

Optional drills. They don't count towards the certificate, but they're the fastest way to make this lesson stick. Several use a different dataset from the lesson on purpose: if you can do the same thing on unfamiliar data, you've really learned it.

07Check your understanding