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.
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.
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.
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?
- A chart
- An idea turned into a rule a computer can follow
- A single number with no definition
- 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?
- The customers changed
- The file was cleaned again
- The ratings were rounded
- 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?
- Minutes must be from 5 to 240
- Minutes must be sensible
- Minutes should feel right
- 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?
- The rule is wrong
- The data is perfect in every way
- No visit broke that rule
- 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?
- It makes the file smaller
- It changes the dictionary
- It removes duplicates
- 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.
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.
