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

JoshuaBixby

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

<SPAN class="keyword token">from</SPAN> arcpy<SPAN class="punctuation token">.</SPAN>da <SPAN class="keyword token">import</SPAN> NumPyArrayToTable<SPAN class="punctuation token">,</SPAN> TableToNumPyArray
<SPAN class="keyword token">import</SPAN> pandas <SPAN class="keyword token">as</SPAN> pd

out_table <SPAN class="operator token">=</SPAN>  <SPAN class="comment token"># path to output table</SPAN>
in_features <SPAN class="operator token">=</SPAN>  <SPAN class="comment token"># path to table or feature class</SPAN>
group_field <SPAN class="operator token">=</SPAN> <SPAN class="string token">"Identifer"</SPAN> <SPAN class="comment token"># field to group by</SPAN>
concat_field <SPAN class="operator token">=</SPAN> <SPAN class="string token">"Classes"</SPAN> <SPAN class="comment token"># field to concatenate</SPAN>

df <SPAN class="operator token">=</SPAN> pd<SPAN class="punctuation token">.</SPAN>DataFrame<SPAN class="punctuation token">(</SPAN>TableToNumPyArray<SPAN class="punctuation token">(</SPAN>in_features<SPAN class="punctuation token">,</SPAN> <SPAN class="punctuation token">[</SPAN>group_field<SPAN class="punctuation token">,</SPAN> concat_field<SPAN class="punctuation token">]</SPAN><SPAN class="punctuation token">)</SPAN><SPAN class="punctuation token">)</SPAN>
df <SPAN class="operator token">=</SPAN> df<SPAN class="punctuation token">.</SPAN>sort_values<SPAN class="punctuation token">(</SPAN><SPAN class="punctuation token">[</SPAN>group_field<SPAN class="punctuation token">,</SPAN> concat_field<SPAN class="punctuation token">]</SPAN><SPAN class="punctuation token">)</SPAN>
df <SPAN class="operator token">=</SPAN> df<SPAN class="punctuation token">.</SPAN>groupby<SPAN class="punctuation token">(</SPAN><SPAN class="punctuation token">[</SPAN>group_field<SPAN class="punctuation token">]</SPAN><SPAN class="punctuation token">)</SPAN><SPAN class="punctuation token">[</SPAN>concat_field<SPAN class="punctuation token">]</SPAN><SPAN class="punctuation token">.</SPAN>apply<SPAN class="punctuation token">(</SPAN><SPAN class="string token">', '</SPAN><SPAN class="punctuation token">.</SPAN>join<SPAN class="punctuation token">)</SPAN><SPAN class="punctuation token">.</SPAN>reset_index<SPAN class="punctuation token">(</SPAN><SPAN class="punctuation token">)</SPAN>
str_len <SPAN class="operator token">=</SPAN> df<SPAN class="punctuation token">[</SPAN>concat_field<SPAN class="punctuation token">]</SPAN><SPAN class="punctuation token">.</SPAN>str<SPAN class="punctuation token">.</SPAN>len<SPAN class="punctuation token">(</SPAN><SPAN class="punctuation token">)</SPAN><SPAN class="punctuation token">.</SPAN>max<SPAN class="punctuation token">(</SPAN><SPAN class="punctuation token">)</SPAN>
recs <SPAN class="operator token">=</SPAN> df<SPAN class="punctuation token">.</SPAN>to_records<SPAN class="punctuation token">(</SPAN>
    index<SPAN class="operator token">=</SPAN><SPAN class="token boolean">False</SPAN><SPAN class="punctuation token">,</SPAN>
    column_dtypes<SPAN class="operator token">=</SPAN><SPAN class="punctuation token">{</SPAN>group_field<SPAN class="punctuation token">:</SPAN><SPAN class="string token">"int64"</SPAN><SPAN class="punctuation token">,</SPAN> concat_field<SPAN class="punctuation token">:</SPAN> <SPAN class="string token">"S"</SPAN> <SPAN class="operator token">+</SPAN> str<SPAN class="punctuation token">(</SPAN>str_len<SPAN class="punctuation token">)</SPAN><SPAN class="punctuation token">}</SPAN>
<SPAN class="punctuation token">)</SPAN>
NumPyArrayToTable<SPAN class="punctuation token">(</SPAN>recs<SPAN class="punctuation token">,</SPAN> out_table<SPAN class="punctuation token">)</SPAN><SPAN class="line-numbers-rows"><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN></SPAN>