How to Concatenate Fields

[CraigMcDade]

I have an attribute table with approx 1000 records. There are two Fields I care about: Name and Pg_Number (see attached pic)

I would like to consolidate the fields so that I received an output or a new field that combines the Pg_Number field based on the Name Field

Ex.

In my head its something like:

if [Name] = "Orange Lake" then [Pg_Number] = 5, 6, 7, 8, 9, 10...
if [Name] = "Green Lake" then [Pg_Number] = 11, 12, 13, 14, 15..."
and so on...

I haven't had much experience with Python but I'm trying to build a model/script and I'm getting stuck on how to do this field calc/consolidation.

Any help would be appreciated.[ATTACH=CONFIG]21511[/ATTACH]

[MathewCoyle]

I don't understand fully what you are trying to do. Can you give an example of the desired output for the first few rows of the data you posted?

[CraigMcDade]

In my data the NAME field refers to Fire Station Boundaries and the Pg_Number refers to the page in the mapbook. I'm trying to convert or create a table so that all pages that are within each boundary are listed together, separated by a comma.

So instead of the table as it was originally uploaded, you would get a result of:

Name: Orange Lake 
Pg_Number: 5, 6, 7, 8, 9, 10, 11, 12, 13, 25, 26, 27, 28, 29, 30, 31, 32, 33, 34, 47

There are 33 unique NAME attributes. I need to create a mapbook of each NAME

The goal after getting this would be then to take the page list and push it to export to pdf those pages.

I went through the process manually yesterday and it was awful. I wound up taking that table, opening it in excel, downloading KuTools, using their combine tool to create the page listings (manually selecting which cells), then individually taking those page ranges and taking it back into ArcMap MapLogic tools and exporting from there. 10 hours of click.click.click. ugh.

So I figure there has to be an easier/more efficient way than that.

[MathewCoyle]

I haven't tested this exactly but it should get you on the right path. It cursors through your initial table creating a dictionary of key, list values. Then it uses an insert cursor to basically inject those dictionary keys and lists into a new table using the same field names. You'd have to either create the new table or add that aspect to the script.

import arcpy

table = # source_table
new_table = # new table
keyField = 'Name'
valField = 'Pg_number'

tableDict = {}

for row in arcpy.SearchCursor(table):
    tableDict.setdefault(row.getValue(keyField), []).append(row.getValue(valField))

insertCursor = arcpy.InsertCursor(new_table)
for key, val in tableDict.iteritems():
    row = insertCursor.newRow()
    row.setValue(keyField, key)
    row.setValue(valField, ', '.join(val))
    insertCursor.insertRow(row)

[CraigMcDade]

Thanks! This looks promising. I inserted my values and am getting an ambiguous Runtime Error:

Runtime error 
Traceback (most recent call last):
  File "<string>", line 10, in <module>
  File "c:\program files (x86)\arcgis\desktop10.1\arcpy\arcpy\__init__.py", line 1133, in SearchCursor
    return gp.searchCursor(dataset, where_clause, spatial_reference, fields, sort_fields)
  File "c:\program files (x86)\arcgis\desktop10.1\arcpy\arcpy\geoprocessing\_base.py", line 359, in searchCursor
    self._gp.SearchCursor(*gp_fixargs(args, True)))
RuntimeError: ERROR 999999: Error executing function.

[MathewCoyle]

That could be from your target string field you are concatenating all the values together in is not long enough. Or it could be something else. Do you have a join on your table by any chance?

[CraigMcDade]

The table is a result of a spatial join that I performed, but there are no active joins from external tables.

[MathewCoyle]

Try changing the search cursor to these lines.

tableDict = {}
arcpy.MakeTableView_management(table, tempTable)
with arcpy.da.SearchCursor(tempTable, [keyField, valField]) as cursor:
    for row in cursor:
        tableDict.setdefault(row[0], []).append(row[1])

[CraigMcDade]

that gets me:

Runtime error
Traceback (most recent call last):
  File "<string>", line 9, in <module>
NameError: name 'tempTable' is not defined

I appreciate your time and help. I'm also getting an error that my dbf file doesn't exist, so I converted it to an xlsx and don't get that error. Not sure if that matters, and for the new table I just created an empty excel spreadsheet. That could be wrong too. As you can tell. I'm new to this. thanks for your patience.

[MathewCoyle]

Ah yes you'd need to declare that. Something like this.

tempTable = r'\\in_memory\temp_table'

Also you'll want to make it a dbf or fgdb table, something Arc can read easily. Excel is not great for that. You'll also have to create this before you run your script. Make sure the field you want to all the values in is a long enough string field.