title: Pandas Dataframe: Retrieve the Maximum Value in a Pandas Dataframe using .groupby and .idxmax()
body: <p>I have a Pandas Dataframe that contains a series of Airbnb Prices grouped by <em>neighbourhood group</em>  <em>neighbourhood</em> and <em>room_type</em>. My objective is to return the Maximum Average Price for each <em>room_type</em> per Neighbourhood and return only this.</p>
  <p>My approach to this was to use .groupby and .idxmax() to get the maximum values w.r.t to the Index, and then iterate through this accordingly and append the index positions to a list, which is used for filtering purposes.
  (This link was helpful: <a href="https://stackoverflow.com/questions/32459325/python-pandas-dataframe-select-row-by-max-value-in-group">Python Pandas Dataframe select row by max value in group</a>)</p>
  <pre><code>df = airbnb.loc[airbnb['neighbourhood'].isin(list(combined['neighbourhood']))].groupby(['neighbourhood','room_type']).mean().sort_values(by=['Revenues'],ascending=False)['Revenues'].reset_index().sort_values(by=['neighbourhood','room_type']).reset_index()
  tmp_list = []
  test = pd.DataFrame(df.groupby(['neighbourhood','room_type'])['Revenues'].idxmax(axis=0)).reset_index()
  #Re-Assign Revenues back to the Dataframe for Reference
  test['Actual_Revenues'] = df['Revenues']
  #The Maximum Revenue Value for each Sub Grouping is Returned by Index
  for i in neighbourhood_list:
      print(test[test['neighbourhood']==i])
      max_index_list = test[test['neighbourhood']==i].sort_values(by='Actual_Revenues', ascending=False).head(1)['Revenues']
      print(max_index_list)
      tmp_list.append(list(max_index_list))
      
  tmp_list = list(np.concatenate(tmp_list).flat)
  df[df.index.isin(tmp_list)]
  </code></pre>
  <p>Now this works smoothly, but I don't believe this is very performant (esp. with my loop).</p>
  <p>How might I seek to optimise my code further to reduce any redundancies and make my code more Pythonic?</p>
  <p>Our expected output shows the below, with only the maximum room type and pricing shown per Neighbourhood. (i.e. One unique entry per Neighbourhood).</p>
  <p>Thank you very much.</p>
  <p>Here is a reproducible dataset you can use to test the code:
  <a href="https://docs.google.com/spreadsheets/d/1x-ktbfJouPzI0hokKw0fSNXAwmD8Q8Yd/edit?usp=sharing&amp;ouid=101578147330059746959&amp;rtpof=true&amp;sd=true" rel="nofollow noreferrer">https://docs.google.com/spreadsheets/d/1x-ktbfJouPzI0hokKw0fSNXAwmD8Q8Yd/edit?usp=sharing&amp;ouid=101578147330059746959&amp;rtpof=true&amp;sd=true</a></p>
  <p>Here is a picture of my output (using the complete dataset as opposed to the partial shown - this is just to show the concept of the maximum price shown per Neighbourhood. If a neighbourhood has multiple room types (which they will), it will return the maximum price and the associated room type.)</p>
  <p><a href="https://ibb.co/HnBysVc" rel="nofollow noreferrer">https://ibb.co/HnBysVc</a></p>