I have a table with 2 fields the first is for community and the second for the main community that it belongs to, as in the image
I want to summarize the second field (Belongs_to) but with an extra field (multiple value field) that shows components of each main community, as in the image
Could anyone help me please?
There is a script tool called "Concatenate Row Values" that does exactly what you want. It is available for download here. There is also a blog entry about this script tool. All you have to do is add a field to your FeatureClass where the concatenated values will be written and summarize.
For those interested in seeing the script:
# Import system modules
import sys, traceback, arcpy
#Define AddPrintMessage
def AddPrintMessage(msg, severity):
print msg
if severity == 0: arcpy.AddMessage(msg)
elif severity == 1: arcpy.AddWarning(msg)
elif severity == 2: arcpy.AddError(msg)
try:
# Set the parameters
InputTable = arcpy.GetParameterAsText(0)
if (InputTable == ''):
arcpy.AddError("No input provided")
CaseField = arcpy.GetParameterAsText(1)
if (CaseField == ''):
arcpy.AddError("No iterate field provided. The iteration will be based on unique values")
ReadFromField = arcpy.GetParameterAsText(2)
if (ReadFromField == ''):
arcpy.AddError("No field provided to read the values from")
CopyToField = arcpy.GetParameterAsText(3)
if (CopyToField == ''):
arcpy.AddError("No field provided to copy the values to")
Delimiter = arcpy.GetParameterAsText(4)
# Validate the inputs that are provided for field type.
# A text field value can only be copied to a text type field, and
# The numric field values with a delimiter can only be copied to a text type field.
# Short Integer can be copied to field of type text, short integer, long integer, single or double without a delimiter.
if arcpy.ListFields(InputTable, ReadFromField)[0].type == "String":
if arcpy.ListFields(InputTable, CopyToField)[0].type == "String":
""
else:
arcpy.AddError("Copy To Field must be of type text when Read From Field is of type text.")
else:
if arcpy.ListFields(InputTable, CopyToField)[0].type == "String":
""
else:
if Delimiter != "":
arcpy.AddError("Copy To Field must be of type text when Read From Field is of type numeric or date and you are using a delimiter.")
if Delimiter == "":
if arcpy.ListFields(InputTable, ReadFromField)[0].type == "SmallInteger":
if arcpy.ListFields(InputTable, CopyToField)[0].type in ["Integer", "SmallInteger", "Single", "Double"]:
""
else:
if arcpy.ListFields(InputTable, CopyToField)[0].type == "Date":
arcpy.AddError("Copy To Field must be of type text.")
if arcpy.ListFields(InputTable, ReadFromField)[0].type == "Integer":
if arcpy.ListFields(InputTable, CopyToField)[0].type in ["SmallInteger", "Integer", "Single", "Double", "Date"]:
arcpy.AddError("Copy To Field must be of type text.")
else:
if arcpy.ListFields(InputTable, ReadFromField)[0].type in ["Single", "Double" , "Date"]:
if arcpy.ListFields(InputTable, CopyToField)[0].type in ["Integer", "SmallInteger", "Single", "Double" , "Date"]:
arcpy.AddError("Copy To Field must be of type text.")
# Create an empty dictionary.
dictionary = {}
# Create a variable and set its value to the last row value. The first one is -1 which means no row before the first.
lastid = -1
# Create an empty variable which will store the value of the last row in the code below.
lastvalue = ""
# Insert Search cursor on a feature class or table to iterate through row objects and extract field values.
# Sort values of a Search Cursor based on the CaseField and ReadFromField in ascending order.
# Define what will happen once the curser moves through each row.
# While it is in each row it will get the value of CaseField field that you are using as id to iterate.
# While it is in row it will also get the value of the ReadFromField field that you want to concatenate.
# Set the value of the dictionary to the values read by the cursor from the ReadFromField.
# Set an if condition for what should the cursor do when it reads through fields with same ID or the CaseField value.
# In if condition set the new value to last value of the ReadFromField + the defined delimiter + the new value that is read.
# Again set the dictionary value to this new value.
# Set the loop to have the lastid to the id that you got from getValue before it goes through the seconnd loop and so on...
# Set the loops last value variable to the last value that was read such that it starts with that last value for the second loop and so on...
cur1 = arcpy.SearchCursor(InputTable, "", "", "", CaseField +" A;" + ReadFromField +" A")
for row in cur1:
id = row.getValue(CaseField)
value = row.getValue(ReadFromField)
dictionary[id] = value
if id == lastid:
value = str(lastvalue) + Delimiter + str(value)
dictionary[id] = value
lastid = id
lastvalue = value
# Delete cursor and row objects to remove the lock on the data that will remain until either the
# script completes or the cursor object is deleted.
del cur1, row
# Insert Update cursor to update or delete rows on the specified feature class, shapefile, or table.
# Define what will happen once the curser moves through each row.
# While you are in each row set the cursor to get the value of the CaseField that is used as Id to iterate.
# Set the value of the field that the concatenated values should be written to with the dictionary values that you concatenated in the code above.
# Set the cursor to update the row values with the dictionary values.
cur2 = arcpy.UpdateCursor(InputTable)
for row in cur2:
id = row.getValue(CaseField)
row.setValue(CopyToField, dictionary[id])
cur2.updateRow(row)
# Delete cursor and row objects to remove the lock on the data that will remain until either the
# script completes or the cursor object is deleted.
del cur2, row
# If you are using the tool in ModelBuilder, set the derived output parameter to the value
# of input table so that it is not empty and can be used with other tools.
arcpy.SetParameterAsText(5, str(InputTable))
except:
tb = sys.exc_info()[2]
tbinfo = traceback.format_tb(tb)[0]
pymsg = "PYTHON ERRORS:\nTraceback Info:\n" + tbinfo + "\nError Info:\n " + \
str(sys.exc_type)+ ": " + str(sys.exc_value) + "\n"
AddPrintMessage(pymsg, 2)
msgs = "ArcPy ERRORS:\n" + arcpy.GetMessages(2) + "\n"
AddPrintMessage(msgs, 2)
Use the Pivot Table tool to transform the Output Table into a table that contains one record for each "zone" with class attributes as separate attribute fields. This creates a table from the Input Table by reducing redundancy in records and flattening one-to-many relationships.
Try this solution in Excel. It's clumsy but it worked for me.
