# Help with performance tuning this dataframe aggregation

**URL:** <https://discourse.julialang.org/t/help-with-performance-tuning-this-dataframe-aggregation/15357>\
**Category:** Performance\
**Created:** [September 22, 2018, 10:04pm UTC](https://discourse.julialang.org/t/help-with-performance-tuning-this-dataframe-aggregation/15357 "2018-09-22T22:04:48Z")\
**Posts on this page:** 11\
**Page:** 1

<div class="post-metadata">

**Author:** ![jonjilla](https://avatars.discourse-cdn.com/v4/letter/j/7ba0ec/32.png) [@jonjilla](https://discourse.julialang.org/u/jonjilla)\
**Post date:** [September 22, 2018, 10:04pm UTC](https://discourse.julialang.org/t/help-with-performance-tuning-this-dataframe-aggregation/15357/1 "2018-09-22T22:04:49Z")

</div>

I’ve been working on an application and have narrowed down the piece that appears to be taking the most time.

in this following code, it’s generating dataframes d,e,f

```julia
l = 10000
data = DataFrame([Int64, Int64, Int64, Int64, Int64],[:x,:y,:z,:m,:n],l)
data.x = rand(1:100,l)
data.y = rand(1:100,l)
data.z = rand(0:1, l)

function looptest(data,loopsize)
	summary = DataFrame([Int64, Int64, Float64],[:cfg, :count, :ave],0)
	for i in 1:loopsize
		for j in 1:loopsize
			cfg = i * j
			data.m = convert.(Int64, floor.(data.x ./ i))
			data.n = convert.(Int64, floor.(data.y ./ j))
			d = by(data, [:m, :n], df -> minimum(df[:z]))
			e = by(data, [:m, :n], nrow)
			f = join(d,e[e.x1 .== cfg,[:m,:n]],on = [:m,:n])
			push!(summary,[cfg size(f,1) mean(f.x1)])
		end
	end 
	return summary
end

julia> @btime looptest(data,4)
  562.855 ms (7902541 allocations: 362.75 MiB)

```

The rest of the code takes about 5ms, but this loop accunts for the majority. Is there something here I can do to improve this performance?

I was thinking at least there should be a way to generate f without the interim e table, but couldn’t figure that out.

Thanks.

---

<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:** [September 23, 2018, 12:23am UTC](https://discourse.julialang.org/t/help-with-performance-tuning-this-dataframe-aggregation/15357/2 "2018-09-23T00:23:26Z")

</div>

You can use a DataFrame constructor in your `by` command. That way you cut the number of DataFrame constructors down.

```julia
p = by(data, [:m, :n], d -> DataFrame(a = minimum(d[:z]), nrow = nrow(d)))

```

Ultimately, its the `join` that’s costing you. I would suggest both DataFrames at once with the constructor above, then subset based on the `:nrow` variable. That should help the performance. Though a DataFramesMeta approach might be easier and faster.

I would say that you should also pre-allocate your `summary` dataframe, since you know the loopsize. that would mean arrays would have to re-size less frequently. However the way DataFrame’s setindex works is a bit weird at the moment, especially with regards to rows. See [this issue](https://github.com/JuliaData/DataFrames.jl/issues/1507).

---

<div class="post-metadata">

**Author:** ![jonjilla](https://avatars.discourse-cdn.com/v4/letter/j/7ba0ec/32.png) [@jonjilla](https://discourse.julialang.org/u/jonjilla)\
**Post date:** [September 23, 2018, 1:56am UTC](https://discourse.julialang.org/t/help-with-performance-tuning-this-dataframe-aggregation/15357/3 "2018-09-23T01:56:40Z")

</div>

Thanks for the help, I couldn’t figure out how to simultaneously get the minimum and the nrow count.

But even so, it’s only slightly better.

```julia
l = 10000
data = DataFrame([Int64, Int64, Int64, Int64, Int64],[:x,:y,:z,:m,:n],l)
data.x = rand(1:100,l)
data.y = rand(1:100,l)
data.z = rand(0:1, l)

function looptest(data,loopsize)
	summary = DataFrame([Int64, Int64, Float64],[:cfg, :count, :ave],0)
	for i in 1:loopsize
		for j in 1:loopsize
			cfg = i * j
			data.m = convert.(Int64, floor.(data.x ./ i))
			data.n = convert.(Int64, floor.(data.y ./ j))
			d = by(data, [:m, :n], df -> minimum(df[:z]))
			e = by(data, [:m, :n], nrow)
			f = join(d,e[e.x1 .== cfg,[:m,:n]],on = [:m,:n])
			push!(summary,[cfg size(f,1) mean(f.x1)])
		end
	end 
	return summary
end

function looptest2(data,loopsize)
	summary = DataFrame([Int64, Int64, Float64],[:cfg, :count, :ave],0)
	for i in 1:loopsize
		for j in 1:loopsize
			cfg = i * j
			data.m = convert.(Int64, floor.(data.x ./ i))
			data.n = convert.(Int64, floor.(data.y ./ j))
			p = by(data, [:m, :n], d -> DataFrame(a = minimum(d[:z]), nrow = nrow(d)))
			push!(summary,[cfg size(f,1) mean(p.a[p.nrow .== cfg,:])])
		end
	end 
	return summary
end

julia> @btime looptest(data,4)
  611.216 ms (7906012 allocations: 362.80 MiB)

julia> @btime looptest2(data,4)
  483.848 ms (4476416 allocations: 208.60 MiB)

```

---

<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:** [September 23, 2018, 2:07am UTC](https://discourse.julialang.org/t/help-with-performance-tuning-this-dataframe-aggregation/15357/4 "2018-09-23T02:07:13Z")

</div>

DataFramesMeta’s grouped operations are more specialized than DataFrames’s ones. See [this PR](https://github.com/JuliaData/DataFrames.jl/pull/1520). So that might be a better solution.

I would try pre-allocating the `summary` DataFrame and then doing

```julia
for k in ncol(summary)
    summary[i + j,k] = ... 
end

```

instead of `push!`

---

<div class="post-metadata">

**Author:** ![jonjilla](https://avatars.discourse-cdn.com/v4/letter/j/7ba0ec/32.png) [@jonjilla](https://discourse.julialang.org/u/jonjilla)\
**Post date:** [September 23, 2018, 2:10am UTC](https://discourse.julialang.org/t/help-with-performance-tuning-this-dataframe-aggregation/15357/5 "2018-09-23T02:10:32Z")

</div>

The push has almost no effect. I tested this before I posted.

Here is the loop without the push. Almost no difference

```julia
function looptest3(data,loopsize)
	for i in 1:loopsize
		for j in 1:loopsize
			cfg = i * j
			data.m = convert.(Int64, floor.(data.x ./ i))
			data.n = convert.(Int64, floor.(data.y ./ j))
			p = by(data, [:m, :n], d -> DataFrame(a = minimum(d[:z]), nrow = nrow(d)))
		end
	end 
end

julia> @btime looptest3(data,4)
  781.232 ms (4476093 allocations: 208.43 MiB)

```

---

<div class="post-metadata">

**Author:** ![jonjilla](https://avatars.discourse-cdn.com/v4/letter/j/7ba0ec/32.png) [@jonjilla](https://discourse.julialang.org/u/jonjilla)\
**Post date:** [September 23, 2018, 2:17am UTC](https://discourse.julialang.org/t/help-with-performance-tuning-this-dataframe-aggregation/15357/6 "2018-09-23T02:17:06Z")

</div>

And here are the two operations by themselves

```julia
julia> @btime let a 
                             push!(summary,[cfg size(f,1) mean(f.x1)])
                         end
  611.815 ns (9 allocations: 288 bytes)

julia> @btime let a
                           p = by(data, [:m, :n], d -> DataFrame(a = minimum(d[:z]), nrow = nrow(d)))
                        end
  11.769 ms (137683 allocations: 5.42 MiB)

```

---

<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:** [September 23, 2018, 2:38am UTC](https://discourse.julialang.org/t/help-with-performance-tuning-this-dataframe-aggregation/15357/7 "2018-09-23T02:38:37Z")

</div>

I’m getting around the same benchmarks for the DataFramesMeta approach.

```julia
julia> function foo(data)
       @linq data |>
       groupby([:m, :n]) |>
       based_on(a = minimum(:z), nrow = length(:z));
       end

```

It’s worth nothing that the `grouping` on its own is about half the time

```julia
julia> function foo(data)
       @linq data |>
       groupby([:m, :n]) |>
       based_on(a = minimum(:z), nrow = length(:z));
       end

julia> function goo(df)
       groupby(df, [:m, :n])
       end

julia> @btime goo($data);
  3.612 ms (87501 allocations: 2.46 MiB)

julia> @btime foo($data);
  7.896 ms (114904 allocations: 4.35 MiB)

julia> @btime by($data, [:m, :n], d -> DataFrame(a = minimum(d[:z]), nrow = nrow(d)));
  8.541 ms (115784 allocations: 4.37 MiB)

```

You could probably improve performance by leaving DataFrames, but at the cost of more complicated code. Sorry I couldn’t be of more help.

Its worth nothing that grouped DataFrame operations are difficult everywhere. In Stata they are very slow given what you expect from the hand-optimized `mata` they use for everything.

---

<div class="post-metadata">

**Author:** ![jonjilla](https://avatars.discourse-cdn.com/v4/letter/j/7ba0ec/32.png) [@jonjilla](https://discourse.julialang.org/u/jonjilla)\
**Post date:** [September 23, 2018, 8:05am UTC](https://discourse.julialang.org/t/help-with-performance-tuning-this-dataframe-aggregation/15357/8 "2018-09-23T08:05:50Z")

</div>

Thanks for the help. I tried a quick test using a simple array but it was actually worse for some reason. Anyways, it’s very helpful to know I wasn’t doing something fundamentally wrong. That’s important information for a newbie like me. It means I’m making progress!

```julia
function looptest5(data,loopsize)
	summary = DataFrame([Int64, Int64, Float64],[:cfg, :count, :ave], loopsize^2)
	summarycounter = 0
	for i in 1:loopsize
		for j in 1:loopsize
			cfg = i * j
			data.m = convert.(Int64, floor.(data.x ./ i))
			data.n = convert.(Int64, floor.(data.y ./ j))
			temp = Array{Int64}(undef,size(data,1))
			local counter = 0
			for a in unique(data.m)
				for b in unique(data[data.m .== a,:n])
					results = data[(data.m .== a) .& (data.n .== b),:z]
					if size(results,1) == cfg
						counter += 1
						temp[counter] = minimum(results)
					end
				end 
			end
			summarycounter += 1
			summary[summarycounter,:cfg] = cfg
			summary[summarycounter,:count] = counter
			summary[summarycounter,:ave] = mean(temp[1:counter])
		end
	end 
	return summary
end

julia> @btime looptest5(data,4)
  1.127 s (529235 allocations: 235.43 MiB)

```

---

<div class="post-metadata">

**Author:** ![laborg](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/laborg/32/5474_2.png) [@laborg](https://discourse.julialang.org/u/laborg)\
**Post date:** [September 23, 2018, 1:17pm UTC](https://discourse.julialang.org/t/help-with-performance-tuning-this-dataframe-aggregation/15357/9 "2018-09-23T13:17:18Z")

</div>

Hi,

I think you are doing this a little bit to complicated. Especially the inner loops over columns `m` and `n` aren’t necessary.

Using Query.jl following solution is much faster:

```julia
function looptest7(data,loopsize)
	summary = DataFrame([Int64, Int64, Float64],[:cfg, :count, :ave],0)
	for i in 1:loopsize
		data.m .= fld.(data.x, i)
		for j in 1:loopsize
			cfg = i * j
			data.n .= fld.(data.y, j)

			x = @from i in data begin
				@group i.z by {i.m, i.n} into g
				@where length(g) == cfg
				@select minimum(g)
				@collect
			end

			push!(summary, [cfg length(x) mean(x)])
		end
	end
	return summary
end

julia> @btime looptest7(data,4)
  42.480 ms (184858 allocations: 13.03 MiB)

```

The not shared `looptest6` was a similar to `looptest5` but without using Query.jl and only using `groupby`. `looptest7` is twice as fast, because `groupby` on DataFrames isn’t typestable… (see: [Type of groupby(df,id) elements are Any](https://discourse.julialang.org/t/type-of-groupby-df-id-elements-are-any/9872))

---

<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:** [September 23, 2018, 3:36pm UTC](https://discourse.julialang.org/t/help-with-performance-tuning-this-dataframe-aggregation/15357/10 "2018-09-23T15:36:00Z")

</div>

You could try [this PR](https://github.com/JuliaData/DataFrames.jl/pull/1520) and have your function return a named tuple instead of a data frame. Not sure whether it will be merged for the next release.

---

<div class="post-metadata">

**Author:** ![jonjilla](https://avatars.discourse-cdn.com/v4/letter/j/7ba0ec/32.png) [@jonjilla](https://discourse.julialang.org/u/jonjilla)\
**Post date:** [September 23, 2018, 5:11pm UTC](https://discourse.julialang.org/t/help-with-performance-tuning-this-dataframe-aggregation/15357/11 "2018-09-23T17:11:28Z")

</div>

Thanks @laborg . That made a huge difference. I guess I’ll have to investigate the Query syntax more. So much to learn!