Find maximum row per group in Spark DataFrame
<p>I'm trying to use Spark dataframes instead of RDDs since they appear to be more high-level than RDDs and tend to produce more readable code.</p>

<p>In a 14-nodes Google Dataproc cluster, I have about 6 millions names that are translated to ids by two different systems: <code>sa</code> and <code>sb</code>. Each <code>Row</code> contains <code>name</code>, <code>id_sa</code> and <code>id_sb</code>. My goal is to produce a mapping from <code>id_sa</code> to <code>id_sb</code> such that for each <code>id_sa</code>, the corresponding <code>id_sb</code> is the most frequent id among all names attached to <code>id_sa</code>.</p>

<p>Let's try to clarify with an example. If I have the following rows:</p>

<pre><code>[Row(name='n1', id_sa='a1', id_sb='b1'),
 Row(name='n2', id_sa='a1', id_sb='b2'),
 Row(name='n3', id_sa='a1', id_sb='b2'),
 Row(name='n4', id_sa='a2', id_sb='b2')]
</code></pre>

<p>My goal is to produce a mapping from <code>a1</code> to <code>b2</code>. Indeed, the names associated to <code>a1</code> are <code>n1</code>, <code>n2</code> and <code>n3</code>, which map respectively to <code>b1</code>, <code>b2</code> and <code>b2</code>, so <code>b2</code> is the most frequent mapping in the names associated to <code>a1</code>. In the same way, <code>a2</code> will be mapped to <code>b2</code>. It's OK to assume that there will always be a winner: no need to break ties.</p>

<p>I was hoping that I could use <code>groupBy(df.id_sa)</code> on my dataframe, but I don't know what to do next. I was hoping for an aggregation that could produce, in the end, the following rows:</p>

<pre><code>[Row(id_sa=a1, max_id_sb=b2),
 Row(id_sa=a2, max_id_sb=b2)]
</code></pre>

<p>But maybe I'm trying to use the wrong tool and I should just go back to using RDDs.</p>