Calculate field with a variable attribute

[CommericalAnalytics]

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

[XanderBakker]

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.

[CommericalAnalytics]

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

[RichardFairhurst]

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.

[CommericalAnalytics]

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

[RichardFairhurst]

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]

[CommericalAnalytics]

In this case, it would be

updateFieldsList = ["BSB_Text", “Value”]

as per the example data I sent through.

Warren

[CommericalAnalytics]

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

[RichardFairhurst]

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

[XanderBakker · Accepted solution]

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.

[CommericalAnalytics]

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