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

ScottLehto4 — Tue, 21 Mar 2023 20:28:45 GMT

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)

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

Clubdebambos — Fri, 24 Mar 2023 09:24:18 GMT

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))

topic A Many-to-One DataFrame Merge then Get a Value from Main Table in ArcGIS API for Python Questions

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

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