Calculate field with a variable attribute

1929
20
Jump to solution
03-25-2018 07:06 PM
CommericalAnalytics
New Contributor II

Hello All,

I have a python script which creates a join between two layers.

I am then trying to use calculate field to take the data from one column (DemandWeight) and adding it to another column. However, the other column is made up from a variable so cannot be hard coded. For example, I have a variable called weightedvalue made up from attributes from another table. So after the join I have NetworkLocations.weightedvalue. But the message I get is that is not an actual column name. it is looking for the actual enbedded coIumn name. I also tried NetworkLocations.%weightedvalue% but does not work either. The weighedvalue changes as per a loop.

Any ideas would be appreciated

Tags (1)
0 Kudos
1 Solution

Accepted Solutions
XanderBakker
Esri Esteemed Contributor

I just did a test with the "data" you provided. I had to read it into a fgdb to mimic the situation that you have and change the schema, since things get altered when you export the data to a text and csv file. Next time please provide the data in its original format.

See the code below that I used:

def main():
    import arcpy

    # define input tables and fields
    tbl_source = r'C:\GeoNet\CommericalAnalytics\data.gdb\FacilitiesOutput'
    fld_source_key = 'Name'
    fld_source_val = 'DemandWeight'

    # define output tables and fields
    tbl_destination = 'C:\GeoNet\CommericalAnalytics\data.gdb\ABN'
    fld_dest_key = 'BSB_Text'
    fld_dest_val = 'Value'

    # create dictionary of input
    flds = (fld_source_key, fld_source_val)
    dct_source = {r[0]: r[1] for r in arcpy.da.SearchCursor(tbl_source, flds)}

    # update output
    flds = (fld_dest_key, fld_dest_val)
    with arcpy.da.UpdateCursor(tbl_destination, flds) as curs:
        for row in curs:
            dest_key = row[0]
            if dest_key in dct_source:
                row[1] = dct_source[dest_key]
                curs.updateRow(row)


if __name__ == '__main__':
    main()‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

Table get's updated, although some BSB_Text values are missing in the FacilitiesOutput table.

In the ABN table the values get updated:

BSB_Text 3035 in this example is not in the acilitiesOutput table.

View solution in original post

20 Replies
DanPatterson_Retired
MVP Esteemed Contributor

If you are running the script as a script tool, there is a dependency parameter that may cover your case.

Your field name for the calculation will 'depend' on the fields in that featureclass.

http://pro.arcgis.com/en/pro-app/arcpy/geoprocessing_and_python/setting-script-tool-parameters.htm

XanderBakker
Esri Esteemed Contributor

You may also be interested in reading this blog post (in case you want a solution without join and using dictionaries): /blogs/richard_fairhurst/2014/11/08/turbo-charging-data-manipulation-with-python-cursors-and-diction... 

CommericalAnalytics
New Contributor II

Brilliant, thanks for that.

Have been looking for a while.

0 Kudos
CommericalAnalytics
New Contributor II

Thanks very much for this.

Both useful, especially the Turbo charging one. I have been racking my brains to get this sorted, and it looks like you have provided it.

Thanks again.

Regards,

Warren

0 Kudos
XanderBakker
Esri Esteemed Contributor

If you need any help with the script, just post back here 

0 Kudos
CommericalAnalytics
New Contributor II

Hello Xander,

Appreciate the help. This is my issue and it is the last step!

Below I am adding a variable weightfldval which is the attribute for the new table. This changes as per a loop earlier, and works fine.

The issue is where I create the updateFieldsList = ["BSB_Text", ?????] If I use weightfldval, then nothing happens, the data is not transferred . So I have the scenario where the columns have been create, just like I want, but nothing is transferred over from the DemandWeight of the FacilitiesOutput.

Let me know if this does not make sense. Appreciate any help. Again.

Regards,

Warren

#with the sublayer names as the keys

subLayers = dict((lyr.datasetName, lyr) for lyr in arcpy.mapping.ListLayers(outNALayer)[1:])

FacilitiesSubLayer = subLayers["Facilities"]

  1. Process: Add Field (2)

arcpy.AddField_management(ABN, weightfldval, "DOUBLE", "", "", "", "", "NULLABLE", "NON_REQUIRED", "")

print("Added field to ABN_Opp_Metrics_Homeloan")

#Create new Facilities FC

arcpy.management.CopyFeatures(FacilitiesSubLayer,FacilitiesOutput)

print("Facility Class Created")

  1. Transfer of a Single Field Value between Feature Classes

sourceFC = r"C:\xxx\xxx\Scratch_Workspce.gdb\FacilitiesOutput"

sourceFieldsList = ["Name", "DemandWeight"]

  1. Use list comprehension to build a dictionary from a da SearchCursor

valueDict = {r[0]:(r[1:]) for r in arcpy.da.SearchCursor(sourceFC, sourceFieldsList)}

updateFC = r"C:\xxx\xxx\Scratch_Workspce.gdb\ABN "

updateFieldsList = ["BSB_Text", ?????]

with arcpy.da.UpdateCursor(updateFC, updateFieldsList) as updateRows:

for updateRow in updateRows:

  1. store the Join value of the row being updated in a keyValue variable

keyValue = updateRow[0]

  1. verify that the keyValue is in the Dictionary

if keyValue in valueDict:

  1. transfer the value stored under the keyValue from the dictionary to the updated field.

updateRow[1] = valueDict[keyValue][0]

updateRows.updateRow(updateRow)

del valueDict

print "Finished script: " + strftime("%Y-%m-%d %H:%M:%S")

0 Kudos
CommericalAnalytics
New Contributor II

Not sure that is the problem actually. Even when I hard code the attribute, to test if the data is carried from DemandWeight to the Proposed Attribute, it does not work. So my issue might be somewhere else. Will have a look. I get no errors, just no data passes through to the new attributes. The dictionary data definitely has the desired attribute.

Warren

0 Kudos
RichardFairhurst
MVP Honored Contributor

Warren:

Are you sure that the values in the Name field in the FacilitiesOutput FC exactly match the values in the BSB_Text field in the ABN FC?  The code will only transfer data where there is an exact match on the values in those fields.  Also, since you are only using two fields as a key and value to create the dictionary, you do not need to load the value portion into a tuple and can just return the value of the dictionary directly without indexing.

valueDict = {r[0]:r[1] for r in arcpy.da.SearchCursor(sourceFC, sourceFieldsList)}

...

 updateRow[1] = valueDict[keyValue]

CommericalAnalytics
New Contributor II

Thanks for the Reply Richard, I am in training all day today but will get back to you tomorrow. Appreciate the response.

Warren

0 Kudos