根据另一个数据框架在R中按3条条件滚动总和

Question

我有一个DF，我需要根据第一个DF的值在另一个数据框中执行滚动总和

1. ，按日期＆amp;帐户
2. 一天

结束	是	的	余额
数据	这	时	要
我	构建	框	在
​	​	​	​
2021-01-02	A	A	12
2021-01-02	A	A	4
2021-01-02	A	B	4

日期	公司	帐户	价值
2021-01-01	A	A	10
2021-01-01	A	B	15
2021-01-02	A	A	26
2021-01-02	A	B	14
2021-01-01	b	x	I
2021-01-01	b	y	I
2021-01-01-01-01-01-01-02	b	x	ii
2021-01-01-01-01-01-01-02	b	y	ii

在Excel中sumif您指出标准必须我公司＆amp;帐户％date＆lt; =

提前的日期thnks

Answer 1

使用data.table

library(data.table)

setDT(df)

df[, .(Value = sum(Value)), by = .(Date, Company, Account)][, .(Date, Value = cumsum(Value)), by = .(Company, Account)]

结果

#    Company Account       Date Value
# 1:       A       a 2021-01-01    10
# 2:       A       a 2021-01-02    26
# 3:       A       b 2021-01-01    15
# 4:       A       b 2021-01-02    19

data

df <- structure(list(Date = structure(c(18628L, 18628L, 18628L, 18629L, 
18629L, 18629L), class = c("IDate", "Date")), Company = c("A", 
"A", "A", "A", "A", "A"), Account = c("a", "b", "b", "a", "a", 
"b"), Value = c(10L, 10L, 5L, 12L, 4L, 4L)), row.names = c(NA, 
-6L), class = c("data.frame"))

编辑评论中OP的额外要求

# same code as above
df <- df[, .(Value = sum(Value)), by = .(Date, Company, Account)][, .(Date, Value = cumsum(Value)), by = .(Company, Account)]

# now create a small table with the full range of dates per group
fullrange <- df[, .(Date = seq(min(Date), max(Date), "days")), .(Company, Account)]

# merge the two tables, missing dates will here get NA as value
output <- merge(df, fullrange, by = c("Company", "Account", "Date"), all = T)

# now replace the NA values with the previous value
setnafill(output, type = "locf", cols = "Value")

output

#    Company Account       Date Value
# 1:       A       a 2021-01-01    10
# 2:       A       a 2021-01-02    10
# 3:       A       a 2021-01-03    26
# 4:       A       b 2021-01-01    15
# 5:       A       b 2021-01-02    15
# 6:       A       b 2021-01-03    19

# changed the data a bit and replaced the 2021-01-02 for 2021-01-03
df <- structure(list(Date = structure(c(18628L, 18628L, 18628L, 18630L, 
18630L, 18630L), class = "Date"), Company = c("A", "A", "A", 
"A", "A", "A"), Account = c("a", "b", "b", "a", "a", "b"), Value = c(10L, 
10L, 5L, 12L, 4L, 4L)), row.names = c(NA, -6L), class = c("data.frame"))

Answer 2

如果您可以编写代码，这样任何人都可以更轻松地复制它，那就更好了。
例如

df <- data.frame(
  Date = c("2021-01-02", "2021-01-02"),
  Company = c("A", "A"),
  Account = c("a", "b"),
  Value = c(10, 12),
)

，您是否尝试过类似的事情：

aggregate(Value~Company+Account, data=df, FUN=sum)