topic Re: Writing Unique Attribute Field Values to csv in Python Questions

Writing Unique Attribute Field Values to csv

benberman — Thu, 21 May 2015 19:41:52 GMT

I am trying to do something similar to the URL linked below. The first answer writes out all the values for each attribute field, using the attribute names as the column headers.

python - How to export only certain columns to a CSV file in ArcGIS? - Geographic Information Systems Stack Exchange

What I want to be able to do is write out the unique values for each attribute field, using the attribute names as the column headers.

Re: Writing Unique Attribute Field Values to csv

Zeke — Sat, 11 Dec 2021 22:25:20 GMT

You may want to look at sets in python. They essentially take a list of items and return a set of unique items in that list. E.g.

>>> myList = [1, 3, 6, 1, 4, 7, 6)
>>> mySet = set(mylist)
>>> mySet
set([1, 3, 6, 4, 7])
>>>

Maybe post some of your existing code and we can see what you need.

Re: Writing Unique Attribute Field Values to csv

SepheFox — Thu, 21 May 2015 19:58:30 GMT

So what is different between the answer given to that stack exchange question, and what you are trying to do?

import arcpy fc = 'C:\\antenna_shp\\cables.shp' CSVFile = 'C:\\antenna_shp\\FinalOutput.csv' fields = [f.name for f in arcpy.ListFields(fc)] for i,f in enumerate(fields): if f == 'Shape' or f == 'Shape_Length' or f == 'OBJECTID': del fields[i] with open(CSVFile, 'w') as f: f.write(','.join(fields)+'\n') #csv headers with arcpy.da.SearchCursor(fc, fields) as cursor: for row in cursor: f.write(','.join([str(r) for r in row])+'\n')

Re: Writing Unique Attribute Field Values to csv

benberman — Thu, 21 May 2015 20:53:01 GMT

As I have mentioned in my opening post, the SE solution pulls ALL the values in any given attribute field. I am only interested in UNIQUE values.

Re: Writing Unique Attribute Field Values to csv

benberman — Thu, 21 May 2015 20:56:09 GMT

I am familiar with sets in python. I attempted applying it to the solution posted on SE, but because the code writes by row instead of writing by column, applying set only extracts the unique values on each ROW. I would need it to extract the unique values on each COLUMN.

Re: Writing Unique Attribute Field Values to csv

SepheFox — Thu, 21 May 2015 21:05:42 GMT

Sounds a lot like a summary table and then a pivot table.

Re: Writing Unique Attribute Field Values to csv

benberman — Sat, 11 Dec 2021 22:25:22 GMT

The enumerate block does not even need to be in there, just add a conditional at the end of the fields list, per the snippet below. I also added the "set()" method to the final line. Because the code is writing by row instead of by column, it is extracting the unique values by row. I can't seem to get it to extract unique values by column for some reason.

import arcpy
fc = 'C:\\antenna_shp\\cables.shp'
CSVFile = 'C:\\antenna_shp\\FinalOutput.csv'
fields = [f.name for f in arcpy.ListFields(fc) if f.type != 'Geometry']
with open(CSVFile, 'w') as w:
    w.write(','.join(fields)+'\n') 
    with arcpy.da.SearchCursor(fc, fields) as cursor:
        for row in cursor:
            w.write(','.join(set([str(r) for r in row]))+'\n')

Re: Writing Unique Attribute Field Values to csv

benberman — Thu, 21 May 2015 22:37:35 GMT

sure, there are ways to do this through arc tools. I was just interested in more of a pythonic way of doing it.

Re: Writing Unique Attribute Field Values to csv

RebeccaStrauch__GISP — Sat, 11 Dec 2021 22:25:25 GMT

I'm working on something similar. I'm writing the items that I want to a variable. I then check to see it it's unique, and append it to my list if not. the I sort the list. then I write it to my .csv. In short...

outFile = r"d:/_dataTest/myoutput.csv"  

f = open(outFile, "w") 

print ("File is open:  " + str(not f.closed))

f.write("Type, TOCRef, FullPath, newPathn") 

uniqueList = []

for root, dirs, files in os.walk(rootDir): 
    if something: #do stuff and create various anItem's
        anItem = ("blah", "blah", "blah") # creates an item with the fields or variable values I want

    if anItem not in uniqueList:    # created tuple list of uniq combinations
        uniqueList.append(anItem)  #appends the item only if it is completely unique

#once all is done and everything is appended and unique
uniqueList.sort()  # sorts the list, order Type, Path, TOC

