AnsweredAssumed Answered

Updating 2 tables related by 3 ID fields

Question asked by RTreecare on Feb 26, 2015
Latest reply on Mar 3, 2015 by RTreecare

I'm having a little trouble setting up this script. Here are the bullet points.

  • 2 Tables have the same three fields. [SiteID], [EasyID], [FeatureID]. For simplicity, lets pretend these are the only fields (besides OID).
  • [FeatureID] is a unique integer for each feature created in Table 1.
  • [SiteID] is a non-unique integer and groups features by location.
  • [EasyID] is a string (typically a number) unique within the same [SiteID], but not unique in the field.
  • Table 1 - The master table. New features have all three ID fields populated.
  • Table 2 - New features always have [SiteID], sometimes [EasyID], and never a [FeatureID].
  • Table 2 - Features with [SiteID] and [EasyID], and no [FeatureID] may have a matching [SiteID] and [EasyID] in Table 1. If so, update [FeatureID] in Table 2.
  • Table 2 - Features missing [EasyID] need to be assigned the next number for that [SiteID] in either Table 1 or 2. If current EasyID's for a site are '1', '3', '4A', '6-7','S', and '55', new features would be '2', '4', '5', '6', '7', '8'....etc.
  • Table 2 - New features given a [FeatureID] when inserted into Table 1. The features are then updated in Table 2.
  • Table 1 - Finally, any features with a [SiteID] in Table 2, but the [FeatureID] and [EasyID] are not in Table 2, are inserted into Table 2
  • At the end of the script both tables should match.

 

I've looked through using model builder with no luck. With python dictionaries and search cursors i'm getting stuck trying to join against [SiteID] and [EasyID] at the same time. I am also don't know how to return the dictionaries with just integer EasyID's and loop through updating the next smallest integer.

Here is what I've got so far. Much of it stems from what I read from Richard Fairhurst's post Turbo Charging Data Manipulation with Python Cursors and Dictionaries

 

 

import arcpy

#Tables
T1 = r"C:\Python\Scratch.gdb\Table1"
T2 = r"C:\Python\Scratch.gdb\Table2"
fields = ["FeatureID", "SiteID", "EasyID"]

# Get FeatureID dictionaries for each Table
T1Dict = {r[0]:(r[0:]) for r in arcpy.da.SearchCursor(T1, fields)}
T2Dict = {r[0]:(r[0:]) for r in arcpy.da.SearchCursor(T2, fields)}

# Get SiteID+EasyID dictionaries for each Table
T1ConcatDict = {str(r[1]) + "," + str(r[2]):(r[0]) for r in arcpy.da.SearchCursor(T1, fields)}
T2ConcatDict = {str(r[1]) + "," + str(r[2]):(r[0]) for r in arcpy.da.SearchCursor(T2, fields)}

#First, If T2.FeatureID is Null but T2.EasyID and T2.SiteID are in T1, Update T2.FeatureID
with arcpy.da.UpdateCursor(T2, fields) as updateRows:  
    for updateRow in updateRows:  
        # store the Join value by combining 3 field values of the row being updated in a keyValue variable  
        keyValue = str(updateRow[1]) + "," + str(updateRow[2])  
        # verify that the keyValue is in the Dictionary  
        if keyValue in T1ConcatDict & updateRow[0] is None & updateRow[1] is not None:  
            # transfer the value stored under the keyValue from the dictionary to the updated field: FeatureID.  
            updateRow[0] = T1ConcatDict[keyValue][0]  
            updateRows.updateRow(updateRow)  

#Rebuild Dictionary if it is needed again
T2ConcatDict = {str(r[1]) + "," + str(r[2]):(r[0]) for r in arcpy.da.SearchCursor(T2, fields)}
T2Dict = {r[0]:(r[0:]) for r in arcpy.da.SearchCursor(T2, fields)}


'''
#Get Max(EasyID) within SiteID
NumberList = []
for value in T1Dict[2]:
    try:
        NumberList.append(int(value))
    except ValueError:
        continue
T1EasyNumberDict = [s[2] for s in T1Dict[2] if s.isdigit()]
T1MaxEasyDict = max(T1EasyNumberDict)
'''


