R语言循环匹配行列名并计算前三列平均值的实现需求

Got it, let's work through this problem together. First, let's align on the requirements: you have a dataset with id, startdate, and daily date columns spanning 2014-2017 (each column holds chemical concentrations for that date). For each row, you need to locate the column matching the row's startdate, then compute the average of the three columns immediately before that target date column.

Step 1: Simulate Example Data #

First, let's create a sample dataset that mirrors your structure—this will make it easier to test our code:

# Set seed for reproducible random values
set.seed(123)
sample_data <- data.frame(
  id = 1:5,
  startdate = c(20141119, 20141027, 20150105, 20140812, 20160320),
  `20141116` = rnorm(5),
  `20141117` = rnorm(5),
  `20141118` = rnorm(5),
  `20141119` = rnorm(5),
  `20141024` = rnorm(5),
  `20141025` = rnorm(5),
  `20141026` = rnorm(5),
  `20141027` = rnorm(5),
  `20150102` = rnorm(5),
  `20150103` = rnorm(5),
  `20150104` = rnorm(5),
  `20150105` = rnorm(5)
)

# Critical: Ensure date columns are sorted chronologically
# (You must do this for your actual data—otherwise "previous three columns" won't match dates!)
date_cols <- grep("^201[4-7]", colnames(sample_data), value = TRUE)
date_cols_sorted <- as.character(sort(as.numeric(date_cols)))
sample_data <- sample_data[, c("id", "startdate", date_cols_sorted)]

Step 2: Base R Solution with Loop #

If you prefer base R, here's a straightforward loop approach that walks through each row and computes the required average:

# Add a new column to store results
sample_data$prev_3_avg <- NA

# Loop through each row
for (i in 1:nrow(sample_data)) {
  # Convert startdate to character to match column names
  target_date <- as.character(sample_data$startdate[i])
  
  # Find the position of the target date column
  target_col_idx <- which(colnames(sample_data) == target_date)
  
  # Only calculate if target exists and has at least three preceding columns
  if (length(target_col_idx) == 1 && target_col_idx > 3) {
    # Get indices of the three columns before the target
    prev_cols_idx <- (target_col_idx - 3):(target_col_idx - 1)
    # Compute average for the current row
    sample_data$prev_3_avg[i] <- mean(sample_data[i, prev_cols_idx], na.rm = TRUE)
  } else {
    # Handle edge cases (missing target or too few prior columns)
    warning(paste("Row", i, ": Target date not found or insufficient preceding columns"))
  }
}

# View the results
head(sample_data[, c("id", "startdate", "prev_3_avg")])

Step 3: Tidyverse Solution (dplyr + purrr) #

If you use the tidyverse ecosystem, this can be done more concisely with row-wise operations:

library(dplyr)
library(purrr)

sample_data_tidy <- sample_data %>%
  rowwise() %>%
  mutate(
    target_date = as.character(startdate),
    target_col_idx = which(colnames(.) == target_date),
    prev_3_avg = if_else(
      length(target_col_idx) == 1 && target_col_idx > 3,
      mean(c_across((target_col_idx - 3):(target_col_idx - 1)), na.rm = TRUE),
      NA_real_
    )
  ) %>%
  ungroup() %>%
  select(-target_date, -target_col_idx)

# View results
head(sample_data_tidy[, c("id", "startdate", "prev_3_avg")])

Key Notes #

• Chronological Sorting: Your date columns must be sorted in order of the dates they represent. If they're out of order, the "previous three columns" won't correspond to the three days before the start date—don't skip the sorting step!
• Error Handling: Both solutions include checks for missing target dates or cases where the target column is among the first three columns (so there aren't three preceding columns). Adjust the warning/NA behavior to fit your needs.
• NA Values: The na.rm = TRUE argument ensures missing concentration values don't break the average calculation. Remove it if you want NA to propagate when any of the three columns has an NA.

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