# Replace missing values based on column data type

**URL:** <https://discourse.julialang.org/t/replace-missing-values-based-on-column-data-type/94186>\
**Category:** General Usage\
**Tags:** package, plotting, strings, dataframes, missing-values\
**Created:** [February 7, 2023, 4:29am UTC](https://discourse.julialang.org/t/replace-missing-values-based-on-column-data-type/94186 "2023-02-07T04:29:44Z")\
**Posts on this page:** 8\
**Page:** 1

<div class="post-metadata">

**Author:** ![Sandy45](https://avatars.discourse-cdn.com/v4/letter/s/3ec8ea/32.png) [@Sandy45](https://discourse.julialang.org/u/Sandy45)\
**Post date:** [February 7, 2023, 4:29am UTC](https://discourse.julialang.org/t/replace-missing-values-based-on-column-data-type/94186/1 "2023-02-07T04:29:44Z")

</div>

Hello,

Question 1:  
I have df with multiple columns say a,b,c,d… so on and i would like to replace all missing values dynamically in each columns with 0(if column data type is int) and with “xyz” if column data type is string.

Question 2:

I have column a in dataframe df and it could consists of numerical values where they should have been strings and i tried to convert them using string.(df[!, :a]) and it worked but issue that i have identified was if column has missing values then they have been converted to string “missing” then I used passmissing to ignore missing values and it worked just fine. I would like to check if there is any efficient way to solve this issue.

---

<div class="post-metadata">

**Author:** ![rmsmsgood](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/rmsmsgood/32/20544_2.png) [@rmsmsgood](https://discourse.julialang.org/u/rmsmsgood)\
**Post date:** [February 7, 2023, 6:02am UTC](https://discourse.julialang.org/t/replace-missing-values-based-on-column-data-type/94186/2 "2023-02-07T06:02:53Z")

</div>

For question 1, you may want below result:

```julia
julia> df = DataFrame(a = [-1, missing,missing,3], b = [4,5,missing, 7.2], c = ["k", missing, "m", "asd"])   
4×3 DataFrame
 Row │ a b c       
     │ Int64? Float64? String? 
─────┼─────────────────────────────
   1 │ -1 4.0 k
   2 │ missing 5.0 missing 
   3 │ missing missing m
   4 │ 3 7.2 asd

julia> for col ∈ eachcol(df)
           if String <: eltype(col)
               col[ismissing.(col)] .= "xyz"
           else
               col[ismissing.(col)] .= 0
           end
       end

julia> df
4×3 DataFrame
 Row │ a b c       
     │ Int64? Float64? String?
─────┼───────────────────────────
   1 │ -1 4.0 k
   2 │ 0 5.0 xyz
   3 │ 0 0.0 m
   4 │ 3 7.2 asd

```

Code:

```julia
using DataFrames

df = DataFrame(a = [-1, missing,missing,3], b = [4,5,missing, 7.2], c = ["k", missing, "m", "asd"])

for col ∈ eachcol(df)
    if String <: eltype(col)
        col[ismissing.(col)] .= "xyz"
    else
        col[ismissing.(col)] .= 0
    end
end
df

```

* * *

For question 2, well, I don’t know it’s efficient or short, but `ifelse` function solves almost cases.

```julia
julia> df = DataFrame(a = [-1, missing,missing,3], b = [4,5,missing, 7.2], c = ["k", missing, "m", "asd"])   
4×3 DataFrame
 Row │ a b c       
     │ Int64? Float64? String?
─────┼─────────────────────────────
   1 │ -1 4.0 k
   2 │ missing 5.0 missing
   3 │ missing missing m
   4 │ 3 7.2 asd

julia> df[!, :a] = ifelse.(ismissing.(df[!, :a]), df[!, :a], string.(df[!, :a]))
4-element Vector{Union{Missing, String}}:
 "-1"
 missing
 missing
 "3"

julia> df
4×3 DataFrame
 Row │ a b c       
     │ String? Float64? String?
─────┼─────────────────────────────
   1 │ -1 4.0 k
   2 │ missing 5.0 missing
   3 │ missing missing m
   4 │ 3

```

---

<div class="post-metadata">

**Author:** ![bkamins](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/bkamins/32/208538_2.png) [@bkamins](https://discourse.julialang.org/u/bkamins)\
**Post date:** [February 7, 2023, 8:53am UTC](https://discourse.julialang.org/t/replace-missing-values-based-on-column-data-type/94186/3 "2023-02-07T08:53:47Z")

</div>

> [@Sandy45](#):
>
> I used passmissing to ignore missing values and it worked just fine.

This is an efficient solution. Did you have any issue with it?

---

<div class="post-metadata">

**Author:** ![Sandy45](https://avatars.discourse-cdn.com/v4/letter/s/3ec8ea/32.png) [@Sandy45](https://discourse.julialang.org/u/Sandy45)\
**Post date:** [February 7, 2023, 2:36pm UTC](https://discourse.julialang.org/t/replace-missing-values-based-on-column-data-type/94186/4 "2023-02-07T14:36:04Z")

</div>

@rmsmsgood Thank You, this solved my issue.

---

<div class="post-metadata">

**Author:** ![Sandy45](https://avatars.discourse-cdn.com/v4/letter/s/3ec8ea/32.png) [@Sandy45](https://discourse.julialang.org/u/Sandy45)\
**Post date:** [February 7, 2023, 2:38pm UTC](https://discourse.julialang.org/t/replace-missing-values-based-on-column-data-type/94186/5 "2023-02-07T14:38:28Z")

</div>

@bkamins No, I just wanted to check if I am using right approach. Thanks for your response!

---

<div class="post-metadata">

**Author:** ![Sandy45](https://avatars.discourse-cdn.com/v4/letter/s/3ec8ea/32.png) [@Sandy45](https://discourse.julialang.org/u/Sandy45)\
**Post date:** [February 7, 2023, 2:41pm UTC](https://discourse.julialang.org/t/replace-missing-values-based-on-column-data-type/94186/6 "2023-02-07T14:41:24Z")

</div>

@rmsmsgood Thanks for your response, here is what I am used just in case if anyone needs in future.

df.a = passmissing(x-\>string.(x)).(df.a)

---

<div class="post-metadata">

**Author:** ![bkamins](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/bkamins/32/208538_2.png) [@bkamins](https://discourse.julialang.org/u/bkamins)\
**Post date:** [February 7, 2023, 5:19pm UTC](https://discourse.julialang.org/t/replace-missing-values-based-on-column-data-type/94186/7 "2023-02-07T17:19:45Z")

</div>

The following is cleaner IMO and enough:

```julia
julia> df = DataFrame(a=[1, missing, 2])
3×1 DataFrame
 Row │ a
     │ Int64?
─────┼─────────
   1 │ 1
   2 │ missing
   3 │ 2

julia> passmissing(string).(df.a)
3-element Vector{Union{Missing, String}}:
 "1"
 missing
 "2"

```

You do not need to broadcast `string` inside `passmissing`, as `passmissing` anyway gets a scalar.

---

<div class="post-metadata">

**Author:** ![AugustoCL](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/augustocl/32/32022_2.png) [@AugustoCL](https://discourse.julialang.org/u/AugustoCL)\
**Post date:** [February 10, 2023, 2:23am UTC](https://discourse.julialang.org/t/replace-missing-values-based-on-column-data-type/94186/8 "2023-02-10T02:23:18Z")

</div>

An altenative solution to question 01:

```julia
julia> df = DataFrame(a=[-1,missing,missing,3], b=[4,5,missing,7.2], c=["k", missing,"m","asd"])
4×3 DataFrame
 Row │ a b c
     │ Int64? Float64? String?
─────┼─────────────────────────────
   1 │ -1 4.0 k
   2 │ missing 5.0 missing
   3 │ missing missing m
   4 │ 3 7.2 asd

# get bool vector of numeric variables
julia> idxs = map(x -> nonmissingtype(x) <: Real, eltype.(eachcol(df)))
3-element Vector{Bool}:
 1
 1
 0

# apply transformation as you requested
julia> transform!(df,
           [col => ByRow(x->coalesce(x, 0)) for col in propertynames(df)[idxs]],
           [col => ByRow(x->coalesce(x, "xyz")) for col in propertynames(df)[.!idxs]],
           renamecols=false)
4×3 DataFrame
 Row │ a b c      
     │ Int64 Real String 
─────┼─────────────────────
   1 │ -1 4.0 k
   2 │ 0 5.0 xyz
   3 │ 0 0 m
   4 │ 3 7.2 asd

```

You could also change the filter in `map` to adjust your needs.  
For example, you could add in anonymous function the CategoricalValue type from CategoricalArrays.jl

```julia
map(
  x -> (nonmissingtype(x) <: AbstractString) || (nonmissingtype(x) <: CategoricalValue), 
  eltype.(eachcol(df)
)

```