CODEWITHSUNDEEP
rdata.tabledplyrsqldf
Sharath
Sharath

Reputation: 2267

Use both LIKE and NOT LIKE in the same query

I have a df like this 

Category <- c('D_L','D_R','FA1','LBP0W','L-010','L-020','LW_-010','LWA_PT_035','LWA_PT_055','RBP0W','RET_MAG','R-010','R-000','RWA_PT_035','RWA_PT_055','TPH')
ID <- c(111,112,113,114,115,116,117,118,119,120,121,122,123,124,125,126)
df <- data.frame(ID,Category)
df

    ID   Category
1  111        D_L
2  112        D_R
3  113        FA1
4  114      LBP0W
5  115      L-010
6  116      L-020
7  117    LW_-010
8  118 LWA_PT_035
9  119 LWA_PT_055
10 120      RBP0W
11 121    RET_MAG
12 122      R-010
13 123      R-000
14 124 RWA_PT_035
15 125 RWA_PT_055
16 126        TPH

I am using sqldf to filter my dataset into 2 categories. 

df_R <- sqldf("SELECT * FROM df 
                  WHERE Category NOT LIKE '%_L'
                  AND Category NOT LIKE 'LW_%'
                  AND category NOT LIKE 'L-%'
                  AND category NOT LIKE 'LB%'")

df_L <- sqldf("SELECT * FROM df 
              WHERE Category NOT LIKE '%_R'
              AND Category NOT LIKE 'RW_%'
              AND category NOT LIKE 'R-%'
              AND category NOT LIKE 'RB%'")

Here I get 2 data frames. Challenge is that:

1) for df_R - I need to return "RWA_PT_035" & not "RWA_PT_055" category 2) for df_L - I need to return "LWA_PT_035" & not "LWA_PT_055" category

and hence when I try to do this 

df_R <- sqldf("SELECT * FROM df 
                      WHERE Category NOT LIKE '%_L'
                      AND Category NOT LIKE 'LW_%'
                      AND Category NOT LIKE 'L-%'
                      AND Category NOT LIKE 'LB%'
                      AND Category LIKE 'RWA_PT_035'")

it returns only 1 observation and that is "RWA_PT_035" for df_R but my desired output is 

   ID   Category
1 112        D_R
2 113        FA1
3 120      RBP0W
4 121    RET_MAG
5 122      R-010
6 123      R-000
7 124 RWA_PT_035
8 126        TPH

and for df_L 

    ID   Category
1  111        D_L
2  113        FA1
3  114      LBP0W
4  115      L-010
5  116      L-020
6  117    LW_-010
7  118 LWA_PT_035
8  121    RET_MAG
9 126        TPH

I would like to know if I could use "LIKE" and "NOT LIKE" in a query at the same time like the above? or if there is any other way to do this?

I am also open to other methods like data.table or dplyr instead of sqldf.

Upvotes: 1

Views: 2017

Answers (2)

zx8754
zx8754

Reputation: 56149

To replicate @DavidArenburg solution using sqldf:

#Using @DavidArenburg solution:
res1 <- df[!grepl("_R|RWA_|R-|RB|_PT_055", df$Category),]

#Using sqldf
library(sqldf)
res2 <- sqldf("SELECT * FROM df 
               WHERE Category NOT LIKE '%_R' AND
                     Category NOT LIKE 'RWA_%' AND
                     Category NOT LIKE 'R-%' AND
                     Category NOT LIKE 'RB%' AND
                     Category NOT LIKE '%_PT_055'")

# res1 == res2
#      ID Category
# 1  TRUE     TRUE
# 3  TRUE     TRUE
# 4  TRUE     TRUE
# 5  TRUE     TRUE
# 6  TRUE     TRUE
# 7  TRUE     TRUE
# 8  TRUE     TRUE
# 11 TRUE     TRUE
# 16 TRUE     TRUE

Upvotes: 1

Sharath
Sharath

Reputation: 2267

I got the solution from David Arenburg 

df[!grepl("_R|RWA_|R-|RB|_PT_055", df$Category),]

Upvotes: 2

Related Questions