# How to convert all nothings in dataframe to missing?

**URL:** <https://discourse.julialang.org/t/how-to-convert-all-nothings-in-dataframe-to-missing/54004>\
**Category:** General Usage\
**Tags:** question\
**Created:** [January 26, 2021, 5:42pm UTC](https://discourse.julialang.org/t/how-to-convert-all-nothings-in-dataframe-to-missing/54004 "2021-01-26T17:42:02Z")\
**Posts on this page:** 15\
**Page:** 1

<div class="post-metadata">

**Author:** ![Joe\_Schnetzler](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/joe_schnetzler/32/20423_2.png) [@Joe\_Schnetzler](https://discourse.julialang.org/u/Joe_Schnetzler)\
**Post date:** [January 26, 2021, 5:42pm UTC](https://discourse.julialang.org/t/how-to-convert-all-nothings-in-dataframe-to-missing/54004/1 "2021-01-26T17:42:03Z")

</div>

I have a dataframe with a bunch of nothings mixed between union columns, etc. How can I change all nothings to missings efficiently?

Thanks!

Best,  
Joe

---

<div class="post-metadata">

**Author:** ![lmiq](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/lmiq/32/18314_2.png) [@lmiq](https://discourse.julialang.org/u/lmiq)\
**Post date:** [January 26, 2021, 6:22pm UTC](https://discourse.julialang.org/t/how-to-convert-all-nothings-in-dataframe-to-missing/54004/2 "2021-01-26T18:22:28Z")

</div>

From the point of view of the function itself, I do not know if you can get faster than the simple loop:

```julia
julia> function nothing_to_missing!(x)
         for i in eachindex(x)
           if isnothing(x[i])
             x[i] = missing
           end
         end
       end

```

However, the performance is very much dependent on the type of vector:

```julia
# Vector Any[]
julia> @btime nothing_to_missing!(x) setup=(x=Any[isodd(i) ? nothing : 1 for i in 1:1000]) evals=1
  4.905 μs (0 allocations: 0 bytes)

# Vector Union{Int,Nothing,Missing}
julia> @btime nothing_to_missing!(x) setup=(x=Union{Int,Nothing,Missing}[isodd(i) ? nothing : 1 for i in 1:1000]) evals=1
  1.343 μs (0 allocations: 0 bytes)

```

This for arrays. I do not know if dataframes have some specific behavior concerning these values.

Edit: With a `DataFrame` it seems to be much slower. But I am not completely sure if this benchmark makes sense, I am not a regular user of data frames:

```julia
julia> function nothing_to_missing!(df,col)
         for i in eachindex(df[col])
           if isnothing(df[col][i])
             df[col][i] = missing
           end
         end
       end

julia> @btime nothing_to_missing!(df,1) setup=(df=DataFrame([Any[isodd(i) ? nothing : 1 for i in 1:1000]],[:x])) evals=1
  145.723 μs (1978 allocations: 46.52 KiB)

julia> @btime nothing_to_missing!(df,1) setup=(df=DataFrame([Union{Int,Missing,Nothing}[isodd(i) ? nothing : 1 for i in 1:1000]],[:x])) evals=1
  127.832 μs (1978 allocations: 46.52 KiB)

```

---

<div class="post-metadata">

**Author:** ![Joe\_Schnetzler](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/joe_schnetzler/32/20423_2.png) [@Joe\_Schnetzler](https://discourse.julialang.org/u/Joe_Schnetzler)\
**Post date:** [January 26, 2021, 6:53pm UTC](https://discourse.julialang.org/t/how-to-convert-all-nothings-in-dataframe-to-missing/54004/4 "2021-01-26T18:53:47Z")

</div>

> [@Joe\_Schnetzler](#):
>
> Thank you. The loop will do then! Thanks again!

Thank you. The loop will do then! Thanks again!

---

<div class="post-metadata">

**Author:** ![MatFi](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/matfi/32/10002_2.png) [@MatFi](https://discourse.julialang.org/u/MatFi)\
**Post date:** [January 26, 2021, 6:59pm UTC](https://discourse.julialang.org/t/how-to-convert-all-nothings-in-dataframe-to-missing/54004/5 "2021-01-26T18:59:50Z")

</div>

Not sure if this is faster:

```julia
df = DataFrame(A=["A","B",nothing, "C"])
df.A = (df.A .|> a -> isnothing(a) ? missing : a)

```

---

<div class="post-metadata">

**Author:** ![lmiq](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/lmiq/32/18314_2.png) [@lmiq](https://discourse.julialang.org/u/lmiq)\
**Post date:** [January 26, 2021, 7:06pm UTC](https://discourse.julialang.org/t/how-to-convert-all-nothings-in-dataframe-to-missing/54004/6 "2021-01-26T19:06:43Z")

</div>

Yes, for data frames it is faster than the above loop, and much dependent on the type of array as well:

```julia
julia> f(df) = (df.x .|> a -> isnothing(a) ? missing : a )
f (generic function with 1 method)

julia> @btime f(df) setup=(df=DataFrame([Any[isodd(i) ? nothing : 1 for i in 1:1000]],[:x])) evals=1;
  53.321 μs (501 allocations: 16.95 KiB)

julia> @btime f(df) setup=(df=DataFrame([Union{Int,Missing,Nothing}[isodd(i) ? nothing : 1 for i in 1:1000]],[:x])) evals=1;
  2.655 μs (11 allocations: 9.29 KiB)

```

(but it does not mutate the original data frame, it creates a new one)

---

<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:** [January 26, 2021, 7:12pm UTC](https://discourse.julialang.org/t/how-to-convert-all-nothings-in-dataframe-to-missing/54004/7 "2021-01-26T19:12:17Z")

</div>

`replace` is the most obvious answer here

```julia
df = DataFrame(a = [1, 2, nothing, 4])
df.a = replace(df.a, nothing => missing)

```

---

<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:** [January 26, 2021, 7:12pm UTC](https://discourse.julialang.org/t/how-to-convert-all-nothings-in-dataframe-to-missing/54004/9 "2021-01-26T19:12:59Z")

</div>

I recommend creating a new `Vector` and assigning it to the column, as @MatFi did. Otherwise you need that the DataFrame column is of type `Any[]` or any other type that supports both `missing` and `nothing` what is not common nor recommended. I personally use:

```julia
df.A = ifelse.(isnothing.(df.A), missing, df.A)

```

---

<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:** [January 26, 2021, 7:32pm UTC](https://discourse.julialang.org/t/how-to-convert-all-nothings-in-dataframe-to-missing/54004/10 "2021-01-26T19:32:40Z")

</div>

This is actually less performant, since you create `isnothing.(df.A)` as a temporary vector.

---

<div class="post-metadata">

**Author:** ![lmiq](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/lmiq/32/18314_2.png) [@lmiq](https://discourse.julialang.org/u/lmiq)\
**Post date:** [January 26, 2021, 7:37pm UTC](https://discourse.julialang.org/t/how-to-convert-all-nothings-in-dataframe-to-missing/54004/11 "2021-01-26T19:37:18Z")

</div>

> [@pdeffebach](#):
>
> `df.a = replace(df.a, nothing => missing)`

This also creates a new vector and puts it in the place of `df.a`. What if one wants to mutate the values? Is there any alternative that will perform well?

---

<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:** [January 26, 2021, 7:41pm UTC](https://discourse.julialang.org/t/how-to-convert-all-nothings-in-dataframe-to-missing/54004/12 "2021-01-26T19:41:20Z")

</div>

No, this approach does the minimum amount of copying possibe, since there is no copying in the assignment to `df.a`.

In the `ifelse.(isnothing.(df.A)...)` case, the `isnothing.(df.A)` creates an intermediate temporary vector that is never used.

There are no options to do this entirely in-place, since a vector, say `[1, 2, nothing, 4]` only has the memory footprint for `Int64` and `nothing` laid out for it. You need a new vector slotted to hold `Int64` and `missing`s.

---

<div class="post-metadata">

**Author:** ![lmiq](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/lmiq/32/18314_2.png) [@lmiq](https://discourse.julialang.org/u/lmiq)\
**Post date:** [January 26, 2021, 7:44pm UTC](https://discourse.julialang.org/t/how-to-convert-all-nothings-in-dataframe-to-missing/54004/13 "2021-01-26T19:44:09Z")

</div>

Complementing the answer to my own question, there is `replace!`, but one need to explicitly define the type of the array to accept both `missing` and `nothing` values, as pointed above. And the difference in performance is huge from `Any` to `Union{Missing,Nothing,...}`:

```julia
julia> @btime replace!(df.x,nothing=>missing) setup=(df=DataFrame([Any[isodd(i) ? nothing : 1 for i in 1:10000]],[:x])) evals=1;
  112.331 μs (0 allocations: 0 bytes)

julia> @btime replace!(df.x,nothing=>missing) setup=(df=DataFrame([Union{Missing,Nothing,Int}[isodd(i) ? nothing : 1 for i in 1:10000]],[:x])) evals=1;
  5.607 μs (0 allocations: 0 bytes)

```

---

<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:** [January 26, 2021, 8:02pm UTC](https://discourse.julialang.org/t/how-to-convert-all-nothings-in-dataframe-to-missing/54004/14 "2021-01-26T20:02:51Z")

</div>

> [@pdeffebach](#):
>
> This is actually less performant, since you create `isnothing.(df.A)` as a temporary vector.

Do I? Is this not a case of [broadcast fusion](https://julialang.org/blog/2017/01/moredots/)?

---

<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:** [January 26, 2021, 8:08pm UTC](https://discourse.julialang.org/t/how-to-convert-all-nothings-in-dataframe-to-missing/54004/15 "2021-01-26T20:08:14Z")

</div>

I think that if there is less than 4 type in the `Union` and all of them are concrete, the code will be reasonably fast. Nevertheless, it is not usual to declare such `Union{Missing,Nothing,...}` columns. A CSV reader will probably read as either `Union{Missing,...}` or `Union{Nothing,...}`, and if the user intends to change the convention/standard the original array will not have the element type necessary without allocating a new array anyway.

---

<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:** [January 26, 2021, 8:29pm UTC](https://discourse.julialang.org/t/how-to-convert-all-nothings-in-dataframe-to-missing/54004/16 "2021-01-26T20:29:00Z")

</div>

Not quite, I guess

```julia
julia> x = [rand() < .2 ? rand() : nothing for i in 1:1_000_000];

julia> function f1(x)
       replace(x, nothing => missing)
       end;

julia> function f2(x)
       ifelse.(isnothing.(x), missing, x)
       end;

julia> @btime f1($x);
  3.296 ms (2 allocations: 8.58 MiB)

julia> @btime f2($x);
  24.277 ms (13 allocations: 8.58 MiB)

```

---

<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:** [January 26, 2021, 8:43pm UTC](https://discourse.julialang.org/t/how-to-convert-all-nothings-in-dataframe-to-missing/54004/17 "2021-01-26T20:43:31Z")

</div>

This is strange, because it is clear that there is no allocation of an intermediary `Vector` otherwise my method would use the double of the memory (or at least something significantly larger), but any difference in the memory used is less than 1%.

The number of allocations however is higher, maybe the broadcast machinery use `@views` or something that allocates without duplicating the whole intermediary array, and for such simple task these extra allocations make the difference.