Introduction

A student activities database is likely going to appear simple; it has an ID for a student, an ID for a week, and how many exercises they did during that week. However, we cannot use that data to determine the level of activity from the students until we identify what type of data is included in our dataset and what kind of data we are excluding. A zero indicates some form of completion (either completing no exercise or completing the minimum number of exercises), a missing value indicates either incomplete data or unknown data, while a missing row represents a missing record altogether. If we assume that every one of these forms represents zero activity, then we may be able to infer things about the data based on conclusions that the data does not actually support.

This code-through introduces tidyr::complete() through a toy dataset of weekly practice exercises. In addition to making missing weeks visible, we will also provide summaries of what information exists for each student. This example was created solely for the purposes of this tutorial and, therefore, should not be used to represent actual students.


Content Overview

Specifically, we’ll explain and demonstrate how to:

  • Create a small student activity dataset.
  • Identify the difference between zero activity and missing information.
  • Add missing student-week combinations.
  • Label the different types of records.
  • Summarize activity alongside data completeness.


Why You Should Care

Imagine a school is using weekly exercise counts to find out which students may be in need of extra assistance. If there was no report on a specific student during a certain week, it would seem like they had completed zero exercises. However, the lack of a report for this student could indicate other problems, such as a report issue, a change of enrollment status, etc. The problem here is that we do not know why. By making the missing reports visible, we can at least look into the reasons for these missing reports before determining whether their absence represents their behavior.


Learning Objectives

Specifically, you’ll learn how to:

  1. Differentiate between recorded zeros, missing values, and missing rows.
  2. Use complete() to create expected student-week combinations.
  3. Preserve information about which records originally existed.
  4. Build a summary that reports both observed activity and missing information.



Finding Missing Weeks in Student Records

Here, we’ll use dplyr for data manipulation, tidyr for completing missing combinations, and pander for displaying tables. These packages are loaded in the setup chunk.


Further Exposition

Data Wrangling can be further expanded upon with the distinction between missing values for individual measurements (missing measurements) and missing values for individual records (missing observations). Missing values in an existing row, such as a missing exercise count, are explicit; absent rows, such as an absence of a specific student-week combination, represent implicit missing values. In this case, it was assumed that each of the three students would be represented by one record per week. Therefore, every row represents one student for one week. The variable exercises represents the number of practice exercises completed and logged in a fictional learning management system.

The function complete() generates missing combinations of the given variables. We will use it to reveal absent rows without assuming that the missing exercise counts are zero (tidyr documentation).

Create the Example Data

activity <- tibble(
  student_id = c(
    "S01", "S01", "S01",
    "S02", "S02", "S02", "S02",
    "S03", "S03"
  ),
  week = c(
    1, 2, 4,
    1, 2, 3, 4,
    1, 4
  ),
  exercises = c(
    4, 0, 3,
    2, NA, 1, 5,
    0, 2
  )
)

activity %>%
  pander()
student_id week exercises
S01 1 4
S01 2 0
S01 4 3
S02 1 2
S02 2 NA
S02 3 1
S02 4 5
S03 1 0
S03 4 2

tibble() creates the table, and each c() supplies the values for a column.

There are three situations worth noticing:

  • Recorded zero: S01 has a week 2 record reporting zero completed exercises.
  • Missing value: S02 has a week 2 record, but its exercise count is NA.
  • Missing row: S01 has no week 3 record at all.

A recorded zero describes the exercise count in the source. It does not establish that the student did no learning or participated in no other way.


Basic Example

A basic example shows how to make absent weeks visible. We expect three students multiplied by four weeks, or 12 student-week records. The original dataset contains only nine.

activity.basic <- activity %>%
  complete(student_id, week = 1:4)

activity.basic %>%
  pander()
student_id week exercises
S01 1 4
S01 2 0
S01 3 NA
S01 4 3
S02 1 2
S02 2 NA
S02 3 1
S02 4 5
S03 1 0
S03 2 NA
S03 3 NA
S03 4 2


student_id uses the student IDs present in the data, while week = 1:4 explicitly defines the expected weeks. The completed dataset includes three previously absent rows, which are S01 in week 3 and S03 in weeks 2 and 3. Their exercise counts remain NA.

complete() exposes gaps; it does not recover the missing measurements. However, S02’s original missing count now looks the same as the missing counts in newly created rows. We need to preserve that distinction.

Advanced Examples

Tracking Which Records Originally Existed

Before completing the data, we can add a marker to every original row.

activity.tagged <- activity %>%
  mutate(record_present = TRUE)

activity.complete <- activity.tagged %>%
  complete(student_id, week = 1:4)

activity.complete %>%
  pander()
student_id week exercises record_present
S01 1 4 TRUE
S01 2 0 TRUE
S01 3 NA NA
S01 4 3 TRUE
S02 1 2 TRUE
S02 2 NA TRUE
S02 3 1 TRUE
S02 4 5 TRUE
S03 1 0 TRUE
S03 2 NA NA
S03 3 NA NA
S03 4 2 TRUE


record_present = TRUE marks every row that existed in the original dataset. After using complete(), original records retain TRUE, while newly created records have NA in record_present. The marker needs to be added before complete(). Adding it afterward would mark newly created rows as original records too.

S02’s week 2 row has a missing exercise count but retains TRUE because the record existed. S01’s week 3 and S03’s weeks 2 and 3 rows have NA in both columns because the rows were added during complete().

