2  Who is that one row? — Units of observation and coding

The first step in descriptive statistics is not computing means or proportions. It is checking “what does one row of this data represent?” and “what does each column’s value mean?”

2.1 Opening the Framingham data

There are 11627 rows.

NoteMake a prediction

If you compute “the proportion of people who died during follow-up” from this data, what percentage would it be? Is the following code correct?

3527 deaths (30.3%), mean age 54.8 years … all of these are wrong.

2.2 One row is not necessarily one person

Let us count the number of IDs.

There are 4434 participants. Each person had up to 3 examinations, and the data had one row per examination stacked vertically (so-called long format). Let us extract the rows for one person.

  • AGE and SYSBP change from examination to examination (values at that time point)
  • DEATH and TIMEDTH are the same in all rows (whether that person died during the 24-year follow-up)

So if you sum DEATH over all rows, you count the same person’s death twice or three times. Moreover, people who attended all 3 examinations are counted more often.

2.2.1 Align the unit of analysis

If you are describing “the characteristics of the participants”, align the data to one row per person. Here we use the first examination (PERIOD == 1, baseline).

There were 1550 deaths (35.0%), and the mean age at baseline was 49.9 years.

TipTypical cases where rows multiply or are duplicated
  • Multiple visits, check-ups, or tests for the same person (longitudinal data)
  • Rows of diagnoses and prescriptions in claims data (one visit becomes many rows)
  • A join with another table where the key turned out to be one-to-many
  • Double registration of data (hospital transfers, reassigned IDs)

Checking n_distinct(ID) == nrow(data) is worth doing first with any data.

2.3 Do not treat code values as numbers

Next, the meaning of the columns. Let us take the mean of sex.

SEX is a code: “1 = male, 2 = female”. A mean of 1.56 is meaningless. The same goes for the mean or median of education educ (categories 1–4). R will compute numerically whenever there are numbers in a column, so you need to look at the codebook (the table of variable definitions) and fix the types.

NoteFor 0/1 variables, the mean is fine

For a variable such as CURSMOKE (0/1), the mean is “the proportion of 1s”, so you can use it as is. mean(base$CURSMOKE) is the proportion of smokers. However, check in the codebook what 1 means (smoker? non-smoker?).

2.4 Check units and time scales

TIMEDTH is in days. The maximum of 8766 days = 24 years. If you analyse it thinking it is in years, you will get the units of rates and hazard ratios wrong.

2.5 Check for contradictions between variables

Cross-tabulating related variables lets you notice differences in definitions or data-entry errors.

All non-smokers have 0 cigarettes, so there is no contradiction. However, 32 of the smokers have a missing number of cigarettes (NA).

  • Diabetes “no” but glucose 126 or higher: 24 people
  • Diabetes “yes” but glucose 125 or lower: 47 people

This is not necessarily an error in the data. It is normal for the definition of diabetes (diagnostic criteria, whether under treatment, whether the blood sample was fasting) not to match a single glucose value. What matters is to check what the “diabetes” variable is based on, and to write that in the methods of your paper.

ImportantSummary of this chapter
  • First check what one row represents (a person? a visit? a prescription?) and duplicate IDs
  • In longitudinal data, align the unit to suit your purpose, e.g. one row per person (baseline), before describing
  • Make code values (sex, education) factors (categories). Do not take their mean
  • Check units (days/years, mg/dL/mmol/L) and contradictions between variables