title: Python pandas: keep row with highest column value
body: <p>Say I have a dataframe of test scores of students, where each student studies different subjects. Each student can take the test for each subject multiple times, and only the highest score (out of 100) will be retained. For instance, say I have a dataframe of all test records:</p>
  
  <pre><code>| student_name | subject | test_number | score | 
  |--------------|---------|-------------|-------|
  | sarah        | maths   | test1       | 78    |
  | sarah        | maths   | test2       | 71    |
  | sarah        | maths   | test3       | 83    |
  | sarah        | physics | test1       | 91    |
  | sarah        | physics | test2       | 97    |
  | sarah        | history | test1       | 83    |
  | sarah        | history | test2       | 87    |
  | joan         | maths   | test1       | 83    |
  | joan         | maths   | test2       | 88    |
  </code></pre>
  
  <p>(1) How do I keep only the test records (rows) with the maximum score? That is,</p>
  
  <pre><code>| student_name | subject | test_number | score | 
  |--------------|---------|-------------|-------|
  | sarah        | maths   | test1       | 78    |
  | sarah        | maths   | test2       | 71    |
  | sarah        | maths   | test3       | 83    |
  | sarah        | physics | test1       | 91    |
  </code></pre>
  
  <p>(2) How would I keep the <em>average</em> of all tests taken for the same subject, for the same student? That is:</p>
  
  <pre><code>| student_name | subject | test_number | ave_score | 
  |--------------|---------|-------------|-----------|
  | sarah        | maths   | na          | 77.333    |
  | sarah        | maths   | na          | 94        |
  | sarah        | maths   | na          | 85        |
  | sarah        | physics | na          | 85.5      |
  </code></pre>
  
  <p>I've tried various combinations of <code>df.sort_values()</code> and <code>df.drop_duplicates(subset=..., keep=...)</code>, to no avail. </p>
  
  <p><strong>Actual Data</strong></p>
  
  <pre><code>| query | target   | pct-similarity | p-val | aln_length | bit-score |
  |-------|----------|----------------|-------|------------|-----------|
  | EV239 | B/Fw6/623 | 99.23         | 0.966 |  832       | 356       |
  | EV239 | B/Fw6/623 | 97.34         | 0.982 |  1022      | 739       |
  | EV239 | MMS-alpha | 92.23         | 0.997 |  838       | 384       |
  | EV239 | MMS-alpha | 93.49         | 0.993 |  1402      | 829       |
  | EV380 | B/Fw6/623 | 94.32         | 0.951 |  324       | 423       |
  | EV380 | B/Fw6/623 | 95.27         | 0.932 |  1245      | 938       |
  | EV380 | MMS-alpha | 99.23         | 0.927 |  723       | 522       |
  | EV380 | MMS-alpha | 99.15         | 0.903 |  948       | 1092      |
  </code></pre>
  
  <p>After aggregation function is applied, only the column <code>pct-similarity</code> will be of interest.</p>
  
  <p>(1) Drop duplicate query+target rows, by choosing the maximum <code>aln_length</code>. Retain the <code>pct-similarity</code> value that belongs to the row with maximum <code>aln_length</code>. </p>
  
  <p>(2) Aggregate duplicate query+target rows by choosing the row with maximum <code>aln_length</code>, and computing the average <code>pct-similarity</code> for that set of duplicate rows. The other numerical columns aren't necessary and will be dropped eventually, so I really don't care what aggregation function (max or mean) is applied to them. </p>