Selecting data frame rows based on partial string match in a column

Question

I want to select rows from a data frame based on partial match of a string in a column  e g  column  x  contains the string  quot hsa quot   Using sqldf - if it had a like syntax - I would do something like  select   from  lt  gt  where x like  hsa   Unfortunately  sqldf does not support that syntax  Or similarly  selectedRows  lt - df    df x  like   quot hsa- quot    Which of course doesn t work  Can somebody please help me with this

User · Answer

I notice that you mention a function  like  in your current approach  I don t know if that s a reference to the  like  from  data table   but if it is  you can definitely use it as follows   Note that the object does not have to be a data table  but also remember that subsetting approaches for data frames and data tables are not identical    library data table  mtcars rownames mtcars   like   Merc     iris iris Species  like   osa       If that is what you had  then perhaps you had just mixed up row and column positions for subsetting data     If you don t want to load a package  you can try using grep   to search for the string you re matching  Here s an example with the mtcars dataset  where we are matching all rows where the row names includes  Merc    mtcars grep  Merc   rownames mtcars                   mpg cyl  disp  hp drat   wt qsec vs am gear carb   Merc 240D   24 4   4 146 7  62 3 69 3 19 20 0  1  0    4    2   Merc 230    22 8   4 140 8  95 3 92 3 15 22 9  1  0    4    2   Merc 280    19 2   6 167 6 123 3 92 3 44 18 3  1  0    4    4   Merc 280C   17 8   6 167 6 123 3 92 3 44 18 9  1  0    4    4   Merc 450SE  16 4   8 275 8 180 3 07 4 07 17 4  0  0    3    3   Merc 450SL  17 3   8 275 8 180 3 07 3 73 17 6  0  0    3    3   Merc 450SLC 15 2   8 275 8 180 3 07 3 78 18 0  0  0    3    3   And  another example  using the iris dataset searching for the string osa   irisSubset  lt - iris grep  osa   iris Species     head irisSubset      Sepal Length Sepal Width Petal Length Petal Width Species   1          5 1         3 5          1 4         0 2  setosa   2          4 9         3 0          1 4         0 2  setosa   3          4 7         3 2          1 3         0 2  setosa   4          4 6         3 1          1 5         0 2  setosa   5          5 0         3 6          1 4         0 2  setosa   6          5 4         3 9          1 7         0 4  setosa   For your problem try   selectedRows  lt - conservedData grep  hsa-   conservedData miRNA

User · Answer

Another option would be to simply use grepl function  df grepl  er   df name     CO2 grepl  non   CO2 Treatment      df  lt - data frame name   c  bob   robert   peter                     id   c 1 2 3                        name id   2 robert  2   3  peter  3

User · Answer

Try str detect   from the stringr package  which detects the presence or absence of a pattern in a string   Here is an approach that also incorporates the   gt   pipe and filter   from the dplyr package   library stringr  library dplyr   CO2   gt     filter str detect Treatment   non        Plant        Type  Treatment conc uptake 1    Qn1      Quebec nonchilled   95   16 0 2    Qn1      Quebec nonchilled  175   30 4 3    Qn1      Quebec nonchilled  250   34 8 4    Qn1      Quebec nonchilled  350   37 2 5    Qn1      Quebec nonchilled  500   35 3       This filters the sample CO2 data set  that comes with R  for rows where the Treatment variable contains the substring  non    You can adjust whether str detect finds fixed matches or uses a regex - see the documentation for the stringr package

User · Answer

LIKE should work in sqlite   require sqldf  df  lt - data frame name   c  bob   robert   peter   id c 1 2 3   sqldf  select   from df where name LIKE   er         name id 1 robert  2 2  peter  3

[r] Selecting data frame rows based on partial string match in a column

Examples related to r

Examples related to regex

Examples related to string

Examples related to match

Examples related to subset