# How to speed up the for-loop with dataframe access

**URL:** <https://discourse.julialang.org/t/how-to-speed-up-the-for-loop-with-dataframe-access/79447>\
**Category:** Performance\
**Tags:** dataframes\
**Created:** [April 13, 2022, 6:15pm UTC](https://discourse.julialang.org/t/how-to-speed-up-the-for-loop-with-dataframe-access/79447 "2022-04-13T18:15:13Z")\
**Posts on this page:** 20\
**Page:** 1

<div class="post-metadata">

**Author:** ![tiZ](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/tiz/32/211800_2.png) [@tiZ](https://discourse.julialang.org/u/tiZ)\
**Post date:** [April 13, 2022, 6:15pm UTC](https://discourse.julialang.org/t/how-to-speed-up-the-for-loop-with-dataframe-access/79447/1 "2022-04-13T18:15:13Z")

</div>

Hi, I am new to Julia from R, and I have a little trouble in learning how to speed up my code.

for a simple demo example, I have a DataFrame with 3 column X 8679568 row, each row is a record of protein pair. The row 1 and row 3 are the same, so row 3 should be removed. I wanna remove the duplicate items that in the whole dataframe for the first 200 row

![fi](https://global.discourse-cdn.com/julialang/original/3X/e/8/e87da638a3207c96712ea208e29a44cc0f9a3342.png)  
ps. the picture only show the first 3 row

here is my code that have to speed up. (I know it is a stupid solution)

```julia
@time begin
    pos = []
    for i in 1:200 # I will find the duplicate rows only for the first 200 rows.
        for j in i:8679568
            # if the column 1 and 2 in row i is the same as column 2 and 1 in row j
            # then the row j will be add into vector "pos" 
            if df.protein1[i] == df.protein2[j] && df.protein1[j] == df.protein2[i] 
                push!(pos,j)
                break
            end
        end
    end
end

```

The code it takes time: 21.660150 seconds (339.22 M allocations: 10.111 GiB, 8.08% gc time)

I have no idea how to speed up my code.

[update] If I find the duplicate row just for the first 100 row, then the time takes only 4.774666 seconds (79.00 M allocations: 2.361 GiB, 7.43% gc time, 2.91% compilation time)

[update 2] If it is not possible to speedup this simple code, what is the reason for the time it take to 4 seconds to find duplication for the frist 100 row, but 21 second for the first 200 row? ( from what I understand it should be 8 seconds)

**[update 3]**  
For the test, here is the link of whole data : [protein](https://stringdb-static.org/download/protein.links.v11.0/9606.protein.links.v11.0.txt.gz), it is about 70M size in txt.gz fomat, and it will contains some missing in the column 3, so I run `dropmissing!(df)` after load it in julia.

I check the julia performance tips, and now I know that I should put the code into functions instead of run them in global scope. Compare to the original code I put above, the code wraped in function takes 17.615409 seconds (339.22 M allocations: 10.111 GiB, 8.60% gc time), yes, it runs a little faster but no improve in memory allocations. Then according to tip of @bkamins and @pdeffebach, I modify the code for type stabilities, then the memory allocation problem is solved. it takes 9.137771 seconds (1 allocation: 1.766 KiB) to for seach dup for first 200 row, 35.960302 seconds for first 400, and 214.447423 seconds (2 allocations: 7.953 KiB) for first 1000.

P.S. I actuctlly solve the problem with below code, but any suggestion to reduce memory allocation in the for-loop is helpful. Thanks.

```julia
@time begin
    dropmissing!(df)
    df2 = Array(df)
    df2 = string.(df2) # turn all items into string for sort
    df2 = sort(df2, dims = 2) # for each row, sort its column 
    df2 = unique(df2, dims=1) # only keep the unique row 
end

```

it takes 9.367578 seconds (52.49 M allocations: 3.247 GiB, 27.73% gc time) for the whole dataset to de-duplicate instead of only the first 200 row.

---

<div class="post-metadata">

**Author:** ![tiZ](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/tiz/32/211800_2.png) [@tiZ](https://discourse.julialang.org/u/tiZ)\
**Post date:** [April 13, 2022, 6:16pm UTC](https://discourse.julialang.org/t/how-to-speed-up-the-for-loop-with-dataframe-access/79447/2 "2022-04-13T18:16:11Z")

</div>

The profileView.js give me this picture

 ![profileView](https://global.discourse-cdn.com/julialang/original/3X/1/7/1755fd5bfd7d805ead61473274725a73a6c9f00b.png)

---

<div class="post-metadata">

**Author:** ![Oscar\_Smith](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/oscar_smith/32/25343_2.png) [@Oscar\_Smith](https://discourse.julialang.org/u/Oscar_Smith)\
**Post date:** [April 13, 2022, 6:18pm UTC](https://discourse.julialang.org/t/how-to-speed-up-the-for-loop-with-dataframe-access/79447/3 "2022-04-13T18:18:41Z")

</div>

I think what you want here is `innerjoin` [Joins · DataFrames.jl](https://dataframes.juliadata.org/stable/man/joins/).

---

<div class="post-metadata">

**Author:** ![tiZ](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/tiz/32/211800_2.png) [@tiZ](https://discourse.julialang.org/u/tiZ)\
**Post date:** [April 13, 2022, 6:24pm UTC](https://discourse.julialang.org/t/how-to-speed-up-the-for-loop-with-dataframe-access/79447/5 "2022-04-13T18:24:30Z")

</div>

Thanks for your helpful tips. I know that this code is not suitable for solving the problem of deduplication. I want to use this example to learn how to reduce memory allocation. But I have no idea.

---

<div class="post-metadata">

**Author:** ![goerch](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/goerch/32/29122_2.png) [@goerch](https://discourse.julialang.org/u/goerch)\
**Post date:** [April 13, 2022, 6:29pm UTC](https://discourse.julialang.org/t/how-to-speed-up-the-for-loop-with-dataframe-access/79447/6 "2022-04-13T18:29:24Z")

</div>

Nice problem. How many different proteins do you have, 4?

Edit: corrected the number of visible proteins…

---

<div class="post-metadata">

**Author:** ![tiZ](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/tiz/32/211800_2.png) [@tiZ](https://discourse.julialang.org/u/tiZ)\
**Post date:** [April 13, 2022, 6:32pm UTC](https://discourse.julialang.org/t/how-to-speed-up-the-for-loop-with-dataframe-access/79447/7 "2022-04-13T18:32:42Z")

</div>

Sorry for the confuse, I have 8679568 pair of protein, and many are duplicate. the pic just for a simple demo.

---

<div class="post-metadata">

**Author:** ![goerch](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/goerch/32/29122_2.png) [@goerch](https://discourse.julialang.org/u/goerch)\
**Post date:** [April 13, 2022, 6:33pm UTC](https://discourse.julialang.org/t/how-to-speed-up-the-for-loop-with-dataframe-access/79447/8 "2022-04-13T18:33:54Z")

</div>

Yes, but I only see proteins A, B, C, D (so, sorry, 4)? So again, how big is the set of proteins?

---

<div class="post-metadata">

**Author:** ![tiZ](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/tiz/32/211800_2.png) [@tiZ](https://discourse.julialang.org/u/tiZ)\
**Post date:** [April 13, 2022, 6:35pm UTC](https://discourse.julialang.org/t/how-to-speed-up-the-for-loop-with-dataframe-access/79447/9 "2022-04-13T18:35:07Z")

</div>

it is about 20k protein.

---

<div class="post-metadata">

**Author:** ![lawless-m](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/lawless-m/32/30869_2.png) [@lawless-m](https://discourse.julialang.org/u/lawless-m)\
**Post date:** [April 13, 2022, 6:42pm UTC](https://discourse.julialang.org/t/how-to-speed-up-the-for-loop-with-dataframe-access/79447/10 "2022-04-13T18:42:38Z")

</div>

Allocations is the place to start. Using push! extends the storage of the vector. As you already know how many rows there are, we can allocate once and track which of the 200 rows has a duplicate in j - this is slightly different to your `pos` so I changed the name to `dup`

```julia
@time begin
    dup = zeros(Int, 200)
    for i in 1:200
        for j in i:size(df, 1)
            if df.protein1[i] == df.protein2[j] && df.protein1[j] == df.protein2[i] 
                dup[i] = j
                break
            end
        end
    end
end

```

So now you have a list of either 0 or the index of the first duplicate found

---

<div class="post-metadata">

**Author:** ![tiZ](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/tiz/32/211800_2.png) [@tiZ](https://discourse.julialang.org/u/tiZ)\
**Post date:** [April 13, 2022, 6:50pm UTC](https://discourse.julialang.org/t/how-to-speed-up-the-for-loop-with-dataframe-access/79447/11 "2022-04-13T18:50:06Z")

</div>

Thanks, I have try it, but no significant difference, it still takes 22.897745 seconds (339.22 M allocations: 10.111 GiB, 8.56% gc time).

so, is it possible to descrease the memory allocation and speed up for this code block?

---

<div class="post-metadata">

**Author:** ![lawless-m](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/lawless-m/32/30869_2.png) [@lawless-m](https://discourse.julialang.org/u/lawless-m)\
**Post date:** [April 13, 2022, 7:06pm UTC](https://discourse.julialang.org/t/how-to-speed-up-the-for-loop-with-dataframe-access/79447/12 "2022-04-13T19:06:38Z")

</div>

There is no way that the code I posted is doing 339M allocations / allocating 10GiB of memory.

I don’t think you are posting enough of your actual code

---

<div class="post-metadata">

**Author:** ![kfrb](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/kfrb/32/29557_2.png) [@kfrb](https://discourse.julialang.org/u/kfrb)\
**Post date:** [April 13, 2022, 7:14pm UTC](https://discourse.julialang.org/t/how-to-speed-up-the-for-loop-with-dataframe-access/79447/13 "2022-04-13T19:14:35Z")

</div>

@tiZ, do you run the calculation in global scope? Try this (didn’t test):

```julia
function calcdup(df)
    n = 200
    m = size(df, 1)
    dup = zeros(Int, n)
    for i in 1:n
        for j in i:m
            if df.protein1[i] == df.protein2[j] && df.protein1[j] == df.protein2[i] 
                dup[i] = j
                break
            end
        end
    end
    return dup
end

@time calcdup(df)

```

---

<div class="post-metadata">

**Author:** ![goerch](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/goerch/32/29122_2.png) [@goerch](https://discourse.julialang.org/u/goerch)\
**Post date:** [April 13, 2022, 7:17pm UTC](https://discourse.julialang.org/t/how-to-speed-up-the-for-loop-with-dataframe-access/79447/14 "2022-04-13T19:17:38Z")

</div>

> [@lawless-m](#):
>
> I don’t think you are posting enough of your actual code

That`s why I was asking. Trying to establish some kind of baseline:

```julia
using BenchmarkTools

function setup()
    df = [(rand(1:20_000), rand(1:20_000)) for _ in 1:1_000_000]
end

function indices(df)
    idx = Dict{Int, Dict{Int, Vector{Int}}}()
    for (row_no, row) in enumerate(df)
        if !haskey(idx, row[1])
            idx[row[1]] = Dict{Int, Vector{Int}}()
        end
        if !haskey(idx[row[1]], row[2])
            idx[row[1]][row[2]] = Int[]
        end
        push!(idx[row[1]][row[2]], row_no)
    end
    idx
end

function duplicates(df, idx)
    dups = Dict{Int, Int}()
    for protein1 in keys(idx) 
        for protein2 in keys(idx[protein1])
            if haskey(idx, protein2) && haskey(idx[protein2], protein1)
                for row_no1 in idx[protein1][protein2], row_no2 in idx[protein2][protein1]
                    # @show df[row_no1], df[row_no2]
                    dups[row_no1] = row_no2
                end
            end
        end
    end
    dups
end

df = @btime setup()
idx = @btime indices($df)
dups = @btime duplicates($df, $idx)

```

yielding

```julia
  12.841 ms (2 allocations: 15.26 MiB)
  576.248 ms (2189014 allocations: 249.76 MiB)
  158.008 ms (18 allocations: 91.95 KiB)

```

> [@Oscar\_Smith](#):
>
> I think what you want here is `innerjoin` [Joins · DataFrames.jl](https://dataframes.juliadata.org/stable/man/joins/)

That sounds correct. It would probably be useful if OP would add some artificial data generation to his code.

Edit: corrected benchmark.

---

<div class="post-metadata">

**Author:** ![DNF](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/dnf/32/10191_2.png) [@DNF](https://discourse.julialang.org/u/DNF)\
**Post date:** [April 13, 2022, 7:19pm UTC](https://discourse.julialang.org/t/how-to-speed-up-the-for-loop-with-dataframe-access/79447/15 "2022-04-13T19:19:22Z")

</div>

> [@tiZ](#):
>
> `pos = []`

If you have this anywhere in your code, it will be slow. Make sure to add a type to the container.

Also, make sure you put your code in functions.

---

<div class="post-metadata">

**Author:** ![tiZ](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/tiz/32/211800_2.png) [@tiZ](https://discourse.julialang.org/u/tiZ)\
**Post date:** [April 13, 2022, 7:21pm UTC](https://discourse.julialang.org/t/how-to-speed-up-the-for-loop-with-dataframe-access/79447/16 "2022-04-13T19:21:26Z")

</div>

Thanks. I run the whole code directly instead of put them into a function.

I learn a lot today.

---

<div class="post-metadata">

**Author:** ![goerch](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/goerch/32/29122_2.png) [@goerch](https://discourse.julialang.org/u/goerch)\
**Post date:** [April 13, 2022, 7:39pm UTC](https://discourse.julialang.org/t/how-to-speed-up-the-for-loop-with-dataframe-access/79447/17 "2022-04-13T19:39:09Z")

</div>

Just curious: how does your code look like now? How much better are the results? Do you intend to find _all_ duplicates in such a data set?

---

<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:** [April 13, 2022, 7:41pm UTC](https://discourse.julialang.org/t/how-to-speed-up-the-for-loop-with-dataframe-access/79447/18 "2022-04-13T19:41:57Z")

</div>

to remove duplicate, try something like this

`Set(Set.(df))`

wwhere df is the array of +8M pair(p1,p2)

or

```julia
Set(Set.(Tables.namedtupleiterator(df[:,1:2])))

```

this seems faster

```julia
Set(sort.([[a,b] for (a,b) in Tables.namedtupleiterator(df[:,1:2])]))

```

and this is “more” faster

```julia
Set(([a>b ? [b,a] : [a,b] for (a,b) in zip(df.p1,df.p2)]))

```

---

<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:** [April 13, 2022, 8:32pm UTC](https://discourse.julialang.org/t/how-to-speed-up-the-for-loop-with-dataframe-access/79447/19 "2022-04-13T20:32:56Z")

</div>

> [@kfrb](#):
>
> `df.protein1[i] == df.protein2[j] && df.protein1[j] == df.protein2[i]`

Instead of this it would be faster to use barrier and define `calcdup(p1::AbstractVector, p2:;AbstractVector)` and then call it `calcdup(df.protein1, df.protein2)`. Then you also need to change `m = length(p1)`.

---

<div class="post-metadata">

**Author:** ![goerch](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/goerch/32/29122_2.png) [@goerch](https://discourse.julialang.org/u/goerch)\
**Post date:** [April 13, 2022, 10:21pm UTC](https://discourse.julialang.org/t/how-to-speed-up-the-for-loop-with-dataframe-access/79447/20 "2022-04-13T22:21:11Z")

</div>

> [@kfrb](#):
>
> @tiZ, do you run the calculation in global scope? Try this:

I am not convinced. I tried to adapt my synthetic benchmark to `DataFrames`

```julia
using BenchmarkTools
using DataFrames

function setup()
    df = DataFrame(protein1 = [rand(1:20_000) for _ in 1:1_000_000], protein2 = [rand(1:20_000) for _ in 1:1_000_000])
end

function duplicates(df)
    n = 200
    dup = zeros(Int, n)
    for i in 1:n
        for j in i:size(df, 1)
            if df.protein1[i] == df.protein2[j] && df.protein1[j] == df.protein2[i] 
                dup[i] = j
                break
            end
        end
    end
    return dup
end

df = @btime setup()
dups = @btime duplicates($df)

```

yielding

```julia
  15.308 ms (54 allocations: 30.52 MiB)
  19.705 s (594182583 allocations: 8.85 GiB)

```

Please consider: this is checking the first 200 rows for duplicates in 1\_000\_000 rows in 20 seconds, whereas my baseline checks for _all_ duplicates in the data set in around 1 second. So I’m curious to learn what I’m missing here.

Edit: one more remark: I also tried to use `innerjoin` but failed miserably to generate the necessary self join. Any hints?

---

<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:** [April 13, 2022, 10:39pm UTC](https://discourse.julialang.org/t/how-to-speed-up-the-for-loop-with-dataframe-access/79447/21 "2022-04-13T22:39:12Z")

</div>

As @bkamins referenced above, the type stabilities are causing all the problems. Here is a version like recommended, but using DataFramesMeta.jl for easier syntax.

```julia
julia> using DataFramesMeta, BenchmarkTools;

julia> function setup()
           df = DataFrame(protein1 = [rand(1:20_000) for _ in 1:1_000_000], protein2 = [rand(1:20_000) for _ in 1:1_000_000])
       end;

julia> function duplicates(df)
           n = 200
           dup = zeros(Int, n)
           for i in 1:n
               for j in i:size(df, 1)
                   if df.protein1[i] == df.protein2[j] && df.protein1[j] == df.protein2[i]
                       dup[i] = j
                       break
                   end
               end
           end
           return dup
       end;
julia> function duplicates_dfm(df)
           n = 200
           dup = zeros(Int, n)
           @with df begin
               for i in 1:n
                   for j in i:length(:protein1)
                       if :protein1[i] == :protein2[j] && :protein1[j] == :protein2[i]
                           dup[i] = j
                           break
                       end
                   end
               end
           end
           return dup
       end;

julia> df = @btime setup();
  15.265 ms (33 allocations: 30.52 MiB)

julia> dups = @btime duplicates($df);
  31.710 s (990677773 allocations: 20.72 GiB)

julia> dups = @btime duplicates_dfm($df);
  193.206 ms (7 allocations: 2.03 KiB)

```

[Next page](https://discourse.julialang.org/t/how-to-speed-up-the-for-loop-with-dataframe-access/79447.md?page=2)