How to restrict "grepl" function to only search for a string in one column?

user_14144637 0
Viewed 123

I'm working with a very large dataset and trying to pull a small string from only one column (variable) out of 47 using the grepl function. Here's the code I used,

x<-ebd_whbnut_relJun.2020[grepl("migrat", ebd_whbnut_relJun.2020[["SPECIES.COMMENTS"]]),]

in which I'm trying to extract any mention of the string "migrat" from only the column "SPECIES.COMMENTS". "ebd_whbnut_relJun.2020" is the file name.

I ran the code and received 49 entries, but it came back as "49 obs. of 47 variables". Some entries contained just entries from the correct column, but others apparently contained information from numerous other columns. Because of this, I was unable to export the data to Excel and given the error message, "Error in libxlsxwriter: 'String exceeds Excel's limit of 32,767 characters.'"

Is this too primitive and broad a use of the grepl function, or is it something as simple as a missed comma/bracket?

grepl r string
1 Answers

I'll demonstrate some techniques using the diamonds dataset from ggplot2 (though the package is otherwise not required).

data("diamonds", package = "ggplot2")
dat <- as.data.frame(head(diamonds))
dat
#   carat       cut color clarity depth table price    x    y    z
# 1  0.23     Ideal     E     SI2  61.5    55   326 3.95 3.98 2.43
# 2  0.21   Premium     E     SI1  59.8    61   326 3.89 3.84 2.31
# 3  0.23      Good     E     VS1  56.9    65   327 4.05 4.07 2.31
# 4  0.29   Premium     I     VS2  62.4    58   334 4.20 4.23 2.63
# 5  0.31      Good     J     SI2  63.3    58   335 4.34 4.35 2.75
# 6  0.24 Very Good     J    VVS2  62.8    57   336 3.94 3.96 2.48
grep("Good", dat$cut, value = TRUE)
# [1] "Good"      "Good"      "Very Good"
dat$cut[ grepl("Good", dat$cut) ]
# [1] Good      Good      Very Good
# Levels: Fair < Good < Very Good < Premium < Ideal
dat[ grepl("Good", dat$cut), "cut" ]
# [1] Good      Good      Very Good
# Levels: Fair < Good < Very Good < Premium < Ideal

Note that if you're using tbl_df or data.table, then the column-selection behaves a little differently:

as_tibble(dat)[ grepl("Good", dat$cut), "cut" ]
# # A tibble: 3 x 1
#   cut      
#   <ord>    
# 1 Good     
# 2 Good     
# 3 Very Good
as.data.table(dat)[ grepl("Good", dat$cut), "cut" ]
#          cut
# 1:      Good
# 2:      Good
# 3: Very Good

And in fact you can mimic this in base R, too, in a way that also suggests a fix:

dat[ grepl("Good", dat$cut), "cut", drop = FALSE ]
#         cut
# 3      Good
# 5      Good
# 6 Very Good
as_tibble(dat)[ grepl("Good", dat$cut), "cut", drop = TRUE ]
# [1] Good      Good      Very Good
# Levels: Fair < Good < Very Good < Premium < Ideal

data.table is a little different, but if you're trying it then you already know that:

as.data.table(dat)[ grepl("Good", cut), cut ]
# [1] Good      Good      Very Good
# Levels: Fair < Good < Very Good < Premium < Ideal

The data, in case you don't have ggplot2 around:

structure(list(carat = c(0.23, 0.21, 0.23, 0.29, 0.31, 0.24), 
    cut = structure(c(5L, 4L, 2L, 4L, 2L, 3L), .Label = c("Fair", 
    "Good", "Very Good", "Premium", "Ideal"), class = c("ordered", 
    "factor")), color = structure(c(2L, 2L, 2L, 6L, 7L, 7L), .Label = c("D", 
    "E", "F", "G", "H", "I", "J"), class = c("ordered", "factor"
    )), clarity = structure(c(2L, 3L, 5L, 4L, 2L, 6L), .Label = c("I1", 
    "SI2", "SI1", "VS2", "VS1", "VVS2", "VVS1", "IF"), class = c("ordered", 
    "factor")), depth = c(61.5, 59.8, 56.9, 62.4, 63.3, 62.8), 
    table = c(55, 61, 65, 58, 58, 57), price = c(326L, 326L, 
    327L, 334L, 335L, 336L), x = c(3.95, 3.89, 4.05, 4.2, 4.34, 
    3.94), y = c(3.98, 3.84, 4.07, 4.23, 4.35, 3.96), z = c(2.43, 
    2.31, 2.31, 2.63, 2.75, 2.48)), row.names = c(NA, -6L), class = "data.frame")
user_33582720
Related