ArcPy – How to Update Cursor with Nested Dictionary in ArcPy effectively

arcpycursordictionarymosaic-datasetpandas

I'm trying to update a mosaic dataset tables attributes with values from a CSV. The CSV and dataset share a field that uniquely identifies each record, and all of the fields to update have exactly the same name. The ultimate goal is to update all the values of the matching fields for each record.

The MD and CSV have this format – Name is the unique field:

Name Field0 Field1
Name0 1 2
Name1 4 5

My main problem has been the execution of the update cursor. My approach was to use Pandas to create a dictionary and iterate through the update the rows as below.

The dictionary created looks like this:

{Field[0]: {Name[0]: Value, Name[1]: Value}, Field[1]: {Name[0]: Value, Name[1]: Value}...}

My current code looks like this, the update cursor tends to fail with Index (list assignment index out of range) or Runtime Errors (table not found) depending on how I've tweaked it – I've been trying to chip at for a while now without success.

# imports
import os
import pandas as pd
import arcpy

# paths
folder = r"D:\folder"
gdb = "mosaic_dataset.gdb"
mosaic = "mosaic"

# csv
csv = r"D:\docs\update.csv"

# paths
gdb_path = os.path.join(folder, gdb)
md_path = os.path.join(folder, gdb, mosaic)
arcpy.env.workspace = md_path

# read csv
df = pd.read_csv(csv)

# create lists, dict
project_list = df["Name"].tolist() #Name is the unique identifier for both csv and MD
this_dict = df.set_index("Name").to_dict()
dict_keys = this_dict.keys()

# create a string to query the MD
whereclause = "NAME IN ('{}')".format("','".join(project_list))

# create mosaic layer 
arcpy.MakeMosaicLayer_management(md_path, "mdlayer")
mdl = "mdlayer"

# update cursor - doesn't work
with arcpy.da.UpdateCursor(mdl, list(dict_keys), whereclause) as updateRows:
    for row in updateRows:
        i = 0
        while i < len(list(project_list)):
            for k, v in this_dict.items():
                for key in v:
                    row[i] = v[key]
                    i += 1
        updateRows.updateRow(row)

How can I execute the loop?

Best Answer

Thanks for the suggestions from Vince and Bera, I got it working as below. The format of the dictionary was changed so that it was no longer nested which made writing the loop much easier.

# imports
import os
import pandas as pd
import arcpy

# paths
folder = r"D:\folder"
gdb = "mosaic_dataset.gdb"
mosaic = "mosaic"

# csv
csv = r"D:\docs\update.csv"

# paths
gdb_path = os.path.join(folder, gdb)
md_path = os.path.join(folder, gdb, service_name)
arcpy.env.workspace = md_path

# read csv
df = pd.read_csv(csv)

# create lists, dict
project_list = df["Name"].tolist()
field_list = list(df)
this_dict = df.set_index("Name").T.to_dict('list')
field_count = (len(field_list)-2)
               
# create mosaic layer
arcpy.MakeMosaicLayer_management(md_path, "mdlayer")
mdl = "mdlayer"
whereclause = "{0} IN{1}".format(arcpy.AddFieldDelimiters(datasource="mdl", field="Name"), tuple(project_list))

# update cursor
with arcpy.da.UpdateCursor(mdl, field_list, whereclause) as updateRows:
    for row in updateRows:
        for k, v in this_dict.items():
            i = 0
            while i <= field_count:
                row[i+1] = this_dict[row[0]][i]
                print(this_dict[row[0]][i])
                i += 1
                updateRows.updateRow(row)
Related Question