Arcpy Spatial Join - Not joining all records with 'JOIN_ONE_TO_MANY', 'KEEP_ALL' option

1488
11
07-11-2017 12:12 PM
TessOldemeyer
Occasional Contributor

I am trying to perform a spatial join that will join all my join features (along with their attributes), within a specified distance, to my target features. Given the nature of the datasets, this should more than double the number of records in the target feature class (taking into account that not all of the join features are within the specified search radius). However, this join appears to rarely be functioning accurately. 

My target feature class consists of ~7000 records and when joined accurately via a 'JOIN_ONE_TO_MANY', 'KEEP_ALL'  spatial join contains ~16000 records. However, at least ~90% of the time the spatial join produces just under 8000 records. This has been frustrating, as this join is an integral part of a long script, and incorrect joins propagate into subsequent calculations. 

I am able to get the spatial join tool to run correctly using the same layers and options in ArcMap 10.4 or in ArcPro 1.4. Though, the python script seems to be producing unreliable results when ran in Visual Studio leveraging arcpy.SpatialJoin_analysis. 

The script was originally using Python 2 and ArcGIS 10.4. I switched to Python 3 (3.5.2) using a fresh Conda 3 download and the ArcPro Python package hoping that if the tool was buggy, this may fix it. No luck. Does anyone have any recommendations?

Here is the line of code for the spatial join:

arcpy.SpatialJoin_analysis ("SubLayer_Layer_2", "pp_trans_GUJoin_Layer_2", SubLayer_pp_trans_GUJoin_UniqueSubs, 'JOIN_ONE_TO_MANY', 'KEEP_ALL', "#", 'WITHIN_A_DISTANCE', .000002) 
0 Kudos
11 Replies
DuncanHornby
MVP Frequent Contributor

Your search radius is very small, which suggests to me that your datasets are in WGS84? If so project them to a coordinate system where the units are feet or metres and then try the spatial join. What coordinate system you settle on depends upon the location of your data, which we obviously don't know.

0 Kudos
TessOldemeyer
Occasional Contributor

Thanks for the suggestion. I projected the datasets being used to USA Contigous Albers Equal Area (linear unit: meters) and adjusted the search distance to an appropriate value in meters. However, this did not seem to affect the final output. After a bit more digging, it turns out that the spatial join is producing the correct output, though that feature class is 'losing records' throughout the rest of the script. 

The number of records is included as the printed integer in the screenshot below:

Here is the structure of the code following the spatial join (beginning with Null/Zero line count):

###################################### Line Count 1000 #######################

#add in Line_Count_1000 field
arcpy.AddField_management(subLayer, "Line_Count_1000", "SHORT", "", "", "", "", "NULLABLE", "NON_REQUIRED", "")

#where clause
where_1000 = ' "VOLTAGE" = 1000 '

Join_1000_voltage_freq = defaultGdbPath + '\Join_1000_voltage_freq'

# Make feature layer 
arcpy.MakeFeatureLayer_management(SubLayer_pp_trans_GUJoin_UniqueSubs, "JoinLineCountVoltage_1000", where_1000)
arcpy.Frequency_analysis("JoinLineCountVoltage_1000", Join_1000_voltage_freq, ["SUBID"])


search_feats_unique_voltage_1000 = {f[0]:f[1] for f in arcpy.da.SearchCursor(Join_1000_voltage_freq,["SUBID","Frequency"])}

with arcpy.da.UpdateCursor(subLayer,["SUBID","Line_Count_1000"]) as upd_cur:
    for upd_row in upd_cur:
        upd_row[1] = search_feats_unique_voltage_1000.get(upd_row[0], 0)
        upd_cur.updateRow(upd_row)    
    print("Line_Count_1000 Updated")
del upd_cur

RecordCount = arcpy.GetCount_management(SubLayer_pp_trans_GUJoin_UniqueSubs)
count = int(RecordCount.getOutput(0))
print(count)


########################## NULL or Zero Line Count ##################


#add in NULL_ZERO_LINE_COUNT field
arcpy.AddField_management(subLayer, "NULL_ZERO_LINE_COUNT", "SHORT", "", "", "", "", "NULLABLE", "NON_REQUIRED", "")

#where clause
NULL_ZERO_Voltage = ' "VOLTAGE" is NULL or "VOLTAGE" = 0 '

Join_null_zero_voltage_freq = defaultGdbPath + '\Join_null_zero_voltage_freq'

# Make feature layer 
arcpy.MakeFeatureLayer_management(SubLayer_pp_trans_GUJoin_UniqueSubs, "JoinLineCount_Null_Zero_Voltage", NULL_ZERO_Voltage)
arcpy.Frequency_analysis("JoinLineCount_Null_Zero_Voltage", Join_null_zero_voltage_freq, ["SUBID"])


search_feats_null_zero_voltage = {f[0]:f[1] for f in arcpy.da.SearchCursor(Join_null_zero_voltage_freq,["SUBID","Frequency"])}

with arcpy.da.UpdateCursor(subLayer,["SUBID","NULL_ZERO_LINE_COUNT"]) as upd_cur:
    for upd_row in upd_cur:
        upd_row[1] = search_feats_null_zero_voltage.get(upd_row[0], 0)
        upd_cur.updateRow(upd_row)    
    print("NULL_ZERO_LINE_COUNT Updated")
del upd_cur

RecordCount = arcpy.GetCount_management(SubLayer_pp_trans_GUJoin_UniqueSubs)
count = int(RecordCount.getOutput(0))
print(count)
############################### PP_Line Count ##########################
#add in PP_LINE_COUNT field
arcpy.AddField_management(subLayer, "PP_LINE_COUNT", "SHORT", "", "", "", "", "NULLABLE", "NON_REQUIRED", "")

