Blake Walden Blake Walden - 1 year ago 106
R Question

How to Calculate Period Change with Criteria Column

I want to calculate percent change as you move down a data frame column, but with extra criteria. With the below table as an example, I want to calculate the percent change between dates, but subtracting out Value.2 on the same period before doing the calculation. The subtraction could not affect Value.1 because the full value is needed for the next period's percent change.

Date Value.1 Value.2 Percent.Change
1 1/1/2017 100 NA NA
2 1/2/2017 105 NA 0.05
3 1/3/2017 103 NA -0.02
4 1/4/2017 101 NA -0.02
5 1/5/2017 99 10 -0.12
6 1/6/2017 115 NA 0.16

The part I'm unsure about is the conditional calculation. I can calculate period return no problem, but I would do that using the Value.1 column. I was unsure how to calculate period return while subtracting out Value.2 from Value.1 for the calculation and then putting Value.2 back into Value.1 for the next period's calculation.

N.B. the table shows the values I'm trying to solve for.

All help is welcome, thanks!

Answer Source


df <- data.frame("Date" =  c("1/1/2017","1/2/2017","1/3/2017","1/4/2017","1/5/2017","1/6/2017"),
                 "Value.1" = c(100,105,103,101,99,115), "Value.2" = c(NA,NA,NA,NA,10,NA))

This should do it-

df$percent.change <- c(NA, ((df[2:nrow(df), "Value.1"] - 
                       ifelse([2 : nrow(df), "Value.2"]),
                              0, df[2:nrow(df), "Value.2"]))
                      - df[1:(nrow(df) - 1), "Value.1"]) / df[1:(nrow(df)-1),"Value.1"])
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download