Calculate column bottom to top from reference value R

user_18143306 0
Viewed 32

I have a dataframe that includes a column of change in water pressure. I also have a known/reference value. This value is the most recent value, so will be the final value in a new column. I need to use this to work backwards to populate the rest of the column.

Data frame

This is a snippet of the dataframe. The only relevant column is the last one.

structure(list(Rec = 1:10, DateTime = structure(c(1585468800, 
1585472400, 1585476000, 1585479600, 1585483200, 1585486800, 1585490400, 
1585494000, 1585497600, 1585501200), class = c("POSIXct", "POSIXt"
), tzone = "GMT"), TempHMB5 = c(6.78, 6.78, 6.78, 6.78, 6.78, 
6.77, 6.77, 6.77, 6.77, 6.76), PressHMB5 = c(1074.09, 1074.87, 
1074.8, 1075.02, 1074.95, 1074.95, 1074.59, 1074.45, 1074.45, 
1074.52), TempBaro = c(3.89, 5.1, 7.79, 8.89, 8.04, 8.01, 7.7, 
7.88, 7.25, 6.5), PressBaro = c(1053.98, 1054.5, 1054.53, 1054.71, 
1054.66, 1054.67, 1054.39, 1054.24, 1054.26, 1054.22), subPress = c(20.1099999999999, 
20.3699999999999, 20.27, 20.3099999999999, 20.29, 20.28, 20.1999999999998, 
20.21, 20.1900000000001, 20.3), subPressM = c(0.201099999999999, 
0.203699999999999, 0.2027, 0.203099999999999, 0.2029, 0.2028, 
0.201999999999998, 0.2021, 0.201900000000001, 0.203), subPressChange = c(0, 
0.00259999999999991, 0.00160000000000082, 0.00200000000000045, 
0.00180000000000063, 0.00170000000000073, 0.000899999999999179, 
0.00100000000000136, 0.000800000000001549, 0.00190000000000054
)), row.names = c(NA, 10L), class = "data.frame")

enter image description here

Desired Output

The new column needs to be calculated like below. So the last value in 'relToDip' column is the reference value of 0.285 and the rest of the column is populated off this e.g. B10=B11+A10.

enter image description here

r
1 Answers

Maybe this can be simplified, but in base R you can try the following. After setting an initial value, you can reverse your column subPressChange (assuming ordered by datetime). You can obtain the cumulative sum, and reverse again when adding the result column. We remove the last value in subPressChange that isn't used, and replace with your initial starting value of 0.285.

i <- 0.285
df$relToDrip <- c(rev(cumsum(rev(df$subPressChange[-nrow(df)])) + i), i)
df[, c(1, 2, 9, 10)]

Output

   Rec            DateTime subPressChange relToDrip
1    1 2020-03-29 08:00:00         0.0000    0.2974
2    2 2020-03-29 09:00:00         0.0026    0.2974
3    3 2020-03-29 10:00:00         0.0016    0.2948
4    4 2020-03-29 11:00:00         0.0020    0.2932
5    5 2020-03-29 12:00:00         0.0018    0.2912
6    6 2020-03-29 13:00:00         0.0017    0.2894
7    7 2020-03-29 14:00:00         0.0009    0.2877
8    8 2020-03-29 15:00:00         0.0010    0.2868
9    9 2020-03-29 16:00:00         0.0008    0.2858
10  10 2020-03-29 17:00:00         0.0019    0.2850
user_34606700
Related