# DataFrames operation scales badly

**URL:** <https://discourse.julialang.org/t/dataframes-operation-scales-badly/16223>\
**Category:** Performance\
**Created:** [October 12, 2018, 9:17am UTC](https://discourse.julialang.org/t/dataframes-operation-scales-badly/16223 "2018-10-12T09:17:35Z")\
**Posts on this page:** 20\
**Page:** 1

<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:** [October 12, 2018, 9:17am UTC](https://discourse.julialang.org/t/dataframes-operation-scales-badly/16223/1 "2018-10-12T09:17:35Z")

</div>

I’m finally making a proper effort to replace my pandas workflows with DataFrames, but currently hitting a massive performance issue which makes me think I’m missing something fundamental.

The following snippet of code calculates some statistics of a DataFrame which is grouped by two columns (organisation and either service level or subservice level). There are c. 200 services and 3,000 subservices in the data set.

When running it at service level, the code takes 0.6 seconds to run, slightly faster than an equivalent pandas code. When moving to the more granular subservice level though, runtime goes up by a factor of almost 100 (code takes 58 seconds), while in pandas the increase is only a factor of 4 (0.8 to 3.5 seconds).

Any ideas why this code scales so badly to a larger number of groups? Apologies that this isn’t really an MWE, I’m hoping someone spots something that’s obviously off - although I could make the data and full code available if that’s helpful.

```julia
function opportunity2(df::DataFrame; level=:service_code)
    
    # Group dataframe by level
    gr = by(rc, [level, :org_code]) do df
              DataFrame(activity = sum(df.activity), 
                        actual_cost = sum(df.actual_cost))
        end

    # Merge on group-level average for activity
    gr = join(gr, by(gr, level) do df
        DataFrame(mean_activity = mean(skipmissing(df.activity)))
            end, on = level);
    
    # Calculate unit cost at aggregation level
    gr[:unit_cost] = gr.actual_cost ./ gr.activity
                
    # Calculate opportunities 
    l = size(gr, 1)
    gr = [gr DataFrame(uc_mean=rand(l), uc_median=rand(l), uc_lq=rand(l), uc_ld=rand(l))]
    
    for s in Set(gr[level])
        cond = (gr[level].==s) 
        gr[cond, :uc_mean] = mean(skipmissing(gr[cond, :].unit_cost))
        gr[cond, :uc_median] = median(skipmissing(gr[cond, :].unit_cost))
        gr[cond, :uc_lq] = quantile(skipmissing(gr[cond, :].unit_cost), 0.25)
        gr[cond, :uc_ld] = quantile(skipmissing(gr[cond, :].unit_cost), 0.1)
    end
    
    gr[:cost_opp_lq] = gr.activity .* max.(gr.unit_cost .- gr.uc_lq, 0.0)
    gr[:cost_opp_ld] = gr.activity .* max.(gr.unit_cost .- gr.uc_ld, 0.0)
    
    return gr
end

```

---

<div class="post-metadata">

**Author:** ![tamasgal](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/tamasgal/32/27946_2.png) [@tamasgal](https://discourse.julialang.org/u/tamasgal)\
**Post date:** [October 12, 2018, 9:34am UTC](https://discourse.julialang.org/t/dataframes-operation-scales-badly/16223/2 "2018-10-12T09:34:50Z")

</div>

I don’t have an idea yet, but my first thought was: why not taking those comments as function names, put the code below them into the functions body and time them to find the bottlenecks? 😉

---

<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:** [October 12, 2018, 9:40am UTC](https://discourse.julialang.org/t/dataframes-operation-scales-badly/16223/3 "2018-10-12T09:40:53Z")

</div>

That’s actually a good point - I was going to try some profiling in Juno, but wrapping individual steps in functions might make it easier!

---

<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 12, 2018, 10:41am UTC](https://discourse.julialang.org/t/dataframes-operation-scales-badly/16223/4 "2018-10-12T10:41:34Z")

</div>

Dataframes group by are not ootimised. See [Group-by performance benchmarks and recommendations](https://discourse.julialang.org/t/group-by-performance-benchmarks-and-recommendations/9313) for some background

---

<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:** [October 12, 2018, 10:53am UTC](https://discourse.julialang.org/t/dataframes-operation-scales-badly/16223/5 "2018-10-12T10:53:26Z")

</div>

Thanks will have a read!

I have followed @tamasgal’s suggestion and benchmarked the different steps separately. Results:

```julia
# Group dataframe by level
403.786 ms --> 7.872 s (~20x)

# Merge on group-level average for activity
  4.607 ms --> 146.446 ms (~30x)

# Calculate unit cost at aggregation level
  572.308 µs --> 17.174 ms (~30x)

# Calculate opportunities
  135.559 ms --> 44.528 s (~328x)

# Total opportunity (the bit after the loop in code above)
  1.409 ms --> 46.898 ms (~33x) 

```

So it appears there’s two things:

Firstly, the simply groupby operation takes 20 times longer, which is in line with the other bits of the code but alone would amount to more than the increase between fewer and more subgroups in the pandas code (here +7s, in pandas total time only goes up by c. 2.5 seconds)

Secondly, the loop runtime completely explodes - and I’m not sure why. In pandas I’m doing this step with a groupby operation, but can’t do the same in DataFrames due to the way missings are handled (in my real code I end up with subgroups which don’t have any data in, causing mean/median/quantile to fail, so I’m testing for that in the loop).

Any ideas why the loop scales so badly?

---

<div class="post-metadata">

**Author:** ![foobar\_lv2](https://avatars.discourse-cdn.com/v4/letter/f/ee59a6/32.png) [@foobar\_lv2](https://discourse.julialang.org/u/foobar_lv2)\
**Post date:** [October 12, 2018, 11:36am UTC](https://discourse.julialang.org/t/dataframes-operation-scales-badly/16223/6 "2018-10-12T11:36:19Z")

</div>

> [@nilshg](#):
>
> gr[cond, :uc\_mean] = mean(skipmissing(gr[cond, :].unit\_cost))

This allocates a new dataframe. Consider

```julia
    for s in Set(gr[level])
        cond = (gr[level].==s)
        subframe = view(gr, cond, :) 
        uc = collect(skipmissing(subframe.unit_cost))
        subframe.uc_mean .= mean(uc)
        subframe.uc_median .= median(uc)
        subframe.uc_lq .= quantile(uc, 0.25)
        subframe.uc_ld .= quantile(uc, 0.1)
    end

```

---

<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:** [October 12, 2018, 11:55am UTC](https://discourse.julialang.org/t/dataframes-operation-scales-badly/16223/7 "2018-10-12T11:55:07Z")

</div>

Thanks, that’s super helpful - cut down execution time of the loop by roughly 60%!

Given that I need the results to be saved in `gr` down the line, I’ve amended the lines in your loop from:

```julia
subframe.uc_mean .= mean(uc)

```

to

```julia
gr[cond, uc_mean] .= mean(uc)

```

as I’m assuming the problem with the allocation happens on the right hand side rather than the left?

---

<div class="post-metadata">

**Author:** ![foobar\_lv2](https://avatars.discourse-cdn.com/v4/letter/f/ee59a6/32.png) [@foobar\_lv2](https://discourse.julialang.org/u/foobar_lv2)\
**Post date:** [October 12, 2018, 12:27pm UTC](https://discourse.julialang.org/t/dataframes-operation-scales-badly/16223/8 "2018-10-12T12:27:59Z")

</div>

Well, `meanuc = mean(uc); subframe.uc_mean .= meanuc` will iterate over the subframe, for `length(findall(cond))` many iterations, and `gr[cond, uc_mean] .= meanuc` will iterate over the entire frame for `length(gr.uc_mean)` many iterations. In other words, no reason to repeat the work spent on `findall(cond)`.

I’m really just counting how often you run over your data (presumably you are bound by memory bandwidth because your data doesn’t fit into cache, your computations are trivial and CPUs are damn good at hiding memory latency), and proposing to reuse / fuse things, whenever there is a one-line way of doing so.

---

<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:** [October 12, 2018, 12:35pm UTC](https://discourse.julialang.org/t/dataframes-operation-scales-badly/16223/9 "2018-10-12T12:35:25Z")

</div>

Hmm, that makes sense (and I can save a further c. 20% with the first approach), but ultimately I will need to write the results to `gr` rather than to `subframe`, no? I might miss something but in your original proposal the mean/median etc values written to `subframe` are not available outside the loop?

---

<div class="post-metadata">

**Author:** ![foobar\_lv2](https://avatars.discourse-cdn.com/v4/letter/f/ee59a6/32.png) [@foobar\_lv2](https://discourse.julialang.org/u/foobar_lv2)\
**Post date:** [October 12, 2018, 12:38pm UTC](https://discourse.julialang.org/t/dataframes-operation-scales-badly/16223/10 "2018-10-12T12:38:28Z")

</div>

> [@nilshg](#):
>
> I might miss something but in your original proposal the mean/median etc values written to `subframe` are not available outside the loop?

Writing to a view mutates the original object. The assignment `gr[cond, :uc_mean] = mean_uc` is effectively constructing a view to write to. Constructing a view is not free when using logical indexing (need to iterate over `cond`), so better reuse it.

You can also play with using `quantile!` and `median!`: First, this avoids temp copies of `uc`, and second it is silly to do three partial sorts on the same `uc` instead of using fact that your last call already performed a partial sort. But I’m not sure which partial sorts are run internally for which invocation, and the partial quicksort can degenerate into a worse complexity class for inputs that are partially sorted in just the wrong way.

---

<div class="post-metadata">

**Author:** ![piever](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/piever/32/1815_2.png) [@piever](https://discourse.julialang.org/u/piever)\
**Post date:** [October 12, 2018, 1:15pm UTC](https://discourse.julialang.org/t/dataframes-operation-scales-badly/16223/11 "2018-10-12T13:15:08Z")

</div>

Out of curiosity, how well / poorly does the performance critical bit fare in IndexedTables?

```julia
    gr = by(rc, [level, :org_code]) do df
              DataFrame(activity = sum(df.activity), 
                        actual_cost = sum(df.actual_cost))
        end

```

would correspond to:

```julia
using IndexedTables
gr = summarize(sum, rc, (level, :org_code), select = (:activity, :actual_cost))

```

And you can translate from `DataFrames` to `IndexedTables` with:

```julia
using DataFrames, IndexedTables, IterableTables
t = table(df)

```

---

<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:** [October 12, 2018, 1:48pm UTC](https://discourse.julialang.org/t/dataframes-operation-scales-badly/16223/12 "2018-10-12T13:48:23Z")

</div>

> [@piever](#):
>
> ```julia
> using Indexed
> Tables gr = summarize(sum, rc, (level, :org_code), select = (:activity, :actual_cost))
> 
> ```

Gives me a MethodError in the `groupby` function that `summarize` calls:

```julia
MethodError: no method matching groupby(::ApplyColwise{typeof(sum)}, ::DataFrame, ::Tuple{Symbol,Symbol}; select=(:activity, :actual_cost), flatten=false)

```

Not 100% sure what’s going on here - `rc` is just a plain `DataFrame` read in through `CSV.read`

---

<div class="post-metadata">

**Author:** ![piever](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/piever/32/1815_2.png) [@piever](https://discourse.julialang.org/u/piever)\
**Post date:** [October 12, 2018, 3:25pm UTC](https://discourse.julialang.org/t/dataframes-operation-scales-badly/16223/13 "2018-10-12T15:25:18Z")

</div>

Sorry, I was a bit unclear, you need to give it the converted version already:

```julia
using DataFrames, IndexedTables, IterableTables
t = table(rc)
gr = summarize(sum, t, (level, :org_code), select = (:activity, :actual_cost))

```

EDIT: just to clarify, IndexedTables is a distinct framework from DataFrames (though things can be converted easily from one to the other). I’m not necessarily recommending that you use it instead, but given that it uses a very different algorithm from DataFrames for grouping, I was curious to see some performance comparison in real life use.

---

<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:** [October 12, 2018, 4:30pm UTC](https://discourse.julialang.org/t/dataframes-operation-scales-badly/16223/14 "2018-10-12T16:30:33Z")

</div>

Seems to be doing worse - just the `summarize` vs `by` is c. 1.5 seconds vs. 0.5 seconds, but for some reason the conversion via table takes extremely long, so any speed difference would be immaterial (although I suppose I could work with IndexedTables throughout so the conversion cost wouldn’t matter).

So to clarify my takeaway from this thread is that currently there is no fast way of doing large-ish groupby operations where there are a few thousand groups in DataFrames?

---

<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 12, 2018, 4:45pm UTC](https://discourse.julialang.org/t/dataframes-operation-scales-badly/16223/15 "2018-10-12T16:45:58Z")

</div>

If you want to be extra thorough, you could always checkout PR [#1520](https://github.com/JuliaData/DataFrames.jl/pull/1520) by @nalimilan. It provides a lot of perfomance improvements for this kind of operation, and is pretty far along, I think.

---

<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 12, 2018, 4:46pm UTC](https://discourse.julialang.org/t/dataframes-operation-scales-badly/16223/16 "2018-10-12T16:46:31Z")

</div>

See: [https://github.com/JuliaData/DataFrames.jl/pull/1520](https://github.com/JuliaData/DataFrames.jl/pull/1520) and [https://github.com/JuliaData/DataFramesMeta.jl/pull/101](https://github.com/JuliaData/DataFramesMeta.jl/pull/101) for relevant PRs.

---

<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:** [October 12, 2018, 7:01pm UTC](https://discourse.julialang.org/t/dataframes-operation-scales-badly/16223/17 "2018-10-12T19:01:07Z")

</div>

You could also try to use [Query.jl](https://github.com/queryverse/Query.jl) for this. I’m not sure it would do well on this kind of problem, but who knows, would be great if you could run this code on your dataset and report back 🙂

For the first group step, the simplest code would look like this:

```julia
gr = rc |>
    @groupby({_.subservice, _.org_code}) |>
    @map({key(_).subservice, key(_).org_code, activity = sum(_.activity), actual_cost = sum(_.actual_cost)}) |>
    DataFrame

```

A more efficient version would only put the two columns that you actually need into the groups:

```julia
gr = rc |>
    @groupby({_.subservice, _.org_code}, {_.activity, _.actual_cost}) |>
    @map({key(_).subservice, key(_).org_code, activity = sum(_.activity), actual_cost = sum(_.actual_cost)}) |>
    DataFrame

```

Note that you’ll have to hard code whether you want to group by the sub service or service level column.

---

<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:** [October 13, 2018, 10:50am UTC](https://discourse.julialang.org/t/dataframes-operation-scales-badly/16223/18 "2018-10-13T10:50:54Z")

</div>

Can you try with my `nl/grouping2` branch (the one @bkamins mentioned)? `]add DataFrames#nl/grouping2` should do the trick. Then in code like `DataFrame(activity = sum(df.activity), actual_cost = sum(df.actual_cost))` you can just drop the `DataFrame` part to return a `NamedTuple`, which is much faster to create. That PR currently triggers a crash due to a bug in Julia, but since that’s a corner case maybe we can merge it anyway.

But that won’t help with the majority of the time, which is spent in the loop (almost completely outside of DataFrames code, using vector operations). It sounds too bad to have to write this manually. Can’t you use `by` instead, but instead of calling `quantile` directly use an anonymous function wrapper which returns `NaN` when `all(ismissing, x)`? Also, you can use `quantile(x, [0.1, 0.25, 0.5])` instead of doing the work three times.

You haven’t mentioned what’s the type of the grouping column. If that’s a string, doing `gr[level].==s` for each group is going to be really slow. If that’s an integer it should be less of a problem. But DataFrames’ `by` uses a dict for that which avoids going over the full vector for each group.

The fact that `median` and `quantile` throw an error in this case is annoying. We should do something about it, maybe just by adding an argument specifying which value should be returned when it’s empty. The problem has been discussed in [this issue](https://github.com/JuliaLang/julia/issues/19444).

---

<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:** [October 13, 2018, 7:24pm UTC](https://discourse.julialang.org/t/dataframes-operation-scales-badly/16223/19 "2018-10-13T19:24:02Z")

</div>

Thanks all, that’s a ton of helpful additional things to try out, which I’ll definitely do and report back - I’m really committed to finally ditch pandas, which is pretty much the only thing that ties me to Python at this stage, so I’ll try what I can to get this to an acceptable level of performance.

Just one question - how does the workflow with different versions of the same package work practically? Is the tagged version of DataFrames removed when I move to the grouping branch? Or do then have different verions installed simultaneously and somehow need to switch between them?

Also @nalimilan I’m indeed grouping on strings, so I suppose one way to improve things here would be to create a lookup table and then replace the strings with `Int`s for the groupby and then re-replace them at some point later.

---

<div class="post-metadata">

**Author:** ![piever](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/piever/32/1815_2.png) [@piever](https://discourse.julialang.org/u/piever)\
**Post date:** [October 13, 2018, 7:39pm UTC](https://discourse.julialang.org/t/dataframes-operation-scales-badly/16223/20 "2018-10-13T19:39:44Z")

</div>

If you have a limited set of strings that are repeated very often, you could probably use [CategoricalArrays](https://github.com/JuliaData/CategoricalArrays.jl) which gives you an automated way of doing what you suggest (you don’t need to manually perform any replacement).

[Next page](https://discourse.julialang.org/t/dataframes-operation-scales-badly/16223.md?page=2)