CODEWITHSUNDEEP
rdplyr
c.custer
c.custer

Reputation: 419

Conditionally replace values of subset of rows with column name in R using only tidy

I am looking for a way to conditionally replace certain values in a subset of columns with that column name staying in the tidyverse. See example below:

wl <- data_frame(x=1:4,
  multi=c("Y","Y","Y","Y"),
  ABC=c("","Y","Y",""), 
  ABD=c("","","",""),
  ABE=c("Y","Y","","Y"))

# A tibble: 4 x 5
      x multi ABC   ABD   ABE  
  <int> <chr> <chr> <chr> <chr>
1     1 Y     ""    ""    Y    
2     2 Y     Y     ""    Y    
3     3 Y     Y     ""    ""   
4     4 Y     ""    ""    Y   

df <- wl %>% mutate_at(vars(matches("AB")),
         funs(replace(.,.=='Y',values="column name")))
# A tibble: 4 x 5
      x multi ABC         ABD   ABE        
  <int> <chr> <chr>       <chr> <chr>      
1     1 Y     ""          ""    column name
2     2 Y     column name ""    column name
3     3 Y     column name ""    ""         
4     4 Y     ""          ""    column name

except it would be the actual column name instead of 'column name'. I've used two different answers on here to get to both mutating on conditional columns (which is how I got to my example) and how to replace certains values with a column name (not used in my current example but the answer is here).

I can combine the two answers to get it to work: 

w <- which(df=="column name",arr.ind=TRUE)
df[w] <- names(df)[w[,"col"]]                 
# A tibble: 4 x 5
      x multi ABC   ABD   ABE  
  <int> <chr> <chr> <chr> <chr>
1     1 Y     ""    ""    ABE  
2     2 Y     ABC   ""    ABE  
3     3 Y     ABC   ""    ""   
4     4 Y     ""    ""    ABE

I acknowledge the process above is completely functional but out of pure curiosity, is there a way to do this without the second chunk of code? Is there some function I can plug into the values= portion of the replace(.,.=='Y',values="column name") step that can capture the column name and perform this entire process within a single mutate_at function?

Upvotes: 3

Views: 1847

Answers (4)

akrun
akrun

Reputation: 887213

With tidy verse

library(dplyr)
wl %>% 
  mutate_at(vars(starts_with("AB")),
     funs(c("", deparse(substitute(.)))[(.=="Y" & !is.na(.)) + 1]))
# A tibble: 4 x 5
#     x multi ABC   ABD   ABE  
#  <int> <chr> <chr> <chr> <chr>
#1     1 Y     ""    ""    ABE  
#2     2 Y     ABC   ""    ABE  
#3     3 Y     ABC   ""    ""   
#4     4 Y     ""    ""    ABE

Using base R

nm1 <- grep("^AB", names(wl))
i1 <- wl[,(nm1)] == "Y" & !is.na(wl[,(nm1)])
wl[,(nm1)][i1] <- names(wl)[(nm1)][col(wl[,(nm1)])][i1]
wl
# A tibble: 4 x 5
#      x multi ABC   ABD   ABE  
#  <int> <chr> <chr> <chr> <chr>
#1     1 Y     ""    ""    ABE  
#2     2 Y     ABC   ""    ABE  
#3     3 Y     ABC   ""    ""   
#4     4 Y     ""    ""    ABE

Or we can do this faster by assigning in place with set from data.table

library(data.table)
setDT(wl)
for(j in nm1) set(wl, i=which(wl[[j]] == "Y"), j=j, names(wl)[j])
wl
#   x multi ABC ABD ABE
#1: 1     Y         ABE
#2: 2     Y ABC     ABE
#3: 3     Y ABC        
#4: 4     Y         ABE

Upvotes: 1

markus
markus

Reputation: 26343

Here is an option with imap

library(purrr)
AB_cols <- grep("^AB", names(wl)) # find positions of columns that start with "AB"
wl[AB_cols] <- imap(.x = wl[AB_cols], .f = ~replace(.x, .x == "Y", .y))
wl
# A tibble: 4 x 5
#      x multi ABC   ABD   ABE  
#  <int> <chr> <chr> <chr> <chr>
#1     1 Y     ""    ""    ABE  
#2     2 Y     ABC   ""    ABE  
#3     3 Y     ABC   ""    ""   
#4     4 Y     ""    ""    ABE

Upvotes: 1

Onyambu
Onyambu

Reputation: 79238

You can use replace_na:

wl%>%
   mutate_at(vars(starts_with('AB')),~`is.na<-`(.x,.x=='Y'))%>%
   replace_na(set_names(as.list(names(.)),names(.)))
# A tibble: 4 x 5
  x     multi ABC   ABD   ABE  
  <chr> <chr> <chr> <chr> <chr>
1 1     Y     ""    ""    ABE  
2 2     Y     ABC   ""    ABE  
3 3     Y     ABC   ""    ""   
4 4     Y     ""    ""    ABE

Upvotes: 1

camille
camille

Reputation: 16842

You can do a little tidyeval in your mutate_at function to get the column name, then an ifelse (or whatever other logic structure you might want) to replace certain values.

library(tidyverse)

wl %>%
  mutate_at(vars(starts_with("AB")), function(x) {
    x_var <- rlang::enquo(x)
    ifelse(x == "Y", rlang::quo_name(x_var), x)
  })
#> # A tibble: 4 x 5
#>       x multi ABC   ABD   ABE  
#>   <int> <chr> <chr> <chr> <chr>
#> 1     1 Y     ""    ""    ABE  
#> 2     2 Y     ABC   ""    ABE  
#> 3     3 Y     ABC   ""    ""   
#> 4     4 Y     ""    ""    ABE

Created on 2018-08-16 by the reprex package (v0.2.0).

Upvotes: 2

Related Questions