0

I'm trying to merge duplicates rows using this Python script. After I run the script I get this error and the output just drop the duplicates and it doesn't merge any duplicate row. I want to create a one to many relationship where the FID carries all its attributes in one row. I have look at other solutions. They recommended using Pivot tool but that doesn't solve the duplicate issues.

#MERGE ROW USING CURSOR SEARCH
import arcpy

arcpy.env.workspace = 'C:dummy' # Replace correct geodatabase path
arcpy.env.overwriteOutput = True

# Make a copy of the original feature class/ table
arcpy.Copy_management('attribPivot_4', 'attribPivot_4_flatten')

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

for FID in FIDList:
    #Syntax SearchCursor (in_table, field_names, {where_clause}, {spatial_reference}, {explode_to_points}, {sql_clause})
    # Compile the values in the original feature class
    attList = [0,0,0,0,0,0,0,0,0,0,0,0,0,0,0]
    with arcpy.da.SearchCursor('attribPivot_4', ['PERCENTAGE', 'C5-3', 'M1-4', 'M1-4/R6A', 'M1-4/R6B', 'M1-4/RA7', 'M1-5/R10', 'M1-5/R7-3', 'M1-5/R9', 'M1-6/R10', 'PARK', 'R6A', 'R6B', 'R7A','R9'], "FID = " + str(FID)) as cursor:
        for row in cursor:
            for i in range(0,14):
                if row[i] != 0:
                    attList[i] = row[i]

    # Update the values in the collapse feature class
    with arcpy.da.UpdateCursor('attribPivot_4_flatten', ['PERCENTAGE', 'C5-3', 'M1-4', 'M1-4/R6A', 'M1-4/R6B', 'M1-4/RA7', 'M1-5/R10', 'M1-5/R7-3', 'M1-5/R9', 'M1-6/R10', 'PARK', 'R6A', 'R6B', 'R7A','R9'], "FID = " + str(FID)) as cursor:
        for row in cursor:
            for i in range(0,14):
                row[i] = attList[i]
            cursor.updateRow(row)

Runtime error Traceback (most recent call last): File "", line 24, in RuntimeError: A column was specified that does not exist.

Input

OBJ AREA_GEO  pk_area   FID PERCENTAGE  C5-3    M1-4    M1-4/R6A    M1-4/R6B    M1-4/R7A    M1-5/R10    M1-5/R7-3   M1-5/R9 M1-6/R10    PARK    R6A R6B R7A R9
172 1.113347    1.13    11  100.000009  0   1.113336    0   0   0   0   0   0   0   0   0   0   0   0
42  0.456301    0.43    12  8.909635    0   0.040654    0   0   0   0   0   0   0   0   0   0   0   0
43  0.456301    0.43    12  91.09035    0   0   0   0.415642    0   0   0   0   0   0   0   0   0   0
178 1.210127    1.25    13  100.000018  0   1.210115    0   0   0   0   0   0   0   0   0   0   0   0
126 0.791088    0.81    14  99.999996   0   0.79108 0   0   0   0   0   0   0   0   0   0   0   0
171 1.09726     1.2     15  99.999977   0   1.097248    0   0   0   0   0   0   0   0   0   0   0   0
62  0.508533    0.47    16  99.999976   0   0   0   0.508528    0   0   0   0   0   0   0   0   0   0
66  0.513681    0.4     17  1.785656    0   0   0   0.009172    0   0   0   0   0   0   0   0   0   0
67  0.513681    0.4     17  98.214385   0   0   0.504504    0   0   0   0   0   0   0   0   0   0   0
149 0.876281    0.89    26  2.115458    0   0   0   0   0.018537    0   0   0   0   0   0   0   0   0
150 0.876281    0.89    26  31.28377    0   0   0.274131    0   0   0   0   0   0   0   0   0   0   0
151 0.876281    0.89    26  32.079764   0   0   0   0   0   0   0   0   0   0   0.281106    0   0   0
152 0.876281    0.89    26  34.520978   0   0   0   0   0   0   0   0   0   0.302498    0   0   0   0

This is what I'm trying to get, where all the percentages(the percentages can be in one column or in separate column I don't mind) and all the values for the other columns be in one row. Thus one ID with all its attributes in the same row.

