# Large dataframe. fast row selection

**URL:** <https://discourse.julialang.org/t/large-dataframe-fast-row-selection/14849>\
**Category:** Data\
**Tags:** query, dataframes\
**Created:** [September 12, 2018, 10:33am UTC](https://discourse.julialang.org/t/large-dataframe-fast-row-selection/14849 "2018-09-12T10:33:40Z")\
**Posts on this page:** 6\
**Page:** 1

<div class="post-metadata">

**Author:** ![grandemundo82](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/grandemundo82/32/17667_2.png) [@grandemundo82](https://discourse.julialang.org/u/grandemundo82)\
**Post date:** [September 12, 2018, 10:33am UTC](https://discourse.julialang.org/t/large-dataframe-fast-row-selection/14849/1 "2018-09-12T10:33:40Z")

</div>

I have a very large dataframe and would like to know the fastest way to select a subset of rows. I tried Query package. It is elegant but really slow compared to ugly/brute force solution. Any hints? Thank you. Much appreciated.

Example

using DataFrames, Query

const N = 100000000 ;  
const min\_age, max\_age = 20, 60 ;  
const min\_year, max\_year = 1980, 2010 ;  
const J = 9  
df = DataFrame(age=rand(min\_age:max\_age,N),  
year=rand(min\_year:max\_year,N),  
jx = rand(1:J,N)) ;

const min\_age2, max\_age2 = 30, 40 ;  
const min\_year2, max\_year2 = 1990, 2000 ;

@time df1 = df[(df[:age].\>=min\_age2).\*  
(df[:age].\<=max\_age2).\*  
(df[:year].\>=min\_year2).\*  
(df[:year].\<=max\_year2).\*  
(df[:jx].\<J),:] ;

1.372216 seconds (20.77 k allocations: 206.762 MiB)

@time ds1 = @from i in df begin  
@where i.age \>= min\_age && i.age \<= max\_age && i.year \>= min\_year && i.year \<= max\_year && i.jx \< J  
@select {i.year,i.age,i.jx}  
@collect DataFrame  
end

14.201888 seconds (100.02 M allocations: 1.869 GiB, 5.57% gc time)

---

<div class="post-metadata">

**Author:** ![nalimilan](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/nalimilan/32/147_2.png) [@nalimilan](https://discourse.julialang.org/u/nalimilan)\
**Post date:** [September 12, 2018, 11:39am UTC](https://discourse.julialang.org/t/large-dataframe-fast-row-selection/14849/2 "2018-09-12T11:39:46Z")

</div>

DataFramesMeta is as fast as the plain DataFrames version, but more compact:

```julia
using DataFramesMeta
df1 = @where(df, min_age2 .<= :age .<= max_age2, min_year2 .<= :year .<= max_year2, :jx .< J)

```

(BTW, note that you can combine minimum and maximum conditions even without DataFramesMeta.)

---

<div class="post-metadata">

**Author:** ![jacobadenbaum](https://avatars.discourse-cdn.com/v4/letter/j/5daacb/32.png) [@jacobadenbaum](https://discourse.julialang.org/u/jacobadenbaum)\
**Post date:** [September 12, 2018, 2:00pm UTC](https://discourse.julialang.org/t/large-dataframe-fast-row-selection/14849/3 "2018-09-12T14:00:58Z")

</div>

One thing that you can do to speed things up is to index the dataframes using row numbers instead of logical indices. I’m not sure why, but I find that if you have a bitarray of rows you want to keep, you can get better performance by applying `findall` to it before indexing the array. In your example, the comparison would be:

```julia
using BenchmarkTools

@btime df1 = @where($df, min_age2 .<= :age .<= max_age2,
                    min_year2 .<= :year .<= max_year2,
                    :jx .< J);
# 1.508 s (59 allocations: 238.46 MiB)

@btime begin
    idx = ($df[:age].>=min_age2).*
        ($df[:age].<=max_age2).*
        ($df[:year].>=min_year2).*
        ($df[:year].<=max_year2).*
        ($df[:jx].<J);
    df1 = $df[findall(idx),:]
end;
# 963.606 ms (66 allocations: 279.15 MiB)

```

That still leaves you with the problem of the ugly syntax. I like to use `map` for problems like this:

```julia
@btime df1 = $df[map($df[:age], $df[:year], $df[:jx]) do age, year, jx
   min_age2 <= age <= max_age2 || return false
   min_year2<= year <= max_year2|| return false
   jx < J || return false
   return true
end |> findall, :];
# 947.216 ms (49 allocations: 362.59 MiB)

```

It’s not necessarily less code to type, but I think that handling it with explicit switches makes it a lot more readable.

---

<div class="post-metadata">

**Author:** ![nalimilan](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/nalimilan/32/147_2.png) [@nalimilan](https://discourse.julialang.org/u/nalimilan)\
**Post date:** [September 12, 2018, 2:25pm UTC](https://discourse.julialang.org/t/large-dataframe-fast-row-selection/14849/4 "2018-09-12T14:25:44Z")

</div>

> [@jacobadenbaum](#):
>
> I’m not sure why, but I find that if you have a bitarray of rows you want to keep, you can get better performance by applying `findall` to it before indexing the array.

Good catch. This is probably because the `getindex` method for `DataFrame` calls `getindex` on each column, which needs to compute the number and position of `true` entries repeatedly (via the internal `LogicalIndex` type). We should try two alternative solutions:

- create a `LogicalIndex` once and pass it to `getindex` to avoid recomputing the number of entries
- call `findall` and pass a vector of indices to `getindex` to also avoid recomputing the position of entries

The second approach is equivalent to what you are doing. It has the drawback that a new vector needs to be allocated, but that’s not a big deal given that we already have to allocate new columns with the same length.

Would you be interested in making a pull request?

---

<div class="post-metadata">

**Author:** ![jacobadenbaum](https://avatars.discourse-cdn.com/v4/letter/j/5daacb/32.png) [@jacobadenbaum](https://discourse.julialang.org/u/jacobadenbaum)\
**Post date:** [September 12, 2018, 2:34pm UTC](https://discourse.julialang.org/t/large-dataframe-fast-row-selection/14849/5 "2018-09-12T14:34:48Z")

</div>

Sure. I can try to work on it later this week.

---

<div class="post-metadata">

**Author:** ![davidanthoff](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/davidanthoff/32/223493_2.png) [@davidanthoff](https://discourse.julialang.org/u/davidanthoff)\
**Post date:** [September 13, 2018, 5:49pm UTC](https://discourse.julialang.org/t/large-dataframe-fast-row-selection/14849/6 "2018-09-13T17:49:23Z")

</div>

Yes, [Query.jl](https://github.com/queryverse/Query.jl)’s current row based implementation won’t be able to compete with the kind of column based implementation you are seeing better performance for. The design to add a column based backend to Query.jl is all there (well, in my head 😉 ), but it is a lot of work. We are starting on that, but this won’t be done in a few weeks/months…

Having said that, I’m seeing a smaller difference on julia 1.0 than on julia 0.6 on my system:

julia 0.6:  
Query: 10.895567 seconds (100.02 M allocations: 1.533 GiB, 7.64% gc time)  
Your code: 1.527942 seconds (20.77 k allocations: 206.659 MiB, 0.42% gc time)

julia 1.0:  
Query: 5.751088 seconds (390.53 k allocations: 3.022 GiB, 9.96% gc time)  
Your code: 1.121577 seconds (60 allocations: 205.535 MiB)

There might also still be stuff we can do to improve the performance of the row based implementation, I actually have not run a profiler on this kind of stuff in a long time…