# Replacing \*missing\* and \*NaN\* values in dataframe

**URL:** <https://discourse.julialang.org/t/replacing-missing-and-nan-values-in-dataframe/78687>\
**Category:** New to Julia\
**Tags:** question, dataframes, missing-values\
**Created:** [March 29, 2022, 4:13pm UTC](https://discourse.julialang.org/t/replacing-missing-and-nan-values-in-dataframe/78687 "2022-03-29T16:13:19Z")\
**Posts on this page:** 7\
**Page:** 1

<div class="post-metadata">

**Author:** ![janilin](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/janilin/32/33769_2.png) [@janilin](https://discourse.julialang.org/u/janilin)\
**Post date:** [March 29, 2022, 4:13pm UTC](https://discourse.julialang.org/t/replacing-missing-and-nan-values-in-dataframe/78687/1 "2022-03-29T16:13:19Z")

</div>

Hi! I was trying trying to change all _missing_ and _NaN_ values into 0.

Firstly, created a dataframe

```julia
using DataFrames
df_i = DataFrame( id =[101, 102, 103, 104, 105],
    name = ["A", "B", "C", NaN, "E"],
    age = [28, 32, missing, NaN, 31],
    salary = [3200, 3200, 4500, missing, missing]
)

```

Then tried a loop for replacing _missing_ and _NaN_ values, which was:

```julia
col = names(df_i);

for i in 1:length(col)
    replace!(df_i.col[i], missing => 0)
    replace!(df_i.col[i], NaN => 0)
end

```

Well, the arguement did not go well, hence error was inevitable.

In this context, I actually need 2 things to know:

**i) Is there any way to replace _missing_, _NaN_, and any specific value, whole across the dataframe? If so, how?**  
**ii) In my looping, what went wrong?**

---

<div class="post-metadata">

**Author:** ![nilshg](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/nilshg/32/2283_2.png) [@nilshg](https://discourse.julialang.org/u/nilshg)\
**Post date:** [March 29, 2022, 4:18pm UTC](https://discourse.julialang.org/t/replacing-missing-and-nan-values-in-dataframe/78687/2 "2022-03-29T16:18:28Z")

</div>

Your indexing does not make sense: what is `df_i.col` supposed to mean?

```julia
julia> df_i.col
ERROR: ArgumentError: column name :col not found in the data frame

```

To replace all missings use `coalesce`:

```julia
julia> coalesce.(df_i, 0.0)
5×4 DataFrame
 Row │ id name age salary
     │ Int64 Any Float64 Float64
─────┼───────────────────────────────
   1 │ 101 A 28.0 3200.0
   2 │ 102 B 32.0 3200.0
   3 │ 103 C 0.0 4500.0
   4 │ 104 NaN NaN 0.0
   5 │ 105 E 31.0 0.0

```

if you want to loop over columns, just do so directly:

```julia
julia> for c ∈ eachcol(df_i)
           replace!(c, NaN => 0.0)
       end

```

I’d also recommend going through [https://github.com/bkamins/Julia-DataFrames-Tutorial](https://github.com/bkamins/Julia-DataFrames-Tutorial) to get the hang of DataFrames

---

<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:** [March 29, 2022, 4:22pm UTC](https://discourse.julialang.org/t/replacing-missing-and-nan-values-in-dataframe/78687/3 "2022-03-29T16:22:00Z")

</div>

If you want to replace both in one shot you can do:

```julia
julia> fun(x) = ismissing(x) || (x isa Number && isnan(x)) ? 0 : x
fun (generic function with 1 method)

julia> fun.(df_i)
5×4 DataFrame
 Row │ id name age salary
     │ Int64 Any Float64 Int64
─────┼──────────────────────────────
   1 │ 101 A 28.0 3200
   2 │ 102 B 32.0 3200
   3 │ 103 C 0.0 4500
   4 │ 104 0 0.0 0
   5 │ 105 E 31.0 0

```

---

<div class="post-metadata">

**Author:** ![julien\_goo](https://avatars.discourse-cdn.com/v4/letter/j/ce73a5/32.png) [@julien\_goo](https://discourse.julialang.org/u/julien_goo)\
**Post date:** [March 29, 2022, 4:31pm UTC](https://discourse.julialang.org/t/replacing-missing-and-nan-values-in-dataframe/78687/4 "2022-03-29T16:31:29Z")

</div>

I bumped into the same issue yesterday in a context of:

- DataFrames
- with columns of multiples types (dates, numeric, string, categoricals)
- with ‘missing’ in multiple columns
- NaN in Float columns (in addtion to missing)

I really struggled to find a robust method with the different types and mixes of NaN and missings.  
Would it be possible to have a function like `complete_cases` or add an option to `complete_cases` to treat it?  
It would be super useful for preprocessing, prior ML operations.

---

<div class="post-metadata">

**Author:** ![janilin](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/janilin/32/33769_2.png) [@janilin](https://discourse.julialang.org/u/janilin)\
**Post date:** [March 29, 2022, 4:42pm UTC](https://discourse.julialang.org/t/replacing-missing-and-nan-values-in-dataframe/78687/5 "2022-03-29T16:42:48Z")

</div>

> [@nilshg](#):
>
> df\_i.col

In context of `df_i.col`, since I created ,

```julia
col = names(df_i)

```

I thought it might work (kind of a litte experiment 😅), as col[i] gives the column names, however, it evidently doesn’t.

---

<div class="post-metadata">

**Author:** ![Henrique\_Becker](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/henrique_becker/32/15443_2.png) [@Henrique\_Becker](https://discourse.julialang.org/u/Henrique_Becker)\
**Post date:** [March 29, 2022, 5:01pm UTC](https://discourse.julialang.org/t/replacing-missing-and-nan-values-in-dataframe/78687/6 "2022-03-29T17:01:27Z")

</div>

Ah, you wanted the compiler to replace `col[i]` with a name before resolving the fact it is connected to the `df_i` by a dot. No, you cannot do this with this syntax, to programmatically access a field with its name value (instead the name as a literal spelled out in the source code) you need to use `getproperty` as in `getproperty(df_i, col[i])`. Basically, in Julia, any code `obj.field_name` is transformed before compilation into `getproperty(obj, :field_name)` (note the field name is a `Symbol`).

---

<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:** [March 29, 2022, 5:09pm UTC](https://discourse.julialang.org/t/replacing-missing-and-nan-values-in-dataframe/78687/7 "2022-03-29T17:09:07Z")

</div>

> [@julien\_goo](#):
>
> Would it be possible to have a function like `complete_cases` or add an option to `complete_cases` to treat it?

This seems more of a job of a specialized package for data cleaning as there are many possible ways how you might want to fill `missing`/`NaN`. However, I propose you open an issue in DataFrames.jl with your requirements and then we can think what makes sense to live in DataFrames.jl and what should go to a separate package.

In general to replace `missings` use `coalesce` as @nilshg proposed. To replace `NaN`s you need to check if the column is numeric and if the value is `NaN`. OP wanted both so I combined them in one function but I guess this is a rare use case.