Chapter 4 · 58 minutes

Last updated November 11, 2026

Working with Data: Structure, Measures, and Study Design

Opening scenario

A manager asks an analyst what sounds like a simple question: “How many patients did we serve last quarter?” Watch how one question turns into a sequence of decisions:

  • The question. Count the distinct patients the clinic cared for during the quarter.
  • The first action. She opens the appointment file and finds 36,000 rows.
  • The tempting answer. She could report 36,000 and move on.
  • The outcome. That number is wrong twice over. It counts visits, not patients, so a patient who came four times is counted four times. And the file covers two full years, not just last quarter, so it is not even scoped to the question.

The report she produces depends entirely on what a single row of her table represents. Get that wrong, and every count built on top of it is wrong too.

This chapter is about the shape of data: how tables are built, how they link to one another, and how to name what one row represents before you count anything. Once you can name what a row is, the chapter turns to the next decision, defining exactly what you are counting from it.

By the end of this chapter, you will be able to:

  • Read a table as rows of records and columns of variables.
  • Classify a variable as quantitative (discrete or continuous) or qualitative (nominal or ordinal), and explain why the type governs how it can be summarized.
  • Identify a primary key and a foreign key, check that they hold, and explain what joining two tables on them produces.
  • Name the unit of analysis of a table and explain why it decides what you can count.
  • Use a PivotTable to regroup a table to a coarser unit of analysis.
  • Define a measure by its numerator, denominator, inclusion rules, and time window, and write it as an operational definition.
  • Assess whether a measure actually answers the question it is meant to answer.
  • Tell an observational study apart from an experiment, and say what each can and cannot show.
  • Describe a target population, a sampling frame, and a sample, and name the biases in the gaps between them.
  • Match a sampling method (simple random, stratified, cluster, or systematic) to a situation.
  • Explain how a control group and randomization let an experiment support a causal claim.

Key concepts

Chapter 2 introduced the idea that dimension tables describe actors and fact tables record events. This chapter looks inside the tables themselves. Nearly all introductory analysis runs on rectangular data, and reading that rectangle well is a core analytic skill worth developing deliberately. The chapter has three connected parts. The first reads the structure of a table and names what one row represents. The second defines the measure you compute from it. The third steps back to ask where the data came from: whether it was observed or produced by an experiment, who was sampled, and which biases crept in along the way. The first two parts answer different questions about a file you already have: its structure shows which kinds of record you are able to count, such as patients, visits, or claims, while a measure’s definition states which of those records to count and under what rules. The third part reminds you that even a correct count from a flawed sample can mislead. Get any of the three wrong, and the number means something other than you think.

Rows are records, columns are variables

A table is a rectangle of data, built from two elements:

  • Each row is one record, also called an observation: a single entity the table describes.
  • Each column is a variable: one attribute measured for every record.

In datasets/DS1_patient_dim.csv, every row is a patient, and every column, such as birth_date or payer_primary_id, records one fact about that patient. This structure is called a data matrix, and it is the default shape for analysis because new records add as rows and new attributes add as columns without disturbing anything else. When you are handed a spreadsheet, the first step is to read it this way: what is one row, and what does each column measure?

Figure 1 labels the parts of a table on a few appointment rows, from a single cell up to the whole column and row.

Figure 1: How to read a data table: each row is one observation, each column is one variable, and a cell is the single value where a row and column meet. The unit of analysis, one appointment, names what each row represents. Source: concept schematic.

A table reads cleanly only when it is tidy: each row is one observation at a single grain, and each column is one variable (Wickham, 2014; Broman & Woo, 2018). Figure 2 contrasts a messy table, which crams several appointments and a patient-level summary into one row, with the tidy version that keeps one appointment per row. Chapter 8 returns to reshaping messy files; for now, notice that a tidy shape is what makes the filtering, grouping, and joining in this chapter reliable.

Figure 2: From a messy table to a tidy one. The messy table mixes grain and repeats appointment columns across a single row; the tidy table holds one appointment per row and one variable per column. Source: concept schematic.

Classifying variables by type

Naming what a column measures is only the first half of reading it. The second half is knowing what kind of value the column holds, because the type decides what you are allowed to do with it. Two columns can both contain numbers and yet call for entirely different handling: averaging a patient’s length of stay produces a meaningful average, but averaging a patient identifier produces a number that points at no real patient, because the identifier only looks like a quantity. Deciding which type a variable belongs to is the first thing introductory statistics texts such as OpenIntro settle before any analysis begins (Diez et al., 2019).

Every variable is one of two broad kinds, and each kind splits once more.

A quantitative variable, also called numeric, holds a quantity you can do arithmetic on, a value where adding and averaging make sense. It comes in two forms:

  • Discrete: a count that takes separate, whole values. Examples: lead_days, the number of whole days between booking and the visit, or a patient’s number of prior no-shows.
  • Continuous: a value measured on a scale that can take any value in a range. Examples: copay_usd, a patient’s weight, or an emergency department stay in minutes, where a finer value is always possible between any two others.

A qualitative variable, also called categorical, holds a label that places a record in a group. Its distinct values are its levels; attendance_outcome has five levels, from Completed to Rescheduled. It also comes in two forms:

  • Nominal: categories with no inherent order. Examples: visit_type, a facility name, or a patient’s primary payer. One category is not higher or lower than another; they are different.
  • Ordinal: categories with a meaningful order but not necessarily equal gaps. Examples: a patient-satisfaction rating from 1 (very dissatisfied) to 5 (very satisfied), a triage acuity level, or a cancer stage from I to IV.

You can rank ordinal values, but you cannot assume the step from 1 to 2 is the same size as the step from 4 to 5, which is why averaging them can mislead.

Figure 3 gathers the whole taxonomy on one page, each branch ending in a real column from the appointment file.

Figure 3: The four variable types, each leaf tagged with a column from datasets/DS2_appointments_clean.csv. Quantitative variables are discrete or continuous; qualitative variables are nominal or ordinal. The columns at the bottom are not quantities, even though they are written as digits or codes: a 0/1 flag and an identifier both behave as categories. Source: concept schematic built from datasets/DS2_appointments_clean.csv.

Whether two variables move together, and whether one helps predict another, is a question that needs their types settled first; Chapters 10 and 12 take it up.