search_feats_PP_LINE_COUNT = {f[0]:f[1] for f in arcpy.da.SearchCursor(SubLayer_pp_trans_GUJoin,["SUBID","Join_Count"])}

with arcpy.da.UpdateCursor(subLayer,["SUBID","PP_LINE_COUNT"]) as upd_cur:
    for upd_row in upd_cur:
        upd_row[1] = search_feats_PP_LINE_COUNT.get(upd_row[0], 0)
        upd_cur.updateRow(upd_row)    
    print("PP_LINE_COUNT Updated")
del upd_cur

RecordCount = arcpy.GetCount_management(SubLayer_pp_trans_GUJoin_UniqueSubs)
count = int(RecordCount.getOutput(0))
print(count)
#################################### Generating Unit Count #############################

where_GU = ' "GEN_UNITS" > 0 '
 
#add in GU_COUNT field
arcpy.AddField_management(subLayer, "GU_COUNT", "SHORT", "", "", "", "", "NULLABLE", "NON_REQUIRED", "")

search_feats_GU_COUNT = {f[0]:f[1] for f in arcpy.da.SearchCursor(SubLayer_pp_trans_GUJoin,["SUBID","GEN_UNITS"], where_GU)}

with arcpy.da.UpdateCursor(subLayer,["SUBID","GU_COUNT","PP_Line_Count"]) as upd_cur:
    for upd_row in upd_cur:
        #if int(upd_row[2]) > 0:
        if upd_row[2] is not None and int(upd_row[2]) > 0:
        #if search_feats_GU_COUNT[1] > 0 and upd_row[2] > 0:
            upd_row[1] = search_feats_GU_COUNT.get(upd_row[0], 0)/upd_row[2]
        else:
            upd_row[1] = 0 
        upd_cur.updateRow(upd_row)    
    print("GU_Count Updated")
del upd_cur

RecordCount = arcpy.GetCount_management(SubLayer_pp_trans_GUJoin_UniqueSubs)
count = int(RecordCount.getOutput(0))
print(count)
############################## PP_COUNT ######################################

#add in PP_COUNT field
arcpy.AddField_management(subLayer, "PP_COUNT", "SHORT", "", "", "", "", "NULLABLE", "NON_REQUIRED", "")

search_feats_PP_COUNT = {f[0]:f[1] for f in arcpy.da.SearchCursor(SubLayer_pp_trans_GUJoin,["SUBID","Join_Count_PP"])}

with arcpy.da.UpdateCursor(subLayer,["SUBID","PP_COUNT"]) as upd_cur:
    for upd_row in upd_cur:
        upd_row[1] = search_feats_PP_COUNT.get(upd_row[0], 0)
        upd_cur.updateRow(upd_row)    
    print("PP_Count Updated")
del upd_cur

RecordCount = arcpy.GetCount_management(SubLayer_pp_trans_GUJoin_UniqueSubs)
count = int(RecordCount.getOutput(0))
print(count)

##########################################################################################################


arcpy.AddField_management(SubLayer_pp_trans_GUJoin_UniqueSubs, "SUB1SUB2", "TEXT", "", "", "", "", "NULLABLE", "NON_REQUIRED", "")

arcpy.CalculateField_management(SubLayer_pp_trans_GUJoin_UniqueSubs, "SUB1SUB2",'!SUB_1! + "," + !SUB_2!', "PYTHON_9.3")

################################ UNIQUE SUBS BY VOLTAGE - 13.8 ########################

arcpy.AddField_management(subLayer, "UNIQUE_SUB_COUNT_13_8", "SHORT", "", "", "", "", "NULLABLE", "NON_REQUIRED", "")

Join_13_8_freq = defaultGdbPath + '\Join_13_8_freq'
# Make feature layer 
arcpy.MakeFeatureLayer_management(SubLayer_pp_trans_GUJoin_UniqueSubs, "JoinUniqueSubs_13_8", where_13_8)
arcpy.DeleteIdentical_management("JoinUniqueSubs_13_8", ['SUBID', 'SUB1SUB2', 'VOLTAGE'])
arcpy.Frequency_analysis("JoinUniqueSubs_13_8", Join_13_8_freq, ["SUBID"])


search_feats_Unique_Subs_13_8 = {f[0]:f[1] for f in arcpy.da.SearchCursor(Join_13_8_freq,["SUBID","Frequency"])}

with arcpy.da.UpdateCursor(subLayer,["SUBID","UNIQUE_SUB_COUNT_13_8"]) as upd_cur:
    for upd_row in upd_cur:
        upd_row[1] = search_feats_Unique_Subs_13_8.get(upd_row[0], 0)
        upd_cur.updateRow(upd_row)    
    print("UNIQUE_SUB_COUNT_13_8 Updated")
del upd_cur

RecordCount = arcpy.GetCount_management(SubLayer_pp_trans_GUJoin_UniqueSubs)
count = int(RecordCount.getOutput(0))
print(count)
################################### UNIQUE SUBS BY VOLTAGE - 46 ########################

arcpy.AddField_management(subLayer, "UNIQUE_SUB_COUNT_46", "SHORT", "", "", "", "", "NULLABLE", "NON_REQUIRED", "")

Join_46_freq = defaultGdbPath + '\Join_46_freq'

