title: Method to dynamically build large dataframe (spark or pandas) for export to csv
body: <p>I have a csv that I import into databricks using spark.read.  This large file contains records/transactions on a daily level.  I trim the dataframe down to 5 columns and leave the 500,000 rows as-is.  I am trying to build a summary table of this source file that represents these records/transactions at a month level (aggregate).</p>
  <p>The script has a filter/groupby/sum command that returns one row that summarizes the data into counts for a month.  A row that is returned by the query would look like this:</p>
  <pre><code>+---------+---------+-------+-------------+
  |  Country|StockCode|YYYY-MM|sum(Quantity)|
  +---------+---------+-------+-------------+
  |Singapore|        M| 2011-4|           10|
  +---------+---------+-------+-------------+
  </code></pre>
  <p>the script iterates over the source dataframe and returns each time.  I am having the trouble being able to use the output (display or csv export) of this script.  Both in pyspark and pandas I have problems.  I'm not sure how to stack the result of the query and what form it should be in?</p>
  <p>#Pandas
  If I do it in pandas, the script takes very long to generate the file (I believe pandas + me doing it not so efficiently is causing the extended duration) ~ 2.5 hours.  The display and write.csv commands works rather quickly though and complete in approx a few seconds.</p>
  <p>#Pyspark
  If I do this in pyspark the script takes about 10 minutes to complete, but the display and the export crash.  The notebook either returns a timeout error, restarts or throws crash errors.</p>
  <p>Should the approach be to create a list of lists dynamically, and when that is completely built, convert that to a dataframe for use?  I've been trying all the ways I have come across and I seem to not make any progress.</p>
  <p>Here is the code that generates the results</p>
  <pre><code>#officeSummaryDFBefore
  column_names = &quot;Country|StockCode|YYYY-MM|Quantity&quot;
  monthlyCountsBeforeImpactDate = spark.createDataFrame(
    [
      tuple('' for i in column_names.split(&quot;|&quot;))
    ],
    column_names.split(&quot;|&quot;)
  ).where(&quot;1=0&quot;)
  
  monthlyCountsBeforeImpacteDateRow = spark.createDataFrame(
    [
      tuple('' for i in column_names.split(&quot;|&quot;))
    ],
    column_names.split(&quot;|&quot;)
  ).where(&quot;1=0&quot;)
  
  try :
    for country in country_lookup :
      country = country[0]
      print(country_count, &quot; country(s) left&quot;)
      country_count = country_count - 1
      for stockCode in stockCode_lookup :
        stockCode = stockCode[0]
        monthlyCountsBeforeImpacteDateRow = dataBeforeImpactDate.filter((col(&quot;Country&quot;).rlike(country)) &amp; (col(&quot;StockCode&quot;).rlike(stockCode))).groupby(&quot;Country&quot;, &quot;StockCode&quot;, &quot;YYYY-MM&quot;).sum()
        monthlyCountsBeforeImpacteDateRow.show()
        dfsCountsBefore = [monthlyCountsBeforeImpacteDateRow, monthlyCountsBeforeImpactDate]
        monthlyCountsBeforeImpactDate = reduce(DataFrame.union, dfsCountsBefore)
        
  except Exception as e:
    print(e) 
  </code></pre>
  <p>I declare dfsCountsBeforeImpactDate inside the loop which doesn't seem right, but when it is outside it comes back as NULL.</p>