0

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?

5
  • 1
    Whereclause prints - NAME IN ('Name0','Name1') etc performs as expected.
    – mapface
    Commented Sep 27, 2021 at 5:30
  • 1
    Can you use a dictionary looking like {Name:[field1, field2, field3]? So the list of values/fields are sorted and then have the same field order in you updatecursor? row[1:] = this_dict[row[0]]
    – Bera
    Commented Sep 27, 2021 at 5:37
  • 1
    I could change the dictionary format, I did try that approach but I'm not too good with pandas. I tried the new whereclause and it still throws up 'IndexError: list assignment index out of range' even on the first row
    – mapface
    Commented Sep 27, 2021 at 5:40
  • 1
    I think that will probably be easier. d = dict(zip(df['NAME'], df[['Field0','Field1','Field2']]))
    – Bera
    Commented Sep 27, 2021 at 5:53
  • 1
    A triple-nested search doesn't belong inside a cursor for loop, especially one that could exceed the field count. Build the dictionary so it can retrieve the field data by a key in the table.
    – Vince
    Commented Sep 27, 2021 at 10:33

1 Answer 1

0

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)
            

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.