# Correct SQL expression when looping different fields

I'm having trouble with the follow lines of code. Practically what I am doing is a 10 shapefile loop in which field represents the name of the field that I want to be calculated( the name differs by every shapefile and the name of that column is also the name of the file). Since it's 10 shapefiles, I want each one to be calculated individually to the new field I created. Everything works fine except the SQL expression. I have no idea how to format the expression in so that field is divided by the [MinorityS] field. If I were to be doing it without the loop it would just be the expression of '[Cuban] / [MinorityS]'. I just want Cuban to be the 10 shapefiles which in this case is field representing all the field names that I want to be in that first bracket of Cuban.

import arcpy
arcpy.env.workspace = r"myworkspace"
arcpy.env.overwriteOutput = True
MetroCT =  r"metrotracts"

Fieldslist = arcpy.ListFields(MetroCT)
Metrofields = [f.name for f in arcpy.ListFields(MetroCT)]
Clusterslist = ["SouthAmeri", "SpanishSpa", "Mexican", "PuertoRica", "Cuban", "Dominican", "CentralAme", "European", "Arab", "Africans"]

for field in Clusterslist:
out = field + ".shp"
Cluster = arcpy.ClustersOutliers_stats(MetroCT, field, out)
output = field + "1"+".shp"
HHClusters = arcpy.FeatureClassToFeatureClass_conversion(Cluster,r"mylocation", output, "\"COType\" = 'HH'")
cursor = arcpy.da.SearchCursor(Cluster, field)
summed_total = 0
for row in cursor:
summed_total = summed_total + row[0]
arcpy.CalculateField_management(HHClusters, "MinorityS", summed_total)
expressionmin = expressionmin = '[{}] / [MinorityS]'.format(field)
arcpy.CalculateField_management(HHClusters, "MinbyTot", expressionmin, "VB")

-
Can you post the rest of the code, or show us how you are determining the value of field? It would also be helpful to know what kind of error is occurring when you try that? – wmebane Feb 24 at 1:51
Just added the whole code, thanks! – XSC Feb 24 at 2:23
There's seems to be something going wrong with your quotes in this line HHClusters = arcpy.FeatureClassToFeatureClass_conversion(Cluster,r"mylocation, output, "\"COType\" = 'HH'") – Midavalo Feb 24 at 2:26
Oh completely ignore that, I changed the location before posting here! But thanks. Everything works great until the last 2 lines in which the calculation isn't happening. – XSC Feb 24 at 2:28
Do you have some example values from your field and MinorityS fields? – Midavalo Feb 24 at 3:18

Without seeing any more of your code, try this:

arcpy.AddField_management(HHClusters, "MinbyTot", "LONG", 10)
expressionmin = '[{}] / [MinorityS]'.format(field)
arcpy.CalculateField_management(HHClusters, "MinbyTot", expressionmin, "VB")


With your second field [MinorityS] being in square brackets, I think your first field also needs to be in square brackets - '[yourfield] / [MinorityS]'.

'[{}] / [MinorityS]'.format(field) instead of '[' + field + '] / [MinorityS]'

Alternatively try changing from VB to Python expression type:

arcpy.AddField_management(HHClusters, "MinbyTot", "LONG", 10)
expressionmin = '!{}! / !MinorityS!'.format(field)
arcpy.CalculateField_management(HHClusters, "MinbyTot", expressionmin, "PYTHON_9.3")

-
Unfortunately I get a zero in all of them, here's the whole code I'm using on that part if it helps. I'll edit my original post and add it. Thank you for your help! – XSC Feb 24 at 2:17
Is expressionmin = '[{}] / [MinorityS]'.format(field) the right way around? Shouldn't it be expressionmin = '[MinorityS] / [{}]'.format(field)? If I'm reading it right you're dividing the smaller number by the larger number which would give a value < 1 and in a LONG datatype field it'll probably output a 0 as result – Midavalo Feb 24 at 4:41
The change to python 9.3 worked thank you for your help! – XSC Feb 24 at 4:52
excellent - I've updated my answer to include that (rather than in a comment) – Midavalo Feb 24 at 5:10

You can streamline your script by using an UpdateCursor rather than Calculate Field. In this example, "field_c" is empty and the field that is being calculated.

import arcpy

fc = r'C:\path\to\your.gdb\feature_class'

with arcpy.da.UpdateCursor(fc, ["field_a", "field_b", "field_c"]) as cursor:
for row in cursor:
# This is essentially saying: field_c = field_a / field_b
row[2] = row[0]/row[1]
cursor.updateRow(row)

-
The problem is that "field_a" changes with every loop for example first loop is "arab" second loop is "cuban" etc. I tried your code and I got the following error for row in cursor2: RuntimeError: A column was specified that does not exist – XSC Feb 24 at 3:43