Spatial Join in Python Field Mapping with Multiple Merge Rules

Question

I'm performing a Spatial Join with a polygon (target) and a point (joinFeature)- which has 5 fields in it that I need to "sum". I have written a Spatial Join and (after many long days) gotten the field mapping to work! But this time is different because I am using a Merge Rule.

Here is my code, right now it breaks at the spatial join and outputs an empty feature class.

Also, this is in a Python Toolbox and uses Parameters to get the target and joinFeature. Like I said, I have gotten this to work before without a merge rule and with different fields...

 # Create a new fieldmappings and add the two input feature classes.
 fms = arcpy.FieldMappings()
 fms.addTable(target)
 fms.addTable(joinFeature)
 # Create the required FieldMap and FieldMappings objects
 fm_apt_poly_APT_ID = arcpy.FieldMap()
 fm_stud_ELEM = arcpy.FieldMap()
 fm_stud_INT = arcpy.FieldMap()
 fm_stud_MID = arcpy.FieldMap()
 fm_stud_HIGH = arcpy.FieldMap()
 fm_stud_TOT_STUD = arcpy.FieldMap()
 fms = arcpy.FieldMappings()
 # Get the field names for both original files
 apt_poly_APT_ID = "APT_ID"
 stud_ELEM = "ELEM"
 stud_INT = "INT"
 stud_MID = "MID"
 stud_HIGH = "HIGH"
 stud_TOT_STUD = "TOT_STUD"
 # Add fields to their corresponding FieldMap objects
 fm_apt_poly_APT_ID.addInputField(target, "APT_ID")
 fm_stud_ELEM.addInputField(joinFeature, "ELEM")
 fm_stud_INT.addInputField(joinFeature, "INT")
 fm_stud_MID.addInputField(joinFeature, "MID")
 fm_stud_HIGH.addInputField(joinFeature, "HIGH")
 fm_stud_TOT_STUD.addInputField(joinFeature, "TOT_STUD")
 # Set the output field properties for both FieldMap objects
 apt_poly_APT_ID = fm_apt_poly_APT_ID.outputField
 apt_poly_APT_ID.name = "APT_ID"
 fm_apt_poly_APT_ID.outputField = apt_poly_APT_ID
 stud_ELEM = fm_stud_ELEM.outputField
 stud_ELEM.name = "ELEM"
 stud_ELEM.aliasName = "ELEM"
 fm_stud_ELEM.outputField = stud_ELEM
 stud_INT = fm_stud_ELEM.outputField
 stud_INT.name = "INT"
 stud_INT.aliasName = "INT"
 fm_stud_INT.outputField = stud_INT
 stud_MID = fm_stud_MID.outputField
 stud_MID.name = "MID"
 stud_MID.aliasName = "MID"
 fm_stud_MID.outputField = stud_MID
 stud_HIGH = fm_stud_HIGH.outputField
 stud_HIGH.name = "HIGH"
 stud_HIGH.aliasName = "HIGH"
 fm_stud_HIGH.outputField = stud_HIGH
 stud_TOT_STUD = fm_stud_TOT_STUD.outputField
 stud_TOT_STUD.name = "TOT_STUD"
 stud_TOT_STUD.aliasName = "TOT_STUD"
 fm_stud_TOT_STUD.outputField = stud_TOT_STUD
 # Set the merge rule to sum and then replace the old field map in the mappings object with the updated one
 fm_stud_ELEM.mergeRule = "SUM"
 fm_stud_INT.mergeRule = "SUM"
 fm_stud_MID.mergeRule = "SUM"
 fm_stud_HIGH.mergeRule = "SUM"
 fm_stud_TOT_STUD.mergeRule = "SUM"
 fms.addFieldMap(fm_apt_poly_APT_ID)
 fms.addFieldMap(fm_stud_ELEM)
 fms.addFieldMap(fm_stud_INT)
 fms.addFieldMap(fm_stud_MID)
 fms.addFieldMap(fm_stud_HIGH)
 fms.addFieldMap(fm_stud_TOT_STUD)
 # Spatial Join Sample Code
 # SpatialJoin_analysis (target_features, join_features, out_feature_class, {join_operation}, {join_type}, {field_mapping}, {match_option}, {search_radius}, {distance_field_name})
 # Process: Spatial Join
 arcpy.SpatialJoin_analysis(target, joinFeature, output, "JOIN_ONE_TO_ONE", "KEEP_ALL", fms, "", "INTERSECT", "")
 arcpy.AddMessage("Just performed Spatial Join")