# Make feature layer 
arcpy.MakeFeatureLayer_management(SubLayer_pp_trans_GUJoin_UniqueSubs, "JoinUniqueSubs_46", where_46)
arcpy.DeleteIdentical_management("JoinUniqueSubs_46", ['SUBID', 'SUB1SUB2', 'VOLTAGE'])
arcpy.Frequency_analysis("JoinUniqueSubs_46", Join_46_freq, ["SUBID"])


search_feats_Unique_Subs_46 = {f[0]:f[1] for f in arcpy.da.SearchCursor(Join_46_freq,["SUBID","Frequency"])}

with arcpy.da.UpdateCursor(subLayer,["SUBID","UNIQUE_SUB_COUNT_46"]) as upd_cur:
    for upd_row in upd_cur:
        upd_row[1] = search_feats_Unique_Subs_46.get(upd_row[0], 0)
        upd_cur.updateRow(upd_row)    
    print("UNIQUE_SUB_COUNT_46 Updated")
del upd_cur

RecordCount = arcpy.GetCount_management(SubLayer_pp_trans_GUJoin_UniqueSubs)
count = int(RecordCount.getOutput(0))
print(count)

######################################### UNIQUE SUBS BY VOLTAGE - 69 ########################

arcpy.AddField_management(subLayer, "UNIQUE_SUB_COUNT_69", "SHORT", "", "", "", "", "NULLABLE", "NON_REQUIRED", "")

Join_69_freq =defaultGdbPath + '\Join_69_freq'

# Make feature layer 
arcpy.MakeFeatureLayer_management(SubLayer_pp_trans_GUJoin_UniqueSubs, "JoinUniqueSubs_69", where_69)
arcpy.DeleteIdentical_management("JoinUniqueSubs_69", ['SUBID', 'SUB1SUB2', 'VOLTAGE'])
arcpy.Frequency_analysis("JoinUniqueSubs_69", Join_69_freq, ["SUBID"])


search_feats_Unique_Subs_69 = {f[0]:f[1] for f in arcpy.da.SearchCursor(Join_69_freq,["SUBID","Frequency"])}

with arcpy.da.UpdateCursor(subLayer,["SUBID","UNIQUE_SUB_COUNT_69"]) as upd_cur:
    for upd_row in upd_cur:
        upd_row[1] = search_feats_Unique_Subs_69.get(upd_row[0], 0)
        upd_cur.updateRow(upd_row)    
    print("UNIQUE_SUB_COUNT_69 Updated")
del upd_cur

RecordCount = arcpy.GetCount_management(SubLayer_pp_trans_GUJoin_UniqueSubs)
count = int(RecordCount.getOutput(0))
print(count)

#################################################### UNIQUE SUBS BY VOLTAGE - 115 ########################

arcpy.AddField_management(subLayer, "UNIQUE_SUB_COUNT_115", "SHORT", "", "", "", "", "NULLABLE", "NON_REQUIRED", "")

Join_115_freq =defaultGdbPath + '\Join_115_freq'

# Make feature layer 
arcpy.MakeFeatureLayer_management(SubLayer_pp_trans_GUJoin_UniqueSubs, "JoinUniqueSubs_115", where_115)
arcpy.DeleteIdentical_management("JoinUniqueSubs_115", ['SUBID', 'SUB1SUB2', 'VOLTAGE'])
arcpy.Frequency_analysis("JoinUniqueSubs_115", Join_115_freq, ["SUBID"])


search_feats_Unique_Subs_115 = {f[0]:f[1] for f in arcpy.da.SearchCursor(Join_115_freq,["SUBID","Frequency"])}

with arcpy.da.UpdateCursor(subLayer,["SUBID","UNIQUE_SUB_COUNT_115"]) as upd_cur:
    for upd_row in upd_cur:
        upd_row[1] = search_feats_Unique_Subs_115.get(upd_row[0], 0)
        upd_cur.updateRow(upd_row)    
    print("UNIQUE_SUB_COUNT_115 Updated")
del upd_cur

RecordCount = arcpy.GetCount_management(SubLayer_pp_trans_GUJoin_UniqueSubs)
count = int(RecordCount.getOutput(0))
print(count)

#################################################### UNIQUE SUBS BY VOLTAGE - 138 ########################

######################################## UNIQUE_SUBS_COUNT ########################

arcpy.AddField_management(subLayer, "UNIQUE_SUBS_COUNT", "SHORT", "", "", "", "", "NULLABLE", "NON_REQUIRED", "")

Join_all_freq =defaultGdbPath + '\Join_all_freq'

# Make feature layer 
arcpy.MakeFeatureLayer_management(SubLayer_pp_trans_GUJoin_UniqueSubs, "JoinUniqueSubs_all",)
arcpy.DeleteIdentical_management("JoinUniqueSubs_all", ['LATITUDE', 'LONGITUDE', 'SUB1SUB2'])
arcpy.Frequency_analysis("JoinUniqueSubs_all", Join_all_freq, ["SUBID"])


search_feats_Unique_Subs_all = {f[0]:f[1] for f in arcpy.da.SearchCursor(Join_all_freq,["SUBID","Frequency"])}

with arcpy.da.UpdateCursor(subLayer,["SUBID","UNIQUE_SUBS_COUNT"]) as upd_cur:
    for upd_row in upd_cur:
        upd_row[1] = search_feats_Unique_Subs_all.get(upd_row[0], 0)
        upd_cur.updateRow(upd_row)    
    print("UNIQUE_SUBS_COUNT Updated")
del upd_cur

RecordCount = arcpy.GetCount_management(SubLayer_pp_trans_GUJoin_UniqueSubs)
count = int(RecordCount.getOutput(0))
print(count)

