# DataFrame and Missings.replace()

**URL:** <https://discourse.julialang.org/t/dataframe-and-missings-replace/9834>\
**Category:** Data\
**Created:** [March 20, 2018, 12:18pm UTC](https://discourse.julialang.org/t/dataframe-and-missings-replace/9834 "2018-03-20T12:18:54Z")\
**Posts on this page:** 11\
**Page:** 1

<div class="post-metadata">

**Author:** ![johann.spies](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/johann.spies/32/8805_2.png) [@johann.spies](https://discourse.julialang.org/u/johann.spies)\
**Post date:** [March 20, 2018, 12:18pm UTC](https://discourse.julialang.org/t/dataframe-and-missings-replace/9834/1 "2018-03-20T12:18:54Z")

</div>

Why does this example (adapted from the DataFrames documentation) work:

```julia
julia> x = [1, 2, missing]
3-element Array{Union{Int64, Missings.Missing},1}:
 1       
 2       
  missing

julia> df = collect(Missings.replace(x, 1))
3-element Array{Int64,1}:
 1
 2
 1

```

and this not?:

```julia
input_file = "/home/js/Downloads/data-1512997404715.csv"
df = CSV.read(input_file, nullable=true)
x = collect(Missings.replace(df, "\\N"))
ERROR: MethodError: no method matching start(::DataFrames.DataFrame)
Closest candidates are:
  start(::SimpleVector) at essentials.jl:258
  start(::Base.MethodList) at reflection.jl:560
  start(::ExponentialBackOff) at error.jl:107

```

Stacktrace:  
[1] copy!(::Array{Any,1}, ::Missings.EachReplaceMissing{DataFrames.DataFrame,String}) at ./abstractarray.jl:573  
[2] \_collect(::UnitRange{Int64}, ::Missings.EachReplaceMissing{DataFrames.DataFrame,String}, ::Base.HasEltype, ::Base.HasLength) at ./array.jl:437  
[3] collect(::Missings.EachReplaceMissing{DataFrames.DataFrame,String}) at ./array.jl:431

```julia
I am building a script that create a sql dumpfile from a csv and want to replace all the missing values with "\N"

Regards
Johann
```

---

<div class="post-metadata">

**Author:** ![y4lu](https://avatars.discourse-cdn.com/v4/letter/y/47e85d/32.png) [@y4lu](https://discourse.julialang.org/u/y4lu)\
**Post date:** [March 20, 2018, 1:00pm UTC](https://discourse.julialang.org/t/dataframe-and-missings-replace/9834/2 "2018-03-20T13:00:07Z")

</div>

The top-most level of the dataframe object / struct isn’t the iterable part

```julia
df1 = DataFrame(a=collect(1:10), b= collect(10:-1:1));
d2 = collect(Missings.replace(df.columns, 0));
df2 = DataFrame(a = d2[1], b= d2[2])
> Row | x1 | x2
> ...

```

---

<div class="post-metadata">

**Author:** ![johann.spies](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/johann.spies/32/8805_2.png) [@johann.spies](https://discourse.julialang.org/u/johann.spies)\
**Post date:** [March 20, 2018, 1:20pm UTC](https://discourse.julialang.org/t/dataframe-and-missings-replace/9834/3 "2018-03-20T13:20:46Z")

</div>

> [@y4lu](#):
>
> df1 = DataFrame(a=collect(1:10), b= collect(10:-1:1));

Thanks. You inspired me to look at the source and this seems to work now where df is the original dataframe:

```julia
df2 = DataFrame(collect(Missings.replace(df.columns, "\\N")), names(df))

```

---

<div class="post-metadata">

**Author:** ![y4lu](https://avatars.discourse-cdn.com/v4/letter/y/47e85d/32.png) [@y4lu](https://discourse.julialang.org/u/y4lu)\
**Post date:** [March 20, 2018, 1:51pm UTC](https://discourse.julialang.org/t/dataframe-and-missings-replace/9834/4 "2018-03-20T13:51:20Z")

</div>

It doesn’t seem to be working properly though

```julia
df1 = DataFrame(a=[missing; collect(1:10)], b=[collect(10:-1:1); missing]);
d2 = collect(Missings.replace(df.columns, 0)); ## missing columns
d2 = collect(Missings.replace(df.columns[1], 0)); ## one column
d4= [collect(Missings.replace(a, 0)) for a in df.columns];
```

---

<div class="post-metadata">

**Author:** ![johann.spies](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/johann.spies/32/8805_2.png) [@johann.spies](https://discourse.julialang.org/u/johann.spies)\
**Post date:** [March 20, 2018, 2:07pm UTC](https://discourse.julialang.org/t/dataframe-and-missings-replace/9834/5 "2018-03-20T14:07:16Z")

</div>

Correct. My “solution” did not produce an error message but it did not do the job.

with

```julia
[collect(Missings.replace(a, 0)) for a in df.columns]

```

I now get

```julia
MethodError: Cannot `convert` an object of type String to an object of type Int64
This may have arisen from a call to the constructor Int64(...),
since type constructors fall back to convert methods.

Stacktrace:
 [1] replace(::Array{Union{Int64, Missings.Missing},1}, ::String) at /home/js/.julia/v0.6/Missings/src/Missings.jl:276
 [2] collect_to!(::Array{CategoricalArrays.CategoricalArray{String,1,UInt32,String,CategoricalArrays.CategoricalString{UInt32},Union{}},1}, ::Base.Generator{Array{Any,1},##21#22}, ::Int64, ::Int64) at ./array.jl:508
 [3] collect(::Base.Generator{Array{Any,1},##21#22}) at ./array.jl:476
 [4] include_string(::String, ::String) at ./loading.jl:522

```

Maybe I should first convert all the values in the dataframe to strings before trying this.  
In the end it would be written as strings to an pg\_dump file anyhow.  
Not that I know how to do it at this stage. But I will find out.

Thanks for thinking with me.

---

<div class="post-metadata">

**Author:** ![y4lu](https://avatars.discourse-cdn.com/v4/letter/y/47e85d/32.png) [@y4lu](https://discourse.julialang.org/u/y4lu)\
**Post date:** [March 20, 2018, 2:38pm UTC](https://discourse.julialang.org/t/dataframe-and-missings-replace/9834/6 "2018-03-20T14:38:05Z")

</div>

It’s close but still not quite

```julia
d = Dict([Union{Int64, Missing}=>0, Union{String, Missing}=>""])
d2 = [collect(Missings.replace(a, d[eltype(a)])) for a in df.columns];
> key union{Int64, Missing} not found 

```

Passing a full set of replacement missing values (one per column), as in ` for a,b in df.columns, dfmissingvec = [0, 0, "", "etc"]` should be okay though

---

<div class="post-metadata">

**Author:** ![johann.spies](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/johann.spies/32/8805_2.png) [@johann.spies](https://discourse.julialang.org/u/johann.spies)\
**Post date:** [March 22, 2018, 7:10am UTC](https://discourse.julialang.org/t/dataframe-and-missings-replace/9834/7 "2018-03-22T07:10:59Z")

</div>

My problem is that I will not know beforehand what the types of the columns will be.

---

<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 22, 2018, 9:59am UTC](https://discourse.julialang.org/t/dataframe-and-missings-replace/9834/8 "2018-03-22T09:59:49Z")

</div>

You can try something like:

```julia
for (v, name) in eachcol(df)
    df[name] = collect(Missings.replace(v, "\\N"))
end

```

` collect(Missings.replace(v, "\\N"))` can also be `coalesce.(v, "\\N")` or `recode(v, missing => "\\N")` (the latter is in CategoricalArrays). But if you don’t know the type of the columns in advance I don’t see how you could choose an appropriate replacement…

---

<div class="post-metadata">

**Author:** ![johann.spies](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/johann.spies/32/8805_2.png) [@johann.spies](https://discourse.julialang.org/u/johann.spies)\
**Post date:** [March 22, 2018, 1:51pm UTC](https://discourse.julialang.org/t/dataframe-and-missings-replace/9834/9 "2018-03-22T13:51:58Z")

</div>

Thanks. However that will only work on columns with a `String` type.

With `names(df)` I get an array of column headers. How can I use that to create a second dataframe with those names but of the type `String` for each column? If I can do that, I can possibly copy the non strings values to the second dataframe with `string(v)`?

---

<div class="post-metadata">

**Author:** ![daschw](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/daschw/32/2926_2.png) [@daschw](https://discourse.julialang.org/u/daschw)\
**Post date:** [June 6, 2018, 1:24pm UTC](https://discourse.julialang.org/t/dataframe-and-missings-replace/9834/10 "2018-06-06T13:24:49Z")

</div>

I think it `v` and `name` should be replaced in the `for` statement:

```julia
for (name, v) in eachcol(df)
    df[name] = collect(Missings.replace(v, "\\N"))
end

```

---

<div class="post-metadata">

**Author:** ![merlin](https://avatars.discourse-cdn.com/v4/letter/m/8baadc/32.png) [@merlin](https://discourse.julialang.org/u/merlin)\
**Post date:** [November 12, 2020, 5:28am UTC](https://discourse.julialang.org/t/dataframe-and-missings-replace/9834/11 "2020-11-12T05:28:57Z")

</div>

I like this solution that I picked up from [far down the page in this stackoverflow:](https://stackoverflow.com/questions/34611109/julia-dataframe-replacing-missing-values)

```nohighlight
coalesce.(df, 0)

# or rather 
x = coalesce.(df, "\\N")

```

I’d prefer to use replace and the pairs definition of the transform like the following, but the SO example only works for a single column, the `coalesce` brodcast makes the replacement for every column every row that has `missing`.

```nohighlight
replace!(df.x, missing => 0)

```