1

I'm attempting to compare entries in a CSV file to entries in a geodatabase using a search cursor through arcpy. I'm not sure if this is the best way to go about a comparison against these two datasets but I do not want to have to write to a new CSV. I have attached an extract from the code I am writing and am getting stuck comparing my two lists to each other. Is there a better way to compare these two datasets?

The data is in rows and I want to find entries where 'ID' is common in both list 1 and list 2 when the 'Status' field in list 2 is 'Complete'. When the common element is not found in both lists I want to print the 'ID' field from list 1.

Haven't made it up to that step yet but am confident to work that section out.

Can't seem to compare a list from an arcpy search cursor to a list from a csvreader and am getting stuck here. Have tried every combination of indenting, calling upon the lists specifically, using the list indexes and other methods in IDLE but have removed these for clarity. Haven't used Python for a number of years but relearning everything. Data snapshots added.

enter image description here

enter image description here

Code below:

import arcpy
arcpy.env.workspace = r'Database Connections\PROD edit Water.sde\Water.WATER.Network'
Water = 'Database Connections\PROD edit Water.sde\Water.WATER.Network\Water.WATER.Main'
B = ''
expression =  "\"ID\" != '" + B + "'"
f = 'ID'
Gday = 'DAY'
Gmonth = 'MONTH'
Gyear = 'YEAR'
Gpage = 'PAGE'
y = []
with arcpy.da.SearchCursor(Water, [f, Gday, Gyear], where_clause = expression ) as cursor:
for row in cursor:
    IDs = row[0]
    DAYs = row[1]
    YEARs = row[2]
    y.append([IDs,DAYs,YEARs])
    for num in [IDs]:
        print DAYs

    with open (r'CSV_of_Job_List.csv', 'r') as f:
        csvReader = csv.reader(f, delimiter=',', quotechar='"')
        header = csvReader.next()
        JobDateIndex = header.index("Entered")
        StatusIndex = header.index("status")
        CaptureDateIndex = header.index("Captured")
        EditIDIndex = header.index("ID")
        dates2 =[]
        for row in csvReader:
            JobDate = row[JobDateIndex]
            Status = row[StatusIndex]
            capturedate = row[CaptureDateIndex]
            JobID = row[EditIDIndex]
            for x in [Status]:
                if x == 'Completed':
                    Status = Status

                else:
                    Status = 0
                    dates2.append([JobID,Status,JobDate,capturedate])
            for num in [JobID]:
                print JobDate
  • This seems more of a Python question than a GIS one. Lists are suboptimal for comparing two sets, whereas sets or dictionaries would be more appropriate. As a rule, you should avoid re-reading files, especially small ones --cache once and be done. – Vince Aug 13 '18 at 11:23
  • Could you add a screenshot of the beginning of the csv file and top of fc attribute table (only relevant fields). – BERA Aug 13 '18 at 12:37
  • Sure thing, attribute table fields attached and csv snapshot attached. I am finding I am able to draw correlations between the two but I believe the comparison of columns isn't correctly calling upon the lines required. Simple If x in [IDs] and x in [JobID]: and similar combinations don't provide me with what I want and can't seem to find similar matches. Will try using dictionaries. The use of pandas is not an option either for this task. – Alex_A Aug 14 '18 at 6:47
1

Not sure what you want to do but try creating a Dictionary from the csv and then use this to compare each row from the feature class:

import arcpy, csv

csvdict = {}
with open(r"C:\Test\joblist.csv") as csvfile:
    r = csv.reader(csvfile)
    header = r.__next__() #or .next()
    for row in r:
        csvdict[row[0]]=row[1:]

with arcpy.da.SearchCursor(r'C:\database.gdb\featureclass', ['ID','DAY','YEAR']) as cursor:
    for ID, day, year in cursor:
        if ID in csvdict:
            if csvdict[ID][1] == 'Completed':
                #do something
        else:
            #do something else

The Dictionary has ID as key and a list of all other attributes as value:

{'21879': ['31/07/2018', 'Completed', '30/08/2018'], '21878': ['31/07/2018', 'Completed', '30/08/2018'],...}

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.