for item in uniqueList:
    # creating output as Type, TOC, Path

    f.write("{},{}, {}\n".format(item[0], item[2], item[1]))
f.close()

I trimmed this from a much larger script so obviously it will not run as is. Hope this helps.

Re: Writing Unique Attribute Field Values to csv

DanPatterson_Retired — Sat, 11 Dec 2021 22:25:28 GMT

Numpy has tools to find unique values in files. This example gives you a sniff at what you can do. When working with tabular data, use numpy, it is way faster and there is more that can be done...and yes...it even works with columns.

The example below simply finds the unique values in two columns, one integer, the other string....

import numpy as np
np.set_printoptions(edgeitems=4,linewidth=60,precision=2,suppress=True,threshold=10)
X = [6.0,7,8,3,6,2,9,2,8,3]
Y = [0.0,9,6,2,1,5,4,1,4,5]
Group = [4,2,1,4,3,2,2,3,4,1]
Text = ['a','c','b','a','a','b','a','c','d','b']
dt = [('X','f8'),('Y','f8'),('Group','i4'),('Text','|S5')]
arr = np.array(zip(X,Y,Group,Text),dtype=dt)
print('\nInput array: X,Y,Group,Text,\n{}'.format(arr))
for col in ['Group','Text']:
    arr_where,idx= np.unique(arr[col],return_index=True)
    print('Unique results==> Column: {}\n  Unique: {}\n  Indices: {}'.format(col,arr_where,idx))

give it whirl

Re: Writing Unique Attribute Field Values to csv

BlakeTerhune — Thu, 21 May 2015 23:34:11 GMT

Is this what you're looking for? It might help to see an example.

Input table:

PrimaryKey	SomeAttr	OtherSomething	NewVal
123	ABC	LOW	6.123
456	ABC	MED	15.456
789	ABC	HIGH	24.789
321	CBA	LOW	6.321
654	CBA	MED	15.654

Output CSV:

PrimaryKey	SomeAttr	OtherSomething	NewVal
123, 456, 789, 321, 654	ABC, CBA	LOW, MED, HIGH	6.123, 6.321, 15.456, 15.654, 24.789

Re: Writing Unique Attribute Field Values to csv

DanPatterson_Retired — Sat, 11 Dec 2021 22:25:30 GMT

using my example above, just add the code

Groups = (np.unique(arr['Group'])).tolist()
Classes =  (np.unique(arr['Text'])).tolist()
print('Unique groups: {}\nUnique text: {}'.format(Groups,Classes))

Gives

Unique groups: [1, 2, 3, 4]

Unique text: ['a', 'b', 'c', 'd']

which you could obviously add to a field or dictionary or whatever

Re: Writing Unique Attribute Field Values to csv

Zeke — Fri, 22 May 2015 12:29:44 GMT

Dan's way is no doubt better, but since I don't know numpy, what I meant is to read the values of each column into a list (arcpy.da.SearchCursor), then use set on that list. You can then turn the set back into a list if that is more appropriate for your needs. Repeat for each column. This will give you a a collection of sets (or lists) of unique values for each column.

If you're in an environment where you can use SQL queries, you could also just issue a SELECT DISTINCT command. First link on googling 'python sql'.

Re: Writing Unique Attribute Field Values to csv

BlakeTerhune — Sat, 11 Dec 2021 22:25:33 GMT

You can actually include a DISTINCT statement as a "sql prefix" in the sql_clause parameter of the arcpy.da.SearchCursor(). I've used it before and it works quite well.

sqlprefix = "DISTINCT MyFieldName"
sqlpostfix = "ORDER BY MyFieldName"
myField_Distinct = tuple(
    row[0] for row in arcpy.da.SearchCursor(
        "MyGeodatabaseTableName",
        ["MyFieldName"],
        sql_clause=(sqlprefix, sqlpostfix)
    )
)

You could do that for each field and write it out however you like.

But you did say you didn't want to use arcpy, so... maybe go with Dan's NumPy example.

Re: Writing Unique Attribute Field Values to csv

benberman — Fri, 22 May 2015 17:35:17 GMT

Correct, except I'm trying to write it out each unique value in sucessive rows, one value per cell, instead of all on the same row(multiple values in the same cell).

Re: Writing Unique Attribute Field Values to csv

BlakeTerhune — Sat, 11 Dec 2021 22:25:36 GMT

Here's my solution.

