Concatenating multiple rows by common ID's

146
6
2 weeks ago
Highlighted
Occasional Contributor

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'sConcatenating 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. 

Reply
0 Kudos
6 Replies
Highlighted
MVP Esteemed Contributor

Is python an option for you?

Reply
0 Kudos
Highlighted
Occasional Contributor

it could be. 😁

Reply
0 Kudos
Highlighted
MVP Esteemed Contributor

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
Highlighted
Occasional Contributor

Thanks, I'll give this a go.

 

Much appreciated. 👍

Reply
0 Kudos
Highlighted
Occasional Contributor

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?

Reply
0 Kudos
Highlighted
Occasional Contributor

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.

Reply
0 Kudos