title: How to calculate standard deviation over a range of dates when there are dates missing in pyspark 2.2.0
body: <p>I have a pyspark df wherein I am using a combination of windows + udf function to calculate standard deviation over historical business dates. The challenge is that my df is missing dates when there is no transaction. How do I calculate std dev including these missing dates without adding them as additional rows into my df to limit the df size going out of memory.</p>
  <p>Sample Table &amp; Current output</p>
  <pre><code>| ID | Date     | Amount | Std_Dev|
  |----|----------|--------|--------|
  |1   |2021-03-24| 10000  |        |
  |1   |2021-03-26|  5000  |        |
  |1   |2021-03-29| 10000  |2886.751|
  </code></pre>
  <p>Current Code</p>
  <pre><code>from pyspark.sql.functions import udf,first,Window,withColumn
  import numpy as np
  from pyspark.sql.types import IntegerType
  
  windowSpec = Window.partitionBy(&quot;ID&quot;).orderBy(&quot;date&quot;)
  
  workdaysUDF = F.udf(lambda date1, date2: int(np.busday_count(date2, date1)) if (date1 is not None and date2 is not None) else None, IntegerType()) # UDF to calculate difference between business days#
  
  df = df.withColumn(&quot;date_dif&quot;, workdaysUDF(F.col('Date'), F.first(F.col('Date')).over(windowSpec))) #column calculating business date diff#
  
  windowval = lambda days: Window.partitionBy('id').orderBy('date_dif').rangeBetween(-days, 0)
  
  df = df.withColumn(&quot;std_dev&quot;,F.stddev(&quot;amount&quot;).over(windowval(6))\
          .drop(&quot;date_dif&quot;)  
  </code></pre>
  <p>Desired Output where the values of dates missing between 24 to 29 March are being substituted with 0.</p>
  <pre><code>| ID | Date     | Amount | Std_Dev|
  |----|----------|--------|--------|
  |1   |2021-03-24| 10000  |        |
  |1   |2021-03-26|  5000  |        |
  |1   |2021-03-29| 10000  |4915.96 |
  </code></pre>
  <p>Please note that I am only showing std dev for a single date for illustration, there would be value for each row as I am using a rolling windows function.
  Any help would be greatly appreciated.</p>
  <p>PS: Pyspark version is 2.2.0 at enterprise so I do not have flexibility to change the version.</p>
  <p>Thanks,
  VSG</p>