Select to view content in your preferred language

Label auto filler with excel query - Need assistance

177
1
06-10-2024 01:30 PM
Joseph_Nolan
New Contributor

Background info: I produce a bunch of maps and the labels have to look consistent between the various projects. Each project has an unique list of analytes and each analyte must be presented and any analytes above a certain risk value must be in bold. I have been working with a similar script for a while, however, the current version requires the entry of each analyte and modifying the code to accommodate for the precise number of analytes. This can be very time consuming when you get 25+ analytes to list. Additionally, colleagues are not python savvy so this will benefit them as well. 

Goal of the code: To generate labels for point features through the Label Properties tool utilizing python. To query a .shp with values entered in fields. The script should query the field for the field name and return the value of the corresponding row and field. Thus allowing the script to be unmodified based on the fields. The only modification to the code should be the shapefile location or shapefile name. 

Problem with the code(s): Currently I have 18 versions all with varying levels of success. I will focus on two versions (below) of the code as they represent the end members of the problems faced with other versions lying somewhere along the spectrum of these two problems. The resulting outcome of these two scripts are commented in the script themselves.

 

 

#script 1
import pandas as pd
import inspect
import arcpy

RSKPath = "path to excel file"
ShapePath = "path to shp file"
label = ""
def FindLabel ():
    field_names = [f.name for f in arcpy.ListFields(ShapePath)] 
    fields = arcpy.ListFields(ShapePath)#attempts to produce a list of fields entries across the row
    skip = (0,1)
    for i in range(len(field_names)):
        if i in list(skip): #skips the OBJECTID and SHAPE fields default in Arc
            continue
        TargetAnalyte = field_names[i]
        try: #confirms values from field are floats in order to value compare
            a = float(fields[i]) if fields[i].isnumeric else str(fields[i]) 
        except (ValueError,AttributeError): #catches <null> and other typed text
            a = str(fields[i])
        df = pd.read_excel(RSKPath) #excel file info
        rsk = df.loc[df['Analyte'] == TargetAnalyte, 'RSK_Value'].tolist() #query excel for TargetAnalyte
        try: #checks float against Risk value
            a = str("<bol>" + str(a) + "</bol>") if a >= rsk else str(a) #22
        except TypeError: #skips if not float 
            a = str(a)
        
        label += field_names[i] + '= ' + a + '\n' #concatenates label 
       
    return label
"""
Output: Each point has an unique label with differing values in each label entry.
Problem: each value is always <geoprocessing describe field object object...>. 
If this script stopped giving <geoprocessing...> values it appears it would work. """
#script 2
import pandas as pd
import inspect
import arcpy

RSKPath = "path to excel"
ShapePath = "path to shp"
label = ' '
def FindLabel ():
    rows = arcpy.SearchCursor(ShapePath)
    field_names = [f.name for f in arcpy.ListFields(ShapePath)]
    fields = arcpy.ListFields(ShapePath)
    for row in rows:
        for field in fields:
            if field.name == 'OBJECTID' or field.name == 'Shape':#skips first 2 fields
                continue            
            TargetAnalyte = field.name
            a = row.getValue(field.name)
            try:#converts a to float like script 1
                a = float(a) if a.isnumeric else str(a) 
            except (ValueError,AttributeError): #catches nulls and typed text
                a = str(a)
            df = pd.read_excel(RSKPath)
            rsk = df.loc[df['Analyte'] == TargetAnalyte, 'RSK_Value'].tolist()
            
            try: #bolds value above certain value
                a = str("<bol>" + str(a) + "</bol>") if a >= rsk else str(a) 
            except TypeError: 
                a = str(a)
            label += TargetAnalyte + '= ' + a + '\n'
        
    return label
"""
Output: Each field is querried properly and corresponding is displayed correctly. 
Problem: All labels have all the data for all the labels stacked and ordered as iterated. 

Example all labels say:
ProbeID = Test1
Lead = 5.0
PCE = 2.1
ProbeID = Test2
Lead = 3.4
PCE = 5.1
ProbeID = Test3....
"""

 

 

All other versions of the script range between these two end results ranging from labels with identical non-unique data, parts of the label missing, or <geoprocessing....> and a few time all of the above.

I have tried various solutions across this Esri Community forum or Stack Exchange. These solutions include getValue, Describe, ListFields,  SearchCursor (including da.SearchCursor), GetParameters, and others I have probably lost track of. All of the solutions took the script in a different direction, not the correct direction, but different. 

I would appreciate any insight on getting either script to produce the desire outcome. I hope I have provided enough information, if not I apologize in advance and will modify the post to provide needed information. 

0 Kudos
1 Reply
HaydenWelch
Occasional Contributor II

Where is this code going to be running? If you are running it standalone, a variant of script 2 should work, but you're generating all labels at once, so you'll need to create a label *per feature*. As it's written now you are creating a single label for all features that are found by the Search Cursor:

 

#script 2.1
import arcpy
import pandas as pd
import os

def find_label(shape_path: os.PathLike, rsk_path: os.PathLike) -> str:
    if not arcpy.Exists(shape_path):
        raise FileNotFoundError(f"ShapeFile: {shape_path} not found")
    if not os.path.exists(rsk_path):
        raise FileNotFoundError(f"RSK File: {rsk_path} not found")
    
    field_names = [f.name for f in arcpy.ListFields(shape_path)][2:] # skip first 2 with slice
    rsk = pd.read_excel(rsk_path) # Don't read this in a loop, read it once
    
    labels = []
    with arcpy.da.SearchCursor(shape_path, field_names) as cursor:
        for row in cursor:
            label = ''
            for field_name, value in zip(field_names, row):
                rsk = rsk.loc[rsk['Analyte'] == field_name, 'RSK_Value'].tolist()
                
                if isinstance(value, (int, float)):
                    value = f"<bol>{value}</bol>" if value >= rsk else value
                label += f"{field_name} = {value}\n"
            labels.append(label)
    return labels
                

 

 

If you want these labels to be written to the shapefile itself, that can be done easily by using an UpdateCursor:

 

#script 2.2
import arcpy
import pandas as pd
import os

def find_label(shape_path: os.PathLike, rsk_path: os.PathLike) -> None:
    if not arcpy.Exists(shape_path):
        raise FileNotFoundError(f"ShapeFile: {shape_path} not found")
    if not os.path.exists(rsk_path):
        raise FileNotFoundError(f"RSK File: {rsk_path} not found")
    
    field_names = [f.name for f in arcpy.ListFields(shape_path)][2:] # skip first 2 with slice
    rsk = pd.read_excel(rsk_path) # Don't read this in a loop, read it once
    
    # Create a Label field if it doesn't exist
    if 'Label' not in field_names:
        arcpy.AddField_management(shape_path, 'Label', 'TEXT', field_length=1024) # Adjust this if the labels are too long
        field_names.append('Label')
    
    with arcpy.da.UpdateCursor(shape_path, field_names) as cursor:
        for row in cursor:
            # Create a dictionary of field names and values so they can be accessed by field name
            row = dict(zip(field_names, row))
            # Create a label string
            label = ''
            # Iterate over the fields and values
            for field_name, value in row.items():
                # Get the RSK value for the field name
                rsk = rsk.loc[rsk['Analyte'] == field_name, 'RSK_Value'].tolist()
                # Bold RSK values
                if isinstance(value, (int, float)):
                    value = f"<bol>{value}</bol>" if value >= rsk else value
                label += f"{field_name} = {value}\n"
                
            # Update the Label field if it doesn't match the label
            if row['Label'] != label:
                row["Label"] = label
                cursor.updateRow(list(row.values()))
    return

 

0 Kudos