I am trying to replicate some modelling I did in Excel, using R. I have read the data from a csv file into a dataframe. The csv file contains two columns of data A and B.
I now want to add additional columns C, D and E to the dataframe and to populate the new columns C, D and E with data generated by applying a formula to the data in the previous columns.
The snippet below should hopefully clarify what I'm trying to do:
A B C D E
1 100.5 101.3
2 102.6 102.5
3 107.2 109.3
4 99.3 89.3
5 102.8 100.7 =(B5-B1)*A5 = C5 = IF(D5 >100,1,-1)
6 107.2 98.9 =(B6-B2)*A6 = C6+C5 = IF(D6 >100,1,-1)
7 99.8 109.9 =(B7-B3)*A7 = C7+C6 = IF(D7 >100,1,-1)
8 108.2 99.5 =(B8-B4)*A8 = C8+C7 = IF(D8 >100,1,-1)
9 78.7 89.6 =(B9-B5)*A9 = C9+C8 = IF(D9 >100,1,-1)
10 108.9 109.2 =(B10-B6)*A10 = C10+C9 = IF(D10 >100,1,-1)
How can I replicate this kind of "columnar" functional programming that Excel (ahem - Excels) in - using R?
My brain is doing this under protest. It makes me feel that I'm back at a Minitab session.
dfrm$C <- NA
dfrm$C[5:10] <- with(dfrm, (B[5:10]-B[1:6])*A[5:10])
dfrm$D <- NA
dfrm$D[5:10] <- cumsum(dfrm$C[5:10])
dfrm$E <- NA
dfrm$E[5:10] <- 1 - 2*(dfrm$D[5:10] <= 100) # could also use ifelse()
dfrm
A B C D E
1 100.5 101.3 NA NA NA
2 102.6 102.5 NA NA NA
3 107.2 109.3 NA NA NA
4 99.3 89.3 NA NA NA
5 102.8 100.7 -61.68 -61.68 -1
6 107.2 98.9 -385.92 -447.60 -1
7 99.8 109.9 59.88 -387.72 -1
8 108.2 99.5 1103.64 715.92 1
9 78.7 89.6 -873.57 -157.65 -1
10 108.9 109.2 1121.67 964.02 1
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With