Calculate mean by going forward without for loop

Question

My data frame looks like this:

BookValue  Maturity   Yield  Weight
   20      2018     4.000  0.00282
   30      2019     4.550  0.00424
   70      2026     1.438  0.00989
   80      2026     1.438  0.01131
   60      2043     0.000  0.00848
   40      2043     0.000  0.00565

I want to calculate the sum of the total book values of all years by reducing in every step one year in order to get the following output:

Year       Book Value
2018-2043     300 
2019-2043     280
2026-2043     250
2043          100

How is it possible and is it possible without a for-loop?

Darren Tsai · Answer 1 · 2020-08-10T08:55:13.853

4

With a base way, You can use rev() and cumsum().

val <- tapply(df$BookValue, df$Maturity, sum)
rev(cumsum(rev(val)))

# 2018 2019 2026 2043 
#  300  280  250  100

Data

df <- data.frame(BookValue = c(20, 30, 70, 80, 60, 40),
                 Maturity = c(2018, 2019, 2026, 2026, 2043, 2043))

edited Aug 10 '20 at 08:55

answered Jul 12 '18 at 10:17

Darren Tsai

15,872
4
15
40

perfect, thank you very much. – Christian Jul 12 '18 at 10:34
@Christian if my answer has solved your question, please mark it as accepted by clicking the check mark next to it. Thank you! – Darren Tsai Aug 15 '20 at 08:53

score 2 · Answer 2 · answered Jul 12 '18 at 10:17

Here is a possible approach using base functions:

#aggregate by year first
ans <- aggregate(dat$BookValue, list(dat$Maturity), sum)
N <- nrow(ans)

#then sum from 1:N, 2:N, 3:N, and so on
if (nrow(ans) >= 1) {
    ans$BVSum <- sapply(1:N, function(n) sum(ans$x[ n:N ]))
}

data:

dat <- read.table(text="BookValue  Maturity   Yield  Weight
20      2018     4.000  0.00282
30      2019     4.550  0.00424
70      2026     1.438  0.00989
80      2026     1.438  0.01131
60      2043     0.000  0.00848
40      2043     0.000  0.00565", header=TRUE)

score 2 · Answer 3 · answered Jul 12 '18 at 10:37

An alternative:

# Assuming df is in order we extract first row for each year:
frow <- which(!duplicated(df$Maturity))
n <- nrow(df)


tbv <- lapply(
  frow, 
  function(x) {
    data.frame(
      year = paste0(df$Maturity[x], "-", df$Maturity[n]),
      book_value = sum(df$BookValue[x:n])
    )
  }
)
do.call(rbind, tbv)
       year book_value
1 2018-2043        300
2 2019-2043        280
3 2026-2043        250
4 2043-2043        100

Calculate mean by going forward without for loop

3 Answers3