# Read csv files slow

**URL:** <https://discourse.julialang.org/t/read-csv-files-slow/43803>\
**Category:** Performance\
**Tags:** filesystem\
**Created:** [July 28, 2020, 7:38am UTC](https://discourse.julialang.org/t/read-csv-files-slow/43803 "2020-07-28T07:38:19Z")\
**Posts on this page:** 14\
**Page:** 1

<div class="post-metadata">

**Author:** ![Ruan\_Stander](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/ruan_stander/32/11304_2.png) [@Ruan\_Stander](https://discourse.julialang.org/u/Ruan_Stander)\
**Post date:** [July 28, 2020, 7:38am UTC](https://discourse.julialang.org/t/read-csv-files-slow/43803/1 "2020-07-28T07:38:19Z")

</div>

Hi

I am loading a long list of text files into memory using DataFrames (joined on the Date Column)

It is taking very long to read them in so tried to do multithreading since my nthreads()=12

The multithreading is only working up to a point though as seen in the table below and 8000 files takes 10x longer than 4000 files despite only being 40% larger in storage. The table below shows time taken vs number of files and total file size

Any tips for improvement will be greatly appreciated!

| Files | Size (mb) | Time (s) |
| --- | --- | --- |
| 1000 | 372 | 15 |
| 2000 | 644 | 28 |
| 4000 | 1000 | 127 |
| 8000 | 1400 | 1260 |

```julia
#single threaded readfiles
function readfiles(files,filedirectory)
    df3=DataFrame!(CSV.File(filedirectory*files[1],threaded=true))
    DataFrames.rename!(df3, [Symbol("col$i") for i in 1:6])
    select!(df3,"col1","col5")
    DataFrames.rename!(df3, "col5"=> SubString(files[1],1:length(files[1])-4))
    deleteat!(files,1)
    for file in files
        df2=DataFrame!(CSV.File(filedirectory*file,threaded=true))
        DataFrames.rename!(df2, [Symbol("col$i") for i in 1:6])
        select!(df2,"col1","col5")
        DataFrames.rename!(df2, "col5"=> SubString(file,1:length(file)-4))
        df3=outerjoin(df3, df2, on = :"col1")
    end
    return df3
end
#multithreaded readfiles
function readfiles_par(filedirectory)
    files=readdir(filedirectory)
    n=Threads.nthreads()
    width=div(length(files),n)
    a=Array{Task}(undef,n)
    for j=1:n
        start=(j-1)*width+1
        a[j]=Threads.@spawn readfiles(files[start:(start+width-1)],filedirectory)
    end
    results=Array{DataFrame}(undef,n)
    for j=1:n
        results[j]=fetch(a[j])
    end
    df=results[1]
    for j=2:n
        df=outerjoin(df,results[j],on=:col1)
    end
    df.Date = Date.(df.col1, "mm/dd/yyyy")
    select!(df, Not("col1"))
    return df
end
```

---

<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:** [July 28, 2020, 8:30am UTC](https://discourse.julialang.org/t/read-csv-files-slow/43803/2 "2020-07-28T08:30:52Z")

</div>

The exponential(?) timings indicate the problem I would expect from `outerjoin`. Have you verified that _reading_ the CSV files is the bottleneck and not the joining part?

---

<div class="post-metadata">

**Author:** ![Ruan\_Stander](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/ruan_stander/32/11304_2.png) [@Ruan\_Stander](https://discourse.julialang.org/u/Ruan_Stander)\
**Post date:** [July 28, 2020, 9:21am UTC](https://discourse.julialang.org/t/read-csv-files-slow/43803/3 "2020-07-28T09:21:39Z")

</div>

> [@laborg](#):
>
> uld expect from `outerjoin` . Have you verified that _reading_ the CSV files is the bottleneck and not the joining part?

Not sure, trying to get a baseline with a once off read of files

Error MethodError: no method matching joinpath(::Array{String,1})

```julia
fileDirectory="d:/Data/Test/"
using Glob
files=glob("*.txt", fileDirectory) 
df3=DataFrame(CSV.File(files))
```

---

<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:** [July 28, 2020, 9:26am UTC](https://discourse.julialang.org/t/read-csv-files-slow/43803/4 "2020-07-28T09:26:06Z")

</div>

You need to broadcast the vector of files. Try this (untested) code instead:

```julia
fileDirectory="d:/Data/Test/"
using Glob
files=glob("*.txt", fileDirectory) 
df3=DataFrame.(CSV.File.(files))

```

---

<div class="post-metadata">

**Author:** ![Ruan\_Stander](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/ruan_stander/32/11304_2.png) [@Ruan\_Stander](https://discourse.julialang.org/u/Ruan_Stander)\
**Post date:** [July 28, 2020, 9:57am UTC](https://discourse.julialang.org/t/read-csv-files-slow/43803/5 "2020-07-28T09:57:57Z")

</div>

Files are not the same length unfortunately  
**DimensionMismatch(“column :x1 has length 14735 and column :x2 has length 14734”)**

---

<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:** [July 28, 2020, 12:55pm UTC](https://discourse.julialang.org/t/read-csv-files-slow/43803/6 "2020-07-28T12:55:25Z")

</div>

Can you import just one file using CSV.jl? Its best to try and break your problem into smaller steps to isolate the problem.

---

<div class="post-metadata">

**Author:** ![tbeason](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/tbeason/32/15898_2.png) [@tbeason](https://discourse.julialang.org/u/tbeason)\
**Post date:** [July 28, 2020, 3:38pm UTC](https://discourse.julialang.org/t/read-csv-files-slow/43803/7 "2020-07-28T15:38:10Z")

</div>

Like the other posters, I also doubt that the CSV reading is slow. Likely it is the manipulation and joining of the DataFrames that is slow.

Here is one other thing you could try, which may or may not be faster. Use `pmap` and read ALL of the CSVs in and do your small bit of preparation on them but do not join them into one.

```julia
using Distributed, DataFrames, CSV
addprocs(12)
function readandprep(file)
    df2=DataFrame!(CSV.File(file))
    DataFrames.rename!(df2, [Symbol("col$i") for i in 1:6])
    select!(df2,"col1","col5")
    DataFrames.rename!(df2, "col5"=> SubString(file,1:length(file)-4))
    return df2
end

all_df = pmap(readandprep,listofcsvfiles)

```

That should (I think?) net you a vector of DataFrames that are ready to be smooshed together however you like. I’d guess that the `pmap` should not take excessively long compared to the join, but who knows.

---

<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:** [July 28, 2020, 4:15pm UTC](https://discourse.julialang.org/t/read-csv-files-slow/43803/8 "2020-07-28T16:15:43Z")

</div>

What is likely to be the bottleneck is:

> [@Ruan\_Stander](#):
>
> `df3=outerjoin(df3, df2, on = :"col1")`

(note: you do not need `:` befor `"col1"`)

---

<div class="post-metadata">

**Author:** ![Ruan\_Stander](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/ruan_stander/32/11304_2.png) [@Ruan\_Stander](https://discourse.julialang.org/u/Ruan_Stander)\
**Post date:** [July 28, 2020, 5:23pm UTC](https://discourse.julialang.org/t/read-csv-files-slow/43803/9 "2020-07-28T17:23:33Z")

</div>

thanks. will give that a shot. seems like join only accepts two arguments so cant join 8000 files. how would you outerjoin the list of files on a column like date?

---

<div class="post-metadata">

**Author:** ![tbeason](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/tbeason/32/15898_2.png) [@tbeason](https://discourse.julialang.org/u/tbeason)\
**Post date:** [July 28, 2020, 5:43pm UTC](https://discourse.julialang.org/t/read-csv-files-slow/43803/10 "2020-07-28T17:43:46Z")

</div>

In a loop just like you are doing now, but in this case the data preparation is already done.

---

<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:** [July 28, 2020, 5:48pm UTC](https://discourse.julialang.org/t/read-csv-files-slow/43803/11 "2020-07-28T17:48:53Z")

</div>

Are you sure you need a join? and not `vcat` the data frames together?

---

<div class="post-metadata">

**Author:** ![klaff](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/klaff/32/7637_2.png) [@klaff](https://discourse.julialang.org/u/klaff)\
**Post date:** [July 28, 2020, 5:57pm UTC](https://discourse.julialang.org/t/read-csv-files-slow/43803/12 "2020-07-28T17:57:12Z")

</div>

I know I’m late to the party, but I believe CSV uses threading out of the box when reading files above a certain size (at least I found a bug on error reporting which was related to that) and so trying to thread on top of that might not work as expected.

---

<div class="post-metadata">

**Author:** ![Ruan\_Stander](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/ruan_stander/32/11304_2.png) [@Ruan\_Stander](https://discourse.julialang.org/u/Ruan_Stander)\
**Post date:** [July 28, 2020, 6:20pm UTC](https://discourse.julialang.org/t/read-csv-files-slow/43803/13 "2020-07-28T18:20:37Z")

</div>

output dataframe [date, file1, file2, file3…]  
joined on date  
so unfortunately have to join at some stage

---

<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:** [July 28, 2020, 6:29pm UTC](https://discourse.julialang.org/t/read-csv-files-slow/43803/14 "2020-07-28T18:29:28Z")

</div>

If so, you can do something like this

```julia
julia> dfs = [DataFrame(a = rand(1:100, 2), b = rand(2)) for i in 1:100]

julia> function myjoin(df1, df2)
           outerjoin(df1, df2, on = "a", makeunique = true)
       end

julia> reduce(myjoin, dfs);

```