# Finding DataFrame rows with \`missing\` values in specific columns?

**URL:** <https://discourse.julialang.org/t/finding-dataframe-rows-with-missing-values-in-specific-columns/75811>\
**Category:** General Usage\
**Tags:** dataframes, missing-values\
**Created:** [February 4, 2022, 5:21pm UTC](https://discourse.julialang.org/t/finding-dataframe-rows-with-missing-values-in-specific-columns/75811 "2022-02-04T17:21:33Z")\
**Posts on this page:** 13\
**Page:** 1

<div class="post-metadata">

**Author:** ![forestacorn](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/forestacorn/32/19260_2.png) [@forestacorn](https://discourse.julialang.org/u/forestacorn)\
**Post date:** [February 4, 2022, 5:21pm UTC](https://discourse.julialang.org/t/finding-dataframe-rows-with-missing-values-in-specific-columns/75811/1 "2022-02-04T17:21:33Z")

</div>

I have a DataFrame, like the following example, where I am trying to find the rows where the columns `x2` and `x3` are `missing` values:

```julia
df = DataFrame([1 2 3; 4 5 6; 7 missing missing; 10 11 12; 13 missing missing; 16 17 18], :auto)

    x1 x2 x3
    Int64? Int64? Int64?
1 1 2 3
2 4 5 6
3 7 missing missing
4 10 11 12
5 13 missing missing
6 16 17 18

```

After much trial and error I found the following code would work:

```julia
e = [:x2, :x3]

[all(r) for r in eachrow(ismissing.(df[:,e]))]

```

producing a desired result:

```julia
[false false true false true false]

```

However the code seems overly complicated for what it does and I believe I am missing a much simpler solution. Can anyone help?

---

<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 4, 2022, 5:31pm UTC](https://discourse.julialang.org/t/finding-dataframe-rows-with-missing-values-in-specific-columns/75811/2 "2022-02-04T17:31:24Z")

</div>

I guess this can go one of two ways. Do you know that `:x2` and `:x3` are the columns you need to analyze, or are you looking for a solution where you don’t know the columns that are important ex-ante and need a general solution?

If you know it’s `:x2` and `:x3` than maybe

```julia
ismissing.(df.x2) .&& ismissing.(df.x3)

```

(which works in 1.7)

If you don’t know the columns ex-ante, then the current solution is not bad. I would maybe use `map` though.

```julia
julia> e = [:x2, :x3];

julia> map(eachrow(df)) do r
           all(ismissing, r[e])
       end
6-element Vector{Bool}:
 0
 0
 1
 0
 1
 0

```

From the perspective of DataFramesMeta , for the first option you can write

```julia
julia> @with df @byrow ismissing(:x2) && ismissing(:x3)
6-element Vector{Bool}:
 0
 0
 1
 0
 1
 0

```

or

```julia
julia> @with df @. ismissing(:x2) && ismissing(:x3)
6-element BitVector:
 0
 0
 1
 0
 1
 0

```

With DataFramesMeta in the 2nd perspective, that exact feature exists on master, but is not fully implemented.

---

<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 4, 2022, 5:36pm UTC](https://discourse.julialang.org/t/finding-dataframe-rows-with-missing-values-in-specific-columns/75811/3 "2022-02-04T17:36:23Z")

</div>

> [@pdeffebach](#):
>
> ```julia
> ismissing.(df.x2) .&& ismissing.(df.x3)
> 
> ```
> 
> (which works in 1.7)

This works also in 1.6 and 1.8:

```julia
ismissing.(df.x2) .& ismissing.(df.x3)
#or
ismissing.(df[:,:x2]) .& ismissing.(df[:,:x3])

```

---

<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 4, 2022, 5:47pm UTC](https://discourse.julialang.org/t/finding-dataframe-rows-with-missing-values-in-specific-columns/75811/4 "2022-02-04T17:47:50Z")

</div>

And another one:

```julia
all.(map.(ismissing,eachrow(df[:,e])))

```

which is the same as @pdeffebach map version but in one line.

---

<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 4, 2022, 6:08pm UTC](https://discourse.julialang.org/t/finding-dataframe-rows-with-missing-values-in-specific-columns/75811/5 "2022-02-04T18:08:21Z")

</div>

They are not the same.

```julia
df[:, e]

```

makes a copy of all the columns. This will be expensive for large data frames.

Similarly, `&` is not the same as `&&`. `1 & 2` is a bit-wise operation, `1 && 2` will fail.

---

<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 4, 2022, 7:36pm UTC](https://discourse.julialang.org/t/finding-dataframe-rows-with-missing-values-in-specific-columns/75811/6 "2022-02-04T19:36:40Z")

</div>

> [@pdeffebach](#):
>
> This will be expensive for large data frames

Ah, great, thanx for the clarification, didn’t pay attention to this.

> [@pdeffebach](#):
>
> `&` is not the same as `&&`

This I know, I didn’t say they are the same. Why is `&` a problem here? Or why is `&&` to be prefered?

---

<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 4, 2022, 7:39pm UTC](https://discourse.julialang.org/t/finding-dataframe-rows-with-missing-values-in-specific-columns/75811/7 "2022-02-04T19:39:44Z")

</div>

It’s a not problem in this exact instance, but it is less safe to use in general. If you were to forget to write `ismissing.(x)` and just did `x`, the code would still run, but the result would be nonsense.

---

<div class="post-metadata">

**Author:** ![forestacorn](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/forestacorn/32/19260_2.png) [@forestacorn](https://discourse.julialang.org/u/forestacorn)\
**Post date:** [February 4, 2022, 9:34pm UTC](https://discourse.julialang.org/t/finding-dataframe-rows-with-missing-values-in-specific-columns/75811/9 "2022-02-04T21:34:07Z")

</div>

Thanks for your replies, they have helped my understanding, leading me to try this code:

```julia
map(r -> all(ismissing, r[e]), eachrow(df))

```

as suggested by the `do` loop documentation and the help of @pdeffebach’s code . It takes around 22\mu s in Pluto on my old system as does @pdeffebach’s do loop code, @oheil 's version takes about 92\mu s, which although slower is helpful for seeing the effect of different approaches. My original code takes 60\mu s.

---

<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 4, 2022, 9:36pm UTC](https://discourse.julialang.org/t/finding-dataframe-rows-with-missing-values-in-specific-columns/75811/10 "2022-02-04T21:36:39Z")

</div>

If you really need good performance, instead of `eachrow(df)` use `Tables.namedtupleiterator(df[!, e])`, which will be faster. Assuming `e` isn’t too big, hopefully not bigger than 20.

Of course, there are even faster ways to go about this that we can dig into if you need.

---

<div class="post-metadata">

**Author:** ![rocco\_sprmnt21](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/rocco_sprmnt21/32/20127_2.png) [@rocco\_sprmnt21](https://discourse.julialang.org/u/rocco_sprmnt21)\
**Post date:** [February 5, 2022, 3:12pm UTC](https://discourse.julialang.org/t/finding-dataframe-rows-with-missing-values-in-specific-columns/75811/11 "2022-02-05T15:12:47Z")

</div>

```julia
ismissing.(coalesce.(df.x2, df.x3))

```

---

<div class="post-metadata">

**Author:** ![rocco\_sprmnt21](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/rocco_sprmnt21/32/20127_2.png) [@rocco\_sprmnt21](https://discourse.julialang.org/u/rocco_sprmnt21)\
**Post date:** [February 5, 2022, 5:12pm UTC](https://discourse.julialang.org/t/finding-dataframe-rows-with-missing-values-in-specific-columns/75811/12 "2022-02-05T17:12:51Z")

</div>

try this, for the generic case

```julia
e=eachcol(df[!,names(df, !=("x1"))])

ismissing.(reduce((x,y)->coalesce.(x,y), e))

```

---

<div class="post-metadata">

**Author:** ![rocco\_sprmnt21](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/rocco_sprmnt21/32/20127_2.png) [@rocco\_sprmnt21](https://discourse.julialang.org/u/rocco_sprmnt21)\
**Post date:** [February 5, 2022, 7:17pm UTC](https://discourse.julialang.org/t/finding-dataframe-rows-with-missing-values-in-specific-columns/75811/13 "2022-02-05T19:17:14Z")

</div>

```julia
cols1=eachcol(df1[!,names(df1, !=("x1"))])
mapreduce(x->findall(ismissing,x), intersect, cols1)

```

Although not as fast as the use of broadcast coalesce on columns, the filter and subset functions would do the job.

```julia
filter(x -> all(ismissing,x[2:end]), df)

```

```julia
subset(df, names(df,!=("x1"))=>ByRow((r...)->all(ismissing,r)) )

```

PS  
the syntax of the subset function in this case is much less intuitive and direct (it is necessary to slurp the input variables and it is not the first thing that comes to mind)

---

<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 7, 2022, 6:01pm UTC](https://discourse.julialang.org/t/finding-dataframe-rows-with-missing-values-in-specific-columns/75811/14 "2022-02-07T18:01:56Z")

</div>

That’s what `AsTable` is for