filter data based on multiple values seperated by comma within a column

user_9097114 0
Viewed 99

Need to filter data from a table, where I have an input table as below

Emp_id = c(121,122,191,181,277,333)  
salary = c(7838,8389,8940,9491,10042,10593)  
Country = c("USA","USA","UK","USA","UK","USA")

df = data.frame(Emp_id,salary,Country)

and I have one more single character separated by a comma as below:

Emp_lst = "121,191,181"

Now, I need to filter df rows based on values in Emp_lst. For that, I have tried the code below, but I am getting 0 rows

library(dplyr)
df_flter = df %>% filter(Emp_id %in% Emp_lst)

Any Modification required in above code?

dplyr r
4 Answers

We could use a regex based approach here by converting your CSV list of employee IDs into an alternation:

regex <- paste0("^(?:", gsub(",", "|", Emp_lst, fixed=TRUE), ")$")
df[grepl(regex, df$Emp_id), ]

  Emp_id salary Country
1    121   7838     USA
3    191   8940      UK
4    181   9491     USA

Data:

Emp_id <- c(121,122,191,181,277,333)  
salary <- c(7838,8389,8940,9491,10042,10593)  
Country <- c("USA","USA","UK","USA","UK","USA")
df <- data.frame(Emp_id,salary,Country)
Emp_lst <- "121,191,181"
user_18632290

Change Emp_lst = "121,191,181" to Emp_lst = c(121,191,181)

Emp_id = c(121,122,191,181,277,333)  
salary = c(7838,8389,8940,9491,10042,10593)  
Country = c("USA","USA","UK","USA","UK","USA")

Emp_lst = c(121,191,181)

library(dplyr)
df_flter = df %>% filter(Emp_id %in% Emp_lst)
df_flter

  Emp_id salary Country
1    121   7838     USA
2    191   8940      UK
3    181   9491     USA
user_100433230

You can do so:

  1. unlist Emp_lst with the use of strsplit to have access to all 3 numbers
  2. usiong filter_all as a variation of filter
library(dplyr)
Emp_lst <- unlist(strsplit(Emp_lst,","))
filter_all(df, any_vars(. %in% Emp_lst))

Output:

  Emp_id salary Country
1    121   7838     USA
2    191   8940      UK
3    181   9491     USA
user_133216470

You can split Emp_lst on comma and select rows where Emp_id matches.

Emp_lst = "121,191,181"
subset(df, Emp_id %in% strsplit(Emp_lst, ',\\s*')[[1]])

#  Emp_id salary Country
#1    121   7838     USA
#3    191   8940      UK
#4    181   9491     USA
user_39629140
Related