CODEWITHSUNDEEP
rdplyr
user8009353
user8009353

Reputation: 35

Use previous value inside mutate function

I have a following table:

table<- data.frame(a = rep(100, 8),
                   b = seq(from = 0.2, by = 0.1, length.out = 8))

What I would like to obtain is following column c which has values:

  1. For the first row, take a*b
  2. For the second row, take previous row c, add it to the second row a and then multiply with b. Hence, ((100*0.2)+100)*0.3 = 36. This logic is then same for the rest of the rows. Hence, for the third row I would have: (100+36) * 0.4 = 54.4.

I tried something like code below, but I cannot refer to the new column inside mutate.

table %>% mutate(c = (a + ifelse(is.na(lag(c), 0, lag(c)))) * b)

Some tips?

Thank!:)

Upvotes: 2

Views: 1249

Answers (1)

Maurits Evers
Maurits Evers

Reputation: 50668

First off: table is a poor name for a user variable, as it corresponds to a reserved base R function.

Here is a solution using a for loop.

(Just to preempt any for loop objections: We're not dynamically growing an object here, and are instead changing pre-initialised entries, so a for loop will be fast; that's not saying that there won't be any faster ways...):

# Initialise
df <- transform(df, c = a * b);

# Calculate c for rows 2..
for (i in 2:nrow(df)) df$c[i] <- (df$c[i - 1] + df$a[i]) * df$b[i];
df;
#    a   b        c
#1 100 0.2  20.0000
#2 100 0.3  36.0000
#3 100 0.4  54.4000
#4 100 0.5  77.2000
#5 100 0.6 106.3200
#6 100 0.7 144.4240
#7 100 0.8 195.5392
#8 100 0.9 265.9853

Sample data

df <- data.frame(
    a = rep(100, 8),
    b = seq(from = 0.2, by = 0.1, length.out = 8))

Upvotes: 1

Related Questions