6

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 imageTable 1

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 imageenter image description here

Could anyone help me please?

0

3 Answers 3

7

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.

enter image description here

enter image description here

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)   
1
  • Thanks Aaron and Hornbydd, I tried it and it worked when the field values are in English, but showed an error when working with another language. Feb 4, 2014 at 18:33
1

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.

3
  • Thank you Geog, I used summary statistics, but I need to perform more detailed summary that gives me the participating values for each summarized record as shown in the second table. Feb 2, 2014 at 19:41
  • I've changed my answer Feb 2, 2014 at 19:45
  • Pivot Table might do the trick if you assign your "values" both as "Pivot field" and "Value Field" and put "grouping values" as "Input field". You will get a table with as many columns as there were unique values (potential risk of exceeding maximum allowed columns). Now all you have to do is concatenate all the columns for each row. Laborious, could be wrapped up in Python, but I can imagine Python has better solution.
    – pg85
    Oct 15, 2015 at 12:28
0

Try this solution in Excel. It's clumsy but it worked for me.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.