3

I get a lot of shapefiles with no metadata. Often there are tons of table columns (fields) with no description of what the column is for or what the coded values mean.

Is there an easy way to export a table so that the domain of unique values are listed per column?

In other words, if a table has two columns, say, Color and Shape, is there a way to export a table that lists all the unique values in Color and all the unique values in Shape?

I know a quick way is to open "Select by Attribute" in ArcMap, select a field and click "Get Unique Values" to see the list of unique attributes within the field. It would be great if there was an easy way to export a table of these unique values by column (field).

To keep it simple, I'm just interested in text or string fields.

5
  • Are you using Python?
    – Aaron
    Apr 20, 2020 at 18:06
  • Which ArcGIS version do you have?
    – BERA
    Apr 20, 2020 at 18:11
  • 2
    There's easier ways than playing around in the UI for value selection to find unique values. There's Summary Statistics (Statistics_analysis), and Frequency (Frequency_analysis ) for those with Advanced licenses, but you can also do this with five lines of DA Search Cursor code using a set to compile a list of unique values or a dictionary to count occurrences.
    – Vince
    Apr 20, 2020 at 18:27
  • I'm not opposed to using Python, though I'm not great at scripting. I'm on ArcGIS Desktop 10.8, or ArcGIS Pro 2.5. Only have a standard license. Thanks!
    – Lance
    Apr 20, 2020 at 20:26
  • For an initial check, open the attribute table, right click on the field, and run Summary.
    – danak
    Apr 24, 2020 at 21:58

3 Answers 3

0

Delete Identical tool will do this for you. Here's the documentation for Desktop and Pro version. Caution: This tool modifies the input data! Make a copy of your dataset before applying this.

enter image description here

1
  • 1
    Just thinking out aloud, whilst this approach would certainly work I would say it's quite a "destructive" approach and only works if the reader has listened to your warning and works from a copy!
    – Hornbydd
    Jan 10, 2022 at 21:10
0

Taking Vince's comment and turning it into code, the following should work in the interactive Python Window in Pro or the Pro Python Command Prompt:

import arcpy
from collections import defaultdict
from pprint import pprint

tbl = # path to table or feature class or name of table view of feature layer

desc = arcpy.Describe(tbl)
field_names = [field.name 
               for field 
               in desc.fields 
               if field.type not in ('OID', 'GlobalID', 'Geometry')]

unique_values = defaultdict(set)
with arcpy.da.SearchCursor(tbl, field_names) as cur:
    for row in cur:
        for i, value in enumerate(row):
            unique_values[field_names[i]].add(value)
            

pprint(unique_values)
-1

The only solution is Python with mighty Pandas:

import pandas as pd
import os, simpledbf
from simpledbf import Dbf5

allFields=pd.DataFrame(columns = ["FOLDER","FILE","FIELD","VALUE"])
# traverse root directory, and list directories as dirs and files as files
i=0
for root, dirs, files in os.walk("C:/scratch/backup"):
    path = root.split(os.sep)
    for file in files:
        if file[-4:]!=".dbf":continue
##        print root, file
        dbf = Dbf5(root+os.sep+file)
        df = dbf.to_dataframe()
        for field in list(df):
            fieldType = df[field].dtypes
            if fieldType!='object':continue
            print field
            for item in df[field].unique():
                allFields.loc[i]=[root,file,field,item]
                i+=1
allFields.to_csv (r'c:\SCRATCH\output.csv', index = None, header=True)

I tested it on folder with few shapefiles, might take a long time in your case. Use filters on output.csv in Excel:

enter image description here

Note you'll need to install simpledbf. My bad, more than 5 lines, learning curve.

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.