Table of Contents
To select rows where a particular value appears in any column, use the SELECT statement with the WHERE condition and the operator IN, followed by the value you’re looking for. This will return all rows that contain the value in any column. Alternatively, you could use the OR operator to list all columns in which the value could appear. Both options will return the same result.
You can use the following basic syntax to find the rows of a data frame in R in which a certain value appears in any of the columns:
library(dplyr) df %>% filter_all(any_vars(. %in% c('value1', 'value2', ...)))
The following examples show how to use this syntax in practice.
Example 1: Find Value in Any Column
Suppose we have the following data frame in R:
#define data frame df = data.frame(points=c(25, 12, 15, 14, 19), assists=c(5, 7, 7, 9, 12), rebounds=c(11, 8, 10, 6, 6)) #view data frame df points assists rebounds 1 25 5 11 2 12 7 8 3 15 7 10 4 14 9 6 5 19 12 6
The following syntax shows how to select all rows of the data frame that contain the value 25 in any of the columns:
library(dplyr) #select rows where 25 appears in any column df %>% filter_all(any_vars(. %in% c(25))) points assists rebounds 1 25 5 11
There is exactly one row where the value 25 appears in any column.
The following syntax shows how to select all rows of the data frame that contain the values 25, 9, or 6 in any of the columns:
library(dplyr) #select rows where 25, 9, or 6 appears in any column df %>% filter_all(any_vars(. %in% c(25, 9, 6))) points assists rebounds 1 25 5 11 2 14 9 6 3 19 12 6
Example 2: Find Character in Any Column
Suppose we have the following data frame in R:
#define data frame df = data.frame(points=c(25, 12, 15, 14, 19), assists=c(5, 7, 7, 9, 12), position=c('G', 'G', 'F', 'F', 'C')) #view data frame df points assists position 1 25 5 G 2 12 7 G 3 15 7 F 4 14 9 F 5 19 12 C
The following syntax shows how to select all rows of the data frame that contain the character G in any of the columns:
library(dplyr) df %>% filter_all(any_vars(. %in% c('G'))) points assists position 1 25 5 G 2 12 7 G
There are two rows where the character ‘G’ appears in any column.
library(dplyr) df %>% filter_all(any_vars(. %in% c('G', 'C'))) points assists position 1 25 5 G 2 12 7 G 3 19 12 C
The following tutorials explain how to perform other common functions in R: