# Using Select on Dataset with missing keys

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.

• Would Select[data, Lookup[#, "Job", ""] === "Police" &] be useful? – WReach Aug 8 at 20:20
• @WReach, That seems to do the same thing as the double select using KeyExistsQ. Am I missing something? – kickert Aug 8 at 21:20
• Yes, but it ought to be faster as it only requires a single pass. It is basically the same as the original Select[data, #Job == "Police"&] except that it is tolerant of missing keys. – WReach Aug 8 at 22:16
• @WReach, I will give it a shot. My concern is that I have quite a few other functions I regularly use with Select such as ContainsAny, MemberQ, StringContains, etc. – kickert Aug 9 at 3:04

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[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 |
|-------------------------------------+-------------------------------|

• That certainly makes my code look much less hacked together. But interestingly, it seems much slower on my full dataset. In one example using ContainsAny using Part took about 6.8 seconds to run while using the double Select approach and KeyExistsQ only took 5.8 seconds. In a StringContainsQ search the Part approach took 7.7 seconds while the double select only took 1.9 seconds. – kickert Aug 8 at 18:40
• Check my edit! 😇 – Carl Lange Aug 8 at 18:43
• Thanks for the edited solution @CarlLange. I reran it with using the Slot function and it is much quicker than using Part but still slightly slower than double select. With ContainsAny I was getting 5.8 for double select, 6.0 for Slot and 6.8 for Part. With StringContainsQ I got 1.9, 2.2, and 7.7 respectively. The cleaner code is probably worth the very slight performance difference. – kickert Aug 8 at 18:48
• Yes, I think it's very slightly slower on really large datasets - I have noticed a difference of about 0.15 seconds between #slot and #["slot"] on a test dataset with a million rows, coming in at 1.14 seconds rather than 0.99 seconds. Even so, it's very much comparable and a lot faster than Part :) – Carl Lange Aug 8 at 18:51
• Another edit - check out the KeyValuePattern stuff I added. It seems like the fastest option. – Carl Lange Aug 8 at 18:59