def main():
    import arcpy
    import os
    import csv
    import itertools
    import collections

    # Local variables
    workingDir = r"N:\TechTemp\BlakeT\Work"
    workingGDB = os.path.join(workingDir, "TEMP.gdb")
    inputTable = os.path.join(workingGDB, "TestingFc")
    outputCSV = os.path.join(workingDir, "MyOutput.csv")

    # Get list of usable field names (exclude OID and SHAPE fields)
    fieldNames = [
        f.name for f in arcpy.ListFields(inputTable)
        if f.type != "Geometry" and f.editable == True
    ]

    # Populate dictionary with field names and their distinct values
    fieldVals_Distinct = collections.OrderedDict()
    for field in fieldNames:
        print("Processing {}".format(field))
        ## Get distinct field values and populate dictionary
        sqlprefix = "DISTINCT {}".format(field)
        sqlpostfix = "ORDER BY {}".format(field)
        fieldVals_Distinct[field] = [
            row[0] for row in arcpy.da.SearchCursor(
                inputTable,
                [field],
                sql_clause=(sqlprefix, sqlpostfix)
            )
        ]

    # Create CSV
    ## Using the w mode will overwrite an existing file or create a new one
    with open(outputCSV, "w") as csvfile:
        csvwriter = csv.writer(csvfile, delimiter=',', lineterminator='\n')
        ## Write field name header line
        csvwriter.writerow(fieldVals_Distinct.keys())
        ## Zip distinct values together for all fields and write to CSV
        fieldVals_zipped = itertools.izip_longest(
            *fieldVals_Distinct.values(),
            fillvalue=None
        )
        csvwriter.writerows(list(fieldVals_zipped))


if __name__ == '__main__':
    main()

The ordered dictionary may not be necessary, but from my initial testing, it sometimes changed the order of stuff when zipping all of the values together at the end. Thank you to Dan Patterson for the idea to use zip (or zip_longest in this case).

Re: Writing Unique Attribute Field Values to csv

DanPatterson_Retired — Sat, 11 Dec 2021 22:25:38 GMT

Blake

Very nice job...!!!

I will put forth this little ditty in the hopes that you can see that a search cursor can be completely replaced if desired and everything brought into numpy by using TableToNumPyArray (and its reverse equivalent).

The example produces 3 columns of data... ID,Single,Double with ID being...well...an ID field and the last 2 fields just random letters. I find it easier to use data that I can control for testing. I do like the use of the OrderedDict and Counter. Anyhow, have some fun with this. I will post a follow up on my blog that incorporates time testing, but I thought this would amuse you and others in the interim.

Dan

import numpy as np
import string
import random
from collections import Counter, OrderedDict
np.set_printoptions(edgeitems=4,linewidth=60,precision=2,suppress=True,threshold=10)
# testing stuff...................................................
classes = string.uppercase[:5]  # first 5 letters of the alphabet
N = 100
col1 = [random.choice(classes) for i in range(N)]
col2 = [random.choice(classes)*2 for i in range(N)]
ID = np.arange(N)
data = zip(ID,col1,col2)
arr = np.array(data,dtype=[("ID","int32"),("Single","|S5"),("Double","|S5")])
#.................................................................
# real stuff ... see
#  arr = TableToNumPyArray(in_table,field_names,{where_clause},
#                          {skip_nulls},{null_value})
#
print("\nInput array:\n{}\ndtype: {}".format(arr,arr.dtype))
fields = arr.dtype.names  # array field names
for f in fields:
    if arr.dtype.char == 'S':   # could include other field types@
        c = Counter(arr)
        keys = c.keys()
        od = OrderedDict((x,c) for x in keys)
        print("\nField: {}\n  Counter: {}\n  OrderedDict: {}".format(f,c,od))
    else:
        print("\nField: {}, type: {} not string type... darn ...".format(f,arr.dtype))

Re: Writing Unique Attribute Field Values to csv

benberman — Fri, 22 May 2015 21:39:35 GMT

Thank you, Blake and Dan! This helps a lot!

Re: Writing Unique Attribute Field Values to csv

BlakeTerhune — Fri, 22 May 2015 21:52:04 GMT

I can see the utility of NumPy, but I just haven't learned it yet. I also did not know about Counter. Every little bit helps in learning these new things. Thank you for posting the follow-up Dan Patterson!

Re: Writing Unique Attribute Field Values to csv

DanPatterson_Retired — Fri, 22 May 2015 22:12:31 GMT

Blake...there is some older stuff here from Aug 2014...I am currently updating stuff, so it isn't uptodate

Collections in numpy: producing frequency distributions and graphing