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 · 2022-07-04T07:50:21.727

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 Jul 04 '22 at 07:50

answered Jul 12 '18 at 10:17

Darren Tsai

32,117
5
21
51

perfect, thank you very much. – Christian Jul 12 '18 at 10:34

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

Data