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

174
4
05-07-2020 01:25 PM New Contributor III

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

1 Solution

Accepted Solutions by MVP Esteemed Contributor

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, TableToNumPyArrayimport pandas as pdout_table =  # path to output tablein_features =  # path to table or feature classgroup_field = "Identifer" # field to group byconcat_field = "Classes" # field to concatenatedf = 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)‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍``
4 Replies by MVP Regular Contributor

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

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. 🙂 by MVP Esteemed Contributor

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, TableToNumPyArrayimport pandas as pdout_table =  # path to output tablein_features =  # path to table or feature classgroup_field = "Identifer" # field to group byconcat_field = "Classes" # field to concatenatedf = 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)‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍`` New Contributor III

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. 