Giving Each Record a Clear Status

We can label the situations so readers do not have to interpret combinations of NA and TRUE.

activity.complete <- activity.complete %>%
  mutate(
    record_status = case_when(
      is.na(record_present) ~ "No original record",
      is.na(exercises) ~ "Recorded value missing",
      exercises == 0 ~ "Recorded zero",
      TRUE ~ "Recorded activity"
    )
  )

activity.complete %>%
  select(student_id, week, exercises, record_status) %>%
  pander()
student_id week exercises record_status
S01 1 4 Recorded activity
S01 2 0 Recorded zero
S01 3 NA No original record
S01 4 3 Recorded activity
S02 1 2 Recorded activity
S02 2 NA Recorded value missing
S02 3 1 Recorded activity
S02 4 5 Recorded activity
S03 1 0 Recorded zero
S03 2 NA No original record
S03 3 NA No original record
S03 4 2 Recorded activity


case_when() evaluates each condition in the order they appear. The string of text immediately before the ~ is the test for that particular condition; the string immediately after ~ will be assigned if the condition is true (dplyr documentation).

When testing conditions, we have to evaluate them in the right order. First, we want to identify which rows were missing originally (as opposed to having been inserted); second, we want to find those with a present value where the count is missing; third, we want to identify those where the count equals zero; finally, everything else gets labeled as recorded activity. If we had tested for NA in exercises first, all new records would have been classified as measurement errors, and we would have lost the distinction we’ve worked so hard to create.

For this fictional dataset, exercise counts are nonnegative. In a real application, invalid values such as negative counts would need separate checks.

Summarizing Activity and Missing Information

A useful summary should show how much information supports the activity totals.

activity.summary <- activity.complete %>%
  group_by(student_id) %>%
  summarize(
    expected_weeks = n(),
    recorded_weeks = sum(!is.na(record_present)),
    missing_rows = sum(is.na(record_present)),
    missing_values = sum(
      !is.na(record_present) & is.na(exercises)
    ),
    zero_weeks = sum(exercises == 0, na.rm = TRUE),
    observed_exercises = sum(exercises, na.rm = TRUE),
    .groups = "drop"
  )

activity.summary %>%
  pander()
Table continues below
student_id expected_weeks recorded_weeks missing_rows missing_values
S01 4 3 1 0
S02 4 4 0 1
S03 4 2 2 0
zero_weeks observed_exercises
1 7
0 8
1 2



group_by(student_id) organizes the calculations by student. summarize() produces one row per student, with the requested measures. .groups = "drop" removes grouping from the result (dplyr documentation).

The calculations mean:

  • expected_weeks: Counts the four rows now present for each student.
  • recorded_weeks: Counts rows marked as originally present.
  • missing_rows: Counts records added during completion.
  • missing_values: Counts original records whose exercise count is missing.
  • zero_weeks: Counts known exercise values equal to zero.
  • observed_exercises: Adds the available exercise counts.

Interpreting the Results

There are seven observed exercises in S01; one has a recorded zero, and there is one absent weekly record. In S02, we observe eight exercises. We have all four weeks of data for this student; however, we do not have the fourth week’s observation. To say we have every row does not necessarily mean we have every measurement. For S03, we have two observed exercises, one with a recorded zero, and two absent weekly records. In general, our data on S03 will be less complete than our data for S01 and S02.

With the use of na.rm=TRUE, we can add up what we know. The use of na.rm=TRUE does NOT imply that we have established that all missing values are zero nor that those sums constitute complete four-week totals. Therefore, the new column is called observed_exercises. If all exercise counts were missing, sum(..., na.rm = TRUE) would return zero. This would indicate that we have no available counts; it wouldn’t confirm that the individual was inactive. Thus, we need to track missingness along with the total.


What This Approach Can and Cannot Tell Us

This workflow will identify those gaps that are outside of the defined expectation of one recorded activity by each student in each week. However, this process can’t determine the reason for a missing recorded activity (or measurement).

Therefore, prior to implementing this elsewhere, we would first want to confirm that all of the following were true:

  • All students were expected to participate throughout all of the weeks referenced within the data set.
  • For each student/week combination, there is only one record.
  • The counts for the exercises were consistently defined.
  • The list of students is comprehensive.

In addition, since student_id is treated as a character column and because the student IDs used to identify the students to whom each row corresponds are based on the same list of student IDs provided at the start of the workflow, if a student has no records associated with them in the activity dataset, then they will remain not associated until an expected student list is provided. Similarly, students who begin classes late should not receive automatic assignment of “missing” records for any weeks in which the student did not enroll. We should also not automatically assign zero values for unknown exercise counts. Doing so would make a claim regarding student activity that would not have been supported by their existing recorded activities.

The resulting summary lists both observed activity and what is missing from each student’s recorded activity. Thus providing an improved framework for further analysis and investigation before making claims regarding participation through exercise count usage.



Further Resources

Learn more about completing missing records, classifying observations, and summarizing data with the following:


  • tidyr: complete() explains how to create missing combinations, specify expected values, and control replacement values.

  • dplyr: case_when() explains how to create categories from multiple conditions. It is useful for extending the record-status labels.

  • dplyr: summarize() explains grouped summaries and how to produce one summary row per group.



Works Cited

This code through references and cites the following sources: