# How to filter InMemoryDatasets

**URL:** <https://discourse.julialang.org/t/how-to-filter-inmemorydatasets/83478>\
**Category:** General Usage\
**Tags:** question, package, inmemorydatasets\
**Created:** [June 28, 2022, 7:29pm UTC](https://discourse.julialang.org/t/how-to-filter-inmemorydatasets/83478 "2022-06-28T19:29:18Z")\
**Posts on this page:** 8\
**Page:** 1

<div class="post-metadata">

**Author:** ![ufechner7](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/ufechner7/32/51363_2.png) [@ufechner7](https://discourse.julialang.org/u/ufechner7)\
**Post date:** [June 28, 2022, 7:29pm UTC](https://discourse.julialang.org/t/how-to-filter-inmemorydatasets/83478/1 "2022-06-28T19:29:18Z")

</div>

Example:

```julia
using InMemoryDatasets
ds = Dataset(x1 = 1, x2 = 1:10, x3 = repeat(1:2, 5))
res = modify!(ds, :x2 => byrow(isodd) => :ODD)

```

The output is;

```julia
julia> include("test/filter.jl")
10×4 Dataset
 Row │ x1 x2 x3 ODD      
     │ identity identity identity identity 
     │ Int64? Int64? Int64? Bool?    
─────┼────────────────────────────────────────
   1 │ 1 1 1 true
   2 │ 1 2 2 false
   3 │ 1 3 1 true
   4 │ 1 4 2 false
   5 │ 1 5 1 true
   6 │ 1 6 2 false
   7 │ 1 7 1 true
   8 │ 1 8 2 false
   9 │ 1 9 1 true
  10 │ 1 10 2 false

```

How can I filter `res` for all values were ds.ODD == true ?

---

<div class="post-metadata">

**Author:** ![ufechner7](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/ufechner7/32/51363_2.png) [@ufechner7](https://discourse.julialang.org/u/ufechner7)\
**Post date:** [June 28, 2022, 8:04pm UTC](https://discourse.julialang.org/t/how-to-filter-inmemorydatasets/83478/2 "2022-06-28T20:04:26Z")

</div>

Ok, found a solution:

```julia
res[res[!, :ODD] .== true, :]

```

A bit strange (but nice) that I can column numbers or column names as index…

---

<div class="post-metadata">

**Author:** ![rocco\_sprmnt21](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/rocco_sprmnt21/32/20127_2.png) [@rocco\_sprmnt21](https://discourse.julialang.org/u/rocco_sprmnt21)\
**Post date:** [June 29, 2022, 6:38am UTC](https://discourse.julialang.org/t/how-to-filter-inmemorydatasets/83478/3 "2022-06-29T06:38:51Z")

</div>

you ould try one of these way  
[here the related doc](https://docs.juliahub.com/InMemoryDatasets/cS87e/0.6.5/man/filter/)

```julia
julia> filter(ds, [:x2,:x3], by =[>(5),isodd])
2×3 Dataset
 Row │ x1 x2 x3       
     │ identity identity identity
     │ Int64? Int64? Int64?
─────┼──────────────────────────────
   1 │ 1 7 1
   2 │ 1 9 1

julia> filter(ds, [:x2,:x3], type=any, by =[>(5),isodd])
8×3 Dataset
 Row │ x1 x2 x3       
     │ identity identity identity
     │ Int64? Int64? Int64?
─────┼──────────────────────────────
   1 │ 1 1 1
   2 │ 1 3 1
   3 │ 1 5 1
   4 │ 1 6 2
   5 │ 1 7 1
   6 │ 1 8 2
   7 │ 1 9 1
   8 │ 1 10 2

julia> filter(ds, 2:3, type=any, by =[>(5),iseven])
7×3 Dataset
 Row │ x1 x2 x3       
     │ identity identity identity
     │ Int64? Int64? Int64?
─────┼──────────────────────────────
   1 │ 1 2 2
   2 │ 1 4 2
   3 │ 1 6 2
   4 │ 1 7 1
   5 │ 1 8 2
   6 │ 1 9 1
   7 │ 1 10 2

```

---

<div class="post-metadata">

**Author:** ![rocco\_sprmnt21](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/rocco_sprmnt21/32/20127_2.png) [@rocco\_sprmnt21](https://discourse.julialang.org/u/rocco_sprmnt21)\
**Post date:** [June 29, 2022, 9:23am UTC](https://discourse.julialang.org/t/how-to-filter-inmemorydatasets/83478/4 "2022-06-29T09:23:47Z")

</div>

Could the following comparison be of interest to you?  
I don’t know if the result obtained in this specific case is generalizable and also valid for the real cases of your interest.

```julia
using SplitApplyCombine, TypedTables, BenchmarkTools
t = Table(x1 = fill(1,10), x2 = collect(1:10), x3 = repeat(1:2, 5))
@btime filterview(r->r.x2>(5)&&isodd(r.x2),rows(t))

julia> @btime filterview(r->r.x2>(5)&&isodd(r.x2),rows(t))
  429.146 ns (9 allocations: 448 bytes)
Table with 3 columns and 2 rows:
     x1 x2 x3
   ┌───────────
 1 │ 1 7 1
 2 │ 1 9 1

#while the same operation with IMD

julia> @btime filter(ds, [:x2,:x3], by =[>(5),isodd])
  7.050 μs (67 allocations: 5.28 KiB)
2×3 Dataset
 Row │ x1 x2 x3       
     │ identity identity identity
     │ Int64? Int64? Int64?
─────┼──────────────────────────────
   1 │ 1 7 1
   2 │ 1 9 1

# and with DF 

julia> @btime subset(df, :x2=> ByRow(x-> x>5 && isodd(x)) )
  13.400 μs (163 allocations: 8.62 KiB)
2×3 DataFrame
 Row │ x1 x2 x3    
     │ Int64 Int64 Int64
─────┼─────────────────────
   1 │ 1 7 1
   2 │ 1 9 1

```

---

<div class="post-metadata">

**Author:** ![monopolynomial](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/monopolynomial/32/34782_2.png) [@monopolynomial](https://discourse.julialang.org/u/monopolynomial)\
**Post date:** [July 1, 2022, 3:51am UTC](https://discourse.julialang.org/t/how-to-filter-inmemorydatasets/83478/5 "2022-07-01T03:51:48Z")

</div>

filterview returns a view, adding view=true gives better performance

```julia
@btime filter(ds, [:x2,:x3], by =[>(5),isodd],view=true)

```

but I find mapformats more interesting

```julia
ds = Dataset(x1 = 1, x2 = 1:10, x3 = repeat(1:2, 5))
setformat!(ds,:x2=>isodd)
filter(ds,:x2,mapformats=true,view=true)
removeformat!(ds,:x2)

```

I run your benchmark with a little larger data set and the results are different:

```julia
ds = Dataset(x1 = 1, x2 = 1:10, x3 = repeat(1:2, 5))
repeat!(ds,10^5)
t=Table(ds)
@btime filterview(r->r.x2>(5)&&isodd(r.x2),rows(t))
  286.256 ms (5000011 allocations: 169.49 MiB)
@btime filter(ds, [:x2,:x3], by =[>(5),isodd], view = true)
  884.615 μs (125 allocations: 2.60 MiB)

```

---

<div class="post-metadata">

**Author:** ![aplavin](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/aplavin/32/222056_2.png) [@aplavin](https://discourse.julialang.org/u/aplavin)\
**Post date:** [July 1, 2022, 11:04am UTC](https://discourse.julialang.org/t/how-to-filter-inmemorydatasets/83478/6 "2022-07-01T11:04:18Z")

</div>

> [@monopolynomial](#):
>
> ```julia
> @btime filterview(r->r.x2>(5)&&isodd(r.x2),rows(t))
> 286.256 ms (5000011 allocations: 169.49 MiB)
> 
> ```

I get 200 times better performance:

```julia
julia> tbl = (x1 = repeat([1], 10), x2 = 1:10, x3 = repeat(1:2, 5)) |> rowtable
julia> tbl_L = repeat(tbl, 10^5);
julia> using TypedTables
julia> @btime filterview(r->r.x2>(5)&&isodd(r.x3), rows($(Table(tbl_L))));
  1.257 ms (9 allocations: 1.65 MiB)

```

---

<div class="post-metadata">

**Author:** ![rocco\_sprmnt21](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/rocco_sprmnt21/32/20127_2.png) [@rocco\_sprmnt21](https://discourse.julialang.org/u/rocco_sprmnt21)\
**Post date:** [July 1, 2022, 1:14pm UTC](https://discourse.julialang.org/t/how-to-filter-inmemorydatasets/83478/7 "2022-07-01T13:14:56Z")

</div>

> [@monopolynomial](#):
>
> I run your benchmark with a little larger data set and the results are different:

in fact I had already done some tests with bigger tables.

Although I made a wrong comparison in the first place because the conditions in the two queries were different.  
But even “competing” in the same way, filterview comes first, according to my pc

```julia
julia> t = Table(x1 = fill(1,10^5), x2 = collect(1:10^5), x3 = collect(1:2:2*10^5));     

julia> @btime filterview(r->r.x2>(5) && isodd(r.x2),rows(t));
  70.200 μs (11 allocations: 407.53 KiB)

julia> @btime filterview(r->r.x2>(5) && isodd(r.x3),rows(t));
  154.900 μs (11 allocations: 798.16 KiB)

julia> ds = Dataset(x1 = 1, x2 = 1:10^5, x3 = 1:2:2*10^5);

julia> @btime filter(ds, [:x2,:x3], by =[>(5),isodd],view=true);
  263.700 μs (42 allocations: 893.73 KiB)

```

the view version of DataFrame the fastest of the 3

```julia

julia> @btime subset(df, [:x2,:x3]=> ByRow((x,y)-> x>5 && isodd(y)), view=true); 
  131.400 μs (460 allocations: 143.00 KiB)

julia> df = DataFrame(x1 = 1, x2 = 1:10^5, x3 = 1:2:2*10^5); 

```

---

<div class="post-metadata">

**Author:** ![DataFrames](https://avatars.discourse-cdn.com/v4/letter/d/e19b73/32.png) [@DataFrames](https://discourse.julialang.org/u/DataFrames)\
**Post date:** [July 23, 2022, 5:14am UTC](https://discourse.julialang.org/t/how-to-filter-inmemorydatasets/83478/8 "2022-07-23T05:14:31Z")

</div>

`t=Table(ds)` creates table with columns including `missing` and this might be the reason for having such a poor performance of `filterview`.  
I guess, in general, `InMemoryDatasets` should be the fastest one, because it uses parallel computation but `DataFrames` and `SplitApplyCombine` are single threaded.