# Select observations in a DataFrame from variable with some missing observations

**URL:** <https://discourse.julialang.org/t/select-observations-in-a-dataframe-from-variable-with-some-missing-observations/59427>\
**Category:** Data\
**Tags:** dataframes\
**Created:** [April 16, 2021, 5:33pm UTC](https://discourse.julialang.org/t/select-observations-in-a-dataframe-from-variable-with-some-missing-observations/59427 "2021-04-16T17:33:04Z")\
**Posts on this page:** 16\
**Page:** 1

<div class="post-metadata">

**Author:** ![Albert\_Zevelev](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/albert_zevelev/32/11844_2.png) [@Albert\_Zevelev](https://discourse.julialang.org/u/Albert_Zevelev)\
**Post date:** [April 16, 2021, 5:33pm UTC](https://discourse.julialang.org/t/select-observations-in-a-dataframe-from-variable-with-some-missing-observations/59427/1 "2021-04-16T17:33:04Z")

</div>

```julia
using CSV, HTTP, DataFrames
link = "https://raw.githubusercontent.com/azev77/azev77/main/HP_m.csv";
r = HTTP.get(link)
d = CSV.read(r.body, DataFrame)

d = d[(d.sizerank .<= 50), :]
ERROR: ArgumentError: unable to check bounds for indices of type Missing
Stacktrace:
 [1] checkindex(#unused#::Type{Bool}, inds::Base.OneTo{Int64}, i::Missing)
   @ Base .\abstractarray.jl:671
 [2] checkindex
   @ .\abstractarray.jl:686 [inlined]
 [3] getindex(df::DataFrame, row_inds::Vector{Union{Missing, Bool}}, #unused#::Colon)
   @ DataFrames ~\.julia\packages\DataFrames\zXEKU\src\dataframe\dataframe.jl:458
 [4] top-level scope
   @ REPL[30]:1

```

The variable `sizerank` is either type `Int64` or `missing`.  
Hence `d.sizerank .<= 50` doesn’t work well here.

I hoped the option ` missingstring="NA"` would solve this, but `d.sizerank .<= "50"` does not correspond to the integer values \<50.

```julia
using CSV, HTTP, DataFrames
link = "https://raw.githubusercontent.com/azev77/azev77/main/HP_m.csv";
r = HTTP.get(link)
d = CSV.read(r.body, DataFrame, missingstring="NA")
d = d[(d.sizerank .<= "50"), :] 

```

Hopefully there is an easy way to do this that I’m missing…

---

<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 16, 2021, 7:00pm UTC](https://discourse.julialang.org/t/select-observations-in-a-dataframe-from-variable-with-some-missing-observations/59427/2 "2021-04-16T19:00:07Z")

</div>

`d[coalesce(d.sizerank .<= "50", false), :]` uses `false` in place of `missing` for the filter.

---

<div class="post-metadata">

**Author:** ![Albert\_Zevelev](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/albert_zevelev/32/11844_2.png) [@Albert\_Zevelev](https://discourse.julialang.org/u/Albert_Zevelev)\
**Post date:** [April 16, 2021, 7:04pm UTC](https://discourse.julialang.org/t/select-observations-in-a-dataframe-from-variable-with-some-missing-observations/59427/3 "2021-04-16T19:04:45Z")

</div>

Thanks but my problem is that

> [@Albert\_Zevelev](#):
>
> `d.sizerank .<= "50"` does not correspond to the integer values \<50.

---

<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 16, 2021, 7:08pm UTC](https://discourse.julialang.org/t/select-observations-in-a-dataframe-from-variable-with-some-missing-observations/59427/4 "2021-04-16T19:08:43Z")

</div>

Ah, CSV.read has got the wrong type for that field. It needs to be `Int`.

```julia
d = CSV.read(r.body, DataFrame, missingstring="NA", types=Dict(:sizerank => Int))

```

Though I notice there are some errors when reading.

```julia
┌ Warning: thread = 1 warning: error parsing Int64 around row = 4625, col = 8: ",", error=INVALID: DELIMITED 

```

Make sure you understand the structure of the csv so you can parse it correctly.

---

<div class="post-metadata">

**Author:** ![Albert\_Zevelev](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/albert_zevelev/32/11844_2.png) [@Albert\_Zevelev](https://discourse.julialang.org/u/Albert_Zevelev)\
**Post date:** [April 16, 2021, 7:14pm UTC](https://discourse.julialang.org/t/select-observations-in-a-dataframe-from-variable-with-some-missing-observations/59427/5 "2021-04-16T19:14:40Z")

</div>

The following hack works, but it ain’t great

```julia
using CSV, HTTP, DataFrames
link = "https://raw.githubusercontent.com/azev77/azev77/main/HP_m.csv";
r = HTTP.get(link)
d = CSV.read(r.body, DataFrame, missingstring="NA")
d.sizerank[d.sizerank .== ""] .= "1"
d.sizerank = parse.(Int, d.sizerank)
d = df[(d.sizerank .<= 50), :] 

```

---

<div class="post-metadata">

**Author:** ![matthieu](https://avatars.discourse-cdn.com/v4/letter/m/da6949/32.png) [@matthieu](https://discourse.julialang.org/u/matthieu)\
**Post date:** [April 16, 2021, 7:19pm UTC](https://discourse.julialang.org/t/select-observations-in-a-dataframe-from-variable-with-some-missing-observations/59427/6 "2021-04-16T19:19:36Z")

</div>

This is a job for `subset` in the `dev` version of DataFrames

```julia
using CSV, HTTP, DataFrames
link = "https://raw.githubusercontent.com/azev77/azev77/main/HP_m.csv";
r = HTTP.get(link)
d = CSV.read(r.body, DataFrame)
subset(d, :sizerank => ByRow(<=(50)), skipmissing = true)

```

---

<div class="post-metadata">

**Author:** ![Albert\_Zevelev](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/albert_zevelev/32/11844_2.png) [@Albert\_Zevelev](https://discourse.julialang.org/u/Albert_Zevelev)\
**Post date:** [April 16, 2021, 7:42pm UTC](https://discourse.julialang.org/t/select-observations-in-a-dataframe-from-variable-with-some-missing-observations/59427/7 "2021-04-16T19:42:20Z")

</div>

Thanks! The package conflict breaks my current pkg environment.  
I’ll try it as soon as DataFrames is updated

---

<div class="post-metadata">

**Author:** ![matthieu](https://avatars.discourse-cdn.com/v4/letter/m/da6949/32.png) [@matthieu](https://discourse.julialang.org/u/matthieu)\
**Post date:** [April 16, 2021, 7:50pm UTC](https://discourse.julialang.org/t/select-observations-in-a-dataframe-from-variable-with-some-missing-observations/59427/8 "2021-04-16T19:50:21Z")

</div>

In the current version you can use `filter` with `coalesce`

```julia
using CSV, HTTP, DataFrames
link = "https://raw.githubusercontent.com/azev77/azev77/main/HP_m.csv";
r = HTTP.get(link)
d = CSV.read(r.body, DataFrame)
filter(:sizerank => x -> coalesce(x <= 50, false), d)

```

---

<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 16, 2021, 8:19pm UTC](https://discourse.julialang.org/t/select-observations-in-a-dataframe-from-variable-with-some-missing-observations/59427/10 "2021-04-16T20:19:56Z")

</div>

DataFramesMeta’s `@where` will drop `missing`s by default.

… but it will be deprecated in favor of `@subset` and the `missing`s behavior will probably be the same as `subset`.

---

<div class="post-metadata">

**Author:** ![feanor12](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/feanor12/32/8212_2.png) [@feanor12](https://discourse.julialang.org/u/feanor12)\
**Post date:** [April 16, 2021, 8:29pm UTC](https://discourse.julialang.org/t/select-observations-in-a-dataframe-from-variable-with-some-missing-observations/59427/11 "2021-04-16T20:29:35Z")

</div>

If you have problems with the type you could also use something like this

```julia
emptytomissing(x) = isempty(x) ? missing : parse(Int,x)
select(df, :sizerank => ByRow(emptytomissing) => :newname)

```

---

<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 16, 2021, 8:30pm UTC](https://discourse.julialang.org/t/select-observations-in-a-dataframe-from-variable-with-some-missing-observations/59427/12 "2021-04-16T20:30:36Z")

</div>

Can also do `passmissing(parse)(Int, x)`

---

<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 16, 2021, 8:32pm UTC](https://discourse.julialang.org/t/select-observations-in-a-dataframe-from-variable-with-some-missing-observations/59427/13 "2021-04-16T20:32:49Z")

</div>

You can probably just pass `CSV.read(..., missingstring="")`.

---

<div class="post-metadata">

**Author:** ![Albert\_Zevelev](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/albert_zevelev/32/11844_2.png) [@Albert\_Zevelev](https://discourse.julialang.org/u/Albert_Zevelev)\
**Post date:** [April 17, 2021, 12:06am UTC](https://discourse.julialang.org/t/select-observations-in-a-dataframe-from-variable-with-some-missing-observations/59427/14 "2021-04-17T00:06:21Z")

</div>

I downloaded the lasted DataFrames, but no luck

```julia
julia> using CSV, HTTP, DataFrames;

julia> link = "https://raw.githubusercontent.com/azev77/azev77/main/HP_m.csv";

julia> r = HTTP.get(link);

julia> d = CSV.read(r.body, DataFrame)
109593×9 DataFrame
    Row │ loc Date HPI HPGyy HPGqq HPGmm id sizerank i_treated 
        │ String String Float64 Float64 Float64 Float64 String Int64? Int64     
────────┼───────────────────────────────────────────────────────────────────────────────────────────────────────
      1 │ Bancroft_and_Area 1/1/2010 131.1 5.64062 7.10785 14.2982 Canada missing 0
      2 │ Bancroft_and_Area 2/1/2010 131.6 4.77708 9.84975 0.381388 Canada missing 0
   ⋮ │ ⋮ ⋮ ⋮ ⋮ ⋮ ⋮ ⋮ ⋮ ⋮
 109592 │ Zapata, TX 2/1/2020 91218.0 0.865815 -1.21828 -0.508267 US 927 0
 109593 │ Zapata, TX 3/1/2020 90920.0 0.188432 -1.47271 -0.32669 US 927 0
                                                                                             109589 rows omitted

julia> unique(d.sizerank) |> sort
850-element Vector{Union{Missing, Int64}}:
   0
   1
   2
   3
   ⋮
 930
 932
 933
    missing

julia> subset(d, :sizerank => ByRow(<=(50)), skipmissing = true)
6273×9 DataFrame
  Row │ loc Date HPI HPGyy HPGqq HPGmm id sizerank i_treated 
      │ String String Float64 Float64 Float64 Float64 String Int64? Int64     
──────┼────────────────────────────────────────────────────────────────────────────────────────────────────
    1 │ Atlanta, GA 1/1/2010 157645.0 -10.4162 -1.74821 -0.270761 US 9 0
    2 │ Atlanta, GA 2/1/2010 157070.0 -9.39036 -1.38192 -0.364744 US 9 0
  ⋮ │ ⋮ ⋮ ⋮ ⋮ ⋮ ⋮ ⋮ ⋮ ⋮
 6272 │ Washington, DC 2/1/2020 438354.0 2.97397 1.01905 0.182606 US 7 0
 6273 │ Washington, DC 3/1/2020 439386.0 2.9441 0.904356 0.235426 US 7 0
                                                                                          6269 rows omitted

julia> unique(d.sizerank) |> sort
850-element Vector{Union{Missing, Int64}}:
   0
   1
   2
   3
   ⋮
 930
 932
 933
    missing

```

---

<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 17, 2021, 12:12am UTC](https://discourse.julialang.org/t/select-observations-in-a-dataframe-from-variable-with-some-missing-observations/59427/15 "2021-04-17T00:12:30Z")

</div>

you want `subset!`. `subset` (no `!`) returns a new DataFrame.

---

<div class="post-metadata">

**Author:** ![Albert\_Zevelev](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/albert_zevelev/32/11844_2.png) [@Albert\_Zevelev](https://discourse.julialang.org/u/Albert_Zevelev)\
**Post date:** [April 17, 2021, 1:38am UTC](https://discourse.julialang.org/t/select-observations-in-a-dataframe-from-variable-with-some-missing-observations/59427/16 "2021-04-17T01:38:43Z")

</div>

Yes! This is why I should try to understand code before copy & pasting from discourse.  
Either of the following (mutating or not) works

```julia
subset!(d, :sizerank => ByRow(<=(50)), skipmissing = true)
d = subset(d, :sizerank => ByRow(<=(50)), skipmissing = true)

```

---

<div class="post-metadata">

**Author:** ![DataFrames](https://avatars.discourse-cdn.com/v4/letter/d/e19b73/32.png) [@DataFrames](https://discourse.julialang.org/u/DataFrames)\
**Post date:** [April 17, 2021, 2:02am UTC](https://discourse.julialang.org/t/select-observations-in-a-dataframe-from-variable-with-some-missing-observations/59427/18 "2021-04-17T02:02:44Z")

</div>

How about defining a custom function for your first code:

```julia
sel123(x) = !ismissing(x) ? x<=50 : false
d[sel123.(d.sizerank),:]

```