I've got a df and i need to perform a rolling sum in another dataframe based on the values of the first df

1. All the transactions of each company, by date & account
2. This is the dataframe I want to build with the balance at the end of the day with each company's account

In Excel this would be something like a SUMIF where you state that the criteria must me Company & Account % Date<=Date

Thnks in advance

CodePudding user response：

It would be better if you could write your code so anyone can reproduce it easier. For example

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

Anyway, have you tried something like:

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

CodePudding user response：

using data.table

library(data.table)

setDT(df)

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

results

#    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"))