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)
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.
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
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.
Have you had this before?
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.