+ Reply to Thread
Results 1 to 12 of 12

Thread: Get Unique Values

  1. #1
    Alejandro Dominguez
    Join Date
    Jul 2010
    Posts
    5
    Points
    0
    Answers Provided
    0


    0

    Default Get Unique Values

    I was wondering if there was a way to get the list of unique values from a table like the list in the Select by Attributes using VBA. i have attached a screen shot of what i am looking for.
    Attached Thumbnails Attached Thumbnails Click image for larger version

Name:	selectByAttributes.JPG‎
Views:	491
Size:	35.5 KB
ID:	1838  

  2. #2
    Guenter Doerffel
    Join Date
    Oct 2009
    Posts
    112
    Points
    3
    Answers Provided
    0


    0

    Default Re: Get Unique Values

    Hi Alejandro,

    where and how do you need that list?
    As a GP-Tool for any field you select?

    Just in case: Tables can be very long ... would you want to be able to limit the search to a certain number of records or "wait"?
    Guenter Doerffel
    Head of Marketing
    SynerGIS Informationssysteme GmbH
    Austria

  3. #3
    Alejandro Dominguez
    Join Date
    Jul 2010
    Posts
    5
    Points
    0
    Answers Provided
    0


    0

    Default Re: Get Unique Values

    Thanks for the reply g.doerffel,

    What I am trying to do is a mass merge of feature that have the same TRA code. i can cycle through the code by adding one to a starting number but there is a lot of empty number that are not use and it takes forever to run through a hundred thousand diffferent codes but if i was able to get the list of unique values it would cut that number down to about two hundred. i need the code to work in a marco. any ideas would be welcome.

  4. #4
    Guenter Doerffel
    Join Date
    Oct 2009
    Posts
    112
    Points
    3
    Answers Provided
    0


    0

    Default Re: Get Unique Values

    Hi Alejandro,

    this is actually an easy task with python. There is an element calles a SET in python that is by design a collection of unique values. Before writing buggy code I just quote the ArcGIS Online help here - since there is actually a sample describing exactly what you want to do here

    As mentioned, if you do not run this on millions of records it will be very fast to get a list of unique values that way - now you can cycle through them and do whatever you want.

    Does that solve your problem?
    Guenter Doerffel
    Head of Marketing
    SynerGIS Informationssysteme GmbH
    Austria

  5. #5
    Richard Fairhurst

    Join Date
    Apr 2010
    Posts
    2,155
    Points
    1367
    Answers Provided
    194


    0

    Default Re: Get Unique Values

    Quote Originally Posted by alejandro194 View Post
    Thanks for the reply g.doerffel,

    What I am trying to do is a mass merge of feature that have the same TRA code. i can cycle through the code by adding one to a starting number but there is a lot of empty number that are not use and it takes forever to run through a hundred thousand diffferent codes but if i was able to get the list of unique values it would cut that number down to about two hundred. i need the code to work in a marco. any ideas would be welcome.
    I have attached a text file containing code for a pair of class modules that performs extraction of unique sorted values from a FeatureLayer and a brief example of how you could fill a listbox on a form with it. The Class Modules you must add to your document are called ISorter and Sorter. The Class Modules are designed to be generic so that you can export them and reuse them in other map documents without modification.

    The main interface function of the ISorter class module requires three parameters: a FeatureLayer variable, a string variable holding the valid name of a field in the FeatureLayer, and an integer for the number of digits or character positions to use to right-justify values, like integers, for right-justified sorting (0 will result in left-justified alphabetic sorting). The key ArcObject interface used by the code for extracting unique values is the IDataStatistics interface. You can study the Sorter Class Module code to see how that interface is implemented.

    You should get in the habit of developing Class Modules for generic code that can be reused across many forms and modules. When developing class modules you should create two of them, with one declaring the interface of the class (beginning with an "I" in front of a meaningful name), and the second implementing the interface (using the same name as the interface class but without the "I"). This practice is used in Visual Studio as the fundamental way to implement code reusability, polymorphism and inheritance. VBA will be depricaed after ArcGIS 10 and even with ArcGIS 10 you can only use VBA if you custom install VBA, so you eventually you will need to migrate to Visual Studio to continue to use forms and buttons like you do with VBA. Python may also work, but limits some interface choices at the present time.

    The code below demonstrates how to use the class modules, once you have created them, within a Form in your map. The example fills in a listbox named LstValues with a list of unique sorted values extracted from a FeatureLayer variable you have previously created on a field name you specify with a string variable. I have another pair of class modules that will return a valid feature layer after searching your map document for a layer name that matches the value specified in a layer name string variable. I can provide that module separately if you need it.

    '**** Example of how to use the ISorter class module in a VBA Form to fill a listbox called LstValues.
    '**** The Example assumes you have previously assigned a valid Feature Layer
    '**** to a FeatureLayer variable called pFLayer
    Dim strField as String
    Dim pSorter As ISorter ' Variable defined by the interface class module.
    Dim MyCollection As New Collection
    Dim item as String
    strField = "MYFIELD" ' Substitue a real field name that is in your Feature Layer.
    Set pSorter = New Sorter ' Variable is instantiated with the New keyword to the implementation class module.
    Set MyCollection = pSorter.GetFieldUniqueSort(pFLayer, strField, 4) ' right justifies values with a required 4 digits or characters.
    Set pSorter = Nothing
    Dim i as Integer
    lstValues.Clear
    For Each item In MyCollection
    lstValues.AddItem Item
    Next
    Set MyCollection = Nothing ' This line has an error in the attached text file and should be corrected to read like this.
    '**** End of Example Code.

    I also have another pair of class modules that implements sorting that is very similar to the Advanced Sort option within a TableView in ArcMap. It can use up to 4 fields to perform a multi-field sort of an array with rows holding as many fields as you like. It is limited currently to either ascending or descending sorts on all fields and cannot do mixed sorting, but someday I plan on expanding it.

    I hope this is what you need.

    Rich
    Attached Files
    Last edited by rfairhur24; 07-24-2010 at 08:16 PM.

  6. #6
    Alejandro Dominguez
    Join Date
    Jul 2010
    Posts
    5
    Points
    0
    Answers Provided
    0


    0

    Default Re: Get Unique Values

    Thank you to both of you g.doerffel and rfairhur24 for replying to my post. I will look through both sample and see which is the best fit for my problem. thanks again for the replys.

  7. #7
    Chris Morneau
    Join Date
    Apr 2010
    Posts
    14
    Points
    0
    Answers Provided
    0


    0

    Default Re: Get Unique Values

    Quote Originally Posted by g.doerffel View Post
    Hi Alejandro,

    this is actually an easy task with python. There is an element calles a SET in python that is by design a collection of unique values. Before writing buggy code I just quote the ArcGIS Online help here - since there is actually a sample describing exactly what you want to do here

    As mentioned, if you do not run this on millions of records it will be very fast to get a list of unique values that way - now you can cycle through them and do whatever you want.

    Does that solve your problem?
    Hello Guenter,

    Thank you for sharing the link to the code in "Saving, loading, and recalling your work in the Python window".

    What modifications to the script are needed to list the unique values for more than one field? In other words, I'd like to list all the unique values for Field A, Field B, etc.

    I found a script that lists all the fields in a feature class, but I have not been successful at integrating that code with the unique values script. I'm new at using Python, so any assistance I can receive on this will be greatly appreciated.

    Chris Morneau
    L.A. County Dept of Regional Planning

  8. #8
    Jeff Christiansen
    Join Date
    Apr 2010
    Posts
    18
    Points
    0
    Answers Provided
    0


    0

    Default Re: Get Unique Values

    I have attached a data dictionary program. It is far from "refined" but it does the job I need it to do.

    There are two parts to the program:
    1. Extract a list of fields and the field parameters
    2. Extract unique values for each field

    You can specify a list of fields you want to ignore (e.g., area, perimeter, etc.) because the list of unique values would essentially be every record) or you can specify only particular fields you want to examine.

    Like I said, it's quick and dirty, but it'll get the job done. The outputs are on screen and to 2 textfiles (csv files that can open in excel).
    Attached Files

  9. #9
    Curtis Price

    Join Date
    Oct 2009
    Posts
    1,797
    Points
    874
    Answers Provided
    127


    0

    Thumbs up Re: Get Unique Values

    There's a neat collection of python equivalents to AML that I think is a good general resources for everyone.

    It has an example for the AML LISTUNIQUE function that I think is worth sharing here, with an improvement using that "set" function described above.

    Here's what's in the 10.0 help:
    Code:
    import arcpy
    
    list = []
    rows = arcpy.SearchCursor(specifier)
    for row in rows:
        list.append(row.GetValue(item))
    
    del row
    del rows
    
    list.sort()
    count = len(list)
    pos = 1
    prev = list[0]
    while pos < count:
        if prev == list[pos]:
            del(list[pos])
            count = count - 1
        else:
            prev = list[pos]
            pos = pos + 1
    and here are some improvements:

    Code:
    # 10.0
    import arcpy
    lst = []
    rows = arcpy.SearchCursor(specifier)
    for row in rows:
        lst.append(row.GetValue(item))
    del row, rows
    lst = list(set(lst))
    
    
    # 9.3 --or-- 10.0 
    import arcgisscripting
    gp.arcgisscripting.create(9.3)
    lst = []
    rows = arcpy.SearchCursor(specifier)
    row = rows.Next()
    while row
        lst.append(row.GetValue(item))
        row = rows.Next()
    del row, rows
    lst = list(set(lst))
    
    
    # here it is as a function:
    import arcgisscripting
    gp = arcgisscripting.create(9.3)
    def ListUnique(Layer,Field):
      """
      Create a list of unique values from a table view
    
      Example: ListUnique("table.dbf","POINT_ID")
    
      """  
      lst = []
      Rows = gp.SearchCursor(Layer)
      Row = Rows.Next()
      while Row:
        lst.append(Row.GetValue(Field))
        Row = Rows.Next()
      del Row, Rows
      return list(set(lst)) # return a unique list
    Reference:
    http://www.peterbe.com/plog/uniqifiers-benchmark
    Last edited by curtvprice; 03-04-2011 at 10:16 AM.

  10. #10
    Chris Morneau
    Join Date
    Apr 2010
    Posts
    14
    Points
    0
    Answers Provided
    0


    0

    Default Re: Get Unique Values

    Quote Originally Posted by foresterguy View Post
    I have attached a data dictionary program. It is far from "refined" but it does the job I need it to do.

    There are two parts to the program:
    1. Extract a list of fields and the field parameters
    2. Extract unique values for each field

    You can specify a list of fields you want to ignore (e.g., area, perimeter, etc.) because the list of unique values would essentially be every record) or you can specify only particular fields you want to examine.

    Like I said, it's quick and dirty, but it'll get the job done. The outputs are on screen and to 2 textfiles (csv files that can open in excel).
    This solution appears to be far more complex than what I probably need. Again, what I'm attempting to do is simply derive from a table the field names (or aliases) & the unique values contained in each field (excluding blank cells). So far, I've developed a long-winded sample script (shapes_colors.py) that will accomplish this task, but I have not been able to make it more concise. Right now, my script is constructed of roughly the same block, written out each time for a different field. I've tried incorporating a "for" loop structure into the code, but without much programming experience, I'm having difficulty structuring the script & writing the proper syntax.

    The attached code is setup to run on the following sample table:

    Click image for larger version

