What data analysis is: from question to decision

Data analysis is the work of answering a question with data, carefully enough that someone can act on the answer. Behind almost every question there is a decision: how many cooks to put on the Saturday shift, whether to keep a new checkout page, which bus route needs a second bus. The analysis is worth doing because that decision is better made with evidence than with a hunch.

The work runs as a loop. You pin down the question and the decision behind it, find the data, clean it, explore it, give an answer with its definition, and explain it to the people who decide. What you learn on the way often sharpens the question, and the loop starts again.

The step that changes answers most comes before any code: writing down exactly what you will measure. This lesson walks through the loop, writes one metric definition in full, and runs 84 days of canteen data through two reasonable definitions of “busy” that point in opposite directions.

The analysis loop

1. Question. Find out who will decide what, and by when. Ask which answer would change their mind: if no possible answer would, the analysis becomes a report nobody uses.
2. Data. Find out what exists, who keeps it and how it was collected. Write down what one row of each table stands for before you use it.
3. Cleaning. Fix what would bend the answer: numbers stored as text, missing values, duplicates, impossible values. Record every change in a data log, so that someone else can repeat it.
4. Exploration. Summarise, group and plot. Look for what surprises you, because a surprise is either a finding or a data problem, and you need to know which one it is.
5. Answer. Give the number with its definition and with how sure you are: “34.8 orders per open hour at weekends, over the last 12 weeks, with closed days left out”.
6. Communication. Lead with the answer and the decision it supports, then the evidence, then the limits. A table or a chart is the evidence; the sentence above it is the message.

The arrows back matter as much as the ones forward. Exploration often shows that the data cannot answer the question as it was asked (a shop records its sales but not how many people walked in, say), and the honest move is to go back to step 1 with a question the data can answer.

Start from the decision

Requests reach analysts as vague questions: “How are sales doing?”, “Is the new menu working?”, “Are weekends busier?”. Before you open any file, ask the person who asked:

• What will you do differently depending on the answer?
• By when do you need it, and how precise does it have to be?
• What answer would surprise you?

The canteen owner who asked “are weekends busier?” answers the first question at once: she wants to know whether to roster a second cook on Saturdays and Sundays. That one sentence settles what “busy” has to mean for her: how much work the kitchen has while it is open.

A question becomes a metric

A metric is a number computed the same way every time, from a definition that is written down. A useful definition has five parts:

Every part is a choice that can change the answer. “Monthly active users” sounds precise, yet it is a different number when “active” means opening the app, signing in or placing an order, and again when the month is counted in UTC rather than in local time. Two teams that report “active users” from different definitions end up arguing about numbers that were never comparable.

Two definitions, two answers

The canteen kept one row per day for 12 weeks: the week, the day, how many hours it was open and how many orders it took. On weekdays it opens for 12 hours, on Saturdays and Sundays for 6, and in week 6 it stayed shut on Wednesday for a holiday. The numbers are invented for this lesson, with a fixed random seed so that they never change, but their shape is a common one: shorter opening hours and a busier counter at weekends. Four of the 84 rows:

This program computes both definitions from the same table with pandas, the library most of this track uses:

# Are weekends busier at the canteen? Two definitions of "busy" on the same 84 days.
import pandas as pd

days = pd.read_csv("canteen_days.csv")  # one row per day: week, day, open_hours, orders
open_days = days[days["open_hours"] > 0]  # the filter: a closed day is not a slow day
weekend = open_days["day"].isin(["Sat", "Sun"])
open_days = open_days.assign(day_type=weekend.map({True: "weekend", False: "weekday"}))

busy = open_days.groupby("day_type").agg(
    days=("day", "size"), orders=("orders", "sum"), open_hours=("open_hours", "sum")
)
busy["per_day"] = (busy["orders"] / busy["days"]).round(1)  # definition A
busy["per_open_hour"] = (busy["orders"] / busy["open_hours"]).round(1)  # definition B
print(busy)

weekdays = days[~days["day"].isin(["Sat", "Sun"])]
print("\nWeekday orders per day if the closed day counted:", round(weekdays["orders"].mean(), 1))

          days  orders  open_hours  per_day  per_open_hour
day_type                                                  
weekday     59   17053         708    289.0           24.1
weekend     24    5011         144    208.8           34.8

Weekday orders per day if the closed day counted: 284.2

