Using Select on Dataset with missing keys

Question

I have a very large dataset with about a million entries with up to 75 keys each. Across the whole dataset about half of the keys are blank. In order to reduce filesize, I have used the following code to remove all the blanks keys:

DeleteCases[Normal[data], "", {2}]

I have another much smaller expression with all the keys pointing to blank strings that I can use with KeyUnion to rebuild the original version if I need to. This works great because it reduces the WL or MX file I am using by about half.

My issue comes when I am trying to use Select. The ragged dataset works when all entries have the keys present, but if it missing the key, I get an error.

Consider this simplified example:

data = Dataset[{<|"Name" -> "Bob", "Location" -> "USA", 
    "Job" -> "Police"|>, <|"Name" -> "Jill", 
    "Location" -> "Canada"|>, <|"Name" -> "Joe", "Location" -> "USA", 
    "Job" -> "Teacher"|>}]

When all keys are there, I can use Select as normal:

Select[data, #Location == "USA" &]

But, if one of the entries is missing the key, I get an error:

Select[data, #Job == "Police" &]

There are a few hacky things I can do. I can use KeyUnion to rebuild a full database.

keys = <|"Name" -> "", "Location" -> "", "Job" -> ""|>
Select[KeyUnion[data, keys], #Job == "Police" &]

I can also use KeyExistsQ to do an initial select and then run from there:

Select[Select[data, KeyExistsQ["Job"]], #Job == "Police" &]

While both of these options yield the results I am looking for, neither are particularly effecient when working with a very large dataset.

I found this question from three years ago that addresses a similar issue with Query, but since that would have been on V10 and I am in V12, I was hoping something had changed to allow Select to work with missing keys.

Answer 1

You can use the Slot function rather than #slot. This is nearly as fast as the #slot implementation, but doesn't cause this issue.

Select[dsa, #["Job"] == "Police" &]

You can also pattern match with KeyValuePattern instead of using Select, which returns the same result - from my testing, this is the fastest option without this issue:

Cases[dsa, KeyValuePattern[{"baz" -> "consequent"}]]

If you are married to using Select, you can use it with KeyValuePattern and MatchQ, but this appears slower:

Select[dsa, 
   MatchQ@KeyValuePattern[{"baz" -> "consequent"}]];

Another, slower option is to use Part instead of #slots. This is a lot slower.

Select[data, #[["Job"]] == "Police" &]
Select[data, Part[#, "Job"] == "Teacher" &]

Now to test:

words = RandomWord[20];

ds = Dataset@
      Table[AssociationThread[{"foo", "bar", "baz"}, 
        Table[RandomChoice@words, 3]], 100000]   
dsa = Dataset[<|"foo" -> #foo, 
         "bar" -> #bar, (If[RandomReal[] > .5, ("baz" -> #baz), 
           Nothing])|> & /@ Normal@ds]

(* --- *)

Select[dsa, #bar == "consequent" &] // RepeatedTiming // First

> 0.0816

Select[dsa, #[["baz"]] == "consequent" &]; // RepeatedTiming // First

> 1.14

Select[dsa, #["baz"] == "consequent" &]; // RepeatedTiming // First

> 0.114

Cases[dsa, KeyValuePattern[{"baz" -> "consequent"}]]; // 
  RepeatedTiming // First

> 0.0943

Select[dsa, MatchQ@KeyValuePattern[{"baz" -> "consequent"}]]; // 
  RepeatedTiming // First

> 0.123

So in order of fast-ness:

| Method                              | Speed on 100,000 test Dataset |
|-------------------------------------+-------------------------------|
| #key                                |                        0.0816 |
| Cases and KeyValuePattern           |                        0.0943 |
| #["key"]                            |                        0.1140 |
| Select, MatchQ, and KeyValuePattern |                        0.1230 |
| #[["key"]]                          |                        1.1400 |
|-------------------------------------+-------------------------------|