The ArcGIS Join Field tool is handy but extremely slow. This is a Python implementation that joins one field very fast using a Python dictionary.
Broad procedure here is to:
def JoinOneField(tbl, key1, jtbl, key2, jfield, ftype="LONG", fprec="", flen="", fname=""): """Joins one field from another table (like Join Field, but fast, and field values are not modified for no-matches)! tbl - source table key1 - source table key field jtbl - join table key2 - join field (must be unique in jtbl) jfield - field to copy over ftype - field type fprec - field precision flen - field length (for ftype="TEXT") fname - field name (if different from jfield) The function returns a two-integer tuple of number of matches and no-matches. ** The field data values are not changed for no-matches ** JoinOneField(srctbl, key1, jtbl, key2, NHDPlusID", "DOUBLE")""" # # Curtis Price, cprice@usgs.gov # # by default the join field and destination field are the same if not fname: fname = jfield # for shapefile output make sure we have enough precision for double if ftype.upper() == "DOUBLE" and not fprec: fprec = 15 if not arcpy.ListFields(tbl, key1): raise Exception("Key Field {} not found in {}".format(key1, tbl)) if not arcpy.ListFields(jtbl, key2): raise Exception("Key Field {} not found in {}".format(jfield, jtbl)) if not arcpy.ListFields(jtbl, jfield): raise Exception("Join Field {} not found in {}".format(jfield, jtbl)) try: arcpy.AddField_management(tbl, fname, ftype, fprec, "", flen) except: pass # load data into dictionary jdict = {} k = 0 first_dupe = True with arcpy.da.SearchCursor(jtbl, [key2, jfield]) as rows: for row in rows: key, val = row if first_dupe and jdict.has_key(key): # display a single warning message for one to many joins print("Key field {} not unique in {}".format(key2, jtbl)) first_dupe = False else: jdict[key] = val # copy data values to output table num_match = 0 num_nomatch = 0 with arcpy.da.UpdateCursor(tbl, [key1, fname]) as rows: for row in rows: try: key = row[0] row[1] = jdict[key] num_match += 1 rows.updateRow(row) except KeyError: # no match in dictionary (join no-match) # do nothing -- do not change existing value num_nomatch += 1 ## print ("{}:{}".format(key, "no match")) except RuntimeError as msg: if str(msg).find("The value type is incompatible") != -1: raise Exception( "Invalid value for field {}: {!r}".format( jfield, jdict[key])) else: # some other error raise # return number of rows matched and not matched ## GPMsg("JoinOneField: {} rows, {} matches, {} no-matches".format( ## num_match + num_nomatch, num_match, num_nomatch)) return num_match, num_nomatch
1 Comment
Unknown User (cprice@usgs.gov)
Your comments look good Roland, thanks for adding the description.