Can I extract the value from several columns by column's name?

user_13791972 0
Viewed 68
library(dplyr)
mydf <- data.frame(a_x =  c(1,2,3,4,5),
                   b_x = c(8,9,10,11,12),
                   a_y = c("k",'b','a','d','z'),
                   b_y = c('aa','bb','cc','dd','ee'),
                   prefix=c("a","b","c","a","a"))
mydf

Assuming that the data I have is mydf, I would like to produce the same result as mydf2.

I made a column with the name of the column containing the value to be extracted. I want to extract the value through this column.

mydf2 <- data.frame(a_x=c(1,2,3,4,5),
                    b_x=c(8,9,10,11,12),
                    prefix=c("a","b","c","a","a"),
                    desired_x_value = c(1,9,NA,4,5),
                    desired_y_value = c('k','bb',NA,'d','z'))
mydf2

I've used 'get' and 'paste0' but it doesn't work. Can I solve this problem through 'dplyr' chain?

mydf %>% mutate(desired_x_value = get(paste0(prefix,"_x")),
                desired_y_value = get(paste0(prefix,"_y")))
dplyr r
4 Answers

So basically you want to create new columns (desired_x_value and desired_y_value) of which its value depends on a condition. Using dplyr I prefer case_when as it is the best readable way to do it, but you could also use (nested) if(else) statements. What it is doing is "if X meets condition A do Y, if X meets condition B do Z, if X meets condition .... do ..."

mydf %>% 
  dplyr::mutate(
    desired_x_value = case_when(
      prefix == "a" ~ a_x,
      prefix == "b" ~ b_x,
    desired_y_values = case_when(
      prefix == "a" ~a_y,
      prefix == "b" ~b_y,
      TRUE ~ NA_character_ ))

You can remove the columns you don't need anymore in a second step if you want. the code above results in the table:

 a_x b_x a_y b_y prefix desired_x_value desired_y_values
1   1   8   k  aa      a               1                k
2   2   9   b  bb      b               9               bb
3   3  10   a  cc      c              NA             <NA>
4   4  11   d  dd      a               4                d
5   5  12   z  ee      a               5                z
user_123310880

You can write a helper function for this :

get_value <- function(data, prefix, group) {
  data[cbind(1:nrow(data), match(paste(prefix, group, sep = '_'), names(data)))]  
}

mydf %>%
  mutate(desired_x_value = get_value(select(., ends_with('_x')), prefix, 'x'), 
         desired_y_value = get_value(select(., ends_with('_y')), prefix, 'y'))

#  a_x b_x a_y b_y prefix desired_x_value desired_y_value
#1   1   8   k  aa      a               1               k
#2   2   9   b  bb      b               9              bb
#3   3  10   a  cc      c              NA            <NA>
#4   4  11   d  dd      a               4               d
#5   5  12   z  ee      a               5               z
user_39629140

A simple rowwise also works.

mydf %>% rowwise() %>%
  mutate(desired_x = ifelse(any(str_detect(names(mydf)[-5], prefix)),
                            get(paste(prefix, 'x', sep = '_')), NA),
         desired_y = ifelse(any(str_detect(names(mydf)[-5], prefix)),
                            get(paste(prefix, 'y', sep = '_')), NA))

# A tibble: 5 x 7
# Rowwise: 
    a_x   b_x a_y   b_y   prefix desired_x desired_y
  <dbl> <dbl> <chr> <chr> <chr>      <dbl> <chr>    
1     1     8 k     aa    a              1 k        
2     2     9 b     bb    b              9 bb       
3     3    10 a     cc    c             NA NA       
4     4    11 d     dd    a              4 d        
5     5    12 z     ee    a              5 z

If the prefixes don't contain any invalid column prefixes, this will do without ifelse statement.

mydf <- data.frame(a_x =  c(1,2,3,4,5),
                   b_x = c(8,9,10,11,12),
                   a_y = c("k",'b','a','d','z'),
                   b_y = c('aa','bb','cc','dd','ee'),
                   prefix=c("a","b","a","a","a"))

mydf %>% rowwise() %>%
  mutate(desired_x = get(paste(prefix, 'x', sep = '_')),
         desired_y = get(paste(prefix, 'y', sep = '_')))

# A tibble: 5 x 7
# Rowwise: 
    a_x   b_x a_y   b_y   prefix desired_x desired_y
  <dbl> <dbl> <chr> <chr> <chr>      <dbl> <chr>    
1     1     8 k     aa    a              1 k        
2     2     9 b     bb    b              9 bb       
3     3    10 a     cc    a              3 a        
4     4    11 d     dd    a              4 d        
5     5    12 z     ee    a              5 z
user_28848590

First I would like to say that I am not presenting this as a good solution as other proposed solutions are much better and simpler. However, since you have brought up get function, I wanted to show you how to make use of it to get your desired output. As a matter of fact some of the values in your prefix column such as c does not have a match among your column names and get function throws an error on terminating the execution, and unlike mget function it does not have a ifnotfound argument. So you need a way to go around that error message by means of an ifelse:

library(dplyr)
library(stringr)
library(tidyr)
library(purrr)
library(glue)

mydf1 %>%
  mutate(desired_x_value = map(prefix, ~ ifelse(any(str_detect(names(mydf)[-5], .x)), 
                                        get(glue("{.x}_x")), NA)), 
         desired_y_value = map(prefix, ~ ifelse(any(str_detect(names(mydf)[-5], .x)), 
                                                get(glue("{.x}_y")), NA))) %>%
  unnest(cols = c(desired_x_value, desired_y_value))


# A tibble: 5 x 7
    a_x   b_x a_y   b_y   prefix desired_x_value desired_y_value
  <dbl> <dbl> <chr> <chr> <chr>            <dbl> <chr>          
1     1     8 k     aa    a                    1 k              
2     2     9 b     bb    b                    9 bb             
3     3    10 a     cc    NA                  NA NA             
4     4    11 d     dd    a                    4 d              
5     5    12 z     ee    a                    5 z

You can also use paste function instead of glue and in case we already know the output types of the desired columns, we can spare the last line:

mydf1 %>%
  mutate(desired_x_value = map_dbl(prefix, ~ ifelse(any(str_detect(names(mydf)[-5], .x)), 
                                                get(paste(.x, "x", sep = "_")), NA)), 
         desired_y_value = map_chr(prefix, ~ ifelse(any(str_detect(names(mydf)[-5], .x)), 
                                                get(paste(.x, "y", sep = "_")), NA)))

# A tibble: 5 x 7
# Rowwise: 
    a_x   b_x a_y   b_y   prefix desired_x_value desired_y_value
  <dbl> <dbl> <chr> <chr> <chr>            <dbl> <chr>          
1     1     8 k     aa    a                    1 k              
2     2     9 b     bb    b                    9 bb             
3     3    10 a     cc    NA                  NA NA             
4     4    11 d     dd    a                    4 d              
5     5    12 z     ee    a                    5 z
user_143145200
Related