title: Efficient set intersection to get rows in DataFrame
body: <p>I have a dataframe with 3 values relevant to this question, <code>:ID</code>, <code>:Position</code>, <code>:Probability</code>. Each row is unique, but multiple rows may have the same <code>ID</code>. What I'd like to do is get all of the rows for a certain value of <code>Position</code>, that share an <code>ID</code> with any row that has a <code>Probability</code> higher than some value in a different position.</p>
  
  <p>For example, say I have the following DataFrame (df):</p>
  
  <pre><code>1020692×8 DataFrames.DataFrame
  │ Row     │ ID  │ Position      │ Probability │
  ├─────────┼─────┼───────────────┼─────────────┤
  │ 1       │ 425 │ "first"       │ 0.02        │
  │ 2       │ 425 │ "last"        │ 0.03        │
  │ 3       │ 425 │ "penultimate" │ 0.02        │
  │ 4       │ 425 │ "other"       │ 0.04        │
  │ 5       │ 421 │ "first"       │ 0.44        │
  │ 6       │ 421 │ "last"        │ 0.85        │
  │ 7       │ 421 │ "second"      │ 0.59        │
  │ 8       │ 421 │ "other"       │ 1.0         │
  ⋮
  </code></pre>
  
  <p>If I set a threshold of <code>0.8</code>, I want to end up with all of the rows where <code>:Position == "first"</code> if that <code>:ID</code> has <code>:Position == "last" &amp;&amp; :Probability &gt; 0.8</code>. In other words, I'd want row 5, since row 6 has a <code>:Probability &gt; 0.8</code>, but not row 1, since row 2 does not.</p>
  
  <p>The row to check the threshold will not always follow the row I want to keep. Not all rows where <code>:Position == "first"</code> will have a <code>"last"</code> row to check, but there will be at most one.</p>
  
  <p>The way I tried to solve this was to make a vector of all the <code>ID</code>s in <code>last</code> position with <code>Probability &gt; 0.8</code>, and then tried to subset the dataframe using <code>in()</code>. So...</p>
  
  <pre><code>firsts = df[df[:Position] .== "first", :]
  lasts = df[df[:Position] .== "last", :]
  meetsthreshold = lasts[lasts[:Probability] .&gt; 0.8, :ID]
  
  final = firsts[[in(i, meetsthreshold) for i in firsts[:ID]], :]
  </code></pre>
  
  <p>I tested this with a super short vector of <code>ID</code>s and it works, but it lags super hard on my actual data (where <code>length(meetsthreshold)</code> is > 100k). I think what I want is basically a set intersection, and if I do that with the <code>ID</code>s(eg <code>intersect(Set(firsts[:ID]), Set(meetsthreshold))</code>) it's basically instantaneous. Is there a way to do the set intersection with a dataframe so I can actually get the rows?</p>