Populating geodatabase field based on query using two other fields

StefaniGermanotta

I am struggling with how to approach filling in the field ???Code??? using python from within an ArcMap 10.1 Advanced license session. I tried to use the calculate field tool in model builder but could not get the code right in the Code Block. I also tried to use the UpdateCursor but still could not get the syntax right. Below is a description of what snippet of code I am struggling with now and my ultimate output goal. Any and all help on any part of this output would be greatly appreciated. Sample of geodatabase table attached.

if PERCENTAGE => 100 AND PCNT_BUFF => 100 then Code = 1
elif PERCENTAGE => 100 AND 0 < PCNT_BUFF < 100 then Code = 2
elif 0 < PERCENTAGE < 100  AND < PCNT_BUFF < 100  then Code = 3
elif PERCENTAGE <=0 AND 0 < PCNT_BUFF < 100 then Code = 4
else PERCENTAGE <= 0 AND PCNT_BUFF <= 0 then Code = 5

What I am trying to do is to populate the Code field in the TI_Test geodatabase table using the above parameters.
Ultimately I will be sorting and counting the values for Code. Each value will have a specific sentence that results will be plugged into. The table values will change each time I run the script on a project area.
For instance:

Value 1 = Distribution of  <CommonName> completely cover(s) the project area and buffer area.
Value 2 = Distribution of  <CommonName> completely cover(s) the project area and some of buffer area.
Value 3 = Distribution of  <CommonName> cover(s) some the project area and buffer area
Value 4 = Distribution of  <CommonName> cover(s) some of the buffer area only
Value 5 = No distribution of <CommonName> cover(s) the project or buffer area.

Count will be needed because the number of species (CommonName) will determine whether commas and the word ???and??? will be required.

Thus if the Code and CommonName for records 1-4 (there will always be just 4 records) was:

1, Mule Deer; 1, Pronghorn antelope; 5, Bighorn sheep; 3, Elk

The resulting text file would look like this (output of sentences sorted by code value):

Distribution of Mule deer and Pronghorn antelope completely cover the project area and buffer area. Distribution of Elk covers some the project area and buffer area. No distribution of Bighorn sheep covers the project or buffer area.

If the table looks like this:

4, Mule Deer; 2, Pronghorn antelope; 2, Bighorn sheep; 2, Elk

The resulting text file would look like this (note cover(s) is variable too):

Distribution of Pronghorn antelope, Bighorn sheep, and Elk completely cover the project area and some of buffer area. Distribution of Mule deer covers some of the buffer area only.

DataDump.gdb.zip

StefaniGermanotta

