Using for loop and spatial join in ArcPy?

Question

I have centroids for all the census blocks in the US. These are stored in 51 different datasets, one for each state plus DC. That's my point data.

Then I have a shapefile with the boundaries of each unified public school district in the US. That's my polygon data.

My goal is to loop through each state dataset, and assign each block-centroid the school district in which it sits, and then save the result as a new dataset.

When I do this using the drop-down menus in arcgis, it works fine. The result looks like this:

But when I try to automate this, using arcgis' python function, I get a different, less helpful result, which looks like this:

Here's my code:

#Set the workspace
arcpy.env.workspace = workspace

feature_list = arcpy.ListFeatureClasses()

#create a list with all the files we need (1 for each state plus DC). We'll loop through these.
files = sorted(arcpy.ListFiles("*_blocks_centroids.shp"))

#Using spatial join, merge school district into block centroid data of each block in each state
for num in range(0, len(files) ):
    # Get the state's shapefile:
    current_state_shapefile = files[num]
    #get first 2 letters of filename, which are the state abbreviation
    state = files[num][:2]

    #create local for the target feature dataset
    target_features_temp = state + r'_blocks_centroids.shp'

    #name the output dataset
    out_feature_class_temp = state + r'_blocks_centroids_SpatialJ_sdu'

    #local for the field mapping arguement in arcpy.SpatialJoin_Analysis
    field_mapping_temp = r'STATEFP10 "STATEFP10" true true false 2 Text 0 0 ,First,#,' + state + r'_blocks_centroids,STATEFP10,-1,-1;COUNTYFP10 "COUNTYFP10" true true false 3 Text 0 0 ,First,#,' + state + r'_blocks_centroids,COUNTYFP10,-1,-1;TRACTCE10 "TRACTCE10" true true false 6 Text 0 0 ,First,#,' + state + r'_blocks_centroids,TRACTCE10,-1,-1;BLOCKCE10 "BLOCKCE10" true true false 4 Text 0 0 ,First,#,' + state + r'_blocks_centroids,BLOCKCE10,-1,-1;GEOID10 "GEOID10" true true false 15 Text 0 0 ,First,#,' + state + r'_blocks_centroids,GEOID10,-1,-1;NAME10 "NAME10" true true false 10 Text 0 0 ,First,#,' + state + r'_blocks_centroids,NAME10,-1,-1;MTFCC10 "MTFCC10" true true false 5 Text 0 0 ,First,#,' + state + r'_blocks_centroids,MTFCC10,-1,-1;UR10 "UR10" true true false 1 Text 0 0 ,First,#,' + state + r'_blocks_centroids,UR10,-1,-1;UACE10 "UACE10" true true false 5 Text 0 0 ,First,#,' + state + r'_blocks_centroids,UACE10,-1,-1;UATYP10 "UATYP10" true true false 1 Text 0 0 ,First,#,' + state + r'_blocks_centroids,UATYP10,-1,-1;FUNCSTAT10 "FUNCSTAT10" true true false 1 Text 0 0 ,First,#,' + state + r'_blocks_centroids,FUNCSTAT10,-1,-1;ALAND10 "ALAND10" true true false 19 Double 0 0 ,First,#,' + state + r'_blocks_centroids,ALAND10,-1,-1;AWATER10 "AWATER10" true true false 19 Double 0 0 ,First,#,' + state + r'_blocks_centroids,AWATER10,-1,-1;INTPTLAT10 "INTPTLAT10" true true false 11 Text 0 0 ,First,#,' + state + r'_blocks_centroids,INTPTLAT10,-1,-1;INTPTLON10 "INTPTLON10" true true false 12 Text 0 0 ,First,#,' + state + r'_blocks_centroids,INTPTLON10,-1,-1;GISJOIN "GISJOIN" true true false 18 Text 0 0 ,First,#,' + state + r'_blocks_centroids,GISJOIN,-1,-1;Shape_area "Shape_area" true true false 19 Double 0 0 ,First,#,' + state + r'_blocks_centroids,Shape_area,-1,-1;Shape_len "Shape_len" true true false 19 Double 0 0 ,First,#,' + state + r'_blocks_centroids,Shape_len,-1,-1;ORIG_FID "ORIG_FID" true true false 10 Long 0 10 ,First,#,' + state + r'_blocks_centroids,ORIG_FID,-1,-1;STATEFP10_1 "STATEFP10_1" true true false 2 Text 0 0 ,First,#,tl_2010_sdu,STATEFP10,-1,-1;UNSDLEA10 "UNSDLEA10" true true false 5 Text 0 0 ,First,#,tl_2010_sdu,UNSDLEA10,-1,-1;GEOID10_1 "GEOID10_1" true true false 7 Text 0 0 ,First,#,tl_2010_sdu,GEOID10,-1,-1;NAME10_1 "NAME10_1" true true false 100 Text 0 0 ,First,#,tl_2010_sdu,NAME10,-1,-1;LSAD10 "LSAD10" true true false 2 Text 0 0 ,First,#,tl_2010_sdu,LSAD10,-1,-1;LOGRADE10 "LOGRADE10" true true false 2 Text 0 0 ,First,#,tl_2010_sdu,LOGRADE10,-1,-1;HIGRADE10 "HIGRADE10" true true false 2 Text 0 0 ,First,#,tl_2010_sdu,HIGRADE10,-1,-1;MTFCC10_1 "MTFCC10_1" true true false 5 Text 0 0 ,First,#,tl_2010_sdu,MTFCC10,-1,-1;SDTYP10 "SDTYP10" true true false 1 Text 0 0 ,First,#,tl_2010_sdu,SDTYP10,-1,-1;FUNCSTAT10_1 "FUNCSTAT10_1" true true false 1 Text 0 0 ,First,#,tl_2010_sdu,FUNCSTAT10,-1,-1;ALAND10_1 "ALAND10_1" true true false 14 Double 0 14 ,First,#,tl_2010_sdu,ALAND10,-1,-1;AWATER10_1 "AWATER10_1" true true false 14 Double 0 14 ,First,#,tl_2010_sdu,AWATER10,-1,-1;INTPTLAT10_1 "INTPTLAT10_1" true true false 11 Text 0 0 ,First,#,tl_2010_sdu,INTPTLAT10,-1,-1;INTPTLON10_1 "INTPTLON10_1" true true false 12 Text 0 0 ,First,#,tl_2010_sdu,INTPTLON10,-1,-1'

    arcpy.SpatialJoin_analysis(target_features= target_features_temp, \
                               join_features="tl_2010_sdu.shp", \
                               out_feature_class= out_feature_class_temp, \
                               join_operation="JOIN_ONE_TO_MANY", \
                               join_type="KEEP_ALL", \
                               field_mapping=field_mapping_temp, \
                               match_option="INTERSECT", \
                               search_radius="", \
                               distance_field_name="")

I've also tried changing the match_option argument from INTERSECT to WITHIN and that didn't fix things.

Answer 1

I think your main issue is to hard code field mappings of the spatial join, which has a reasonable purpose to distinguish states when you merge them. As mentioned in my comment, the first thing to try is to remove the field mapping, to let spatial join to deal with it, as in:

arcpy.SpatialJoin_analysis(target_features= target_features_temp, join_features="tl_2010_sdu.shp", out_feature_class= out_feature_class_temp, join_operation="JOIN_ONE_TO_MANY", join_type="KEEP_ALL")

Also, from your subsequent comment about non-nullable field issue, please have a look at this GIS SE thread, which talks about an almost identical problem and the solution advised there is to create field mapping properly and to make sure the field's isNullable property is set to True. One important point to note there though, try setting your output workspace to a GDB, since if you choose a folder where you can create shapefiles cannot handle NULL values.

Lastly, have a look at pyhton's enumerate function for looping through a list where you would like to use both index and the list member itself in the loop (e.g., for num in range(0, len(files) ):)