# Dealing with missing values in DataFrame (again)

**URL:** <https://discourse.julialang.org/t/dealing-with-missing-values-in-dataframe-again/45373>\
**Category:** Data\
**Created:** [August 22, 2020, 4:29pm UTC](https://discourse.julialang.org/t/dealing-with-missing-values-in-dataframe-again/45373 "2020-08-22T16:29:03Z")\
**Posts on this page:** 6\
**Page:** 1

<div class="post-metadata">

**Author:** ![gaspardelanuit](https://avatars.discourse-cdn.com/v4/letter/g/f475e1/32.png) [@gaspardelanuit](https://discourse.julialang.org/u/gaspardelanuit)\
**Post date:** [August 22, 2020, 4:29pm UTC](https://discourse.julialang.org/t/dealing-with-missing-values-in-dataframe-again/45373/1 "2020-08-22T16:29:03Z")

</div>

I am wondering if there has been progress on the usability of the missing interface.  
I am trying to write most of my R commands into Julia step by step to build a guide that would have interested people switch over easily.

However I am stuck just before I could get started.

I am working with standard dataset widely used in academic research in finance and economics, the crsp stock files (the daily stock file here)

```julia
using DataFrames, Pipe, CSV
df_dsf = CSV.File("./dsf.csv") |> DataFrame();
df_dsf[1:4, [:PERMNO, :date, :RET]]

```

This returns the dataset of interest:

```nohighlight
4×3 DataFrame
│ Row │ PERMNO │ date │ RET │
│ │ Int64 │ Int64 │ String? │
├─────┼────────┼──────────┼───────────┤
│ 1 │ 10000 │ 19860106 │ missing │
│ 2 │ 10000 │ 19860107 │ C │
│ 3 │ 10000 │ 19860108 │ -0.024390 │
│ 4 │ 10000 │ 19860109 │ 0.000000 │

```

As you can see `RET` (stock returns) are a mix of strings (delisting flags for example), missing values, and floats.  
Typically we parse this dataset to convert all to numeric and default to missing. For those familiar with R, a data.table syntax would be:

```nohighlight
df_dsf[, ret_num := as.numeric(RET)]

```

and `as.numeric` takes care of defaulting both missing and strings to missing (NA).

I am only diving into DataFrame so excuse the syntax but here is my approximation:

```julia
df_dsf = @pipe df_dsf |> 
    transform(_, :RET => ByRow(passmissing(x->tryparse(Float64, x))) => :ret_num);
replace!(df_dsf.ret_num , nothing => missing);

```

The first command is barely readable and assumes long time scavenging for information on **all of its three** functions (`ByRow`, `passmissing` and `tryparse`).  
And on top of this because we cannot default to missing in parse (I saw a bunch of activity on GitHub around this very question), we need to add another command to convert all of the `nothing` (from parsing strings) ex-post.

I do not mean to be critical. I am here to learn. I hope we have or find easier way of expressing such simple data transformation using DataFrame.  
This is maybe an extreme example (I have found other parts of DataFrame to work super well), but it is also the first data transformation lots of academics in finance are doing; it is typical and frequent if not fully representative.

Thanks for your help.

---

<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:** [August 22, 2020, 5:00pm UTC](https://discourse.julialang.org/t/dealing-with-missing-values-in-dataframe-again/45373/2 "2020-08-22T17:00:08Z")

</div>

See this issue:  
[https://github.com/JuliaData/DataFrames.jl/issues/2314](https://github.com/JuliaData/DataFrames.jl/issues/2314)  
That won’t address the `tryparse` annoyance, but note you can do `something(tryparse(Float64, x), missing)` instead calling `replace!` after the fact.

---

<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:** [August 22, 2020, 5:13pm UTC](https://discourse.julialang.org/t/dealing-with-missing-values-in-dataframe-again/45373/3 "2020-08-22T17:13:50Z")

</div>

Unclear exactly what you want. I would just set it to `missing` right after reading in the file.

```julia
df_dsf = replace(df_dsf.RET,"C"=>missing) .|> identity

```

The call to `identity` will fix the column type to `Union{Missing,Float64}`.

Edit: And because there are like 8 missing codes `(A,B,C,D,E,S,T,P)`, you can just generalize the replacement condition to cover all of them.

```julia
replace(v -> v isa String ? missing : v,df_dsf.RET) .|> identity

```

---

<div class="post-metadata">

**Author:** ![gaspardelanuit](https://avatars.discourse-cdn.com/v4/letter/g/f475e1/32.png) [@gaspardelanuit](https://discourse.julialang.org/u/gaspardelanuit)\
**Post date:** [August 22, 2020, 6:00pm UTC](https://discourse.julialang.org/t/dealing-with-missing-values-in-dataframe-again/45373/4 "2020-08-22T18:00:35Z")

</div>

Thank you for the help.

Honestly, my personal problem is a little bit of a sideshow here. I just want to raise awareness to some of the problems when dealing with missing even at a very basic level.

I think the issue linked by @nalimilan captures parts of the experience I am trying to share. AFAIK this is still an open issue. I just want to make sure we try to find a resolution before we rush into freezing DataFrames.

---

<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:** [August 23, 2020, 3:18am UTC](https://discourse.julialang.org/t/dealing-with-missing-values-in-dataframe-again/45373/5 "2020-08-23T03:18:59Z")

</div>

I’ll add that `CSV.File` also accepts some keyword arguments related to parsing of `missing` values when reading files. This case would likely be covered by the `missingstrings` kwarg.

---

<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:** [August 23, 2020, 12:51pm UTC](https://discourse.julialang.org/t/dealing-with-missing-values-in-dataframe-again/45373/6 "2020-08-23T12:51:54Z")

</div>

I’ve been playing around with this example and I do think there are some syntax things that will make things better without getting too much in the weeds with regards to missing values.

1. DataFramesMeta to get rid of the anonymous function
2. Using DataFramesMeta to hopefully make `ByRow` easier
3. Hopefully using DataFramesMeta to work with literals instead of symbols
4. Using `passmissing(tryparse)`

```julia
df_dsf = @pipe df_dsf |>
    @transform(_, ret_num = @row passmissing(tryparse)(Float64, RET)
    @transform(_, ret_num = @row isnothing(ret_num) ? missing : ret_num)

```

This is pretty good, and we haven’t worked with missing values yet.

Next, with regards to missing values we could add an option for `tryparse` to return a default value.

```julia
df_dsf = @pipe df_dsf |>
    @transform(_, ret_num = @row passmissing(tryparse)(Float64, RET, missing)

```

This looks pretty good to me, but we haven’t even touched base DataFrames missing handling. It would be helpful if you could comment on the issue discussion `missing`s [here](https://github.com/JuliaData/DataFrames.jl/issues/2314) about what specific ways your common problems would be most helped by the proposed chnages.

EDIT: not to mention the `CSV.jl` `missingstring` option, which would probably eliminate errant strings from your data frame in the first place.

EDIT: Also I’ve filed an issue for `tryparse` [here](https://github.com/JuliaLang/julia/issues/37162).