R - Select and return column value based on value in another column

Question

I'd like to select and return a column value contained within one of a subset of columns, based on the value in another column.

Specifically, I'd like to create a new variable ("NEWE") by selecting the value in RE when the MAX variable = "RP" and by selecting the value in IE when the MAX variable = "IP".

Of note, I am working with a data table of ~160 columns and ~70,000 rows (I am noting the existence of additional columns in my example by including V1-V9).

Have:

V1  …   V9  RE  IE  MAX
4   …   3   3   4   RP  
6   …   6   3   2   IP  
2   …   2   1   2   IP  
6   …   2   2   3   RP  
.   .   .   .   .   .
.   .   .   .   .   .   
.   .   .   .   .   .

And the data set I would like:

V1  …   V9  RE  IE  MAX NEWE
4   …   3   3   4   RP  3
6   …   6   3   2   IP  2
2   …   2   1   2   IP  2
6   …   2   2   3   RP  2
.   .   .   .   .   .   .
.   .   .   .   .   .   .
.   .   .   .   .   .   .

Thanks in advance for your help!

No, the procedure will be carried out in two iterations between six different columns — Justin
– Justin, Commented Dec 5, 2016 at 19:01
didn't get your point ..2 iterations? anyway hope my answer helps you in it — joel.wilson
– joel.wilson, Commented Dec 5, 2016 at 19:07
First, "MAX" value (of which there are six unique strings) will be used to choose the value from one of six columns: when RP choose value in RE, when IP choose IE, when AP choose AE, when SP chose SE, when EP choose EE, when CP chose CE. Then I need to do the same thing where "MAX" will be used to choose the value from one of six DIFFERENT columns (say RA, IA, AA, SA, EA, CA). This would result in a second new variable added ("NEWA"). Could you extend to this more complete example? Thanks for the help here! — Justin
– Justin, Commented Dec 5, 2016 at 19:08

joel.wilson · Accepted Answer · 2016-12-06 05:51:23Z

4

data$NEWE <- ifelse(data$MAX == "RP", data$RE, ifelse(data$MAX == "RP",data$IE, "value if both doesn't satify"))

if its either of RP and IP always!

data$NEWE <- ifelse(data$MAX == "RP", data$RE , data$IE)

for your case : its a simple extension of ifelse - (to avoid typo prefer using with())

data$NEWE <- with(data, ifelse(MAX == "RP", RE,
                               ifelse(MAX == "IP",IE, 
                                     ifelse(MAX == "AP", AE,
                                           ifelse(MAX == "SP", SE,
                                                  ifelse(MAX == "EP",EE,
                                                         ifelse(MAX == "CP", CE, NA)))))))

edited Dec 6, 2016 at 5:51

answered Dec 5, 2016 at 18:25

joel.wilson

8,4535 gold badges30 silver badges49 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

R - Select and return column value based on value in another column

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related