CODEWITHSUNDEEP
rdplyr
j235
j235

Reputation: 150

Multiplying by cumulative product within data frame

I have one data.frame with four columns, T,Price, Adjustment_factor and Adjusted price. So with columns Price and Adjustment_factor I want to calculate Adjusted_price like table below.

T      Price     Adjustment_factor       Adjusted_price
----------------------------------------------------
2010    78,974     1,000                      79,01
2012    78,935     1,008                      78,97
2013    78,294     1,081                      78,97
2014    72,436     1,070                      78,97
2015    67,700     1,000                      78,97

Formula for calculation Adjusted_price is not same for every row. 

E.g For 2015  
         78,97 = 67,700 * (1,070 * 1,081 * 1,008 * 1,000)
    For 2014 
         78,97 =  72,436 * (1,081 * 1,008 * 1,000)
    For 2013 
        78,97 =  78,294 * ( 1,008 * 1,000)

Can anyone help me with some code for calculation?

Upvotes: 0

Views: 3136

Answers (2)

A. Suliman
A. Suliman

Reputation: 13125

Using @Maurits cumprod and dplyr::lag

library(dplyr)
df %>% 
   mutate(Adjusted_price = Price * lag(cumprod(Adjustment_factor), k=1, default=0))

     T  Price Adjustment_factor Adjusted_price
1 2010 78.974             1.000        0.00000
2 2012 78.935             1.008       78.93500
3 2013 78.294             1.081       78.92035
4 2014 72.436             1.070       78.92974
5 2015 67.700             1.000       78.93301

Upvotes: 1

Maurits Evers
Maurits Evers

Reputation: 50678

It seems you're looking for cumprod.

In base R:

transform(df, Adjusted_price = Price * cumprod(Adjustment_factor))
#     T  Price Adjustment_factor Adjusted_price
#1 2010 78.974             1.000       78.97400
#2 2012 78.935             1.008       79.56648
#3 2013 78.294             1.081       85.31290
#4 2014 72.436             1.070       84.45482
#5 2015 67.700             1.000       78.93301

Or to write directly to a new column Adjusted_price:

df$Adjusted_price <- df$Price * cumprod(df$Adjustment_factor)

Or the tidyverse way:

library(tidyverse)
df %>%
    arrange(T) %>%
    mutate(Adjusted_price = Price * cumprod(Adjustment_factor))
#     T  Price Adjustment_factor Adjusted_price
#1 2010 78.974             1.000       78.97400
#2 2012 78.935             1.008       79.56648
#3 2013 78.294             1.081       85.31290
#4 2014 72.436             1.070       84.45482
#5 2015 67.700             1.000       78.93301

Sample data

df <- read.table(text =
    "T      Price     Adjustment_factor
2010    78.974     1.000
2012    78.935     1.008
2013    78.294     1.081
2014    72.436     1.070
2015    67.700     1.000", header = T)

Upvotes: 0

Related Questions