# Tables package for fast grouping and filtering?

**URL:** <https://discourse.julialang.org/t/tables-package-for-fast-grouping-and-filtering/31950>\
**Category:** Performance\
**Tags:** package\
**Created:** [December 6, 2019, 10:25pm UTC](https://discourse.julialang.org/t/tables-package-for-fast-grouping-and-filtering/31950 "2019-12-06T22:25:33Z")\
**Posts on this page:** 19\
**Page:** 1

<div class="post-metadata">

**Author:** ![robsmith11](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/robsmith11/32/29641_2.png) [@robsmith11](https://discourse.julialang.org/u/robsmith11)\
**Post date:** [December 6, 2019, 10:25pm UTC](https://discourse.julialang.org/t/tables-package-for-fast-grouping-and-filtering/31950/1 "2019-12-06T22:25:33Z")

</div>

I already use Julia for much of my simulation and model calibration work, but am considering using it for some of the more basic data preparation I do.

This work is primarly filtering, grouping, and applying time series joins to large tables of financial market data. I currently use kdb+/q, which is a highly refined in-memory database and query language.

I’ve started by looking at the popular DataFrames.jl package, which seems to support most of the features I would like, but a simple aggregation seems to take 6.7x times longer.

Am I doing something wrong? Should I try a different Tables package?

```julia
julia> n=10^7; t=DataFrame(x=rand(10^7), y=rand(Bool,10^7))
julia> @btime by($t, :y, :x => sum)
  147.204 ms (130 allocations: 356.89 MiB)
2×2 DataFrame
│ Row │ y │ x_sum │ 
│ │ Bool │ Float64 │
├─────┼──────┼───────────┤
│ 1 │ 1 │ 2.49808e6 │
│ 2 │ 0 │ 2.50187e6 │

```

(I’m using a nightly Julia started with `--optimize=3 --inline=yes --check-bounds=no --math-mode=fast`)

```julia
q)n:prd 7#10; t:([]x:n?1f; y:n?0b)
q)\ts select sum x by y from t
22 134218464

```

kdb+/q is also using 64bit floats and the timing results with units are 22ms and 134MB of allocations.

---

<div class="post-metadata">

**Author:** ![Yifan\_Liu](https://avatars.discourse-cdn.com/v4/letter/y/4da419/32.png) [@Yifan\_Liu](https://discourse.julialang.org/u/Yifan_Liu)\
**Post date:** [December 6, 2019, 10:32pm UTC](https://discourse.julialang.org/t/tables-package-for-fast-grouping-and-filtering/31950/2 "2019-12-06T22:32:36Z")

</div>

RCall + data.table

---

<div class="post-metadata">

**Author:** ![robsmith11](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/robsmith11/32/29641_2.png) [@robsmith11](https://discourse.julialang.org/u/robsmith11)\
**Post date:** [December 6, 2019, 10:38pm UTC](https://discourse.julialang.org/t/tables-package-for-fast-grouping-and-filtering/31950/3 "2019-12-06T22:38:14Z")

</div>

If I wanted to go that route, I’d just call kdb+/q from julia. I used R’s data.table many years ago before I moved to kdb+/q. I’d rather not go back. 😉

The main reason I’d like to write everything in Julia is the simplicity of having everything in one language, espeically for running functions in parallel. I’d imagine using RCall isn’t compatibile with Julia’s multithreading.

---

<div class="post-metadata">

**Author:** ![baggepinnen](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/baggepinnen/32/693_2.png) [@baggepinnen](https://discourse.julialang.org/u/baggepinnen)\
**Post date:** [December 6, 2019, 10:39pm UTC](https://discourse.julialang.org/t/tables-package-for-fast-grouping-and-filtering/31950/4 "2019-12-06T22:39:52Z")

</div>

[https://github.com/xiaodaigh/FastGroupBy.jl](https://github.com/xiaodaigh/FastGroupBy.jl)

---

<div class="post-metadata">

**Author:** ![robsmith11](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/robsmith11/32/29641_2.png) [@robsmith11](https://discourse.julialang.org/u/robsmith11)\
**Post date:** [December 6, 2019, 10:45pm UTC](https://discourse.julialang.org/t/tables-package-for-fast-grouping-and-filtering/31950/5 "2019-12-06T22:45:54Z")

</div>

That’s an improvement, but at 75ms, it’s still 3.4x slower.

---

<div class="post-metadata">

**Author:** ![Yifan\_Liu](https://avatars.discourse-cdn.com/v4/letter/y/4da419/32.png) [@Yifan\_Liu](https://discourse.julialang.org/u/Yifan_Liu)\
**Post date:** [December 6, 2019, 11:49pm UTC](https://discourse.julialang.org/t/tables-package-for-fast-grouping-and-filtering/31950/6 "2019-12-06T23:49:34Z")

</div>

just curious. could you benchmark kdb+/q against data.table?

---

<div class="post-metadata">

**Author:** ![robsmith11](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/robsmith11/32/29641_2.png) [@robsmith11](https://discourse.julialang.org/u/robsmith11)\
**Post date:** [December 7, 2019, 12:11am UTC](https://discourse.julialang.org/t/tables-package-for-fast-grouping-and-filtering/31950/7 "2019-12-07T00:11:41Z")

</div>

It’s been a while, so there may be a faster way:

```julia
> library(data.table)
data.table 1.12.6 using 4 threads (see ?getDTthreads). Latest news: r-datatable.com
> n=10^7; t=data.table(x=runif(n),y=runif(n)>0.5)
> system.time(t[,sum(x),by=y])
   user system elapsed
  0.532 0.084 0.161
> setDTthreads(1)
> system.time(t[,sum(x),by=y])
   user system elapsed
  0.203 0.050 0.252

```

---

<div class="post-metadata">

**Author:** ![Yifan\_Liu](https://avatars.discourse-cdn.com/v4/letter/y/4da419/32.png) [@Yifan\_Liu](https://discourse.julialang.org/u/Yifan_Liu)\
**Post date:** [December 7, 2019, 12:17am UTC](https://discourse.julialang.org/t/tables-package-for-fast-grouping-and-filtering/31950/8 "2019-12-07T00:17:04Z")

</div>

could you change by to keyby in your data.table example?

---

<div class="post-metadata">

**Author:** ![robsmith11](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/robsmith11/32/29641_2.png) [@robsmith11](https://discourse.julialang.org/u/robsmith11)\
**Post date:** [December 7, 2019, 12:19am UTC](https://discourse.julialang.org/t/tables-package-for-fast-grouping-and-filtering/31950/9 "2019-12-07T00:19:08Z")

</div>

Same results +/- 5 ms.

---

<div class="post-metadata">

**Author:** ![Yifan\_Liu](https://avatars.discourse-cdn.com/v4/letter/y/4da419/32.png) [@Yifan\_Liu](https://discourse.julialang.org/u/Yifan_Liu)\
**Post date:** [December 7, 2019, 12:24am UTC](https://discourse.julialang.org/t/tables-package-for-fast-grouping-and-filtering/31950/10 "2019-12-07T00:24:57Z")

</div>

kdb+ really fast

---

<div class="post-metadata">

**Author:** ![robsmith11](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/robsmith11/32/29641_2.png) [@robsmith11](https://discourse.julialang.org/u/robsmith11)\
**Post date:** [December 7, 2019, 12:33am UTC](https://discourse.julialang.org/t/tables-package-for-fast-grouping-and-filtering/31950/11 "2019-12-07T00:33:56Z")

</div>

Wins what? The results are:

```julia
R data.table 1-thread: 252 ms
R data.table 4-threads: 161 ms
Julia DataFrames: 147 ms
Julia FastGroupBy: 75 ms
kdb+/q: 22 ms

```

---

<div class="post-metadata">

**Author:** ![baggepinnen](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/baggepinnen/32/693_2.png) [@baggepinnen](https://discourse.julialang.org/u/baggepinnen)\
**Post date:** [December 7, 2019, 1:01am UTC](https://discourse.julialang.org/t/tables-package-for-fast-grouping-and-filtering/31950/12 "2019-12-07T01:01:22Z")

</div>

@xiaodai, maybe this is a new benchmark for you to use in the pursuit of a faster groupby?

---

<div class="post-metadata">

**Author:** ![xiaodai](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/xiaodai/32/15937_2.png) [@xiaodai](https://discourse.julialang.org/u/xiaodai)\
**Post date:** [December 7, 2019, 1:19am UTC](https://discourse.julialang.org/t/tables-package-for-fast-grouping-and-filtering/31950/13 "2019-12-07T01:19:57Z")

</div>

Are you able to test it out on a large example? Or you need to do these small group by millions of times? If u only need it 20times then I consider the gap to be in the “who cares” range. But it’s interesting to see what can done to match it though. Can kdb/q support strings? `y` is a boolean so I think you can use one of the internal functions of FastGroupBy to make it faster. I will look at it when I get home.

---

<div class="post-metadata">

**Author:** ![robsmith11](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/robsmith11/32/29641_2.png) [@robsmith11](https://discourse.julialang.org/u/robsmith11)\
**Post date:** [December 7, 2019, 1:45am UTC](https://discourse.julialang.org/t/tables-package-for-fast-grouping-and-filtering/31950/14 "2019-12-07T01:45:33Z")

</div>

With `n=10^9`:

```julia
Julia DataFrames: 13.57 seconds
Julia FastGroupBy: 7.48 seconds
kdb+/q: 2.30 seconds

```

You’re right that in this trivial example I don’t care about performance. A more realistic example where things get slow, even in kdb+/q, is a rolling time window join by symbol between two tables while applying custom aggregation functions to multiple columns.

kdb+/q does support strings, as well as symbols (enumerated strings):

> **[Data types | Basics | kdb+ and q documentation - Kdb+ and q documentation](https://code.kx.com/q/basics/datatypes/)**
>
> Every kdb+ object has a data type. This page tabulates the datatypes.

---

<div class="post-metadata">

**Author:** ![xiaodai](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/xiaodai/32/15937_2.png) [@xiaodai](https://discourse.julialang.org/u/xiaodai)\
**Post date:** [December 7, 2019, 1:53am UTC](https://discourse.julialang.org/t/tables-package-for-fast-grouping-and-filtering/31950/15 "2019-12-07T01:53:27Z")

</div>

> [@robsmith11](#):
>
> kdb+/q, is a rolling time window join by symbol between

How slow is it compared to data.table::foverlaps?

---

<div class="post-metadata">

**Author:** ![xiaodai](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/xiaodai/32/15937_2.png) [@xiaodai](https://discourse.julialang.org/u/xiaodai)\
**Post date:** [December 7, 2019, 10:06am UTC](https://discourse.julialang.org/t/tables-package-for-fast-grouping-and-filtering/31950/16 "2019-12-07T10:06:18Z")

</div>

@robsmith11

```julia
fgroupreduce(by::Vector{Bool}, x::Vector{S}) where {T, S} = begin
    @inbounds for (b, x1) in zip(by, x)
        x[b + 1] += x1
    end
end

@time fgroupreduce(t[!, :y], t[!, :x])

```

This is the fastest way to do the `group_by` in Julia. Which is a `groupreduce`. I was in the process of implementing this in `FastGroupBy`.jl` before I got side tracked by other projects. I might prioritise these feature if u want to use them.

---

<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:** [December 7, 2019, 10:21am UTC](https://discourse.julialang.org/t/tables-package-for-fast-grouping-and-filtering/31950/17 "2019-12-07T10:21:00Z")

</div>

Currently DataFrames doesn’t have an optimized method for `Bool` grouping keys, nor for integers unfortunately. But you can try converting it to `PooledArray` or `CategoricalArray` and it should be quite faster. I’m still planning to improve this by avoiding some unnecessary computations when you just want to compute the sum.

---

<div class="post-metadata">

**Author:** ![robsmith11](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/robsmith11/32/29641_2.png) [@robsmith11](https://discourse.julialang.org/u/robsmith11)\
**Post date:** [December 7, 2019, 10:51pm UTC](https://discourse.julialang.org/t/tables-package-for-fast-grouping-and-filtering/31950/18 "2019-12-07T22:51:16Z")

</div>

Wow, that is indeed faster. 11.6 ms and 0 allocations for `n=10^7`, twice as fast as kdb+/q!

I don’t have a specific need for this feature, but good to know that Julia can compete with kdb+/q’s black magic.

@nalimilan  
Using `CategoricalArray` with DataFrames’ `by` also produces a decent speed-up: 81 ms and 152 MB allocations, so almost 2x faster than with a `Bool` array.

---

<div class="post-metadata">

**Author:** ![xiaodai](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/xiaodai/32/15937_2.png) [@xiaodai](https://discourse.julialang.org/u/xiaodai)\
**Post date:** [December 8, 2019, 6:40am UTC](https://discourse.julialang.org/t/tables-package-for-fast-grouping-and-filtering/31950/19 "2019-12-08T06:40:10Z")

</div>

> [@robsmith11](#):
>
> Wow, that is indeed faster. 11.6 ms and 0 allocations for `n=10^7` , twice as fast as kdb+/q!

For something simple like this we can expect Julia to be as fast as possible. Really should have that in DataFrames at soe point.