week,day,open_hours,orders
1,Mon,12,262
1,Tue,12,270
1,Wed,12,277
1,Thu,12,313
1,Fri,12,288
1,Sat,6,202
1,Sun,6,200
2,Mon,12,292
2,Tue,12,288
2,Wed,12,279
2,Thu,12,304
2,Fri,12,338
2,Sat,6,229
2,Sun,6,199
3,Mon,12,270
3,Tue,12,277
3,Wed,12,250
3,Thu,12,310
3,Fri,12,314
3,Sat,6,232
3,Sun,6,204
4,Mon,12,300
4,Tue,12,251
4,Wed,12,260
4,Thu,12,321
4,Fri,12,351
4,Sat,6,232
4,Sun,6,218
5,Mon,12,289
5,Tue,12,243
5,Wed,12,265
5,Thu,12,292
5,Fri,12,302
5,Sat,6,214
5,Sun,6,213
6,Mon,12,302
6,Tue,12,275
6,Wed,0,0
6,Thu,12,255
6,Fri,12,334
6,Sat,6,209
6,Sun,6,191
7,Mon,12,281
7,Tue,12,276
7,Wed,12,276
7,Thu,12,261
7,Fri,12,318
7,Sat,6,229
7,Sun,6,200
8,Mon,12,287
8,Tue,12,297
8,Wed,12,260
8,Thu,12,282
8,Fri,12,351
8,Sat,6,215
8,Sun,6,196
9,Mon,12,255
9,Tue,12,252
9,Wed,12,251
9,Thu,12,257
9,Fri,12,334
9,Sat,6,236
9,Sun,6,176
10,Mon,12,296
10,Tue,12,268
10,Wed,12,275
10,Thu,12,269
10,Fri,12,347
10,Sat,6,223
10,Sun,6,193
11,Mon,12,286
11,Tue,12,298
11,Wed,12,308
11,Thu,12,291
11,Fri,12,323
11,Sat,6,225
11,Sun,6,186
12,Mon,12,309
12,Tue,12,270
12,Wed,12,275
12,Thu,12,315
12,Fri,12,313
12,Sat,6,205
12,Sun,6,184

It reads the file, keeps the days the canteen was open, labels each day as a weekday or a weekend day, and adds up the days, orders and open hours for each label. The last two columns divide those sums. You do not need to follow every line yet: what matters here is the definition that each line carries out.

The two definitions disagree:

• Per day (definition A), weekdays win clearly: 289.0 orders against 208.8, about 38 % more.
• Per open hour (definition B), weekends win: 34.8 orders against 24.1, about 44 % more.

Neither number is a mistake; each one answers a different decision. For ordering ingredients, which are used up per day, definition A is the one that matters. For the owner’s decision, how many cooks the kitchen needs while it is open, definition B is the right one: at weekends the counter is about 44 % busier per hour, so a second cook is worth considering even though the day’s total is lower.

The filter matters as well. The last line counts the closed Wednesday as a weekday with no orders, and the weekday average falls from 289.0 to 284.2. One holiday in 12 weeks moves it by under 2 %, but a month with a 10-day closure in it would pull the average down by about a third. Definition B does not move at all, because the closed day adds neither orders nor open hours.

The same answer in three tools

A definition is not tied to a tool. Here is the same calculation in polars, which builds the query from expressions, and in DuckDB, which runs SQL straight on the CSV file:

# The same question in polars: filter, label, group, then divide the sums.
import polars as pl

days = pl.read_csv("canteen_days.csv")
busy = (
    days.filter(pl.col("open_hours") > 0)
    .with_columns(
        day_type=pl.when(pl.col("day").is_in(["Sat", "Sun"])).then(pl.lit("weekend")).otherwise(pl.lit("weekday"))
    )
    .group_by("day_type")
    .agg(days=pl.len(), orders=pl.col("orders").sum(), open_hours=pl.col("open_hours").sum())
    .with_columns(
        per_day=(pl.col("orders") / pl.col("days")).round(1),
        per_open_hour=(pl.col("orders") / pl.col("open_hours")).round(1),
    )
    .sort("day_type")
)
print(busy)