Before you summarize a column, name its type. Quantitative variables are discrete (counts) or continuous (measured on a scale); qualitative variables are nominal (unordered groups) or ordinal (ordered groups). The type decides which summary, chart, and model are valid, choices you will make in Chapters 9 through 13.

Watch for columns that look numeric but are not quantities:

  • A 0/1 flag like no_show_flag is a two-category label in disguise, not a count.
  • An identifier like patient_id, provider_id, or facility_id is a label, often a code such as RBP000001 or SP03, not a quantity; its average would point at no real record.

Neither is a fifth type of variable. Both are nominal variables presented as numbers, so they belong on the qualitative side of the tree even when their values happen to be digits. What you can legitimately do with a 0/1 flag is count how many equal 1, or average it to get a rate, a proportion, which is a genuine quantity even though the flag itself is a category. An identifier never earns that treatment: you can count distinct identifiers, but you can never average one. This is exactly why Chapter 3 filtered before averaging: cancelled and rescheduled appointments also carry no_show_flag = 0, so averaging the whole column gives 13.6 percent, every scheduled appointment in the denominator, not the 16.1 percent kept-or-missed rate the clinic actually wanted.

The unit of analysis decides what a row means

The unit of analysis, also called the grain, is the answer to “what does one row represent?” It is the single most important fact about a table, because it sets what you can count and how. A table at the appointment grain can count visits and no-shows. It cannot directly count patients, because one patient can own many rows. To count patients you must first change the grain, collapsing many appointment rows into one row per patient. The number changes when the grain changes, and that is not an error. That change is the expected result.

From data to a measure

Knowing what one row represents shows what you can count. It does not yet state what to count. That second decision is the measure, and it is where most analytic disagreements begin. Recall from Chapter 3 that you computed one no-show rate, 16.1 percent, and the text warned you to state the rule you used. This section is about why that warning matters and how to write the rule down.

A measure is a precise, repeatable definition of something you can count or calculate. A metric is the number that definition produces when you run it on data. The order matters: you define the measure first, then the data gives you the metric. When two analysts report different numbers from the same file, the cause is usually not a spreadsheet mistake. It is two different measures described by the same name.

Figure 6 follows one measure from the decision question to the reported number, with the written definition in the middle.

Figure 6: From question to reproducible metric: a decision question becomes a written measure, then an operational rule in terms of columns and values, then a calculation, then the reported metric. Define first, calculate second, and report the number with its definition. Source: concept schematic.

Many healthcare measures are expressed as rates or proportions, and a rate has four parts:

  • Numerator: what you are counting, the events of interest.
  • Denominator: the population those events are drawn from.
  • Inclusion and exclusion criteria: which records belong in the denominator at all.
  • Time window: the period the measure covers.

State all four and the measure is defined; leave one unstated and it is not.

A rate is not defined until you can state its numerator, its denominator, what it includes and excludes, and its time window. If any of the four is missing, you do not yet have a measure, only a name for one.

Figure 7 assembles the four parts into one no-show rate definition.

Figure 7: The four parts of a defined rate, assembled into a single no-show rate. Numerator (No Show), denominator (Completed plus No Show), inclusion and exclusion rules, and time window together make the measure reproducible. Source: concept schematic.

The denominator is where the unit of analysis returns. A no-show rate “per visit” and a no-show rate “per patient” are different measures with different denominators, because they count at different grains. Choosing the denominator is therefore not a formality. It decides what the rate is really measuring, which is why the structure and measure parts of this chapter are the same skill seen from two sides.

A definition also has to be written in the language of the data, not in English alone. An operational definition states the measure in terms of the actual columns and values in the file. “A no-show is a patient who did not attend” is an idea. “A no-show is a row where attendance_outcome equals No Show” is an operational definition: another analyst can run it and get the same result. The first cannot be run at all.

Conventions vary across the field: some quality programs, including HEDIS (the Healthcare Effectiveness Data and Information Set), reserve measure for the written specification and rate for the number it produces. The labels are less important than the practice behind them, which is to pin down the definition before you compute anything.

A measure can be perfectly defined and still fail to capture the intended construct. Construct validity asks whether the thing you can actually count stands in for the thing you care about (Cronbach & Meehl, 1955). The no-show rate is easy to count, but if the real concern is patients falling out of care, it misses everyone who never booked an appointment at all, because they are nowhere in the file. When what you count and what you care about drift apart, even a precisely calculated number can still mislead. Exercise 9 asks you to find a measure with exactly this flaw.

Figure 8 shows the gap: the no-show rate captures only patients who reached scheduling, while the broader concern includes people who never booked at all.

Figure 8: When a measure misses its construct. The no-show rate captures appointment behavior among patients who reached scheduling, but the broader idea of patients falling out of care also includes those who never booked, whom the file never sees. Source: concept schematic.

Never report a rate without its definition. “The no-show rate is 16 percent” invites the reader to assume a denominator, and they will assume the wrong one. Report it as “16.1 percent of kept-or-missed appointments over the two years in the file,” so the number carries its own rule.

Measures also differ in what part of care they capture. A durable framework sorts them into three kinds (Donabedian, 1988): structure measures describe what a system has, such as staff and equipment; process measures describe what care is delivered, such as the share of eligible patients given an A1c test; and outcome measures describe what happens to health, such as the share who reach A1c control, which is an intermediate outcome, a lab value on the way to the complications the care is meant to prevent. Figure 9 lines up the three for diabetes care. Process measures are often the most actionable, because a team controls the care it delivers more directly than the outcomes that follow from it. The two can diverge: a clinic can test nearly everyone, a strong process measure, and still see poor control, a weak outcome, which is exactly why naming where a measure sits in this framework tells you what it does and does not reveal.

Figure 9: Structure, process, and outcome measures for diabetes care. Structure is what the organization has, process is the care delivered, and outcome is what happens to patient health; A1c control is an intermediate outcome, and strong performance at one stage supports but does not guarantee the next. Source: concept schematic, after Donabedian (1988).

Where the data came from: observational studies and experiments

