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
Solved! Go to 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)
This might be one way to do it in excel
Combine Rows into a Delimited List | Excel University
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. 🙂
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)
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.