在 R 中等效的 Excel SUMIFS [英] Excel SUMIFS equivalent in R
问题描述
我对 R 非常陌生,正在寻找重新创建 Excel VBA 宏和 Excel 工作表函数(例如 SUMIFS)的方法.如果该行在其其他列上具有与多个条件匹配的条目,则 SUMIFS 会对该列求和.
I'm very very new to R and am looking at ways of recreating an Excel VBA macro and Excel worksheet functions such as SUMIFS. SUMIFS sums a column if the row has entries matching multiple conditions on its other columns.
我有以下数据框,我想计算一个新列.新列是与 Start Date
和 EndDate
范围重叠的所有行的 Sample
总和.例如,在 1
行上,它将是 697
(前 3 行
的总和).总和的标准特别是:包括 Sample
if EndDate >= StartDate[i] &开始日期 <=结束日期[i]
I have the below data frame and I want to compute a new column. The new column is the sum of Sample
for all rows that overlap with the Start Date
and EndDate
range. For example on line 1
it would be 697
(the sum of the first 3 lines
). The criteria for the sum specifically: include Sample
if EndDate >= StartDate[i] & StartDate <=EndDate[i]
StartDate EndDate Sample *SUMIFS example*
10/01/14 24/01/14 139 *697*
12/01/14 26/01/14 136
19/01/14 02/02/14 422
25/01/14 08/02/14 762
29/01/14 12/02/14 899
05/02/14 19/02/14 850
07/02/14 21/02/14 602
09/02/14 23/02/14 180
18/02/14 04/03/14 866
如有任何意见或建议,我们将不胜感激.
Any comments or pointers would be greatly appreciated.
推荐答案
您可以使用 base R
中的 lapply/sapply
来做到这一点.x
来自@cameron.bracken 的帖子.
You could use lapply/sapply
from base R
to do this. x
from @cameron.bracken's post.
x$sumifs <- sapply(seq_len(nrow(x)), function(i) with(x,
sum(Sample[EndDate >= StartDate[i] & StartDate <= EndDate[i]])))
x
# StartDate EndDate Sample sumifs
#1 2014-01-10 2014-01-24 139 697
#2 2014-01-12 2014-01-26 136 1459
#3 2014-01-19 2014-02-02 422 2358
#4 2014-01-25 2014-02-08 762 3671
#5 2014-01-29 2014-02-12 899 3715
#6 2014-02-05 2014-02-19 850 4159
#7 2014-02-07 2014-02-21 602 4159
#8 2014-02-09 2014-02-23 180 3397
#9 2014-02-18 2014-03-04 866 2498
这篇关于在 R 中等效的 Excel SUMIFS的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!