# Filter a number when missing values are in column

**URL:** <https://discourse.julialang.org/t/filter-a-number-when-missing-values-are-in-column/76246>\
**Category:** New to Julia\
**Tags:** dataframes, missing-values\
**Created:** [February 11, 2022, 2:11pm UTC](https://discourse.julialang.org/t/filter-a-number-when-missing-values-are-in-column/76246 "2022-02-11T14:11:13Z")\
**Posts on this page:** 20\
**Page:** 1

<div class="post-metadata">

**Author:** ![sai\_matcha](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/sai_matcha/32/35801_2.png) [@sai\_matcha](https://discourse.julialang.org/u/sai_matcha)\
**Post date:** [February 11, 2022, 2:11pm UTC](https://discourse.julialang.org/t/filter-a-number-when-missing-values-are-in-column/76246/1 "2022-02-11T14:11:13Z")

</div>

Hi i want filter the rows having perticular value, but the column contains missing values as well. can some body help me to do this  
data frame i have

```julia
df = DataFrame()
df.a = ([3,5,6,0,missing])
df.b = 1:5

```

i want to filter rows with value 5 in column df.a

```julia
df = filter(r -> r.a == 5 , df)

```

this code gives me an non-boolean error

data frame i want is

```julia
df = DataFrame()
df.a = 5
df.b = 2

```

Thanks

---

<div class="post-metadata">

**Author:** ![oheil](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/oheil/32/220745_2.png) [@oheil](https://discourse.julialang.org/u/oheil)\
**Post date:** [February 11, 2022, 2:14pm UTC](https://discourse.julialang.org/t/filter-a-number-when-missing-values-are-in-column/76246/2 "2022-02-11T14:14:34Z")

</div>

```julia
df = filter( r -> !ismissing(r.a) && r.a == 5 , df)

```

---

<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:** [February 11, 2022, 2:19pm UTC](https://discourse.julialang.org/t/filter-a-number-when-missing-values-are-in-column/76246/3 "2022-02-11T14:19:07Z")

</div>

For working with `missing` imho the best thing is generally to use `isequal`, because

```julia
julia> 5 == missing
missing

julia> isequal(5, missing)
false

```

so

```julia
julia> df[isequal.(df.a, 5), :]
1×2 DataFrame
 Row │ a b     
     │ Int64? Int64 
─────┼───────────────
   1 │ 5 2

```

(Incidentally, are you aware that you can assign columns directly in the DataFrame construtor, i.e. `DataFrame(a = [3, 5, 6, 0, missing], b = 1:5])`?

---

<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:** [February 11, 2022, 2:20pm UTC](https://discourse.julialang.org/t/filter-a-number-when-missing-values-are-in-column/76246/4 "2022-02-11T14:20:10Z")

</div>

> [@nilshg](#):
>
> ```julia
> julia> 5 == missing
> missing
> 
> ```

What’s the rationale behind that?

---

<div class="post-metadata">

**Author:** ![oheil](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/oheil/32/220745_2.png) [@oheil](https://discourse.julialang.org/u/oheil)\
**Post date:** [February 11, 2022, 2:22pm UTC](https://discourse.julialang.org/t/filter-a-number-when-missing-values-are-in-column/76246/5 "2022-02-11T14:22:10Z")

</div>

Doesn’t explain it but there is some logic inside:

```julia
julia> missing == missing
missing

julia> missing === missing
true

```

---

<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:** [February 11, 2022, 2:22pm UTC](https://discourse.julialang.org/t/filter-a-number-when-missing-values-are-in-column/76246/6 "2022-02-11T14:22:23Z")

</div>

The idea is that you don’t know - `missing` represents a value that’s missing in a statistical sense, i.e. the value exists but is not known (for example because it was not measured, someone didn’t answer that survey question etc.). You therefore can’t be sure that it’s **not** 5, so returning `false` wouldn’t be correct.

(There’s been a lot of ink spilled over this and how it can make working with DataFrames in Julia less ergonomic than some other popular languages, but it is what it is!)

---

<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:** [February 11, 2022, 2:25pm UTC](https://discourse.julialang.org/t/filter-a-number-when-missing-values-are-in-column/76246/7 "2022-02-11T14:25:19Z")

</div>

Thanks, seems useful in many contexts. Of course it is then arbitrary that `isequal` returns `false`, but that allows one to choose what one wants, and probably that is the reason for the two alternatives.

---

<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:** [February 11, 2022, 2:27pm UTC](https://discourse.julialang.org/t/filter-a-number-when-missing-values-are-in-column/76246/8 "2022-02-11T14:27:01Z")

</div>

Yes, at least that’s what the docstring suggests:

```julia
help?> isequal

  isequal(x, y)

  Similar to ==, except for the treatment of floating point numbers and of missing values. 
isequal treats all floating-point NaN values as equal to each other, treats -0.0 as unequal 
to 0.0, and missing as equal to missing. Always returns a Bool value.

```

---

<div class="post-metadata">

**Author:** ![sai\_matcha](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/sai_matcha/32/35801_2.png) [@sai\_matcha](https://discourse.julialang.org/u/sai_matcha)\
**Post date:** [February 11, 2022, 2:39pm UTC](https://discourse.julialang.org/t/filter-a-number-when-missing-values-are-in-column/76246/9 "2022-02-11T14:39:17Z")

</div>

thanks @oheil @nilshg @lmiq for your help.

---

<div class="post-metadata">

**Author:** ![sai\_matcha](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/sai_matcha/32/35801_2.png) [@sai\_matcha](https://discourse.julialang.org/u/sai_matcha)\
**Post date:** [February 11, 2022, 2:40pm UTC](https://discourse.julialang.org/t/filter-a-number-when-missing-values-are-in-column/76246/10 "2022-02-11T14:40:11Z")

</div>

how can remove only df.a = 5 row, keep the remianing data frame as such.

---

<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:** [February 11, 2022, 2:42pm UTC](https://discourse.julialang.org/t/filter-a-number-when-missing-values-are-in-column/76246/11 "2022-02-11T14:42:29Z")

</div>

I wrote a package, [MissingsAsFalse.jl](https://github.com/pdeffebach/MissingsAsFalse.jl) which solves this exact problem via a macro.

```julia
@mfalse missing == 5

```

---

<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 11, 2022, 2:43pm UTC](https://discourse.julialang.org/t/filter-a-number-when-missing-values-are-in-column/76246/12 "2022-02-11T14:43:21Z")

</div>

I would rather recommend (I ignore performance considerations):

```julia
filter(r -> coalesce(r.a == 5, false) , df)

```

for the following reasons:

- by passing `true` you can decide to keep these rows
- it is more explicit what is going on
- `==` and `isequal` have a bit different behavior, so if you want `==` it is best to stick to using `==`

---

<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 11, 2022, 2:44pm UTC](https://discourse.julialang.org/t/filter-a-number-when-missing-values-are-in-column/76246/13 "2022-02-11T14:44:58Z")

</div>

MissingsAsFalse.jl is of course a valid alternative. I was commenting on using `isequal`.

---

<div class="post-metadata">

**Author:** ![lawless-m](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/lawless-m/32/30869_2.png) [@lawless-m](https://discourse.julialang.org/u/lawless-m)\
**Post date:** [February 11, 2022, 2:45pm UTC](https://discourse.julialang.org/t/filter-a-number-when-missing-values-are-in-column/76246/14 "2022-02-11T14:45:02Z")

</div>

Couple more tips

get a view of the dataframe with no missings in any column (no duplicating data)

```julia
julia> dfv = dropmissing(df; view=true)
4×2 SubDataFrame
 Row │ a b     
     │ Int64? Int64 
─────┼───────────────
   1 │ 3 1
   2 │ 5 2
   3 │ 6 3
   4 │ 0 4

```

or just particular columns

```julia
julia> dfv = dropmissing(df, [:a]; view=true)
4×2 SubDataFrame
 Row │ a b     
     │ Int64? Int64 
─────┼───────────────
   1 │ 3 1
   2 │ 5 2
   3 │ 6 3
   4 │ 0 4

```

specifying just the column in the filter is more efficient

```julia
julia> filter(:a=>a -> a == 5 , dfv)
1×2 DataFrame
 Row │ a b     
     │ Int64? Int64 
─────┼───────────────
   1 │ 5 2

```

or multiples

```julia
julia> filter([:a, :b]=>(a,b) -> a >b, dfv)
3×2 DataFrame
 Row │ a b     
     │ Int64? Int64 
─────┼───────────────
   1 │ 3 1
   2 │ 5 2
   3 │ 6 3

```

and finally, again you can filter to a view

```julia
julia> filter([:a]=>a -> a == 5 , dfv; view=true)
1×2 SubDataFrame
 Row │ a b     
     │ Int64? Int64 
─────┼───────────────
   1 │ 5 2

```

---

<div class="post-metadata">

**Author:** ![sai\_matcha](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/sai_matcha/32/35801_2.png) [@sai\_matcha](https://discourse.julialang.org/u/sai_matcha)\
**Post date:** [February 11, 2022, 2:50pm UTC](https://discourse.julialang.org/t/filter-a-number-when-missing-values-are-in-column/76246/15 "2022-02-11T14:50:02Z")

</div>

how to remove only df.a = 5, so ill get a dataframe

```julia
df2 = DataFrame(a = [3,6,0,missing], b = [1,3,4,5] )

```

---

<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:** [February 11, 2022, 2:53pm UTC](https://discourse.julialang.org/t/filter-a-number-when-missing-values-are-in-column/76246/16 "2022-02-11T14:53:35Z")

</div>

I’m going to give an answer using DataFramesMeta.jl

```julia
@rsubset df @mfalse :a != 5

```

---

<div class="post-metadata">

**Author:** ![sai\_matcha](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/sai_matcha/32/35801_2.png) [@sai\_matcha](https://discourse.julialang.org/u/sai_matcha)\
**Post date:** [February 11, 2022, 3:00pm UTC](https://discourse.julialang.org/t/filter-a-number-when-missing-values-are-in-column/76246/17 "2022-02-11T15:00:47Z")

</div>

> [@pdeffebach](#):
>
> ataFrames

i have loaded DataFramesMeta , still if shows @mfalse not defined

```julia

julia> using DataFramesMeta

julia> amik9d = @rsubset amik9 @mfalse :RATE != 0
ERROR: LoadError: UndefVarError: @mfalse not defined

```

---

<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:** [February 11, 2022, 3:05pm UTC](https://discourse.julialang.org/t/filter-a-number-when-missing-values-are-in-column/76246/18 "2022-02-11T15:05:59Z")

</div>

You also need to add the package MissingsAsFalse.jl as well. Though actually the `@rsubset` command will work even without `@mfalse`.

---

<div class="post-metadata">

**Author:** ![sai\_matcha](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/sai_matcha/32/35801_2.png) [@sai\_matcha](https://discourse.julialang.org/u/sai_matcha)\
**Post date:** [February 11, 2022, 3:09pm UTC](https://discourse.julialang.org/t/filter-a-number-when-missing-values-are-in-column/76246/19 "2022-02-11T15:09:43Z")

</div>

it removed both missing and 5 from df.a, i wanted to remove only 5 and keep missing as such

---

<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:** [February 11, 2022, 3:11pm UTC](https://discourse.julialang.org/t/filter-a-number-when-missing-values-are-in-column/76246/20 "2022-02-11T15:11:40Z")

</div>

Ah interesting! That would indeed happen and is a bit of a corner case I hadn’t thought of. You would have to specify this directly, unfortunately

```julia
@rsubset df @mfalse ismissing(:a) || :a != 5

```

I will have to think about a better syntax to support this.

EDIT: This isn’t going to work either. You will have to use `if ... else` commands.

```julia
@rsubset df ismissing(:a) ? true : :a != 5

```

[Next page](https://discourse.julialang.org/t/filter-a-number-when-missing-values-are-in-column/76246.md?page=2)