title: Max value of for each column for distinct rows of data frame in r
body: <p>Let’s say there is an ordered df with an ID column, and other columns containing numeral data, ordered by the last column.</p>
  
  <pre><code>ID &lt;- c(123, 142, 21, 562, 36, 721, 847, 321)
  A &lt;- c(96, 83, 73, 47, 88, 65, 72, 67)
  B &lt;- c(72, 69, 88, 75, 63, 89, 48, 80)
  C &lt;- c(95, 94, 94, 94, 65, 81, 75, 75)
  D &lt;- c(63, 88, 89, 88, 89, 79, 88, 79)
  Rating &lt;- c(97, 95, 92, 87, 85, 83, 79, 77)
  df &lt;- data.frame(ID, A, B, C, D, Rating)
  df
  #   ID  A  B  C  D Rating
  #1 123 96 72 95 63     97
  #2 142 83 69 94 88     95
  #3  21 73 88 94 89     92
  #4 562 47 75 94 88     87
  #5  36 88 63 65 89     85
  #6 721 65 89 81 79     83
  #7 847 72 48 75 88     79
  #8 321 67 80 75 79     77
  </code></pre>
  
  <p>The aim is to get the max value for each group/column, with its ID, and each pair needs to be from a distinct row (unique ID). For two IDs with the same value for a column, pick the one with the better Rating.</p>
  
  <p>What I did is use the apply() function to get the max from each column, extract the IDs that have that value, and join them all into a data frame. Because I was still missing an ID for the 4th column, I used an anti join to take out the previous IDs and repeated the process to get this data frame:</p>
  
  <pre><code>my_max &lt;- data.frame(apply(df, 2, max))
  A2 &lt;- df[which(df$A == my_max[2,1]),]%&gt;% dplyr::select(ID, A)
  B2 &lt;- df[which(df$B == my_max[3,1]),]%&gt;% dplyr::select(ID, B)
  C2 &lt;- df[which(df$C == my_max[4,1]),]%&gt;% dplyr::select(ID, C)
  D2 &lt;- df[which(df$D == my_max[5,1]),]%&gt;% dplyr::select(ID, D)
  all &lt;- full_join(A2, B2, by='ID') %&gt;% full_join(C2, by='ID') %&gt;% full_join(D2, by='ID')
  all &lt;- all[-c(4),]
  df &lt;- anti_join(df, all, by='ID')
  my_max &lt;- data.frame(apply(df, 2, max))
  C2 &lt;- df[which(df$C == my_max[4,1]),]%&gt;% dplyr::select(ID, C)
  all &lt;- all %&gt;% full_join(C2, by='ID')
  all &lt;- all[-c(5),-c(4)]
  </code></pre>
  
  <p>To finally give me:</p>
  
  <pre><code>all
  #   ID  A  B  D C.y
  #1 123 96 NA NA  NA
  #2 721 NA 89 NA  NA
  #3  21 NA NA 89  NA
  #4 142 NA NA NA  94
  </code></pre>
  
  <p>Is there a more clean or concise/efficient way of doing this? Not necessarily the same way, perhaps just the ID and role like:</p>
  
  <pre><code>#   ID  Group
  #1 123    A
  #2 721    B
  #3 142    C
  #4 21     D
  </code></pre>