使用R语言检查员工调研数据的日期连续性问题

Hey there! Let's figure out how to check if your employees have filled out the survey on consecutive days using R. First, let's fix up the original data since the dates were being treated as numbers (oops!) instead of actual date values. Then we'll walk through two common scenarios depending on what you mean by "continuous filling":

Step 1: Fix the Original Data #

First, we need to convert the Created and Updated columns to proper date format. We'll also make sure empty strings are handled correctly:

# Correct date formatting and create the data frame
EmployeeID <- c(101,101,101,102,102,102,102,104,104)
Created <- as.Date(c("2020-06-19","2020-06-20","2020-06-21","2020-06-24","2020-06-25","2020-06-28","2020-06-28","2020-06-23","2020-06-24"))
Updated <- as.Date(c("2020-06-19","2020-06-20","2020-06-21","2020-06-24","2020-06-25","2020-06-28","2020-06-28","2020-06-23","2020-06-24"))
happy <- c("True", "false", "false"," ", "false", "True","false", "True", "false")
active <- c("false", "false", " "," ", "false", "True"," ", "false", "false")
sad <- c("True", "false", "false"," ", "false", "True","false", "True", "false")
energitic <- c("True", "false", "false"," ", "false", "True","false", "True", "false")
df <- data.frame(EmployeeID, Created, Updated, happy, active, sad, energitic, stringsAsFactors = FALSE)

Scenario 1: Check if Recorded Dates Are Consecutive #

This checks if the days the employee submitted a survey (even if some fields are empty) are back-to-back. We'll use dplyr for easy grouping and calculations:

library(dplyr)

# Check consecutive survey submission dates
consecutive_dates <- df %>%
  group_by(EmployeeID) %>%
  # Remove duplicate entries for the same day
  distinct(Created, .keep_all = TRUE) %>%
  # Sort dates in order
  arrange(Created) %>%
  # Calculate the number of days between consecutive entries
  mutate(date_gap = as.integer(Created - lag(Created))) %>%
  # Determine if all gaps are exactly 1 day (ignoring the first NA value)
  summarise(
    has_consecutive_entries = all(date_gap[-1] == 1, na.rm = TRUE),
    date_range = paste(min(Created), max(Created), sep = " to "),
    total_submission_days = n()
  )

# View the results
print(consecutive_dates)

Output Explanation: #

• Employee 101: Submitted on 2020-06-19, 2020-06-20, 2020-06-21 → gaps are 1 day each, so has_consecutive_entries is TRUE
• Employee 102: Submitted on 2020-06-24, 2020-06-25, 2020-06-28 → there's a 3-day gap between 25th and 28th, so FALSE
• Employee 104: Submitted on 2020-06-23, 2020-06-24 → 1-day gap, so TRUE

Scenario 2: Check if Valid Entries Are Consecutive #

If you consider an entry "valid" only when at least one survey field (happy/active/sad/energitic) is not empty, we first filter out invalid entries, then check consecutive days:

# Filter out entries where all survey fields are empty
valid_entries <- df %>%
  rowwise() %>%
  mutate(
    is_valid = any(c(happy, active, sad, energitic) != " ")
  ) %>%
  filter(is_valid) %>%
  ungroup()

# Check consecutive valid entry dates
consecutive_valid <- valid_entries %>%
  group_by(EmployeeID) %>%
  distinct(Created, .keep_all = TRUE) %>%
  arrange(Created) %>%
  mutate(date_gap = as.integer(Created - lag(Created))) %>%
  summarise(
    has_consecutive_valid_entries = all(date_gap[-1] == 1, na.rm = TRUE),
    valid_date_range = paste(min(Created), max(Created), sep = " to "),
    total_valid_days = n()
  )

# View the results
print(consecutive_valid)

Output Explanation: #

• Employee 102's 2020-06-24 entry has all empty fields, so it's filtered out. The remaining dates are 2020-06-25 and 2020-06-28 → still a gap, so FALSE
• Employees 101 and 104 still have consecutive valid entries → TRUE

Bonus: Identify Exact Gap Periods #

If you want to see exactly which days are missing for employees with non-consecutive entries:

# Find gaps in submission dates
gap_details <- df %>%
  group_by(EmployeeID) %>%
  distinct(Created) %>%
  arrange(Created) %>%
  mutate(
    next_submission = lead(Created),
    gap_length = as.integer(next_submission - Created) - 1
  ) %>%
  filter(gap_length > 0) %>%
  mutate(
    gap_period = paste(Created + 1, next_submission - 1, sep = " to ")
  ) %>%
  select(EmployeeID, gap_period, gap_length)

# View gap details
print(gap_details)

Output: #

# A tibble: 1 × 3
  EmployeeID gap_period       gap_length
       <dbl> <chr>                 <dbl>
1        102 2020-06-26 to 2020-06-27     2

This shows Employee 102 missed 2 days between 2020-06-25 and 2020-06-28.

内容的提问来源于stack exchange，提问作者priya