Finally figured it out and this is the resulting snippet of code inside my larger script, should anyone need something like this. It is probably clunky but I have only been doing coding for a short time and it is not my area of expertise. But it works like a charm. And if anyone has ideas as to how to streamline this, I'm open to suggestions.

 #  Building big game paragraph # import csv import shutil  dbfFields = ('PERCENTAGE','Code', 'PCNT_BUFF')  # Update Code field based on values in PERCENTAGE and PCNT_BUFF fields # arcpy.AddMessage("Updating Code field in big game overlay table")  with arcpy.da.UpdateCursor(AppendTo_TI, dbfFields) as cursor:     # For each row, evaluate the PERCENTAGE and PCNT_BUFF value (index positions     #  of 0 and 2), and update Code (index position of 1)     #     for row in cursor:         if (row[0] >= 100 and row[2] >= 100):             row[1] = 1         elif (row[0] >= 100 and row[2] < 100 and row[2] > 0):             row[1] = 2         elif (row[0] < 100 and row[0] > 0 and row[2] < 100 and row[2] > 0):             row[1] = 3         elif (row[0] <=0 and row[2] < 100 and row[2] > 0):             row[1] = 4         elif (row[0] <= 0 and row[2] <= 0):             row[1] = 5         # Update the cursor with the updated list         #         cursor.updateRow(row)     if row:         del row  arcpy.env.workspace = os.path.dirname(mxd.filePath)  ti_Test_View = "TI_Test_View" tempDataDir = "V:\\ActiveProjects\\DataRequests\\Template\\TempData" template_bg_dist ="V:\\ActiveProjects\\DataRequests\\Template\\DistPara.txt" bg_dist_textfile = databaseDir + "\\DistPara.txt"  shutil.copyfile(template_bg_dist,bg_dist_textfile)  arcpy.AddMessage("Populating big game text file")  counterFile = ("1","2","3","4","5") # Code field values for iterating through for variablefile  in counterFile:     code = "\"Code\" = " + variablefile     outputFile = int(variablefile)     print code     print outputFile      # Make Table View querying data by code number     arcpy.MakeTableView_management(AppendTo_TI, ti_Test_View, code, geodatabaseLocation, "OBJECTID OBJECTID VISIBLE NONE;PERCENTAGE PERCENTAGE VISIBLE NONE;Code Code VISIBLE NONE;CommonName CommonName VISIBLE NONE;PCNT_BUFF PCNT_BUFF VISIBLE NONE")      # Sort Table View by Common Name     ti_Test_Sort = "V:\\ActiveProjects\\DataRequests\\Template\\TempData\\DataDump.gdb\\TI_Test_Sort" + str(outputFile)     arcpy.Sort_management(ti_Test_View, ti_Test_Sort, "CommonName ASCENDING", "UR")      #  Output query results to csv file with file name equal to code number     outputCSVFile = "V:\\ActiveProjects\\DataRequests\\Template\\TempData\\" + str(outputFile) + ".csv"      def TableToCSV(ti_Test_Sort,outputCSVFile):          fields = [f.name for f in arcpy.ListFields(ti_Test_Sort) if f.type <> 'Geometry']         with open(outputCSVFile, 'w') as f:             f.write(','.join(fields)+'\n') #csv headers             with arcpy.da.SearchCursor(ti_Test_Sort, fields) as cursor:                 for row in cursor:                     f.write(','.join([str(r) for r in row])+'\n')      TableToCSV(ti_Test_Sort, outputCSVFile)      arcpy.Delete_management(ti_Test_Sort)      outputFile = int(variablefile)     outputCSVFile = "V:\\ActiveProjects\\DataRequests\\Template\\TempData\\" + str(outputFile) + ".csv"      with open(outputCSVFile) as f:         reader = csv.DictReader(f)         distribution_list = tuple([row["CommonName"] for row in reader])      if outputCSVFile == (tempDataDir+"\\1.csv"):         with open(bg_dist_textfile, 'a') as f:             if len(distribution_list) == 0:                 Dist_Sentence = ""              elif len(distribution_list) == 1:                 Dist_Sentence = "Occupied %s distribution exists throughout the entire project area and four-mile buffer area. " % distribution_list              elif len(distribution_list) == 2:                 Dist_Sentence = "Occupied %s and %s distribution exist throughout the entire project area and four-mile buffer area. " % distribution_list              else:                 for record in distribution_list:                     Dist_Sentence = "Occupied " + ('%s, ' * (len(distribution_list)-1) + 'and %s') % tuple(distribution_list) + " distribution exist throughout the entire project area and four-mile buffer area. "              f.write(Dist_Sentence)     try:         os.remove(tempDataDir+"\\1.csv")     except OSError:         pass      if outputCSVFile == (tempDataDir+"\\2.csv"):         with open(bg_dist_textfile, 'a') as f:             if len(distribution_list) == 0:                 Dist_Sentence = ""              elif len(distribution_list) == 1:                 Dist_Sentence = "Occupied %s exists throughout the entire project area and portions of the four-mile buffer area. " % distribution_list              elif len(distribution_list) == 2:                 Dist_Sentence = "Occupied %s and %s distribution exist throughout the entire project area and portions of the four-mile buffer area. " % distribution_list              else:                 for record in distribution_list:                     Dist_Sentence = "Occupied " + ('%s, ' * (len(distribution_list)-1) + 'and %s') % tuple(distribution_list) + " distribution exist throughout the entire project area and portions of the four-mile buffer area. "              f.write(Dist_Sentence)     try:         os.remove(tempDataDir+"\\2.csv")     except OSError:         pass      if outputCSVFile == (tempDataDir+"\\3.csv"):         with open(bg_dist_textfile, 'a') as f:             if len(distribution_list) == 0:                 Dist_Sentence = ""              elif len(distribution_list) == 1:                 Dist_Sentence = "Occupied %s distribution exists within the project area and four-mile buffer area. " % distribution_list              elif len(distribution_list) == 2:                 Dist_Sentence = "Occupied %s and %s distribution exist within the project area and four-mile buffer area. " % distribution_list              else:                 for record in distribution_list:                     Dist_Sentence = "Occupied " + ('%s, ' * (len(distribution_list)-1) + 'and %s') % tuple(distribution_list) + " distribution exist within the project area and four-mile buffer area. "              f.write(Dist_Sentence)     try:         os.remove(tempDataDir+"\\3.csv")     except OSError:         pass      if outputCSVFile == (tempDataDir+"\\4.csv"):         with open(bg_dist_textfile, 'a') as f:             if len(distribution_list) == 0:                 Dist_Sentence = ""              elif len(distribution_list) == 1:                 Dist_Sentence = "Occupied %s distribution exists in the vicinity of the project area. " % distribution_list              elif len(distribution_list) == 2:                 Dist_Sentence = "Occupied %s and %s distribution exist in the vicinity of the project area. " % distribution_list              else:                 for record in distribution_list:                     Dist_Sentence = "Occupied " + ('%s, ' * (len(distribution_list)-1) + 'and %s') % tuple(distribution_list) + " distribution exist in the vicinity of the project area. "              f.write(Dist_Sentence)     try:         os.remove(tempDataDir+"\\4.csv")     except OSError:         pass      if outputCSVFile == (tempDataDir+"\\5.csv"):         with open(bg_dist_textfile, 'a') as f:             if len(distribution_list) == 0:                 Dist_Sentence = ""              elif len(distribution_list) == 1:                 Dist_Sentence = "No known occupied %s distribution exists in the vicinity of the project area. " % distribution_list              elif len(distribution_list) == 2:                 Dist_Sentence = "No known occupied %s and %s distribution exist in the vicinity of the project area. " % distribution_list              else:                 for record in distribution_list:                     Dist_Sentence = "No known occupied " + ('%s, ' * (len(distribution_list)-1) + 'and %s') % tuple(distribution_list) + " distribution exists in the vicinity of the project area. "              f.write(Dist_Sentence)     try:         os.remove(tempDataDir+"\\5.csv")     except OSError:         pass  arcpy.AddMessage("Done creating big game text file")