title: Filter rows where the lead/lag are specific values (window with filter)
body: <p>I have a data frame like this:</p>
  
  <pre><code>  id x y
  1  a 1 P
  2  a 2 S
  3  b 3 P
  4  b 4 S
  </code></pre>
  
  <p>I want to keep rows where the 'lead' value of y is 'S' let us say, so that my resulting data frame will be:</p>
  
  <pre><code>      id     x      y
  1      a     1      P
  2      b     3      P
  </code></pre>
  
  <p>I am able to do it as follows with PySpark:</p>
  
  <pre class="lang-py prettyprint-override"><code>getLeadPoint = udf(lambda x: 'S' if (y == 'S') else 'NOTS', StringType())
  windowSpec = Window.partitionBy(df['id'])
  df = df.withColumn('lead_point', getLeadPoint(lead(df.y).over(windowSpec)))
  dfNew = df.filter(df.lead_point == 'S')
  </code></pre>
  
  <p>But, here, I am mutating an unnecessary column and then filtering.</p>
  
  <p>What I want to do instead is something like this where I filter using lead(), but can't get it to work:</p>
  
  <pre class="lang-py prettyprint-override"><code>dfNew = df.filter(lead(df.y).over(windowSpec) == 'S')
  </code></pre>
  
  <p>Any ideas on how I can achieve the result with direct filter using windowing?</p>
  
  <p>R equivalent is:</p>
  
  <pre class="lang-r prettyprint-override"><code>library(dplyr)
  df %&gt;% group_by(id) %&gt;% filter(lead(y) == 'S')
  </code></pre>