Friday, 18 August 2017

ArcPy Insert Cursor not inserting all rows?



I have a script here that takes data from a cross-tabulated non-spatial dataset where monthly values are stored in a single table row for a given year and linearizes those data so that each monthly value is a new record in a new table. I'm using arcpy to create a search cursor to grab the cross-tabulated data and an insert cursor to write the linearized data to a new table. Here is a screenshot of the original table (obviously there are other ancillary fields as well):


enter image description here


The script:


import arcpy, os

months = {'JAN':1,'FEB':2,'MAR':3,'APR':4,'MAY':5,'JUN':6,'JUL':7,'AUG':8,
'SEP':9,'OCT':10,'NOV':11,'DEC':12}
ignore = ['Arbitrary_count','TOTAL_MGY','JAN','FEB','MAR','APR','MAY','JUN','JUL','AUG','SEP','OCT','NOV','DEC']

ws = r'D:\Data\Users\jbellino\Project\faswam\data\water_use\SC\FromTomAb\SC_WELLS_data_jcb.mdb'

arcpy.env.workspace = ws
arcpy.env.overwriteOutput = True
tbl = 'ORIGINAL_DHEC_WELL DATA'
itbl = 'monthly_dhec_well_data'

fields = arcpy.ListFields(tbl)

rows = arcpy.SearchCursor(os.path.join(ws,tbl))
irows = arcpy.InsertCursor(os.path.join(ws,itbl))
for row in rows:

for month in months:
#--for each row in the original table, and for each month stored in that row,
# create a new record in 'itbl'
irow = irows.newRow()
for field in fields:
if field.name == month:
#--if the field name refers to a month abbreviation it contains data
# first convert the month abbreviation to month number
irow.cn_mo = months[month]
try:

# then grab the data in the field and process it into the appropriate
# fields of the new table in the correct units
irow.cn_qnty_mo_va = row.getValue(field.name)*1000000
irow.cn_qnty_mo_va_mega = row.getValue(field.name)
except:
#--skip null values
pass
elif field.name not in ignore:
#--if the field name is not a month abbreviation, just copy the data
# to the new table

irow.setValue(field.name,row.getValue(field.name))
irows.insertRow(irow)
del irows

Now, the problem is that the script will process every row in the original table, however not all rows are transferred into the new linearized table. The number of records that do make it is seemingly random and it has happened with a number of different source and destination tables. My work around has been to create/delete my insert cursor inside the for month in months: loop, but this can really bog things down with large datasets. Even then the script always "misses" the last record in the original table and I have to append the monthly values to the new table manually.


Any ideas of what may be going on here?



Answer



I had initially neglected to delete my irow object at the end of the script; after updating my code the insert cursor appears to be adding all rows (even the last one!) the way it should be. Here is the final code:


import arcpy, os


months = {'JAN':1,'FEB':2,'MAR':3,'APR':4,'MAY':5,'JUN':6,'JUL':7,'AUG':8,
'SEP':9,'OCT':10,'NOV':11,'DEC':12}
ignore = ['Arbitrary_count','TOTAL_MGY','JAN','FEB','MAR','APR','MAY','JUN','JUL','AUG','SEP','OCT','NOV','DEC']

ws = r'D:\Data\Users\jbellino\Project\faswam\data\water_use\SC\FromTomAb\SC_WELLS_data_jcb.mdb'
arcpy.env.workspace = ws
arcpy.env.overwriteOutput = True
tbl = 'ORIGINAL_DHEC_WELL DATA'
itbl = 'monthly_dhec_well_data'


fields = arcpy.ListFields(tbl)

rows = arcpy.SearchCursor(os.path.join(ws,tbl))
irows = arcpy.InsertCursor(os.path.join(ws,itbl))
for row in rows:
for month in months:
#--for each row in the original table, and for each month stored in that row,
# create a new record in 'itbl'
irow = irows.newRow()
for field in fields:

if field.name == month:
#--if the field name refers to a month abbreviation it contains data
# first convert the month abbreviation to month number
irow.cn_mo = months[month]
try:
# then grab the data in the field and process it into the appropriate
# fields of the new table in the correct units
irow.cn_qnty_mo_va = row.getValue(field.name)*1000000
irow.cn_qnty_mo_va_mega = row.getValue(field.name)
except:

#--skip null values
pass
elif field.name not in ignore:
#--if the field name is not a month abbreviation, just copy the data
# to the new table
irow.setValue(field.name,row.getValue(field.name))
irows.insertRow(irow)
del irow,irows
del row,rows

No comments:

Post a Comment

arcpy - Changing output name when exporting data driven pages to JPG?

Is there a way to save the output JPG, changing the output file name to the page name, instead of page number? I mean changing the script fo...