# Help improving the speed of a DataFrames operation

**URL:** <https://discourse.julialang.org/t/help-improving-the-speed-of-a-dataframes-operation/107615>\
**Category:** Performance\
**Tags:** performance, query, dataframes\
**Created:** [December 14, 2023, 3:20pm UTC](https://discourse.julialang.org/t/help-improving-the-speed-of-a-dataframes-operation/107615 "2023-12-14T15:20:21Z")\
**Posts on this page:** 20\
**Page:** 1

<div class="post-metadata">

**Author:** ![abelsiqueira](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/abelsiqueira/32/47269_2.png) [@abelsiqueira](https://discourse.julialang.org/u/abelsiqueira)\
**Post date:** [December 14, 2023, 3:20pm UTC](https://discourse.julialang.org/t/help-improving-the-speed-of-a-dataframes-operation/107615/1 "2023-12-14T15:20:22Z")

</div>

Hi all, quick question (hopefully) related to doing an operation on DataFrames.  
I am trying to improve the transformation below so any help would be great. More context and MWE are at the bottom.

Thanks for all the help.

```julia
df_flows_subsetting_matching_to(rp, a) =
    @view df_flows[(df_flows.rp .== rp) .&& (df_flows.to .== a), :]

sum_matching(df, time_block, rp) =
    coalesce(sum(df.flow .* length.(Ref(time_block) .∩ df.time_block) * 3.14rp), AffExpr(0.0))

transform!(
    df_cons,
    [:rp, :asset, :time_block] =>
        ByRow((rp, a, T) -> sum_matching(
            df_flows_subsetting_matching_to(a, rp, T), T, rp)
        ) => :incoming_term,
)

```

The number of elements in the `time_block` columns grows to ~8000 in our current cases.

**More context** :

I am essentially trying to compute

\displaystyle \sum\_{T\_f} f\_{(u,a), rp, T\_f} \times |T\_f \cap T\_a| \times \phi(rp) \qquad \forall a, rp, T\_a \in \mathcal{P}\_{a, rp}

The set \mathcal{P}\_{a, rp} depends on a and rp. Each variation is a line of `df_cons` below. Each different value of f is on `df_flows`.

This is related to [Speeding up JuMP model creation with sets that depend on other indexes - #6 by slwu89](https://discourse.julialang.org/t/speeding-up-jump-model-creation-with-sets-that-depend-on-other-indexes/107333/6)

MWE: Including previous code

```julia
using DataFrames, JuMP

df_flows = DataFrame(;
    from = ["p1", "p1", "p2", "p2", "p1", "p2"],
    to = ["d", "d", "d", "d", "d", "d"],
    rp = [1, 1, 1, 1, 2, 2],
    time_block = [1:3, 4:6, 1:2, 3:6, 1:6, 1:6],
    index = 1:6,
)
model = Model()
df_flows.flow = [
    @variable(
        model,
        base_name = "flow[($(row.from),$(row.to)), $(row.rp), $(row.time_block)]"
    ) for row in eachrow(df_flows)
]

df_cons = DataFrame(;
    asset = ["p1", "p1", "p1", "p2", "p2", "d", "d", "d"],
    rp = [1, 1, 2, 1, 2, 1, 1, 2],
    time_block = [1:3, 4:6, 1:6, 1:6, 1:6, 1:4, 5:6, 1:6],
    index = 1:8,
)

df_flows_subsetting_matching_to(rp, a) =
    @view df_flows[(df_flows.rp .== rp) .&& (df_flows.to .== a), :]

sum_matching(df, time_block, rp) =
    coalesce(sum(df.flow .* length.(Ref(time_block) .∩ df.time_block) * 3.14rp), AffExpr(0.0))

transform!(
    df_cons,
    [:rp, :asset, :time_block] =>
        ByRow((rp, a, T) -> sum_matching(
            df_flows_subsetting_matching_to(rp, a), T, rp)
        ) => :incoming_term,
)

```

---

<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:** [December 14, 2023, 3:30pm UTC](https://discourse.julialang.org/t/help-improving-the-speed-of-a-dataframes-operation/107615/2 "2023-12-14T15:30:49Z")

</div>

Good news! You will be able to get _very_ big performance gains!

You have two problems: (1) Type instability from accessing columns inside function and (2) using `df` as a global variable.

Type instability:

DataFrames are “type-unstable”. That is when you write `get_a(df) = df.a`, just can’t infer what type of vector `get_a` returns.

This has a lot of benefits in terms of interactive use and general overhead. But it has performance downsides. Fortunately, the solution is simple. Write functions which accept vectors directly, not the data frame. Instead of `foo(df) = df.a + df.b`, write `foo(a, b)` and call `foo(df.a, df.b)`

Global variable:

Don’t use anything inside functions that are not passed to the functions themselves. These “global variables” also hurt performance.

---

<div class="post-metadata">

**Author:** ![abelsiqueira](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/abelsiqueira/32/47269_2.png) [@abelsiqueira](https://discourse.julialang.org/u/abelsiqueira)\
**Post date:** [December 15, 2023, 9:16am UTC](https://discourse.julialang.org/t/help-improving-the-speed-of-a-dataframes-operation/107615/3 "2023-12-15T09:16:41Z")

</div>

Hi @pdeffebach, I changed it to the following:

```julia
transform!(
        df_cons,
        [:rp, :asset, :time_block] =>
            ByRow(
                (rp, a, time_block) -> begin
                    df = @view df_flows[(df_flows.rp.==rp).&&(df_flows.to.==a), :]
                    coalesce(
                        sum(df.flow .* length.(Ref(time_block) .∩ df.time_block) * 3.14rp),
                        AffExpr(0.0),
                    )
                end,
            ) => :incoming_term,
    )

```

But there are no improvements. The code is wrapped in a function.

---

<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:** [December 15, 2023, 2:44pm UTC](https://discourse.julialang.org/t/help-improving-the-speed-of-a-dataframes-operation/107615/4 "2023-12-15T14:44:39Z")

</div>

No, you aren’t writing functions which take in vectors.

```julia
                    df = @view df_flows[(df_flows.rp.==rp).&&(df_flows.to.==a), :]

```

When you refer to `df_flows` here, that is still a global variable.

I would try to avoid working with `transform` and dataframes at the moment. Just write a function which takes in vectors _only_ and only after you’ve written that, apply it to a dataframe.

Also, a plug for DataFramesMeta.jl which exports the macro `@with` might make the syntax a bit easier.

---

<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:** [December 15, 2023, 2:46pm UTC](https://discourse.julialang.org/t/help-improving-the-speed-of-a-dataframes-operation/107615/5 "2023-12-15T14:46:52Z")

</div>

Also,

```julia
                    df = @view df_flows[(df_flows.rp.==rp).&&(df_flows.to.==a), :]

```

makes me think maybe you want either a grouped operation or maybe to `leftjoin` together `df_cons` and `df_flows`. But I’m not totally sure your use-case.

---

<div class="post-metadata">

**Author:** ![abelsiqueira](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/abelsiqueira/32/47269_2.png) [@abelsiqueira](https://discourse.julialang.org/u/abelsiqueira)\
**Post date:** [December 15, 2023, 3:18pm UTC](https://discourse.julialang.org/t/help-improving-the-speed-of-a-dataframes-operation/107615/6 "2023-12-15T15:18:32Z")

</div>

The code is defined inside a function, say `main`, that I have not written here.  
The `df_flows` variable is defined inside that function.  
Instead of writing functions, I am using the explicit access to the variable `df_flows.rp`. Since `df_flows` is a local variable, I think that using a function is not necessary here, right? I was just trying to make the example readable.

About the use case, it is a left join that I then group. I can try to explain below what I want:

Explanation in join terms:

- `leftjoin(df_cons, df_flows, on = [:rp, :asset => :to])`
- Compute the intersection of the `time_block` of left and right
- Multiply the resulting value by the `flow` column
- Sum `flow` by grouping by `df_cons`’ index

Per row explanation:

- For each row of `df_cons`
- Select/filter `df_flows` by matching `rp = row.rp` and `to = row.asset`
- Compute the intersection of the time blocks
- Multiply the resulting value by the `flow` column
- Sum `flow` and return

My current solution is to not do any use of DataFrames, and just use Dictionaries to store the indices of the non-zero flows. It is slow, but around 10x faster that this version. The full context is [Speeding up JuMP model creation with sets that depend on other indexes - #6 by slwu89](https://discourse.julialang.org/t/speeding-up-jump-model-creation-with-sets-that-depend-on-other-indexes/107333/6)

---

<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:** [December 15, 2023, 3:30pm UTC](https://discourse.julialang.org/t/help-improving-the-speed-of-a-dataframes-operation/107615/7 "2023-12-15T15:30:10Z")

</div>

Any time you do `df_flows.rp`, that’s slow. The goal is to avoid that at all costs. Pass `rp` directly to a function. Keeping a lookup dict of these indices would be a good idea.

---

<div class="post-metadata">

**Author:** ![Dan](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/dan/32/42581_2.png) [@Dan](https://discourse.julialang.org/u/Dan)\
**Post date:** [December 15, 2023, 4:31pm UTC](https://discourse.julialang.org/t/help-improving-the-speed-of-a-dataframes-operation/107615/8 "2023-12-15T16:31:57Z")

</div>

Try:

```julia
transform!(
  df_cons,
  [:rp, :asset, :time_block] =>
    ByRow((rp, a, T) -> begin
        s = AffExpr(0.0)
        for r in eachrow(df_flows)
            r.rp != rp && continue
            r.to != a && continue
            t = length(T ∩ r.time_block)
            s += r.flow * t * 3.14 * rp
        end
        s
    end
  ) => :incoming_term,
)

```

instead of the `transform!` in the OP. Sometimes all the indirection and `@views` cause too many unneeded copies. Especially when `@view`ing calculated masks.

---

<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:** [December 15, 2023, 4:35pm UTC](https://discourse.julialang.org/t/help-improving-the-speed-of-a-dataframes-operation/107615/9 "2023-12-15T16:35:55Z")

</div>

> [@Dan](#):
>
> ```julia
> t = length(T ∩ df_flows.time_block[i])
> 
> ```

As I mentioned above, any time you have `df_flows.time_block`, you are losing performance.

---

<div class="post-metadata">

**Author:** ![Dan](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/dan/32/42581_2.png) [@Dan](https://discourse.julialang.org/u/Dan)\
**Post date:** [December 15, 2023, 4:37pm UTC](https://discourse.julialang.org/t/help-improving-the-speed-of-a-dataframes-operation/107615/10 "2023-12-15T16:37:57Z")

</div>

My suggestions are orthogonal to your comments. So yes, all the global/stability issues are important.

---

<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:** [December 15, 2023, 5:09pm UTC](https://discourse.julialang.org/t/help-improving-the-speed-of-a-dataframes-operation/107615/11 "2023-12-15T17:09:04Z")

</div>

Peter is suggesting that instead of

```julia
sum_matching(df, time_block, rp) =
    coalesce(sum(df.flow .* length.(Ref(time_block) .∩ df.time_block) * 3.14rp), AffExpr(0.0))

```

you have

```julia
sum_matching(df_flow, df_time_block, time_block, rp) =
    coalesce(sum(df_flow .* length.(Ref(time_block) .∩ df_time_block) * 3.14rp), AffExpr(0.0))

```

and call the function as `sum_matching(df.flow, df.time_block, time_block, rp)` instead of `sum_matching(df, time_block, rp)`. When you provide the DataFrame columns as arguments, `sum_matching` can specialise on the types of `df.flow` and `df.time_block`, while `sum_matching(df,...)` can’t do that as it doesn’t know what the types of `df.flow` and `df.time_block` are when the function gets called with just a `DataFrame` as argument.

This is a design decision in DataFrames to allow dynamism and make sure compile times don’t blow up (as they would if you had a type stable `DataFrame{::TypeOfColumn1, ::TypeOfColumn2,...}` object instead). If you insist on passing tables to your functions you either need to have a function barrier (so essentially something like they 4-arg `sum_matching` above inside the 3-arg version) or use a different kind of table, e.g. [GitHub - JuliaData/TypedTables.jl: Simple, fast, column-based storage for data analysis in Julia](https://github.com/JuliaData/TypedTables.jl)

---

<div class="post-metadata">

**Author:** ![Dan](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/dan/32/42581_2.png) [@Dan](https://discourse.julialang.org/u/Dan)\
**Post date:** [December 15, 2023, 5:25pm UTC](https://discourse.julialang.org/t/help-improving-the-speed-of-a-dataframes-operation/107615/12 "2023-12-15T17:25:17Z")

</div>

Another way to phrase this:

```julia
sort( # keep order by index
  combine( # combine flow sub-expressions
    groupby( # group subexpression for each con
      leftjoin(df_cons, df_flows, on=[:rp, :asset => :to];
        makeunique=true), # associate flows with cons
      [:asset, :rp, :time_block, :index]),
    [:time_block, :time_block_1, :flow, :rp] => ((t,t1, fl, rp) ->
      sum(3.14 * length(t[i] ∩ t1[i]) * rp[i] * fl[i] for 
        i in 1:length(fl) if !ismissing(fl[i]); # create expression
        init=AffExpr(0.0))) => :incoming_term), # start with 0.0
  :index)

```

or using Chains.jl package:

```julia
@chain df_cons begin
    leftjoin(df_flows, on=[:rp, :asset => :to], makeunique=true)
    groupby([:asset, :rp, :time_block, :index])
    combine([:time_block, :time_block_1, :flow, :rp] => ((t,t1, fl, rp) ->
      sum(3.14 * length(t[i] ∩ t1[i]) * rp[i] * fl[i] for 
      i in 1:length(fl) if !ismissing(fl[i]);
      init=AffExpr(0.0))) => :incoming_term)
    sort(:index)
end

```

---

<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:** [December 15, 2023, 5:26pm UTC](https://discourse.julialang.org/t/help-improving-the-speed-of-a-dataframes-operation/107615/13 "2023-12-15T17:26:50Z")

</div>

That certainly is another way to phrase it… but it would be better to write this out in a series of discrete steps to make it more readable.

---

<div class="post-metadata">

**Author:** ![Dan](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/dan/32/42581_2.png) [@Dan](https://discourse.julialang.org/u/Dan)\
**Post date:** [December 15, 2023, 6:03pm UTC](https://discourse.julialang.org/t/help-improving-the-speed-of-a-dataframes-operation/107615/14 "2023-12-15T18:03:10Z")

</div>

Because `groupby` - `combine` is multithreaded, this new form may offer a performance advantage.

---

<div class="post-metadata">

**Author:** ![rocco\_sprmnt21](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/rocco_sprmnt21/32/20127_2.png) [@rocco\_sprmnt21](https://discourse.julialang.org/u/rocco_sprmnt21)\
**Post date:** [December 15, 2023, 6:52pm UTC](https://discourse.julialang.org/t/help-improving-the-speed-of-a-dataframes-operation/107615/15 "2023-12-15T18:52:51Z")

</div>

A variant that is entirely inside DataFrames and that calculates only “non-trivial (non-zero length)” data

```julia

df_ij0=innerjoin(df_cons, df_flows, on = [:rp, :asset => :to], makeunique=true) 

v(x)= map(x->@variable(
    model,
    base_name = "flow[($(x[1]),$(x[2])), $(x[3]), $(x[4])]"
), x)

df_ijs=select(df_ij0,[3,6]=>((x,y)->length.(x.∩ y).*3.14)=>:len,
        [5,1,2,6]=>((x...)->v(zip(x...)))=>:flow,[:index]) 

combine(groupby(df_ijs,:index), [1,2]=> (x,y)->sum(x.*y))
#or
# combine(groupby(df_ijt,:index), [1,2]=> (x,y)->x'*y)

```

---

<div class="post-metadata">

**Author:** ![abelsiqueira](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/abelsiqueira/32/47269_2.png) [@abelsiqueira](https://discourse.julialang.org/u/abelsiqueira)\
**Post date:** [December 18, 2023, 1:28pm UTC](https://discourse.julialang.org/t/help-improving-the-speed-of-a-dataframes-operation/107615/16 "2023-12-18T13:28:24Z")

</div>

Hi all, thanks for the suggestions. I tried them that I am still not having significant improvements. The fastest version I have right now is converting to `columntable` and iterating over the lines with `Tables.rows`. I have three versions with that, more or less similar, that end up being about twice as fast as the best normal data frame version.  
Also, I am filtering the null intersection better as well.

Some replies:  
@Dan, adding elements of `df_flows` per row is slow because of the nature of the `flow` column (JuMP expressions).

@pdeffebach and @nilshg , thanks for the clarification. Unfortunately, it is much different from what I have, so maybe I am still doing something wrong.

@Dan and @rocco_sprmnt21, the `join` approaches don’t work for the larger sizes (~100k rows for each DF, at the moment). My VSCode simply shuts down.

This is the fastest version so far:

```julia
tbl_flows = Tables.columntable(df_flows)
tbl_cons = Tables.columntable(df_cons)
incoming = Vector{AffExpr}(undef, length(tbl_cons.asset))
for row in Tables.rows(tbl_cons)
    incoming[row.index] = AffExpr(0.0)
    idx = findall(
        (tbl_flows.rp .== row.rp) .&&
        (tbl_flows.to .== row.asset) .&&
        (last.(tbl_flows.time_block) .≥ row.time_block[1]) .&&
        (first.(tbl_flows.time_block) .≤ row.time_block[end]),
    )
    if length(idx) > 0
        incoming[row.index] = sum(
            tbl_flows.flow[idx] .* length.(Ref(row.time_block) .∩ tbl_flows.time_block[idx]) *
            3.14row.rp,
        )
    end
end

```

---

<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:** [December 18, 2023, 2:42pm UTC](https://discourse.julialang.org/t/help-improving-the-speed-of-a-dataframes-operation/107615/17 "2023-12-18T14:42:12Z")

</div>

I still think that a version with `leftjoin` and DataFramesMeta.jl’s `@with` is going to be the simplest and most effective.

But for your current proposal

```julia
    idx = findall(
        (tbl_flows.rp .== row.rp) .&&
        (tbl_flows.to .== row.asset) .&&
        (last.(tbl_flows.time_block) .≥ row.time_block[1]) .&&
        (first.(tbl_flows.time_block) .≤ row.time_block[end]),

```

This could be re-doing a lot of work over and over again. Are you sure there isn’t a better way to cache this? It’s hard to tell exactly without a full MWE\>

---

<div class="post-metadata">

**Author:** ![abelsiqueira](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/abelsiqueira/32/47269_2.png) [@abelsiqueira](https://discourse.julialang.org/u/abelsiqueira)\
**Post date:** [December 18, 2023, 3:02pm UTC](https://discourse.julialang.org/t/help-improving-the-speed-of-a-dataframes-operation/107615/18 "2023-12-18T15:02:02Z")

</div>

> @pdeffebach: I still think that a version with `leftjoin` and DataFramesMeta.jl’s `@with` is going to be the simplest and most effective.

See my comment above, for ~100k rows in each data frame, my VSCode crashed. Running on terminal gave me segfault.

> It’s hard to tell exactly without a full MWE

Is the code I put on the first post not working or do you mean a large case?

And once more, the context is another problem posted here: [Speeding up JuMP model creation with sets that depend on other indexes](https://discourse.julialang.org/t/speeding-up-jump-model-creation-with-sets-that-depend-on-other-indexes/107333)

I currently don’t use anything close to a data frame. Instead, I have a dictionary to compute the indexes that I have to sum for each row of `df_cons`.  
My expectations are that using a linear structure will be better (`df_cons`). However, computing the `incoming` column of the linear structure is proving to be slower than the dictionary approach.  
I.e., JuMP is slower because I have the dictionaries instead of the linear indexes, but computing the linear indexes are slower than JuMP, so maybe it is what it is.

---

<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:** [December 18, 2023, 3:09pm UTC](https://discourse.julialang.org/t/help-improving-the-speed-of-a-dataframes-operation/107615/19 "2023-12-18T15:09:17Z")

</div>

I think the fact that your `left_join` is not working is indicative of some other problem that’s worth exploring. Is `left_join` trying to create a colossal number of rows? Why could that be? Do your indices not match as much as you thought? Are there `missing` or `NaN` values you have to worry about? Are you joining on a `float` column? What happens if you do `inner_join`?

---

<div class="post-metadata">

**Author:** ![abelsiqueira](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/abelsiqueira/32/47269_2.png) [@abelsiqueira](https://discourse.julialang.org/u/abelsiqueira)\
**Post date:** [December 18, 2023, 3:21pm UTC](https://discourse.julialang.org/t/help-improving-the-speed-of-a-dataframes-operation/107615/20 "2023-12-18T15:21:11Z")

</div>

They don’t match very often, because of the time\_blocks. The number of assets/to values and rp values is small, but the number of time\_blocks is very large.

Ignoring all else, you can thinks of it this way:

```julia
cons_time_blocks = [1:2, 3:4, ..., 997:998, 999:1000]
flow_time_blocks = [1:3, 4:6, ..., 996:999, 1000:1000]

```

I.e., each `time_blocks` array is a partitions of `1:1000`. In this example, the `cons_time_blocks` is the partition taking 2 points at a time, and `flow_time_blocks` is a partition taking 3 points at a time.

Now, for each index of `cons_time_blocks`, I want every `flow_time_blocks` that intersect with it. This is the essence of the problem.

[Next page](https://discourse.julialang.org/t/help-improving-the-speed-of-a-dataframes-operation/107615.md?page=2)