topic Re: Combine Duplicates based on one Field and List Unique Values in a Second in ArcGIS Pro Questions

Combine Duplicates based on one Field and List Unique Values in a Second

BrandonA_CDPH — Thu, 07 May 2020 20:25:59 GMT

I have a table of Identifiers and Types. There are a limited number of types (say 5) and each identifier can have one or more of these types. Each Identifier and class type in a different record and they all have an address (so they all generate a point). The table looks something like this:

Identifier | Class

123 | A

123 | C

234 | A

456 | C

456 | B

I'm trying to find a way to put this on a map so there is one point for each Identifier and a single field showing classes. The table would look something like this:

Identifier | Classes

123 | A, C

234 | A

456 | B, C

I cannot for the life of me figure out how to make this happen in ArcGIS Pro or in Excel even. I'm hoping someone has at least a direction for me. Thanks.

Brandon

Re: Combine Duplicates based on one Field and List Unique Values in a Second

RickeyFight — Thu, 07 May 2020 20:43:30 GMT

Brandon Adcock‌

This might be one way to do it in excel
Combine Rows into a Delimited List | Excel University

Re: Combine Duplicates based on one Field and List Unique Values in a Second

JoshuaBixby — Sat, 11 Dec 2021 14:02:25 GMT

The following should work for you in Pro, but it won't work in ArcMap since ArcMap has such a dated Pandas version.

from arcpy.da import NumPyArrayToTable, TableToNumPyArray
import pandas as pd

out_table =  # path to output table
in_features =  # path to table or feature class
group_field = "Identifer" # field to group by
concat_field = "Classes" # field to concatenate

df = pd.DataFrame(TableToNumPyArray(in_features, [group_field, concat_field]))
df = df.sort_values([group_field, concat_field])
df = df.groupby([group_field])[concat_field].apply(', '.join).reset_index()
str_len = df[concat_field].str.len().max()
recs = df.to_records(
    index=False,
    column_dtypes={group_field:"int64", concat_field: "S" + str(str_len)}
)
NumPyArrayToTable(recs, out_table)‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

Re: Combine Duplicates based on one Field and List Unique Values in a Second

BrandonA_CDPH — Fri, 08 May 2020 16:37:08 GMT

This worked beautifully in Excel. Thank you so much!

PS - while this one is equally correct, I feel obligated to choose the ArcGIS Pro solution as correct given the forum. I will keep this in my pocket though. 🙂

Re: Combine Duplicates based on one Field and List Unique Values in a Second

BrandonA_CDPH — Fri, 08 May 2020 16:51:36 GMT

This is a beautiful solution! Did exactly what I was hoping it would! Thank you so much!

I'm marking this one as correct because it is an ArcGIS forum, but both are correct and work equally well.