# DataFrames: obtaining the subset of rows by a set of values
**URL:** https://discourse.julialang.org/t/dataframes-obtaining-the-subset-of-rows-by-a-set-of-values/15923
**Category:** New to Julia
**Tags:** dataframes
**Created:** [October 5, 2018, 1:14pm UTC](https://discourse.julialang.org/t/dataframes-obtaining-the-subset-of-rows-by-a-set-of-values/15923 "2018-10-05T13:14:50Z")
**Posts on this page:** 20
**Page:** 1
### Author:  [@Luis\_de\_Sousa](https://discourse.julialang.org/u/Luis_de_Sousa)
#### Post date: [October 5, 2018, 1:14pm UTC](https://discourse.julialang.org/t/dataframes-obtaining-the-subset-of-rows-by-a-set-of-values/15923/1 "2018-10-05T13:14:50Z")
Hi everyone,
I would like to perform an operation similar to the SQL `SELECT * FROM table WHERE col IN ("1","2","3")` on a DataFrame. I have an array with a few strings that I wish to use in this selection. From what I have read this could be obtained with the `occursin` function, however, this is what happens when I try to use it:
```julia
julia> occursin(selection, df)
ERROR: MethodError: no method matching occursin(::Array{Union{Missing, String},1}, ::Array{Union{Missing, String},1})
Stacktrace:
[1] top-level scope at none:0
```
Note that there are no `NA` or `nothing` values in the `selection` array. I have two questions:
1. Is `occursin` the right way to this?
2. If yes, how can the MethodError message be addressed?
Thank you.
---
### Author:  [@ExpandingMan](https://discourse.julialang.org/u/ExpandingMan)
#### Post date: [October 5, 2018, 1:33pm UTC](https://discourse.julialang.org/t/dataframes-obtaining-the-subset-of-rows-by-a-set-of-values/15923/2 "2018-10-05T13:33:59Z")
Normally I’d do
```julia
filter(row -> row.col ∈ [1,2,3], df)
```
You could also do
```julia
df[∈([1,2,3]).(df.col), :]
```
If you prefer SQL style statements check out [Query.jl](https://github.com/queryverse/Query.jl) or [DataFramesMeta.jl](https://github.com/JuliaData/DataFramesMeta.jl), but, as you see above, simply using `Base` and basic DataFrames functions is quite nice.
Re-reading your statement, I’m a little confused about whether I had the use case right… is your column `String` valued? In that case, you could do the above with `["1", "2", "3"]`.
---
### Author:  [@Ajaychat3](https://discourse.julialang.org/u/Ajaychat3)
#### Post date: [October 5, 2018, 1:37pm UTC](https://discourse.julialang.org/t/dataframes-obtaining-the-subset-of-rows-by-a-set-of-values/15923/3 "2018-10-05T13:37:13Z")
I think you can use dataframesmeta package and apply the following commands the way they suit your needs.
Select row subsets.
@where(df, :x .\> 1)
@where(df, :x .\> x)
@where(df, :x .\> x, :y .== 3) # the two expressions are “and-ed”
---
### Author:  [@Luis\_de\_Sousa](https://discourse.julialang.org/u/Luis_de_Sousa)
#### Post date: [October 5, 2018, 1:45pm UTC](https://discourse.julialang.org/t/dataframes-obtaining-the-subset-of-rows-by-a-set-of-values/15923/4 "2018-10-05T13:45:47Z")
Hi ExpandingMan,
you are correct, the column is of type `String`, as is the array with the selection values. I tried the methods your propose, both return empty DataFrames.
Thank you.
---
### Author:  [@Luis\_de\_Sousa](https://discourse.julialang.org/u/Luis_de_Sousa)
#### Post date: [October 5, 2018, 1:47pm UTC](https://discourse.julialang.org/t/dataframes-obtaining-the-subset-of-rows-by-a-set-of-values/15923/5 "2018-10-05T13:47:13Z")
Dear Ajay,
Is there a way of applying the `@where` function with an `∈` type of operator?
Thank you.
---
### Author:  [@ExpandingMan](https://discourse.julialang.org/u/ExpandingMan)
#### Post date: [October 5, 2018, 2:03pm UTC](https://discourse.julialang.org/t/dataframes-obtaining-the-subset-of-rows-by-a-set-of-values/15923/6 "2018-10-05T14:03:09Z")
Ah, is this:
```julia
filter(r -> any(occursin.(["1", "2", "3"], r.col)), df)
```
what you’re looking for?
---
### Author:  [@Ajaychat3](https://discourse.julialang.org/u/Ajaychat3)
#### Post date: [October 5, 2018, 4:41pm UTC](https://discourse.julialang.org/t/dataframes-obtaining-the-subset-of-rows-by-a-set-of-values/15923/7 "2018-10-05T16:41:33Z")
Z@Luis\_de\_Sousa: @where is to filter a subset of dataframe columns based on certain conditions. However, you can use any of the two commnads to extract a subset of columns
```julia
flds=map(e->e∈[:x1,:x2,:x3,:x4],names(features))
1. features[flds] Or
2. @select(features,flds)
```
I hope this meets your requirements.
Edit:
You can also use column names directly in place of ‘flds’
---
### Author:  [@Ajaychat3](https://discourse.julialang.org/u/Ajaychat3)
#### Post date: [October 5, 2018, 4:45pm UTC](https://discourse.julialang.org/t/dataframes-obtaining-the-subset-of-rows-by-a-set-of-values/15923/8 "2018-10-05T16:45:04Z")
You can also use columns nos in place of 'flds" e.g. [1,3,5]
---
### Author:  [@davidanthoff](https://discourse.julialang.org/u/davidanthoff)
#### Post date: [October 5, 2018, 6:35pm UTC](https://discourse.julialang.org/t/dataframes-obtaining-the-subset-of-rows-by-a-set-of-values/15923/9 "2018-10-05T18:35:37Z")
Here is the [Query.jl](https://github.com/queryverse/Query.jl) way of doing this (if I understand the question correctly):
```julia
using Queryverse
df = # Get your DataFrame
df |> @filter(_.colA ∈ ["stringA", "stringB", "stringC"]) |> DataFrame
```
---
### Author:  [@nalimilan](https://discourse.julialang.org/u/nalimilan)
#### Post date: [October 5, 2018, 8:08pm UTC](https://discourse.julialang.org/t/dataframes-obtaining-the-subset-of-rows-by-a-set-of-values/15923/10 "2018-10-05T20:08:59Z")
Another (probably faster) way of doing this is `df[findall(in(["stringA", "stringB", "stringC"]), df.col), :]`.
Note that `occursin` is meant to look for substrings inside a string. So that’s quite different.
---
### Author:  [@Ajaychat3](https://discourse.julialang.org/u/Ajaychat3)
#### Post date: [October 6, 2018, 1:20am UTC](https://discourse.julialang.org/t/dataframes-obtaining-the-subset-of-rows-by-a-set-of-values/15923/11 "2018-10-06T01:20:22Z")
> [@Luis\_de\_Sousa](#):
>
> Is there a way of applying the `@where` function with an `∈` type of operator?
I hope this is what you are looking for.
```julia
@where(features,map(x->x ∈ [60,55],features.Age))[1:5]
```
The suffix [1:5] is for the columns nos to select for output.
---
### Author:  [@pdeffebach](https://discourse.julialang.org/u/pdeffebach)
#### Post date: [October 6, 2018, 2:26am UTC](https://discourse.julialang.org/t/dataframes-obtaining-the-subset-of-rows-by-a-set-of-values/15923/12 "2018-10-06T02:26:15Z")
DataFramesMeta lets you do this:
```julia
@linq df |>
where(:a .∈ Ref([1,2,5,7]))
```
Key thing here is the `Ref` for the broadcasted `in`. I suppose thats the most reasonable behavior but its a bit unexpected.
---
### Author:  [@Liso](https://discourse.julialang.org/u/Liso)
#### Post date: [October 6, 2018, 9:08am UTC](https://discourse.julialang.org/t/dataframes-obtaining-the-subset-of-rows-by-a-set-of-values/15923/13 "2018-10-06T09:08:38Z")
You could do:
```julia
@where(df, :col .∈ [[1,2,3]])
```
The trick is putting array into array to apply dot operator on whole array.
---
### Author:  [@Luis\_de\_Sousa](https://discourse.julialang.org/u/Luis_de_Sousa)
#### Post date: [October 9, 2018, 9:23am UTC](https://discourse.julialang.org/t/dataframes-obtaining-the-subset-of-rows-by-a-set-of-values/15923/14 "2018-10-09T09:23:49Z")
Hi again ExpandingMan,
This last formulation returns an error:
```julia
ERROR: MethodError: no method matching filter(::getfield(Main, Symbol("##19#20")))
Closest candidates are:
filter(::Any, ::Array{T,1} where T) at array.jl:2352
filter(::Any, ::BitArray) at bitarray.jl:1637
filter(::Any, ::AbstractArray) at array.jl:2313
...
Stacktrace:
[1] top-level scope at none:0
```
Thank you.
---
### Author:  [@Luis\_de\_Sousa](https://discourse.julialang.org/u/Luis_de_Sousa)
#### Post date: [October 9, 2018, 9:27am UTC](https://discourse.julialang.org/t/dataframes-obtaining-the-subset-of-rows-by-a-set-of-values/15923/15 "2018-10-09T09:27:06Z")
Hello again,
This also returns an empty DataFrame. I suspect the type of the array with the selection values (Array{Union{Missing, String}) is causing some issue. I will try to get to a minimum workable example.
Thank you.
---
### Author:  [@Luis\_de\_Sousa](https://discourse.julialang.org/u/Luis_de_Sousa)
#### Post date: [October 9, 2018, 11:15am UTC](https://discourse.julialang.org/t/dataframes-obtaining-the-subset-of-rows-by-a-set-of-values/15923/16 "2018-10-09T11:15:51Z")
Hi David,
Unfortunately, Queryverse is failing to install on the Julia version I use (1.0.0). So I can not try this suggestion.
Thank you.
---
### Author:  [@ExpandingMan](https://discourse.julialang.org/u/ExpandingMan)
#### Post date: [October 9, 2018, 1:30pm UTC](https://discourse.julialang.org/t/dataframes-obtaining-the-subset-of-rows-by-a-set-of-values/15923/17 "2018-10-09T13:30:10Z")
The following code should not return any error:
```julia
using DataFrames
df = DataFrame(col=rand(["something1", "something2", "something4"], 100), x=rand(100))
tdf = filter(r -> any(occursin.(["1", "2", "3"], r.col)), df)
```
and should return a non-empty dataframe (unless you are extremely unlucky! 😆) with only entries where `col` is `"something1"` or `"something2"`.
---
### Author:  [@davidanthoff](https://discourse.julialang.org/u/davidanthoff)
#### Post date: [October 9, 2018, 2:16pm UTC](https://discourse.julialang.org/t/dataframes-obtaining-the-subset-of-rows-by-a-set-of-values/15923/18 "2018-10-09T14:16:45Z")
Could you open an issue about the Queryverse install issue? I’d like to fix whatever the problem is 🙂
---
### Author:  [@Luis\_de\_Sousa](https://discourse.julialang.org/u/Luis_de_Sousa)
#### Post date: [October 10, 2018, 2:52pm UTC](https://discourse.julialang.org/t/dataframes-obtaining-the-subset-of-rows-by-a-set-of-values/15923/19 "2018-10-10T14:52:47Z")
This is now [issue #11](https://github.com/queryverse/Queryverse.jl/issues/11).
---
### Author:  [@rvasil](https://discourse.julialang.org/u/rvasil)
#### Post date: [October 16, 2018, 3:48pm UTC](https://discourse.julialang.org/t/dataframes-obtaining-the-subset-of-rows-by-a-set-of-values/15923/20 "2018-10-16T15:48:26Z")
Btw, my experience shows that `filter()` is several times slower than `@where` macro from DataFramesMeta or plain DataFrames filters - on significant datasets.
I’m adding few `in` and `not in` queries - notice the time and memory usage from `filter()` function:
(this is from Julia 1.0.1)
```julia
julia> @time df = DataFrame(col=rand(["something1", "something2", "something3", "something4"], 10000000), x=rand(10000000));
0.238002 seconds (54 allocations: 152.591 MiB, 25.23% gc time)
julia> @time tdf = filter(r -> in(r.col, ["something1", "something2"]), df); size(tdf)
3.934676 seconds (85.04 M allocations: 2.694 GiB, 20.75% gc time)
(4999142, 2)
julia> @time tdf = @where df (in.(:col, [["something1", "something2"]])); size(tdf)
0.441025 seconds (43.80 k allocations: 79.399 MiB, 2.83% gc time)
(4999142, 2)
julia> @time tdf = df[in(["something1", "something2"]).(df.col), :]; size(tdf)
0.396203 seconds (41 allocations: 77.479 MiB, 5.39% gc time)
(4999142, 2)
julia> @time tdf = @where df .!(:col .∈ [["something1", "something2"]]); size(tdf)
0.490810 seconds (45.49 k allocations: 79.496 MiB, 10.09% gc time)
(5000858, 2)
julia> @time tdf = @where df .!(in.(:col, [["something1", "something2"]])); size(tdf)
0.607302 seconds (45.49 k allocations: 79.496 MiB, 27.52% gc time)
(5000858, 2)
```
[Next page](https://discourse.julialang.org/t/dataframes-obtaining-the-subset-of-rows-by-a-set-of-values/15923.md?page=2)