topic Re: How to Concatenate Fields in Python Questions

How to Concatenate Fields

CraigMcDade — Wed, 06 Feb 2013 17:52:48 GMT

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]

Re: How to Concatenate Fields

MathewCoyle — Wed, 06 Feb 2013 19:14:17 GMT

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?

Re: How to Concatenate Fields

CraigMcDade — Sat, 11 Dec 2021 21:25:53 GMT

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.

Re: How to Concatenate Fields

MathewCoyle — Sat, 11 Dec 2021 21:25:55 GMT

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)

Re: How to Concatenate Fields

CraigMcDade — Sat, 11 Dec 2021 21:25:58 GMT

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.

Re: How to Concatenate Fields

MathewCoyle — Thu, 07 Feb 2013 12:02:02 GMT

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?

Re: How to Concatenate Fields

CraigMcDade — Thu, 07 Feb 2013 12:11:00 GMT

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

Re: How to Concatenate Fields

MathewCoyle — Sat, 11 Dec 2021 21:26:01 GMT

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])

Re: How to Concatenate Fields

CraigMcDade — Thu, 07 Feb 2013 12:54:23 GMT

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.

Re: How to Concatenate Fields

MathewCoyle — Thu, 07 Feb 2013 13:14:25 GMT

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.

Re: How to Concatenate Fields

CraigMcDade — Sat, 11 Dec 2021 21:26:03 GMT

well it is working and creating the table view. but for whatever reason it isn't reading my new_table portion correctly

I have inputed:

new_table = "c:\users\craig.mcdade\table.dbf" # new table

but getting this error:
IOError: "c:\users\craig.mcdade able.dbf" does not exist

as you can see, it is not recognizing the \t in the file structure?

Re: How to Concatenate Fields

MathewCoyle — Thu, 07 Feb 2013 13:59:40 GMT

Yes because \t is a tab. You need to format paths like this when using single backslashes.

new_table = r"c:\users\craig.mcdade\table.dbf"

You can read this for more information on string literals.
http://docs.python.org/2/reference/lexical_analysis.html#string-literals

Re: How to Concatenate Fields

CraigMcDade — Sat, 11 Dec 2021 21:26:06 GMT

again you've been quite helpful and patient with my lack of understanding. I'm getting a similar error as with the above SearchCursor error:

Runtime error
Traceback (most recent call last):
File "<string>", line 15, in <module>
File "c:\program files (x86)\arcgis\desktop10.1\arcpy\arcpy\__init__.py", line 1180, in InsertCursor
return gp.insertCursor(dataset, spatial_reference)
File "c:\program files (x86)\arcgis\desktop10.1\arcpy\arcpy\geoprocessing\_base.py", line 369, in insertCursor
self._gp.InsertCursor(*gp_fixargs(args, True)))
RuntimeError: ERROR 999999: Error executing function.

It is almost identical to the previous error, so using your example I made an attempt to fix this one with:

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

Am I at least close?

Re: How to Concatenate Fields

MathewCoyle — Sat, 11 Dec 2021 21:26:09 GMT

The insert cursor is a little different than the search cursor. To use the .da insert cursor you want to do something like this.

insertCursor = arcpy.da.InsertCursor(os.path.join(outPath, new_table), [keyField, valField])

for key, val in tableDict.iteritems():
    insertCursor.insertRow((key, ', '.join(val)))

Re: How to Concatenate Fields

CraigMcDade — Sat, 11 Dec 2021 21:26:12 GMT

well. I'm close now. The current code is:

import arcpy, os
  
table = r"c:\users\craig.mcdade\GridforPython.dbf" # source_table
new_table = r"c:\users\craig.mcdade\table.dbf" # new table
tempTable = r'\\in_memory\temp_table'
outPath = r'\\in_memory\outPath'
keyField = 'NAME'
valField = 'Pg_Number'
  
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])
    
open("new_table","w")
    
insertCursor = arcpy.da.InsertCursor(os.path.join(outPath, new_table), [keyField, valField])

for key, val in tableDict.iteritems():
    insertCursor.insertRow((key, ', '.join(val)))

I was getting a Runtime Error: cannot open 'c:\...'

so I added the open("new_table","w") line

now I get a Runtime Error of Permission denied: 'new_table'

I have allowed full permissions on the dbf file so I'm not sure why it isn't allowing it to open.

Re: How to Concatenate Fields

MathewCoyle — Sat, 11 Dec 2021 21:26:14 GMT

Don't open the table that way, that is for a different kind of accessing. The cursor handles all the back end you need. Your outPath variable should be to a place on disk, not in memory. Your new_table variable should just be the name of the table not the path.

Try this.

import os
import arcpy

table = r"c:\users\craig.mcdade\GridforPython.dbf" # source_table
new_table = r"table.dbf" # new table name + extension only
tempTable = r"\\in_memory\temp_table"
outPath = r"c:\users\craig.mcdade" # path to output location on disk
keyField = 'NAME'
valField = 'Pg_Number'

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])

insertCursor = arcpy.da.InsertCursor(os.path.join(outPath, new_table), [keyField, valField])

for key, val in tableDict.iteritems():
    insertCursor.insertRow((key, ', '.join(val)))

Re: How to Concatenate Fields

CraigMcDade — Thu, 07 Feb 2013 18:23:48 GMT

thanks for the tip on the open statement.

still getting the same runtime error:

Runtime error
Traceback (most recent call last):
File "<string>", line 17, in <module>
RuntimeError: cannot open 'c:\users\craig.mcdade\table.dbf'

Re: How to Concatenate Fields

MathewCoyle — Thu, 07 Feb 2013 18:27:47 GMT

Can you open it in ArcCatalog? Or are you leaving it open somewhere while it is trying to be accessed? I'd recommend closing any open ArcGIS instances before running it to make sure.

Re: How to Concatenate Fields

CraigMcDade — Thu, 07 Feb 2013 18:50:06 GMT

once again good call. my file was open and locked by arcmap when i used the incorrect open call

new error:
Runtime error
Traceback (most recent call last):
File "<string>", line 20, in <module>
TypeError: sequence item 0: expected string, int found

Re: How to Concatenate Fields

MathewCoyle — Sat, 11 Dec 2021 21:26:17 GMT

Oh yes I had forgotten it was a list of ints you were pulling here.

Try replacing this as the last line. The 'for key, val...' line is there just for reference it is the same.

for key, val in tableDict.iteritems():
    insertCursor.insertRow((key, ', '.join(str(item) for item in val))