Concatenate Row Values

In a table you can concatenate field values or row values.

Concatenating field values
Learn more about concatenating field values from this blog.

Concatenating row values
Download the script tool from here. This tool is based on a python script that takes an input table, selects a row(s) based on a field value, and concatenates the row values of any other specified field. This tool does not create a new output, but updates the input data.

Example of using Concatenating Row Values tool
Usually, in field sampling data a single point (e.g. the center or the intersection of a quadrant) is linked to an area in the field, and the data recorded for each species (grass/shrubs vegetation) within the quadrant is added as a separate entry linked to the same point as shown below:

This data has multiple records for the same center point of the quadrants. In such cases, if you want to concatenate the vegetation values for the same quadrant you can run the Concatenate Row Values tool (as shown below):

After concatenating the row values, you don’t need all the multiple points because one point has all the information you need (the All_Vegetation field). The Delete Identical tool can then be used to delete all but one of the multiple points (as shown below):

This workflow is shown below in a simple model:

Since both the tools modify the input, creating a backup copy of the input is strongly recommended.

This entry was posted in Analysis & Geoprocessing and tagged , , . Bookmark the permalink.

Leave a Reply

27 Comments

  1. nadinewazen says:

    Thank you this is a very helpful tool.
    I used it to concatenate values in English and it worked just fine.
    However I am having a problem using it to concatenate values in Arabic.
    I am getting the following error:

    Executing: ConcatenateRowValues22 Union_Munip_Concatenated Union_Na_1 Short_Name VillagesAr “, ”
    Start Time: Mon Apr 02 12:11:14 2012
    Running script ConcatenateRowValues22…
    PYTHON ERRORS:
    Traceback Info:
    File “C:Documents and SettingsNadineMy DocumentsArcGISNW_DB_UN_HabitatConcatenateRowValues10ScriptsConcatenaterowvalueArc.py”, line 106, in
    value = str(lastvalue) + Delimiter + str(value)

    Error Info:
    : ‘ascii’ codec can’t encode characters in position 0-4: ordinal not in range(128)

    ArcPy ERRORS:

    Completed script ConcatenateRowValues22…
    Failed to execute (ConcatenateRowValues22).
    Failed at Mon Apr 02 12:11:15 2012 (Elapsed Time: 1.00 seconds)

    If I got it right the problem is in the fact that Arabic is not encoded in ASCII. Is there a way to modify the script to make it identify it as UTF-8?

    Would appreciate any help i can get,

    Thank you !!

  2. shitijmehta says:

    value = str(lastvalue) + Delimiter + str(value) – because I am casting everything as string.

    You will have to cast everything as anything but a string (int/unicode/float whatever)

  3. shitijmehta says:

    Hello All,

    The issue with the unicode characters has been fixed in the uploaded sample. Hope this helps.

    Thanks!

  4. mzweifler91 says:

    The tool seemed to only recognize feature classes as inputs. And did not allow me to select a Table as an input to the tool

    I tried with both an SDE workspace and a file geodatabase and same problem. I would like to use this tool on non feature Tables. Please let me know if that is possible given current tool design.

    Thanks

  5. pjkielty says:

    Could we get the .mxd in version 10.0?

  6. tshifflett_va says:

    I downloaded this tool but all the download contained was the toolbox and a script. The toolbox is empty.

    How do I impliment the script in ArcGIS?

  7. granthumphries says:

    Hello,

    Great tool, this has been super useful for me! I am having one issue though, some of the concatenated rows in my project exceed 254 characters, is there any work around for this? Could you design things such that once the character limit is reached a second field could begin to be populated?

    • shitijmehta says:

      Do you mean that the remainder of the concatenated string to be written to a new field? Is it possible for you to add fields with larger length before running the tool?

      • granthumphries says:

        Yes, that is what I was thinking, that the remainder of the string go into a new field once space in the first field has been exhausted. To the best of my knowledge its not possible to have a string field that is longer than 254 characters in an attribute table within Arc. Do you agree that this is the case or do you know of a way to create a larger field?

        • granthumphries says:

          So any ideas on how to handle concatenated strings that exceed 254 characters? I think this a problem that will be fairly commonly encountered by those using this tool.

          • shitijmehta says:

            HI,

            Are you working with shapefiles? Try to write your input data to a file geodatabase and then add a field with greater length, run the concatenate tool and then if you absolutely need the output to be a shapefile you can use the copy tool or copy features tool and write it to a shapefile.

            Let me know if this worked.

  8. mikruzo says:

    is there a way to add code to the py where if the string being put in the copy to field is the same do not copy. loop threw until it is different: The reason I ask is because it bails saying “try passing a smaller amount of data”
    see link for example
    http://webgis.co.okaloosa.fl.us/website/okaloosagis/gm/LANDUSE-EXAMPLE.PDF
    thanks
    mike

    • shitijmehta says:

      Hi mikruzo,
      Can you check the field length to make sure the copy to field is sufficient for the string you are trying to copy? The tool overwrites the string in the copy to field so checking if the stuff being copied is similar to what already exists in the copy to field is not needed.

      • mikruzo says:

        Sorry, that is exactly the problem. What i am trying to copy to exceeds the MS access field length of 255 characters. So what I was trying to ask is: when running this arcpy.gp.ConcatenateRowValues(LANDUSE_25, “PATPCL_PIN”, “ZONE_”, “ZONING”, “,”) if there is a way to add to the code

        PATPCL_PIN ZONE_ ZONE_1 ZONING FLUPY
        00-2S-22-2088-0000-0CA2 MU MU C-3,C-3,MU,MU,MU-2,MU-2 C,MU,MU,MU,MU-2,MU-2
        00-2S-22-2088-0000-0CA2 MU-2 MU C-3,C-3,MU,MU,MU-2,MU-2 C,MU,MU,MU,MU-2,MU-2
        00-2S-22-2088-0000-0CA2 MU-2 MU-2 C-3,C-3,MU,MU,MU-2,MU-2 C,MU,MU,MU,MU-2,MU-2
        00-2S-22-2088-0000-0CA2 MU MU-2 C-3,C-3,MU,MU,MU-2,MU-2 C,MU,MU,MU,MU-2,MU-2
        00-2S-22-2088-0000-0CA2 C-3 MU C-3,C-3,MU,MU,MU-2,MU-2 C,MU,MU,MU,MU-2,MU-2
        00-2S-22-2088-0000-0CA2 C-3 C C-3,C-3,MU,MU,MU-2,MU-2 C,MU,MU,MU,MU-2,MU-2

        If ZONE_ = ZONE_ (EX: C-3 = C-3) THEN do no copy it so the now you would have the following

        PATPCL_PIN ZONE_ ZONE_1 ZONING FLUPY
        00-2S-22-2088-0000-0CA2 MU MU C-3,MU,MU,MU-2,MU-2 C,MU,MU,MU,MU-2,MU-2
        00-2S-22-2088-0000-0CA2 MU-2 MU C-3,MU,MU,MU-2,MU-2 C,MU,MU,MU,MU-2,MU-2
        00-2S-22-2088-0000-0CA2 MU-2 MU-2 C-3,MU,MU,MU-2,MU-2 C,MU,MU,MU,MU-2,MU-2
        00-2S-22-2088-0000-0CA2 MU MU-2 C-3,MU,MU,MU-2,MU-2 C,MU,MU,MU,MU-2,MU-2
        00-2S-22-2088-0000-0CA2 C-3 MU C-3,MU,MU,MU-2,MU-2 C,MU,MU,MU,MU-2,MU-2
        00-2S-22-2088-0000-0CA2 C-3 C C-3,MU,MU,MU-2,MU-2 C,MU,MU,MU,MU-2,MU-2

        If ZONE_ = ZONE_ (EX: MU = MU) THEN do no copy it so the now you would have the following

        PATPCL_PIN ZONE_ ZONE_1 ZONING FLUPY
        00-2S-22-2088-0000-0CA2 MU MU C-3,MU,MU-2,MU-2 C,MU,MU,MU,MU-2,MU-2
        00-2S-22-2088-0000-0CA2 MU-2 MU C-3,MU,MU-2,MU-2 C,MU,MU,MU,MU-2,MU-2
        00-2S-22-2088-0000-0CA2 MU-2 MU-2 C-3,MU,MU-2,MU-2 C,MU,MU,MU,MU-2,MU-2
        00-2S-22-2088-0000-0CA2 MU MU-2 C-3,MU,MU-2,MU-2 C,MU,MU,MU,MU-2,MU-2
        00-2S-22-2088-0000-0CA2 C-3 MU C-3,MU,MU-2,MU-2 C,MU,MU,MU,MU-2,MU-2
        00-2S-22-2088-0000-0CA2 C-3 C C-3,MU,MU-2,MU-2 C,MU,MU,MU,MU-2,MU-2

        If ZONE_ = ZONE_ (EX: MU-2 = MU-2) THEN do no copy it so the now you would have the following

        PATPCL_PIN ZONE_ ZONE_1 ZONING FLUPY
        00-2S-22-2088-0000-0CA2 MU MU C-3,MU,MU-2 C,MU,MU,MU,MU-2,MU-2
        00-2S-22-2088-0000-0CA2 MU-2 MU C-3,MU,MU-2 C,MU,MU,MU,MU-2,MU-2
        00-2S-22-2088-0000-0CA2 MU-2 MU-2 C-3,MU,MU-2 C,MU,MU,MU,MU-2,MU-2
        00-2S-22-2088-0000-0CA2 MU MU-2 C-3,MU,MU-2 C,MU,MU,MU,MU-2,MU-2
        00-2S-22-2088-0000-0CA2 C-3 MU C-3,MU,MU-2 C,MU,MU,MU,MU-2,MU-2
        00-2S-22-2088-0000-0CA2 C-3 C C-3,MU,MU-2 C,MU,MU,MU,MU-2,MU-2

        This way it would shorten what it is trying to copy
        and all I need is one instant of each zoning classification
        hope this makes better sense
        mike

  9. shitijmehta says:

    Hello All,
    I have updated the script to only return a unique set after concatenating the values.
    Shitij Mehta

  10. shitijmehta says:

    Hi pjkielty,

    The mxd only has the InputPoly feature class added to its Table of Contents. This InputPoly feature class can be found inside the ToolData.gdb.

  11. shitijmehta says:

    Hi mzweifler91,
    The tool now works with the feature classes and tables.

  12. shitijmehta says:

    Hello All,
    The tool has been updated to work with Null values. The tool now skips a Null value in the Read From Field and proceeds to concatenate without the Null value.

    Thanks!

  13. shitijmehta says:

    Hello All,

    The tool has now been updated to return:
    Sorted Values,
    Unique Values,
    Work with Null Values and
    Work with special characters

  14. stuladhar19 says:

    Hi shitijmehta,
    Thanks for the great tool, really works great for us for a small dataset. It didn’t work for the large dataset, so I am just wondering if there is a maximum rows limitation for this tool run? Thanks in advance.

  15. shitijmehta says:

    Hi stuladhar19,

    I don’t know of a reason why it would not work with a large dataset. Let’s see:
    How many records in your dataset?
    Feature Class/Just a table?
    Shapefile/Filegeodatabase?
    Does your large dataset values have something unique/inconsistent/trailing spaces/no match for various reasons/special characters/null values?

    What error are you getting when you run the tool with large dataset? Or does it run but nothing is concatenated? What happens when the tool runs with your large data?

  16. stuladhar19 says:

    I intially tested for only the duplicate values of feature class (file geodatabase). Then I tried the dataset that have some records with duplicate values and some do not have duplicate values and some have null values. It still worked fine and concatenate the values for that scenerio but when I tried for the larger dataset (greater than 1500 records), it didn’t work., I do not get any error messages. The file geodatabase that i need to run this tool has approx. 88,000 records.

  17. stuladhar19 says:

    Yes it runs but nothing is concatenated, and there are no error message also. Thanks!