A Many-to-One DataFrame Merge then Get a Value from Main Table

ScottLehto4

Hi,

    I feel what I am attempting to do is pretty straightforward, but I cannot find an example of it.

     I have a related inspections table that I am looking to join to the parent table using a GUID/GLOBALID. From there I want to get a string value from a field (ID) from the parent table record and paste it into any related records in the inspections table. 

     The code I have accomplishes this (partly) but only for one related record in the inspections table. If there are multiple inspections for that single feature, the remaining related records will have a null value for ID.

     Can someone point me the right direction? Thank you!

     Here is What I Have so Far:😗

 

 

 

 

 

from arcgis import GIS

from arcgis.features import FeatureLayer

import pandas as pd

from datetime import datetime, timedelta

import time

token = GIS(f'')

#INSPECTION TABLE - RELATEDTABLE 

inspections_url =''

inspections_lyr = FeatureLayer(inspections_url)

inspections_sdf = pd.DataFrame.spatial.from_layer(inspections_lyr)

inspections_fset = inspections_lyr.query(where="ASSETID=null", out_fields='PARENT_GLOBALID,ASSETID')

inspection_features = inspections_fset.features

#get all inspection features

workOrder_fset = inspections_lyr.query()

all_features = workOrder_fset.features

#MAIN TABLE INFORMATION

valve_url = ''

valve_lyr = FeatureLayer(valve_url)

valve_sdf = pd.DataFrame.spatial.from_layer(valve_lyr)

valve_fset = valve_lyr.query()

valve_features = valve_fset.features

 

#MERGE TABLES

overlap_rows=pd.merge(left = inspections_sdf, right = valve_sdf, how='inner', left_on='PARENT_GLOBALID',right_on='GlobalID')

 

valve_updates = valve_fset.features

valve_updates.reverse()

for g in overlap_rows['PARENT_GLOBALID']:

        inspection_feature= [f for f in all_features if f.attributes['PARENT_GLOBALID'] == g][0]

        valve_feature = [f for f in valve_features if f.attributes['GlobalID'] == g][0]

        workOrder_edit = inspection_feature

        approval_edit = valve_feature

        workOrder_edit.attributes['ASSETID'] = approval_edit.attributes['ASSETID']

        workOrder_edit.attributes['TestingField'] = approval_edit.attributes['VALVETYPE']

        update_result = inspections_lyr.edit_features(updates=[workOrder_edit])

        time.sleep(5)

 

      

   

Clubdebambos

Hi @ScottLehto4 

You can achieve this using a Python dictionary. Two examples in the code commented below, one for a single attribute and one for multiple attributes.

 

from arcgis import GIS
from arcgis.features import FeatureLayer

## connect to agol/portal
agol = GIS("home")

## the parent table - contains unique GlobalID
valve_url = ""
valve_lyr = FeatureLayer(valve_url)

## the related table - contain many related records to parent table
inspections_url = ""
inspections_lyr = FeatureLayer(inspections_url)

## a dictionary to store the glabalid as key, and attributes as the value
valve_dict = {}

################################################################################
## eg 1 - update one field

## query the valve dataset and populate the dictionary
valve_fset = valve_lyr.query()

## iterate through features and add the GlobalID as key and ASSETID as value
for f in valve_fset:
    valve_dict[f.attributes["GlobalID"]] = f.attributes["ASSETID"]

## update the related table
inspections_fset = inspections_lyr.query()

## for each feature in the related table
for f in inspections_fset:
    pguid = f.attributes["PARENT_GLOBALID"]
    ## if the PARENT_GLOBALID is a key in the dictionary
    if pguid in valve_dict:
        ## the field you want to update in related table
        f.attributes["ASSETID"] = valve_dict[pguid]

## print to identify success/failures with update
print(inspections_lyr.edit_features(updates=inspections_fset))

################################################################################
## eg 2 - update multiple fields

## query the valve dataset and populate the dictionary
valve_fset = valve_lyr.query()

## iterate through features and add the GlobalID as key and a list containing ASSETID and VALVETYPE as the value
for f in valve_fset:
    valve_dict[f.attributes["GlobalID"]] = [f.attributes["ASSETID"], f.attributes["VALVETYPE"]]

## update the related table
inspections_fset = inspections_lyr.query()

## for each feature in the related table
for f in inspections_fset:
    pguid = f.attributes["PARENT_GLOBALID"]
    ## if the PARENT_GLOBALID is a key in the dictionary
    if pguid in valve_dict:
        ## the fields you want to update in related table
        ## get first value in list from dictionary entry
        f.attributes["ASSETID"] = valve_dict[pguid][0]
        ## get second value in list from dictionary entry
        f.attributes["VALVETYPE"] = valve_dict[pguid][1]

## print to identify success/failures
print(inspections_lyr.edit_features(updates=inspections_fset))