All geek questions in one place

Select rows from a database on multi-column conditions

library(mlbench) data("HouseVotes84") head(HouseVotes84) df=HouseVotes84[complete.cases(HouseVotes84),] df[df$Class=='democrat'& df$V1=='y' & df$V2=='n',]

what if I want to filter many variables without having to write as much. For example, if I would like to write ('democrat','y','n', , ,'y','n', ...) as a specification where I can omit some of the variables. Is there any convenient way to do this? I tried

 paste0('df$',names(df),'==',c('democrat','y'))

and then

 df[paste0('df$',names(df),'==',c('democrat','y')),]

but it does not work

+1
source share
2 answers

One idea is to "misuse" the data.table syntax to select columns with keys (see vignette("datatable-keys-fast-subset", package = "data.table") ).

 library(data.table) dt <- HouseVotes84[complete.cases(HouseVotes84), ] # define variables to save typing N <- "n" Y <- "y" NY <- c(N, Y) D <- "democrat" R <- "republican" DR <- c(D, R) setkeyv(dt, names(dt)) key(dt) # [1] "Class" "V1" "V2" "V3" "V4" "V5" "V6" "V7" "V8" "V9" "V10" "V11" "V12" #[14] "V13" "V14" "V15" "V16" dt[CJ(R, Y, N, NY, NY, Y, N), nomatch = 0] # Class V1 V2 V3 V4 V5 V6 V7 V8 V9 V10 V11 V12 V13 V14 V15 V16 #1: republican ynnyynynnynnnyyy #2: republican ynnyynyyynnyyyny #3: republican ynyyynynyynnyyny dt[CJ(D, Y, N, NY, NY, Y), nomatch = 0] # Class V1 V2 V3 V4 V5 V6 V7 V8 V9 V10 V11 V12 V13 V14 V15 V16 #1: democrat ynnnyynnnnyynyny #2: democrat ynnnyyynnyynnyny #3: democrat ynynyynnnnnnnnny #4: democrat ynynyynnyynnyyny #5: democrat ynynyyynnnynnyny dt[CJ(DR, Y, N, NY, NY, Y), nomatch = 0] # Class V1 V2 V3 V4 V5 V6 V7 V8 V9 V10 V11 V12 V13 V14 V15 V16 # 1: democrat ynnnyynnnnyynyny # 2: democrat ynnnyyynnyynnyny # 3: democrat ynynyynnnnnnnnny # 4: democrat ynynyynnyynnyyny # 5: democrat ynynyyynnnynnyny # 6: republican ynnyynynnynnnyyy # 7: republican ynnyynyyynnyyyny # 8: republican ynnyyynnnnyyyynn # 9: republican ynnyyynnnynyyynn #10: republican ynyyynynyynnyyny #11: republican ynyyyyyynynynyyy #12: republican ynyyyyyynyyynyyy
+1
source

We can use the power of regex and filter in dplyr .

Assuming you have a filter variable consisting of n elements, where n also the number of columns of the target df dataframe.

Each element of the vector will filter the corresponding columns, and NA in the vector will take any value:

For instance:

 filter <- c('democrat, 'y', 'y', NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA)

will give as a result to all Democrats who voted y in the first two, and everything else.

 filter <- c('republican', 'y', 'n', NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA) filter <- toString(filter) %>% gsub(pattern = 'NA', x = ., replacement = '.+') df %>% unite(string, Class:V16, sep = ', ', remove = F) %>% filter(grepl(x = string, pattern = filter)) %>% select(-string) # Class V1 V2 V3 V4 V5 V6 V7 V8 V9 V10 V11 V12 V13 V14 V15 V16 # 1 republican ynnyynyyynnyyyny # 2 republican ynyyynynyynnyyny # 3 republican ynyyyyyynynynyyy # 4 republican ynyyyyyynyyynyyy # 5 republican ynnnnnyyyynnnyny
0
source

Source: https://habr.com/ru/post/1264867/

More articles:


All Articles