title: How can I pick a "best match" in Apache Spark when aggregating within a DataFrame?
body: <p>Suppose I have this data in a Spark data frame:</p>
  
  <pre><code>Person   Position
  -----------------
  Tom      Gold
  Tom      Silver
  Tom      Silver
  Dick     Silver
  Dick     Silver
  Dick     null
  Harry    Gold
  Harry    Silver
  Alice    Bronze
  Alice    null
  Bob      null
  Bob      null
  </code></pre>
  
  <p>This is what I want to achieve in my output:</p>
  
  <pre><code>Person   BestPosition
  -----------------
  Tom      Gold
  Dick     Silver
  Harry    Gold
  Alice    Bronze
  Bob      null
  </code></pre>
  
  <p>This is not the real scenario but it's a pretty good approximation. The ranking order of positions can either be hard-coded or config driven, I don't mind (there are only 3 or 4 in the real scenario and they won't change).</p>
  
  <p>If I were doing this in C# I think it would look something like this:</p>
  
  <pre><code>var data = new List&lt;int&gt;{}.Select(x =&gt; new { Name = "Tom",  Position = "Gold" },  ... etc);
  var aggregation = data
      .GroupBy(scores =&gt; scores.Name)
      .Select(grouping =&gt; new {
          Name = grouping.Key,
          BestPosition = new [] {"Gold", "Silver", "Bronze"}.FirstOrDefault(x =&gt; grouping.Any(score =&gt; score.Position == x))
      })
      .ToList();
  </code></pre>
  
  <p>I'm writing my application in Spark for .NET (which in retrospect was a bad decision I can't afford to change direction now). I appreciate nobody else is using Spark for .NET, but what I'm struggling with here is more concept than code so if anyone can solve the problem in Scala/Java/Python I'm pretty sure I can convert.</p>
  
  <p>I'm pretty new to Spark so the solution might be obvious, but I'm not sure how to go about it.</p>