分组求和并写入Excel，Python

Question

我正在写一个脚本，目的是从一个Access数据库的表格中提取一些数据，然后把这些数据写入一个Excel表格。在这个过程中，我还想对Excel表格进行一些格式设置。我把Excel表格的输出结果放在了我的脚本下面。

在下面的脚本中，我从Access表格中提取数据，并把它放到Excel表格里。这个脚本会把第一列的所有值进行分组，比如所有的M1、所有的M20a等等。第二列是它们对应的面积，单位是公顷。第三列是每个分组的面积总和。

所有的面积总和都是正确的，位置也没错，除了最后一个分组（ZWA）。这个分组的总和没有写出来。我觉得我需要调整脚本，让它能够识别表格中的最后一行。

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")

下面是Excel中的输出表格。你会看到在第三列中，我没有得到ZWA的总面积。有人能告诉我这是为什么吗？我还想把第三列第一行的0去掉。

                      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

数据提取数据输出脚本调试数据分组面积计算总和计算 excel格式化 Access数据库

分组求和并写入Excel，Python

1 个回答

撰写回答