# My experiences reading CSVs from the Fannie Mae datasets

**URL:** <https://discourse.julialang.org/t/my-experiences-reading-csvs-from-the-fannie-mae-datasets/8737>\
**Category:** Data\
**Tags:** performance, csv\
**Created:** [February 1, 2018, 10:49am UTC](https://discourse.julialang.org/t/my-experiences-reading-csvs-from-the-fannie-mae-datasets/8737 "2018-02-01T10:49:15Z")\
**Posts on this page:** 20\
**Page:** 2

<div class="post-metadata">

**Author:** ![aaowens](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/aaowens/32/12101_2.png) [@aaowens](https://discourse.julialang.org/u/aaowens)\
**Post date:** [March 1, 2019, 11:37pm UTC](https://discourse.julialang.org/t/my-experiences-reading-csvs-from-the-fannie-mae-datasets/8737/21 "2019-03-01T23:37:51Z")

</div>

I saw these new docs on out of core. Maybe it will help you: [https://juliacomputing.github.io/JuliaDB.jl/latest/out\_of\_core/](https://juliacomputing.github.io/JuliaDB.jl/latest/out_of_core/)

I guess you need to increase the number of chunks enough so that it will fit in memory.

---

<div class="post-metadata">

**Author:** ![Yifan\_Liu](https://avatars.discourse-cdn.com/v4/letter/y/4da419/32.png) [@Yifan\_Liu](https://discourse.julialang.org/u/Yifan_Liu)\
**Post date:** [March 2, 2019, 12:49am UTC](https://discourse.julialang.org/t/my-experiences-reading-csvs-from-the-fannie-mae-datasets/8737/22 "2019-03-02T00:49:14Z")

</div>

Actually the only example that JuliaDB works with TrueFX data is not that useful. Since TrueFX has very good API in R and Python, I can directly get the data into R or Python with customized filtering conditions, there is no need to download so many csv files and then load them into JuliaDB.

I hope JuliaDB could use a more useful example.

---

<div class="post-metadata">

**Author:** ![xiaodai](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/xiaodai/32/15937_2.png) [@xiaodai](https://discourse.julialang.org/u/xiaodai)\
**Post date:** [March 19, 2019, 9:52am UTC](https://discourse.julialang.org/t/my-experiences-reading-csvs-from-the-fannie-mae-datasets/8737/23 "2019-03-19T09:52:04Z")

</div>

CSV.jl can read the data now without much help! But it’s still slower than using R’s data.table and then reading the data into Julia using `@rget`. So performance is still an issue.

```julia
download("https://github.com/xiaodaigh/testing/raw/master/Performance_2016Q1.zip", "ok.zip")

run(`unzip ok.zip`)

using DataFrames, TableReader

@time a = readcsv("Performance_2016Q1.csv", delim ='|', header=false, chunkszie = 0)

using FileIO, TextParse, CSV, DataFrames
@time b = CSV.read("Performance_2016Q1.csv", delim = '|', header=0)
@time adf = DataFrame(b) # 22 seconds

using RCall

function a()
R"""
adf1 = data.table::fread('Performance_2016Q1.csv')
"""
@rget adf1
R"rm(adf1)"
adf1
end

@time a() # 15 seconds

```

---

<div class="post-metadata">

**Author:** ![sdanisch](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/sdanisch/32/1406_2.png) [@sdanisch](https://discourse.julialang.org/u/sdanisch)\
**Post date:** [March 19, 2019, 10:23am UTC](https://discourse.julialang.org/t/my-experiences-reading-csvs-from-the-fannie-mae-datasets/8737/24 "2019-03-19T10:23:08Z")

</div>

I see you have TableReader in there, but don’t report any timings?  
I tried it, and it seems like the fastest of the three!  
Timings:  
R.fread: 20s  
CSV: 12s  
TableReader: 8s

```julia
using TableReader, CSV, RCall
function fread()
    R"""
    adf1 = data.table::fread('/home/sd/Downloads/Performance_2016Q1.csv')
    """
    @rget adf1
    R"rm(adf1)"
    adf1
end

@time readcsv(path, delim = '|', chunksize = 0, header = [Symbol("var_$i") for i in 1:31])
@time CSV.read(path, delim = '|', header=0)
@time fread()

```

---

<div class="post-metadata">

**Author:** ![xiaodai](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/xiaodai/32/15937_2.png) [@xiaodai](https://discourse.julialang.org/u/xiaodai)\
**Post date:** [March 19, 2019, 10:36am UTC](https://discourse.julialang.org/t/my-experiences-reading-csvs-from-the-fannie-mae-datasets/8737/25 "2019-03-19T10:36:01Z")

</div>

> [@sdanisch](#):
>
> @time readcsv(path, delim = ‘|’, chunksize = 0, header = [Symbol(“var\_$i”) for i in 1:31])

Now I will try it! It ran with a bug so I reported it, but the magic seems to be `header = ...`

Now I am getting an error which i have reported [here](https://github.com/bicycle1885/TableReader.jl/issues/7).

doing `data.table::fread` in R should still be faster than.

---

<div class="post-metadata">

**Author:** ![js135005](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/js135005/32/8219_2.png) [@js135005](https://discourse.julialang.org/u/js135005)\
**Post date:** [March 19, 2019, 12:17pm UTC](https://discourse.julialang.org/t/my-experiences-reading-csvs-from-the-fannie-mae-datasets/8737/26 "2019-03-19T12:17:51Z")

</div>

I’m hitting the same TableReader problem (on Windows) with a fairly small tsv file when specifying the delim=‘\t’ keyword.

---

<div class="post-metadata">

**Author:** ![Yifan\_Liu](https://avatars.discourse-cdn.com/v4/letter/y/4da419/32.png) [@Yifan\_Liu](https://discourse.julialang.org/u/Yifan_Liu)\
**Post date:** [March 19, 2019, 2:25pm UTC](https://discourse.julialang.org/t/my-experiences-reading-csvs-from-the-fannie-mae-datasets/8737/27 "2019-03-19T14:25:52Z")

</div>

Just quick question, is there any function to read fst files in Julia 1.1?

---

<div class="post-metadata">

**Author:** ![xiaodai](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/xiaodai/32/15937_2.png) [@xiaodai](https://discourse.julialang.org/u/xiaodai)\
**Post date:** [March 19, 2019, 8:36pm UTC](https://discourse.julialang.org/t/my-experiences-reading-csvs-from-the-fannie-mae-datasets/8737/28 "2019-03-19T20:36:28Z")

</div>

FstFiles.jl or FstFormatFiles.jl part of the Queryverse. But it doesn’t load them natively. It uses RCall so data transfer is slow

---

<div class="post-metadata">

**Author:** ![xiaodai](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/xiaodai/32/15937_2.png) [@xiaodai](https://discourse.julialang.org/u/xiaodai)\
**Post date:** [March 19, 2019, 8:55pm UTC](https://discourse.julialang.org/t/my-experiences-reading-csvs-from-the-fannie-mae-datasets/8737/29 "2019-03-19T20:55:43Z")

</div>

> [@sdanisch](#):
>
> using TableReader, CSV, RCall function fread() R"“” adf1 = data.table::fread(‘/home/sd/Downloads/Performance\_2016Q1.csv’) “”" @rget adf1 R"rm(adf1)" adf1 end @time readcsv(path, delim = ‘|’, chunksize = 0, header = [Symbol(“var\_$i”) for i in 1:31]) @time CSV.read(path, delim = ‘|’, header=0) @time fread()

Do u think u can run the above on Windows and see if you run into issues?

---

<div class="post-metadata">

**Author:** ![sdanisch](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/sdanisch/32/1406_2.png) [@sdanisch](https://discourse.julialang.org/u/sdanisch)\
**Post date:** [March 20, 2019, 10:51am UTC](https://discourse.julialang.org/t/my-experiences-reading-csvs-from-the-fannie-mae-datasets/8737/30 "2019-03-20T10:51:37Z")

</div>

On my slower windows PC I get:

TableReader: ReadOnlyMemoryError()  
CSV: 25s  
fread: 29s

---

<div class="post-metadata">

**Author:** ![xiaodai](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/xiaodai/32/15937_2.png) [@xiaodai](https://discourse.julialang.org/u/xiaodai)\
**Post date:** [March 20, 2019, 10:57am UTC](https://discourse.julialang.org/t/my-experiences-reading-csvs-from-the-fannie-mae-datasets/8737/31 "2019-03-20T10:57:59Z")

</div>

> [@sdanisch](#):
>
> ReadOnlyMemoryError

Ok, so we are seeing the same issues.

---

<div class="post-metadata">

**Author:** ![xiaodai](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/xiaodai/32/15937_2.png) [@xiaodai](https://discourse.julialang.org/u/xiaodai)\
**Post date:** [March 20, 2019, 11:51pm UTC](https://discourse.julialang.org/t/my-experiences-reading-csvs-from-the-fannie-mae-datasets/8737/32 "2019-03-20T23:51:48Z")

</div>

Oh wow. Using R’s data.table via RCall and then writing the file to disk using feather and then reading it in using Feather.jl is faster than all the CSV parsers in the Julia-verse I’ve tried.

See it for yourself

```julia
download("https://github.com/xiaodaigh/testing/raw/master/Performance_2016Q1.zip", "ok.zip")

run(`unzip ok.zip`)
using FileIO, CSVFiles, DataFrames

using RCall, DataFrames, Feather
path = "Performance_2016Q1.csv"

function fread(path)
  R"""
  memory.limit(4095*2)  
  feather::write_feather(data.table::fread($path), "x.feather")
  gc()
  """;  

Feather.read("x.feather")
end

@time a = fread(path) # 20 seconds vs 50 seconds vs Julia CSV readers

```

---

<div class="post-metadata">

**Author:** ![matthieu](https://avatars.discourse-cdn.com/v4/letter/m/da6949/32.png) [@matthieu](https://discourse.julialang.org/u/matthieu)\
**Post date:** [March 21, 2019, 1:12pm UTC](https://discourse.julialang.org/t/my-experiences-reading-csvs-from-the-fannie-mae-datasets/8737/33 "2019-03-21T13:12:22Z")

</div>

I have a hard time understanding why this is faster than rget! In any case, maybe you could write a small package with this function?

---

<div class="post-metadata">

**Author:** ![sdanisch](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/sdanisch/32/1406_2.png) [@sdanisch](https://discourse.julialang.org/u/sdanisch)\
**Post date:** [March 21, 2019, 1:43pm UTC](https://discourse.julialang.org/t/my-experiences-reading-csvs-from-the-fannie-mae-datasets/8737/34 "2019-03-21T13:43:07Z")

</div>

Because Feather was specifically designed for performance, while csv was born out of convenience…

```julia
Feather is a fast, lightweight, and easy-to-use binary file format for storing data frames. It has a few specific design goals:

    Lightweight, minimal API: make pushing data frames in and out of memory as simple as possible

    Language agnostic: Feather files are the same whether written by Python or R code. Other languages can read and write Feather files, too.

    High read and write performance. When possible, Feather operations should be bound by local disk performance.

```

---

<div class="post-metadata">

**Author:** ![matthieu](https://avatars.discourse-cdn.com/v4/letter/m/da6949/32.png) [@matthieu](https://discourse.julialang.org/u/matthieu)\
**Post date:** [March 21, 2019, 1:50pm UTC](https://discourse.julialang.org/t/my-experiences-reading-csvs-from-the-fannie-mae-datasets/8737/35 "2019-03-21T13:50:33Z")

</div>

The weird thing is that, to communicate between R and Julia, rget is slower than writing/reading a feather file.

---

<div class="post-metadata">

**Author:** ![sdanisch](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/sdanisch/32/1406_2.png) [@sdanisch](https://discourse.julialang.org/u/sdanisch)\
**Post date:** [March 21, 2019, 2:39pm UTC](https://discourse.julialang.org/t/my-experiences-reading-csvs-from-the-fannie-mae-datasets/8737/36 "2019-03-21T14:39:35Z")

</div>

Why is that weird?  
Communication between R & Julia should be very fast, reading/writing feather should be substantially faster than reading a CSV file, even if you use the absolute best CSV reader.

---

<div class="post-metadata">

**Author:** ![xiaodai](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/xiaodai/32/15937_2.png) [@xiaodai](https://discourse.julialang.org/u/xiaodai)\
**Post date:** [March 21, 2019, 2:44pm UTC](https://discourse.julialang.org/t/my-experiences-reading-csvs-from-the-fannie-mae-datasets/8737/37 "2019-03-21T14:44:51Z")

</div>

The issue isn’t rget is slower it’s that reading the CSV in R then write it out to feather and then reading it in is faster than reading the CSV directly in Julia

Maybe a faster CSV reader in Julia will take time to emerge or it’s not possible at all. It’s not certain either way for me.

---

<div class="post-metadata">

**Author:** ![sdanisch](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/sdanisch/32/1406_2.png) [@sdanisch](https://discourse.julialang.org/u/sdanisch)\
**Post date:** [March 21, 2019, 2:57pm UTC](https://discourse.julialang.org/t/my-experiences-reading-csvs-from-the-fannie-mae-datasets/8737/38 "2019-03-21T14:57:56Z")

</div>

Ah, guess I should have looked at what `rget` actually does… I didn’t realize, that the R benchmark never actually benchmarked the pure R performance, but instead also converting it to Julia etc!

---

<div class="post-metadata">

**Author:** ![matthieu](https://avatars.discourse-cdn.com/v4/letter/m/da6949/32.png) [@matthieu](https://discourse.julialang.org/u/matthieu)\
**Post date:** [March 21, 2019, 3:45pm UTC](https://discourse.julialang.org/t/my-experiences-reading-csvs-from-the-fannie-mae-datasets/8737/39 "2019-03-21T15:45:18Z")

</div>

Oh I see. So rget is still faster than reading/writing with feather? In any case, could you create a fread package that does this kind of stuff automatically? I think it would be useful.

---

<div class="post-metadata">

**Author:** ![xiaodai](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/xiaodai/32/15937_2.png) [@xiaodai](https://discourse.julialang.org/u/xiaodai)\
**Post date:** [March 21, 2019, 3:49pm UTC](https://discourse.julialang.org/t/my-experiences-reading-csvs-from-the-fannie-mae-datasets/8737/40 "2019-03-21T15:49:10Z")

</div>

I guess so… this is so weird though. All Julia CSV readers feel lethargic atm. Grouping operstions are improving though.

[Previous page](https://discourse.julialang.org/t/my-experiences-reading-csvs-from-the-fannie-mae-datasets/8737.md?page=1)

[Next page](https://discourse.julialang.org/t/my-experiences-reading-csvs-from-the-fannie-mae-datasets/8737.md?page=3)