Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Excel like column operations in R dataframe

Tags:

dataframe

r

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?

like image 639
Homunculus Reticulli Avatar asked Apr 25 '12 22:04

Homunculus Reticulli


1 Answers

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
like image 130
IRTFM Avatar answered Sep 30 '22 04:09

IRTFM