# Csv and dataframes and missing values

**URL:** <https://discourse.julialang.org/t/csv-and-dataframes-and-missing-values/62706>\
**Category:** New to Julia\
**Tags:** dataframes, csv\
**Created:** [June 10, 2021, 11:00pm UTC](https://discourse.julialang.org/t/csv-and-dataframes-and-missing-values/62706 "2021-06-10T23:00:32Z")\
**Posts on this page:** 10\
**Page:** 1

<div class="post-metadata">

**Author:** ![purplishrock](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/purplishrock/32/13451_2.png) [@purplishrock](https://discourse.julialang.org/u/purplishrock)\
**Post date:** [June 10, 2021, 11:00pm UTC](https://discourse.julialang.org/t/csv-and-dataframes-and-missing-values/62706/1 "2021-06-10T23:00:33Z")

</div>

Quite possibly one of the most beloved topics in this forum …  
I have searched the forum and cannot find simple answers, or the answers are (literally) years old.

My csv file has strings and floats which might be missing but i want to substitute “” and 0.0. Is it possible to get CSV to do that automagically ?  
I have not found a way reading through the CSV documentation.

Assuming that the automagic conversion can’t be done, then, if you are reading into a DataFrame, you can use dropmissing to get rid of rows where the offending column has a missing value. That is actually good enough most of the time, but now i have an instance where that’s not good enough.

So it looks like the best way to handle the situation is to use replace on the dataframe replacing values of missing with the desired value ? is that right ?

---

<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:** [June 10, 2021, 11:49pm UTC](https://discourse.julialang.org/t/csv-and-dataframes-and-missing-values/62706/2 "2021-06-10T23:49:33Z")

</div>

I don’t think there is a way to make the `""` become `0` instead of missing when you read it in.

But rather than `replace` you can use `coalesce.(df.x, 0)`

---

<div class="post-metadata">

**Author:** ![purplishrock](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/purplishrock/32/13451_2.png) [@purplishrock](https://discourse.julialang.org/u/purplishrock)\
**Post date:** [June 10, 2021, 11:54pm UTC](https://discourse.julialang.org/t/csv-and-dataframes-and-missing-values/62706/3 "2021-06-10T23:54:07Z")

</div>

I meant that something like

```julia

a,b,,1.0,2.0
a,b,c,,3.0

```

should be read as

“a”,“b”,“”,1.0,2.0  
“a”,“b”,“c”,0.0,3.0

so not “” → 0.0 but  
missing string → “”  
missing float64 → 0.0

thanks for the hint about coalesce, i didn’t know about that function, I’ll go read up on it.

---

<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:** [June 11, 2021, 12:06am UTC](https://discourse.julialang.org/t/csv-and-dataframes-and-missing-values/62706/4 "2021-06-11T00:06:49Z")

</div>

oh i see. No, there is not a way to do this. You will have to use `coalesce`.

---

<div class="post-metadata">

**Author:** ![rafael.guerra](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/rafael.guerra/32/216610_2.png) [@rafael.guerra](https://discourse.julialang.org/u/rafael.guerra)\
**Post date:** [June 11, 2021, 10:45am UTC](https://discourse.julialang.org/t/csv-and-dataframes-and-missing-values/62706/5 "2021-06-11T10:45:21Z")

</div>

@purplishrock, shouldn’t a proper csv file have at least empty slots for the missing data?  
Example:

```julia
a,b,,1.0,2.0
a,b,c,,3.0

```

These would then load as missings:

```julia
julia> CSV.File(file; header=false)
2-element CSV.File{false}:
 CSV.Row: (Column1 = "a", Column2 = "b", Column3 = missing, Column4 = 1.0, Column5 = 2.0)
 CSV.Row: (Column1 = "a", Column2 = "b", Column3 = "c", Column4 = missing, Column5 = 3.0)

```

---

<div class="post-metadata">

**Author:** ![purplishrock](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/purplishrock/32/13451_2.png) [@purplishrock](https://discourse.julialang.org/u/purplishrock)\
**Post date:** [June 12, 2021, 7:23pm UTC](https://discourse.julialang.org/t/csv-and-dataframes-and-missing-values/62706/6 "2021-06-12T19:23:34Z")

</div>

yes, that’s right. Without using “preformatted text”, the extra commas i had in my example didn’t show up. You should now see them.

---

<div class="post-metadata">

**Author:** ![rafael.guerra](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/rafael.guerra/32/216610_2.png) [@rafael.guerra](https://discourse.julialang.org/u/rafael.guerra)\
**Post date:** [June 12, 2021, 7:36pm UTC](https://discourse.julialang.org/t/csv-and-dataframes-and-missing-values/62706/7 "2021-06-12T19:36:51Z")

</div>

So, in that case as indicated by @pdeffebach, you can use coalesce:

```julia
df = CSV.File(file; header=false) |> DataFrame
df[:,3] .= coalesce.(df[:,3], "")
df[:,4] .= coalesce.(df[:,4], 0)
julia> df
2×5 DataFrame
│ Row │ Column1 │ Column2 │ Column3 │ Column4 │ Column5 │
│ │ String │ String │ String? │ Float64? │ Float64 │
├─────┼─────────┼─────────┼─────────┼──────────┼─────────┤
│ 1 │ a │ b │ │ 1.0 │ 2.0 │
│ 2 │ a │ b │ c │ 0.0 │ 3.0 │

```

---

<div class="post-metadata">

**Author:** ![kevbonham](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/kevbonham/32/216165_2.png) [@kevbonham](https://discourse.julialang.org/u/kevbonham)\
**Post date:** [June 12, 2021, 8:19pm UTC](https://discourse.julialang.org/t/csv-and-dataframes-and-missing-values/62706/8 "2021-06-12T20:19:06Z")

</div>

And if you want to change the eltype of the column to exclude `Missing`, just do `=` instead of `.=`, at the cost of reallocating

---

<div class="post-metadata">

**Author:** ![purplishrock](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/purplishrock/32/13451_2.png) [@purplishrock](https://discourse.julialang.org/u/purplishrock)\
**Post date:** [June 12, 2021, 10:46pm UTC](https://discourse.julialang.org/t/csv-and-dataframes-and-missing-values/62706/9 "2021-06-12T22:46:46Z")

</div>

Thank you @kevbonham ! I was wondering about that.

I am sort of wondering , given that you can provide an optional arg to set the expected type in CSV, why you cannot also provide an array which is “use this value in place of missing” as an optional arg.

---

<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:** [June 12, 2021, 11:05pm UTC](https://discourse.julialang.org/t/csv-and-dataframes-and-missing-values/62706/10 "2021-06-12T23:05:28Z")

</div>

Yeah probably. Feel free to file an issue on github.