title: Grouping values, creating a sum and writing to excel, Python
body: <p>i am putting together a script to take some data from an access table and write it to an excel table. In the process, I'm trying to format the excal table. I have pasted the output of the excel table below my script.</p>
  
  <p>In the following script, I pull the data from the access table and get it into the excel table. it groups all the values in the first column (i.e. all the M1's, all the M20a's, and so on). The second column is their associated area in hectares. The third column is the sum of the area for each grouped value.</p>
  
  <p>All the sum of the areas are correct and in the right place, except the last group (ZWA). It's not writing the sum for that value. I have a feeling that I need to set the script so that it detects the last row in the table.</p>
  
  <pre><code>import arcpy, xlwt, sys, traceback, datetime
  from arcpy import env
  from xlwt import *
  from itertools import groupby
  from collections import defaultdict
  from time import strftime
  
  
  # Set workspace for the file to be used
  env.workspace = "Z:\TestFolder"
  env.overwriteOutput = True
  
  
  # Stores access table into a variable and sorts the SMU field ascendingly
  cur = arcpy.SearchCursor("Access Table", "", "", "SMU", "SMU A")
  
  # Create excel workbook
  book = Workbook()
  sheet1 = book.add_sheet('Sheet 1')
  
  # Create a dictionary
  col_counts = defaultdict(int)
  
  # Set a varialb eto be used in looping through rwos and detecting when the value in the cell is different from the last
  last_value = object()
  
  # Set the start of 2 counters. rowx is to count rows in the access table. rowadd is to add the values in a field alled row.SHAPE_Area
  rowx = 3
  rowadd = 0
  
  # Loop through the access table
  for row in cur:
  
      # Ask if the current value is not equal to the last value in the row.SMU column
      if row.SMU != last_value:
  
          last_value = row.SMU
  
          # if the current value doesn't equal the last value, then place the sum of the row.SHAPE_Area field for the last value in a new cell in a different column.
          sheet1.write(int(rowx+1),3,rowadd/10000)
  
          # Reset counter to 0
          rowadd = 0
          # Add 2 to the counter to create a space between groups of values in the excel table
          rowx += 2
      else:
          # Else only add 1 to the counter if the value in the cell reamisn the same as the last one
          rowx += 1
  
  
      # if the value of the row is the same as the last one, then add the values for a second column together and write to the excel sheet
      if row.SMU == str(last_value):
  
          rowadd += row.SHAPE_Area
          print rowadd
  
          sheet1.write(int(rowx),0,row.SMU)
          sheet1.write(int(rowx),1,row.SHAPE_Area/10000)
  
  # Set the counter to += the last value in the (col_counts[last_value]) variable and start over again
  rowx += (col_counts[last_value])
  
  # Save the workbook
  book.save("Z:\TestFolder\simple.xls")
  </code></pre>
  
  <p>Below is the output table in excel. See that I am not getting the ZWA total area in the third column. Can anyone suggest why this is? I'd like to get rid of the 0 in the first row of the third column as well.</p>
  
  <pre><code>                      0
  M1      0.076492721     
  M1      0.406600839     
  M1      2.98016238      
                        3.46325594
  M20a    0.665489193     
                        0.665489193
  M21     0.005333282     
                        0.005333282
  M23b    0.190245719     
  M23b    0.233315779     
                        0.423561498
  S1      0.201021287     
  S1      0.176390376     
  S1      0.200409435     
  S1      0.009312814     
  S1      0.071782163     
                        0.658916076
  ZWA     0.387293182     
  </code></pre>