# Is there a faster way to filter in dataframes

**URL:** <https://discourse.julialang.org/t/is-there-a-faster-way-to-filter-in-dataframes/67245>\
**Category:** New to Julia\
**Tags:** dataframes, namedtuple\
**Created:** [August 28, 2021, 8:26pm UTC](https://discourse.julialang.org/t/is-there-a-faster-way-to-filter-in-dataframes/67245 "2021-08-28T20:26:11Z")\
**Posts on this page:** 10\
**Page:** 1

<div class="post-metadata">

**Author:** ![vtomar](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/vtomar/32/18304_2.png) [@vtomar](https://discourse.julialang.org/u/vtomar)\
**Post date:** [August 28, 2021, 8:26pm UTC](https://discourse.julialang.org/t/is-there-a-faster-way-to-filter-in-dataframes/67245/1 "2021-08-28T20:26:11Z")

</div>

Is there a faster way to filter than the method below when applying filtering across multiple columns of a dataframe.

```julia
using DataFrames, BenchmarkTools
test_df = DataFrame(a = rand(10000), b = rand(10000), 
               c = rand(10000), d = rand(10000))
@btime filter(AsTable([:a, :b]) => ( @. x -> !ismissing(x.a) & 
        (x.b > 0.5) & (0.25 <= x.a <= 0.75) ), test_df )

```

---

<div class="post-metadata">

**Author:** ![pdeffebach](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/pdeffebach/32/10320_2.png) [@pdeffebach](https://discourse.julialang.org/u/pdeffebach)\
**Post date:** [August 28, 2021, 9:25pm UTC](https://discourse.julialang.org/t/is-there-a-faster-way-to-filter-in-dataframes/67245/2 "2021-08-28T21:25:23Z")

</div>

I can’t think of an obviously better way, no. Though the `@.` before the function is weird, I didn’t realize that syntax works.

Is it slow compared to other languages? If so it would be interesting to look in more to really push for performance.

EDIT: The use of `@btime` here might not be the most reliable, since you are constructing an anonymous function inside the call. Maybe wrap in another function and see how it goes?

---

<div class="post-metadata">

**Author:** ![jling](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/jling/32/212909_2.png) [@jling](https://discourse.julialang.org/u/jling)\
**Post date:** [August 28, 2021, 9:36pm UTC](https://discourse.julialang.org/t/is-there-a-faster-way-to-filter-in-dataframes/67245/3 "2021-08-28T21:36:06Z")

</div>

`;view = true` will make it faster if the use case is read-only

---

<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:** [August 28, 2021, 11:05pm UTC](https://discourse.julialang.org/t/is-there-a-faster-way-to-filter-in-dataframes/67245/4 "2021-08-28T23:05:24Z")

</div>

```julia
subset(test_df, AsTable([:a, :b]) => ( @. x -> !ismissing(x.a) &
               (x.b > 0.5) & (0.25 <= x.a <= 0.75)))

```

---

<div class="post-metadata">

**Author:** ![vtomar](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/vtomar/32/18304_2.png) [@vtomar](https://discourse.julialang.org/u/vtomar)\
**Post date:** [August 28, 2021, 11:15pm UTC](https://discourse.julialang.org/t/is-there-a-faster-way-to-filter-in-dataframes/67245/5 "2021-08-28T23:15:40Z")

</div>

subset isn’t as fast as filter.

---

<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:** [August 28, 2021, 11:35pm UTC](https://discourse.julialang.org/t/is-there-a-faster-way-to-filter-in-dataframes/67245/6 "2021-08-28T23:35:38Z")

</div>

allow missing and it will be

```julia
allowmissing!(test_df)
 @btime filter(AsTable([:a, :b]) => ( @. x -> !ismissing(x.a) &
               (x.b > 0.5) & (0.25 <= x.a <= 0.75) ), test_df )
  2.177 ms (40077 allocations: 2.25 MiB)
@btime subset(test_df, AsTable([:a, :b]) => ( @. x -> !ismissing(x.a) &
               (x.b > 0.5) & (0.25 <= x.a <= 0.75) ) )
  118.203 μs (241 allocations: 127.20 KiB)

```

---

<div class="post-metadata">

**Author:** ![pdeffebach](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/pdeffebach/32/10320_2.png) [@pdeffebach](https://discourse.julialang.org/u/pdeffebach)\
**Post date:** [August 29, 2021, 12:40am UTC](https://discourse.julialang.org/t/is-there-a-faster-way-to-filter-in-dataframes/67245/7 "2021-08-29T00:40:46Z")

</div>

It is with `ByRow` instead of broadcasting. I think both `filter` and `ByRow` might enable some sort of multi-threading that isn’t being picked up with the broadcasting?

---

<div class="post-metadata">

**Author:** ![xinchin](https://avatars.discourse-cdn.com/v4/letter/x/54ee81/32.png) [@xinchin](https://discourse.julialang.org/u/xinchin)\
**Post date:** [August 29, 2021, 12:56am UTC](https://discourse.julialang.org/t/is-there-a-faster-way-to-filter-in-dataframes/67245/8 "2021-08-29T00:56:35Z")

</div>

just offtopic, how I can do this for typed tables?

---

<div class="post-metadata">

**Author:** ![pdeffebach](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/pdeffebach/32/10320_2.png) [@pdeffebach](https://discourse.julialang.org/u/pdeffebach)\
**Post date:** [August 29, 2021, 1:46am UTC](https://discourse.julialang.org/t/is-there-a-faster-way-to-filter-in-dataframes/67245/9 "2021-08-29T01:46:16Z")

</div>

If you add another `0` to the length of `test_data` they should be the same. So I think you are seeing the complicated internal logic of `subset` compared to `filter`. This is a fixed cost and won’t matter for bigger data frames.

---

<div class="post-metadata">

**Author:** ![vtomar](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/vtomar/32/18304_2.png) [@vtomar](https://discourse.julialang.org/u/vtomar)\
**Post date:** [August 29, 2021, 5:19am UTC](https://discourse.julialang.org/t/is-there-a-faster-way-to-filter-in-dataframes/67245/10 "2021-08-29T05:19:39Z")

</div>

Yes you are right. I am observing the same.