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

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.

• 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. – Mark R Dec 22 '19 at 21:09
• @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. – Edmund Dec 22 '19 at 21:12
• @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. – Michael E2 Dec 22 '19 at 21:33
• @Edmund, thanks for the clarification. I misunderstood and was trying to "aggregate" the result rather than give results for individual rows. – Mark R Dec 22 '19 at 22:11

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.

• 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[]. – Michael E2 Dec 22 '19 at 21:45
• @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. – Edmund Dec 22 '19 at 21:52
• 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. – Michael E2 Dec 23 '19 at 17:26
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]


• Thanks, this is nice, too! Hard to choose.... – Michael E2 Dec 23 '19 at 16:49