Name:	shapes_colors.png
Views:	138
Size:	10.7 KB
ID:	5303.

    I created this "dummy" table to test my code against all the possible field value combinations that I expect to encounter with a much larger table I'm working on (which involves dozens of fields & numerous potential rows). For example, some fields will contain all one value, other fields will have all values different; some fields will include blank records, other fields will be completely blank.

    With so many expected fields, it'll be much better to have a more concise, adaptable code structure, rather than writing out the same code block X number of times & customizing it each time for different input tables. I cannot be certain that the same set of fields (or even the same table) will be processed each time I run this script, so the code needs to be adaptable to different inputs.

    Thanks,

    Chris Morneau
    Los Angeles County Dept. of Regional Planning

  11. #11
    Jeff Christiansen
    Join Date
    Apr 2010
    Posts
    18
    Points
    0
    Answers Provided
    0


    0

    Default Re: Get Unique Values

    First, you need to generate a list of the fields within your table. That would be something like the following:

    Code:
    lFieldList = []
    lFields = arcpy.ListFields(featureclass)
    vField = lFields.Next()
    while vField:
        if vField.Name not in lFieldList:
            lFieldList.append(vField.Name)
        vField = lFields.Next()
    From that piece of code, you now have a list of your fields that you can iterate through. So, your next step would be to loop through that list and process each field, as follows:

    Code:
    for field in lFieldList:
        valueList = []
        rows = arcpy.SearchCursor(featureclass)
        for row in rows:
            if row.Getvalue(field) <> ' ' and row.Getvalue(field) not in valueList:
                valueList.append(row.getValue(field))
        valueList.sort()
        print field,":",valueList
    I put some minor process improvements into the code. You don't need the 'set' command if you ensure the field value is not already in the list before you write to that list. Same for the ' ' check - just don't let the script write that entry into your valueList.

  12. #12
    Gavin Jackson
    Join Date
    Dec 2010
    Posts
    19
    Points
    1
    Answers Provided
    0


    0

    Default Re: Get Unique Values

    To get unique values based on a field in either a feature class or a table, use the FREQUENCY tool. It requires an ArcINFO license but is an extremely valuable tool.

+ Reply to Thread

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts