title: Compute difference between values int according to a condition with Pyspark
body: <p>I have data like this:</p>
  <pre><code>from pyspark.context import SparkContext
  from pyspark.sql.session import SparkSession
  sc = SparkContext.getOrCreate()
  spark = SparkSession(sc)
  from pyspark.sql.functions import substring, length
  dept = [(&quot;A&quot;,1,&quot;2020-11-07 23:19:12&quot;,50), (&quot;A&quot;,1,&quot;2020-11-07 23:19:16&quot;,42), (&quot;A&quot;,1,&quot;2020-11-07 23:19:56&quot;,30), (&quot;A&quot;,0,&quot;2020-11-07 23:20:37&quot;,30), (&quot;A&quot;,0,&quot;2020-11-07 23:21:06&quot;,35), (&quot;A&quot;,0,&quot;2020-11-07 23:21:47&quot;,42), (&quot;A&quot;,1,&quot;2020-11-07 23:22:05&quot;,42), (&quot;A&quot;,1,&quot;2020-11-07 23:22:30&quot;,30),(&quot;A&quot;,1,&quot;2020-11-07 23:23:00&quot;,22), (&quot;A&quot;,0,&quot;2020-11-07 23:23:20&quot;,50), (&quot;A&quot;,0,&quot;2020-11-07 23:23:40&quot;,52), (&quot;A&quot;,1,&quot;2020-11-07 23:24:10&quot;,52), (&quot;A&quot;,1,&quot;2020-11-07 23:25:50&quot;,45),(&quot;B&quot;,1,&quot;2020-11-07 22:19:12&quot;,40), (&quot;B&quot;,1,&quot;2020-11-07 22:20:10&quot;,32), (&quot;B&quot;,0,&quot;2020-11-07 22:21:31&quot;,32), (&quot;B&quot;,0,&quot;2020-11-07 22:22:01&quot;,40), (&quot;B&quot;,0,&quot;2020-11-07 22:22:45&quot;,45), (&quot;B&quot;,1,&quot;2020-11-07 22:23:52&quot;,45), (&quot;B&quot;,1,&quot;2020-11-07 22:24:10&quot;,30)]
  deptColumns = [&quot;Id&quot;,&quot;BAP&quot;,&quot;Time&quot;,&quot;BAZ&quot;]
  deptDF = spark.createDataFrame(data=dept, schema = deptColumns)
  deptDF.show()
  </code></pre>
  <p><a href="https://i.stack.imgur.com/bG6Hl.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/bG6Hl.png" alt="Data test" /></a></p>
  <p>I wish to calculate the difference between the values in the &quot;BAZ&quot; column corresponding to the first 1 of BAP and the last one for each series of &quot;1&quot; and following each ID. Expected result is Something like this:</p>
  <p><a href="https://i.stack.imgur.com/NhWC1.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/NhWC1.png" alt="Result1" /></a></p>
  <p>The final issue is to get the sum of &quot;Delta&quot;:</p>
  <p><a href="https://i.stack.imgur.com/k3l8Z.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/k3l8Z.png" alt="ResultFinal" /></a></p>
  <p>Here's my code that I can't adjust to get the desired result:</p>
  <pre><code>import pyspark.sql.functions as F
  from pyspark.sql.window import Window
  window = Window.partitionBy('Id').orderBy('Time')
  
  Delta = deptDF.withColumn(
      'begin0',
      (F.lag('BAP').over(window) == 0) &amp; (F.col('BAP') == 1)
  ).withColumn(
      'begin1',
      (F.lag('BAP').over(window) != 0) &amp; (F.col('BAP') == 0)
  ).filter(
      'begin0 or begin1'
  ).withColumn(
      'Delta',
      F.when(
          F.col('BAP') == 1,
              (
              F.col('BAZ').cast('bigint') - F.lag('BAZ').over(window).cast('bigint')
              )
         )
      ).orderBy(
      'Id','Time'
  )
  Delta.show()
  </code></pre>
  <p>The substraction is realized following an asendant time evolution.</p>
  <p>I need to modify this code to get the desired result. For the moment I don't find the right condition to put for the selection of the first and last &quot;1&quot;. I thought of the &quot;First&quot; and &quot;Last&quot; function but I can't find the right combination.</p>
  <p>NB: in the case where we have a first &quot;1&quot; without a last &quot;1&quot; (only one 1) I have to put 0 in the soustraction result without taking the next 1 after the series of zeros which erroneously subtracts it. Here is an example to illustrate the problem:</p>
  <p><a href="https://i.stack.imgur.com/fe2bf.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/fe2bf.png" alt="Illustration" /></a></p>
  <p>I have to avoid this problem:</p>
  <p><a href="https://i.stack.imgur.com/03ky3.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/03ky3.png" alt="res" /></a></p>