Calculate value in a field of a table in hosted Feature layer using Python

Md_TauhidulIslam

Hi, 
I am trying to write a python script which will calculate a field data from one table to another table in a hosted feature service which is hosted in AGOL. The both table has 90K+ records and has a joinID field. which I use as query to calculate the data. I am using below code - 

 

 

 

from arcgis import *
username = "user"  
password = "password"  

gis = GIS("https://www.arcgis.com", username=username, password=password)
# Get the feature service item ID
feature_service_item_id = "item_id"
# Access the feature service item
feature_service_item = gis.content.get(feature_service_item_id)

feature_service_tables = feature_service_item.tables


feature_service_layers = feature_service_item.layers

data_table = None
feat_layer = None
joinTable = None

for table in feature_service_tables:
    if table.properties.name == 'feat_DataTable':
        data_table = table
        print("Table found: {}".format(data_table.properties.name))
    elif table.properties.name == 'feat_linear_interpolation':
        joinTable = table
        print("Table found: {}".format(joinTable.properties.name))

for layer in feature_service_item.layers:
    if layer.properties.name == 'featurelayer':
        feat_layer = layer
        print("Layer found: {}".format(feat_layer.properties.name))


query = "year = 1995"
result_set = joinTable.query(where=query,out_fields = ["joinID,year,F01_human_field"])
for feature in result_set.features:
    joinID = feature.attributes["joinID"]
    year = feature.attributes["year"]
    dataValue = feature.attributes["F01_human_field"]
    innerquery = "joinID = "+joinID
    data_table.calculate(where=innerquery,
                         calc_expression={"field": "Data_Value", "value": dataValue})
    data_table.calculate(where=innerquery,
                         calc_expression={"field": "year", "value": year})

 

 

 

 

the code is working but the issue is it is taking 25 minutes to calculate 3000 features which is impractical. is there any better approach to make the calculation efficient? Any code suggestions would be highly appreciated. I haven't find any method in API reference which batch calculate the whole lot in one go. 

Thanks is advance.

Regards,
Tauhid

Md_TauhidulIslam

Hi Josh,

I have tried to adopt a code by your suggestion it looks below - 

 

 

from arcgis import GIS
import pandas as pd

portal_url = "portalarcgis"
username = "user"  
password = "pass"  
gis = GIS(portal_url, username, password)

service_name = 'service_name'

items = gis.content.search(query=service_name, item_type='Feature Service')

if len(items) == 0:
    print(f"No feature service named '{service_name}' found.")
    exit()
	
feature_service = items[0]

tables = feature_service.tables

data_table = None
join_table = None

for table in feature_service_tables:
    if table.properties.name == 'DataTable':
        data_table = table
        print("Table found: {}".format(data_table.properties.name))
    elif table.properties.name == 'linear_interpolation_20230612':
        join_table = table
        print("Table found: {}".format(join_table.properties.name))

if data_table is not None and join_table is not None:
    data_table_df = county_data_table.query(as_df=True)
    join_table_df = join_table.query(as_df=True, out_fields = ["joinid, year, farm_kg_sqkm"])
    join_table_df = join_table_df.rename(columns={'year': 'year_join'})
	join_df = county_data_table_df.merge(join_table_df, on='joinid', how='inner')
	try:
        join_df['year'] = join_df['year_join']
        join_df['data_value'] = join_df['farm_kg_sqkm']

        data_table_df['year'] = join_df['year_join']
        data_table_df['data_value'] = join_df['data_value']
        updates = data_table_df.to_dict('records')

        # Update the feature service table
        data_table.edit_features(updates=updates)
        print("Feature Service table Updated!!!")
    except Exception as e:
        print(str(e))

 

 

 

I tried to get the tables directly but it was giving me none exception. 

so this code does what I want within the dataframe level  but when it goes to 

 

 

data_table.edit_features(updates=updates)

 

 

 

it works for about the a minute and prints the message that the Feature service table updated. Actually the calculation didn't happened. in the api documentation it says if the layer is more 250+ then should avoid the edit_features method. I tried to truncate first then add the features but no luck. is there any other method should I use? 

 

Thanks in Advance.

 

Regards,

Tauhidul Islam

GIS Consultant

TT Coffey Australia