R语言中基于DataFrame2的匹配条件按时间区间汇总DataFrame1对应ID的view与read数据

Hey there! Let's tackle this problem cleanly. You want to calculate the total view and read from df2 for each row in df1 (matching ID and where df2$date falls between df1$DateStart and df1$DateEnd), then add those totals to df1. Here are a few elegant solutions, plus a breakdown of why your original code returned 0.

1. Tidyverse (dplyr) Approach (Most Readable for Beginners) #

Using dplyr with rowwise() is straightforward and easy to follow for someone new to R:

library(dplyr)

# Add sum columns directly to df1
df1_final <- df1 %>%
  rowwise() %>%
  mutate(
    # Calculate total view for the ID and date range
    view = sum(df2$view[df2$ID == ID & df2$date >= DateStart & df2$date <= DateEnd], na.rm = TRUE),
    # Calculate total read for the ID and date range
    read = sum(df2$read[df2$ID == ID & df2$date >= DateStart & df2$date <= DateEnd], na.rm = TRUE)
  ) %>%
  ungroup()

# View the result
df1_final

This will give you exactly the output you're expecting:

ID DateStart   DateEnd Interaction view read
1 86041 2022-02-04 2022-02-07         122   92   50
2 87371 2022-02-04 2022-02-10          73   57   44
3 98765 2022-02-08 2022-02-11         105  104   88
4 90010 2022-02-08 2022-02-11          82   48   34

2. Fuzzy Join Approach (Better for Large Datasets) #

If you're working with bigger datasets, a fuzzy join is more efficient than row-wise processing. We'll use the fuzzyjoin package to match rows between df1 and df2 by ID and date range, then summarize:

library(dplyr)
library(fuzzyjoin)

# First summarize df2, then join back to df1
df1_final <- df1 %>%
  fuzzy_left_join(
    df2,
    by = c("ID" = "ID", "DateStart" = "date", "DateEnd" = "date"),
    match_fun = list(`==`, `<=`, `>=`)
  ) %>%
  group_by(ID, DateStart, DateEnd, Interaction) %>%
  summarise(
    view = sum(view, na.rm = TRUE),
    read = sum(read, na.rm = TRUE),
    .groups = "drop"
  )

Why Your Original Code Returned 0 #

Your apply() approach had two key issues:

• apply(df1, 1, ...) converts the entire data frame to a character matrix. This means your ID, StartDate, and EndDate arguments were passed as character strings instead of numeric/Date types—so the comparisons df2$date >= StartDate failed entirely.
• You passed the entire df1$StartDate and df1$EndDate columns to the function instead of the single row values that correspond to each iteration.

If you want to fix your original function, use mapply() instead (which handles multiple input vectors correctly):

# Fixed version of your functions
calc_view <- function(ID, StartDate, EndDate) {
  sum(df2$view[df2$ID == ID & df2$date >= StartDate & df2$date <= DateEnd], na.rm = TRUE)
}

calc_read <- function(ID, StartDate, EndDate) {
  sum(df2$read[df2$ID == ID & df2$date >= StartDate & df2$date <= DateEnd], na.rm = TRUE)
}

# Apply functions row-wise with mapply
df1$view <- mapply(calc_view, df1$ID, df1$DateStart, df1$DateEnd)
df1$read <- mapply(calc_read, df1$ID, df1$DateStart, df1$DateEnd)

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