Merging many to one data rows in arcpy

Question

The dataset 'left_right_merge' at the bottom of my screen shot shows the dataset that I'm currently working with. The NODE values indicate a single point feature, but is duplicated with many attributes. I want to collapse the dataset so that it looks like the 'test_data_nodes' at the top of the screen shot. How would I do this in python?

Answer 1

There might be a more elegant way to do this, but here is a quick & dirty script base on my understanding of your problem:

import arcpy

arcpy.env.workspace = r'C:\Users\echiasson\Documents\ArcGIS\Default.gdb' # Replace correct geodatabase path
arcpy.env.overwriteOutput = True

# Make a copy of the original feature class
arcpy.Copy_management('left_right_merge', 'left_right_merge_collapse')

# Collapse the output feature class
nodeList = []
with arcpy.da.UpdateCursor('left_right_merge_collapse', ['NODE']) as cursor:
    for row in cursor:
        if row[0] in nodeList:
            cursor.deleteRow()
        else:
            nodeList.append(row[0])

for node in nodeList:
    # Compile the values in the original feature class
    attList = [0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0]
    with arcpy.da.SearchCursor('left_right_merge', ['NB_L','NB_R', 'SB_L', 'SB_R', 'NWB_L', 'NWB_R', 'NEB_L', 'NEB_R', 'SWB_L', 'SWB_R', 'SEB_L', 'SEB_R', 'EB_L', 'EB_R', 'WB_L', 'WB_R'], "NODE = " + str(node)) as cursor:
        for row in cursor:
            for i in range(0,15):
                if row[i] != 0:
                    attList[i] = row[i]

    # Update the values in the collapse feature class
    with arcpy.da.UpdateCursor('left_right_merge_collapse', ['NB_L','NB_R', 'SB_L', 'SB_R', 'NWB_L', 'NWB_R', 'NEB_L', 'NEB_R', 'SWB_L', 'SWB_R', 'SEB_L', 'SEB_R', 'EB_L', 'EB_R', 'WB_L', 'WB_R'], "NODE = " + str(node)) as cursor:
        for row in cursor:
            for i in range(0,15):
                row[i] = attList[i]
            cursor.updateRow(row)

Please leave a comment if I am missing something.

Answer 2

If you have the Advance (ArcInfo) version of ArcMap you could simply run the Delete Identical tool, no need for any python.

When you ask a question you should state the GIS system you have, license level and appropriate extensions as this will dictate the response you'll get as some tools become available for use with higher license levels.

Answer 3

I didn't think of this earlier, but in the previous question you were working with a single table. Another option is a Spatial Join, which is available at all license levels and so not restricted as the Pivot Table tool is. I have confirmed that you can Spatial Join a layer to itself.

You would set your reduced point table (one record per node) as your target features and your duplicated node table as the join features. By selecting a Join_One_to_One operation, you can create field map merge rule for each of your other fields using Sum. In the resulting output point table, you should get your one record per node with the sum of all the identical node attributes - in this case 1 if there is an attribute value at all, and 0 if not.

Again, at the help page linked above are Python code samples for both a Window and stand-alone script.