#Second, If T2.FeatureID and T2.EasyID are Null, Update T2.EasyID with next smallest number (as string) in either T1 or T2 for the specific SiteID
with arcpy.da.UpdateCursor(T2, fields) as updateRows:  
    for updateRow in updateRows:  
        # store the Join value by combining 3 field values of the row being updated in a keyValue variable  
        keyValue = str(updateRow[1]) + "," + str(updateRow[2])  
        # verify that the keyValue is in the Dictionary  
        if keyValue in T1Dict & updateRow[0] is None & updateRow[1] is None:  
            # transfer the value stored under the keyValue from the dictionary to the updated field.  
            # Perhaps retrieve max int occurs here?
  updateRow[2] = max(T1Dict[keyValue][2], T2Dict[keyValue[2]) 
            updateRows.updateRow(updateRow)  


#Third, Insert into T1 if T2.SiteID is not null and T2.EasyID is not null


#Forth, Update T2.FeatureID with T1.FeatureID from previous insert where T2.SiteID=T1.SiteID and T2.EasyID=T1.EasyID


#Lastly, Insert any T1 features into T2 where T1.EasyID not in (Select EasyID from T2 where T2.SiteID = T1.SiteID) and T1.FeatureID  not in (Select FeatureID from T2)



Thank you for any advise.

 

Edit: Here is a sample of sites with 2 explanatory columns

 

T1.FeatureIDT2.FeatureIDT1.SiteIDT2.SiteIDT1.EasyIDT2.EasyIDStatusResult
35858935858913623813623811Existing T1 and T2 FeatureNo Change
35859035859013623813623822Existing T1 and T2 FeatureNo Change
35859435859413623813623844Existing T1 and T2 FeatureNo Change
65253865253813623813623855Existing T1 and T2 FeatureNo Change
48602848602813623813623888Existing T1 and T2 FeatureNo Change
48602948602913623813623899Existing T1 and T2 FeatureNo Change
5253005253001362381362383434Existing T1 and T2 FeatureNo Change
574802574802136238136238998998Existing T1 and T2 FeatureNo Change
670911136238300New T1 Feature(Step 6) Inserted Into T2
4938401362389996New T1 Feature(Step 6) Inserted Into T2
4938391362389997New T1 Feature(Step 6) Inserted Into T2
4938311362389999New T1 Feature(Step 6) Inserted Into T2
696019136238105-106New T1 Feature(Step 6) Inserted Into T2
6960371362389999NNew T1 Feature(Step 6) Inserted Into T2
696014136238Area1New T1 Feature(Step 6) Inserted Into T2
670910136238NNew T1 Feature(Step 6) Inserted Into T2
580636136238N30cNew T1 Feature(Step 6) Inserted Into T2
360401136401ACExiting T1 Feature(Skip) Not Inserted since no T2.SiteID match
360402136401SPExiting T1 Feature(Skip) Not Inserted since no T2.SiteID match
360510136427Area 1Exiting T1 Feature(Skip) Not Inserted since no T2.SiteID match
36265313663515Exiting T1 Feature(Skip) Not Inserted since no T2.SiteID match
36294336294313669813669811Existing T1 and T2 FeatureNo Change
36294436294413669813669822Existing T1 and T2 FeatureNo Change
36294536294513669813669833Existing T1 and T2 FeatureNo Change
36294636294613669813669844Existing T1 and T2 FeatureNo Change
36294736294713669813669855Existing T1 and T2 FeatureNo Change
36295036295013669813669811C11CExisting T1 and T2 FeatureNo Change
3629483629481366981366988New T2 Feature, Exists in T1(Step 5) Update T2.EasyID
3629493629491366981366989New T2 Feature, Exists in T1(Step 5) Update T2.EasyID
3629511366981366981515New T2 Feature, Exists in T1(Step 1) Update T2.FeatureID
3629541366981366981616New T2 Feature, Exists in T1(Step 1) Update T2.FeatureID
3629551366981366981717New T2 Feature, Exists in T1(Step 1) Update T2.FeatureID
3629561366981366981818New T2 Feature, Exists in T1(Step 1) Update T2.FeatureID
3629571366981366981919New T2 Feature, Exists in T1(Step 1) Update T2.FeatureID
13669820New T2 Feature(Step 3,4) Insterted into T1, Update T2.FeatureID
13669821New T2 Feature(Step 3,4) Insterted into T1, Update T2.FeatureID
13669822New T2 Feature(Step 3,4) Insterted into T1, Update T2.FeatureID
13669825New T2 Feature(Step 3,4) Insterted into T1, Update T2.FeatureID
136698New T2 Feature(Step 2,3,4) Get next lowest integer for T2.EasyID -> 6
136698New T2 Feature(Step 2,3,4) Get next lowest integer for T2.EasyID -> 7
136698New T2 Feature(Step 2,3,4) Get next lowest integer for T2.EasyID -> 10
136698New T2 Feature(Step 2,3,4) Get next lowest integer for T2.EasyID -> 11

Attachments

Outcomes