Take values from one field, and concatenate into string field if IDs are the same.

495
4
Jump to solution
07-20-2022 02:56 PM
Server
by
New Contributor III

Hello, 

Fairly new to python. By no means a beginner, but I still have trouble with logic.

I'm currently trying to create a script which takes an ID field - if the IDs match up, then take the value from a name field, and concatenate together into a list, and fill out a concatenated list field (see table)

I know i will have to use a update_cursor and a counter etc. the logic for whatever reason is escaping me on how to assemble the python. 

ID

Name

Name_cancat

33

hello

Hello, hi, bye

33

hi

Hello, hi, bye

33

bye

Hello, hi, bye

44

red

Red, tan, blue

44

tan

Red, tan, blue

44

blue

Red, tan, blue

88

dog

Dog, cat

88

cat

Dog, cat

 

I know it will be something like this (obviously missing a ton of code/logic)

with arcpy.da.UpdateCursor("FC", ['ID', 'name', 'name_concat']) as cursor:
       myList = []
       for row in cursor:

              myList.append(row[1])

       row[2] = myList

       cursor.updateRow(row)


   any help pointing me to a resource would be greatly appreciated!

 

0 Kudos
1 Solution

Accepted Solutions
JohannesLindner
MVP Frequent Contributor

Python:

# use a SearchCursor to get ids and names
ids_and_names = [row for row in arcpy.da.SearchCursor("FC", ["ID", "name"])]

# use an UpdateCursor to update the concatenated names
with arcpy.da.UpdateCursor("FC", ["ID", "name_concat"]) as cursor:
    for row in cursor:
        names = [name for id, name in ids_and_names if id == row[0]]
        name_concat = ", ".join(names)
        cursor.updateRow([row[0], name_concat])

 

You could also use the field calulator with Arcade:

// filter the dataset by the current id
var id = $feature.ID
var features_with_this_id = Filter($featureset, "ID = @ID")
// extract names as array
var names = []
for(var f in features_with_this_id) {
    Push(names, f.name)
}
// return the concatenated array
return Concatenate(names, ", ")

Have a great day!
Johannes

View solution in original post

0 Kudos
4 Replies
DonMorrison1
Occasional Contributor III

 I would make 2 passes through the table.

On pass one use a search cursor to create a python dict with each entry having the ID as the key and the list of string to be concatenated as the value.

On the second pass use an update cursor to  populate the name_cancat field in each record by using the value of the ID field to get the concatenated string from the dict.

0 Kudos
curtvprice
MVP Esteemed Contributor

How about the Merge tool with a field map with a "," delimiter to combine the values, then a Join Field (Maybe faster for a large dataset.)

0 Kudos
JohannesLindner
MVP Frequent Contributor

Python:

# use a SearchCursor to get ids and names
ids_and_names = [row for row in arcpy.da.SearchCursor("FC", ["ID", "name"])]

# use an UpdateCursor to update the concatenated names
with arcpy.da.UpdateCursor("FC", ["ID", "name_concat"]) as cursor:
    for row in cursor:
        names = [name for id, name in ids_and_names if id == row[0]]
        name_concat = ", ".join(names)
        cursor.updateRow([row[0], name_concat])

 

You could also use the field calulator with Arcade:

// filter the dataset by the current id
var id = $feature.ID
var features_with_this_id = Filter($featureset, "ID = @ID")
// extract names as array
var names = []
for(var f in features_with_this_id) {
    Push(names, f.name)
}
// return the concatenated array
return Concatenate(names, ", ")

Have a great day!
Johannes
0 Kudos
Server
by
New Contributor III

this is exactly what I needed! Thank you!

0 Kudos