11 Replies Latest reply on Mar 11, 2011 7:15 PM by gavinjackson

    Get Unique Values

    alejandro194
      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.
        • Re: Get Unique Values
          g.doerffelmysynergis-esridist
          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"?
          • Re: Get Unique Values
            alejandro194
            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.
            • Re: Get Unique Values
              g.doerffelmysynergis-esridist
              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?
              • Re: Get Unique Values
                rfairhur24
                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
                • Re: Get Unique Values
                  alejandro194
                  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.
                  • Re: Get Unique Values
                    cjmorneau
                    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
                    • Re: Get Unique Values
                      foresterguy
                      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).
                      • Re: Get Unique Values
                        curtvprice
                        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:
                        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:

                        # 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
                        • Re: Get Unique Values
                          cjmorneau
                          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 ([ATTACH]5299[/ATTACH]) 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:

                          [ATTACH]5303[/ATTACH].

                          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
                          • Re: Get Unique Values
                            foresterguy
                            First, you need to generate a list of the fields within your table.  That would be something like the following:

                            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:

                            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.
                            • Re: Get Unique Values
                              gavinjackson
                              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.