• forrestchev: I should note that if I use the replaceFieldMap function like I have found in some of my help research, I get null values in my fields in my output and the TOT_STUD is twice as much as the sum... No idea how that happens!
• PolyGeo: Welcome to GIS SE! As a new user be sure to take the Tour. For any coding questions we ask you to only present a snippet that shows where you are stuck. Although you are only showing the one tool class, I think it should be possible and helpful for you to try to reduce that some more to just the parts we really need to see. There is an edit button beneath your question that you can use to revise it with any requested clarifications. Potential answerers do not always read the comments.

Answer 1

The solution to my problem is a frustrating one but it works so i'm ok with it. I ended up deleting all of the fields in my target and join feature except the ones that I needed to keep. Then field mapped those with a merge rule of "Sum" (see below) and then performed a table join, two field calculations and changed Null values to 0. Here is my code that works:

 # Copy features to a new layer
 arcpy.CopyFeatures_management(target,outputfeatureAPT,config_keyword="#",spatial_grid_1="0",spatial_grid_2="0",spatial_grid_3="0")
 arcpy.CopyFeatures_management(joinFeatureSTUD,outputfeatureSTUD,config_keyword="#",spatial_grid_1="0",spatial_grid_2="0",spatial_grid_3="0")
 # Delete fields in copy features before you spatial join
 arcpy.DeleteField_management(outputfeatureAPT,drop_field="APT;ADDRESS;PA;TYPE;STATUS;LIU;MRU;UNITS;CLASS;LOW_INCOME;APP_NUM;ELEM;INT_;MID;HIGH;ELEM_STUD;INT_STUD;MID_STUD;HIGH_STUD;TOT_STUD;ELEM_YLD;INT_YLD;MID_YLD;HIGH_YLD;TOT_YLD;LAST_EDIT;NOTES")
 # The following inputs are layers or table views: "stud_0915_1"
 arcpy.DeleteField_management(outputfeatureSTUD,drop_field="Loc_name;Status;Score;Match_type;X;Y;Match_addr;Side;Pct_along;ARC_Street;ARC_Zip;ARC_City;ARC_Postal;ISD;STUD_ID;NAME;F_NAME;L_NAME;ADDRESS;ZIP;CITY;CAMPUS_ID;SCHOOL;GRADE;ETHNICITY;ED;BIL;PA;RESIDE;CLASS;SUB;EE_PK;EE;PK;KG;GRD1;GRD2;GRD3;GRD4;GRD5;GRD6;GRD7;GRD8;GRD9;GRD10;GRD11;GRD12;AMER_IND;ASIAN;BLACK;HISPANIC;WHITE;PACIFIC;MULTI;BIL_EE;BIL_PK;BIL_KG;BIL_GRD1;BIL_GRD2;BIL_GRD3;BIL_GRD4;BIL_GRD5;BIL_GRD6;BIL_ELEM;BIL_INT;BIL_MID;BIL_HIGH;ED_ELEM;ED_INT;ED_MID;ED_HIGH;AMER_IND_ELEM;AMER_IND_INT;AMER_IND_MID;AMER_IND_HIGH;ASIAN_ELEM;ASIAN_INT;ASIAN_MID;ASIAN_HIGH;BLACK_ELEM;BLACK_INT;BLACK_MID;BLACK_HIGH;HISPANIC_ELEM;HISPANIC_INT;HISPANIC_MID;HISPANIC_HIGH;WHITE_ELEM;WHITE_INT;WHITE_MID;WHITE_HIGH;PACIFIC_ELEM;PACIFIC_INT;PACIFIC_MID;PACIFIC_HIGH;MULTI_ELEM;MULTI_INT;MULTI_MID;MULTI_HIGH;SF;MF;EXEMPT_65;RENTER")
 # Create a new fieldmappings and add the two input feature classes.
 fms = arcpy.FieldMappings()
 fms.addTable(outputfeatureAPT)
 fms.addTable(outputfeatureSTUD)
 ELEMSumIndex = fms.findFieldMapIndex("ELEM")
 INT_SumIndex = fms.findFieldMapIndex("INT_")
 MIDSumIndex = fms.findFieldMapIndex("MID")
 HIGHSumIndex = fms.findFieldMapIndex("HIGH")
 TOT_STUDSumIndex = fms.findFieldMapIndex("TOT_STUD")
 # Create the required FieldMap and FieldMappings objects
 fm_apt_poly_APT_ID = arcpy.FieldMap()
 fm_stud_ELEM = fms.getFieldMap(ELEMSumIndex)
 fm_stud_INT_ = fms.getFieldMap(INT_SumIndex)
 fm_stud_MID = fms.getFieldMap(MIDSumIndex)
 fm_stud_HIGH = fms.getFieldMap(HIGHSumIndex)
 fm_stud_TOT_STUD = fms.getFieldMap(TOT_STUDSumIndex)
 fms = arcpy.FieldMappings()
 # Create the required FieldMap and FieldMappings objects
 fm_apt_poly_APT_ID = arcpy.FieldMap()
 fm_stud_ELEM = arcpy.FieldMap()
 fm_stud_INT_ = arcpy.FieldMap()
 fm_stud_MID = arcpy.FieldMap()
 fm_stud_HIGH = arcpy.FieldMap()
 fm_stud_TOT_STUD = arcpy.FieldMap()
 fms = arcpy.FieldMappings()
 # Get the field names for both original files
 apt_poly_APT_ID = "APT_ID"
 stud_ELEM = "ELEM"
 stud_INT_ = "INT_"
 stud_MID = "MID"
 stud_HIGH = "HIGH"
 stud_TOT_STUD = "TOT_STUD"
 # Add fields to their corresponding FieldMap objects
 fm_apt_poly_APT_ID.addInputField(outputfeatureAPT, "APT_ID")
 fm_stud_ELEM.addInputField(outputfeatureSTUD, "ELEM")
 fm_stud_INT_.addInputField(outputfeatureSTUD, "INT_")
 fm_stud_MID.addInputField(outputfeatureSTUD, "MID")
 fm_stud_HIGH.addInputField(outputfeatureSTUD, "HIGH")
 fm_stud_TOT_STUD.addInputField(outputfeatureSTUD, "TOT_STUD")
 # Set the output field properties for both FieldMap objects
 apt_poly_APT_ID = fm_apt_poly_APT_ID.outputField
 apt_poly_APT_ID.name = "APT_ID"
 fm_apt_poly_APT_ID.outputField = apt_poly_APT_ID
 stud_ELEM = fm_stud_ELEM.outputField
 stud_ELEM.name = "ELEM"
 stud_ELEM.aliasName = "ELEM"
 fm_stud_ELEM.outputField = stud_ELEM
 stud_INT_ = fm_stud_ELEM.outputField
 stud_INT_.name = "INT_"
 stud_INT_.aliasName = "INT_"
 fm_stud_INT_.outputField = stud_INT_
 stud_MID = fm_stud_MID.outputField
 stud_MID.name = "MID"
 stud_MID.aliasName = "MID"
 fm_stud_MID.outputField = stud_MID
 stud_HIGH = fm_stud_HIGH.outputField
 stud_HIGH.name = "HIGH"
 stud_HIGH.aliasName = "HIGH"
 fm_stud_HIGH.outputField = stud_HIGH
 stud_TOT_STUD = fm_stud_TOT_STUD.outputField
 stud_TOT_STUD.name = "TOT_STUD"
 stud_TOT_STUD.aliasName = "TOT_STUD"
 fm_stud_TOT_STUD.outputField = stud_TOT_STUD
 # Set the merge rule to sum and then replace the old field map in the mappings object with the updated one
 fm_stud_ELEM.mergeRule = "SUM"
 fm_stud_INT_.mergeRule = "SUM"
 fm_stud_MID.mergeRule = "SUM"
 fm_stud_HIGH.mergeRule = "SUM"
 fm_stud_TOT_STUD.mergeRule = "SUM"
 fms.addFieldMap(fm_apt_poly_APT_ID)
 fms.addFieldMap(fm_stud_ELEM)
 fms.addFieldMap(fm_stud_INT_)
 fms.addFieldMap(fm_stud_MID)
 fms.addFieldMap(fm_stud_HIGH)
 fms.addFieldMap(fm_stud_TOT_STUD)
 # Spatial Join Sample Code
 # SpatialJoin_analysis (target_features, join_features, out_feature_class, {join_operation}, {join_type}, {field_mapping}, {match_option}, {search_radius}, {distance_field_name})
 # Process: Spatial Join
 arcpy.AddMessage("Performing Student Spatial Join")
 arcpy.SpatialJoin_analysis(outputfeatureAPT, outputfeatureSTUD, output2, "JOIN_ONE_TO_ONE", "KEEP_ALL", fms, "INTERSECT", "", "")
 arcpy.AddMessage("Just performed Student Spatial Join")
 # Join the output to your apt_poly_xxxx by the APT_ID field
 # JoinField_management Sample Code
 # arcpy.JoinField_management(Input Feature, "Field Name", "join table", "join field", {["fields"]})
 arcpy.AddJoin_management(target, "APT_ID", output2, "APT_ID","")
 arcpy.AddMessage("Just performed Student Table Join")
 # Calculate Field_management Sample Code
 # arcpy.CalculateField_management(Input Feature, "Field Name", expression, "expression type", code block)
 # Process: Calculate Field, with a string index in the expression used to isolate the feature class name
 arcpy.CalculateField_management(target, targetname + ".ELEM_STUD", '!'+output2[74:]+'.ELEM!', "PYTHON_9.3")
 # Process: Calculate Field (2)
 arcpy.CalculateField_management(target, targetname + ".INT_STUD", '!'+output2[74:]+'.INT_!', "PYTHON_9.3")
 # Process: Calculate Field (3)
 arcpy.CalculateField_management(target, targetname + ".MID_STUD", '!'+output2[74:]+'.MID!', "PYTHON_9.3")
 # Process: Calculate Field (4)
 arcpy.CalculateField_management(target, targetname + ".HIGH_STUD", '!'+output2[74:]+'.HIGH!', "PYTHON_9.3")
 # Process: Calculate Field (5)
 arcpy.CalculateField_management(target, targetname + ".TOT_STUD", '!'+output2[74:]+'.TOT_STUD!', "PYTHON_9.3")
 # Remove the Join
 arcpy.RemoveJoin_management(target, "")
 # Process: Calculate Field
 arcpy.CalculateField_management(target, "ELEM_YLD", "[ELEM_STUD] / [UNITS]", "VB", "")
 # Process: Calculate Field (2)
 arcpy.CalculateField_management(target, "INT_YLD", "[INT_STUD]/ [UNITS]", "VB", "")
 # Process: Calculate Field (3)
 arcpy.CalculateField_management(target, "MID_YLD", "[MID_STUD]/ [UNITS]", "VB", "")
 # Process: Calculate Field (4)
 arcpy.CalculateField_management(target, "HIGH_YLD", "[HIGH_STUD]/ [UNITS]", "VB", "")
 # Process: Calculate Field (5)
 arcpy.CalculateField_management(target, "TOT_YLD", "[TOT_STUD]/ [UNITS]", "VB", "")
 arcpy.AddMessage("Changing Null values to 0")
 # Update Null values in the calculated fields
 with arcpy.da.UpdateCursor(target, ("ELEM_STUD","INT_STUD","MID_STUD","HIGH_STUD","TOT_STUD","ELEM_YLD","INT_YLD","MID_YLD","HIGH_YLD","TOT_YLD")) as cursor:
 for row in cursor:
 newrow = [0 if x == None else x for x in row]
 cursor.updateRow(newrow)
 del row
 return