title: PySpark groupby and max value selection
body: <p>I have a PySpark dataframe like</p>
  
  <pre class="lang-none prettyprint-override"><code> name   city     date
   satya  Mumbai  13/10/2016
   satya  Pune    02/11/2016
   satya  Mumbai  22/11/2016
   satya  Pune    29/11/2016
   satya  Delhi   30/11/2016
   panda  Delhi   29/11/2016
   brata  BBSR    28/11/2016
   brata  Goa     30/10/2016
   brata  Goa     30/10/2016
  </code></pre>
  
  <p>I need to find-out most preferred CITY for each name and Logic is " take city as fav_city if city having max no. of city occurrence on aggregate 'name'+'city' pair". And if multiple same occurrence found then consider city with latest Date.  WIll explain:</p>
  
  <pre><code>d = df.groupby('name','city').count()
  #name  city  count
  brata Goa    2  #clear favourite
  brata BBSR   1
  panda Delhi  1  #as single so clear favourite
  satya Pune   2  ##Confusion
  satya Mumbai 2  ##confusion
  satya Delhi  1   ##shd be discard as other cities having higher count than this city
  
  #So get cities having max count
  dd = d.groupby('name').agg(F.max('count').alias('count'))
  ddd = dd.join(d,['name','count'],'left')
  #name  count  city
   brata    2   Goa    #fav found
   panda    1   Delhi  #fav found
   satya    2   Mumbai #can't say
   satya    2   Pune   #can't say
  </code></pre>
  
  <p>In case of user 'satya' I need to go back to trx_history and get latest date for cities having equal_max count I:e from 'Mumbai' or 'Pune' which is last transacted (max date), consider that  city as fav_city. In this case 'Pune' as '29/11/2016' is latest/max date.</p>
  
  <p>But I am not able to proceed further how to get that done.</p>
  
  <p>Please help me with logic or if any better solution(faster/compact way), please suggest. Thanks. </p>