4

I really need some guidance on how to create a python script with arcpy to:

1) Search through a table for unique values in a field.

2) For the first row in each unique valueset, insert that row into a new empty table.

I have been trying to get this work with the da.SearchCursor and da.InsertCursor, but can't really get 2) to work..

The reason why I want to do this is that I have a table with two fields; "wellbore_name" is the ID for wells, "Units" are concatenated geological units for the specific well. There are several rows for each unique ID, and I want an output table with as many rows as unique IDs. Since the units are concatenated I only need one row for each ID.

enter image description here

This is probably easy for someone fluent in cursors, but alas I am not. Hopefully someone out there can help me with this :-)

  • 2
    I would do this using a single tool: Summary Statistics – PolyGeo Apr 23 '15 at 9:18
  • Hi, I currently am using Summary Statistics to do this, but but since the output table gets "FIRST_" added to the fieldname I need to explore other options. But thanks for the tip :-) – NinaWH Apr 24 '15 at 10:22
5

I would take a different approach and use Delete Identical (Data Management). The following script creates a copy of your table or FC and then removes the duplicate rows in that copy.

import arcpy

table = r'C:\test\temp.gdb\table'
copy = r'C:\test\temp.gdb\table2'

# Create a copy of your table
arcpy.CopyFeatures_management(table, copy)

# Delete duplicate rows based on the field "test"
arcpy.DeleteIdentical_management(copy, "test")
  • Hi, this sounds perfect- unfortunately you need an ArcGIS Desktop Advanced license to use this tool.. But thanks anyway! – NinaWH Apr 24 '15 at 10:50
  • 1
    @NinaWH It would be helpful to include your license type in the main question to better guide potential answers. – Aaron Apr 24 '15 at 11:40
  • Yup, good tip :) Thanks! I am used to working with an Advanced license so it's usually not an issue. I think the Delete Identical would be the best solution, if i had the license. – NinaWH Apr 24 '15 at 12:35
3

Vamping on PolyGeo's comment, I believe that the Summary Statistics tool would work easily. If I understand correctly you just need a list of unique values for the "Units" field. You can set up the tool like this (see the screenshot) except instead of using the "Name" field, you would use "Units". [Be sure to add Wellbore_Name w/ stat type "FIRST" as one of your statistics fields.] You will get a red error circle until you choose the statistic type. I think "FIRST" would be correct here.

Aaron's comment is correct so I updated the screenshot. You actually need to use the "Units" as the case field and use the Wellbore_Name with statistic type set to First. This is also just going to give you a table not your feature class (which is probably what you want anyway).

enter image description here

  • Hi, yes I have been using the Summary Statistics for this, but it is not optimal as it alters the fieldnames and creates frequency fields as well. Otherwise it would be perfect for the job. – NinaWH Apr 24 '15 at 10:27
2

As mentioned by several other users here. Using Search and Insert cursors may not be necessary, but if for some reason you would still like to see how it is done, the following script should do everything you need. You will need to setup parameters for the script tool. I'll assume for now that you already know how to do this.

Parameters:

  1. (0) Input Features - Feature Class
  2. (1) Output Location - Workspace
  3. (2) Output Name - String

Updated Script:

import arcpy, os
from arcpy import env

arcpy.env.overwriteOutputs = True

inFC = arcpy.GetParameterAsText(0)
outLoc = arcpy.GetParameterAsText(1)
outName = arcpy.GetParameterAsText(2)

fieldList = ["WELLBORE_NAME", "Units"]

desc = arcpy.Describe(inFC)
geoType = desc.shapeType.upper()
spatRef = arcpy.Describe(inFC).spatialReference

arcpy.CreateTable_management(outLoc, str(outName), inFC)  

newFeature = os.path.join(outLoc, str(outName))

dataArray = []

insertCursor = arcpy.da.InsertCursor(newFeature, (fieldList))

with arcpy.da.SearchCursor(inFC, (fieldList)) as cursor:
    for row in cursor:
        if (row[0] not in dataArray):                          
            dataArray.append(row[0])
            insertCursor.insertRow((row[0], row[1]))


    del row
del cursor

This could be customized to meet the needs of other datasets by adding the appropriate fields and row references.

  • Hi, great- exactly what i was looking for. Thanks! It does seem to do the job, but I do not get the correct amount of rows in the output table (1001 rows) when comparing to Summary Statistics (1467 rows) result? – NinaWH Apr 24 '15 at 10:49
  • The problem might have to do with my understanding of your question. It sounds like you want all the unique rows from the table, correct? Another thought: if you select the use geometry option you will probably get a lot more records in the final output unless some of your rows share the same geometry. How many features are in the original dataset? – GeoJohn Apr 24 '15 at 13:44
  • Hi! The original dataset is a geodatabase table with approx 29000 rows. There are 1467 unique wellbore_name (IDs), so for the result I would want a table with 1467 rows- the first row for each unique wellbore_name (ID). I only need the non-geometry option for this. So from the example screen shot my result would be a table with three rows. I am using Summary Statistics for this now, but since that tool changes the names of the output fields and adds some other frequency fields as well I am looking for a better way to do this. Thanks a lot for helping out with this, I really appreciate it! :-) – NinaWH Apr 27 '15 at 6:41
  • Hi again, and thanks for the suggestion. I implemented the changes, but now I am getting 29000 rows in the output table (which is the same as the input table). It also spent a lot of time searching through the rows, and got slower and slower. I must be doing something wrong, but what? – NinaWH Apr 29 '15 at 9:29
  • @NinaWH I realized my suggested changes were incorrect. I have edited my answer, and simplified it. It should be correct now. Sorry for the confusion. – GeoJohn Apr 29 '15 at 19:47
0

1) Create uniq list of values with python set

uniq = set() name_field = "test_field" with arcpy.da.SearchCursor(your_table, [name_field]) as sCur: for row in Scur: set.add(row[0])

2) For uniq values find first id (using OID@ token to access id value of row):

for uniq_value in uniq: with arcpy.da.SearchCursor(your_table, [name_field,"OID@"])as sCur: # select min value of id for each uniq value print min([row[1] for row in sCur if row[0]==uniq_value])

In block 2 you can paste InsertCursor for adding this values to new table. If you need geometry in out table, you can access geometry with token "SHAPE@" and paste it in InsertCursor too.

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.