# Append!() function extremely slow in DataFrames + CSV

**URL:** <https://discourse.julialang.org/t/append-function-extremely-slow-in-dataframes-csv/8396>\
**Category:** Data\
**Tags:** package\
**Created:** [January 16, 2018, 9:57am UTC](https://discourse.julialang.org/t/append-function-extremely-slow-in-dataframes-csv/8396 "2018-01-16T09:57:53Z")\
**Posts on this page:** 15\
**Page:** 1

<div class="post-metadata">

**Author:** ![Fred](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/fred/32/14175_2.png) [@Fred](https://discourse.julialang.org/u/Fred)\
**Post date:** [January 16, 2018, 9:57am UTC](https://discourse.julialang.org/t/append-function-extremely-slow-in-dataframes-csv/8396/1 "2018-01-16T09:57:53Z")

</div>

Hi,

When I switched to CSV instead of readtable() to read tabular data, I was surprised to see an extreme slowdown. I attached a small benchmark which seems to indicate that the slowdown is in the append!() function.

Thank you for any advice !

```julia
using DataFrames
using CSV

function newDF(nrow::Int64)
    x = CSV.read("test.tsv"; delim="\t", header=true)
    y = similar(x,0)

    for i in 1:nrow
        append!(y,x[i,:])
    end 
end

function oldDF(nrow::Int64)
    x = readtable("test.tsv", separator='\t', header=true)
    y = similar(x,0)

    for i in 1:nrow
        append!(y,x[i,:])
    end
end

@time oldDF(3000)
@time newDF(3000)
@time oldDF(3000)
@time newDF(3000)

#=
Results :
readtable()
WARNING: readtable is deprecated, use CSV.read from the CSV package instead
4.402112 seconds (3.77 M allocations: 257.848 MiB, 4.21% gc time)

CSV
240.959365 seconds (518.44 M allocations: 41.142 GiB, 4.02% gc time)

readtable()
WARNING: readtable is deprecated, use CSV.read from the CSV package instead
0.947368 seconds (1.86 M allocations: 157.937 MiB, 15.46% gc time)

CSV
231.824696 seconds (513.78 M allocations: 40.898 GiB, 4.00% gc time)
=#

```

---

<div class="post-metadata">

**Author:** ![kristoffer.carlsson](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/kristoffer.carlsson/32/22_2.png) [@kristoffer.carlsson](https://discourse.julialang.org/u/kristoffer.carlsson)\
**Post date:** [January 16, 2018, 10:07am UTC](https://discourse.julialang.org/t/append-function-extremely-slow-in-dataframes-csv/8396/2 "2018-01-16T10:07:04Z")

</div>

Could you put the `test.tsv` file somewhere so it is easier to run your code.

---

<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:** [January 16, 2018, 10:24am UTC](https://discourse.julialang.org/t/append-function-extremely-slow-in-dataframes-csv/8396/3 "2018-01-16T10:24:04Z")

</div>

What makes you think it’s due to `append!`? You should move the `append!` calls to a separate function to see that. Anyway, calling `append!` like that is really not efficient. What’s the actual use case?

---

<div class="post-metadata">

**Author:** ![Fred](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/fred/32/14175_2.png) [@Fred](https://discourse.julialang.org/u/Fred)\
**Post date:** [January 16, 2018, 10:25am UTC](https://discourse.julialang.org/t/append-function-extremely-slow-in-dataframes-csv/8396/4 "2018-01-16T10:25:35Z")

</div>

@kristoffer.carlsson, the file is large (30Mo) is there a way to attach a file here ?

---

<div class="post-metadata">

**Author:** ![kristoffer.carlsson](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/kristoffer.carlsson/32/22_2.png) [@kristoffer.carlsson](https://discourse.julialang.org/u/kristoffer.carlsson)\
**Post date:** [January 16, 2018, 10:27am UTC](https://discourse.julialang.org/t/append-function-extremely-slow-in-dataframes-csv/8396/5 "2018-01-16T10:27:47Z")

</div>

Is there a way you could reduce the file size while still showing the performance difference? Answers tend to be much better and accurate if it is possible to actually try out the code instead of guessing what is happening.

---

<div class="post-metadata">

**Author:** ![Fred](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/fred/32/14175_2.png) [@Fred](https://discourse.julialang.org/u/Fred)\
**Post date:** [January 16, 2018, 10:46am UTC](https://discourse.julialang.org/t/append-function-extremely-slow-in-dataframes-csv/8396/6 "2018-01-16T10:46:05Z")

</div>

@kristoffer.carlsson : a smaller test.tsv file is available here  
[test.tsv](https://www.transfernow.net/91d1s164yqhp)  
(don’t forget to reduce the row number

```julia
@time oldDF(1999)
@time newDF(1999)

```

@nalimilan

This is a new version of the test code with every step isolated in separate functions 😉

```julia
using DataFrames
using CSV

function newDF(nrow::Int64)
    x = CSV.read("test.tsv"; delim="\t", header=true)
    y = similar(x,0)
    loop(y, x, nrow)
end

function oldDF(nrow::Int64)
    x = readtable("test.tsv", separator='\t', header=true)
    y = similar(x,0)
    loop(y, x, nrow)
end

function loop(y::DataFrame, x::DataFrame, nrow::Int64)
    for i in 1:nrow
        addRow(y, x[i,:])
    end
end

function addRow(df::DataFrame, row::DataFrame)
    append!(df,row)
end

@time oldDF(1999)
@time newDF(1999)
@time oldDF(1999)
@time newDF(1999)

#=
WARNING: readtable is deprecated, use CSV.read from the CSV package instead
 3.547139 seconds (2.17 M allocations: 113.254 MiB, 1.16% gc time)
 12.916447 seconds (15.24 M allocations: 1.090 GiB, 1.35% gc time)

WARNING: readtable is deprecated, use CSV.read from the CSV package instead
  0.118423 seconds (260.28 k allocations: 13.271 MiB)
  5.810863 seconds (10.58 M allocations: 866.798 MiB, 1.64% gc time)

=#

```

---

<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:** [January 16, 2018, 10:51am UTC](https://discourse.julialang.org/t/append-function-extremely-slow-in-dataframes-csv/8396/7 "2018-01-16T10:51:57Z")

</div>

Thanks, but that doesn’t address my question: is the time due to reading the TSV file, or to calling `loop` on the resulting data frame? You need to call `@time` separately to find this out.

---

<div class="post-metadata">

**Author:** ![Fred](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/fred/32/14175_2.png) [@Fred](https://discourse.julialang.org/u/Fred)\
**Post date:** [January 16, 2018, 10:59am UTC](https://discourse.julialang.org/t/append-function-extremely-slow-in-dataframes-csv/8396/8 "2018-01-16T10:59:19Z")

</div>

@nalimilan you are absolutely right 😉

The read time is even faster with the new dataframe so I think it is the append() which is much slower 🙂

```julia
using DataFrames
using CSV

function newDF(nrow::Int64)
    @time x = CSV.read("test.tsv"; delim="\t", header=true)
    y = similar(x,0)
    loop(y, x, nrow)
end

function oldDF(nrow::Int64)
    @time x = readtable("test.tsv", separator='\t', header=true)
    y = similar(x,0)
    loop(y, x, nrow)
end

function loop(y::DataFrame, x::DataFrame, nrow::Int64)
    for i in 1:nrow
        addRow(y, x[i,:])
    end
end

function addRow(df::DataFrame, row::DataFrame)
    append!(df,row)
end

@time oldDF(1999)
@time newDF(1999)
@time oldDF(1999)
@time newDF(1999)

#=
WARNING: readtable is deprecated, use CSV.read from the CSV package instead
  2.635364 seconds (1.42 M allocations: 74.783 MiB, 1.01% gc time)
  3.699579 seconds (2.27 M allocations: 118.356 MiB, 1.30% gc time)
  4.071875 seconds (1.59 M allocations: 84.596 MiB, 0.82% gc time)
 12.948613 seconds (15.24 M allocations: 1.090 GiB, 1.45% gc time)

WARNING: readtable is deprecated, use CSV.read from the CSV package instead
  0.017080 seconds (76.86 k allocations: 4.369 MiB) readtime old df
  0.122019 seconds (260.42 k allocations: 13.278 MiB) append time old df
  0.007308 seconds (54.06 k allocations: 1.844 MiB) readtime new df
  5.860227 seconds (10.58 M allocations: 866.799 MiB, 1.70% gc time) append time new df

```

---

<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:** [January 16, 2018, 11:03am UTC](https://discourse.julialang.org/t/append-function-extremely-slow-in-dataframes-csv/8396/9 "2018-01-16T11:03:55Z")

</div>

> [@Fred](#):
>
> The read time is even faster with the new dataframe so I think it is the append() which is much slower 🙂

OK, so that must be due to the fact that columns are `Vector{Union{T, Missing}}` rather than `DataVector{T}`. I guess you’re on Julia 0.6? It would be worth trying on 0.7, where the memory layout should be much more efficient.

Anyway, do you really need to call `append!` repeatedly like that? It should be much faster to specify the final number of rows when calling `similar`, and then assign the values to each row. There are probably even more efficient approaches, depending on what you actually need to do.

---

<div class="post-metadata">

**Author:** ![Fred](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/fred/32/14175_2.png) [@Fred](https://discourse.julialang.org/u/Fred)\
**Post date:** [January 16, 2018, 11:18am UTC](https://discourse.julialang.org/t/append-function-extremely-slow-in-dataframes-csv/8396/10 "2018-01-16T11:18:32Z")

</div>

@nalimilan here are the results with Julia 0.7

0.085393 seconds (199.48 k allocations: 11.631 MiB, 3.11% gc time) old  
5.846089 seconds (8.43 M allocations: 814.511 MiB, 1.37% gc time) new

almost a factor 7 slowdown

I admit that calling append!() repeatedly is not elegant but I have to filter large datafile and I cannot know the final number of rows until everything is filtered so a speed factor of 7 can make a good difference.

---

<div class="post-metadata">

**Author:** ![rveltz](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/rveltz/32/2707_2.png) [@rveltz](https://discourse.julialang.org/u/rveltz)\
**Post date:** [January 16, 2018, 11:52am UTC](https://discourse.julialang.org/t/append-function-extremely-slow-in-dataframes-csv/8396/11 "2018-01-16T11:52:16Z")

</div>

Maybe ElasticArrays could help your `append`?

---

<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:** [January 16, 2018, 11:52am UTC](https://discourse.julialang.org/t/append-function-extremely-slow-in-dataframes-csv/8396/12 "2018-01-16T11:52:28Z")

</div>

Hmm… Looks like some major optimizations are missing here. If you want to help making progress, it would be useful to check whether you can reproduce this without DataFrames, i.e. by calling `append!` directly on vectors, comparing `Vector{T}`, `DataVector`{T} with `Vector{Union{T, Missing}}`. Then it would be worth filing an issue in Julia.

If you need to filter a data set, maybe you can fill a boolean vector indicating whether a row should be kept or not, and then call `getindex` on the data frame with it? That would be dramatically faster. There’s also an (unoptimized as the moment) `filter` function in the latest DataFrames version which could be used to get the same result.

---

<div class="post-metadata">

**Author:** ![Fred](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/fred/32/14175_2.png) [@Fred](https://discourse.julialang.org/u/Fred)\
**Post date:** [January 16, 2018, 12:58pm UTC](https://discourse.julialang.org/t/append-function-extremely-slow-in-dataframes-csv/8396/13 "2018-01-16T12:58:27Z")

</div>

> [@nalimilan](#):
>
> If you need to filter a data set, maybe you can fill a boolean vector indicating whether a row should be kept or not, and then call getindex on the data frame with it?

I think it is indeed possible to do this in two steps 🙂

---

<div class="post-metadata">

**Author:** ![quinnj](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/quinnj/32/11_2.png) [@quinnj](https://discourse.julialang.org/u/quinnj)\
**Post date:** [January 16, 2018, 2:10pm UTC](https://discourse.julialang.org/t/append-function-extremely-slow-in-dataframes-csv/8396/14 "2018-01-16T14:10:43Z")

</div>

CSV.jl also supports appending to an existing sink natively, so you can do something like:

```julia
sink = CSV.read("test.tsv"; delim='\t', header=true)
for i = 1:nrow
    CSV.read("test.tsv", sink; delim='\t', header=true, append=true)
end

```

though it’s unclear if that’s what you’re really trying to do here or not (is it just a one-row file?)

---

<div class="post-metadata">

**Author:** ![Fred](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/fred/32/14175_2.png) [@Fred](https://discourse.julialang.org/u/Fred)\
**Post date:** [January 16, 2018, 2:21pm UTC](https://discourse.julialang.org/t/append-function-extremely-slow-in-dataframes-csv/8396/15 "2018-01-16T14:21:16Z")

</div>

Thank you for this remark @quinnj

it is not a one row file.

I want to select in a very large table (\>30 000 rows) some rows that satisfy a test and merge all theses selected rows in a new table with exactly the same header than the initial table 🙂