# What is the recommended way to filter rows of a Dataframe?

**URL:** <https://discourse.julialang.org/t/what-is-the-recommended-way-to-filter-rows-of-a-dataframe/117367>\
**Category:** Performance\
**Tags:** dataframes\
**Created:** [July 23, 2024, 12:30am UTC](https://discourse.julialang.org/t/what-is-the-recommended-way-to-filter-rows-of-a-dataframe/117367 "2024-07-23T00:30:14Z")\
**Posts on this page:** 7\
**Page:** 1

<div class="post-metadata">

**Author:** ![curious](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/curious/32/7629_2.png) [@curious](https://discourse.julialang.org/u/curious)\
**Post date:** [July 23, 2024, 12:30am UTC](https://discourse.julialang.org/t/what-is-the-recommended-way-to-filter-rows-of-a-dataframe/117367/1 "2024-07-23T00:30:14Z")

</div>

Hello,

I am asking for recommendations/tips on how to filter rows of a dataframe. I was using `filter` for sometime. But it seems inefficient/slow compared to subsetting dataframe using other methods. Here are benchmarks with three different ways of filtering a smallish dataframe of size (26760, 14). Do you prefer/recommend one of these ways, or something else entirely?

Thank you very much.

```julia
julia> import DataFramesMeta as Dfm

julia> dfsub1 = @btime Dfm.@subset(df1, :col8 .== 2, :col9 .== true);
  178.280 μs (385 allocations: 401.98 KiB)

julia> dfsub2 = @btime df1[(df1.col8 .== 2) .& (df1.col9 .== true), :];
  51.515 μs (87 allocations: 371.91 KiB)
  
julia> dfsub3 = @btime filter(row -> row.col8 .== 2 && row.col9 .== true, df1);
16.746 ms (134454 allocations: 3.23 MiB)

```

---

<div class="post-metadata">

**Author:** ![alfaromartino](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/alfaromartino/32/52986_2.png) [@alfaromartino](https://discourse.julialang.org/u/alfaromartino)\
**Post date:** [July 23, 2024, 1:04am UTC](https://discourse.julialang.org/t/what-is-the-recommended-way-to-filter-rows-of-a-dataframe/117367/2 "2024-07-23T01:04:20Z")

</div>

When I use DataFrames, I usually prioritize readability over speed (as long as there are no huge differences).

Considering this, I usually switch between the following options.

```julia
using DataFrames, DataFramesMeta, BenchmarkTools

dff = DataFrame(x = rand(100), y = rand(100), z = repeat([1,2], 50))

# fastest (as far as I know)
  # it solves the inherent type unstability of dataframes
function foo1()
    cond(x,y) = @.( (x > 0.5) && (y > 0.5) )
    
    dff[cond(dff.x, dff.y), :]
end
@btime foo1() # 1.580 μs (14 allocations: 1.86 KiB)

# what I usually use (prioritize readability)
function foo2()
    cond = @with dff @.( (:x > 0.5) && (:y > 0.5) )
    
    dff[cond, :]
end

@btime foo2() # 3.462 μs (29 allocations: 2.44 KiB)

```

In my experiencie, any other method provides a similar speed or is slower. I’d avoid `filter` for dataframes, because as far as a I remember it’s type unstable by construction.

---

<div class="post-metadata">

**Author:** ![Satvik](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/satvik/32/20486_2.png) [@Satvik](https://discourse.julialang.org/u/Satvik)\
**Post date:** [July 23, 2024, 1:27am UTC](https://discourse.julialang.org/t/what-is-the-recommended-way-to-filter-rows-of-a-dataframe/117367/3 "2024-07-23T01:27:37Z")

</div>

The `DataFrames` documentation recommends using `subset`:

> │ Note that as the subset function works in exactly the same way as other transformation functions defined in DataFrames.jl this is the preferred way to subset rows of a data frame or grouped data frame. In particular it uses a different set of rules for specifying transformations than filter which is implemented in DataFrames.jl to ensure support for the standard Julia API for collections.

It looks like the `DataFramesMeta.@subset` macro calls `DataFrames.subset`, so there shouldn’t be any difference there.

---

<div class="post-metadata">

**Author:** ![nilshg](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/nilshg/32/2283_2.png) [@nilshg](https://discourse.julialang.org/u/nilshg)\
**Post date:** [July 23, 2024, 8:46am UTC](https://discourse.julialang.org/t/what-is-the-recommended-way-to-filter-rows-of-a-dataframe/117367/4 "2024-07-23T08:46:03Z")

</div>

To recover the performance of `filter` use the `cols => fun` version and don’t broadcast your conditions, as `filter` operates row-wise already:

```julia
julia> using DataFrames, Chairmarks

julia> df = DataFrame(a = rand(1:10, 1_000_000); b = rand(Bool, 1_000_000));

julia> @b df[df.a .== 2 .&& df.b, :]
507.200 μs (30 allocs: 956.469 KiB)

julia> @b filter(r -> r.a == 2 && r.b, $df)
58.145 ms (2199647 allocs: 34.498 MiB)

julia> @b subset($df, :a => ByRow(==(2)), :b)
599.200 μs (192 allocs: 1.894 MiB)

julia> @b filter([:a, :b] => ((a, b) -> a == 2 && b), $df)
507.400 μs (27 allocs: 956.453 KiB)

julia> function foo1(data)
           cond(x, y) = x .== 2 .&& y

           df[cond(data.a, data.b), :]
       end
foo1 (generic function with 1 method)

julia> @b foo1($df)
504.900 μs (28 allocs: 956.406 KiB)

```

---

<div class="post-metadata">

**Author:** ![curious](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/curious/32/7629_2.png) [@curious](https://discourse.julialang.org/u/curious)\
**Post date:** [July 23, 2024, 12:43pm UTC](https://discourse.julialang.org/t/what-is-the-recommended-way-to-filter-rows-of-a-dataframe/117367/5 "2024-07-23T12:43:17Z")

</div>

Thank you all. I learned new things from your answers. I cannot decide whether to accept the response by @alfaromartino, or by @nilshg as the solution. I will choose the latter because I will most likely use the `foo1` example from there.

Can you please explain what is the significance of using `$df` (instead of just `df`) in these examples?

---

<div class="post-metadata">

**Author:** ![nilshg](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/nilshg/32/2283_2.png) [@nilshg](https://discourse.julialang.org/u/nilshg)\
**Post date:** [July 23, 2024, 1:03pm UTC](https://discourse.julialang.org/t/what-is-the-recommended-way-to-filter-rows-of-a-dataframe/117367/6 "2024-07-23T13:03:30Z")

</div>

This interpolates the variable into the expression to ensure it is not treated as a global variable in the benchmark. See here in the BenchmarkTools docs (same holds for Chairmarks afaik):

> **[Manual · BenchmarkTools.jl](https://juliaci.github.io/BenchmarkTools.jl/stable/manual/#Interpolating-values-into-benchmark-expressions)**
>
> Documentation for BenchmarkTools.jl.

---

<div class="post-metadata">

**Author:** ![Nathan\_Boyer](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/nathan_boyer/32/14825_2.png) [@Nathan\_Boyer](https://discourse.julialang.org/u/Nathan_Boyer)\
**Post date:** [July 23, 2024, 1:15pm UTC](https://discourse.julialang.org/t/what-is-the-recommended-way-to-filter-rows-of-a-dataframe/117367/7 "2024-07-23T13:15:48Z")

</div>

The “recommended” way for DataFramesMeta would be `@rsubset`, which is the same syntax as your first example but without the broadcasting dots.

(You may also find useful the summary of methods I posted [here](https://github.com/JuliaData/DataFrames.jl/issues/3051#issuecomment-1701507457) as part of a discussion for proposing better syntax for this.)