#### additonal Unique Sub Count segments of the code have been omitted for breity #####

################################################# UNIQUE VOLTAGE COUNT ###################################

arcpy.AddField_management(subLayer, "UNIQUE_VOLTAGE_COUNT", "SHORT", "", "", "", "", "NULLABLE", "NON_REQUIRED", "")

Join_voltage_freq = defaultGdbPath + '\Join_voltage_freq'

# Make feature layer 
arcpy.MakeFeatureLayer_management(SubLayer_pp_trans_GUJoin_UniqueSubs, "JoinUniqueVoltage",)
arcpy.DeleteIdentical_management("JoinUniqueVoltage", ['VOLTAGE','SUBID'])
arcpy.Frequency_analysis("JoinUniqueVoltage", Join_voltage_freq, ["SUBID"])


search_feats_Unique_Voltage = {f[0]:f[1] for f in arcpy.da.SearchCursor(Join_voltage_freq,["SUBID","Frequency"])}

with arcpy.da.UpdateCursor(subLayer,["SUBID","UNIQUE_VOLTAGE_COUNT"]) as upd_cur:
    for upd_row in upd_cur:
        upd_row[1] = search_feats_Unique_Voltage.get(upd_row[0], 0)
        upd_cur.updateRow(upd_row)    
    print("UNIQUE_VOLTAGE_COUNT Updated")
del upd_cur

RecordCount = arcpy.GetCount_management(SubLayer_pp_trans_GUJoin_UniqueSubs)
count = int(RecordCount.getOutput(0))
print(count)


end = time.time()
print((end - start)/60)

#if __name__ == '__main__':
#    entry()‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

Any ideas on how the feature class may be losing records?

Thanks!

0 Kudos
DuncanHornby
MVP Frequent Contributor

That's a lot of code to get ones head around but a quick glance and I can see you make numerous calls to 

arcpy.DeleteIdentical_management()

so I guess these are the points at which rows are being removed?

0 Kudos
TessOldemeyer
Occasional Contributor

'DeleteIdentical' is being applied to a specific feature layer in each case. I'm wondering if converting that 'SubLayer_pp_trans_GUJoin_UniqueSubs' feature class to a feature layer multiple times is somehow the culprit?

Digging into it now...

0 Kudos
DuncanHornby
MVP Frequent Contributor

You create layers with different names but they are all built from the same featureclass: SubLayer_pp_trans_GUJoin_UniqueSubs so all your calls to delete identical ultimately feed back to the same featureclass.

0 Kudos
TessOldemeyer
Occasional Contributor

I was under the impression that creating a feature layer created a new independent temporary layer. I was hoping that using the feature layer route would enable specific selection from each feature layer to be passed to the update cursor leaving the original feature class,  SubLayer_pp_trans_GUJoin_UniqueSubs, intact. 

Any suggestions on how to work around this issue? I would like SubLayer_pp_trans_GUJoin_UniqueSubs to retain the record count of 16763 for the proper count updates from the update cursor.

Here is an updated printout showing the record count of the feature layer and SubLayer_pp_trans_GUJoin_UniqueSubs after each throughout processing:

