a more efficient way to compute a spark dataframe
<p>I have a sales dataframe similar to:</p>

<pre><code>id   | date              | amount  
-----|-------------------|-------
1    |2016-03-04 12:03:00|10.40
1    |2016-03-04 12:05:10|5.0
1    |2016-03-04 12:15:50|11.30
1    |2016-03-04 12:16:00|9.40
1    |2016-03-04 12:30:00|10.0
1    |2016-03-04 12:40:00|5.40
</code></pre>

<p>And I am trying to group by a time with a time frame of 10 minutes and sum the amount and create a dataframe similar to:  </p>

<pre><code>date             | amount  
-----------------|-------
2016-03-04 12:00 |0.0
2016-03-04 12:10 |15.40
2016-03-04 12:20 |20.70
2016-03-04 12:30 |10.0
2016-03-04 12:40 |5.40
</code></pre>

<p>I have tried to loop a datetime variable, filter the dataframe,group and sum than append to a list and create a dataframe with the list .    </p>

<pre><code>bar_list = []
while date_loop &lt; final_date:
    start_time = date_loop - datetime.timedelta(minutes=10)
    end_time = date_loop - datetime.timedelta(seconds=1)
    df_range = (df_sale
               .filter((df_sale.date &gt;= start_time) &amp; (df_sale.date &lt;= end_time))
               .groupby()
               .sum('amount'))
  bar_list.append((date_loop,df_range.head()['sum(amount)']))
  date_loop += datetime.timedelta(minutes=10)

fields = ['date','amount']
df = sqlContext.createDataFrame(bar_list,fields).na.fill(0)
</code></pre>

<p>In a file with 214626 lines this code can take up to 20 minutes to calculate the sales of 2 months in a time frame of 10 minutes.    </p>

<p>Is there a more efficient way to do it?, I understand that I can share variable between workers,Can a share a list? Is appending to the list my bottle neck?</p>

<p>Thank you.     </p>

<p>This can be little dirty if you want to process as String, you can try this:</p>

<pre><code>def getDTClosestMin(s:String):String = {
 s.substring(0,4)+"-"+s.substring(5,7)+"-"+s.substring(8,10)+" " +
 s.substring(11,13)+":" +
 ((((s.substring(14,16)).toInt)*0.1).ceil)*10).round.toString.padTo(2,"0").mkString }


timeAmtRDD.map(x=&gt; x._1+","+x._2+","+x._3)
  .map(x=&gt;x.split(","))
  .map(x=&gt; (getDTClosestMin(x(1)), x(2).toFloat))
  .reduceByKey(_+_)
  .sortByKey().toDF("date", "amount").show()


Output:
+----------------+------+
|            date|amount|
+----------------+------+
|2016-03-04 12:10|  15.4|
|2016-03-04 12:20|  20.7|
|2016-03-04 12:30|  10.0|
|2016-03-04 12:40|   5.4|
+----------------+------+
</code></pre>

<p>Update what time this took.. ;)</p>

python apache-spark pyspark