# 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:** 3

<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, 4:02pm UTC](https://discourse.julialang.org/t/my-experiences-reading-csvs-from-the-fannie-mae-datasets/8737/41 "2019-03-21T16:02:44Z")

</div>

If you set `fread(path, nThreads = 1)` I get ~4s, while I get ~6s with TableReader… So not that much worse 😉  
I wouldn’t be surprised, if it’s relatively easy to process chunks in threads with TableReader as well!

---

<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, 4:07pm UTC](https://discourse.julialang.org/t/my-experiences-reading-csvs-from-the-fannie-mae-datasets/8737/42 "2019-03-21T16:07:29Z")

</div>

Last time i checked tablereader failed on Windows.

---

<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 23, 2019, 10:48pm UTC](https://discourse.julialang.org/t/my-experiences-reading-csvs-from-the-fannie-mae-datasets/8737/43 "2019-03-23T22:48:08Z")

</div>

Wow! Now TableReader is by far the best performer! But it’s a still a far cry from R’s `data.table::fread` at 2.5 seconds. Perhaps adding multithreading is the key then.

```julia
# download("https://github.com/xiaodaigh/testing/raw/master/Performance_2016Q1.zip", "ok.zip")
# run(`unzip -o ok.zip`)
using TableReader
path = "Performance_2016Q1.csv"
@time a = readcsv(path, delim = '|', hasheader = false); # 12~15 seconds

```

---

<div class="post-metadata">

**Author:** ![bicycle1885](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/bicycle1885/32/107_2.png) [@bicycle1885](https://discourse.julialang.org/u/bicycle1885)\
**Post date:** [March 24, 2019, 4:05am UTC](https://discourse.julialang.org/t/my-experiences-reading-csvs-from-the-fannie-mae-datasets/8737/44 "2019-03-24T04:05:00Z")

</div>

I tried `fread` and it was unbelievably fast! But I think parallel parsing (when Julia incorporates the parallel task runtime into its core) and other minor improvements will close the gap and make TableReader.jl more competitive in this CSV parser race.

---

<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 24, 2019, 4:12am UTC](https://discourse.julialang.org/t/my-experiences-reading-csvs-from-the-fannie-mae-datasets/8737/45 "2019-03-24T04:12:55Z")

</div>

Thank you for your great package. I am going to teach your package for sure!

---

<div class="post-metadata">

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

</div>

Going back to the question of why `Feather.read` is faster than `rget`, by default the `.feather` file is memory-mapped so the overhead is very low and there is no copying of the data in memory.

When I tried to reproduce the `feather::write_feather...` sequence in R I got a warning about not having the `bit64` package available which will mean that 64-bit integers are displayed as weird-looking floating point numbers. I believe this is why reading the file into Julia produces unusual values in the `V1` column

```julia
julia> a = Feather.read("/home/bates/Performance_2016Q1.feather")
6520505×31 DataFrames.DataFrame. Omitted printing of 16 columns
│ Row │ V1 │ V2 │ V3 │ V4 │ V5 │ V6 │ V7 │ V8 │ V9 │ V10 │ V11 │ V12 │ V13 │ V14 │ V15 │
│ │ Float64 │ String │ String │ Float64 │ Float64⍰ │ Int32 │ Int32 │ Int32⍰ │ String │ Int32 │ String │ String │ Int32⍰ │ String │ String │
├─────────┼──────────────┼────────────┼────────┼─────────┼───────────┼───────┼───────┼────────┼─────────┼───────┼────────┼────────┼─────────┼────────┼────────┤
│ 1 │ 4.94068e-313 │ 02/01/2016 │ OTHER │ 3.75 │ missing │ 1 │ 359 │ 359 │ 01/2046 │ 12260 │ 0 │ N │ missing │ │ │
│ 2 │ 4.94068e-313 │ 03/01/2016 │ │ 3.75 │ missing │ 2 │ 358 │ 357 │ 01/2046 │ 12260 │ 0 │ N │ missing │ │ │
│ 3 │ 4.94068e-313 │ 04/01/2016 │ │ 3.75 │ missing │ 3 │ 357 │ 356 │ 01/2046 │ 12260 │ 0 │ N │ missing │ │ │
│ 4 │ 4.94068e-313 │ 05/01/2016 │ │ 3.75 │ missing │ 4 │ 356 │ 355 │ 01/2046 │ 12260 │ 0 │ N │ missing │ │ │
│ 5 │ 4.94068e-313 │ 06/01/2016 │ │ 3.75 │ missing │ 5 │ 355 │ 354 │ 01/2046 │ 12260 │ 0 │ N │ missing │ │ │
│ 6 │ 4.94068e-313 │ 07/01/2016 │ │ 3.75 │ missing │ 6 │ 354 │ 353 │ 01/2046 │ 12260 │ 0 │ N │ missing │ │ │
│ 7 │ 4.94068e-313 │ 08/01/2016 │ │ 3.75 │ 64208.1 │ 7 │ 353 │ 352 │ 01/2046 │ 12260 │ 0 │ N │ missing │ │ │
│ 8 │ 4.94068e-313 │ 09/01/2016 │ │ 3.75 │ 64107.8 │ 8 │ 352 │ 351 │ 01/2046 │ 12260 │ 0 │ N │ missing │ │ │
│ 9 │ 4.94068e-313 │ 10/01/2016 │ │ 3.75 │ 64006.4 │ 9 │ 351 │ 350 │ 01/2046 │ 12260 │ 0 │ N │ missing │ │ │
⋮
│ 6520496 │ 4.94062e-312 │ 09/01/2016 │ │ 3.5 │ 2.38727e5 │ 5 │ 355 │ 345 │ 04/2046 │ 42200 │ 0 │ N │ missing │ │ │
│ 6520497 │ 4.94062e-312 │ 10/01/2016 │ │ 3.5 │ 2.37849e5 │ 6 │ 354 │ 343 │ 04/2046 │ 42200 │ 0 │ N │ missing │ │ │
│ 6520498 │ 4.94062e-312 │ 11/01/2016 │ │ 3.5 │ 2.36968e5 │ 7 │ 353 │ 341 │ 04/2046 │ 42200 │ 0 │ N │ missing │ │ │
│ 6520499 │ 4.94062e-312 │ 12/01/2016 │ │ 3.5 │ 2.36087e5 │ 8 │ 352 │ 339 │ 04/2046 │ 42200 │ 0 │ N │ missing │ │ │
│ 6520500 │ 4.94062e-312 │ 01/01/2017 │ │ 3.5 │ 2.35204e5 │ 9 │ 351 │ 336 │ 04/2046 │ 42200 │ 0 │ N │ missing │ │ │
│ 6520501 │ 4.94062e-312 │ 02/01/2017 │ │ 3.5 │ 2.34317e5 │ 10 │ 350 │ 334 │ 04/2046 │ 42200 │ 0 │ N │ missing │ │ │
│ 6520502 │ 4.94062e-312 │ 03/01/2017 │ │ 3.5 │ 2.33429e5 │ 11 │ 349 │ 332 │ 04/2046 │ 42200 │ 0 │ N │ missing │ │ │
│ 6520503 │ 4.94062e-312 │ 04/01/2017 │ │ 3.5 │ 2.32537e5 │ 12 │ 348 │ 330 │ 04/2046 │ 42200 │ 0 │ N │ missing │ │ │
│ 6520504 │ 4.94062e-312 │ 05/01/2017 │ │ 3.5 │ 2.31643e5 │ 13 │ 347 │ 328 │ 04/2046 │ 42200 │ 0 │ N │ missing │ │ │
│ 6520505 │ 4.94062e-312 │ 06/01/2017 │ │ 3.5 │ 2.30747e5 │ 14 │ 346 │ 326 │ 04/2046 │ 42200 │ 0 │ N │ missing │ │ │

```

Also, julia takes an error exit if, for example, I try `describe(a)`.

---

<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 25, 2019, 3:31pm UTC](https://discourse.julialang.org/t/my-experiences-reading-csvs-from-the-fannie-mae-datasets/8737/47 "2019-03-25T15:31:54Z")

</div>

can you post an issue in DataFrames for the `describe` error? we have tons of `try-catch` statements there to try prevent any error like that.

---

<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:** [March 25, 2019, 3:42pm UTC](https://discourse.julialang.org/t/my-experiences-reading-csvs-from-the-fannie-mae-datasets/8737/48 "2019-03-25T15:42:49Z")

</div>

When you say “julia takes an error exit”, you mean it crashes, right? Then it’s probably not a bug in `describe` but in Feather.jl.

---

<div class="post-metadata">

**Author:** ![dmbates](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/dmbates/32/44_2.png) [@dmbates](https://discourse.julialang.org/u/dmbates)\
**Post date:** [March 25, 2019, 4:02pm UTC](https://discourse.julialang.org/t/my-experiences-reading-csvs-from-the-fannie-mae-datasets/8737/49 "2019-03-25T16:02:59Z")

</div>

Yes, Julia crashes. I believe the problem is in Feather.read or perhaps the feather file written by the R packages is corrupt.

---

<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 25, 2019, 10:51pm UTC](https://discourse.julialang.org/t/my-experiences-reading-csvs-from-the-fannie-mae-datasets/8737/50 "2019-03-25T22:51:12Z")

</div>

> [@dmbates](#):
>
> I got a warning about not having the `bit64` package available which will mean that 64-bit integers are displayed as weird-looking floating point numbers.

Actually, when I use the data I actually supply the full list of column types. The read speed is not that different in that case in `data.table`. Actually the first column should be read as a string according to the official tutorial on Fannie Mae’s website.

---

<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:** [April 23, 2019, 12:30pm UTC](https://discourse.julialang.org/t/my-experiences-reading-csvs-from-the-fannie-mae-datasets/8737/51 "2019-04-23T12:30:39Z")

</div>

> [@xiaodai](#):
>
> ```julia
> # download("https://github.com/xiaodaigh/testing/raw/master/Performance_2000Q1.zip", "ok.zip") 
> # run(`unzip -o ok.zip`) 
> using TableReader
> path = "Performance_2000Q1.txt"
> @time a = readcsv(path, delim = '|', hasheader = false);
> 
> ```

I tried TableReader on a slightly larger file from the Fannie Mae dataset and it took 3 times longers when the files is only 200mb larger. 😭

---

<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:** [May 6, 2019, 2:26pm UTC](https://discourse.julialang.org/t/my-experiences-reading-csvs-from-the-fannie-mae-datasets/8737/52 "2019-05-06T14:26:20Z")

</div>

> [@xiaodai](#):
>
> I tried TableReader on a slightly larger file from the Fannie Mae dataset and it took 3 times longers when the files is only 200mb larger

This has now been fixed and I am happy to report that **CSV.jl** is now even faster than TableReader.jl on this dataset now with minimal “hand-holding” i.e. I don’t need to specify too much except the `delim` and `header`

```julia
using CSV
@time a = CSV.read(path, delim='|', header =0)

```

CSV.jl is also only about twice as slow as R’s `data.table::fread` even though `fread` is multi-threaded.

---

<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:** [May 6, 2019, 4:36pm UTC](https://discourse.julialang.org/t/my-experiences-reading-csvs-from-the-fannie-mae-datasets/8737/53 "2019-05-06T16:36:10Z")

</div>

Note that you don’t even need to specify the `delim='|'` any more as it’s detected automatically 😄

---

<div class="post-metadata">

**Author:** ![Juan](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/juan/32/7657_2.png) [@Juan](https://discourse.julialang.org/u/Juan)\
**Post date:** [June 24, 2019, 11:22am UTC](https://discourse.julialang.org/t/my-experiences-reading-csvs-from-the-fannie-mae-datasets/8737/54 "2019-06-24T11:22:43Z")

</div>

I thought JuliaDB was designed for out-of-core tasks.

At the end… Did you do any benchmark with different packages or options?

---

<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:** [August 24, 2019, 2:24am UTC](https://discourse.julialang.org/t/my-experiences-reading-csvs-from-the-fannie-mae-datasets/8737/55 "2019-08-24T02:24:29Z")

</div>

I am pretty excited! CSV.jl has gotten to the point where it beats using RCall.jl and `data.table::fread` hands down!

I can read the Fannie Data alot faster now! I can read the smallest file from Fannie Mae which is about 500mb in size using `CSV.read` on Julia 1.3 in about 5 seconds (11s including compilation), but RCall.jl and `fread` is up wards of 20 seconds (on Julia 1.2 as Rcall.jl isn’t working for me on 1.3).

However, pure `fread` is just under 3s, so is still faster than CSV.jl, but it’s a point where I wouldn’t reach for R just because of speed!

I can also load a 7GB dataset in about 320s with CSV.jl with `threaded=true`, however it’s only 42 seconds with `fread`. So for large datasets, there is still a gap in performance for large data.

---

<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:** [August 25, 2019, 2:14pm UTC](https://discourse.julialang.org/t/my-experiences-reading-csvs-from-the-fannie-mae-datasets/8737/56 "2019-08-25T14:14:27Z")

</div>

I tested reading every file of Fannie Mae’s data (from [Fannie Mae Single-Family Loan Performance Data | Fannie Mae](https://bit.ly/2HntX9I) and you need to register to download, but this is one of the best open data sources).

This is the timings I got from `CSV.read` vs `data.table::fread` on my computer with 64G RAM. I only recorded the timings once, but the point is not absolute precision here

![image](https://global.discourse-cdn.com/julialang/original/3X/9/5/95204b409e0b5c0c300955f026787622cc0d2aa1.png)

I have to say Julia CSV parsing is way better now vs before! Thanks to @quinnj’s great contributions! Even though `data.table::fread` is still better for the Fannie Mae case, I think it’s gotten to the point where I wouldn’t reach for R straight-away. The type-inferencing and auto-detection of delimiter are pretty awesome in CSV.jl at this time.  
To put things into perspective, let’s consider one of the most popular CSV readers in the R-sphere, `reader::read_csv`. It can’t detect the delimiter (duh, they would say CSV means _comma_ separated). Also, it is roughly 2x slower than CSV.jl! Every time I see a post/tweet recommending `readr::read_csv`, I die a little. Clearly, `data.table::fread` is the one to beat! So I’ve been spamming the various CSV readers in the Julia-verse with my comments about Fannie Mae and `fread`. In Python, `pandas.read_csv` is fairly competent, but can’t detect the delimiter correctly, and is slower than CSV.jl. Another new thing in Python-land is `pyarrow` which is quite fast, but if you convert the `pyarrow.Table` to pandas dataframe then it’s still slower than `CSV.read` which reads data and converts it to a `DataFrame`.

Curiously, I can get TextParse.jl to read the CSVs but not JuliaDB.jl.

---

<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:** [August 25, 2019, 2:26pm UTC](https://discourse.julialang.org/t/my-experiences-reading-csvs-from-the-fannie-mae-datasets/8737/57 "2019-08-25T14:26:45Z")

</div>

> [@xiaodai](#):
>
> using CSVFiles, FileIO @time a = load(File(format"csv", file\_path), delim = ‘|’, type\_detect\_rows = 1000)

Wow. This no longer works… Gotta to report a bug.

---

<div class="post-metadata">

**Author:** ![favba](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/favba/32/2735_2.png) [@favba](https://discourse.julialang.org/u/favba)\
**Post date:** [August 25, 2019, 5:06pm UTC](https://discourse.julialang.org/t/my-experiences-reading-csvs-from-the-fannie-mae-datasets/8737/58 "2019-08-25T17:06:29Z")

</div>

Are the axes inverted? Y-axis should be time and X-axis file size?

---

<div class="post-metadata">

**Author:** ![davidanthoff](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/davidanthoff/32/223493_2.png) [@davidanthoff](https://discourse.julialang.org/u/davidanthoff)\
**Post date:** [August 25, 2019, 7:12pm UTC](https://discourse.julialang.org/t/my-experiences-reading-csvs-from-the-fannie-mae-datasets/8737/59 "2019-08-25T19:12:50Z")

</div>

> [@xiaodai](#):
>
> Wow. This no longer works… Gotta to report a bug.

You need to use capital letters for the format specifier, and `delim` is not a keyword argument. `load(File(format"CSV", file_path), '|', type_detect_rows = 1000)` is the correct syntax here.

---

<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:** [August 25, 2019, 10:11pm UTC](https://discourse.julialang.org/t/my-experiences-reading-csvs-from-the-fannie-mae-datasets/8737/60 "2019-08-25T22:11:34Z")

</div>

Yeah

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

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