CODEWITHSUNDEEP
r
Vasco de Gama
Vasco de Gama

Reputation: 49

R: conditional populating of a column in a table based on input from other columns

I have a table with dates in column 1 (Column titled "Date") and values in columns 2 through 5 (Columns titled "A"->"D").

Date   A  B  C  D
1/1/16 12 75 38 88
1/2/16 32 76 44 34

etc.

I need to create additional 6th column E that contains the following:

For each row:

If value in column A > B > C then value in column E = X

If value in column A < B < C then value in column E = Y

for anything else value in column E = Z

What is the best way to do this?

Upvotes: 0

Views: 1146

Answers (2)

dcc310
dcc310

Reputation: 1076

I think this should work fine:

set.seed(1)
myframe = data.frame(date=1:10, a=sample(1:10), b=sample(1:10),     c=sample(1:10), d=sample(1:10), e=NA)
myframe[myframe$a > myframe$b & myframe$b > myframe$c, "e"] = "x"
myframe[myframe$a < myframe$b & myframe$b < myframe$c, "e"] = "y"
myframe[is.na(myframe$e), "e"] = "z"
myframe

Gives 

   date  a  b  c  d e
1     1  3  3 10  5 z
2     2  4  2  2  6 z
3     3  5  6  6  4 z
4     4  7 10  1  2 z
5     5  2  5  9 10 y
6     6  8  7  8  8 z
7     7  9  8  7  9 x
8     8  6  4  5  1 z
9     9 10  1  3  7 z
10   10  1  9  4  3 z

if x <- 1:4 gives 1 2 3 4 then x < - 1:4 < 3 is TRUE TRUE FALSE FALSE. So, someFrame[x, "someCol"] selects that col from rows where x is TRUE, i.e. the first and second rows. The same works for vectors, so c("a", "b", "c", "d")[x] returns a b. I've heard this called "logical indexing", for what it's worth.

Upvotes: 1

Hack-R
Hack-R

Reputation: 23216

# Here I'm simulating your original dataset     
df <- data.frame(Date=seq(Sys.Date(),Sys.Date()+9,by=1), A = seq(1,20,2),
                 B = rep(10,1,1), C=abs(rnorm(10)), D = rnorm(10))  
# Create E
df$E <- NA  
df$E[df$A > df$B & df$B > df$C] <- "X"
df$E[df$A < df$B & df$B < df$C] <- "Y"
df$E[is.na(df$E)]               <- "Z"
df



         Date  A  B            C              D E
1  2016-06-29  1 10 0.5833273005 -0.25244803522 Z
2  2016-06-30  3 10 0.4291374487  0.01669504752 Z
3  2016-07-01  5 10 1.7079045597  1.28413741595 Z
4  2016-07-02  7 10 0.2286708311  1.16421926818 Z
5  2016-07-03  9 10 0.6216853471  1.08934300378 Z
6  2016-07-04 11 10 1.4662821456 -0.58322427720 X
7  2016-07-05 13 10 0.8255102263  0.65217873906 X
8  2016-07-06 15 10 1.6185672627  0.04195996408 X
9  2016-07-07 17 10 0.6752993011 -2.31746231694 X
10 2016-07-08 19 10 0.2901133125  0.97969860678 X

# Create E only for a subset of rows, like 6:10 
df$E <- NA
df$E[1:5] <- "nothing applied to this row"  
df$E[df$A > df$B & df$B > df$C & 6:10] <- "X"
df$E[df$A < df$B & df$B < df$C & 6:10] <- "Y"
df$E[is.na(df$E) & 6:10]               <- "Z"
df

         Date  A  B            C              D                           E
1  2016-06-29  1 10 0.5833273005 -0.25244803522 nothing applied to this row
2  2016-06-30  3 10 0.4291374487  0.01669504752 nothing applied to this row
3  2016-07-01  5 10 1.7079045597  1.28413741595 nothing applied to this row
4  2016-07-02  7 10 0.2286708311  1.16421926818 nothing applied to this row
5  2016-07-03  9 10 0.6216853471  1.08934300378 nothing applied to this row
6  2016-07-04 11 10 1.4662821456 -0.58322427720                           X
7  2016-07-05 13 10 0.8255102263  0.65217873906                           X
8  2016-07-06 15 10 1.6185672627  0.04195996408                           X
9  2016-07-07 17 10 0.6752993011 -2.31746231694                           X
10 2016-07-08 19 10 0.2901133125  0.97969860678                           X

Upvotes: 1

Related Questions