使用R语言通过条件语句填充矩阵，统计各30分钟时段员工在岗人数

Let's break this down into actionable, easy-to-follow steps. I'll use a practical example with simulated data so you can replicate it with your own records.

Step 1: Prepare Your Employee Time Data #

First, let's create a sample dataset that matches the structure of your table. We'll use POSIXct time values because they're straightforward to work with in R:

# Simulate employee arrival/departure records
employee_data <- data.frame(
  employee_id = paste0("Emp_", 1:5),
  arrival_time = as.POSIXct(c(
    "2024-05-20 08:15:00", 
    "2024-05-20 08:30:00", 
    "2024-05-20 09:00:00", 
    "2024-05-20 08:45:00", 
    "2024-05-20 09:15:00"
  )),
  departure_time = as.POSIXct(c(
    "2024-05-20 10:00:00", 
    "2024-05-20 09:30:00", 
    "2024-05-20 11:00:00", 
    "2024-05-20 09:15:00", 
    "2024-05-20 10:30:00"
  ))
)

# View the sample data
print(employee_data)

Step 2: Generate 30-Minute Intervals #

Next, we need to create all the 30-minute intervals that cover your full time range. We'll round to the nearest 30 minutes to ensure we capture every possible window:

# Calculate time bounds (round to nearest 30 mins)
min_time <- floor(min(employee_data$arrival_time) / 1800) * 1800
max_time <- ceiling(max(employee_data$departure_time) / 1800) * 1800

# Create sequence of 30-minute intervals (1800 seconds = 30 minutes)
intervals <- seq(from = min_time, to = max_time, by = 1800)

# Format intervals into a readable data frame
interval_df <- data.frame(
  interval_start = intervals[-length(intervals)],
  interval_end = intervals[-1],
  interval_label = paste(
    format(intervals[-length(intervals)], "%H:%M"), 
    "-", 
    format(intervals[-1], "%H:%M")
  )
)

# View the intervals
print(interval_df)

Step 3: Build and Populate the Presence Matrix #

We'll create a matrix where rows represent intervals and columns represent employees. We'll fill it with 1 if the employee was present during the interval, 0 otherwise. The key condition for presence is:

The employee's arrival time is before/equal to the interval end AND their departure time is after/equal to the interval start (this captures all overlap between the employee's shift and the interval).

Here's a clear loop-based approach (great for understanding the logic):

# Initialize empty matrix with labels
presence_matrix <- matrix(
  nrow = nrow(interval_df),
  ncol = nrow(employee_data),
  dimnames = list(interval_df$interval_label, employee_data$employee_id)
)

# Fill the matrix with presence values
for (i in 1:nrow(interval_df)) {
  current_start <- interval_df$interval_start[i]
  current_end <- interval_df$interval_end[i]
  
  for (j in 1:nrow(employee_data)) {
    emp_arrive <- employee_data$arrival_time[j]
    emp_leave <- employee_data$departure_time[j]
    
    # Check if employee is present in the interval
    presence_matrix[i, j] <- ifelse(
      emp_arrive <= current_end && emp_leave >= current_start,
      1,
      0
    )
  }
}

# View the populated matrix
print(presence_matrix)

Step 4: Calculate Concurrent Employee Counts #

Finally, sum each row of the matrix to get the number of employees present in each interval:

# Calculate concurrent counts per interval
concurrent_counts <- rowSums(presence_matrix)

# Add counts to the interval data frame for easy viewing
interval_df$concurrent_employees <- concurrent_counts

# Show the final results
print(interval_df[, c("interval_label", "concurrent_employees")])

Step 5: Faster Vectorized Alternative (For Large Datasets) #

If you have hundreds of employees or intervals, loops can be slow. Use this vectorized approach with outer() to speed things up:

# Vectorized presence check (no loops!)
presence_vectorized <- outer(
  1:nrow(interval_df),
  1:nrow(employee_data),
  FUN = function(i, j) {
    as.integer(
      employee_data$arrival_time[j] <= interval_df$interval_end[i] &&
      employee_data$departure_time[j] >= interval_df$interval_start[i]
    )
  }
)

# Set labels for clarity
dimnames(presence_vectorized) <- list(interval_df$interval_label, employee_data$employee_id)

# Calculate concurrent counts
concurrent_counts_vectorized <- rowSums(presence_vectorized)

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