# Lesson 2: Data Cleaning and Descriptive Analyses *Companion-podcast transcript • Sarah & Kiffer* *Office Hours episode to listen to after working through the lesson* --- **Sarah:** Welcome back to Office Hours. I'm Sarah. **Kiffer:** And I'm Kiffer. This episode goes with Lesson two, Data Cleaning and Descriptive Analyses. **Sarah:** It's meant for after you've worked through the lesson. We'll add some perspective on how these methods are used in practice and some critique of their limits, work through a few of the questions students tend to find thorny, and do some extra worked examples, including a couple that are harder than the ones on the lesson page. **Kiffer:** At a few points we'll ask you to work something out before we give the answer. When we do, you'll hear a few seconds of quiet. Pause the audio if you'd like more time. **Sarah:** Here's what we'll cover. Is every value outside the outlier fences a mistake? What does missing at random actually mean, and can your data tell you which mechanism you have? How quickly do small amounts of missing data add up when you drop incomplete rows? And what does a standardized difference in Table 1 tell you that a p-value can't? We'll finish with the mean and the median, because Kiffer and I see health care costs differently. **Kiffer:** Let's start with the first one. **Sarah:** Question one. If a value lands outside the fences, is it a mistake? I think a lot of students treat the interquartile range rule as an error detector. **Kiffer:** The rule finds values that are unusual for this particular sample. Whether a value is possible at all is a separate question, and the hard limits in the codebook answer it. A value beyond a hard limit is an error, and checking the source record can show that other values are wrong too. The fence on its own can't tell you either of those things. **Sarah:** Where does the one and a half come from? **Kiffer:** John Tukey used it in his 1977 book, Exploratory Data Analysis. He called the lines one and a half interquartile ranges beyond the quartiles the inner fences, and the lines three interquartile ranges beyond them the outer fences. It's a convention. If the data come from a perfect bell curve, about seven values in every thousand still land outside the inner fences. So in a sample of eight hundred, you'd expect five or six flagged values even when every value is correct. **Sarah:** And in a bigger sample, more flags. **Kiffer:** Right. The rule makes no allowance for sample size, so even with bell-shaped data, a registry of a million people would flag thousands of real values. Skewed data make it worse. **Sarah:** Here's one for everyone listening. In a made-up set of hospital stays, the first quartile is two days and the third quartile is six days. Where's the upper fence, and would a stay of twenty days be flagged? Take a few seconds. *(Pause)* **Kiffer:** The interquartile range is six minus two, which is four days. One and a half times four is six, and six plus six gives an upper fence of twelve days. So yes, a stay of twenty days is flagged. **Sarah:** And the lower fence is two minus six, which is minus four days. No stay can be shorter than that, so the rule can only ever flag long stays. **Kiffer:** And for length of stay, the long tail is real. Some patients with complications stay for weeks. If an analyst deleted every stay past twelve days, they'd remove the patients who use the most hospital care, which is often the group a health system most wants to understand. **Sarah:** So what's the right move with a skewed variable? **Kiffer:** Treat the flag as a reason to look. Check the value against the hard limit and, where possible, the source record, and look at the histogram. For a right-skewed variable, looking for outliers on the log scale, where the shape is closer to symmetric, gives fences that make more sense. And use the soft-limit habit from the codebook. You flag the value, check it, and keep it unless it's shown to be wrong. **Sarah:** Question two. What does missing at random actually mean? The name has always bothered me, because it sounds like it means the gaps are random. **Kiffer:** The name causes a lot of confusion. Missing at random means the chance that a value is missing can depend on things you observed. Once you allow for those observed things, it no longer depends on the missing value itself. The word random describes what's left over after you've accounted for the observed variables. **Sarah:** Let's test that with a made-up cohort study. Blood pressure is measured at the start and again two years later. Some people miss the second visit, and the team finds that people with high blood pressure at the start were more likely to miss it. Is the missing follow-up reading missing completely at random, missing at random, or missing not at random? Take a few seconds. *(Pause)* **Kiffer:** It can't be missing completely at random, because missingness is related to something we observed, the first reading. Whether it's missing at random depends on why people dropped out. If, among people with the same starting blood pressure, dropping out has nothing to do with their follow-up reading, it's missing at random. Then an imputation model that uses the starting reading can correct the bias. **Sarah:** And if people dropped out because their blood pressure climbed after the first visit? **Kiffer:** Then missingness depends on a value we never saw, even after allowing for the first reading, and that's missing not at random. So the label depends on why people dropped out, and also on which variables you measured. If the study had also recorded hospital admissions between visits, the missingness might come close to missing at random once those are taken into account. **Sarah:** So the mechanism is partly a property of your dataset. **Kiffer:** That's a useful way to put it. Collecting variables that predict who drops out is one of the few things that makes missing at random more believable. What no test can do is rule out missing not at random. Little's test can show that data probably aren't missing completely at random. It only looks at observed values, though, so it can't detect missingness that depends on the values you don't have. **Sarah:** Income is the standard example in the lesson. Are there real cases where that worry shaped how data were collected or reported? **Kiffer:** Canada has two, one about whole questionnaires and one about income. In 2011, the long-form census questionnaire became the voluntary National Household Survey. Its unweighted response rate was 68.6 percent, compared with 93.5 percent for the mandatory long form in 2006. Statistics Canada didn't publish standard estimates for areas where the non-response rate reached fifty percent, because studies had linked non-response that high to large bias. The concern was that people who didn't respond might differ from those who did in ways the answers couldn't reveal. **Sarah:** And the second? **Kiffer:** In the 2016 Census, income data came entirely from administrative records, mainly tax and benefit records from the Canada Revenue Agency, so nobody had to answer income questions. Linked records have gaps of their own, though the change did remove the problem of people refusing the income question. Where it's possible, I think the best approach to missing data is a design that prevents it. **Sarah:** Question three is about how fast missing data add up, and I'd like to try this one myself. A made-up analysis of one thousand people uses five variables. They're missing two, three, four, five and six percent of their values, and the gaps fall on people independently of one another. How many people does a complete-case analysis keep? **Kiffer:** Let's give everyone a few seconds to try it first. *(Pause)* **Sarah:** Okay. I add up the percentages. Two plus three plus four plus five plus six is twenty percent. So we lose two hundred people and keep eight hundred. **Kiffer:** Try the same method on a bigger model. Twenty variables, each missing six percent. **Sarah:** Twenty times six is one hundred and twenty percent. That would mean losing more people than we have, so adding can't be right. **Kiffer:** That's a good check. So what's the right way to combine them? **Sarah:** A person is complete only if they have every variable. If the gaps are independent, the chance of having the first is 0.98, the second is 0.97, and so on, and I multiply those. 0.98 times 0.97 is about 0.951. Times 0.96 is about 0.913. Times 0.95 is about 0.867. Times 0.94 is about 0.815. So we keep about eight hundred and fifteen people and lose about eighteen and a half percent. **Kiffer:** That's right. Adding came close here because it only goes wrong for people with more than one gap, who get counted more than once. With twenty variables at six percent each, the share kept is 0.94 to the power of twenty, which is about 0.29. **Sarah:** So we'd lose about seventy-one percent of the sample, from six percent missing on each variable. **Kiffer:** Yes. And independence is only one possibility. In the five-variable example, adding gives the worst case, where nobody has more than one gap, and we'd lose twenty percent. If the gaps all fell on the same people, we'd lose only six percent. The gaps in the course file fall close to independently. After the range checks it has 118 missing values in eight columns, and they remove 113 people. Under independence you'd expect to keep about 689 rows, and the file keeps 687. **Sarah:** Now the harder part. Suppose the data in my example really are missing completely at random. Then the complete cases are a random subset, and the estimate stays unbiased. What does losing those people cost? **Kiffer:** It costs precision. The standard error of a mean is the standard deviation divided by the square root of the sample size. Going from one thousand people to eight hundred and fifteen multiplies the standard error by the square root of one thousand divided by eight hundred and fifteen. That's the square root of about 1.23, which is about 1.11. So every confidence interval is about eleven percent wider. **Sarah:** And in the twenty-variable model, keeping 290 people, it's the square root of one thousand divided by 290. That's the square root of about 3.45, which is about 1.86. The confidence intervals are nearly twice as wide. **Kiffer:** And that's the best case, when nothing is biased. If the people with gaps differ from the rest, the smaller sample can be biased as well. That's a large part of why, once more than about five percent of the rows would be lost, multiple imputation under a stated missing at random assumption, with a sensitivity analysis, is usually the safer choice. It keeps everyone's observed values in the analysis, and it removes the bias when the missing at random assumption holds. It's also why it's worth counting the rows each analysis actually uses before you look at any results. **Sarah:** Question four. What does a standardized difference in Table 1 tell you that a p-value can't? Here's a pair of made-up studies, each comparing the share of smokers in an exposed and an unexposed group. Study A has fifty people per group, and thirty percent of the exposed group smoke, against twenty percent of the unexposed group. Study B has five thousand per group, and the figures are twenty-two percent and twenty percent. Which study has the bigger imbalance, and which has the smaller p-value? Take a few seconds. *(Pause)* **Kiffer:** Study A has the bigger imbalance, ten percentage points against two. Study B has the smaller p-value. A standard test of two proportions gives about 0.25 for Study A and about 0.014 for Study B. **Sarah:** Let me work out the standardized differences. Using the formula for a proportion, for Study A, 0.3 times 0.7 is 0.21, and 0.2 times 0.8 is 0.16. Their average is 0.185, and the square root of that is about 0.43. The difference of 0.10 divided by 0.43 is about 0.23. **Kiffer:** For Study B, the same steps give a denominator of about 0.41, and 0.02 divided by 0.41 is about 0.05. That's under the usual 0.1 line for a negligible imbalance. So the study with the significant p-value has the trivial imbalance, and the study with the bigger imbalance has a p-value nowhere near significance. **Sarah:** So the p-value depends on the sample size as well as on the imbalance. **Kiffer:** Right. The p-value measures how surprising the difference would be if chance alone were at work, and that depends heavily on how many people you have. The standardized difference describes how big the imbalance is. For confounding, what matters is the size of the imbalance together with how strongly the variable affects the outcome. An imbalance in your sample distorts the comparison in your sample, whether it arose by chance or by some other route. That's why the STROBE guideline for observational studies says significance tests should be avoided in descriptive tables. **Sarah:** And for randomized trials, the CONSORT guideline says testing baseline differences is superfluous and can mislead, because when randomization works, any baseline difference is due to chance. I'd push on one thing, though. The 0.1 line is also a convention. And with fifty people per group, standardized differences bounce around a lot from sample to sample. **Kiffer:** That's fair. The cut-off is a rule of thumb, and small samples produce sizeable standardized differences by chance alone. I'd read the standardized difference as a description of the sample you have. Whether a variable needs adjusting for still comes from subject knowledge, including whether it's a confounder or a mediator, as with BMI and smoking in the course data. **Sarah:** Okay. Now the part where we disagree. The reporting rule is mean and standard deviation for symmetric variables, and median and interquartile range for skewed ones. I think that rule is right, and I'd apply it to costs too. **Kiffer:** Costs are where I'd make an exception. Here's a real example. A 2016 study in the Canadian Medical Association Journal looked at publicly funded health care costs for about fifteen million Ontario residents from 2009 to 2011. The median annual cost per person was 333 dollars. The costliest one percent of residents, with costs of about forty-five thousand dollars a year or more, accounted for a third of the costs the study could assign to individuals, and the top five percent accounted for sixty-five percent. **Sarah:** So the median person costs the system very little. The study assigned more than thirty billion dollars a year in costs to individuals. Even spread over all fifteen million people in the study, that's a mean of more than two thousand dollars, at least six times the median. **Kiffer:** Right. For a health ministry planning a budget, I'd say the median tells you very little. The total is the mean times the number of people, so the mean is the number the ministry needs. **Sarah:** Let's make that concrete with a made-up clinic. Nine patients cost two thousand dollars each, and one costs eighty thousand. The median is two thousand dollars. The total is ninety-eight thousand, so the mean is ninety-eight thousand divided by ten, which is 9,800 dollars. **Kiffer:** Now budget for one thousand similar patients. The mean gives 9.8 million dollars, which is the expected total. The median gives two million, which is short by 7.8 million. There's also a trap from the transformations section. If you log the costs, average them and back-transform, you get the geometric mean, which here is about 2,900 dollars. That gives a budget of about 2.9 million, still far too low. **Sarah:** Has anyone made that argument formally? **Kiffer:** Simon Thompson and Julie Barber did, in the BMJ in 2000, writing about costs in pragmatic randomized trials. They concluded that, despite the usual skew, the arithmetic mean is the most informative measure, because the other measures say nothing about the cost of treating all patients, which is what health policy decisions need. **Sarah:** I agree that a budget needs the mean. My worry is everything else. In the made-up clinic, one patient accounts for most of the total. If that patient hadn't enrolled, the mean would be two thousand dollars. In a small study, the mean of a skewed variable can swing a long way depending on who happens to be in the sample. And most readers of a Table 1 want to know what's typical. A patient wants to know what care will likely cost someone like them, and the median answers that. **Kiffer:** That's the strongest point on your side, and I agree the mean is unstable in small samples. That's a reason to estimate it from a large sample and report a confidence interval with it. When the question is about totals, I'd still report the mean. **Sarah:** I'm still more attached to the median than Kiffer is, but we agree on the practical rule. Choose the summary by the question. For a skewed variable, report the median and interquartile range to describe a typical person. When the question is about totals, such as a budget or the burden on a health system, report the mean as well, with its uncertainty, and say which question each number answers. **Kiffer:** Let's pull it together with three things to take away. **Sarah:** First, a flag is a reason to look. The fences and z-scores find values that are unusual for the sample, the codebook's hard limits find values that are impossible, and in skewed data many flagged values are real. **Kiffer:** Second, the missing-data mechanism is a judgement about your variables and your setting. Missing at random depends on what you measured, and no test can rule out missing not at random. Small gaps also compound across variables, so count the rows each analysis actually keeps. **Sarah:** And third, match the summary to the question. In Table 1, the standardized difference describes the size of an imbalance. For a skewed variable, the median describes a typical person and the mean tells you about totals. **Kiffer:** If you'd like more practice, rework the complete-case problem from this episode with your own numbers. Pick eight or ten variables, give each a small percentage missing, multiply out the share of complete rows, and then work out how much wider the confidence intervals get. **Sarah:** Next time, it's Lesson three, Linear and Logistic Regression, where the cleaned and described data from this lesson go into their first models. **Kiffer:** Take care, everyone. **Sarah:** See you in Lesson three.