topic Re: Calculate field with a variable attribute in Python Questions

Calculate field with a variable attribute

CommericalAnalytics — Mon, 26 Mar 2018 02:06:55 GMT

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

Re: Calculate field with a variable attribute

DanPatterson_Retired — Mon, 26 Mar 2018 03:02:55 GMT

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

Re: Calculate field with a variable attribute

XanderBakker — Mon, 26 Mar 2018 03:09:08 GMT

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

Re: Calculate field with a variable attribute

CommericalAnalytics — Mon, 26 Mar 2018 03:19:28 GMT

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

Re: Calculate field with a variable attribute

CommericalAnalytics — Mon, 26 Mar 2018 03:50:59 GMT

Brilliant, thanks for that.

Have been looking for a while.

Re: Calculate field with a variable attribute

XanderBakker — Mon, 26 Mar 2018 11:13:48 GMT

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

Re: Calculate field with a variable attribute

CommericalAnalytics — Mon, 26 Mar 2018 23:05:08 GMT

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"]

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

Transfer of a Single Field Value between Feature Classes

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

sourceFieldsList = ["Name", "DemandWeight"]

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:

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

keyValue = updateRow[0]

verify that the keyValue is in the Dictionary

if keyValue in valueDict:

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

Re: Calculate field with a variable attribute

CommericalAnalytics — Tue, 27 Mar 2018 00:43:56 GMT

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

Re: Calculate field with a variable attribute

RichardFairhurst — Tue, 27 Mar 2018 14:20:06 GMT

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]

Re: Calculate field with a variable attribute

CommericalAnalytics — Tue, 27 Mar 2018 21:00:41 GMT

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

Warren

Re: Calculate field with a variable attribute

XanderBakker — Wed, 28 Mar 2018 10:53:26 GMT

If possible, please share (part of) the data. This makes the discussion less complex and enables us to test the code and see where things might go wrong.

Re: Calculate field with a variable attribute

CommericalAnalytics — Wed, 28 Mar 2018 22:49:58 GMT

Hello Richard,

Thanks for that.

I have done a join in arcmap to make sure they match up, and they do.

Not sure if this is anything, but when I do a print of the dictionary and of the KeyValue

This is what I get:

Dictionary: {u'3923': 0.0, u'3920': 0.0, u'5259': 0.0, u'4023': 0.0, u'6725': 0.0,….

KeyValue: 2003

2006

2010

2013

So the four digit numbers are in both, but maybe it is the format of the dictionary?

I am hesitant to add data, but if it will make it easy for you to help, I will clean it up and attach some.

Regards,

Warren

Re: Calculate field with a variable attribute

RichardFairhurst — Wed, 28 Mar 2018 23:02:21 GMT

Why do you think the code is not working? The DemandWeight values all appear to be 0.0 in your sample, so if that is what you are seeing for keys like u'3923', u'3920', u'5259', u'4023', u'6725', etc., the code worked. Show what you expect the value to be for those keys. I suspect you are not populating the DemainWeight during any pass of your code, like you may have done in your Model. Also, the code you showed does not use a real output field name (?????), so I don't know if that is an issue. If a join succeeded, that indicates that both tables have a text field value that you are matching and would both contain unicode keys like u'2003',u'2006',u'2010',u'2013', not numeric values like 2003, 2006, 2010, 2013. Anyway, without the ability to see the actual table key value pairs the dictionary was told to read or the output table, it is impossible to identify where the mismatch is between your code and what you actually want the code to do.

Re: Calculate field with a variable attribute

CommericalAnalytics — Wed, 28 Mar 2018 23:10:22 GMT

I will send through some of the data.

I know it is not working as the end result are null values (every row), not 0.

Also most of the data are zero’s as to get this Model working I have decreased the number of rows drastically. The first part of the script is a network analysis location-allocation model.

Regards,

Warren

Re: Calculate field with a variable attribute

RichardFairhurst — Wed, 28 Mar 2018 23:17:55 GMT

Please complete what you actually used for this line of code for the output field name:

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

The output field would be wahtever field you used in place of ?????

Also, your dictionary indicates the values associated with the keys are not within a tuple, so it does not seem you are using your original code. Anyway, you would have to make sure the line populating the output field is:

updateRow[1] = valueDict[keyValue]

Re: Calculate field with a variable attribute

CommericalAnalytics — Wed, 28 Mar 2018 23:19:22 GMT

Hello Richard,

What I am trying to do with the script below is take the DemandWeight column in the Facilities layer to the Value column in the ABN layer, using the BSB as the primary key.

Regards,

Warren

Re: Calculate field with a variable attribute

CommericalAnalytics — Wed, 28 Mar 2018 23:25:58 GMT

In this case, it would be

updateFieldsList = ["BSB_Text", “Value”]

as per the example data I sent through.

Warren

Re: Calculate field with a variable attribute

RichardFairhurst — Sat, 11 Dec 2021 16:44:08 GMT

You need to make the updateFieldsList = ["BSB_Text", ?????] line read:

updateFieldsList = ["BSB_Text", "Value"]

I am backing up to code that I you did not show completely.

Where do the variables ABN and weightfldval get set and what values do they have?

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

I do not see where you set the FacilitiesOutput variable value. I believe it should match the same path and FC name as the sourceFC = r"C:\xxx\xxx\Scratch_Workspce.gdb\FacilitiesOutput"

arcpy.management.CopyFeatures(FacilitiesSubLayer,FacilitiesOutput)

Also I hope you removed the erroneous space character that comes after ABN in this line of code:

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

should be

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

If your code still does not work try running this code using hardcoded values and get it to work. Then you can modify all of the processes that set your input FCs, layers, etc based on variables. If the base code below works and then breaks as you modify the way inputs and outputs are determined at runtime, then you will know the problem is not in this part of the code.

import arcpy  
  
# hardcode the path to the correct gdb and FC
sourceFC = r"C:\xxx\xxx\Scratch_Workspce.gdb\FacilitiesOutput"  
  
sourceFieldsList = ["Name", "DemandWeight"]  
  
# Use list comprehension to build a dictionary from a da SearchCursor  
valueDict = {r[0]:r[1] for r in arcpy.da.SearchCursor(sourceFC, sourceFieldsList)}  
  
# hardcode the path to the correct gdb and FC
updateFC = r"C:\xxx\xxx\Scratch_Workspce.gdb\ABN"  
  
updateFieldsList = ["BSB_Text", "Value"]  
  
with arcpy.da.UpdateCursor(updateFC, updateFieldsList) as updateRows:  
    for updateRow in updateRows:  
        # store the Join value of the row being updated in a keyValue variable  
        keyValue = updateRow[0]  
        # verify that the keyValue is in the Dictionary  
        if keyValue in valueDict:  
             # transfer the value stored under the keyValue from the dictionary to the updated field.  
            updateRow[1] = valueDict[keyValue]  
            updateRows.updateRow(updateRow)  
  
del valueDict‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

Re: Calculate field with a variable attribute

XanderBakker — Sat, 11 Dec 2021 16:44:11 GMT

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.

Re: Calculate field with a variable attribute

CommericalAnalytics — Wed, 04 Apr 2018 00:19:55 GMT

Hello Xander,

Thank you very much for the time, effort and input, I greatly appreciate it.

It is working now. The issue I was having was it was running through the loop without doing the copying data from one feature to the other.

When I fixed that it worked like a dream.

Again, thanks for all your help and the help of the others.

Regards,

Warren