Concatenating multiple rows by common ID's

[CliveCartwright]

I'm trying to concatenate an attribute from multiple records by the record's ID.

For example: The two descriptions for 1, and again for 3, added together as one record. (see below)

Concatenating the information associated with common ID's

My dataset has over 2500 records. 

I've tried using the Pivot tool, and I managed to get the ID numbers merged with their corresponding descriptions. but the descriptions were spread over 2500 fields. Way too many to concatenate. 

I'm sure I've seen this demonstrated in an Analysis MOOC, but I can't find it. 😞 

Any help or ideas welcome. 

[JoeBorgione]

Is python an option for you?

[CliveCartwright]

it could be. 😁

[JoshuaBixby]

I agree with @JoeBorgione that Python is really your best option, maybe your only option until Esri updates the Summary Statistics tool to include text concatenation.

Below is some code that should work for you in the interactive Python window, after you make the appropriate changes to tables and field names (make sure to backup table first!):

import arcpy
from collections import defaultdict

lyr = # name of layer in Pro map
flds = ["ID_FLD", "TXT_FLD"]
sql_orderby = "ORDER BY ID_FLD, TXT_FLD"

cat_txt = defaultdict(str)
with arcpy.da.UpdateCursor(lyr, flds, sql_clause=(None, sql_orderby)) as cur:
    id_prev, txt = next(cur)
    cat_txt[id_prev] = txt
    for id, txt in cur:
        if id == id_prev:
            cat_txt[id] += " " + txt
        else:
            cat_txt[id] = txt
        id_prev = id
        
    cur.reset()
    
    id_prev, txt = next(cur)
    cur.updateRow([id_prev, cat_txt[id_prev]])
    for id, txt in cur:
        if id == id_prev:
            cur.deleteRow()
        else:
            cur.updateRow([id, cat_txt[id]])
        id_prev = id

[CliveCartwright]

Thanks, I'll give this a go.

Much appreciated. 👍

[CliveCartwright]

Thanks @JoeBorgione This looks like it might do the trick for me.

However, I've run the code and I keep getting:

Traceback (most recent call last):
File "<string>", line 32, in <module>
RuntimeError: The row contains a bad value. [Description_field]

This field is a text field @ 255. I'm not sure what makes a bad character in a text field. 

I'm confused? 

Have you had this before?

[CliveCartwright]

Thanks @JoeBorgione Sorted. I've managed to make it work.

I had to convert the ID number field into a text field; export the attribute table as a .csv, and export the csv version into the GDB. I guess a type of laundering for Arc to be able to read the table. 

So, a big Thank You for this. It's something I've wanted to be able to do for a long while. Thanks to your help I now can.