I have a Dataset[] like this:

ds = Dataset[{
   <|"x" -> 1, "a" -> Missing[], "b" -> 10, "c" -> 14|>,
   <|"x" -> 1, "a" -> Missing[], "b" -> Missing[], 
    "c" -> Missing[]|>,
   <|"x" -> 2, "a" -> 12, "b" -> 13, "c" -> 16|>,
   <|"x" -> 1, "a" -> 11, "b" -> Missing[], "c" -> 15|>

The keys "a", "b", and "c" are hierarchical, and I would like to get the first non-Missing key/value pair for a given value of "x".

For instance, for each "x" -> 1, I'd like to get the first key/value pair for which the value is not Missing[]. The output would look something like this:

{"b" -> 10, "x" -> Missing[], "a" -> 11}

The key "x" in the second result is irrelevant as long as the value is Missing[].

I can extract the desired keys, more or less:

firstoption[x_] := First[Position[x[[{"a", "b", "c"}]],
     _?(! MissingQ[#] &), 1, Heads -> False] /. {} -> {{Missing[]}}];

ds[Select[#x == 1 &], firstoption] // Normal
(*  {{Key["b"]}, {Missing[]}, {Key["a"]}}  *)

The next step would be to MapThread Lookup on the {rows, keys}:

 (#2 /. Missing[] -> "x") -> Lookup[##, Missing[]] &,
 {Normal@ds[Select[#x == 1 &]],
  Flatten@Normal@ds[Select[#x == 1 &], firstoption]}]
(*  {Key["b"] -> 10, "x" -> Missing[], Key["a"] -> 11}  *)

OK, so that's pretty much what I wanted. The head Key doesn't matter. I can remove it (or add it) easily.

My question is, Is this the best way to do this?

I have my doubts. My approach seems tortuous. MapThread does not work with Dataset[], so either it's an oversight or there's a better way to do this.

  • $\begingroup$ In your first example, should it have be {"b"->10,"c"->Missing[],"a"->11}? You appear to be using "x"->1 to decide what values of the keys {"a","b","c"} so I would expect "c", not "x" to be in the first reported result. Just making sure I understand. $\endgroup$
    – Mark R
    Dec 22, 2019 at 21:09
  • $\begingroup$ @MarkR He wants to select the first key-value that is not missing and default to "x" -> Missing[] when no such pair exist. You are suggesting to select the last Missing when no non-missing exist. Not the same. $\endgroup$
    – Edmund
    Dec 22, 2019 at 21:12
  • $\begingroup$ @MarkR Edmund is right. I'd prefer the key for Missing[] to not be one of "a", "b", or "c", because I'd like the presence of the key to indicate that option was chosen first; however, if the coding were somehow natural for the key to be "c" or some other string, that would be acceptable if the value were still Missing[], indicating that none of the options were chosen. $\endgroup$
    – Michael E2
    Dec 22, 2019 at 21:33
  • $\begingroup$ @Edmund, thanks for the clarification. I misunderstood and was trying to "aggregate" the result rather than give results for individual rows. $\endgroup$
    – Mark R
    Dec 22, 2019 at 22:11

2 Answers 2


With Dataset's syntax sugar you may implicitly use Query.

ds as in OP, then

 KeyDrop["x"] /*
  Normal /*
  Curry[SelectFirst, {3, 1, 2}][FreeQ[_Missing], "x" -> Missing[]]
 ] //Normal
<|1 -> {"b" -> 10, "x" -> Missing[], "a" -> 11}, 2 -> {"a" -> 12}|>

Hope this helps.

  • $\begingroup$ Thanks! I like getting an Association for the whole dataset all at once, too. In my actual use-case there are many columns interspersed, but it seems I can use KeyTake[{"a", "b", "c"}] instead of KeyDrop[]. $\endgroup$
    – Michael E2
    Dec 22, 2019 at 21:45
  • $\begingroup$ @MichaelE2 If you only want to evaluate for a particular value of "x" then just swap out the GroupBy for a Select and drop the All level. Good that swapping KeyDrop for KeyTake works out. $\endgroup$
    – Edmund
    Dec 22, 2019 at 21:52
  • $\begingroup$ Thanks again. This morning I had an insight and think I'm beginning to grok descending vs. ascending and /*. Before when I made a mistake with Dataset, I'd just go back to ordinary arrays. I'm hoping eventually Dataset will seem easier, more natural, and more powerful. $\endgroup$
    – Michael E2
    Dec 23, 2019 at 17:26
foo = Select[MatchQ[Except[_Missing]]] /* 
      (# /.  Association[] -> Association["x" ->  Missing[]] &) /* 
      KeyValueMap[Rule] /* 

ds2 = ds[Select[#x == 1 &], {"a", "b", "c"} /* <|"fc" -> foo|>];

Join[ds[Select[#x == 1 &]], ds2, 2]

enter image description here

  • $\begingroup$ Thanks, this is nice, too! Hard to choose.... $\endgroup$
    – Michael E2
    Dec 23, 2019 at 16:49

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.