如何在 R 中做 SUMIFS [英] How to do SUMIFS in R

问题描述

数据:

 set.seed(42)

df1 = data.frame(
  Date = seq.Date(as.Date("2018-01-01"),as.Date("2018-01-30"),1),
  value = sample(1:30),
  Y = sample(c("yes", "no"), 30, replace = TRUE)
)

df2 = data.frame(
  Date = seq.Date(as.Date("2018-01-01"),as.Date("2018-01-30"),7)
)

如果 df1$Date 中的日期落在 df2$Date 内，我想为 df2$Date 中的每个日期计算 df1$Value 的总和> 和 df2$Date+6

I want for each date in df2$Date calculate the sum of df1$Value if date in df1$Date falls within df2$Date and df2$Date+6

简而言之，我需要计算每周总和

Inshort I need to calculate weekly sums

推荐答案

使用 data.table，创建一个范围开始/结束，然后合并重叠，然后在组上求和:

Using data.table, create a range start/end, then merge on overlap, then get sum over group:

library(data.table)

df1$start <- df1$Date
df1$end <- df1$Date

df2$start <- df2$Date
df2$end <- df2$Date + 6

setDT(df1, key = c("start", "end"))
setDT(df2, key = c("start", "end"))

foverlaps(df1, df2)[, list(mySum = sum(value)), by = Date ]
#          Date mySum
# 1: 2018-01-01   138
# 2: 2018-01-08    96
# 3: 2018-01-15    83
# 4: 2018-01-22   109
# 5: 2018-01-29    39

这篇关于如何在 R 中做 SUMIFS的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！