The 'feature layer count:' print statement is always reflective of the SubLayer_pp_trans_GUJoin_UniqueSubs feature layer. I am really confused as to why it varies so much. Some count values are higher than the previous feature layer (maybe I'm missing something). I realize that the 'selected out' count is dependent on the specific selection applied to each specific feature layer, so it will vary among the print statements. 

Here is the code beginning from where the SubLayer_pp_trans_GUJoin_UniqueSubs deviate from 16763 (...If it helps, sorry it is so long; the added print statements ballooned it even more).

################################ UNIQUE SUBS BY VOLTAGE - 13.8 ########################

arcpy.AddField_management(subLayer, "UNIQUE_SUB_COUNT_13_8", "SHORT", "", "", "", "", "NULLABLE", "NON_REQUIRED", "")

Join_13_8_freq = defaultGdbPath + '\Join_13_8_freq'
# Make feature layer 
arcpy.MakeFeatureLayer_management(SubLayer_pp_trans_GUJoin_UniqueSubs, "JoinUniqueSubs_13_8", where_13_8)

RecordCount = arcpy.GetCount_management("JoinUniqueSubs_13_8")
count = int(RecordCount.getOutput(0))
print("feature layer count:", count)

arcpy.DeleteIdentical_management("JoinUniqueSubs_13_8", ['SUBID', 'SUB1SUB2', 'VOLTAGE'])
arcpy.Frequency_analysis("JoinUniqueSubs_13_8", Join_13_8_freq, ["SUBID"])

RecordCount = arcpy.GetCount_management("JoinUniqueSubs_13_8")
count = int(RecordCount.getOutput(0))
print("feature layer selected out count:", count)

search_feats_Unique_Subs_13_8 = {f[0]:f[1] for f in arcpy.da.SearchCursor(Join_13_8_freq,["SUBID","Frequency"])}

with arcpy.da.UpdateCursor(subLayer,["SUBID","UNIQUE_SUB_COUNT_13_8"]) as upd_cur:
    for upd_row in upd_cur:
        upd_row[1] = search_feats_Unique_Subs_13_8.get(upd_row[0], 0)
        upd_cur.updateRow(upd_row)    
    print("UNIQUE_SUB_COUNT_13_8 Updated")
del upd_cur

RecordCount = arcpy.GetCount_management(SubLayer_pp_trans_GUJoin_UniqueSubs)
count = int(RecordCount.getOutput(0))
print("SubLayer_pp_trans_GUJoin_UniqueSubs count:", count)
################################### UNIQUE SUBS BY VOLTAGE - 46 ########################

arcpy.AddField_management(subLayer, "UNIQUE_SUB_COUNT_46", "SHORT", "", "", "", "", "NULLABLE", "NON_REQUIRED", "")

Join_46_freq = defaultGdbPath + '\Join_46_freq'

# Make feature layer 
arcpy.MakeFeatureLayer_management(SubLayer_pp_trans_GUJoin_UniqueSubs, "JoinUniqueSubs_46", where_46)
RecordCount = arcpy.GetCount_management("JoinUniqueSubs_46")
count = int(RecordCount.getOutput(0))
print("feature layer count:", count)


arcpy.DeleteIdentical_management("JoinUniqueSubs_46", ['SUBID', 'SUB1SUB2', 'VOLTAGE'])
arcpy.Frequency_analysis("JoinUniqueSubs_46", Join_46_freq, ["SUBID"])

RecordCount = arcpy.GetCount_management("JoinUniqueSubs_46")
count = int(RecordCount.getOutput(0))
print("feature layer selected out count:", count)

search_feats_Unique_Subs_46 = {f[0]:f[1] for f in arcpy.da.SearchCursor(Join_46_freq,["SUBID","Frequency"])}

with arcpy.da.UpdateCursor(subLayer,["SUBID","UNIQUE_SUB_COUNT_46"]) as upd_cur:
    for upd_row in upd_cur:
        upd_row[1] = search_feats_Unique_Subs_46.get(upd_row[0], 0)
        upd_cur.updateRow(upd_row)    
    print("UNIQUE_SUB_COUNT_46 Updated")
del upd_cur

RecordCount = arcpy.GetCount_management(SubLayer_pp_trans_GUJoin_UniqueSubs)
count = int(RecordCount.getOutput(0))
print("SubLayer_pp_trans_GUJoin_UniqueSubs count:", count)

######################################### UNIQUE SUBS BY VOLTAGE - 69 ########################

arcpy.AddField_management(subLayer, "UNIQUE_SUB_COUNT_69", "SHORT", "", "", "", "", "NULLABLE", "NON_REQUIRED", "")

Join_69_freq =defaultGdbPath + '\Join_69_freq'

# Make feature layer 
arcpy.MakeFeatureLayer_management(SubLayer_pp_trans_GUJoin_UniqueSubs, "JoinUniqueSubs_69", where_69)
RecordCount = arcpy.GetCount_management("JoinUniqueSubs_69")
count = int(RecordCount.getOutput(0))
print("feature layer count:", count)


arcpy.DeleteIdentical_management("JoinUniqueSubs_69", ['SUBID', 'SUB1SUB2', 'VOLTAGE'])
arcpy.Frequency_analysis("JoinUniqueSubs_69", Join_69_freq, ["SUBID"])

RecordCount = arcpy.GetCount_management("JoinUniqueSubs_69")
count = int(RecordCount.getOutput(0))
print("feature layer selected out count:", count)


search_feats_Unique_Subs_69 = {f[0]:f[1] for f in arcpy.da.SearchCursor(Join_69_freq,["SUBID","Frequency"])}

with arcpy.da.UpdateCursor(subLayer,["SUBID","UNIQUE_SUB_COUNT_69"]) as upd_cur:
    for upd_row in upd_cur:
        upd_row[1] = search_feats_Unique_Subs_69.get(upd_row[0], 0)
        upd_cur.updateRow(upd_row)    
    print("UNIQUE_SUB_COUNT_69 Updated")
del upd_cur

RecordCount = arcpy.GetCount_management(SubLayer_pp_trans_GUJoin_UniqueSubs)
count = int(RecordCount.getOutput(0))
print("SubLayer_pp_trans_GUJoin_UniqueSubs count:", count)

#################################################### UNIQUE SUBS BY VOLTAGE - 115 ########################

arcpy.AddField_management(subLayer, "UNIQUE_SUB_COUNT_115", "SHORT", "", "", "", "", "NULLABLE", "NON_REQUIRED", "")

Join_115_freq =defaultGdbPath + '\Join_115_freq'

# Make feature layer 
arcpy.MakeFeatureLayer_management(SubLayer_pp_trans_GUJoin_UniqueSubs, "JoinUniqueSubs_115", where_115)

RecordCount = arcpy.GetCount_management("JoinUniqueSubs_115")
count = int(RecordCount.getOutput(0))
print("feature layer count:", count)

arcpy.DeleteIdentical_management("JoinUniqueSubs_115", ['SUBID', 'SUB1SUB2', 'VOLTAGE'])
arcpy.Frequency_analysis("JoinUniqueSubs_115", Join_115_freq, ["SUBID"])

RecordCount = arcpy.GetCount_management("JoinUniqueSubs_115")
count = int(RecordCount.getOutput(0))
print("feature layer selected out count:", count)

search_feats_Unique_Subs_115 = {f[0]:f[1] for f in arcpy.da.SearchCursor(Join_115_freq,["SUBID","Frequency"])}

with arcpy.da.UpdateCursor(subLayer,["SUBID","UNIQUE_SUB_COUNT_115"]) as upd_cur:
    for upd_row in upd_cur:
        upd_row[1] = search_feats_Unique_Subs_115.get(upd_row[0], 0)
        upd_cur.updateRow(upd_row)    
    print("UNIQUE_SUB_COUNT_115 Updated")
del upd_cur

RecordCount = arcpy.GetCount_management(SubLayer_pp_trans_GUJoin_UniqueSubs)
count = int(RecordCount.getOutput(0))
print("SubLayer_pp_trans_GUJoin_UniqueSubs count:", count)

#################################################### UNIQUE SUBS BY VOLTAGE - 138 ########################

arcpy.AddField_management(subLayer, "UNIQUE_SUB_COUNT_138", "SHORT", "", "", "", "", "NULLABLE", "NON_REQUIRED", "")

Join_138_freq =defaultGdbPath + '\Join_138_freq'

# Make feature layer 
arcpy.MakeFeatureLayer_management(SubLayer_pp_trans_GUJoin_UniqueSubs, "JoinUniqueSubs_138", where_138)

RecordCount = arcpy.GetCount_management("JoinUniqueSubs_138")
count = int(RecordCount.getOutput(0))
print("feature layer count:", count)

arcpy.DeleteIdentical_management("JoinUniqueSubs_138", ['SUBID', 'SUB1SUB2', 'VOLTAGE'])
arcpy.Frequency_analysis("JoinUniqueSubs_138", Join_138_freq, ["SUBID"])

RecordCount = arcpy.GetCount_management("JoinUniqueSubs_138")
count = int(RecordCount.getOutput(0))
print("feature layer selected out count:", count)

search_feats_Unique_Subs_138 = {f[0]:f[1] for f in arcpy.da.SearchCursor(Join_138_freq,["SUBID","Frequency"])}

with arcpy.da.UpdateCursor(subLayer,["SUBID","UNIQUE_SUB_COUNT_138"]) as upd_cur:
    for upd_row in upd_cur:
        upd_row[1] = search_feats_Unique_Subs_138.get(upd_row[0], 0)
        upd_cur.updateRow(upd_row)    
    print("UNIQUE_SUB_COUNT_138 Updated")
del upd_cur

RecordCount = arcpy.GetCount_management(SubLayer_pp_trans_GUJoin_UniqueSubs)
count = int(RecordCount.getOutput(0))
print("SubLayer_pp_trans_GUJoin_UniqueSubs count:", count)
#################################################### UNIQUE SUBS BY VOLTAGE - 161 ########################

arcpy.AddField_management(subLayer, "UNIQUE_SUB_COUNT_161", "SHORT", "", "", "", "", "NULLABLE", "NON_REQUIRED", "")

Join_161_freq =defaultGdbPath + '\Join_161_freq'

# Make feature layer 
arcpy.MakeFeatureLayer_management(SubLayer_pp_trans_GUJoin_UniqueSubs, "JoinUniqueSubs_161", where_161)

RecordCount = arcpy.GetCount_management("JoinUniqueSubs_161")
count = int(RecordCount.getOutput(0))
print("feature layer count:", count)

arcpy.DeleteIdentical_management("JoinUniqueSubs_161", ['SUBID', 'SUB1SUB2', 'VOLTAGE'])
arcpy.Frequency_analysis("JoinUniqueSubs_161", Join_161_freq, ["SUBID"])

RecordCount = arcpy.GetCount_management("JoinUniqueSubs_161")
count = int(RecordCount.getOutput(0))
print("feature layer selected out count:", count)

search_feats_Unique_Subs_161 = {f[0]:f[1] for f in arcpy.da.SearchCursor(Join_161_freq,["SUBID","Frequency"])}

with arcpy.da.UpdateCursor(subLayer,["SUBID","UNIQUE_SUB_COUNT_161"]) as upd_cur:
    for upd_row in upd_cur:
        upd_row[1] = search_feats_Unique_Subs_161.get(upd_row[0], 0)
        upd_cur.updateRow(upd_row)    
    print("UNIQUE_SUB_COUNT_161 Updated")
del upd_cur

RecordCount = arcpy.GetCount_management(SubLayer_pp_trans_GUJoin_UniqueSubs)
count = int(RecordCount.getOutput(0))
print("SubLayer_pp_trans_GUJoin_UniqueSubs count:", count)

#################################################### UNIQUE SUBS BY VOLTAGE - 230 ########################

arcpy.AddField_management(subLayer, "UNIQUE_SUB_COUNT_230", "SHORT", "", "", "", "", "NULLABLE", "NON_REQUIRED", "")

Join_230_freq =defaultGdbPath + '\Join_230_freq'

# Make feature layer 
arcpy.MakeFeatureLayer_management(SubLayer_pp_trans_GUJoin_UniqueSubs, "JoinUniqueSubs_230", where_230)

RecordCount = arcpy.GetCount_management("JoinUniqueSubs_230")
count = int(RecordCount.getOutput(0))
print("feature layer count:", count)

arcpy.DeleteIdentical_management("JoinUniqueSubs_230", ['SUBID', 'SUB1SUB2', 'VOLTAGE'])
arcpy.Frequency_analysis("JoinUniqueSubs_230", Join_230_freq, ["SUBID"])

RecordCount = arcpy.GetCount_management("JoinUniqueSubs_230")
count = int(RecordCount.getOutput(0))
print("feature layer selected out count:", count)

search_feats_Unique_Subs_230 = {f[0]:f[1] for f in arcpy.da.SearchCursor(Join_230_freq,["SUBID","Frequency"])}

with arcpy.da.UpdateCursor(subLayer,["SUBID","UNIQUE_SUB_COUNT_230"]) as upd_cur:
    for upd_row in upd_cur:
        upd_row[1] = search_feats_Unique_Subs_230.get(upd_row[0], 0)
        upd_cur.updateRow(upd_row)    
    print("UNIQUE_SUB_COUNT_230 Updated")
del upd_cur

RecordCount = arcpy.GetCount_management(SubLayer_pp_trans_GUJoin_UniqueSubs)
count = int(RecordCount.getOutput(0))
print("SubLayer_pp_trans_GUJoin_UniqueSubs count:", count)

########################################## UNIQUE SUBS BY VOLTAGE - 345 ########################

arcpy.AddField_management(subLayer, "UNIQUE_SUB_COUNT_345", "SHORT", "", "", "", "", "NULLABLE", "NON_REQUIRED", "")

Join_345_freq =defaultGdbPath + '\Join_345_freq'

# Make feature layer 
arcpy.MakeFeatureLayer_management(SubLayer_pp_trans_GUJoin_UniqueSubs, "JoinUniqueSubs_345", where_345)

RecordCount = arcpy.GetCount_management("JoinUniqueSubs_345")
count = int(RecordCount.getOutput(0))
print("feature layer count:", count)

arcpy.DeleteIdentical_management("JoinUniqueSubs_345", ['SUBID', 'SUB1SUB2', 'VOLTAGE'])
arcpy.Frequency_analysis("JoinUniqueSubs_345", Join_345_freq, ["SUBID"])

RecordCount = arcpy.GetCount_management("JoinUniqueSubs_345")
count = int(RecordCount.getOutput(0))
print("feature layer selected out count:", count)

search_feats_Unique_Subs_345 = {f[0]:f[1] for f in arcpy.da.SearchCursor(Join_345_freq,["SUBID","Frequency"])}

with arcpy.da.UpdateCursor(subLayer,["SUBID","UNIQUE_SUB_COUNT_345"]) as upd_cur:
    for upd_row in upd_cur:
        upd_row[1] = search_feats_Unique_Subs_345.get(upd_row[0], 0)
        upd_cur.updateRow(upd_row)    
    print("UNIQUE_SUB_COUNT_345 Updated")
del upd_cur

RecordCount = arcpy.GetCount_management(SubLayer_pp_trans_GUJoin_UniqueSubs)
count = int(RecordCount.getOutput(0))
print("SubLayer_pp_trans_GUJoin_UniqueSubs count:", count)

######################################## UNIQUE SUBS BY VOLTAGE - 500 ########################

arcpy.AddField_management(subLayer, "UNIQUE_SUB_COUNT_500", "SHORT", "", "", "", "", "NULLABLE", "NON_REQUIRED", "")

Join_500_freq =defaultGdbPath + '\Join_500_freq'

# Make feature layer 
arcpy.MakeFeatureLayer_management(SubLayer_pp_trans_GUJoin_UniqueSubs, "JoinUniqueSubs_500", where_500)

RecordCount = arcpy.GetCount_management("JoinUniqueSubs_500")
count = int(RecordCount.getOutput(0))
print("feature layer count:", count)

arcpy.DeleteIdentical_management("JoinUniqueSubs_500", ['SUBID', 'SUB1SUB2', 'VOLTAGE'])
arcpy.Frequency_analysis("JoinUniqueSubs_500", Join_500_freq, ["SUBID"])

RecordCount = arcpy.GetCount_management("JoinUniqueSubs_500")
count = int(RecordCount.getOutput(0))
print("feature layer selected out count:", count)

search_feats_Unique_Subs_500 = {f[0]:f[1] for f in arcpy.da.SearchCursor(Join_500_freq,["SUBID","Frequency"])}

with arcpy.da.UpdateCursor(subLayer,["SUBID","UNIQUE_SUB_COUNT_500"]) as upd_cur:
    for upd_row in upd_cur:
        upd_row[1] = search_feats_Unique_Subs_500.get(upd_row[0], 0)
        upd_cur.updateRow(upd_row)    
    print("UNIQUE_SUB_COUNT_500 Updated")
del upd_cur

RecordCount = arcpy.GetCount_management(SubLayer_pp_trans_GUJoin_UniqueSubs)
count = int(RecordCount.getOutput(0))
print("SubLayer_pp_trans_GUJoin_UniqueSubs count:", count)

######################################### UNIQUE SUBS BY VOLTAGE - 1000 ########################

arcpy.AddField_management(subLayer, "UNIQUE_SUB_COUNT_1000", "SHORT", "", "", "", "", "NULLABLE", "NON_REQUIRED", "")

Join_1000_freq =defaultGdbPath + '\Join_1000_freq'

# Make feature layer 
arcpy.MakeFeatureLayer_management(SubLayer_pp_trans_GUJoin_UniqueSubs, "JoinUniqueSubs_1000", where_1000)

RecordCount = arcpy.GetCount_management("JoinUniqueSubs_1000")
count = int(RecordCount.getOutput(0))
print("feature layer count:", count)

arcpy.DeleteIdentical_management("JoinUniqueSubs_1000", ['SUBID', 'SUB1SUB2', 'VOLTAGE'])
arcpy.Frequency_analysis("JoinUniqueSubs_1000", Join_1000_freq, ["SUBID"])

RecordCount = arcpy.GetCount_management("JoinUniqueSubs_1000")
count = int(RecordCount.getOutput(0))
print("feature layer selected out count:", count)

search_feats_Unique_Subs_1000 = {f[0]:f[1] for f in arcpy.da.SearchCursor(Join_1000_freq,["SUBID","Frequency"])}

with arcpy.da.UpdateCursor(subLayer,["SUBID","UNIQUE_SUB_COUNT_1000"]) as upd_cur:
    for upd_row in upd_cur:
        upd_row[1] = search_feats_Unique_Subs_1000.get(upd_row[0], 0)
        upd_cur.updateRow(upd_row)    
    print("UNIQUE_SUB_COUNT_1000 Updated")
del upd_cur

RecordCount = arcpy.GetCount_management(SubLayer_pp_trans_GUJoin_UniqueSubs)
count = int(RecordCount.getOutput(0))
print("SubLayer_pp_trans_GUJoin_UniqueSubs count:", count)

######################################## UNIQUE_SUBS_COUNT ########################

arcpy.AddField_management(subLayer, "UNIQUE_SUBS_COUNT", "SHORT", "", "", "", "", "NULLABLE", "NON_REQUIRED", "")

Join_all_freq =defaultGdbPath + '\Join_all_freq'

# Make feature layer 
arcpy.MakeFeatureLayer_management(SubLayer_pp_trans_GUJoin_UniqueSubs, "JoinUniqueSubs_all",)

RecordCount = arcpy.GetCount_management("JoinUniqueSubs_all")
count = int(RecordCount.getOutput(0))
print("feature layer count:", count)

arcpy.DeleteIdentical_management("JoinUniqueSubs_all", ['LATITUDE', 'LONGITUDE', 'SUB1SUB2'])
arcpy.Frequency_analysis("JoinUniqueSubs_all", Join_all_freq, ["SUBID"])

RecordCount = arcpy.GetCount_management("JoinUniqueSubs_all")
count = int(RecordCount.getOutput(0))
print("feature layer selected out count:", count)

search_feats_Unique_Subs_all = {f[0]:f[1] for f in arcpy.da.SearchCursor(Join_all_freq,["SUBID","Frequency"])}

with arcpy.da.UpdateCursor(subLayer,["SUBID","UNIQUE_SUBS_COUNT"]) as upd_cur:
    for upd_row in upd_cur:
        upd_row[1] = search_feats_Unique_Subs_all.get(upd_row[0], 0)
        upd_cur.updateRow(upd_row)    
    print("UNIQUE_SUBS_COUNT Updated")
del upd_cur

RecordCount = arcpy.GetCount_management(SubLayer_pp_trans_GUJoin_UniqueSubs)
count = int(RecordCount.getOutput(0))
print("SubLayer_pp_trans_GUJoin_UniqueSubs count:", count)

################################################# UNIQUE VOLTAGE COUNT ###################################

arcpy.AddField_management(subLayer, "UNIQUE_VOLTAGE_COUNT", "SHORT", "", "", "", "", "NULLABLE", "NON_REQUIRED", "")

Join_voltage_freq = defaultGdbPath + '\Join_voltage_freq'

# Make feature layer 
arcpy.MakeFeatureLayer_management(SubLayer_pp_trans_GUJoin_UniqueSubs, "JoinUniqueVoltage",)

RecordCount = arcpy.GetCount_management("JoinUniqueVoltage")
count = int(RecordCount.getOutput(0))
print("feature layer count:", count)

arcpy.DeleteIdentical_management("JoinUniqueVoltage", ['VOLTAGE','SUBID'])
arcpy.Frequency_analysis("JoinUniqueVoltage", Join_voltage_freq, ["SUBID"])

RecordCount = arcpy.GetCount_management("JoinUniqueVoltage")
count = int(RecordCount.getOutput(0))
print("feature layer selected out count:", count)

search_feats_Unique_Voltage = {f[0]:f[1] for f in arcpy.da.SearchCursor(Join_voltage_freq,["SUBID","Frequency"])}

with arcpy.da.UpdateCursor(subLayer,["SUBID","UNIQUE_VOLTAGE_COUNT"]) as upd_cur:
    for upd_row in upd_cur:
        upd_row[1] = search_feats_Unique_Voltage.get(upd_row[0], 0)
        upd_cur.updateRow(upd_row)    
    print("UNIQUE_VOLTAGE_COUNT Updated")
del upd_cur

RecordCount = arcpy.GetCount_management(SubLayer_pp_trans_GUJoin_UniqueSubs)
count = int(RecordCount.getOutput(0))
print("SubLayer_pp_trans_GUJoin_UniqueSubs count:", count)


end = time.time()
print((end - start)/60)

#if __name__ == '__main__':
#    entry()‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍
0 Kudos
DuncanHornby
MVP Frequent Contributor

The Make FeatureLayer creates a layer object, this is an "in memory" pointer to the featureclass, so, as you have, you can create many layer objects (or "pointers") to the same featureclass but call them all different names. At no point are you creating a copy of the dataset called something else.

As you create your layer object you are applying a where clause (which you never actually show in your code) and that is filtering the data. That explains the differing counts for your lines  print("feature layer count:", count). Each time you create a new layer object from the SAME featureclass you are apply a different where clause which ultimately returns a different count.

Maybe you can add at each section a Copy Features, you copy from your master FeatureClass 

SubLayer_pp_trans_GUJoin_UniqueSubs to a temporary featureclass and it's from that temporary featureclass you create you layer object and do you deletion. At the next stage you overwrite your temporary featureclass with Copy Features.

0 Kudos
TessOldemeyer
Occasional Contributor

Oh, the where clause makes sense (oops, been staring at the script too long ). The clauses are defined in a previous section of the code. Copy Features might just be the trick. I'll give it a go. Thanks a ton! 

What would be the best way to create a temporary featureclass? 

Would using a table view or in-memory workspace be a good option for minimizing the time this may take?

0 Kudos
DuncanHornby
MVP Frequent Contributor

Use Copy Features tool, copying to IN_MEMORY workspace would be faster, but you may need to tweak your where clause as it uses different field delimiters. Just something to be aware of.

0 Kudos