OBJ AREA_GEO    pk_area FID PERCENTAGE  C5-3    M1-4    M1-4/R6A    M1-4/R6B    M1-4/R7A    M1-5/R10    M1-5/R7-3   M1-5/R9 M1-6/R10    PARK    R6A R6B R7A R9
172 1.113347    1.13    11  100.000009  0   1.113336    0   0   0   0   0   0   0   0   0   0   0   0
42  0.456301    0.43    12  8.909635, 91.09035  0   0.040654    0   0.415642    0   0   0   0   0   0   0   0   0   0
178 1.210127    1.25    13  100.000018  0   1.210115    0   0   0   0   0   0   0   0   0   0   0   0
126 0.791088    0.81    14  99.999996   0   0.79108 0   0   0   0   0   0   0   0   0   0   0   0
171 1.09726     1.2     15  99.999977   0   1.097248    0   0   0   0   0   0   0   0   0   0   0   0
62  0.508533    0.47    16  99.999976   0   0   0   0.508528    0   0   0   0   0   0   0   0   0   0
66  0.513681    0.4     17  1.78, 98.2  0   0   0.504504    0.009172    0   0   0   0   0   0   0   0   0   0
149 0.876281    0.89    26  2.18, 31.27, 32.04, 34.58   0   0   0.274131    0   0.018537    0   0   0   0   0.302498    0.281106    0   0   0
2
  • Which ArcGIS version are you using?
    – BERA
    Jun 21, 2018 at 17:47
  • I'm using version 10.4.1
    – Zesima29
    Jun 21, 2018 at 17:56

1 Answer 1

1

I see no obvious reason why Pivot Table or Summary Statistics and then join should not work. But if you want to use Python, pandas is a great module for this kind of task and is included in 10.4:

pandas is an open source, BSD-licensed library providing high-performance, easy-to-use data structures and data analysis tools for the Python programming language.

Code will output a csv which you can join back to your data (after deleting duplicates on FID column). If this is not what you want i will delete my answer.

import arcpy
import pandas as pd

arcpy.env.workspace = 'C:\dummy.gdb'
output = r'C:\flat_csv.csv'

fieldlist = ['FID','PERCENTAGE','C5-3','M1-4','M1-4/R6A','M1-4/R6B','M1-4/R7A','M1-5/R10',
             'M1-5/R7-3','M1-5/R9','M1-6/R10','PARK','R6A','R6B','R7A','R9']

#Create a pandas dataframe using da.SearchCursor
df = pd.DataFrame.from_records(data=arcpy.da.SearchCursor('attribPivot_4', fieldlist), columns=fieldlist)

#Create dictionary of fieldnames and which function to use when grouping them. PERCENTAGE is comma-separated, rest summed
f = dict.fromkeys(fieldlist[2:], 'sum')
f[fieldlist[1]] = lambda x: ", ".join(x.astype(str))

#Groupby FID and export as csv
df.groupby('FID').agg(f).to_csv(path_or_buf=output, index=False)

Example of output: enter image description here

3
  • Okay the answer seems to be going in the right direction but I get this error once I run the code. Runtime error Traceback (most recent call last): File "<string>", line 14, in <module> File "C:\Python27\ArcGIS10.4\lib\site-packages\pandas\core\frame.py", line 801, in from_records first_row = next(data) RuntimeError: A column was specified that does not exist.
    – Zesima29
    Jun 21, 2018 at 18:21
  • Okay but what about the other fields prior to FID (obj, area_geo, pk area) on my code I didn't state them, but I was able to keep them in my output. I added them to the fieldlist but I keep getting the error. fieldlist = ['OBJ','AREA_GEO','pk_area','FID','PERCENTAGE','C5-3', 'M1-4', 'M1-4/R6A', 'M1-4/R6B', 'M1-4/RA7', 'M1-5/R10', 'M1-5/R7-3', 'M1-5/R9', 'M1-6/R10', 'PARK', 'R6A', 'R6B', 'R7A','R9']
    – Zesima29
    Jun 21, 2018 at 18:47
  • Your answer is correct. After I fixed my table names I got this error. line 21typeerror: drop() got an unexpected keyword argument 'columns' but I fixed adding this (after the word code) I don't know how to comment properly code df2.drop("PERCENTAGE", axis=1, inplace=True)
    – Zesima29
    Jun 22, 2018 at 13:09

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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