topic Re: Concatenating multiple rows by common ID's in ArcGIS Pro Questions

Concatenating multiple rows by common ID's

CliveCartwright — Wed, 18 Nov 2020 18:48:34 GMT

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.

Re: Concatenating multiple rows by common ID's

JoeBorgione — Wed, 18 Nov 2020 19:22:11 GMT

Is python an option for you?

Re: Concatenating multiple rows by common ID's

JoshuaBixby — Wed, 18 Nov 2020 20:16:28 GMT

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

Re: Concatenating multiple rows by common ID's

CliveCartwright — Wed, 18 Nov 2020 22:35:13 GMT

it could be. 😁

Re: Concatenating multiple rows by common ID's

CliveCartwright — Wed, 18 Nov 2020 22:36:17 GMT

Thanks, I'll give this a go.

Much appreciated. 👍

Re: Concatenating multiple rows by common ID's

CliveCartwright — Thu, 19 Nov 2020 10:58:55 GMT

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?

Re: Concatenating multiple rows by common ID's

CliveCartwright — Thu, 19 Nov 2020 14:25:01 GMT

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.