# Bad performance of group\_by of DataFrames - updated -

**URL:** <https://discourse.julialang.org/t/bad-performance-of-group-by-of-dataframes-updated/30061>\
**Category:** General Usage\
**Tags:** performance\
**Created:** [October 18, 2019, 7:15pm UTC](https://discourse.julialang.org/t/bad-performance-of-group-by-of-dataframes-updated/30061 "2019-10-18T19:15:16Z")\
**Posts on this page:** 20\
**Page:** 1

<div class="post-metadata">

**Author:** ![ufechner7](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/ufechner7/32/51363_2.png) [@ufechner7](https://discourse.julialang.org/u/ufechner7)\
**Post date:** [October 18, 2019, 7:15pm UTC](https://discourse.julialang.org/t/bad-performance-of-group-by-of-dataframes-updated/30061/1 "2019-10-18T19:15:16Z")

</div>

Hello,

I am working on a small dataset with 30 rows and 19 columns.

I do a group\_by on 3 colums and aggregate between one and all  
colums, using sum or maximum as aggregation functions.

The number of colums that are queried varies, also their  
order.

As a result the DataFrame.by function is getting recompiled  
for each query, which takes about 50 to 80 ms on my laptop.

While this doesn’t sound much, in reality we might have queries  
with 200 columns and we might need four calls to group\_by per  
web request, in the result the response time gets way too  
slow.

I think improving the performance of my function “aggregate”  
by a factor of 10 should be possible by avoiding any  
recompilation.

I already tried to use the macro “@nospecialize”, but it  
did not help.

Any suggestions how to get a better performance?

The test code:

```julia
# Purpose of this test:
# Reproduce the bad performance of aggregate, when the number and the order of the named_tuple argument changes.
using DataFrames

# TODO: find an implementation that doesn't recompile when the number of colums or their order change
function aggregate(table, agg_clause, (named_tuple), prn=false)
    # println(named_tuple)
    result, t, bytes, gctime, memallocs = @timed by(table, agg_clause, named_tuple, sort=true)
    if prn
        println("time: $(round(t, digits=3))")
    end
    return result
end

# Testdata
ALL = Symbol[:CAT_UTRECHT_CNT, :CAT_MOUSE, :WW_OP, :QWE_GRP, :HWR_KT, :CAT_WEIGHT, :HIGH_GRP, :CAT_WEIGHT_C, :CAT_CMI_C, :CAT_MOUSE_C, :CAT_FREIG_WGT_CNT, :CAT_FREIG_WGT_CNT_C, :AMS_CMI_TOT, :CAT_UTRECHT_DIS_CNT, :AMS_CMI_HRV, 
:GVZ_WXK, :AMS_CMI_DIFF, :CAT_UTRECHT_CNT_C]

agg_clause = Symbol[:HIGH_GRP, :QWE_GRP, :WW_OP]
NAMED_TUPLE = (CAT_WEIGHT_C_T = :CAT_WEIGHT_C => maximum, CAT_UTRECHT_CNT_T = :CAT_UTRECHT_CNT => maximum, CAT_CMI_C_T = :CAT_CMI_C => maximum, 
                 CAT_MOUSE_C_T = :CAT_MOUSE_C => maximum, CAT_FREIG_WGT_CNT_T = :CAT_FREIG_WGT_CNT => maximum, 
                 CAT_FREIG_WGT_CNT_C_T = :CAT_FREIG_WGT_CNT_C => maximum, CAT_MOUSE_T = :CAT_MOUSE => maximum, CAT_WEIGHT_C = :CAT_WEIGHT_C => sum, 
                 CAT_UTRECHT_CNT_C = :CAT_UTRECHT_CNT_C => sum, WW_OP = :WW_OP => maximum, CAT_FREIG_WGT_CNT = :CAT_FREIG_WGT_CNT => sum, 
                 HWR_KT = :HWR_KT => maximum, CAT_WEIGHT = :CAT_WEIGHT => sum, CAT_MOUSE = :CAT_MOUSE => sum, AMS_CMI_TOT = :AMS_CMI_TOT => sum, 
                 CAT_FREIG_WGT_CNT_C = :CAT_FREIG_WGT_CNT_C => sum, CAT_UTRECHT_DIS_CNT = :CAT_UTRECHT_DIS_CNT => sum, CAT_CMI_C = :CAT_CMI_C => sum, 
                 CAT_MOUSE_C = :CAT_MOUSE_C => sum, AMS_CMI_HRV = :AMS_CMI_HRV => sum, GVZ_WXK = :GVZ_WXK => sum, 
                 CAT_UTRECHT_CNT = :CAT_UTRECHT_CNT => sum, AMS_CMI_DIFF = :AMS_CMI_DIFF => sum)

# add a column with random Float64 values and the name col to the table
function add_column(table, col::Symbol)
    COL = rand(Float64, nrow(table))
    table[!, col] = COL 
end

# build a named tuple with the given length, using NAMED_TUPLE as input
function make_named_tuple(len)
    nt = NAMED_TUPLE
    @assert len <= length(nt)
    t_names = propertynames(nt)
    pairs = Vector{Pair}(undef, len)
    indices = []
    new_names = []
    for i in 1:len
        index = rand(setdiff(1:length(nt), indices))
        push!(indices, index)
        pairs[i] = values(nt)[index]
        push!(new_names, t_names[index])
    end
    return NamedTuple{Tuple(new_names)}(Tuple(pairs))
end

# create data frame with 30 rows
jtab = DataFrame([1:30])

# add columns, using random values
for col in ALL
    global jtab
    add_column(jtab, col)
end

# create random aggregates of this dataframe and determine the time
for i = 1:length(NAMED_TUPLE)
    nt = make_named_tuple(i)
    res = aggregate(jtab, agg_clause, nt, true)
end
nothing

```

Example output, second run:

```julia
julia> include("local_tests/test_aggregate2.jl")
time: 0.046
time: 0.054
time: 0.062
time: 0.057
time: 0.057
time: 0.061
time: 0.06
time: 0.063
time: 0.061
time: 0.066
time: 0.065
time: 0.074
time: 0.072
time: 0.075
time: 0.072
time: 0.069
time: 0.069
time: 0.071
time: 0.074
time: 0.075
time: 0.076
time: 0.078
time: 0.076

```

Any help appreciated!

---

<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:** [October 18, 2019, 7:32pm UTC](https://discourse.julialang.org/t/bad-performance-of-group-by-of-dataframes-updated/30061/2 "2019-10-18T19:32:53Z")

</div>

Why are you profiling when `add_column!` creates a random values? How is that helping your aggregation problem?

---

<div class="post-metadata">

**Author:** ![ufechner7](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/ufechner7/32/51363_2.png) [@ufechner7](https://discourse.julialang.org/u/ufechner7)\
**Post date:** [October 18, 2019, 7:43pm UTC](https://discourse.julialang.org/t/bad-performance-of-group-by-of-dataframes-updated/30061/3 "2019-10-18T19:43:43Z")

</div>

Well, the data that is queried might change, and if you query always the same data the aggregation function might use some caching that gives misleading benchmark results.

---

<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:** [October 18, 2019, 7:59pm UTC](https://discourse.julialang.org/t/bad-performance-of-group-by-of-dataframes-updated/30061/4 "2019-10-18T19:59:15Z")

</div>

I understand now.

I think you are doing everything right in terms of `by`, and I don’t think there is any particular DataFrames solution that will make this faster.

A lot of work has been put into making the concatenation step and the groupby step very optimized,so you are probably right that function compilation is the bottleneck here.

I’m sure you know this despite a NamedTuple being typed, DataFrames inputting a named tuple doesn’t provide any performance benefit, since the NamedTuple just stores the functions used, and none of the information about how the functions will be called.

It would be great if you could benchmark this using JuliaDB.

---

<div class="post-metadata">

**Author:** ![bkamins](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/bkamins/32/208538_2.png) [@bkamins](https://discourse.julialang.org/u/bkamins)\
**Post date:** [October 18, 2019, 8:13pm UTC](https://discourse.julialang.org/t/bad-performance-of-group-by-of-dataframes-updated/30061/5 "2019-10-18T20:13:04Z")

</div>

Using legacy API of `by` avoids recompilation. Here is how you can do it (this is a quick hack and I did not try to squeeze out maximum performance but I hope it is clear what is going on):

```julia
julia> function agg1(table, agg_clause, x, prn=false)
           # println(named_tuple)
           result, t, bytes, gctime, memallocs = @timed by(table, agg_clause, sort=true) do sdf
               r = DataFrame()
               for (a,b,c) in x
                   r[!, a] = [c(sdf[!, b])]
               end
               r
           end
           if prn
               println("time: $(round(t, digits=3))")
           end
           return result
       end
agg1 (generic function with 2 methods)

julia> for i = 1:length(NAMED_TUPLE)
           nt = make_named_tuple(i)
           x = []
           for (a,(b, c)) in pairs(nt)
               push!(x, [a, b, c])
           end
           res = agg1(jtab, agg_clause, x, true)
       end
time: 0.012
time: 0.0
time: 0.0
time: 0.0
time: 0.0
time: 0.001
time: 0.001
time: 0.0
time: 0.001
time: 0.001
time: 0.001
time: 0.001
time: 0.001
time: 0.001
time: 0.001
time: 0.001
time: 0.001
time: 0.001
time: 0.001
time: 0.001
time: 0.001
time: 0.002
time: 0.002

```

---

<div class="post-metadata">

**Author:** ![ufechner7](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/ufechner7/32/51363_2.png) [@ufechner7](https://discourse.julialang.org/u/ufechner7)\
**Post date:** [October 18, 2019, 8:29pm UTC](https://discourse.julialang.org/t/bad-performance-of-group-by-of-dataframes-updated/30061/6 "2019-10-18T20:29:57Z")

</div>

Very good! Thank you very much!  
I tried a slightly different version:

```julia
function aggregate1(table, agg_clause, @nospecialize(named_tuple), prn=false)
    # println(named_tuple)
    x = []
    for (a,(b, c)) in pairs(named_tuple)
        push!(x, [a, b, c])
    end
    result, t, bytes, gctime, memallocs = @timed by(table, agg_clause, sort=true) do sdf
        r = DataFrame()
        for (a,b,c) in x
            r[!, a] = [c(sdf[!, b])]
        end
        r
    end
    if prn
        println("time: $(round(t, digits=3))")
    end
    return result
end

```

Still need to check if the result is correct, but it is very fast (about 40 times faster than the original version) 🙂

---

<div class="post-metadata">

**Author:** ![ufechner7](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/ufechner7/32/51363_2.png) [@ufechner7](https://discourse.julialang.org/u/ufechner7)\
**Post date:** [October 18, 2019, 8:33pm UTC](https://discourse.julialang.org/t/bad-performance-of-group-by-of-dataframes-updated/30061/7 "2019-10-18T20:33:07Z")

</div>

JuliaDB is much worse. At least the last time I tried.

---

<div class="post-metadata">

**Author:** ![bkamins](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/bkamins/32/208538_2.png) [@bkamins](https://discourse.julialang.org/u/bkamins)\
**Post date:** [October 18, 2019, 8:43pm UTC](https://discourse.julialang.org/t/bad-performance-of-group-by-of-dataframes-updated/30061/8 "2019-10-18T20:43:07Z")

</div>

In JuliaDB you cannot avoid recompilation. Your use case is one of the key scenarios where type-instability of DataFrames.jl pays off 😄.

---

<div class="post-metadata">

**Author:** ![bkamins](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/bkamins/32/208538_2.png) [@bkamins](https://discourse.julialang.org/u/bkamins)\
**Post date:** [October 18, 2019, 8:45pm UTC](https://discourse.julialang.org/t/bad-performance-of-group-by-of-dataframes-updated/30061/9 "2019-10-18T20:45:15Z")

</div>

Regarding the fix - yes - this will also work, but if you do not want to specialize on this `NamedTuple` then equally you could just avoid creating it in the first place (I left creation of the named tuple, as removing it required to much refactoring of your original code so I introduced this “NamedTuple deconstructing” loop).

---

<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:** [October 18, 2019, 8:47pm UTC](https://discourse.julialang.org/t/bad-performance-of-group-by-of-dataframes-updated/30061/10 "2019-10-18T20:47:14Z")

</div>

Any intuition for why the anonymous function syntax is faster? I thought it wasn’t supposed to be that way

---

<div class="post-metadata">

**Author:** ![bkamins](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/bkamins/32/208538_2.png) [@bkamins](https://discourse.julialang.org/u/bkamins)\
**Post date:** [October 18, 2019, 9:07pm UTC](https://discourse.julialang.org/t/bad-performance-of-group-by-of-dataframes-updated/30061/11 "2019-10-18T21:07:46Z")

</div>

It is slower than type-stable case, but it does not require recompilation. As the processed data frame is very small the cost of compilation is of orders of magnitude larger than the cost of function execution (even if it is type unstable).

It would be a different case if the data frame had like 10,000,000 rows, as then recompilation cost would be negligible.

---

<div class="post-metadata">

**Author:** ![ufechner7](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/ufechner7/32/51363_2.png) [@ufechner7](https://discourse.julialang.org/u/ufechner7)\
**Post date:** [October 19, 2019, 12:58pm UTC](https://discourse.julialang.org/t/bad-performance-of-group-by-of-dataframes-updated/30061/12 "2019-10-19T12:58:49Z")

</div>

Unluckily the problem re-appeared after fixing a bug in the test-data generation. I have three columns now of type String (they are also used for group\_by), and now the original solution does not work anymore. Well, it helps a little bit, but not always.  
New code:

```julia
# Purpose of this test:
# Reproduce the bad performance of aggregate, when the number and the order of the named_tuple argument changes.

using DataFrames

# This function works fine if all colums are Float64, but not so good if it is a mix
# of String and Float64
function aggregate1(table, agg_clause, @nospecialize(named_tuple), prn=false)
    # println(named_tuple)
    x = []
    for (a,(b, c)) in pairs(named_tuple)
        push!(x, [a, b, c])
    end
    result, t, bytes, gctime, memallocs = @timed by(table, agg_clause, sort=true) do sdf
        r = DataFrame()
        for (a,b,c) in x
            r[!, a] = [c(sdf[!, b])]
        end
        r
    end
    if prn
        println("time: $(round(t, digits=3))")
    end
    return result
end

# Testdata
ALL = Symbol[:CAT_UTRECHT_CNT, :CAT_MOUSE, :WW_OP, :QWE_GRP, :HWR_KT, :CAT_WEIGHT, :HIGH_GRP, :CAT_WEIGHT_C, :CAT_CMI_C, :CAT_MOUSE_C, :CAT_FREIG_WGT_CNT, :CAT_FREIG_WGT_CNT_C, :AMS_CMI_TOT, :CAT_UTRECHT_DIS_CNT, :AMS_CMI_HRV, 
:GVZ_WXK, :AMS_CMI_DIFF, :CAT_UTRECHT_CNT_C]

agg_clause = Symbol[:HIGH_GRP, :QWE_GRP, :WW_OP]
NAMED_TUPLE = (CAT_WEIGHT_C_T = :CAT_WEIGHT_C => maximum, CAT_UTRECHT_CNT_T = :CAT_UTRECHT_CNT => maximum, CAT_CMI_C_T = :CAT_CMI_C => maximum, 
                 CAT_MOUSE_C_T = :CAT_MOUSE_C => maximum, CAT_FREIG_WGT_CNT_T = :CAT_FREIG_WGT_CNT => maximum, 
                 CAT_FREIG_WGT_CNT_C_T = :CAT_FREIG_WGT_CNT_C => maximum, CAT_MOUSE_T = :CAT_MOUSE => maximum, CAT_WEIGHT_C = :CAT_WEIGHT_C => sum, 
                 CAT_UTRECHT_CNT_C = :CAT_UTRECHT_CNT_C => sum, WW_OP = :WW_OP => maximum, CAT_FREIG_WGT_CNT = :CAT_FREIG_WGT_CNT => sum, 
                 HWR_KT = :HWR_KT => maximum, CAT_WEIGHT = :CAT_WEIGHT => sum, CAT_MOUSE = :CAT_MOUSE => sum, AMS_CMI_TOT = :AMS_CMI_TOT => sum, 
                 CAT_FREIG_WGT_CNT_C = :CAT_FREIG_WGT_CNT_C => sum, CAT_UTRECHT_DIS_CNT = :CAT_UTRECHT_DIS_CNT => sum, CAT_CMI_C = :CAT_CMI_C => sum, 
                 CAT_MOUSE_C = :CAT_MOUSE_C => sum, AMS_CMI_HRV = :AMS_CMI_HRV => sum, GVZ_WXK = :GVZ_WXK => sum, 
                 CAT_UTRECHT_CNT = :CAT_UTRECHT_CNT => sum, AMS_CMI_DIFF = :AMS_CMI_DIFF => sum)

# add a column with random Float64 values and the name col to the table
function add_column(table, col::Symbol)
    COL = rand(Float64, nrow(table))
    table[!, col] = COL 
end

# add a column with random Float64 values and the name col to the table
function add_string_column(table, col::Symbol)
    COL = String[]
    for i in 1:nrow(table)
        if i % 3 == 0 
            push!(COL, "HUND")
        elseif i % 3 == 1
            push!(COL, "HASE")
        elseif i % 3 == 2
            push!(COL, "IGEL")
        end
    end
    table[!, col] = COL 
end

# build a named tuple with the given length, using NAMED_TUPLE as input
function make_named_tuple(len)
    nt = NAMED_TUPLE
    @assert len <= length(nt)
    t_names = propertynames(nt)
    pairs = Vector{Pair}(undef, len)
    indices = []
    new_names = []
    for i in 1:len
        index = rand(setdiff(1:length(nt), indices))
        push!(indices, index)
        pairs[i] = values(nt)[index]
        push!(new_names, t_names[index])
    end
    return NamedTuple{Tuple(new_names)}(Tuple(pairs))
end

# create data frame with 30 rows
jtab = DataFrame([1:30])

# add columns, using random values
for col in ALL
    global jtab
    if col in agg_clause
        add_string_column(jtab, col)
    else
        add_column(jtab, col)
    end
end

# create random aggregates of this dataframe and determine the time
for i = 1:length(NAMED_TUPLE)
    nt = make_named_tuple(i)
    res = aggregate1(jtab, agg_clause, nt, true)
end
nothing

```

Test result, second run:

```julia
time: 0.011
time: 0.0
time: 0.0
time: 0.051
time: 0.0
time: 0.0
time: 0.0
time: 0.0
time: 0.0
time: 0.0
time: 0.0
time: 0.05
time: 0.053
time: 0.001
time: 0.062
time: 0.051
time: 0.055
time: 0.058
time: 0.049
time: 0.049
time: 0.058
time: 0.049
time: 0.05

```

Now it is sometimes recompiling and sometimes not. Any idea how to fix this?

---

<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:** [October 19, 2019, 1:05pm UTC](https://discourse.julialang.org/t/bad-performance-of-group-by-of-dataframes-updated/30061/13 "2019-10-19T13:05:31Z")

</div>

Not sure if it’s recompiling or GCing.

There’s way too much code for me to consider looking into it. Can you maybe describe succinctly what you are trying to do and give an MWE?

Maybe something like show how your input looks like and how you want your output?

---

<div class="post-metadata">

**Author:** ![ufechner7](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/ufechner7/32/51363_2.png) [@ufechner7](https://discourse.julialang.org/u/ufechner7)\
**Post date:** [October 19, 2019, 1:24pm UTC](https://discourse.julialang.org/t/bad-performance-of-group-by-of-dataframes-updated/30061/14 "2019-10-19T13:24:15Z")

</div>

Well, I construct a dataframe with colums of type String and type Float64. Than I do a group\_by operation on the three string colums, aggregating the Float64 numbers with either sum or maximum.

That’s it.

The tricky part is that the columns selected for the group\_by operation is random. And this is sometimes causing a recompilation.

---

<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:** [October 19, 2019, 1:28pm UTC](https://discourse.julialang.org/t/bad-performance-of-group-by-of-dataframes-updated/30061/15 "2019-10-19T13:28:16Z")

</div>

So your function selects at random one, two, or three String columns as the group-by column and compute either sum or max chosen at random as well?

---

<div class="post-metadata">

**Author:** ![ufechner7](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/ufechner7/32/51363_2.png) [@ufechner7](https://discourse.julialang.org/u/ufechner7)\
**Post date:** [October 19, 2019, 1:31pm UTC](https://discourse.julialang.org/t/bad-performance-of-group-by-of-dataframes-updated/30061/16 "2019-10-19T13:31:09Z")

</div>

Not exactly. The named tuples, used for the aggregation look like this:

```julia
julia> include("local_tests/test_aggregate2.jl")
(CAT_CMI_C_T = :CAT_CMI_C => maximum,)
time: 0.011
(CAT_UTRECHT_DIS_CNT = :CAT_UTRECHT_DIS_CNT => sum, GVZ_WXK = :GVZ_WXK => sum)
time: 0.0
(CAT_CMI_C_T = :CAT_CMI_C => maximum, CAT_UTRECHT_CNT_T = :CAT_UTRECHT_CNT => maximum, CAT_UTRECHT_CNT = :CAT_UTRECHT_CNT => sum)
time: 0.0
(CAT_MOUSE_C = :CAT_MOUSE_C => sum, CAT_CMI_C_T = :CAT_CMI_C => maximum, CAT_FREIG_WGT_CNT = :CAT_FREIG_WGT_CNT => sum, GVZ_WXK = :GVZ_WXK => sum)
time: 0.046
(CAT_CMI_C_T = :CAT_CMI_C => maximum, CAT_MOUSE_C = :CAT_MOUSE_C => sum, AMS_CMI_DIFF = :AMS_CMI_DIFF => sum, CAT_FREIG_WGT_CNT_C_T = :CAT_FREIG_WGT_CNT_C => maximum, CAT_WEIGHT_C = :CAT_WEIGHT_C => sum)
time: 0.0
(CAT_UTRECHT_DIS_CNT = :CAT_UTRECHT_DIS_CNT => sum, CAT_MOUSE_C = :CAT_MOUSE_C => sum, CAT_FREIG_WGT_CNT_C = :CAT_FREIG_WGT_CNT_C => sum, CAT_FREIG_WGT_CNT_T = :CAT_FREIG_WGT_CNT => maximum, CAT_WEIGHT = :CAT_WEIGHT => sum, GVZ_WXK = :GVZ_WXK => sum)
time: 0.0
(CAT_FREIG_WGT_CNT_T = :CAT_FREIG_WGT_CNT => maximum, CAT_CMI_C = :CAT_CMI_C => sum, AMS_CMI_HRV = :AMS_CMI_HRV => sum, CAT_UTRECHT_CNT = :CAT_UTRECHT_CNT => sum, WW_OP = :WW_OP => maximum, CAT_UTRECHT_CNT_T = :CAT_UTRECHT_CNT => maximum, CAT_FREIG_WGT_CNT_C_T = :CAT_FREIG_WGT_CNT_C => maximum)
time: 0.048
(WW_OP = :WW_OP => maximum, CAT_MOUSE_C_T = :CAT_MOUSE_C => maximum, AMS_CMI_HRV = :AMS_CMI_HRV => sum, CAT_MOUSE_C = :CAT_MOUSE_C => sum, CAT_FREIG_WGT_CNT_T = :CAT_FREIG_WGT_CNT => maximum, CAT_UTRECHT_CNT = :CAT_UTRECHT_CNT => sum, CAT_CMI_C_T = :CAT_CMI_C => maximum, CAT_UTRECHT_DIS_CNT = :CAT_UTRECHT_DIS_CNT => sum)
time: 0.0
(CAT_MOUSE = :CAT_MOUSE => sum, AMS_CMI_TOT = :AMS_CMI_TOT => sum, CAT_WEIGHT_C = :CAT_WEIGHT_C => sum, CAT_UTRECHT_DIS_CNT = :CAT_UTRECHT_DIS_CNT => sum, CAT_CMI_C_T = :CAT_CMI_C => maximum, CAT_UTRECHT_CNT_C = :CAT_UTRECHT_CNT_C => sum, GVZ_WXK = :GVZ_WXK => sum, CAT_FREIG_WGT_CNT = :CAT_FREIG_WGT_CNT => sum, CAT_UTRECHT_CNT = :CAT_UTRECHT_CNT => sum)
time: 0.0
(CAT_UTRECHT_DIS_CNT = :CAT_UTRECHT_DIS_CNT => sum, CAT_UTRECHT_CNT_C = :CAT_UTRECHT_CNT_C => sum, CAT_WEIGHT_C = :CAT_WEIGHT_C => sum, CAT_CMI_C_T = :CAT_CMI_C => maximum, CAT_MOUSE_C_T = :CAT_MOUSE_C => maximum, CAT_FREIG_WGT_CNT = :CAT_FREIG_WGT_CNT => sum, CAT_MOUSE_C = :CAT_MOUSE_C => sum, CAT_WEIGHT = :CAT_WEIGHT => sum, AMS_CMI_DIFF = :AMS_CMI_DIFF => sum, CAT_FREIG_WGT_CNT_T = :CAT_FREIG_WGT_CNT => maximum)
time: 0.0
(CAT_FREIG_WGT_CNT = :CAT_FREIG_WGT_CNT => sum, AMS_CMI_HRV = :AMS_CMI_HRV => sum, CAT_FREIG_WGT_CNT_T = :CAT_FREIG_WGT_CNT => maximum, GVZ_WXK = :GVZ_WXK => sum, AMS_CMI_TOT = :AMS_CMI_TOT => sum, CAT_CMI_C_T = :CAT_CMI_C => maximum, CAT_MOUSE = :CAT_MOUSE => sum, CAT_FREIG_WGT_CNT_C_T = :CAT_FREIG_WGT_CNT_C => maximum, CAT_UTRECHT_CNT_T = :CAT_UTRECHT_CNT => maximum, CAT_WEIGHT_C = :CAT_WEIGHT_C => sum, CAT_UTRECHT_CNT = :CAT_UTRECHT_CNT => sum)
time: 0.0
(CAT_FREIG_WGT_CNT_C_T = :CAT_FREIG_WGT_CNT_C => maximum, GVZ_WXK = :GVZ_WXK => sum, CAT_UTRECHT_CNT_C = :CAT_UTRECHT_CNT_C => sum, CAT_WEIGHT = :CAT_WEIGHT => sum, AMS_CMI_HRV = :AMS_CMI_HRV => sum, CAT_WEIGHT_C_T = :CAT_WEIGHT_C => maximum, CAT_UTRECHT_CNT = :CAT_UTRECHT_CNT => sum, CAT_CMI_C_T = :CAT_CMI_C => maximum, CAT_CMI_C = :CAT_CMI_C => sum, CAT_UTRECHT_DIS_CNT = :CAT_UTRECHT_DIS_CNT => sum, CAT_MOUSE = :CAT_MOUSE => sum, WW_OP = :WW_OP => maximum)
time: 0.0
(AMS_CMI_TOT = :AMS_CMI_TOT => sum, CAT_WEIGHT_C_T = :CAT_WEIGHT_C => maximum, CAT_MOUSE_T = :CAT_MOUSE => maximum, CAT_UTRECHT_CNT = :CAT_UTRECHT_CNT => sum, AMS_CMI_HRV = :AMS_CMI_HRV => sum, CAT_FREIG_WGT_CNT_C_T = :CAT_FREIG_WGT_CNT_C => maximum, CAT_FREIG_WGT_CNT_C = :CAT_FREIG_WGT_CNT_C => sum, CAT_FREIG_WGT_CNT_T = :CAT_FREIG_WGT_CNT => maximum, WW_OP = :WW_OP => maximum, CAT_WEIGHT_C = :CAT_WEIGHT_C => sum, HWR_KT = :HWR_KT => maximum, GVZ_WXK = :GVZ_WXK => sum, CAT_UTRECHT_CNT_T = :CAT_UTRECHT_CNT => maximum)
time: 0.0
(CAT_WEIGHT_C = :CAT_WEIGHT_C => sum, CAT_WEIGHT_C_T = :CAT_WEIGHT_C => maximum, AMS_CMI_DIFF = :AMS_CMI_DIFF => sum, AMS_CMI_TOT = :AMS_CMI_TOT => sum, CAT_UTRECHT_DIS_CNT = :CAT_UTRECHT_DIS_CNT => sum, CAT_FREIG_WGT_CNT_T = :CAT_FREIG_WGT_CNT => maximum, CAT_UTRECHT_CNT = :CAT_UTRECHT_CNT => sum, CAT_FREIG_WGT_CNT_C_T = :CAT_FREIG_WGT_CNT_C => maximum, CAT_CMI_C = :CAT_CMI_C => sum, CAT_UTRECHT_CNT_T = :CAT_UTRECHT_CNT => maximum, CAT_MOUSE_T = :CAT_MOUSE => maximum, CAT_FREIG_WGT_CNT = :CAT_FREIG_WGT_CNT => sum, CAT_MOUSE_C = :CAT_MOUSE_C => sum, GVZ_WXK = :GVZ_WXK => sum)
time: 0.046
(CAT_MOUSE = :CAT_MOUSE => sum, GVZ_WXK = :GVZ_WXK => sum, WW_OP = :WW_OP => maximum, CAT_WEIGHT = :CAT_WEIGHT => sum, CAT_WEIGHT_C_T = :CAT_WEIGHT_C => maximum, CAT_FREIG_WGT_CNT = :CAT_FREIG_WGT_CNT => sum, CAT_FREIG_WGT_CNT_C_T = :CAT_FREIG_WGT_CNT_C => maximum, AMS_CMI_DIFF = :AMS_CMI_DIFF => sum, HWR_KT = :HWR_KT => maximum, AMS_CMI_TOT = :AMS_CMI_TOT => sum, CAT_UTRECHT_CNT_T = :CAT_UTRECHT_CNT => maximum, CAT_UTRECHT_DIS_CNT = :CAT_UTRECHT_DIS_CNT => sum, CAT_MOUSE_C_T = :CAT_MOUSE_C => maximum, CAT_MOUSE_T = :CAT_MOUSE => maximum, CAT_MOUSE_C = :CAT_MOUSE_C => sum)
time: 0.001
(CAT_WEIGHT_C_T = :CAT_WEIGHT_C => maximum, CAT_FREIG_WGT_CNT_C = :CAT_FREIG_WGT_CNT_C => sum, CAT_CMI_C = :CAT_CMI_C => sum, CAT_FREIG_WGT_CNT = :CAT_FREIG_WGT_CNT => sum, CAT_FREIG_WGT_CNT_T = :CAT_FREIG_WGT_CNT => maximum, AMS_CMI_TOT = :AMS_CMI_TOT => sum, CAT_UTRECHT_CNT_T = :CAT_UTRECHT_CNT => maximum, CAT_UTRECHT_CNT_C = :CAT_UTRECHT_CNT_C => sum, CAT_FREIG_WGT_CNT_C_T = :CAT_FREIG_WGT_CNT_C => maximum, WW_OP = :WW_OP => maximum, CAT_MOUSE = :CAT_MOUSE => sum, CAT_MOUSE_T = :CAT_MOUSE => maximum, GVZ_WXK = :GVZ_WXK => sum, AMS_CMI_DIFF = :AMS_CMI_DIFF => sum, CAT_UTRECHT_DIS_CNT = :CAT_UTRECHT_DIS_CNT => sum, CAT_CMI_C_T = :CAT_CMI_C => maximum)
time: 0.0
(CAT_UTRECHT_CNT_T = :CAT_UTRECHT_CNT => maximum, CAT_CMI_C = :CAT_CMI_C => sum, CAT_FREIG_WGT_CNT_C_T = :CAT_FREIG_WGT_CNT_C => maximum, CAT_CMI_C_T = :CAT_CMI_C => maximum, CAT_WEIGHT_C_T = :CAT_WEIGHT_C => maximum, CAT_MOUSE_C = :CAT_MOUSE_C => sum, AMS_CMI_HRV = :AMS_CMI_HRV => sum, CAT_FREIG_WGT_CNT_C = :CAT_FREIG_WGT_CNT_C => sum, AMS_CMI_TOT = :AMS_CMI_TOT => sum, CAT_FREIG_WGT_CNT = :CAT_FREIG_WGT_CNT => sum, CAT_UTRECHT_CNT = :CAT_UTRECHT_CNT => sum, CAT_MOUSE_C_T = :CAT_MOUSE_C => maximum, CAT_MOUSE_T = :CAT_MOUSE => maximum, HWR_KT = :HWR_KT => maximum, CAT_WEIGHT = :CAT_WEIGHT => sum, GVZ_WXK = :GVZ_WXK => sum, AMS_CMI_DIFF = :AMS_CMI_DIFF => sum)
time: 0.046
(CAT_WEIGHT_C_T = :CAT_WEIGHT_C => maximum, CAT_MOUSE_C_T = :CAT_MOUSE_C => maximum, CAT_UTRECHT_CNT_T = :CAT_UTRECHT_CNT => maximum, GVZ_WXK = :GVZ_WXK => sum, CAT_WEIGHT = :CAT_WEIGHT => sum, AMS_CMI_DIFF = :AMS_CMI_DIFF => sum, HWR_KT = :HWR_KT => maximum, CAT_FREIG_WGT_CNT_C_T = :CAT_FREIG_WGT_CNT_C => maximum, AMS_CMI_TOT = :AMS_CMI_TOT => sum, CAT_WEIGHT_C = :CAT_WEIGHT_C => sum, CAT_MOUSE_C = :CAT_MOUSE_C => sum, CAT_FREIG_WGT_CNT_C = :CAT_FREIG_WGT_CNT_C => sum, CAT_CMI_C = :CAT_CMI_C => sum, AMS_CMI_HRV = :AMS_CMI_HRV => sum, CAT_FREIG_WGT_CNT = :CAT_FREIG_WGT_CNT => sum, CAT_MOUSE = :CAT_MOUSE => sum, CAT_UTRECHT_DIS_CNT = :CAT_UTRECHT_DIS_CNT => sum, CAT_MOUSE_T = :CAT_MOUSE => maximum)
time: 0.049
(CAT_WEIGHT = :CAT_WEIGHT => sum, CAT_WEIGHT_C = :CAT_WEIGHT_C => sum, CAT_CMI_C = :CAT_CMI_C => sum, CAT_UTRECHT_CNT_T = :CAT_UTRECHT_CNT => maximum, AMS_CMI_TOT = :AMS_CMI_TOT => sum, AMS_CMI_DIFF = :AMS_CMI_DIFF => sum, CAT_UTRECHT_CNT_C = :CAT_UTRECHT_CNT_C => sum, CAT_MOUSE_C = :CAT_MOUSE_C => sum, CAT_FREIG_WGT_CNT_T = :CAT_FREIG_WGT_CNT => maximum, AMS_CMI_HRV = :AMS_CMI_HRV => sum, CAT_FREIG_WGT_CNT_C_T = :CAT_FREIG_WGT_CNT_C => maximum, CAT_UTRECHT_DIS_CNT = :CAT_UTRECHT_DIS_CNT => sum, WW_OP = :WW_OP => maximum, CAT_MOUSE_T = :CAT_MOUSE => maximum, GVZ_WXK = :GVZ_WXK => sum, CAT_WEIGHT_C_T = :CAT_WEIGHT_C => maximum, HWR_KT = :HWR_KT => maximum, CAT_MOUSE = :CAT_MOUSE => sum, CAT_MOUSE_C_T = :CAT_MOUSE_C => maximum)
time: 0.048
(HWR_KT = :HWR_KT => maximum, CAT_FREIG_WGT_CNT_C = :CAT_FREIG_WGT_CNT_C => sum, CAT_UTRECHT_CNT = :CAT_UTRECHT_CNT => sum, CAT_UTRECHT_DIS_CNT = :CAT_UTRECHT_DIS_CNT => sum, CAT_WEIGHT_C_T = :CAT_WEIGHT_C => maximum, AMS_CMI_DIFF = :AMS_CMI_DIFF => sum, CAT_WEIGHT = :CAT_WEIGHT => sum, CAT_FREIG_WGT_CNT = :CAT_FREIG_WGT_CNT => sum, CAT_FREIG_WGT_CNT_T = :CAT_FREIG_WGT_CNT => maximum, GVZ_WXK = :GVZ_WXK => sum, AMS_CMI_TOT = :AMS_CMI_TOT => sum, CAT_WEIGHT_C = :CAT_WEIGHT_C => sum, CAT_MOUSE = :CAT_MOUSE => sum, CAT_CMI_C = :CAT_CMI_C => sum, WW_OP = :WW_OP => maximum, CAT_UTRECHT_CNT_C = :CAT_UTRECHT_CNT_C => sum, CAT_MOUSE_C = :CAT_MOUSE_C => sum, AMS_CMI_HRV = :AMS_CMI_HRV => sum, CAT_MOUSE_C_T = :CAT_MOUSE_C => maximum, CAT_UTRECHT_CNT_T = :CAT_UTRECHT_CNT => maximum)
time: 0.0
(CAT_UTRECHT_CNT_C = :CAT_UTRECHT_CNT_C => sum, WW_OP = :WW_OP => maximum, CAT_FREIG_WGT_CNT_T = :CAT_FREIG_WGT_CNT => maximum, CAT_UTRECHT_CNT = :CAT_UTRECHT_CNT => sum, HWR_KT = :HWR_KT => maximum, CAT_WEIGHT_C_T = :CAT_WEIGHT_C => maximum, CAT_MOUSE_T = :CAT_MOUSE => maximum, CAT_CMI_C = :CAT_CMI_C => sum, CAT_MOUSE_C_T = :CAT_MOUSE_C => maximum, GVZ_WXK = :GVZ_WXK => sum, CAT_CMI_C_T = :CAT_CMI_C => maximum, AMS_CMI_DIFF = :AMS_CMI_DIFF => sum, AMS_CMI_HRV = :AMS_CMI_HRV => sum, CAT_FREIG_WGT_CNT_C = :CAT_FREIG_WGT_CNT_C => sum, CAT_FREIG_WGT_CNT_C_T = :CAT_FREIG_WGT_CNT_C => maximum, CAT_UTRECHT_DIS_CNT = :CAT_UTRECHT_DIS_CNT => sum, CAT_WEIGHT_C = :CAT_WEIGHT_C => sum, AMS_CMI_TOT = :AMS_CMI_TOT => sum, CAT_UTRECHT_CNT_T = :CAT_UTRECHT_CNT => maximum, CAT_FREIG_WGT_CNT = :CAT_FREIG_WGT_CNT => sum, CAT_WEIGHT = :CAT_WEIGHT => sum)
time: 0.048
(HWR_KT = :HWR_KT => maximum, CAT_FREIG_WGT_CNT_C_T = :CAT_FREIG_WGT_CNT_C => maximum, CAT_MOUSE_T = :CAT_MOUSE => maximum, CAT_UTRECHT_CNT_C = :CAT_UTRECHT_CNT_C => sum, CAT_FREIG_WGT_CNT = :CAT_FREIG_WGT_CNT => sum, CAT_FREIG_WGT_CNT_C = :CAT_FREIG_WGT_CNT_C => sum, CAT_CMI_C_T = :CAT_CMI_C => maximum, CAT_FREIG_WGT_CNT_T = :CAT_FREIG_WGT_CNT => maximum, CAT_CMI_C = :CAT_CMI_C => sum, CAT_WEIGHT_C = :CAT_WEIGHT_C => sum, CAT_MOUSE_C_T = :CAT_MOUSE_C => maximum, CAT_MOUSE_C = :CAT_MOUSE_C => sum, CAT_UTRECHT_CNT_T = :CAT_UTRECHT_CNT => maximum, CAT_WEIGHT = :CAT_WEIGHT => sum, GVZ_WXK = :GVZ_WXK => sum, AMS_CMI_TOT = :AMS_CMI_TOT => sum, AMS_CMI_DIFF = :AMS_CMI_DIFF => sum, CAT_UTRECHT_CNT = :CAT_UTRECHT_CNT => sum, WW_OP = :WW_OP => maximum, AMS_CMI_HRV = :AMS_CMI_HRV => sum, CAT_UTRECHT_DIS_CNT = :CAT_UTRECHT_DIS_CNT => sum, CAT_WEIGHT_C_T = :CAT_WEIGHT_C => maximum)
time: 0.0
(HWR_KT = :HWR_KT => maximum, AMS_CMI_HRV = :AMS_CMI_HRV => sum, CAT_WEIGHT_C = :CAT_WEIGHT_C => sum, AMS_CMI_TOT = :AMS_CMI_TOT => sum, CAT_FREIG_WGT_CNT_T = :CAT_FREIG_WGT_CNT => maximum, CAT_MOUSE_C = :CAT_MOUSE_C => sum, CAT_FREIG_WGT_CNT = :CAT_FREIG_WGT_CNT => sum, CAT_CMI_C = :CAT_CMI_C => sum, CAT_UTRECHT_CNT_T = :CAT_UTRECHT_CNT => maximum, CAT_UTRECHT_CNT_C = :CAT_UTRECHT_CNT_C => sum, WW_OP = :WW_OP => maximum, CAT_FREIG_WGT_CNT_C_T = :CAT_FREIG_WGT_CNT_C => maximum, CAT_UTRECHT_CNT = :CAT_UTRECHT_CNT => sum, AMS_CMI_DIFF = :AMS_CMI_DIFF => sum, CAT_FREIG_WGT_CNT_C = :CAT_FREIG_WGT_CNT_C => sum, CAT_WEIGHT_C_T = :CAT_WEIGHT_C => maximum, CAT_MOUSE = :CAT_MOUSE => sum, CAT_CMI_C_T = :CAT_CMI_C => maximum, CAT_MOUSE_C_T = :CAT_MOUSE_C => maximum, CAT_WEIGHT = :CAT_WEIGHT => sum, GVZ_WXK = :GVZ_WXK => sum, CAT_MOUSE_T = :CAT_MOUSE => maximum, CAT_UTRECHT_DIS_CNT = :CAT_UTRECHT_DIS_CNT => sum)
time: 0.0

```

This is artificial data, in reality this are user queries, but what the user queries is kind of random. And this is really recompilation that is sometimes triggered. I turned of the GC to be sure. And the time needed becomes shorter if I use Julia -O0, this is also a hint that the time is needed for recompilation.

---

<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:** [October 19, 2019, 1:47pm UTC](https://discourse.julialang.org/t/bad-performance-of-group-by-of-dataframes-updated/30061/17 "2019-10-19T13:47:23Z")

</div>

You seem to be doing lots of group-by the same set of variables so perhap you can try to group them just once. But it sounds like your group-by variabels can change as well.

Suppose it’s the former, perhaps you can to group by once and then do all those operations to speed things up e.g. (probably not helping)

```julia
jtab_grouped = groupby(jtab, agg_clause)

```

now each `[i]` is a subdataframe

```julia
jtab_grouped[1]

```

and you can use `map(fn, grouped_data_frame)` to apply the same function to each `sub_data_frame`

Another small thing is that

```julia
function add_string_column!(table, col::Symbol) 
  table[!, col] = [("HUND", "HASE", "IGEL")[(i % 3)+1] for i in 1:nrow(table)]
end

```

is more succint.

If u mutate the content then you should add `!` to your function name

---

<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:** [October 19, 2019, 1:52pm UTC](https://discourse.julialang.org/t/bad-performance-of-group-by-of-dataframes-updated/30061/18 "2019-10-19T13:52:31Z")

</div>

> [@ufechner7](#):
>
> function add\_string\_column(table, col::Symbol) COL = String for i in 1:nrow(table) if i % 3 == 0 push!(COL, “HUND”) elseif i % 3 == 1 push!(COL, “HASE”) elseif i % 3 == 2 push!(COL, “IGEL”) end end table[!, col] = COL end

I feel that this is more what you want

```julia
function add_string_column!(table, col::Symbol)
  table[!, col] = rand(["HUND", "HASE", "IGEL"], nrow(table))
end
```

---

<div class="post-metadata">

**Author:** ![ufechner7](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/ufechner7/32/51363_2.png) [@ufechner7](https://discourse.julialang.org/u/ufechner7)\
**Post date:** [October 19, 2019, 7:20pm UTC](https://discourse.julialang.org/t/bad-performance-of-group-by-of-dataframes-updated/30061/19 "2019-10-19T19:20:23Z")

</div>

I am trying to use groupby and map instead of by. But I did not find out how to do that correctly (yet). My current code:

```julia
function aggregate2(table, agg_clause, @nospecialize(named_tuple), prn=false)
    x = []
    for (a,(b, c)) in pairs(named_tuple)
        push!(x, [a, b, c])
    end
    
    table_grouped = groupby(table, agg_clause)
    r = DataFrame()
    for sub_df in table_grouped
        # println(sub_df)
        for (a,b,c) in x
            map(b => c, table_grouped)
        end
    end
    return r
end

```

But how can I combine the results of the map operation to a resulting dataframe?

---

<div class="post-metadata">

**Author:** ![ufechner7](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/ufechner7/32/51363_2.png) [@ufechner7](https://discourse.julialang.org/u/ufechner7)\
**Post date:** [October 19, 2019, 7:45pm UTC](https://discourse.julialang.org/t/bad-performance-of-group-by-of-dataframes-updated/30061/20 "2019-10-19T19:45:06Z")

</div>

Created an issue: [https://github.com/JuliaData/DataFrames.jl/issues/1988](https://github.com/JuliaData/DataFrames.jl/issues/1988)

[Next page](https://discourse.julialang.org/t/bad-performance-of-group-by-of-dataframes-updated/30061.md?page=2)