# Dealing with NaN's

**URL:** <https://discourse.julialang.org/t/dealing-with-nans/60086>\
**Category:** General Usage\
**Tags:** dataframes\
**Created:** [April 27, 2021, 7:50am UTC](https://discourse.julialang.org/t/dealing-with-nans/60086 "2021-04-27T07:50:31Z")\
**Posts on this page:** 20\
**Page:** 1

<div class="post-metadata">

**Author:** ![statspy](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/statspy/32/26630_2.png) [@statspy](https://discourse.julialang.org/u/statspy)\
**Post date:** [April 27, 2021, 7:50am UTC](https://discourse.julialang.org/t/dealing-with-nans/60086/1 "2021-04-27T07:50:31Z")

</div>

Hello,

I am trying to filter a dataframe to exclude NaN’a values but not working so far. How can i deal with NaN’s?

I am trying:

`filter(!isnan(df.salary))`

What are my options to deal with NaN’s?

Thanks

---

<div class="post-metadata">

**Author:** ![jzr](https://avatars.discourse-cdn.com/v4/letter/j/eb9ed0/32.png) [@jzr](https://discourse.julialang.org/u/jzr)\
**Post date:** [April 27, 2021, 8:41am UTC](https://discourse.julialang.org/t/dealing-with-nans/60086/2 "2021-04-27T08:41:41Z")

</div>

Perhaps you mean to have [`missing`](https://docs.julialang.org/en/v1/manual/missing/) instead of NaNs. You can `dropmissing` in that case.

---

<div class="post-metadata">

**Author:** ![statspy](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/statspy/32/26630_2.png) [@statspy](https://discourse.julialang.org/u/statspy)\
**Post date:** [April 27, 2021, 8:44am UTC](https://discourse.julialang.org/t/dealing-with-nans/60086/3 "2021-04-27T08:44:25Z")

</div>

Nope. They were NaN. I tried skipmissing and the NaN values was still there. I replaced NaN’s with zeros then i filtered values \> 0 but there should be a smarter way to do this. thx

---

<div class="post-metadata">

**Author:** ![jzr](https://avatars.discourse-cdn.com/v4/letter/j/eb9ed0/32.png) [@jzr](https://discourse.julialang.org/u/jzr)\
**Post date:** [April 27, 2021, 8:45am UTC](https://discourse.julialang.org/t/dealing-with-nans/60086/4 "2021-04-27T08:45:30Z")

</div>

Are you reading from a csv or what?

---

<div class="post-metadata">

**Author:** ![statspy](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/statspy/32/26630_2.png) [@statspy](https://discourse.julialang.org/u/statspy)\
**Post date:** [April 27, 2021, 8:46am UTC](https://discourse.julialang.org/t/dealing-with-nans/60086/5 "2021-04-27T08:46:48Z")

</div>

yes, i am reading from a csv.

---

<div class="post-metadata">

**Author:** ![jzr](https://avatars.discourse-cdn.com/v4/letter/j/eb9ed0/32.png) [@jzr](https://discourse.julialang.org/u/jzr)\
**Post date:** [April 27, 2021, 8:49am UTC](https://discourse.julialang.org/t/dealing-with-nans/60086/6 "2021-04-27T08:49:27Z")

</div>

1. In the actual csv file, what do the nans look like?
2. What do the nans represent? Are they unknown values? If so, use [`missingstring=`](https://csv.juliadata.org/stable/#Missing-Strings). Or do they have another meaning in your dataset?

---

<div class="post-metadata">

**Author:** ![statspy](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/statspy/32/26630_2.png) [@statspy](https://discourse.julialang.org/u/statspy)\
**Post date:** [April 27, 2021, 9:00am UTC](https://discourse.julialang.org/t/dealing-with-nans/60086/7 "2021-04-27T09:00:14Z")

</div>

I am not sure if you know the enron.csv dataset, its a dataset from kaggle. In this dataset theres a column named salary. When I read it, some of the values appears as NaN’s. Reading the csv in LIbreOffice they appear as NaN as well. So my approach is just to delete them.

In this case, its just 164 values, but my concern about my approach (replacing NaN’s with Zeros then filter) is that this would run slow with larger datasets.

---

<div class="post-metadata">

**Author:** ![jzr](https://avatars.discourse-cdn.com/v4/letter/j/eb9ed0/32.png) [@jzr](https://discourse.julialang.org/u/jzr)\
**Post date:** [April 27, 2021, 9:43am UTC](https://discourse.julialang.org/t/dealing-with-nans/60086/8 "2021-04-27T09:43:34Z")

</div>

Figure out how the nans are represented in the text of the file, and then use `missingstring=` to turn those values into `missing`s, and then use `dropmissing` to drop those rows.

---

<div class="post-metadata">

**Author:** ![ffevotte](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/ffevotte/32/6587_2.png) [@ffevotte](https://discourse.julialang.org/u/ffevotte)\
**Post date:** [April 27, 2021, 10:04am UTC](https://discourse.julialang.org/t/dealing-with-nans/60086/9 "2021-04-27T10:04:02Z")

</div>

Using [`filter`](https://dataframes.juliadata.org/stable/lib/functions/#Base.filter), you could write something like this:

```julia
julia> df = DataFrame(x=rand(10), y=rand((1, 2, NaN), 10).*rand(10))
10×2 DataFrame
 Row │ x y          
     │ Float64 Float64    
─────┼──────────────────────
   1 │ 0.214967 0.955162
   2 │ 0.717033 NaN
   3 │ 0.313037 NaN
   4 │ 0.925829 0.876826
   5 │ 0.77564 0.466991
   6 │ 0.524314 0.50265
   7 │ 0.564011 NaN
   8 │ 0.495634 0.611079
   9 │ 0.366369 0.761782
  10 │ 0.949455 1.41635

```

```julia
julia> filter(:y => !isnan, df)
7×2 DataFrame
 Row │ x y        
     │ Float64 Float64  
─────┼────────────────────
   1 │ 0.214967 0.955162
   2 │ 0.925829 0.876826
   3 │ 0.77564 0.466991
   4 │ 0.524314 0.50265
   5 │ 0.495634 0.611079
   6 │ 0.366369 0.761782
   7 │ 0.949455 1.41635

```

This creates a new DataFrame. If instead you want to delete the rows in the original DataFrame, you can use [`delete!`](https://dataframes.juliadata.org/stable/lib/functions/#Base.delete!), for example like so:

```julia
julia> delete!(df, isnan.(df.y))
7×2 DataFrame
 Row │ x y        
     │ Float64 Float64  
─────┼────────────────────
   1 │ 0.214967 0.955162
   2 │ 0.925829 0.876826
   3 │ 0.77564 0.466991
   4 │ 0.524314 0.50265
   5 │ 0.495634 0.611079
   6 │ 0.366369 0.761782
   7 │ 0.949455 1.41635

```

---

<div class="post-metadata">

**Author:** ![statspy](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/statspy/32/26630_2.png) [@statspy](https://discourse.julialang.org/u/statspy)\
**Post date:** [April 27, 2021, 10:11am UTC](https://discourse.julialang.org/t/dealing-with-nans/60086/10 "2021-04-27T10:11:37Z")

</div>

Thanks @jzr and @ffevotte for the help. xD

---

<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:** [April 27, 2021, 11:34am UTC](https://discourse.julialang.org/t/dealing-with-nans/60086/11 "2021-04-27T11:34:50Z")

</div>

To remove all rows with NaNs in any column, what are simpler methods than using bitvectors as follows:

```julia
# remove all rows with NaNs in any column:
df = DataFrame(x=rand((-1,1,NaN),10), y=rand((-2,2,NaN),10), z=rand((-3,3,NaN),10))

ix = [(!isnan).(c) for c in eachcol(df)]
ix0 = ix[1]
for i in 2:length(ix)
   ix0 = ix0 .& ix[i]
end
df[ix0,:]

```

---

<div class="post-metadata">

**Author:** ![statspy](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/statspy/32/26630_2.png) [@statspy](https://discourse.julialang.org/u/statspy)\
**Post date:** [April 27, 2021, 1:34pm UTC](https://discourse.julialang.org/t/dealing-with-nans/60086/12 "2021-04-27T13:34:21Z")

</div>

> [@rafael.guerra](#):
>
> ```julia
> ix = [(!isnan).(c) for c in eachcol(df)]
> ix0 = ix[1]
> for i in 2:length(ix)
> ix0 = ix0 .& ix[i]
> end
> df[ix0,:]
> 
> ```

I didn’t manage to make this work @rafael.guerra dunno why tho.

---

<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:** [April 27, 2021, 1:40pm UTC](https://discourse.julialang.org/t/dealing-with-nans/60086/13 "2021-04-27T13:40:07Z")

</div>

Uhm… Here it works fine in Julia 1.6.1 on Win10 for both DataFrames v0.21.8 and v1.0.0.  
Results below:

```julia
julia> df
10×3 DataFrame
│ Row │ x │ y │ z │
│ │ Real │ Real │ Real │
├─────┼──────┼──────┼──────┤
│ 1 │ -1 │ 2 │ -3 │
│ 2 │ -1 │ NaN │ -3 │
│ 3 │ -1 │ NaN │ 3 │
│ 4 │ NaN │ 2 │ NaN │
│ 5 │ -1 │ -2 │ 3 │
│ 6 │ -1 │ NaN │ NaN │
│ 7 │ NaN │ NaN │ 3 │
│ 8 │ NaN │ NaN │ 3 │
│ 9 │ 1 │ 2 │ -3 │
│ 10 │ -1 │ 2 │ -3 │

julia> df[ix0,:]
4×3 DataFrame
│ Row │ x │ y │ z │
│ │ Real │ Real │ Real │
├─────┼──────┼──────┼──────┤
│ 1 │ -1 │ 2 │ -3 │
│ 2 │ -1 │ -2 │ 3 │
│ 3 │ 1 │ 2 │ -3 │
│ 4 │ -1 │ 2 │ -3 │

```

---

<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:** [April 27, 2021, 1:41pm UTC](https://discourse.julialang.org/t/dealing-with-nans/60086/14 "2021-04-27T13:41:19Z")

</div>

OP, could you explain a bit more why you are using `NaN`s here? there are lots of convenience functions of this sort for `missing` values. So you may have an easier time if you work with `missing`s.

---

<div class="post-metadata">

**Author:** ![sijo](https://avatars.discourse-cdn.com/v4/letter/s/da6949/32.png) [@sijo](https://discourse.julialang.org/u/sijo)\
**Post date:** [April 27, 2021, 1:58pm UTC](https://discourse.julialang.org/t/dealing-with-nans/60086/15 "2021-04-27T13:58:13Z")

</div>

> [@rafael.guerra](#):
>
> To remove all rows with NaNs in any column, what are simpler methods than using bitvectors as follows:

Here’s one way using `filter`:

```julia
# Using a lambda that receives a named tuple for each row
filter(AsTable(:) => row->!any(isnan(x) for x in row), df)

```

You could also write `filter(row->!any(isnan(x) for x in row), df)` but that’s slower.

Or you could write `filter((:) => (values...)->!any(isnan.(values)), df)` to use a lambda that receives the row as one parameter per column, but that’s even slower apparently.

---

<div class="post-metadata">

**Author:** ![statspy](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/statspy/32/26630_2.png) [@statspy](https://discourse.julialang.org/u/statspy)\
**Post date:** [April 27, 2021, 2:13pm UTC](https://discourse.julialang.org/t/dealing-with-nans/60086/16 "2021-04-27T14:13:08Z")

</div>

When I read the CSV the NaN’s are there. Julia gives me a df with lots of NaN’s (not missing) in numeric columns. I cant just delete these rows because i need most of them.

---

<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:** [April 27, 2021, 2:14pm UTC](https://discourse.julialang.org/t/dealing-with-nans/60086/17 "2021-04-27T14:14:34Z")

</div>

You can do `missingstring="NaN"` when you import the CSV.

It’s worth thinking conceptually about whether your data is `missing` or `NaN`. They have different meanings. `missing` means “I don’t know what this value is”. But `NaN` means “This is not a number, from an operation like `1/0` or similar.”

---

<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:** [April 27, 2021, 2:42pm UTC](https://discourse.julialang.org/t/dealing-with-nans/60086/18 "2021-04-27T14:42:12Z")

</div>

@sijo, your second option (`filter(row->!any(isnan(x) for x in row), df)`) seems to be the fastest for the example provided?

---

<div class="post-metadata">

**Author:** ![sijo](https://avatars.discourse-cdn.com/v4/letter/s/da6949/32.png) [@sijo](https://discourse.julialang.org/u/sijo)\
**Post date:** [April 27, 2021, 3:53pm UTC](https://discourse.julialang.org/t/dealing-with-nans/60086/19 "2021-04-27T15:53:18Z")

</div>

With DataFrames 1.0 and a 1 million row data frame I get the following timings:

```julia
julia> using DataFrames

julia> using BenchmarkTools

julia> df = DataFrame(x=rand((-1,1,NaN),1000000), y=rand((-2,2,NaN),1000000), z=rand((-3,3,NaN),1000000));

julia> @btime filter(AsTable(:) => row->!any(isnan(x) for x in row), $df);
  77.555 ms (61 allocations: 32.03 MiB)

julia> @btime filter(row->!any(isnan(x) for x in row), $df);
  405.556 ms (7033060 allocations: 148.65 MiB)

julia> @btime filter((:) => (values...)->!any(isnan.(values)), $df);
  1.159 s (3704585 allocations: 111.44 MiB)

```

---

<div class="post-metadata">

**Author:** ![tbeason](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/tbeason/32/15898_2.png) [@tbeason](https://discourse.julialang.org/u/tbeason)\
**Post date:** [April 27, 2021, 4:12pm UTC](https://discourse.julialang.org/t/dealing-with-nans/60086/20 "2021-04-27T16:12:29Z")

</div>

`dropmissing` is also fairly quick and certainly the easiest. You have to first convert the `NaN` to `missing` (although this can be done when you read the csv as mentioned previously).

```julia
allowmissing!(df)
foreach(c->replace!(c,NaN=>missing),eachcol(df))
dropmissing(df)

```

benchmark results for posterity:

```julia
julia> function dropnan(df)
       dff = copy(df)
       allowmissing!(dff)
       foreach(c->replace!(c,NaN=>missing),eachcol(dff))
       dropmissing!(dff)
       return dff
       end
dropnan (generic function with 1 method)

julia> @btime dropnan($df)
  271.735 ms (6771432 allocations: 161.38 MiB)

julia> @btime filter(AsTable(:) => row->!any(isnan(x) for x in row), $df);
  64.271 ms (61 allocations: 32.05 MiB)

julia> @btime filter(row->!any(isnan(x) for x in row), $df);
  333.526 ms (7034381 allocations: 148.70 MiB)

julia> @btime filter((:) => (values...)->!any(isnan.(values)), $df);
  868.403 ms (3703999 allocations: 111.45 MiB)

```

[Next page](https://discourse.julialang.org/t/dealing-with-nans/60086.md?page=2)