# How to speed up this DataFrame operation

**URL:** <https://discourse.julialang.org/t/how-to-speed-up-this-dataframe-operation/57505>\
**Category:** Data\
**Tags:** performance\
**Created:** [March 18, 2021, 11:55pm UTC](https://discourse.julialang.org/t/how-to-speed-up-this-dataframe-operation/57505 "2021-03-18T23:55:40Z")\
**Posts on this page:** 12\
**Page:** 1

<div class="post-metadata">

**Author:** ![amrods](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/amrods/32/2543_2.png) [@amrods](https://discourse.julialang.org/u/amrods)\
**Post date:** [March 18, 2021, 11:55pm UTC](https://discourse.julialang.org/t/how-to-speed-up-this-dataframe-operation/57505/1 "2021-03-18T23:55:40Z")

</div>

I have a `DataFrame` and I would like to store the data in an `Array`. This constructs the `DataFrame`:

```julia
using DataFrames
using DataFramesMeta
using StatsBase

n = 9^2*2^2*6^2*42

const df = crossjoin(
                DataFrame(am=1:9),
                DataFrame(af=1:9),
                DataFrame(em=1:2),
                DataFrame(ef=1:2),
                DataFrame(km=1:6),
                DataFrame(kf=1:6),
                DataFrame(t=1:42)
                )
insertcols!(df, :prob => rand(n))
insertcols!(df, :w => 1)
for i in 1:1000
    push!(df, (rand(1:9), rand(1:9), rand(1:2), rand(1:2), rand(1:6), rand(1:6), rand(1:42), rand(), sample(0:1)))
end

```

This stores the data in an `Array`:

```julia
const Pdata = Array{Float64}(undef, (9, 2, 9, 2, 6, 6, 42))
# this loop takes a long time
for idx in CartesianIndices(Pdata)
    am, em, af, ef, km, kf, t = Tuple(idx)
    df1 = @where(df, :t .== t, :am .== am, :af .== af, :em .== em, :ef .== ef, :km .== km, :kf .== kf)
    if size(df1, 1) == 1 && df1[1, :w] > 0
        Pdata[idx] = df[1, :prob]
    elseif size(df1, 1) == 0 || df1[1, :w] == 0
        Pdata[idx] = 0.0
    end
end

```

Is there a way to speed up that loop? or is there another way to achieve the “transfer” of data from a `DataFrame` to an `Array`?

---

<div class="post-metadata">

**Author:** ![jzr](https://avatars.discourse-cdn.com/v4/letter/j/eb9ed0/32.png) [@jzr](https://discourse.julialang.org/u/jzr)\
**Post date:** [March 19, 2021, 3:14am UTC](https://discourse.julialang.org/t/how-to-speed-up-this-dataframe-operation/57505/2 "2021-03-19T03:14:18Z")

</div>

I think you might be looking for a `groupby`/`combine` operation.

---

<div class="post-metadata">

**Author:** ![amrods](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/amrods/32/2543_2.png) [@amrods](https://discourse.julialang.org/u/amrods)\
**Post date:** [March 19, 2021, 4:49am UTC](https://discourse.julialang.org/t/how-to-speed-up-this-dataframe-operation/57505/3 "2021-03-19T04:49:40Z")

</div>

can you provide an example?

---

<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:** [March 19, 2021, 5:18am UTC](https://discourse.julialang.org/t/how-to-speed-up-this-dataframe-operation/57505/4 "2021-03-19T05:18:40Z")

</div>

In general

1. Never write performance-critical code in global scope. See the performance tips [here](https://docs.julialang.org/en/v1/manual/performance-tips/).
2. When writing performance-critical code in DataFrames, write a function and input the column directly, so Julia can infer types. For example,

```julia
    elseif size(df1, 1) == 0 || df1[1, :w] == 0

```

Julia can’t optimize something like this because it can’t figure out what type `df[1, :w]` is.

Also, that `@where` call is _very_ expensive. You are creating a new data frame almost 500,000 times!

It’s not clear exactly what you are doing in that loop, and I think you might have some errors. You could easily have sub-dataframes with `size(df, 1) > 1`, and definitely have no sub-datafames with size `0`. So I modified the function to do something slightly different. Hopefully you can adapt it to your exact use case.

```julia
using DataFrames, Chain, DataFramesMeta
df = @chain begin
    Iterators.product(1:9, 1:2, 1:9, 1:2, 1:6, 1:6, 1:42)
    collect
    vec
    DataFrame
    rename!([:am, :em, :af, :ef, :km, :kf, :t])
end;

n = nrow(df)
insertcols!(df, :prob => rand(n))
insertcols!(df, :w => 1)
for i in 1:1000
	# There was a typo on this line, I fixed it. 
    push!(df, (rand(1:9), rand(1:2), rand(1:9), rand(1:2), rand(1:6), rand(1:6), rand(1:42), rand(), sample(0:1)))
end

function make_array(df, var_to_fill)
	Pdata = Array{Float64}(undef, (9, 2, 9, 2, 6, 6, 42))
    @eachrow df begin
    	Pdata[:am, :em, :af, :ef, :km, :kf, :t] = cols(var_to_fill)
    end

    return Pdata
end

@chain df begin 
	groupby(Not([:prob, :w]))
	@combine newprob =  
		if length(:prob) == 1 && first(:w) > 0
			return first(:prob)
		else 
			return 0.0
		end
	make_array(:newprob)
end;

```

EDIT: This would actually make an excellent benchmark against other data libraries.

---

<div class="post-metadata">

**Author:** ![amrods](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/amrods/32/2543_2.png) [@amrods](https://discourse.julialang.org/u/amrods)\
**Post date:** [March 19, 2021, 5:28am UTC](https://discourse.julialang.org/t/how-to-speed-up-this-dataframe-operation/57505/5 "2021-03-19T05:28:58Z")

</div>

what package is that `@chain` coming from?

---

<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:** [March 19, 2021, 5:29am UTC](https://discourse.julialang.org/t/how-to-speed-up-this-dataframe-operation/57505/6 "2021-03-19T05:29:34Z")

</div>

Chain.jl, sorry. I added it to the top.

---

<div class="post-metadata">

**Author:** ![amrods](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/amrods/32/2543_2.png) [@amrods](https://discourse.julialang.org/u/amrods)\
**Post date:** [March 19, 2021, 5:35am UTC](https://discourse.julialang.org/t/how-to-speed-up-this-dataframe-operation/57505/7 "2021-03-19T05:35:21Z")

</div>

This seems to work. I have to study it more carefully, especially with that `@chain` magic. Thank you!

---

<div class="post-metadata">

**Author:** ![amrods](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/amrods/32/2543_2.png) [@amrods](https://discourse.julialang.org/u/amrods)\
**Post date:** [March 19, 2021, 5:38am UTC](https://discourse.julialang.org/t/how-to-speed-up-this-dataframe-operation/57505/8 "2021-03-19T05:38:54Z")

</div>

One thing that confuses me is this line `Pdata[:am, :em, :af, :ef, :km, :kf, :t]`, especially after you define `Pdata` as an `Array`. It seems you are accessing the array with symbols.

---

<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:** [March 19, 2021, 5:41am UTC](https://discourse.julialang.org/t/how-to-speed-up-this-dataframe-operation/57505/9 "2021-03-19T05:41:22Z")

</div>

Yeah, that’s an easy thing to misinterpret. Check out the docs for `@eachrow` [here](https://juliadata.github.io/DataFramesMeta.jl/stable/#@eachrow). It’s metaprogramming. Just like in `@where`, the `Symbol`s you see are actually columns, in `@eachrow` the `Symbol`s are actually the values of columns at a given row.

---

<div class="post-metadata">

**Author:** ![amrods](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/amrods/32/2543_2.png) [@amrods](https://discourse.julialang.org/u/amrods)\
**Post date:** [March 19, 2021, 5:49am UTC](https://discourse.julialang.org/t/how-to-speed-up-this-dataframe-operation/57505/10 "2021-03-19T05:49:05Z")

</div>

Oh I see, that is fantastic metaprogramming-fu!

---

<div class="post-metadata">

**Author:** ![amrods](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/amrods/32/2543_2.png) [@amrods](https://discourse.julialang.org/u/amrods)\
**Post date:** [March 19, 2021, 6:05am UTC](https://discourse.julialang.org/t/how-to-speed-up-this-dataframe-operation/57505/11 "2021-03-19T06:05:19Z")

</div>

Sorry to ask another question. In this block:

```julia
@combine newprob =  
		if length(:prob) == 1 && first(:w) > 0
			return first(:prob)
		else 
			return 0.0
		end

```

I couldn’t find that use of `@combine` in the documentation [[Introduction · DataFramesMeta Documentation](https://juliadata.github.io/DataFramesMeta.jl/stable/#@combine)]. Is that undocumented? Also confusing me is that you are using `return` as if there is a function somewhere, but it seems to be an `if` block. Can you please clarify that?

---

<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:** [March 19, 2021, 6:11am UTC](https://discourse.julialang.org/t/how-to-speed-up-this-dataframe-operation/57505/12 "2021-03-19T06:11:08Z")

</div>

No it’s not undocumented. It’s just that I spread the expression out over multiple lines. I probably should have written

```julia
@combine newprob = begin
	if length(:prob) == 1 && first(:w) > 0
		return first(:prob)
	else 
		return 0.0
	end
end

```

> Also confusing me is that you are using `return` as if there is a function somewhere, but it seems to be an `if` block. Can you please clarify that?

Indeed! Good catch. I copied and pasted from a function I had written earlier before I realized I could just write it out. This an implementation detail that stems from the way DataFramesMeta works.

```julia
@combine(df, y = f(:x))

```

creates the expression

```julia
combine(df, :x => (x -> f(x)) => :y)

```

See how the `f(:x)` part gets transformed into an anonymous function? So if you have a `return` in there, it will behave just as a `return` in a function.

Maybe we should disallow that because it’s confusing. But it probably has it’s benefits, like allowing early returns for faster performance.