0

Is there a way in ArcGIS Field Calculator to concatenate fields from a column if values match in another column?

I have attached an image for reference.

enter image description here

  • 2
    How big a concatenation will you expect? Looks like in your example 4 three's? But could it be millions? Need more detail as @Midavalo suggest? Amend your question. – Hornbydd Jan 15 '18 at 0:08
  • The operation you want to perform requires operations across multiple rows. I don't think this is possible in the Field Calculator without writing Python or VB scripting code using a search cursor or something similar. – Llaves Jan 15 '18 at 2:32
  • As @Llaves wrode, it shouldn't be possible to solve the task using the Field Calculator. You would Need a search cursor to read the values and in a second step a update cursor to write the values to your "Join" field. – Saleika Jan 15 '18 at 7:57
  • 1
    I found a link to a tool that does exactly what I was asking and showing in the example image that I attached. arcgis.com/home/item.html?id=52dfcef46fdb4c76bfbc08dc01570f3c – huskersila Jan 15 '18 at 13:45
  • @Saleika It is quite possible using the Field Calculator. You can perform a lot of python tasks directly in the Field Calculator codeblock. – Midavalo Jan 15 '18 at 15:44
3

If your concatenation isn't going to be huge (just a handful of values rather than hundreds, or millions as Hornbydd queried) then this is quite doable in the Field Calculator. Select the Python parser, and check the "Show Codeblock" box.

In Pre-logic Script Code:

mydict = dict()
with arcpy.da.SearchCursor('testPoint',['NameField','ValueField','JoinField']) as cursor:
    for row in cursor:
        mydict.setdefault(row[0],[]).append(str(row[1]))

def concat(nf):
    x = None
    if nf in mydict:
        x = ",".join(mydict[nf])
    return x

Replacing testPoint with the name of your layer, and ['NameField','ValueField','JoinField'] with a list of your three fields

And in the expression:

concat(!NameField!)

Where NameField is the field that holds the key values.

In the first part of the Pre-logic script code, a Search Cursor is used to find all the values in the NameField and ValueField fields, and creating a python dictionary to store those values. Then in the function concat() the Field Calculator updates the JoinField with the values from the dictionary.

Here is the code in the Field Calculator window:

enter image description here

And the output result in the attribute table:

enter image description here

Note that this does not require the key values in NameField to be in order - you will see there is a 1 in row 13 and a couple of 2 values in rows 17 & 18.

  • Thank you for the response Midavalo. Very impressive ! I'll be using this in the near future. Much appreciated! – huskersila Feb 8 '18 at 22:11
  • Is there a similar solution for QGIS? I'm stacked with the same problem... – Rodrigo Vargas yesterday

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.