I want to join the attribute tables of two feature classes in ArcGIS and populate the fields from one table to another. This is a one to many join and there are duplicate rows of data in the join table that match the source table. If a row of data has not already been populated by the join fields, it will populate that record. All subsequent record matches will be inserted as new rows.

Data process diagram:

Data process diagram

There is an existing answer to a similar question here: Joining one-to-many using ArcPy?

The solution I am currently working on uses dictionaries and cursors to process the data (as suggested by PolyGeo). I cannot simply create a dictionary on ID1 as this is not a unique field so rows with dupe ID1's will be omitted. I have instead created a dictionary which stores both ID1 and ID2 together as the dictionary keys and the remaining data fields as the dictionary values. This way I can have unique keys for the dictionary. Then, I iterate through each row of the source table and find records in the join table, create a list of 'matched' records, then either populate an existing record with empty join data or insert a new row.

It would be great if someone could share a hint or let me know if I am on the right track with my approach.

import arcpy

SourceFC = r'C:\Test_Data.gdb\SourceFC'

JoinFC = r'C:\Test_Data.gdb\JoinFC'
JoinFCfields = ["ID1","ID2","data1","data2","data3"]
JoinFC_dict = {(r[:1]): r[2:]) for r in arcpy.da.SearchCursor("JoinFC",JoinFCfields)}
# Dictionary keys = (ID1, ID2)
# Dictionary values = (data1, data2, data3)

with arcpy.da.InsertCursor("SourceFC") as insertcursor:
    with arcpy.da.UpdateCursor("SourceFC") as updatecursor:
        for SourceFC_row in updatecursor:

            # Pull records from Join table that match ID1 in Source table
            joinmatch_list = [(k,v) for k, v in JoinFC_dict if k[0] == SourceFC_row[0]]

            for joinmatch in joinmatch_list:
                # Updating source table field row with join table data
                SourceFC_row[1],SourceFC_row[2],SourceFC_row[3],SourceFC_row[4] = joinmatch[0][1],joinmatch[1][0],joinmatch[1][1],joinmatch[1][2]   

                if SourceFC_row[1] is None:
                    # If Source table row has not already been populated, populate with join data

                    # For all subsequent 'many' joins, insert a new row with source + join data
  • Welcome to GIS SE! As a new user be sure to take the Tour. For questions that involve code we ask that you show us where you are stuck with your own code (not just pseudo-code) by including a code snippet in your question. I think you should look for a Q&A here that uses dictionary comprehension on a SearchCursor followed by an InsertCursor that uses that dictionary. – PolyGeo Jan 31 '18 at 22:49
  • AddJoin supports a one-to-many join when your data is in a file geodatabase, you must save the data to a new featureclass. The TOC Join also supports one-to-many join. – klewis Jan 31 '18 at 23:57
  • @klewis Both my source and join tables have around 1 million records so I think I need to use cursors for performance. – mfn Feb 1 '18 at 0:40
  • @PolyGeo I don't think I can use a dictionary in my case because I have records with duplicate ID's, and dictionaries do not support duplicate keys....correct me if I'm wrong! – mfn Feb 1 '18 at 1:16
  • 1
    If it is a one-to-many relationship then you must have a unique key in your source feature class. From that the values in your dictionary can be lists (providing the many side of the relationship). – PolyGeo Feb 1 '18 at 2:01

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.