Selects the row in the data frame that contains a specific value

Question

I have a data.frame like list.1,I want to select the row from list.1, which contain the value of c(101,102,103,104), to create a new data.frame list.2.
list.1

#  ID      col1       col2     col3
#1 1       101        102      201
#2 2       201        202      203
#3 3       104        NA       301
#4 4       101        NA        NA
#4 5       201        301      302

The result I want is:

list.2

#  ID     col1       col2     col3
#1 1       101        102      201
#2 3       104        NA       301
#3 4       101        NA        NA

Then the next I want is: Only contain the value of c(101,102,103,104) from list.2 ,but all put in one column
list.3

#  ID     col1       
#1 1       101
#2 1       102
#3 3       104
#4 4       101

Many Thanks!

Reshape from [wide-to-long](https://stackoverflow.com/questions/2185252/reshaping-data-frame-from-wide-to-long-format) then filter. Possible duplicate: https://stackoverflow.com/questions/2185252/reshaping-data-frame-from-wide-to-long-format — zx8754, Nov 26 '19 at 12:14

SSD93 · Answer 1 · 2019-11-26T12:49:01.117

You can solve the first step by usin filter() from dplyr library and using "|" as OR operator:

library(dplyr)
list.2<-list.1%>%
  filter(col1 %in% (101:104)| col2 %in% (101:104) | col3 %in% (101:104))

The second step can be done by select() from dplyr library and filter, and then using rbind(), it' important that all columns have the same name so yo have to change them manually, the code.

c1<-list.1%>%
  select(c(ID,col1))%>%
  filter(col1 %in% (101:104))

c2<-list.1%>%
  select(c(ID,col2))%>%
  filter(col2 %in% (101:104))
names(c2)<-c("ID","col1")

c3<-list.1%>%
  select(c(ID,col3))%>%
  filter(col3 %in% (101:104))
names(c3)<-c("ID","col1")

list.3<-rbind(c1,c2,c3)

If you're using the same filtering conditions for all 2 columns, you can just use `filter_at` — camille, Nov 26 '19 at 14:34

Roccer · Answer 2 · 2019-11-26T16:57:55.383

I figured out the solution is far too complicated. I would go for the the filter_at approach mentioned in the other answer. Anyhow I leave this answer here for the pivot_longer stuff.

In case you have many more columns and values you might try something like:

library(tidyverse)

get_rows <- function(x) any(x %in% c(101, 102, 103, 104))

list_2 <- list_1[apply(list_1[-1], 1, get_rows), ]

results in

# A tibble: 3 x 4
     ID  col1  col2  col3
  <dbl> <dbl> <dbl> <dbl>
1     1   101   102   201
2     3   104    NA   301
3     4   101    NA    NA

then

list_3 <- pivot_longer(list_2, cols = col1:col3, names_to = "cols", values_to = "value")

filter(list_3, value %in% c(101, 102, 103, 104))

would get you

# A tibble: 4 x 3
     ID cols  value
  <dbl> <chr> <dbl>
1     1 col1    101
2     1 col2    102
3     3 col1    104
4     4 col1    101

or a bit condensed

list_1 %>% 
  filter(pmap_lgl(select(., starts_with("col")), ~any(c(...) %in% c(101:104)))) %>% 
  pivot_longer(cols = col1:col3, names_to = "cols", values_to = "value") %>% 
  filter(value %in% c(101:104))

Selects the row in the data frame that contains a specific value

2 Answers2