Reading a table and defining a measure both assume the data already exists. How it was collected shapes what it can prove, so an analyst has to know the study behind the file. A useful first distinction among primary studies is observational versus experimental:

  • An observational study records what happens without intervening. Claims, scheduling, and registry data are all observational: no one assigned patients to be sick or to miss appointments; the system logged events. Observational data can reveal that two things move together, but it cannot by itself establish that one caused the other, because the groups being compared usually differ in other ways.
  • An experiment deliberately assigns a treatment to measure its effect. A clinic that randomly sends reminders to half of its patients and withholds them from the other half is running an experiment. Because random assignment makes the two groups alike on average beforehand, a difference afterward can be attributed to the reminder rather than to who ended up in which group, provided randomization was preserved, outcomes were measured consistently, and differential attrition or interference did not compromise the comparison.

Figure 10 sets the two side by side: in an observational study the exposure occurs naturally, while an experiment assigns it at random.

Figure 10: Observational study versus randomized experiment. When exposure occurs naturally the data can show association but confounding may remain; when a treatment is randomly assigned, a later difference more plausibly reflects the treatment. Source: concept schematic.

The distinction matters because most healthcare data is observational, and observational data invites a tempting mistake. When two groups differ on an outcome, a third factor often explains both. A confounder is a variable related to both the exposure and the outcome, and not on the causal pathway between them; it need not be hidden. Sicker patients, for instance, both stay longer and cost more, so a raw link between length of stay and cost is partly the work of severity. Chapter 13 returns to confounders when it holds factors constant in a model.

Figure 11 draws that example: illness severity feeds both length of stay and cost, so part of their observed link is really severity at work.

Figure 11: How a confounder distorts an association. Illness severity raises both length of stay and cost, so the observed link between them partly reflects severity rather than a direct effect of one on the other. Source: concept schematic.

Observational and experimental are the two broad families, but the specific studies you will meet, including the ones in datasets/DS8_studies_master.csv, fall into a few recognizable designs within and around them:

  • A cohort study follows a defined group over time, either prospectively or by reconstructing history from existing records, to see who develops an outcome; most registries work this way. It is observational.
  • A cross-sectional study takes a snapshot of a population at one point in time, quick to run but unable to show change.
  • A quasi-experiment studies an intervention without full randomization, such as comparing a clinic before and after a new reminder system; it sits between the observational and experimental families.
  • A quality-improvement (QI) project tests a small operational change on the ground, often in rapid cycles.
  • A systematic review does not enroll new participants; it systematically synthesizes evidence from existing studies. A meta-analysis goes one step further and pools their results statistically.
  • A public-health report summarizes surveillance or population data to describe a trend rather than test a hypothesis.

These sort into three jobs: a primary study collects or analyzes primary observations (cohort, cross-sectional, trial, QI), an evidence synthesis combines existing studies (systematic review, meta-analysis), and a report describes population data. Knowing which of the three you are reading shows how much weight its conclusion can bear.

datasets/DS8_studies_master.csv holds 24 studies spanning these designs. Figure 12 counts how often each appears. A trial can support stronger causal claims but is expensive and slow; a cross-sectional snapshot is quick but cannot show change over time. Matching the approach to the question, and recording its limits, is part of the analyst’s job.

Figure 12: The designs and evidence types across the 24 entries in the teaching evidence base. Each is a different plan for gathering evidence, with different strengths. Source: datasets/DS8_studies_master.csv.

Open datasets/DS8_studies_master.csv and pick any three rows. First sort each into one of three buckets by its study_design: a primary study (trial, cohort, cross-sectional, or QI project), an evidence synthesis (review), or a public-health report. For the primary studies, add one more label: observational or experimental. Then look at evidence_strength_tier and notice that it does not track design alone. A trial can be rated Low and a cohort Moderate, because the tier also reflects how well a study was carried out, not just its design, so a strong cohort can outrank a weak trial.

Populations, samples, and the gaps that bias them

You rarely have data on everyone. A study observes a sample, which is intended to stand in for the whole. Three linked ideas describe that intention, and the gaps between them are where bias enters:

  • The target population is everyone the question is about, for example all adults attributed to our primary care practices.
  • The sampling frame is the list you can actually reach, such as the appointment roster in the scheduling system.
  • The sample is the records you end up observing. It shrinks in two steps: first you select records from the frame, then only some of those selected records respond.

Figure 13 traces these stages. People in the population but not on the frame never appear in your data, no matter how carefully you collect; people on the frame who are selected but do not respond drop out too. Every step inward is a chance for the sample to diverge from the population. The frame is rarely a clean subset: it can also include people who are not in the target population, such as patients who have moved away or died. The mismatch between the population and the frame, in either direction, is called coverage error.

Figure 13: How representativeness is lost between the target population and the data you analyze. Coverage error separates the target population from the sampling frame, which can both miss eligible people and include ineligible ones; selection narrows the frame to the selected sample; nonresponse shrinks that to the respondent sample; and further exclusions leave the analytic sample used in the results. Source: schematic.

Two biases arise in those gaps:

  • Selection bias happens when the way units enter the sample is related to what you are measuring and is not corrected for, so the observed sample does not represent the population. Unequal selection chances alone are not bias: a deliberate oversample stays unbiased once you weight the groups back to their population shares.
  • Nonresponse bias happens when the people who do not answer differ from those who do, which quietly skews any survey (Groves et al., 2009).

Neither is rare; both are the common outcome of collecting whatever data is convenient. At the far extreme sits a single vivid story, a friend who recovered after a treatment. That is anecdotal evidence: not a biased sample but no sample at all.

Not every file is a survey, though. An administrative file like the appointment data is a frame with coverage gaps, since patients who never booked never appear, but because it records events the system logged rather than answers people chose to give, nonresponse bias does not apply to it.

Bias usually enters before analysis, through who got into the data and who did not. State your target population, your frame, and your method so the gaps are visible instead of hidden.

Four ways to draw a sample

When you do sample, the method decides how accurately the sample represents the population. Four methods cover most of what you will meet:

  • Simple random sample: every unit on the frame has an equal chance of selection, usually through a random number generator. It is the standard baseline against which other methods are compared.
  • Stratified sample: split the frame into subgroups, or strata, such as age bands or payers, then sample within each. Proportional allocation gives every stratum some records; when a small but important subgroup is too thin to analyze on its own, you oversample it deliberately, then weight the strata back to their population shares when you combine them.
  • Cluster sample: split the frame into natural groups, such as clinics or counties, randomly pick whole clusters, and include everyone in the chosen ones. It is cheaper for large, dispersed populations.
  • Systematic sample: order the frame and take every kth record from a random start. It is simple when you have a full list, but it can produce biased or inefficient results when a periodic pattern in the ordering aligns with the sampling interval k.

A large real sample often combines these in stages, a multistage sample: pick clusters first, then sample within each. The choice is substantive. A convenience sample of whoever walks by is cheap and almost always biased; a stratified random sample costs more and generalizes more reliably to the population.

Figure 14 shows the four methods, plus the multistage design that combines them.

Figure 14: Four ways to draw a sample, plus multistage sampling. Simple random gives every unit an equal chance; stratified samples within subgroups; cluster selects whole groups; systematic takes every kth unit; multistage combines them across levels. Source: concept schematic.

Experiments: comparing on purpose

An experiment earns its stronger causal claim through deliberate design. Four features do the work:

  • A control group gives you something to compare against. It need not receive nothing: usual care, a placebo, or an alternative treatment all work, as long as it differs from the treatment under study. Without a comparison group you cannot tell the treatment’s effect from everything else that changed.
  • Randomization, assigning each unit to treatment or control by chance, makes the groups alike on average on both measured and unmeasured characteristics, so a later difference more plausibly reflects the treatment than some pre-existing imbalance.
  • Replication, enough units in each group, keeps a chance difference from being mistaken for an effect.
  • Blocking, grouping similar units before randomizing within each block, reduces variation attributable to the blocking factor so the treatment effect is more readily distinguished.

A clinic testing text reminders would randomly assign patients to receive them or not, keep enough patients in each group, and compare no-show rates afterward. Blinding, when feasible, keeps expectations from influencing the result. The payoff is support for a causal claim that observational data cannot easily provide: a difference that random assignment lets you attribute to the treatment, as long as randomization is preserved, outcomes are measured consistently, and differential attrition or interference does not compromise the comparison. That said, a carefully designed observational or quasi-experimental study can also support a causal reading, but only under assumptions it must state and defend.

Figure 15 traces a reminder trial end to end, from eligible patients through optional blocking, random assignment, and the comparison of outcomes between the reminder and control groups.

Figure 15: The anatomy of a randomized experiment testing appointment reminders. Eligible patients are optionally blocked, randomly assigned to a reminder or control group, and their attendance compared; control, randomization, replication, and blinding are the design features that let the difference estimate the treatment effect. Source: concept schematic.

Walkthrough: keys, grain, and a defined rate

Set up the workbook. This walkthrough continues from Clinic_NoShow_Analysis.xlsx, the workbook you built in Chapter 3, which already holds the appointment data as a Table named Appointments. To check the keys you also need the patient dimension in the same workbook: on a new sheet, choose Data > From Text/CSV, import datasets/DS1_patient_dim.csv, and load it. Power Query names the loaded Table after the file, so rename it Patients on the Table Design tab, then save the workbook. (Opening the CSV on its own with File > Open would create a separate workbook, and a Table cannot be saved inside a CSV, so the cross-sheet formulas below would break.)

Work the checks below in order, then define the rate.

Step 1. Confirm the primary key is unique and complete. A primary key must name each row exactly once and never be blank. The Patients Table has 12,000 rows, so test both halves of that rule:

  • Unique: =ROWS(Patients[patient_id]) returns 12,000, and =COUNTA(UNIQUE(Patients[patient_id])) also returns 12,000, so no value repeats.
  • Complete: =COUNTBLANK(Patients[patient_id]) returns 0, so nothing is missing.

A key that is both unique and complete can be trusted. If either check failed, a join through the column would multiply or drop rows.

Step 2. Confirm referential integrity of the foreign key. In the Appointments Table, patient_id is a foreign key that should point at a real patient. Count the appointment keys that match no patient:

=SUM(--ISNA(XMATCH(Appointments[patient_id], Patients[patient_id])))

It returns 0, so every one of the 36,000 appointments resolves to a real patient and none is orphaned, and an appointment can be joined to its patient’s attributes without losing rows. Two notes. XMATCH is used here because a row-by-row COUNTIF across both columns would run hundreds of millions of comparisons and can stall an older machine. And the dimension holds 12,000 patients while only 10,512 appear in the appointment file, which is normal: a dimension can list members who had no events in the period.

Step 3. Change the grain to match the question. The appointment table has 36,000 rows at the appointment grain. Ask a coarser question and the row count drops. Count the distinct values of each key:

  • =COUNTA(UNIQUE(Appointments[patient_id])) returns 10,512 patients, far below the 36,000 visit rows.
  • The same pattern returns 110 providers and 14 facilities.

Figure 16 shows the four counts. These are four alternative groupings of the same records, not rungs of a single hierarchy: a patient does not nest inside a provider, nor a provider inside a facility. Each answers a different question, and reporting one grain when the question asks for another is a common source of error in a count. (A PivotTable can report the same distinct counts through its Distinct Count value option, but that option requires the Data Model and is unavailable in Excel for Mac, so the UNIQUE formula is the portable check.)

Figure 16: The same appointment records regrouped to four grains. At the appointment grain the file holds 36,000 rows; collapsed to one row per patient it holds 10,512, per provider 110, and per facility 14. These are alternative groupings of the same records, not levels of one hierarchy, and each answers a different question. Source: datasets/DS2_appointments_clean.csv.

Reading this figure is the habit the data-source map in Chapter 2 was pointing toward. Naming the grain first, then counting, keeps the analyst from answering a patient question with a visit number.

Step 4. Answer the manager’s question with stacked filters. Return to the opening question: “How many patients did we serve last quarter?” Answering it accurately means stacking three filters on top of the grain change:

  1. Keep only last quarter (2025 Q4).
  2. Keep only Completed visits, because “served” means a patient actually seen.
  3. Count the distinct patient_id values that remain.

One formula does all three:

=COUNTA(UNIQUE(FILTER(
    Appointments[patient_id],
    (Appointments[appointment_date]>=DATE(2025,10,1)) *
    (Appointments[appointment_date]<DATE(2026,1,1)) *
    (Appointments[attendance_outcome]="Completed")
)))

It returns 2,735 patients, not the 10,512 distinct patients in the whole file and not the 36,000 visit rows. The strict <DATE(2026,1,1) keeps every appointment on 31 December even if the date column also carries a time of day. Every filter you drop inflates the number: count all scheduled patients in the quarter rather than only those seen and you get 3,643; ignore the quarter altogether and you are back to 10,512.

Figure 17 stacks those four numbers, from the 36,000 appointment rows down to the 2,735 patients actually served last quarter.

Figure 17: Why 36,000 rows is not 36,000 patients served. Changing the grain to distinct patients gives 10,512; filtering to last quarter gives 3,643 scheduled; keeping only Completed visits gives 2,735 served. Each decision changes what is being counted. Source: datasets/DS2_appointments_clean.csv.

Optional: see the grain collapse in a PivotTable. You can reproduce the distinct grouping with a PivotTable, the tool Chapter 3 introduced:

  1. Select the Appointments Table and choose Insert > PivotTable.
  2. Drag facility_id to Rows, and drag appointment_id to Values, set to Count. Fourteen rows appear, one per facility, and their counts sum to 36,000, the visit total you began with.
  3. Swap facility_id for provider_id in Rows. The same file regroups into 110 provider rows that still sum to 36,000.

Each field placed in Rows creates a summary at a different grouping level; the source table stays at the appointment grain. This makes the point of Figure 16 concrete: the unit of analysis is a choice you make, and the PivotTable is where you make it.

Step 5. Define the loss rate on purpose. Now carry the same file into a defined measure. The raw no-show count, 4,913 rows where attendance_outcome is No Show, is not in dispute. What differs is which appointments you treat as losses and which denominator you measure them against, and three analysts could each defend a different choice:

  • Divide by every scheduled appointment (36,000), and the rate is 13.6 percent.
  • Divide by only the kept-or-missed visits, Completed plus No Show (30,571), and it is the 16.1 percent you computed in Chapter 3.
  • Count the 1,873 late cancellations as no-shows in both the numerator and the denominator, because a slot lost at the last minute cannot be refilled. The numerator becomes 4,913 plus 1,873, or 6,786, and the denominator 30,571 plus 1,873, or 32,444, so the rate rises to 20.9 percent.

In this file, a Late Cancel is cancelled within half an hour of the appointment (at most 24 minutes before) and an Early Cancel six or more hours ahead. Many organizations instead treat anything inside 24 hours as a late cancellation; under that rule, 345 Early Cancel rows would join definition C and the rate would rise from 20.9 to 21.7 percent, so the cutoff is itself a definition choice worth stating.

Figure 18 shows the three side by side.

Figure 18: Three definitions of appointment loss computed from the same file. These are not three versions of one no-show rate: each uses a different numerator and denominator, so the number moves because the definition moves, not because anyone made an arithmetic error. Source: datasets/DS2_appointments_clean.csv.

None of these is the single correct rate; each is valid for the question it answers. The all-appointments rate describes the whole schedule, the kept-or-missed rate describes attendance among appointments that were not cancelled or rescheduled, and the late-cancel rate describes slot waste. Each answers a slightly different question. What matters is that you choose the definition on purpose, write it down as an operational definition, and report it with the number. A rate reported without its definition is not a finding; it is an unsupported number.

Open datasets/DS2_appointments_clean.csv. Without computing exact values, answer: (1) What is the unit of analysis of this table? (2) Name one column that is a foreign key and the table it points to. (3) If you wanted “average appointments per patient,” which two of the numbers in Figure 16 would you use, and how? (4) Using the cancel_datetime and appointment_date columns, write an operational definition for a “same-day cancellation rate”: say which rows count as a same-day cancellation, name the denominator, state what you include and exclude, and give the time window. You need not compute it. The answer key is at the end of the chapter.

The star schema in Figure 5 reflects a design choice called normalization: each fact is stored once, and descriptive attributes are pushed out to dimension tables so that a patient’s payer or language lives in exactly one place. Normalization prevents update anomalies, the situation where the same fact, stored redundantly across many rows, is changed in some and missed in others. The cost is that answering a question often requires a join.

Denormalization makes the opposite trade. Copying a few dimension attributes directly into the fact table, as datasets/DS2_appointments_clean.csv does with patient_age and zip_area_id, removes the join and speeds up reporting, at the risk that the copy drifts from the source if the dimension later changes. Analysts move between the two on purpose: normalized tables for storage and integrity, a denormalized wide table for a specific analysis. Recognizing which one you are holding tells you whether a stored attribute can be trusted as current or is only a snapshot that may now be stale.

These two stories are illustrative composites, not reports of any specific organization. A reporting team published a “patients served” figure that was really a row count from a claims table. Because a single episode of care can generate many claim lines, the number was inflated several times over, and it climbed every time the billing system split a claim. Leadership set targets against it. When an analyst finally named the grain, one row per claim line rather than one row per patient, the real patient count was a fraction of the reported one. The measure had been counting paperwork rather than people.

A second team hit the mirror image of the same problem. A health system compared no-show rates across clinics and flagged one as a poor performer, until an analyst found that the flagged clinic counted late cancellations as no-shows while the others did not. Put on one definition, the gap nearly closed. The first story shows a mismatched grain inflating a count; the second shows an undefined measure producing an apparent difference. Structure and definition fail the same way: the number looks solid and means something other than you think.

Chapter practice: the facility directory. Every healthcare organization keeps a reference list of the sites it operates. Managers use that list to count locations, group them by type, and see how long each site has been open. In this practice you open the Riverbend network’s facility directory, clean up its appearance, add one calculated column, and build a summary of how many sites fall into each type, while reviewing the skills from this chapter. The data file is datasets/DS1_facility_dim.csv, and it holds one row for each of the 18 facilities in the network.

Skills. Open a CSV in Excel, save it as a workbook, format headers, write and copy a formula, sort, and build a simple PivotTable.

Open and save the file.

  1. Open Excel. On Windows, choose File → Open → Browse; on a Mac, choose File → Open. Go to the datasets folder, select DS1_facility_dim.csv, and click Open. The data fills columns A through L, with headers in row 1 and the 18 facilities in rows 2 through 19.
  2. Choose File → Save As. In the file-type list, choose Excel Workbook (the .xlsx format), name the file Facility_Directory, and click Save. Saving as a workbook lets you keep formatting and formulas that a plain CSV cannot store.
  3. Double-click the worksheet tab at the bottom of the screen, type Facilities, and press Enter to rename the sheet.

Set a base font.

  1. Click the Select All button, the small gray triangle in the upper-left corner where the row numbers and column letters meet. This highlights every cell.
  2. On the Home tab, set the font to Calibri and the font size to 12 points. Click any single cell to deselect.

Add a title banner.

  1. Click the row 1 heading number, then hold Shift and click the row 2 heading number so both rows are selected. On the Home tab, click the drop-down arrow on the Insert button and choose Insert Sheet Rows. Two blank rows appear at the top. The headers are now in row 3, and the facilities are in rows 4 through 21.
  2. Click cell A1, type Riverbend Facility Directory, and press Enter.
  3. Click cell A2, type Reference year for calculations: 2026, and press Enter.
  4. Select the range A1:M1 by dragging from cell A1 across to cell M1. On the Home tab, click Merge & Center so the title spans the full width of the table.
  5. With the merged title still selected, apply Bold, change the font size to 16, set the Fill Color to a dark blue, and set the Font Color to white.
  6. Select the range A2:M2, click Merge & Center, and apply Italic so the subtitle sits neatly under the title.

Add a calculated column. Column K holds opening_year. The file records only the opening year, not a month or day, so you can calculate the approximate years a site has been open by subtracting its opening year from the reference year 2026.

  1. Click cell M3, type years_in_operation, and press Enter.
  2. Click cell M4, type the formula =2026-K4, and press Enter. The result for Riverbend Medical Center should be 28.
  3. Click cell M4 once more to select it. Point at the fill handle, the small square at the lower-right corner of the cell. When the pointer changes to a thin black plus sign, hold the left mouse button and drag straight down to cell M21, then release. Excel copies the formula to every row, and each row subtracts its own opening year from 2026. The values range from 12 to 28.

Format the headers and the table.

  1. Select the header range A3:M3. Apply Bold, set the Fill Color to a light blue, and click Center so each heading is centered in its cell.
  2. Select the full table range A3:M21. A quick way to select a range that runs off-screen is to click the Name Box at the left end of the formula bar, type A3:M21, and press Enter. Then click the drop-down arrow next to the Borders button on the Home tab and choose All Borders.
  3. Select column B by clicking the column B letter. On the Home tab, click Wrap Text so the long facility names show on two lines instead of stretching the column. Then select columns A through M by dragging across the column letters, click Format, and choose AutoFit Column Width so every entry is readable. The table is wider than the screen, so use the horizontal scroll bar at the bottom to see columns to the right.
  4. Select the range H4:H21 (bed_count). Then hold Ctrl (on a Mac, hold Command) and also select M4:M21 (years_in_operation) so both ranges are highlighted at once. On the Home tab, click Comma Style, then click Decrease Decimal until no decimal places remain.
  5. Click cell A4. Choose View → Freeze Panes → Freeze Panes. Rows 1 through 3 now stay in place when you scroll down, so the title and headers remain visible.

Sort the facilities.

  1. Select the table range A3:M21. On the Data tab, click Sort. In the dialog box, make sure My data has headers is checked. Set Sort by to years_in_operation and set Order to Largest to Smallest, then click OK. The longest-running sites now appear at the top.

Summarize facilities by type.

  1. Select the table range A3:M21, using the Name Box again if that is easier. Choose Insert → PivotTable → From Table/Range. Confirm that the Table/Range box reads Facilities!$A$3:$M$21, choose New Worksheet, and click OK.
  2. In the PivotTable Fields pane on the right, find facility_type and drag it into the Rows area. If the pane is not showing, click once inside the PivotTable, then on the PivotTable Analyze tab click Field List.
  3. Find facility_id and drag it into the Values area. The value should read Count of facility_id. If it reads Sum instead, click its drop-down arrow, choose Value Field Settings, select Count, and click OK.
  4. Double-click the new worksheet’s tab, type Type Summary, and press Enter.
  5. Save the workbook (File → Save, or Ctrl+S on Windows and Command+S on a Mac), then submit Facility_Directory.xlsx, containing the Facilities and Type Summary worksheets, as directed by your instructor.

Check your work. Your years_in_operation column should contain values ranging from 12 to 28. Your PivotTable on the Type Summary sheet should show these counts: ED 1, Hospital 1, Imaging center 1, Lab center 1, Primary care clinic 6, Specialty clinic 6, Urgent care 2, and a grand total of 18.

  • A table is a data matrix: rows are records, columns are variables. Reading it starts with naming what one row is.
  • Every column has a type: quantitative (discrete or continuous) or qualitative (nominal or ordinal). The type, not the storage format, decides what you may do with it, so numeric-looking identifiers and flags are categories, not quantities.
  • A primary key uniquely identifies each row; a foreign key in another table points back to it.
  • Referential integrity means every foreign key value exists in the table it references. Confirm it before you join.
  • The unit of analysis, or grain, decides what you can count. Changing the grain changes the number, on purpose.
  • Answer the question at the right grain. Reporting a visit count as a patient count is a frequent error.
  • Knowing what a row is shows what you can count; the measure states what to count. Define the measure first, then let the data produce the metric.
  • A rate needs four stated parts: numerator, denominator, inclusion and exclusion rules, and time window. A well-defined measure can still miss the intended construct.
  • How the data was collected decides what it can prove. Randomized experiments generally give the strongest basis for a causal claim; well-designed observational and quasi-experimental studies can support one too, but only when their assumptions are explicit and defensible.
  • A sample stands in for a target population only as well as its frame and method allow. Selection and nonresponse bias enter in the gaps, so state your population, frame, and method.
  • Match the sampling method to the situation: simple random, stratified, cluster, or systematic, often combined in stages.
  1. For three datasets in datasets/, name the unit of analysis, one likely primary key, and one likely foreign key.
  2. Explain in two sentences why datasets/DS2_appointments_clean.csv cannot directly answer “how many patients did we serve” even though it contains patient_id.
  3. Using Figure 16, compute the average number of appointments per facility from the two relevant numbers, and state the unit of analysis of your answer.
  4. Suppose you join appointments to a patient dimension and the row count grows from 36,000 to 41,300. The dimension turns out to store one row per coverage period, so a patient who switched insurance appears several times and patient_id repeats. What is this kind of join fault called, and how would you confirm it with a formula?
  5. Give an example of a question that must be answered at the facility grain and one that must be answered at the appointment grain.
  6. For datasets/DS2_appointments_clean.csv, classify each column by type (quantitative discrete, quantitative continuous, qualitative nominal, or qualitative ordinal): lead_days, copay_usd, visit_type, attendance_outcome, no_show_flag, and patient_id. For any that only look numeric but are not quantities, say why.
  7. Rewrite each vague question as one you could compute from a teaching dataset: (a) “Is the ED too slow?” (b) “Are we losing money on denials?” (c) “Do patients like us?”
  8. Write the full operational definition of the no-show rate you would recommend to a clinic director, and give one sentence justifying your choice of denominator.
  9. Give an example of a measure that is easy to count but has weak construct validity for the question “are our diabetic patients well controlled?”
  10. Classify each as observational or experimental, and say what each can show: (a) a registry linking obesity to diabetes; (b) a trial that randomly assigns a reminder to half of patients; (c) a chart review of last year’s readmissions.
  11. For “do our diabetic patients get an annual eye exam,” name a target population, a sampling frame, and one bias that could enter in the gap between them.
  12. A health system wants to estimate patient satisfaction across five clinics on a limited budget. Recommend one of the four sampling methods and justify the choice in two sentences.
  13. A quality team draws a systematic sample from the clinic’s appointment list, which is sorted by appointment time, taking every 20th slot from a random start. Name the unit of analysis, give the bias risk specific to systematic sampling, and explain in two or three sentences how that risk could distort a result.