shape: (2, 6)
┌──────────┬──────┬────────┬────────────┬─────────┬───────────────┐
│ day_type ┆ days ┆ orders ┆ open_hours ┆ per_day ┆ per_open_hour │
│ ---      ┆ ---  ┆ ---    ┆ ---        ┆ ---     ┆ ---           │
│ str      ┆ u32  ┆ i64    ┆ i64        ┆ f64     ┆ f64           │
╞══════════╪══════╪════════╪════════════╪═════════╪═══════════════╡
│ weekday  ┆ 59   ┆ 17053  ┆ 708        ┆ 289.0   ┆ 24.1          │
│ weekend  ┆ 24   ┆ 5011   ┆ 144        ┆ 208.8   ┆ 34.8          │
└──────────┴──────┴────────┴────────────┴─────────┴───────────────┘

# The same question in DuckDB: SQL straight on the CSV file.
import duckdb

busy = duckdb.sql("""
    SELECT CASE WHEN day IN ('Sat', 'Sun') THEN 'weekend' ELSE 'weekday' END AS day_type,
           count(*) AS days,
           sum(orders) AS orders,
           sum(open_hours) AS open_hours,
           round(sum(orders) / count(*), 1) AS per_day,
           round(sum(orders) / sum(open_hours), 1) AS per_open_hour
    FROM 'canteen_days.csv'
    WHERE open_hours > 0
    GROUP BY day_type
    ORDER BY day_type
""")
print(busy)

┌──────────┬───────┬────────┬────────────┬─────────┬───────────────┐
│ day_type │ days  │ orders │ open_hours │ per_day │ per_open_hour │
│ varchar  │ int64 │ int128 │   int128   │ double  │    double     │
├──────────┼───────┼────────┼────────────┼─────────┼───────────────┤
│ weekday  │    59 │  17053 │        708 │   289.0 │          24.1 │
│ weekend  │    24 │   5011 │        144 │   208.8 │          34.8 │
└──────────┴───────┴────────┴────────────┴─────────┴───────────────┘

All three programs print 289.0 and 208.8 orders per day and 24.1 and 34.8 orders per open hour, in three different layouts; polars and DuckDB also print each column’s type under its name. A tool changes how you write a calculation and how fast it runs on large data. It does not change what the numbers mean, which the definition decides, and that is why the definition is written first.

Four kinds of question

Questions differ in what they ask of the data, and so in the evidence they need:

Each kind rests on the one before it: a recommendation needs a prediction, and a prediction needs good descriptions. The owner’s question sounds descriptive, but her decision is prescriptive. The per-hour numbers are the evidence, and the decision also weighs what a second cook costs. Most day-to-day analyst work is descriptive and diagnostic, and so is this track. The statistics track goes further into uncertainty, and the machine-learning track into prediction.

Grain: what one row means

The grain of a table is what one of its rows stands for. The canteen’s till could give you three tables about the same Monday:

• Order items: one row for every item on an order. An order of a tea and two samosas is two rows, one with a quantity of 1 and one with a quantity of 2.
• Orders: one row per order, with its time and its total.
• Days: one row per day, like the table in the example.

Ask “how many orders on Monday?” and only the second table answers it by counting rows. Counted from the first, every order with two different items counts twice. So before you analyse a table, write its grain in one sentence and name its key, the columns that identify one row: the order number and the item for the first table, the order number for the second, the week and the day for the third. If two rows share a key, either the grain is not what you thought or the data holds duplicates, and checking that every key is unique is one of the first things to do with any new table.

Where each tool fits

This track uses four kinds of tool, and each has work it does best.

Spreadsheets such as Excel, Google Sheets and LibreOffice Calc show every cell and every formula, which makes them good for small tables, one-off questions and sharing with people who do not code. They get harder to trust as the work grows: a formula copied down thousands of rows can be wrong in one of them without anyone seeing it, and an Excel worksheet stops at 1,048,576 rows.

pandas is the Python DataFrame library you will meet most often: on PyPI it is downloaded about ten times as often as polars or DuckDB, and since the counts include automated installs, they compare use rather than count people. It reads files, cleans, groups, joins and reshapes, and it works in memory, so a table has to fit in your computer’s RAM with room left for the copies that some operations make.

polars is a DataFrame library whose core is written in Rust. You describe a calculation with expressions; polars can plan the whole query before running it, spread the work over every processor core and, with its streaming engine, work through data that does not fit in memory, one batch at a time.

DuckDB is a SQL database that runs inside your Python program, with no server to install or look after. It queries CSV, Parquet and JSON files and pandas or polars DataFrames directly, and when memory runs out it spills to disk, so it can process data larger than memory.

