How to enumerate join values with ArcGIS?

Question

I have two shape files (e.g. postal code areas and cities) which I want to join based on an id that both shape files have in common (the id would be the city name in this example). Joining the two tables is not the problem (via Join and selecting the city name as field on which the join is based).

But what I really want is an enumeration of all the postal codes of a city - if I join them and take cities with postal codes, every city will get one random postal code that has the same city name as the city, but I want all of them as comma-separated list as new attribute in the cities shape file).

Is there a way to do that?

Example table postal codes:

Postal Code | City
          0 | A-Town     
          1 | A-Town     
          2 | A-Town     
          3 | B-Town     
          4 | B-Town     
          5 | C-Town

Example cities table:

City Name
   A-Town
   B-Town
   C-Town

Desired outcome table:

City Name | Postal codes
   A-Town | 0, 1, 2
   B-Town | 3, 4
   C-Town | 5

Fezter · Accepted Answer · 2013-04-08T22:58:34.597

4

I remembered an entry from ESRI's Geoprocessing blog which describes a tool which concatenates row values and does what you're after.

I haven't used it before as I haven't had a need for it yet. But I thought it looked useful and I think this will be an easy solution for you.

From the ArcGIS Resource Center:

This tool is based on a python script that takes an input table, selects a set of rows based on a field value and concatenates the row values for a specified field. This tool does not create a new output, but updates the input data.

edited Apr 08 '13 at 22:58

answered Apr 08 '13 at 01:54

Fezter

21,867
11
68
123

+1 I have not used it either but it looks like it will meet @geoSuleiman's requirement - nice find! – PolyGeo Apr 08 '13 at 02:03
+1 - It might be useful to add some more description of the tool and how to use it, in the case that either one or both of those links is damaged at some point in the future. More detail would also allow other people searching to come up with this answer. Just a thought, but a great find regardless!! – Get Spatial Apr 08 '13 at 16:29
Thanks a lot - the version for 10.0 worked after some modification of the python code (http://resources.arcgis.com/gallery/file/geoprocessing/details?entryID=F3368746-1422-2418-A061-D00793D7F9F0). – geoSuleiman Apr 08 '13 at 21:39

score 1 · Answer 2 · answered Apr 07 '13 at 23:27

Please don't think that you can or should do this in DML. Doing so is not only painful for you and your systems, but it is excruciating to watch as a third-party.

Your options are limited to either scripting, writing an extension or using a pivot table. While ESRI has a pivot table function, excel's is much easier to use and manage.

Below is an simple example for how you can script the output you require in C#. It can be quite easily converted to python.

Dictionary<String, List<String>> output = new Dictionary<string, List<string>>();
foreach (var row in table.rows) // Not sure what data structure you're working with.
{
  if (!output.ContainsKey(row.city))
  {
    output.Add(city, new List<string>());
  }
  output[city].Add(row.postcode);
}

String rowTemplate = "\"{0}\", \"{1}\";";
StringBuilder sb = new StringBuilder();
sb.AppendLine(String.Format(rowTemplate, "City", "Postcodes");
foreach (var row in output)
{
  sb.AppendLine(String.Format(rowTemplate, row.Key, String.Join(", ", row.Value.ToArray())));
}
String report = sb.ToString();

How to enumerate join values with ArcGIS?

2 Answers2

Linked