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

# Data

``````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(is.na(df[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