The track teaches pandas first, because most of the code and answers you will meet use it, and then polars and DuckDB.

Mistakes that change the answer

Most wrong answers in analysis come from a short list of mistakes, and every one of them can hide behind code that runs without an error:

• The wrong grain: counting order items as orders, or averaging daily averages as if every day had the same weight.
• Double counting after a join: attaching a table with several rows per key repeats the rows of the other table, and every sum over them grows.
• Silent type changes: numbers read as text, codes such as the ZIP code 02139 losing their leading zero, a missing value turning a column of whole numbers into decimals.
• Missing treated as zero, or zero as missing: an empty cell means “we don’t know”, not “none”.
• Dates and time zones: 03/04 is day 3 of month 4 in day-first countries and day 4 of month 3 in the United States, and a day that ends at midnight UTC is not the business’s day in India, which runs 5 hours 30 minutes ahead.

Exercise

def orders_per_day(days):
    """Orders per open day for weekdays and for the weekend.

    days is a list of dictionaries such as {"day": "Sat", "open_hours": 6, "orders": 210}.
    Returns {"weekday": ..., "weekend": ...}, each value rounded to one decimal place.
    """
    # Replace this line with your code.
    return {"weekday": 0.0, "weekend": 0.0}


def orders_per_open_hour(days):
    """Orders per open hour for weekdays and for the weekend, rounded to one decimal place."""
    # Replace this line with your code.
    return {"weekday": 0.0, "weekend": 0.0}

from busy import orders_per_day, orders_per_open_hour

WEEK = [
    {"day": "Mon", "open_hours": 12, "orders": 281},
    {"day": "Tue", "open_hours": 12, "orders": 266},
    {"day": "Wed", "open_hours": 0, "orders": 0},  # closed for a holiday
    {"day": "Thu", "open_hours": 12, "orders": 301},
    {"day": "Fri", "open_hours": 12, "orders": 322},
    {"day": "Sat", "open_hours": 6, "orders": 219},
    {"day": "Sun", "open_hours": 6, "orders": 190},
]

HALF_DAY = [
    {"day": "Mon", "open_hours": 12, "orders": 300},
    {"day": "Tue", "open_hours": 6, "orders": 150},  # opened for half a day
    {"day": "Sat", "open_hours": 6, "orders": 200},
    {"day": "Sun", "open_hours": 6, "orders": 220},
]


def test_orders_per_day():
    """averages the orders over the open days of each type"""
    assert orders_per_day(WEEK) == {"weekday": 292.5, "weekend": 204.5}


def test_orders_per_open_hour():
    """divides the orders by the hours the canteen was open"""
    assert orders_per_open_hour(WEEK) == {"weekday": 24.4, "weekend": 34.1}


def test_closed_days_are_left_out():
    """gives the same answers when a closed Saturday is added"""
    closed = WEEK + [{"day": "Sat", "open_hours": 0, "orders": 0}]
    assert orders_per_day(closed) == {"weekday": 292.5, "weekend": 204.5}
    assert orders_per_open_hour(closed) == {"weekday": 24.4, "weekend": 34.1}


def test_short_opening_hours():
    """counts a half day as one day but as only 6 open hours"""
    assert orders_per_day(HALF_DAY) == {"weekday": 225.0, "weekend": 210.0}
    assert orders_per_open_hour(HALF_DAY) == {"weekday": 25.0, "weekend": 35.0}

Check yourself

Interview questions

Fresher · What is the grain of a table, and why should you write it down before you analyse the table?

The grain is what one row of the table stands for: one order, one item on an order, one customer per day, one sensor reading per minute. Writing it down tells you which columns should identify a row (the key) and what a count of rows means. If a table holds one row per order item and you count rows to get orders, every order with two different items counts twice. The grain also tells you which tables join safely: joining daily totals to order items repeats each daily total on every item, and a sum taken afterwards is inflated. So state the grain in one sentence, name the key, and check that no two rows share the same key before you compute anything.

Key takeaways

• Data analysis answers a question well enough that someone can act on it, so find the decision first.
• The work is a loop: question, data, cleaning, exploration, answer and communication, then a sharper question.
• Write every metric as a definition: numerator, denominator, time window, filters and grain.
• Two reasonable definitions can point in opposite directions, as orders per day and orders per open hour did here; the decision chooses the definition.
• Tools change how you compute a number, not what it means: pandas, polars and DuckDB printed the same answer.