Creating one variable from a list of variables in R?

Question

I have a sequence of variables in a dataframe (over 100) and I would like to create an indicator variable for if particular text patterns are present in any of the variables. Below is an example with three variables. One solution I've found is using tidyr::unite() followed by dplyr::mutate(), but I'm interested in a solution where I do not have to unite the variables.

c1<-c("T1", "X1", "T6", "R5")
c2<-c("R4", "C6", "C7", "X3")
c3<-c("C5", "C2", "X4", "T2")

df<-data.frame(c1, c2, c3)

  c1 c2 c3
1 T1 R4 C5
2 X1 C6 C2
3 T6 C7 X4
4 R5 X3 T2

code.vec<-c("T1", "T2", "T3", "T4") #Text patterns of interest
code_regex<-paste(code.vec, collapse="|")

new<-df %>% 
  unite(all_c, c1:c3, remove=FALSE) %>% 
  mutate(indicator=if_else(grepl(code_regex, all_c), 1, 0)) %>% 
  select(-(all_c))

  c1 c2 c3 indicator
1 T1 R4 C5 1
2 X1 C6 C2 0
3 T6 C7 X4 0
4 R5 X3 T2 1

Above is an example that produces the desired result, however I feel as if there should be a way of doing this in tidyverse without having to unite the variables. This is something that SAS handles very easily using an ARRAY statement and a DO loop, and I'm hoping R has a good way of handling this.

The real dataframe has many additional variables besides from the "c" fields to search, so a solution that involves searching every column would require subsetting the dataframe to first only contain the variables I want to search, and then joining the data back with the other variables.

You said you don't want to use unite, but it's worth noting that passing the argument remove = FALSE has unite create a column of the united variables leaving the others intact. Might be convenient in this case. — camille
– camille, Commented Apr 22, 2019 at 14:55
Yes, it is convenient. And it does work. I just feel like there may be a simpler approach I'm missing that doesn't need to create a united variable. — patward5656
– patward5656, Commented Apr 22, 2019 at 15:06

Ronak Shah · Accepted Answer · 2019-04-22 14:50:27Z

6

Using base R, we can use sapply and use grepl to find pattern in every column and assign 1 to rows where there is more than 0 matches.

df$indicator <- as.integer(rowSums(sapply(df, grepl, pattern = code_regex)) > 0)

df
#  c1 c2 c3 indicator
#1 T1 R4 C5         1
#2 X1 C6 C2         0
#3 T6 C7 X4         0
#4 R5 X3 T2         1

If there are few other columns and we are interested to apply it only for columns which start with "c" we can use grep to filter them.

cols <- grep("^c", names(df))
as.integer(rowSums(sapply(df[cols], grepl, pattern = code_regex)) > 0)

Using dplyr we can do

library(dplyr)

df$indicator <- as.integer(df %>%
              mutate_at(vars(c1:c3), ~grepl(code_regex, .)) %>%
              rowSums() > 0)

edited Apr 22, 2019 at 14:50

answered Apr 22, 2019 at 14:27

Ronak Shah

391k20 gold badges173 silver badges237 bronze badges

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

3 Comments

patward5656 Over a year ago

This is a good solution, but in the real data there are additional variables that I do not want to pattern search, so this would require me to index the dataframe to include only the columns I want to search first. Will edit my original post to include this information.

patward5656 Over a year ago

The purr solution looks like what I was looking for--one line of code that doesn't involve uniting the variables.

Ronak Shah Over a year ago

@patward5656 I think the purrr solution would not give you the expected output. I changed it to use mutate_at which should work on range of columns. Moreover, you can use column numbers directly in cols for sapply ., say columns 3:5 or 1:3 to find pattern in those column.

akrun · Accepted Answer · 2019-04-22 15:09:07Z

3

We can use tidyverse

library(tidyverse)
df %>%
    mutate_all(str_detect, pattern = code_regex) %>%
    reduce(`+`) %>% 
    mutate(df, indicator = .)
#  c1 c2 c3 indicator
#1 T1 R4 C5         1
#2 X1 C6 C2         0
#3 T6 C7 X4         0
#4 R5 X3 T2         1

Or using base R

Reduce(`+`, lapply(df, grepl, pattern = code_regex))
#[1] 1 0 0 1

answered Apr 22, 2019 at 15:09

akrun

891k38 gold badges590 silver badges700 bronze badges

7 Comments

patward5656 Over a year ago

This tidyverse solution seems to only work in the scenario where all of the columns are being searched. I have other variables in my real dataset, and when using it for that the output is all NA. Does this have something to do with the reduce function?

akrun Over a year ago

@patward5656 That is an easy fix. df %>% mutate_at(vars(starts_with("c")), str_detect, pattern = code_regex) %>% reduce("+") %>% mutate(df, indicator = .)

patward5656 Over a year ago

c1<-c("T1", "X1", "T6", "R5") c2<-c("R4", "C6", "C7", "X3") c3<-c("C5", "C2", "X4", "T2") z1<-c("C5", "C2", "X4", "T2")  df<-data.frame(c1, c2, c3, z1)  df %>%   mutate_at(vars(starts_with("c")), str_detect, pattern = code_regex) %>%   reduce(

+

) %>%    mutate(df, indicator = .)    c1 c2 c3 z1 indicator 1 T1 R4 C5 C5        NA 2 X1 C6 C2 C2        NA 3 T6 C7 X4 X4        NA 4 R5 X3 T2 T2        NA Warning message: In Ops.factor(.x, .y) : ‘+’ not meaningful for factors

This produced NAs, it seems.

akrun Over a year ago

@patward5656 I would use transmute_at instead of mutate_at df %>% transmute_at(vars(starts_with("c")), str_detect, pattern = code_regex) %>% reduce(+)

patward5656 Over a year ago

Thanks. I believe transmute_at() solves it perfectly.

|

nsinghphd · Accepted Answer · 2019-04-22 15:13:50Z

1

Base R with apply

apply(df[cols], 1, function(x) sum(grepl(code_regex, x)))
# [1] 1 0 0 1

answered Apr 22, 2019 at 15:13

nsinghphd

2,02216 silver badges25 bronze badges

Collectives™ on Stack Overflow

Creating one variable from a list of variables in R?

3 Answers 3

3 Comments

7 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

3 Comments

7 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related