IntermediateLesson 11 of 306 min read

Designing Measures and a Data Dictionary

Turn an idea like "happy customers" into a precise rule, write a data dictionary, and let code check the data against it.

In this lesson you will

  • Turn an idea into a measure with four parts
  • See how the denominator changes a share
  • Write a short data dictionary
  • Check data against rules with code

About 26 minutes

Same words, different numbers

Omar and Leila argue about a simple sentence: “Most of our customers are happy.” Omar says yes. Leila says it feels lower. They are both telling the truth, because they have not agreed what happy means or what it is a share of. In the first course you learned to clean a table. At the intermediate level, the first skill is earlier than that: deciding exactly what you will measure and writing the decision down, so that two people looking at the same table get the same number.

From idea to measure

An idea such as “happy customers” cannot be calculated. A measure is an idea turned into a rule that a computer can follow. The rule needs four parts:

  • What is counted, for example a visit that has a rating of four or five.
  • What it is compared with, called the denominator: all visits, or only the visits that have a rating?
  • The unit: a count, a share in percent, minutes, or money in sample pounds.
  • The period: this week, this month, since the start of the file.

The program below uses one definition, “happy means a rating of four or five”, and applies it to the clean file. Watch what happens when only the denominator changes.

import pandas as pd
df = pd.read_csv("clean.csv")
rules = {
    "visit_id": lambda s: s.is_unique,
    "rating": lambda s: s.dropna().between(1, 5).all(),
    "minutes": lambda s: s.dropna().between(5, 240).all(),
    "cost": lambda s: (s >= 0).all(),
    "repair": lambda s: s.isin(["puncture", "brakes", "gears", "chain", "wheel"]).all(),
}
def failed(table):
    return [name for name, rule in rules.items() if not rule(table[name])]
print("real table, failed rules:", failed(df) or "none")
bad = df.copy()
bad.loc[0, "rating"] = 7
bad.loc[1, "cost"] = -5
bad.loc[2, "repair"] = "gear"
bad.loc[3, "visit_id"] = bad.loc[4, "visit_id"]
print("damaged copy, failed rules:", len(failed(bad)), "of", len(rules))
for name in failed(bad):
    print("  caught:", name)
rated = df["rating"].dropna()
happy = (rated >= 4).sum()
print("happy = rating 4 or 5")
print("share of rated visits:", round(happy / len(rated) * 100, 1), "percent")
print("share of all visits:", round(happy / len(df) * 100, 1), "percent")
print("mean minutes:", round(df["minutes"].mean(), 1))
print("median minutes:", df["minutes"].median())
real table, failed rules: none
damaged copy, failed rules: 4 of 5
  caught: visit_id
  caught: rating
  caught: cost
  caught: repair
happy = rating 4 or 5
share of rated visits: 72.7 percent
share of all visits: 63.2 percent
mean minutes: 31.0
median minutes: 32.0

Among visits that have a rating, 72.7 percent are happy. Among all visits, 63.2 percent are happy. Nothing about the customers changed. The gap appears because some visits have no rating at all, and the second version silently counts every blank as not happy. Neither number is wrong. They answer different questions: “Of the people who told us, how many were happy?” and “Of everyone, how many are known to be happy?”. A good report states which one it uses. The same goes for the average: the mean time is 31.0 minutes and the median is 32.0, and the lesson on summaries showed why a report should say which of the two it means.

A data dictionary

A data dictionary is a short document that says what every column means. It sounds dull, and it saves more arguments than any other file in an analysis. A few lines are enough for each column.

Column Meaning and rule
visit_id One per visit, never repeated
minutes Time at the bench, from 5 to 240
cost Sample money, never below zero
rating Whole number 1 to 5, blank if not asked
repair One of five agreed repair names

Notice that each rule can be checked by a computer. “Time at the bench, from 5 to 240” is a rule. “A sensible number of minutes” is only a hope. Write rules the way the left-hand column of a test would: precise enough that it can say yes or no.

Let the rules check the data

The first lines of the program turn the dictionary into five small functions. On the real table, none of the rules fails. Then a damaged copy is made on purpose: a rating of 7, a negative cost, a repair spelled gear, and a repeated visit number. The rules caught 4 of 5 problems. The fifth rule, the range of minutes, found nothing because this copy had no bad minutes. That is exactly how a test should behave: quiet on good data, loud on bad.

Run checks like these every time new data arrives. A new month of visits might include a rating typed as 55, or a repair named in a new way. Without checks, the mistake flows silently into your totals. With checks, it stops at the door.

Good measures and risky measures

  • Close to the question. A rating is only a stand-in for satisfaction. A customer who is happy but never fills in the card is invisible.
  • Hard to fake. If volunteers were praised for high ratings, they might ask only the happy customers. Measures that become targets tend to bend.
  • Stable over time. If you change the definition halfway through the year, a jump in the line may be your rule and not the workshop.
  • Simple to explain. If it takes a paragraph to explain, the reader will not trust it.
Common mistake: a share with no base

Quoting a percentage without its denominator. “70 percent” means little until you say “of rated visits” or “of all visits”. Always show the count beside the share.

Teaching data

The visits and money are made up for teaching. The limits in the dictionary, such as 5 to 240 minutes, are examples chosen for this made-up workshop. Your own limits should come from the real work.

Pick one number you report at work or at home, such as a monthly average. Write its four parts: what is counted, the denominator, the unit and the period.

Practice for this lesson

Check what you learned

Five quick questions. Pick an answer and you will see straight away whether it is right and why.

What is a measure, as used here?

  1. A chart
  2. An idea turned into a rule a computer can follow
  3. A single number with no definition
  4. A column name

A measure needs a clear rule, such as a rating of four or five.

Why did the happy share change from one number to the other?

  1. The customers changed
  2. The file was cleaned again
  3. The ratings were rounded
  4. The denominator changed, from rated visits to all visits

Blank ratings were counted as not happy in the second version.

Which rule could a computer check?

  1. Minutes must be from 5 to 240
  2. Minutes must be sensible
  3. Minutes should feel right
  4. Minutes should look tidy

Only the first gives a clear yes or no.

What does it mean if a rule stays quiet on the real data?

  1. The rule is wrong
  2. The data is perfect in every way
  3. No visit broke that rule
  4. The file is empty

A quiet check only says that rule held; it says nothing about other problems.

Why run the rules every time new data arrives?

  1. It makes the file smaller
  2. It changes the dictionary
  3. It removes duplicates
  4. New data can contain mistakes the old data did not

Checks stop mistakes at the door.

Summary

  • A measure needs what is counted, a denominator, a unit and a period.
  • Changing the denominator changes the answer.
  • A data dictionary lists the meaning and rule for every column.
  • Run the rules every time new data arrives.

Next lesson

Lesson 12 combines two tables with joins and shows how a join can quietly change your totals.

Questions

Is a data dictionary only for big projects?

No. Even a five-column table benefits from a few lines per column. It takes minutes to write and stops arguments about what a column means.

Should I use the mean or the median?

Neither is always right. The median is steadier when a few values are extreme. Say which one you report, and consider showing both when they differ.

Can validation rules prove my data is correct?

No. They catch some kinds of mistake. A value can pass every rule and still be wrong, for example a plausible rating typed against the wrong customer.

Progress is saved in this browser.