Re-configuring Near Table with Multiple Near Features using ArcGIS Pro

Question

I'm trying to reconfigure the results of a near table in ArcGIS Pro. The table currently shows the nearest 5 features from a lake layer relative to multiple project locations. I've then used a join to add the name of each lake to the near table. What I want is to reconfigure the table so that the names of each lake are in a new field, comma separated, but based on the unique project locations.

So basically go from a table like this:

To a table like this:

I'm trying to do this as part of a ModelBuilder model.

Answer 1

This is somewhat tricky, if you insist using Model Builder and much easier with Python. The problem you have similar to using Spatial Join tool's Join operator of Field Mapping (for more info, please see https://esriaustraliatechblog.wordpress.com/2015/06/22/spatial-joins-hidden-trick-or-how-to-transfer-attribute-values-in-a-one-to-many-relationship/). However you do not have geometries to spatially join. Therefore I have two suggestions (1) Making your table spatial and using Spatial Join to find overlapping attributes, and (2) Using Calculate Value tool of the model builder with help of some Python.

Making your table spatial and using spatial join:

This is a process to create overlapping project area features for each near feature (lakes).

1. Use Make Query Table tool to find matching project areas features. Your table and your project areas feature class will be two inputs (make sure they are in the same workspace, e.g., in the same file GDB) and you need an expression like, Near_Table.Project_Location = My_Project_Locations_Feature_Class.Project_Location for matching.
2. Use Spatial Join as explained in the link above. Basically, you need to add a new TEXT field with enough length (to make sure the concatenated lake names fit in), say Lake_Names_Collated, and by using Join operator add the Near_Lake_Name field from your near table as input field into the Field Map of Join Features. Match Option should be ARE_IDENTICAL_TO.
3. Dissolve your Spatial_Join output by using Project_Location and Lake_Names_Collated as Dissolve Fields.

Using Calculate Value tool

The idea is to create a python dictionary and use it in Field Calculator. Be aware though there is a character length limit of 30,000 (see this post) if you have a quite large near table! Here is the snapshot of the model, pay attention to the Preconditions before and after the Calculate Value

1. Create a python dictionary as string by using the code below in a Calculate Value tool:

Expression**:

name_dict("%My_Near_Table%")

Code Block:

def name_dict(fc):
  a_dict = {}
  for row in arcpy.SearchCursor(fc):
    a_dict.setdefault(row.Project_Location, set()).add(row.Lake_Name)
  return str(a_dict)

2. Use Summary Statistics tool to create a new table with your project locations to add concatenated values. Only parameter that you need to pay attention is the Case_Field, which should be pointing to Project_Location field. The Statistics_Field could be any of your fields.

3. Add a TEXT field to concatenate lake names

4. Use the PYTHON expression below to calculate this field:

', '.join(map( str, %output_value%[!Project_Location!]))

** The table name in between %s is an inline variable substitution