Select from column in dataframe based on value in another column

Question

I have a dataframe as follows:

dataDF <- data.frame(
          id = 1:5,
          to_choose = c('red', 'blue', 'red', 'green', 'yellow'),
          red_value = c(1,2,3,4,5),
          blue_value = c(6,7,8,9,10),
         yellow_value = c(11,12,13,14,15)
)

id to_choose red_value blue_value yellow_value
 1       red         1          6           11
 2      blue         2          7           12
 3       red         3          8           13
 4     green         4          9           14
 5    yellow         5         10           15

I want to create a new column value, which is the value from the appropriate column based on the to_choose column.

I could do this with an ifelse as follows

mutate(dataDF,
   value = ifelse(to_choose == 'red', red_value,
                  ifelse(to_choose == 'blue', blue_value,
                         ifelse(to_choose == 'yellow', yellow_value, NA))))

To give

id to_choose red_value blue_value yellow_value value
 1       red         1          6           11     1
 2      blue         2          7           12     7
 3       red         3          8           13     3
 4     green         4          9           14    NA
 5    yellow         5         10           15    15

But if there a simpler one line way of doing this along the lines of

mutate(dataDF, value = paste(to_choose, 'value', sep = '_'))

kath · Accepted Answer · 2018-10-11 14:03:59Z

4

dataDF %>% 
  gather(var, value , 3:5) %>%   
  mutate(var = gsub('_value', '', var))  %>% 
  filter(to_choose == var)

edited Oct 11, 2018 at 14:03

kath

7,75419 silver badges32 bronze badges

answered Oct 11, 2018 at 13:55

Mouad_Seridi

2,71617 silver badges29 bronze badges

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

Comments

Ronak Shah · Accepted Answer · 2018-10-11 14:05:43Z

A base R approach using mapply

dataDF$value <- mapply(function(x, y) if(length(y) > 0)  dataDF[x, y] else NA, 
      1:nrow(dataDF), sapply(dataDF$to_choose, function(x) grep(x, names(dataDF))))


dataDF

#  id to_choose red_value blue_value yellow_value value
#1  1       red         1          6           11     1
#2  2      blue         2          7           12     7
#3  3       red         3          8           13     3
#4  4     green         4          9           14    NA
#5  5    yellow         5         10           15    15

The idea is to get the appropriate row and column indices to subset upon. Row indices we are already know that we need to get value for each row of the dataframe. As far as getting the appropriate column is concerned we use grep over to_choose to find the column index from where the value needs to be extracted.

Collectives™ on Stack Overflow

Select from column in dataframe based on value in another column

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related