Try It. The unit of analysis is one scheduled appointment. patient_id is a foreign key pointing at datasets/DS1_patient_dim.csv (so are provider_id and facility_id, pointing at their dimensions). For average appointments per patient, divide the appointment count by the distinct-patient count from Figure 16, which is 36,000 divided by 10,512, or about 3.4 appointments per patient. The answer is at the patient grain. A reasonable same-day cancellation rate counts, in the numerator, any appointment whose cancel_datetime falls on its appointment_date (mostly Late Cancel rows, with a few Early Cancel and Rescheduled rows that also happened that day); sets the denominator to Completed plus No Show plus those same-day cancellations; excludes cancellations and reschedules made on earlier days; and states a reporting period, such as a single quarter you filter the two-year file down to. A clinic manager focused on slot use and a finance manager focused on lost revenue may reasonably choose different denominators.

Selected exercises.

Exercise 2. The file sits at the appointment grain, so one patient can own many rows; counting the 36,000 rows counts visits, not patients. Collapsing to one row per patient_id gives 10,512 distinct patients, and even that is not quite “patients served”: served means a patient you actually saw, that is, one with at least one Completed visit, and only 9,692 qualify; the rest appear solely through no-shows or cancellations. The file also spans two years, so a “last quarter” question means filtering to the quarter first. Each tightening of the definition changes the count.

Exercise 4. The join multiplied rows because patient_id is not unique in that dimension: a patient with several coverage-period rows matches each appointment more than once, a one-to-many join against what is called a slowly changing dimension. It is a join-key cardinality and grain problem, patient_id is not unique because the dimension contains multiple coverage-period records, not a referential-integrity one; an orphaned foreign key would instead drop rows or leave blank columns. Confirm it by comparing =ROWS(Patients[patient_id]) with =COUNTA(UNIQUE(Patients[patient_id])); if the distinct count is smaller, the key repeats. The safe fix depends on the question: for a current attribute, keep one current row per patient; for a historical one, join on patient_id together with the appointment date falling inside each coverage period’s effective dates. It is really a grain-and-time problem, not just duplicate removal.

Exercise 6. lead_days is quantitative discrete; copay_usd is quantitative continuous; visit_type and attendance_outcome are qualitative nominal. no_show_flag and patient_id only look numeric: no_show_flag is a two-level flag and patient_id is an identifier code. Never average the identifier. You may average the 0/1 flag, but only because that yields a proportion (a no-show rate), not because the flag is itself a quantity.

Exercise 8. A defensible definition is No Shows divided by Completed plus No Shows, over a stated reporting period, excluding Early Cancel, Late Cancel, and Rescheduled; the kept-or-missed denominator counts only the visits a patient was expected to keep, which is what “no-show” is meant to measure.

Exercise 10. (a) Observational; it can show that obesity and diabetes occur together, not by itself that one causes the other. (b) Experimental; because the reminder was randomly assigned, a later difference in no-shows can be attributed to it, provided the trial was large enough, follow-up was adequate, and attrition did not differ substantially between the groups. (c) Observational; a chart review records what already happened, so it can describe patterns but not establish causes.

Exercise 12. A stratified sample is a strong choice: treat each clinic as a stratum and sample within every clinic so all five sites appear. If one clinic is much smaller, oversample it deliberately so its estimate is precise enough to use. A simple random sample across the whole system could, by chance, underrepresent a small clinic.

Exercise 13. The unit of analysis is one appointment slot. The risk specific to systematic sampling is periodicity: if the ordering has a repeating pattern whose period lines up with the interval of 20, the sample keeps landing on the same kind of slot. Because the list is sorted by appointment time, every 20th slot might always fall near 8 a.m., over-sampling early-morning visits and distorting any estimate, such as average wait time, that varies across the day.

Broman, K. W., & Woo, K. H. (2018). Data organization in spreadsheets. The American Statistician, 72(1), 2-10. https://doi.org/10.1080/00031305.2017.1375989

Cronbach, L. J., & Meehl, P. E. (1955). Construct validity in psychological tests. Psychological Bulletin, 52(4), 281-302. https://doi.org/10.1037/h0040957

Diez, D. M., Çetinkaya-Rundel, M., & Barr, C. D. (2019). OpenIntro statistics (4th ed.). OpenIntro.

Donabedian, A. (1988). The quality of care: How can it be assessed? JAMA, 260(12), 1743-1748. https://doi.org/10.1001/jama.1988.03410120089033

