### Author:  [@xiaodai](https://discourse.julialang.org/u/xiaodai)
#### Post date: [February 25, 2018, 11:23am UTC](https://discourse.julialang.org/t/group-by-performance-benchmarks-and-recommendations/9313/1 "2018-02-25T11:23:26Z")
I have been trying to improve Julia’s DataFrame group-by for a while now and I think I am able to synthesized my thinking into APIs.
Here are my **synthesized recommendation (as of 25th Feb 2018)**
| Recommendation | Why? | Benchmark Status |
| --- | --- | --- |
| If you need to perform **group-by A LOT** use **JuliaDB.jl/IndexedTables.jl** | You can create indexes on the group-by columns and it is generally faster than other available methods | No benchmarks; as the benchmarks was intended to test non-indexing performance |
| If the # of unique groups is small use **DataFramesMeta.jl** ’s `@by` | DataFramesMeta.jl uses a hashtable (Dict) in the background and it works pretty well for types with a small number of unique values (due to efficient CPU cache usage I assume) | Benchmarked. it beats FastGroupBy.jl in one case |
| **FastGroupBy.jl** ’s fast algorithm will eventually but contributed back into DataFrames.jl and JuliaDB.jl. Use **DataFramesMeta.jl** instead and the group-by will gain a speed boost when these algorithms get there in a PR | **FastGroupBy.jl** tends to have the fastest group-by algorithms. Use **CategoricalArrays.jl** whenever possible as the group-by vectors. FastGroupBy.jl implemented [an optimization that is not yet in data.table](https://github.com/Rdatatable/data.table/issues/2458) | Benchmarked |
| if nice API is more important than speed then use **DataFramesMeta.jl** | It’s got the cleanest API and easy to remember verbs | Benchmarked |
| Do **NOT** use **Query.jl** if performance matters | You can see in my benchmarking code that I have benchmarked Query.jl but it’s way too slow for even 10m records. This issue has been reported before, I am not sure the design of Query.jl can support performance-oriented group-bys | Benchmarked; not plotted |
**Benchmarks**
Group-by in Julia using strings as the group-by variable tends to result in much slower timings than R’s data.table. This can’t be avoided as R has string interning. So I have provided benchmarks for when the group-by is a string (data.table is faster) vs when the group-by is a `categorical` (FastGroupBy.jl 10x faster) for one particular case to illustrate. When the group-by is a reduce operation e.g. sum but not mean, then using `fgroupreduce` will yield faster results. Again **do not** try to use FastGroupBy.jl just yet, as it’s immature and only have algorithms for the five types of group-by’s benchmarked below. Also R’s data.table is faster than any Julia implementation unless the group-by variables is a `cateogrical`; but FastGroupBy.jl beats the best known Julia solutions (without indexing) by a wide margin most of the time, so its algorithms which are radix-sort based should be contributed back to DataFrames.jl and JuliaDB.jl
**Benchmarking code**
>
---
### Author:  [@nalimilan](https://discourse.julialang.org/u/nalimilan)
#### Post date: [February 25, 2018, 12:00pm UTC](https://discourse.julialang.org/t/group-by-performance-benchmarks-and-recommendations/9313/2 "2018-02-25T12:00:31Z")
DataFramesMeta uses the plain DataFrames `by` method, right? Then its slowness is due in large part to the type instability of the reduction, since the type of the columns isn’t encoded in the object type. I’m afraid the performance of the algorithm won’t be the main determinant as long as we haven’t fixed that. See [this issue](https://github.com/JuliaData/DataFrames.jl/issues/1256).
---
### Author:  [@xiaodai](https://discourse.julialang.org/u/xiaodai)
#### Post date: [February 25, 2018, 12:12pm UTC](https://discourse.julialang.org/t/group-by-performance-benchmarks-and-recommendations/9313/3 "2018-02-25T12:12:36Z")
> [@nalimilan](#):
>
> Then its slowness is due in large part to the type instability of the reduction, since the type of the columns isn’t encoded in the object type.
I think you can address the above by updating the DataFramesMeta.jl’s macro `@by` to make it do a “translation” step. The trick is not to work with DataFrames but have macros that “break it down” so that the functions work on vectors or tuples of vectors; so NamedTuples would be one implementation.
You can write the functions so that they are called by passing columns into them. We have macros like DataFramesMeta that works like this
```julia
@> df begin
@by(:bygroup, sum(:v1)
end
```
you see how that can be transformed into something like `groupreduce(+, df[:bygroup], df[:v1])` and if you make that a “function boundary” then optimized type-stable code will be called at that point. You can see some of it in action in my benchmarking code.
I can probably post an example of this in FastGroupBy.jl next weekend.
I have an idea about how to design the API and have the implementation use some weighted graphs and apply Dykstra’s algorithm to find optimal ways to perform these types of operation. There is a good chance I will blog about it in the next 6 months or so.
---
### Author:  [@nalimilan](https://discourse.julialang.org/u/nalimilan)
#### Post date: [February 25, 2018, 8:55pm UTC](https://discourse.julialang.org/t/group-by-performance-benchmarks-and-recommendations/9313/4 "2018-02-25T20:55:18Z")
Yes, that approach would work. Though I’m not sure it’s easy to implement in full generality, e.g. if the function returns a data frame for each group (which `by` currently supports). Sometimes it’s also useful to have access to all columns in the data frame, e.g. if you want to standardize them within each group. So it would be useful to also make `by` more efficient with plain DataFrames.
---
### Author:  [@xiaodai](https://discourse.julialang.org/u/xiaodai)
#### Post date: [February 25, 2018, 9:00pm UTC](https://discourse.julialang.org/t/group-by-performance-benchmarks-and-recommendations/9313/5 "2018-02-25T21:00:33Z")
Yeah. I think some functions will have to work with Dataframe but again all functions should woke with vectors and have function boundaries so everything is optimised whenever possible. In R’s data.table they basically recognise that you what `mean(v1)` and provide a fast path for that.
Anyway, that how DataFramesMeta should work as well.
Functions that deal with DataFramesMeta e.g size should work fine. But the moment you work with as vector it should be called via a function so the function boundary effect can take place
---
### Author:  [@Juan](https://discourse.julialang.org/u/Juan)
#### Post date: [February 25, 2018, 11:45pm UTC](https://discourse.julialang.org/t/group-by-performance-benchmarks-and-recommendations/9313/6 "2018-02-25T23:45:31Z")
Where are the results for JuliaDB.jl/IndexedTables.jl?
---
### Author:  [@xiaodai](https://discourse.julialang.org/u/xiaodai)
#### Post date: [February 25, 2018, 11:54pm UTC](https://discourse.julialang.org/t/group-by-performance-benchmarks-and-recommendations/9313/7 "2018-02-25T23:54:57Z")
didn’t put them up because they seem slow without indexing. Might add later
---
### Author:  [@bramtayl](https://discourse.julialang.org/u/bramtayl)
#### Post date: [February 16, 2019, 8:44pm UTC](https://discourse.julialang.org/t/group-by-performance-benchmarks-and-recommendations/9313/8 "2019-02-16T20:44:27Z")
Curious about the results with LightQuery?
---
### Author:  [@xiaodai](https://discourse.julialang.org/u/xiaodai)
#### Post date: [February 16, 2019, 10:52pm UTC](https://discourse.julialang.org/t/group-by-performance-benchmarks-and-recommendations/9313/9 "2019-02-16T22:52:33Z")
Yeah. Would they be drastically different?
---
### Author:  [@bramtayl](https://discourse.julialang.org/u/bramtayl)
#### Post date: [February 17, 2019, 1:25am UTC](https://discourse.julialang.org/t/group-by-performance-benchmarks-and-recommendations/9313/10 "2019-02-17T01:25:18Z")
Maybe? If the data is presorted I think LightQuery will almost definitely be pretty fast. I just tagged a new version (0.1.6).
---
### Author:  [@datnamer](https://discourse.julialang.org/u/datnamer)
#### Post date: [February 17, 2019, 1:34am UTC](https://discourse.julialang.org/t/group-by-performance-benchmarks-and-recommendations/9313/11 "2019-02-17T01:34:47Z")
also query.jl
---
### Author:  [@Juan](https://discourse.julialang.org/u/Juan)
#### Post date: [September 2, 2019, 5:05pm UTC](https://discourse.julialang.org/t/group-by-performance-benchmarks-and-recommendations/9313/12 "2019-09-02T17:05:36Z")
Any update?
---
### Author:  [@xiaodai](https://discourse.julialang.org/u/xiaodai)
#### Post date: [September 2, 2019, 9:58pm UTC](https://discourse.julialang.org/t/group-by-performance-benchmarks-and-recommendations/9313/13 "2019-09-02T21:58:48Z")
Might update this at some point. Currently I only spend Sunday mornings on OSS’s work, so progress will be slow. But I think DataFrames with DataFramesMeta is the way to go. Other table libraries have lost steam