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]

[CraigMcDade]

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?

[MathewCoyle]

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

[CraigMcDade]

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?

[MathewCoyle]

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

[CraigMcDade]

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.

[MathewCoyle]

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

[CraigMcDade]

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'

[MathewCoyle]

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.

[CraigMcDade]

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

[MathewCoyle]

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