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

[BrandonA_CDPH]

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

[RickeyFight]

Brandon Adcock‌

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

[BrandonA_CDPH]

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. 🙂 

[JoshuaBixby · Accepted solution]

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)‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

[BrandonA_CDPH]

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.