apply function across columns using column names within function

Question

I am trying to iterate over 100 columns to identify whether the a variable in a separate column matches the column name. I thought maybe the across function might be able to but can't figure out how to use the mutate on each column. See example below.

tst=structure(list(type = c("DOG", "DOG", "DOG", "CAT", "CAT", "CAT", 
"MOUSE", "MOUSE", "MOUSE"), CAT = c(NA_character_, NA_character_, 
NA_character_, NA_character_, NA_character_, NA_character_, NA_character_, 
NA_character_, NA_character_), DOG = c(NA_character_, NA_character_, 
NA_character_, NA_character_, NA_character_, NA_character_, NA_character_, 
NA_character_, NA_character_), MOUSE = c(NA_character_, NA_character_, 
NA_character_, NA_character_, NA_character_, NA_character_, NA_character_, 
NA_character_, NA_character_), id = 1:9), row.names = c(NA, -9L
), class = c("tbl_df", "tbl", "data.frame"))

My table currently has the following structure.

   type  CAT   DOG   MOUSE    id
  <chr> <chr> <chr> <chr> <int>
1 DOG   NA    NA    NA        1
2 DOG   NA    NA    NA        2
3 DOG   NA    NA    NA        3
4 CAT   NA    NA    NA        4
5 CAT   NA    NA    NA        5
6 CAT   NA    NA    NA        6
7 MOUSE NA    NA    NA        7
8 MOUSE NA    NA    NA        8
9 MOUSE NA    NA    NA        9

I would like the end result to look like this:

   type  CAT   DOG   MOUSE    id
  <chr> <chr> <chr> <chr> <int>
1 DOG   NA    TRUE  NA        1
2 DOG   NA    TRUE  NA        2
3 DOG   NA    TRUE  NA        3
4 CAT   TRUE  NA    NA        4
5 CAT   TRUE  NA    NA        5
6 CAT   TRUE  NA    NA        6
7 MOUSE NA    NA    TRUE      7
8 MOUSE NA    NA    TRUE      8
9 MOUSE NA    NA    TRUE      9 

This works but it is not sufficient for 100 columns.

tst<-tst%>%mutate(CAT=ifelse(type==names(tst[2]),'TRUE',NA))
tst<-tst%>%mutate(DOG=ifelse(type==names(tst[3]),'TRUE',NA))
tst<-tst%>%mutate(MOUSE=ifelse(type==names(tst[4]),'TRUE',NA))

score 1 · Accepted Answer · answered Mar 17 '21 at 22:50

A candidate solution is the following (Without dplyr)

# initialise list
tmpList <- list()

# iterate over each row
for (i in 1:nrow(tst)) {
  
  tmpList[[i]] <- colnames(tst[-c(1,5)]) %in% tst$type[i]
  
}

# save as data frame
output <- as.data.frame(do.call(rbind, tmpList))
colnames(output) <- colnames(tst[-c(1,5)]) 

# cbind with data
output <- cbind(tst[,c(1,5)],output)

Which gives what you are looking for! If there is a better a solution, it is not something that readily comes to my mind.

Best!

score 0 · Answer 2 · answered Apr 23 '21 at 10:54

The dplyr solution to this is super neat. You can use across() with cur_column() to get the name of the current column, and put that in your formula:

tst %>% 
  mutate(across(CAT:MOUSE, ~if_else(type == cur_column(), 'TRUE', .x)))

You can replace CAT:MOUSE with whatever tidy select function you need to pick up your 100+ columns.

Here's the full reprex:

library(tidyverse)

# I like tibbles because they print nicely, but this could just be a plain dataframe
tst <- tibble(
  type = c(
    "DOG", "DOG", "DOG", "CAT", "CAT", "CAT",
    "MOUSE", "MOUSE", "MOUSE"
  ),
  CAT = NA_character_,
  DOG = NA_character_,
  MOUSE = NA_character_,
  id = 1:9
)

# .x here could be NA_character_, if you don't want the value from the existing column
tst %>% 
  mutate(across(CAT:MOUSE, ~if_else(type == cur_column(), 'TRUE', .x)))
#> # A tibble: 9 x 5
#>   type  CAT   DOG   MOUSE    id
#>   <chr> <chr> <chr> <chr> <int>
#> 1 DOG   <NA>  TRUE  <NA>      1
#> 2 DOG   <NA>  TRUE  <NA>      2
#> 3 DOG   <NA>  TRUE  <NA>      3
#> 4 CAT   TRUE  <NA>  <NA>      4
#> 5 CAT   TRUE  <NA>  <NA>      5
#> 6 CAT   TRUE  <NA>  <NA>      6
#> 7 MOUSE <NA>  <NA>  TRUE      7
#> 8 MOUSE <NA>  <NA>  TRUE      8
#> 9 MOUSE <NA>  <NA>  TRUE      9

^{Created on 2021-04-23 by the reprex package (v1.0.0)}

apply function across columns using column names within function

2 Answers2