Picking a different column in each row of a Dataset[]

Question

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:

ClearAll[firstoption];
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}:

MapThread[
 (#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.

Answer 1

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

ds as in OP, then

ds[
 GroupBy["x"],
 All,
 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.

Answer 2

foo = Select[MatchQ[Except[_Missing]]] /* 
      (# /.  Association[] -> Association["x" ->  Missing[]] &) /* 
      KeyValueMap[Rule] /* 
      First;

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

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