Groves, R. M., Fowler, F. J., Couper, M. P., Lepkowski, J. M., Singer, E., & Tourangeau, R. (2009). Survey methodology (2nd ed.). Wiley.

Kimball, R., & Ross, M. (2013). The data warehouse toolkit (3rd ed.). Wiley.

Wickham, H. (2014). Tidy data. Journal of Statistical Software, 59(10), 1-23. https://doi.org/10.18637/jss.v059.i10

Table
A rectangle of data in which rows are records and columns are variables; also called a data matrix.
Variable
A column of a table, one attribute measured for every record.
Observation
One row of a table; a single unit the data describes.
Quantitative variable
A variable that holds a numeric quantity you can do arithmetic on; also called numeric. It is either discrete or continuous.
Qualitative variable
A variable that holds a label placing a record in a group; also called categorical. It is either nominal or ordinal.
Discrete variable
A quantitative variable that counts and takes separate, whole values, such as a number of visits.
Continuous variable
A quantitative variable measured on a scale that can take any value in a range, such as cost or minutes.
Nominal variable
A qualitative variable whose categories have no inherent order, such as visit type or payer.
Ordinal variable
A qualitative variable whose categories have a meaningful order but not necessarily equal gaps, such as a 1-to-5 satisfaction rating.
Levels
The distinct categories a qualitative variable can take.
Primary key
A column, or a combination of columns, whose value uniquely identifies each row of a table and is never missing.
Foreign key
A column in one table that refers to the primary key of another.
Join
Combining two tables by matching a foreign key to a primary key, producing a wider table whose rows carry columns from both.
Referential integrity
The property that every foreign key value exists in the table it points to.
Unit of analysis
What one row of a table represents; also called the grain. It decides what the table can count.
Tidy data
Data in which each row is one observation at a single grain and each column is one variable.
Measure
A precise, repeatable definition of something you can count or calculate.
Metric
The number a measure produces when it is run on data.
Rate
A measure formed by dividing a numerator by a denominator over a fixed time window.
Numerator
The events of interest being counted in a rate.
Denominator
The population the numerator events are drawn from.
Inclusion and exclusion criteria
The rules that decide which records belong in the denominator.
Operational definition
A measure written in terms of the actual columns and values in the data, so that anyone can reproduce it.
Construct validity
The degree to which what a measure counts actually stands in for the thing the question is about.
Observational study
A study that records what happens without intervening; it can show associations but cannot by itself establish causation.
Experiment
A study that deliberately assigns a treatment to measure its effect, which can support a causal claim.
Confounder
A variable related to both the exposure and the outcome and not on the causal pathway between them, which can explain an apparent link; it need not be hidden.
Quasi-experimental study
A study that compares a treatment and a comparison group without full randomization; it can support causal claims under stated, defensible assumptions.
Cohort study
An observational study that follows a defined group over time, prospectively or retrospectively, to see who develops an outcome.
Cross-sectional study
An observational study that measures a population at a single point in time.
Systematic review
A study that does not enroll new participants but systematically synthesizes evidence from existing studies; a meta-analysis pools their results statistically.
Target population
Everyone the question is about.
Sampling frame
The list of records you can actually reach and sample from.
Sample
The records you observe. It narrows in two steps: the selected sample is what you draw from the frame, and the respondent sample is the subset that actually provides data.
Selected sample
The records drawn from the sampling frame before any nonresponse.
Respondent sample
The subset of the selected sample that actually responds or yields data.
Analytic sample
The records finally used in an analysis, which may be smaller than the respondent sample after missing-data, eligibility, or analysis-specific exclusions.
Selection bias
Distortion that occurs when the way units enter the sample is related to what is being measured and is not corrected for, so the sample does not represent the population.
Nonresponse bias
Distortion that occurs when those who do not respond differ from those who do.
Coverage error
The mismatch between the target population and the sampling frame: eligible people the frame omits, and ineligible people it wrongly includes.
Anecdotal evidence
A single vivid case offered in place of a sample; it cannot support a general claim.
Simple random sample
A sample in which every unit on the frame has an equal chance of selection.
Stratified sample
A sample drawn separately within defined subgroups so each subgroup can be represented according to the allocation plan.
Cluster sample
A sample of whole natural groups, such as clinics, with everyone in the chosen groups included.
Systematic sample
A sample that takes every kth record from a random start in an ordered frame.
Multistage sample
A sample drawn in stages, such as selecting whole clusters first and then sampling within each.
Convenience sample
A sample of whoever is easiest to reach; cheap but prone to bias.
Control group
The units in an experiment that do not receive the treatment under study, instead getting usual care, a placebo, or an alternative, used as a comparison.
Randomization
Assigning units to treatment or control by chance so the groups are alike on average beforehand.
Replication
Including enough units in each group that a chance difference is unlikely to be mistaken for a real effect.
Blocking
Grouping similar units before randomizing within each block, to reduce variation attributable to the blocking factor.
Blinding
Keeping participants or assessors unaware of group assignment so expectations do not influence the result.
Study design
The overall plan for how evidence is collected, such as a trial, cohort, or cross-sectional study.

Frequently asked questions

What is the unit of analysis in a dataset?

The unit of analysis, also called the grain, is the answer to what one row represents, for example one appointment or one patient. It is the single most important fact about a table because it decides what you can count.

What are the four types of variables in statistics?

Quantitative variables hold quantities: discrete counts or continuous measurements. Qualitative variables hold labels: nominal categories with no order, or ordinal categories with a meaningful order. The type determines which summaries and charts are valid.

What is a primary key versus a foreign key?

A primary key is a column whose value is unique for every row and never missing, a stable name for that record. A foreign key in another table points back to a primary key elsewhere. Joins match rows wherever the keys agree.

What is the difference between an observational study and an experiment?

An observational study watches what happens without intervening, so it cannot prove cause. An experiment assigns a control group and randomizes, which lets it support a causal claim. Most healthcare data is observational.

Cite this chapter
@incollection{sher2026ch04,
  author = {Sher, Hoshiar J.},
  title = {Working with Data: Structure, Measures, and Study Design},
  booktitle = {Introduction to Healthcare Data Analytics Using Excel},
  year = {2026},
  doi = {10.5281/zenodo.23327956},
  url = {https://yar177.github.io/intro-to-HDA-site/ch04.html}
}