# 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:** 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:** [February 1, 2018, 10:49am UTC](https://discourse.julialang.org/t/my-experiences-reading-csvs-from-the-fannie-mae-datasets/8737/1 "2018-02-01T10:49:15Z")

</div>

I am pretty excited about the [Fannie Mae data](http://www.fanniemae.com/portal/funding-the-market/data/loan-performance-data.html) being released so I tried to load the data into Julia. Unfortunately, it’s too much work trying to produce a MWE as being able to load large datasets is part of the point. But if you are interested you should definitely download the data from the Fannie website.

I have extracted the first file in the Performance dataset so I tried to load it using **CSV.jl, TextParse.jl, and uCSV.jl**. All of them **failed** to load the data even after setting delim to ‘|’. This is important for wider adoption of Julia for data tasks; as I can see how someone who is not invested in Julia will move on to pandas and/or R at this point, as those tools just work. So some catching up to do on reading CSVs.

**R’s data.table’s `fread`** was quite good in this regard as I can just call `fread` and it reads it fine and I didn’t even have to specify the delim as it was auto-detected. Neat!

I got it to work in Julia by setting all the types, and because most columns contained missing, I needed `Union{...,Missing}` and that might have impacted performance. But it took 45 seconds vs data.table’s 20 seconds.

So it’s fair to ask; why use Julia at all? Because I think it has potential, especially with the JuliaDB.jl being worked on, and I think I can optimize many of the tasks to be faster than R in the future. But for now R seems to be better at manipulating “medium” data like Fannie Mae’s.

I will **summarize** some of my findings here

1. Using RCall.jl to read the CSV and saving it as feather file is a good strategy; as it’s almost as quick, and you don’t need to specify the types and it enables faster reads in the future by just reading the feather files
2. If you specify all the column types then your chance of being able to read it increases and it is slightly faster than 1.

Further details below for those that are interested

**Code to read in data in Julia**

```julia
using DataFrames, CSV, Missings

dirpath = "d:/data/fannie_mae/"
dirpaths = readdir(dirpath)
filepath = joinpath(dirpath, "Performance_2000Q1.txt")

const types = [
    String, Union{String, Missing}, Union{String, Missing}, Union{Float64, Missing}, Union{Float64, Missing}, 
    Union{Float64, Missing}, Union{Float64, Missing}, Union{Float64, Missing}, Union{String, Missing}, Union{String, Missing},
    Union{String, Missing}, Union{String, Missing}, Union{String, Missing}, Union{String, Missing}, Union{String, Missing}, 
    Union{String, Missing}, Union{String, Missing}, Union{Float64, Missing}, Union{Float64, Missing}, Union{Float64, Missing}, 
    Union{Float64, Missing}, Union{Float64, Missing}, Union{Float64, Missing}, Union{Float64, Missing}, Union{Float64, Missing}, 
    Union{Float64, Missing}, Union{Float64, Missing}, Union{Float64, Missing}, Union{String, Missing}, Union{Float64, Missing}, 
    Union{String, Missing}]

@time perf = CSV.read(filepath, delim='|', header = false, types = types) #45;

```

**Read using RCall.jl and saving as feather**  
Interestingly, I can read the CSV using R’s data.table’s `fread` and save the data as Feather and read in the saved feather file in about 50 seconds. This seems to be a better strategy as saving a feather file in Julia takes longer than in R; `@time Feather.write("julia_feather.feather", perf) # 27`

```julia
using RCall
using Feather
function rfread(path)
    R"""
    feather::write_feather(data.table::fread($path), 'tmp.feather')
    gc()
    """
    Feather.read("tmp.feather")
end

@time perf = rfread(filepath) # 50 seconds

@time Feather.read("tmp.feather") # 9 seconds only

```

---

<div class="post-metadata">

**Author:** ![Tamas\_Papp](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/tamas_papp/32/25949_2.png) [@Tamas\_Papp](https://discourse.julialang.org/u/Tamas_Papp)\
**Post date:** [February 1, 2018, 11:42am UTC](https://discourse.julialang.org/t/my-experiences-reading-csvs-from-the-fannie-mae-datasets/8737/2 "2018-02-01T11:42:14Z")

</div>

I don’t know if you did this already, but typically opening an issue is the most helpful and constructive thing to do if you find a bug.

---

<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:** [February 1, 2018, 11:49am UTC](https://discourse.julialang.org/t/my-experiences-reading-csvs-from-the-fannie-mae-datasets/8737/3 "2018-02-01T11:49:49Z")

</div>

yeah. i have done one and will do more once i find a mwe. but it’s hard as the files are quite large, so it takes time to find the culprit.

---

<div class="post-metadata">

**Author:** ![Liso](https://avatars.discourse-cdn.com/v4/letter/l/898d66/32.png) [@Liso](https://discourse.julialang.org/u/Liso)\
**Post date:** [February 1, 2018, 12:02pm UTC](https://discourse.julialang.org/t/my-experiences-reading-csvs-from-the-fannie-mae-datasets/8737/4 "2018-02-01T12:02:17Z")

</div>

Sample files are without problem?

[https://loanperformancedata.fanniemae.com/lppub-docs/performance-sample-file.txt](https://loanperformancedata.fanniemae.com/lppub-docs/performance-sample-file.txt) [https://loanperformancedata.fanniemae.com/lppub-docs/acquisition-sample-file.txt](https://loanperformancedata.fanniemae.com/lppub-docs/acquisition-sample-file.txt)

---

<div class="post-metadata">

**Author:** ![avik](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/avik/32/17_2.png) [@avik](https://discourse.julialang.org/u/avik)\
**Post date:** [February 1, 2018, 12:04pm UTC](https://discourse.julialang.org/t/my-experiences-reading-csvs-from-the-fannie-mae-datasets/8737/5 "2018-02-01T12:04:27Z")

</div>

Thanks @xiaodai. There is no doubt JuliaDB needs to be able to work with this data seamlessly, and quickly. Finding and using real world data however is not always an easy task, so reports such as these are very helpful. They’ll certainly help improve JuliaDB as well as other Julia packages.

Regards

Avik

---

<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:** [February 1, 2018, 12:08pm UTC](https://discourse.julialang.org/t/my-experiences-reading-csvs-from-the-fannie-mae-datasets/8737/6 "2018-02-01T12:08:40Z")

</div>

recommend that you download the full data.

---

<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:** [February 1, 2018, 5:14pm UTC](https://discourse.julialang.org/t/my-experiences-reading-csvs-from-the-fannie-mae-datasets/8737/7 "2018-02-01T17:14:38Z")

</div>

I guess the failure to load when you don’t specify the types was due to the fact that missing values appear only beyond the first rows used for type detection? We really need to do something about this. BTW, it would have been useful to post the error message.

---

<div class="post-metadata">

**Author:** ![Tamas\_Papp](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/tamas_papp/32/25949_2.png) [@Tamas\_Papp](https://discourse.julialang.org/u/Tamas_Papp)\
**Post date:** [February 1, 2018, 5:45pm UTC](https://discourse.julialang.org/t/my-experiences-reading-csvs-from-the-fannie-mae-datasets/8737/8 "2018-02-01T17:45:17Z")

</div>

I think that reading _all_ the data before type detection would be a reasonable default. For small datasets, the performance drop is not an issue, and it would not _fail_ on large datasets, just be slower. Users who read large data and care about speed more than the price of occasional failure could still restrict it to the first few rows.

The alternative is “widen when necessary”, similar to the mapping/collecting functions of `Base`, with the understanding that here you will guess wrong initially most of the time, but things should work out.

---

<div class="post-metadata">

**Author:** ![sswatson](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/sswatson/32/5135_2.png) [@sswatson](https://discourse.julialang.org/u/sswatson)\
**Post date:** [February 1, 2018, 5:48pm UTC](https://discourse.julialang.org/t/my-experiences-reading-csvs-from-the-fannie-mae-datasets/8737/9 "2018-02-01T17:48:16Z")

</div>

Just to note that one can fix this problem by setting the `rows_for_type_detect` keyword argument of `CSV.read` to a sufficiently large number (at risk of saying something that may be obvious to some). Clearly this is not the permanent solution you’re calling for, but I found it to be the simplest path to getting my code working. For users who are dealing with this problem, it could be worth a try.

---

<div class="post-metadata">

**Author:** ![shashi](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/shashi/32/1824_2.png) [@shashi](https://discourse.julialang.org/u/shashi)\
**Post date:** [February 1, 2018, 5:56pm UTC](https://discourse.julialang.org/t/my-experiences-reading-csvs-from-the-fannie-mae-datasets/8737/10 "2018-02-01T17:56:43Z")

</div>

I was able to load the file using JuliaDB:

```julia
loadtable("Performance_2000Q1.txt",
     delim='|',
     nastrings=vcat(TextParse.NA_STRINGS, "X"),
     header_exists=false);

```

The options: 1) set `|` as delimiter, 2) considers `X` as a missing value (along with predefined ones), 3) says that headers don’t exist.

It took 80 seconds though… But can probably be improved if you specify all the column types.

In the process however I encountered a very unhelpful error message (when I didn’t specify `header_exists=false`) so that’s something I will try and fix.

---

<div class="post-metadata">

**Author:** ![piever](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/piever/32/1815_2.png) [@piever](https://discourse.julialang.org/u/piever)\
**Post date:** [February 1, 2018, 6:06pm UTC](https://discourse.julialang.org/t/my-experiences-reading-csvs-from-the-fannie-mae-datasets/8737/11 "2018-02-01T18:06:30Z")

</div>

It seems to me there are two issues at the moment:

1. File starts with too many non-missing values before encountering a `missing`: it fails because it assumes the column does not allow missing data
2. File starts with too many `missing` values before encountering a non-missing: it fails because it infers the column is of type `Missing`

I’d tend to agree with @tpapp that it’s probably better to be slow but correct by default.

---

<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:** [February 1, 2018, 6:31pm UTC](https://discourse.julialang.org/t/my-experiences-reading-csvs-from-the-fannie-mae-datasets/8737/12 "2018-02-01T18:31:03Z")

</div>

Agreed, we really need to fix that failure when missing values appear to late in the data. Until we implement something more efficient, I guess we could simply catch the exception and restart parsing the file with a wider type for the offending column.

---

<div class="post-metadata">

**Author:** ![piever](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/piever/32/1815_2.png) [@piever](https://discourse.julialang.org/u/piever)\
**Post date:** [February 1, 2018, 6:43pm UTC](https://discourse.julialang.org/t/my-experiences-reading-csvs-from-the-fannie-mae-datasets/8737/13 "2018-02-01T18:43:59Z")

</div>

I think `TextParse` is pretty good at this and doesn’t seem to be affected by error 1) or 2). Maybe whatever strategy it’s using could be implemented by CSV as well?

Another not very subtle approach is to start with _all_ columns allowing missing data and make them stricter at the end. Does one need to copy the data to go from `Vector{Union{T, Missing}}` to `Vector{T}` ?

---

<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:** [February 1, 2018, 7:22pm UTC](https://discourse.julialang.org/t/my-experiences-reading-csvs-from-the-fannie-mae-datasets/8737/14 "2018-02-01T19:22:32Z")

</div>

> [@piever](#):
>
> I think TextParse is pretty good at this and doesn’t seem to be affected by error 1) or 2). Maybe whatever strategy it’s using could be implemented by CSV as well?

No idea. Anyway the strategies are relatively clear, we just need somebody to do the work. 😉

> [@piever](#):
>
> Another not very subtle approach is to start with all columns allowing missing data and make them stricter at the end. Does one need to copy the data to go from Vector{Union{T, Missing}} to Vector{T} ?

In theory no copy would be needed in either direction, so it would be even better to start with a `Vector{T}`, which is faster, and widen it if needed. Currently a copy is made, though, but it shouldn’t be too bad in terms of performance compared with the time required to parse the file.

---

<div class="post-metadata">

**Author:** ![pasha](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/pasha/32/3319_2.png) [@pasha](https://discourse.julialang.org/u/pasha)\
**Post date:** [February 1, 2018, 8:14pm UTC](https://discourse.julialang.org/t/my-experiences-reading-csvs-from-the-fannie-mae-datasets/8737/15 "2018-02-01T20:14:08Z")

</div>

As you know the dataframe ecosystem in Julia is going through some rework with Nullables as DataFrames 0.11 gets to a full release.

In this state I also had a number of travails in getting a padded CSV with a blank line in it. DataFrames.readtable() is soon to be deprecated but has the ignorepadding parameter I needed.

---

<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:** [February 1, 2018, 9:00pm UTC](https://discourse.julialang.org/t/my-experiences-reading-csvs-from-the-fannie-mae-datasets/8737/16 "2018-02-01T21:00:30Z")

</div>

Yeah. Adding more rows to type was something I tried but eventually it gave another error which was hard to decipher. It’s hard to give a MWE as the file is large; so downloading the data and setting up a test suite to test “big ticket” datasets like Fannie Mae and Lending Club is the way to go

---

<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:** [October 24, 2018, 11:07pm UTC](https://discourse.julialang.org/t/my-experiences-reading-csvs-from-the-fannie-mae-datasets/8737/17 "2018-10-24T23:07:07Z")

</div>

Happy to report that TextParse.jl can read the Fannie Mae data with minimal help now

```julia
using CSVFiles, FileIO
@time a = load(File(format"csv", file_path), delim = '|', type_detect_rows = 1000)

```

works (!!!) for the first Performance files! The same code without `type_detect_rows` still fails though, and it can’t auto detect the dlimiter like R’s `fread`. So it’s not yet able to topple data.table but it’s still a good performer now!

---

<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:** [February 20, 2019, 12:12pm UTC](https://discourse.julialang.org/t/my-experiences-reading-csvs-from-the-fannie-mae-datasets/8737/18 "2019-02-20T12:12:40Z")

</div>

Pretty excited. I can finally load the Fannie Mae data using JuliaDB

**Edit** actually it failed complaining about out of memory.

```julia
using Distributed
addprocs(6)

@time @everywhere using JuliaDB, Dagger

datapath = "c:/data/Performance_All\\"
ifiles = joinpath_(datapath, readdir(datapath))

colnames = ["loan_id", "monthly_rpt_prd", "servicer_name", "last_rt", "last_upb", "loan_age",
    "months_to_legal_mat" , "adj_month_to_mat", "maturity_date", "msa", "delq_status",
    "mod_flag", "zero_bal_code", "zb_dte", "lpi_dte", "fcc_dte","disp_dt", "fcc_cost",
    "pp_cost", "ar_cost", "ie_cost", "tax_cost", "ns_procs", "ce_procs", "rmw_procs",
    "o_procs", "non_int_upb", "prin_forg_upb_fhfa", "repch_flag", "prin_forg_upb_oth",
    "transfer_flg"];

@time jll = loadtable(
    ifiles,
    output = "c:/data/fm.jldb\\", 
    delim='|', 
    header_exists=false, 
    filenamecol = "filename", 
    chunks = 256, 
    type_detect_rows = 2000, 
    colnames = colnames, 
    indexcols=["loan_id"])

```

---

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

</div>

Does JuliaDB actually provide out of core functionally as it claims? I also want to explore this feature a little bit. As for now, I haven’t seen any good working example of this except for the one that uses TrueFX 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:** [March 1, 2019, 11:18pm UTC](https://discourse.julialang.org/t/my-experiences-reading-csvs-from-the-fannie-mae-datasets/8737/20 "2019-03-01T23:18:06Z")

</div>

Yeah. Still having trouble loading CSVs

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