nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

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]

table.PNG

Najděte více příspěvků označených jako

Accepted answers

All comments

CraigMcDade

I made the following change to the code and it is working now:

Replaced:

with arcpy.da.SearchCursor(tempTable, [keyField, valField]) as cursor:

with:

with arcpy.da.SearchCursor(table, [keyField, valField]) as cursor:

Thanks for all your help! Now I just need to figure out how to create the field long enough so that all results are populated.

CraigMcDade

The NAME field doesn't need to fit that many characters, The Pg_Number field needs to be long, I've adjusted it to its maximum size (which is apparently only 254 characters?) However, one of the NAME fields has 74 different page numbers plus the ", " so its maximum size should be set to 370

The code results in the same error, the full code I'm running is:

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(str(item) for item in val)))

MathewCoyle

Not sure why you would get that error. Are you sure 50 characters is long enough? I think you would see a different error if that were the issue though...

Actually I think I may have missed the last bracket in the last line I posted.

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

If that modification nets the same error can you post the code you are running?

CraigMcDade

I've attached it. Thanks again.

table.zip

MathewCoyle

Post a copy of the table you created.

CraigMcDade

I figured as much and created them as indicated in the code. Same error.

MathewCoyle

You need to create your target table with the field names and properties you want. You could modify the insertCursor object creation to have different field names if you'd like, but yes they will have to created already.

CraigMcDade

1 step closer. Now its telling me:

Runtime error
Traceback (most recent call last):
File "<string>", line 19, in <module>
RuntimeError: Cannot find field 'NAME'

Does my new table need to have these field names or will it generate them automatically?

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

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

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

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

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

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

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

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

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

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

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.

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

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

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

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

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

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

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

Připojte se k diskusi

Přihlášení členové mohou přispívat, sledovat aktualizace a další. Jste tu noví? Zaregistrujte si bezplatný účet.

Rychlé odkazy

Community Help
Find useful guides, FAQs, and documents to help you navigate and make the most of Esri Community.