topic Re: Using SearchCursor to read two columns and process in Python Questions

Using SearchCursor to read two columns and process

Thiru_P — Sat, 11 Dec 2021 13:08:39 GMT

Hi Python experts, I am writing my first python script and would need some advice on how to read and manipulate a list.

On my network drive, I have several folders named after Project and they belong to one or more Years.

I have a FGDB feature class in which I have 2 desired columns FLYING, FILMNAME representing Year, Project respectively.

For e.g.,

Year	Project
1960	A
1972	B
1980	C
1972	D
1980	E
1980	F
1960	G
1972	H
1972	I
1960	J

I would like to read all the rows and create one mosaic data set for each Year and add the Project into each Year's mosaic .

e.g, 1960 will have A;G;J 1972 will have B;D;H

To do this, I would like to get two lists

1. a list of unique Years - to create empty mosaics

2. a list of Projects per Year - to append airphoto folders to each mosaic

Below is the code that does the first list. Any pointers on how to progress further would be much helpful. Thanks in advance.

#import modules
import os
import arcpy
from arcpy import env
from datetime import datetime

#assign variables
in_fc = r'C:\Working\airphoto.gdb\airphoto_index'
fields = ['FLYING','FILMNAME']
gdb = 'C:\Working\images.gdb'
sourceDir = "O:\\airphoto\\75dpi\\"
in_mosaic = 'C:\Working\images.gdb\test'
prjfile = arcpy.SpatialReference(3857)

#create lists
Years = []
Films = []
YearFilmString = []
FilmList = []
#read feature class columns and iterate through rows
with arcpy.da.SearchCursor(in_fc,fields) as cursor:
    for row in cursor:
        #extract year from datetime field
        yearString = (str(row[0])[:4])
        Years.append(yearString)
        filmString = row[1]
        Films.append(filmString)
uniqYears = sorted(set(Years))
uniqYearsList = list(uniqYears)
#for each year, create one mosaic
for i in uniqYearsList:
    x = "C:\Working\images.gdb\T"+i
    if arcpy.Exists(x):
        print "mosaic already exists, skipping..."
        continue
    else:
        arcpy.CreateMosaicDataset_management(gdb,i,prjfile,'','','','')
        print  i, "mosaic created"
        
    ‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

Re: Using SearchCursor to read two columns and process

DanPatterson_Retired — Thu, 16 Aug 2018 08:26:44 GMT

what message did you get... or what happened when you ran the script?

Lines 30-on need to be dedented since you would want to create th mosaic dataset after you have assembled all the parts not while you are within the searchcursor

Re: Using SearchCursor to read two columns and process

Thiru_P — Fri, 17 Aug 2018 01:01:28 GMT

Thanks, Dan. I copied and pasted partial code. My bad. Checked it. I have updated the snippet now.

Re: Using SearchCursor to read two columns and process

DanPatterson_Retired — Fri, 17 Aug 2018 01:23:31 GMT

What happens? anything? no errors or output?

arcpy.CreateMosaicDataset_management(gdb,i,prjfile,'','','','')

do you want the year to be the name (ie 'i') or do you want 'T' in front of the year, since you test 'x' to see if it exists.

Re: Using SearchCursor to read two columns and process

Thiru_P — Fri, 17 Aug 2018 01:30:28 GMT

Hi Dan, it works fine. All the mosaics (T1960, T1972..) are created with prefix "T" just because, the mosaic names cant start with a number.

I have not figured out how to construct the list of films (projects) for each year and this is where I need help from.

I was reading your List Comprehension posts (Great contributions, btw) and could not figure how I can apply it.

Do I use array or dictionary?

Re: Using SearchCursor to read two columns and process

DanPatterson_Retired — Sat, 11 Dec 2021 13:08:42 GMT

year = [1960, 1972, 1980, 1972, 1980, 1980, 1960, 1972, 1972, 1960]
Project = ['A', 'B', 'C', 'D', 'E', 'F', 'G', 'H', 'I', 'J']

keys = sorted(list(set(year)))
a = np.array(list(zip(year, Project)), dtype=[('year', '<i4'), ('project', 'U2')])

out = []
for i in keys:
    kv = [i]
    idx = np.where(a['year'] == i)
    vals = a[idx]['project']
    kv.append(vals.tolist())
    out.append(kv)

out

[[1960, ['A', 'G', 'J']], [1972, ['B', 'D', 'H', 'I']], [1980, ['C', 'E', 'F']]]‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

EDIT

line 0 ADD the following line

import numpy as np

Sorry... been doing a lot of array programming lately so I found this the quickest.

Get the unique years (line 4)

Take your year and projects and form an array (or a list of lists). (line 5)

Then extract the projects for each year in the keys (line 8).

kv is just a small list to append the project list.

do what you want with the list of lists... you could make a dictionary if needed

Re: Using SearchCursor to read two columns and process

RandyBurton — Sat, 11 Dec 2021 13:08:45 GMT

Here's another of many ways:

cursor = [ # rows in your geodatabase
    [ '1960-01-01', 'A' ], 
    [ '1972-01-01', 'B' ],
    [ '1980-01-01', 'C' ], 
    [ '1972-02-01', 'D' ], 
    [ '1980-02-01', 'E' ], 
    [ '1980-03-01', 'F' ], 
    [ '1960-02-01', 'G' ], 
    [ '1972-03-01', 'H' ],
    [ '1972-04-01', 'I' ],
    [ '1960-03-01', 'J' ]
    ]

d = {} # empty dictionary 

# with arcpy.da.SearchCursor(in_fc,fields) as cursor:
#    for row in cursor:
for row in cursor:
    if row[0][:4] not in d:
        d[row[0][:4]] = [row[1]]
    else:
        d[row[0][:4]].append(row[1])

print d
# {'1960': ['A', 'G', 'J'], '1972': ['B', 'D', 'H', 'I'], '1980': ['C', 'E', 'F']}

for k in d.keys():  # access years and films
    print k
    print d[k]
'''
1960
['A', 'G', 'J']
1972
['B', 'D', 'H', 'I']
1980
['C', 'E', 'F']
'''‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

Re: Using SearchCursor to read two columns and process

Thiru_P — Fri, 17 Aug 2018 05:30:53 GMT

Hi Dan, tried this. I am getting empty list for the Projects.

[['1944', []], ['1948', []], ['1956', []], ['1957', []], ['1958', []], ['1959', []], ['1962', []], ['1963', []], ['1964', []], ['1968', []], ['1969', []], ['1970', []], ['1972', []], ['1973', []], ['1974', []], ['1976', []], ['1977', []], ['1978', []], ['1979', []], ['1980', []], ['1981', []], ['1982', []], ['1983', []], ['1984', []], ['1985', []], ['1986', []], ['1988', []], ['1989', []], ['1990', []], ['1991', []]]

Here's a subset of my FILMS list-

5042, 2985, 2985, 2985, 2985, 3021, 3021, 3021, 4095, 4095, 4095, 4095, 3604, 2020, 3930, 2004, 798, 798, 2584, 2593, 3825, 3825, 2618, 27027, 572, 798, 798, 798, 798, 1317, 1317, 1317, 3825, 890, 890, 798, 798, 3614, 1317, 1317]

I suspected it might be due to array data type U2 being wrong. Tried substituting with string equivalent S10. Did not work. I got an empty list for the Projects.

Will post the python code as a reply to the original question.

Re: Using SearchCursor to read two columns and process

Thiru_P — Sat, 11 Dec 2021 13:08:48 GMT

Dan's method:

#import modules
import os
import arcpy
from arcpy import env
from datetime import datetime
import numpy as np

#assign variables
in_fc = r'C:\Working\airphoto.gdb\airphoto_index'
fields = ['FLYING','FILM']
gdb = 'C:\Working\images.gdb'
sourceDir = "O:\\airphoto\\75dpi\\"
in_mosaic = 'C:\Working\images.gdb\test'
prjfile = arcpy.SpatialReference(3857)


#create lists
Years = []
Films = []
YearFilmString = []
FilmList = []
#read feature class columns and iterate through rows
with arcpy.da.SearchCursor(in_fc,fields) as cursor:
    for row in cursor:
        #extract year from datetime field
        yearString = (str(row[0])[:4])
        Years.append(yearString)
        filmString = row[1]
        Films.append(filmString)
keys = sorted(list(set(Years)))
a = np.array(list(zip(Years, Films)), dtype = [('Years', '<i4'), ('Films', 'U2')])
out=[]
for i in keys:
    kv = [i]
    idx = np.where(a['Years'] == i)
    vals = a[idx]['Films']
    kv.append(vals.tolist())
    out.append(kv)
print out‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

Re: Using SearchCursor to read two columns and process

Thiru_P — Sat, 11 Dec 2021 13:08:50 GMT

Hi Randy, thanks for the reply. I tried your code. It works but I have got duplicate Projects.

#import modules
import os
import arcpy
from arcpy import env
from datetime import datetime
import numpy as np

#assign variables
in_fc = r'C:\Working\airphoto.gdb\airphoto_index'
fields = ['FLYING','FILM']
gdb = 'C:\Working\images.gdb'
sourceDir = "O:\\airphoto\\75dpi\\"
in_mosaic = 'C:\Working\images.gdb\test'
prjfile = arcpy.SpatialReference(3857)
d = {}
#create lists
Years = []
Films = []
YearFilmString = []
FilmList = set()
#read feature class columns and iterate through rows
with arcpy.da.SearchCursor(in_fc,fields) as cursor:
    for row in cursor:
        #extract year from datetime field
        if str(row[0])[:4] not in d:
            d[str(row[0])[:4]] = [row[1]]
        else:
            d[str(row[0])[:4]].append(row[1])
print d

'''
for k in d.keys():
    print k
    print d
'''‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

Result:

'1974': [3005, 3008, 3008, 3008, 3008, 3005, 3005, 3005, 3005, 3008, 3008, 3006, 3006, 3004, 3004, 3004, 3004, 3004, 3004, 27027, 2775, 3021, 3021, 3005, 3005, 3008, 3008, 3008, 3008, 3008, 3005, 3005, 3008, 3008, 3008, 3006, 3006, 3004, 3004, 3004, 3021, 3021, 2775, 3005, 3008, 3005, 3005, 3004, 3004, 3004, 3005, 3005, 3005, 3005, 3005, 3008, 3008, 3008, 3005, 3005, 3005, 3005, 3005, 3005, 3008, 3008, 3008, 3008, 3008, 3006, 3004, 2985, 3021, 2775, 3005, 3005, 3005, 3008, 3008, 3008, 3008, 3008, 3005, 3005, 3005, 3005, 3005, 3006, 3004, 3004, 3004, 3004, 3005, 2983, 3021, 3021, 27027, 2775, 2775, 3005, 3005, 3005, 3004, 2983, 2983, 3005, 3005, 3008, 3008, 3008, 3008, 3008, 3008, 3005, 3005, 3005, 3005, 3005, 3005, 3005, 3005, 3008, 3006, 3006, 3004, 3004, 3004, 3004, 3004, 3004, 3008, 3005, 2985, 2985, 2983, 3021, 3021, 2775, 3005, 3005, 3008, 3008, 3005, 3008, 3008, 3008, 3008, 3004, 3004, 2983, 3005, 3005, 3005, 3005, 3008, 3008, 3008, 3008, 3008, 3005, 3005, 3006, 3006, 3006, 3006, 3004, 3004, 3004, 2985, 3005, 3005, 3005, 3005, 3005, 3008, 3008, 3008, 3008, 3008, 3008, 3008, 3008, 3005, 3005, 3005, 3005, 3005, 3005, 3008, 3006, 3006, 2985, 2985, 2985, 2985, 3021, 3021, 3021, 27027, 3021],

'1973': [2618, 2618, 2618, 2618, 2618, 2618, 2618, 2618, 2618, 2618, 2618, 2618, 2618, 2618, 2618, 2618, 2618, 2618, 2618, 2618, 2618],

'1972': [2626, 2626, 2626, 2626, 2626, 2626, 2626, 2626, 2626, 2626, 2626, 2626, 2626, 2626, 2626, 2626, 2626, 2626, 2626, 2626, 2626, 2626, 2626, 2626, 2626, 2626, 2626, 2626, 2626, 2626, 2626, 2626, 2626, 2626, 2626, 2626, 2626, 2626, 2626, 2626, 2626, 2626, 2626, 2626, 2626, 2626, 2626, 2626, 2626, 2626, 2626, 2626],

'1970': [2183, 2183, 2183, 2183, 2183, 2183, 2183, 2183]}

I am trying to get unique values for each key. e.g.,

'1974': [3008, 3005, 3006, 3004, 27027, 2775, 3021, 2985, 2983]

Re: Using SearchCursor to read two columns and process

DanPatterson_Retired — Fri, 17 Aug 2018 06:43:10 GMT

a = np.array(list(zip(Years, Films)), dtype = [('Years', '<i4'), ('Films', 'U2')])

you made years a string so the dtype should be 'U4' or its equivalent if using python 2.7.

Similarly your 'Films' is now a number and would need to be '<i4'.

If that isn't the case, post a list of the way that each is ie string or number

Re: Using SearchCursor to read two columns and process

RandyBurton — Sat, 11 Dec 2021 13:08:53 GMT

Using set on a list will give unique elements:

d = {'1974': [3005, 3008, 3008, 3008, 3008, 3005, 3005, 3005, 3005, 3008, 3008,
              3006, 3006, 3004, 3004, 3004, 3004, 3004, 3004, 27027, 2775, 3021,
              3021, 3005, 3005, 3008, 3008, 3008, 3008, 3008, 3005, 3005, 3008,
              3008, 3008, 3006, 3006, 3004, 3004, 3004, 3021, 3021, 2775, 3005,
              3008, 3005, 3005, 3004, 3004, 3004, 3005, 3005, 3005, 3005, 3005,
              3008, 3008, 3008, 3005, 3005, 3005, 3005, 3005, 3005, 3008, 3008,
              3008, 3008, 3008, 3006, 3004, 2985, 3021, 2775, 3005, 3005, 3005,
              3008, 3008, 3008, 3008, 3008, 3005, 3005, 3005, 3005, 3005, 3006,
              3004, 3004, 3004, 3004, 3005, 2983, 3021, 3021, 27027, 2775, 2775,
              3005, 3005, 3005, 3004, 2983, 2983, 3005, 3005, 3008, 3008, 3008,
              3008, 3008, 3008, 3005, 3005, 3005, 3005, 3005, 3005, 3005, 3005,
              3008, 3006, 3006, 3004, 3004, 3004, 3004, 3004, 3004, 3008, 3005,
              2985, 2985, 2983, 3021, 3021, 2775, 3005, 3005, 3008, 3008, 3005,
              3008, 3008, 3008, 3008, 3004, 3004, 2983, 3005, 3005, 3005, 3005,
              3008, 3008, 3008, 3008, 3008, 3005, 3005, 3006, 3006, 3006, 3006,
              3004, 3004, 3004, 2985, 3005, 3005, 3005, 3005, 3005, 3008, 3008,
              3008, 3008, 3008, 3008, 3008, 3008, 3005, 3005, 3005, 3005, 3005,
              3005, 3008, 3006, 3006, 2985, 2985, 2985, 2985, 3021, 3021, 3021,
              27027, 3021],
     '1970': [2183, 2183, 2183, 2183, 2183, 2183, 2183, 2183]}

for k in d.keys():
    print k, set(d)

# 1974 set([3008, 2983, 2985, 3021, 27027, 2775, 3004, 3005, 3006])
# 1970 set([2183])
‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

Re: Using SearchCursor to read two columns and process

RandyBurton — Sat, 11 Dec 2021 13:08:56 GMT

Should you need unique items sorted:

for k in d.keys():
    print "{}: ".format(k),
    for i in sorted(set(d[k])):
        print i,
    print

# results
# 1974:  2775 2983 2985 3004 3005 3006 3008 3021 27027
# 1970:  2183
‍‍‍‍‍‍‍‍‍

Re: Using SearchCursor to read two columns and process

DanPatterson_Retired — Sat, 11 Dec 2021 13:08:58 GMT

year = ['1960', '1972', '1980', '1972', '1980', '1980', '1960', '1972', '1972', '1960']
Project = ['A', 'B', 'C', 'D', 'E', 'F', 'G', 'H', 'I', 'J']
a = np.array(list(zip(year, Project)))
keys = sorted(list(set(year)))
out = []
for i in keys:
    idx = np.where(a[:, 0] == i)[0]
    vals = a[idx, 1].tolist()
    out.append([i, vals])
    

out

[['1960', ['A', 'G', 'J']],
 ['1972', ['B', 'D', 'H', 'I']],
 ['1980', ['C', 'E', 'F']]]‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

easier when the dtypes are the same

Re: Using SearchCursor to read two columns and process

Thiru_P — Tue, 21 Aug 2018 03:55:28 GMT

Thanks Dan Patterson‌ for your help. I could not mark two entries as Correct Answers. I am choosing to go with dictionary approach in this case. Once I finish my script, I will add it here so it might benefit someone else.

This is what I love about GeoNet. I am not alone when it comes to learning. Hopefully, I can share my python expertise when I learn enough. Regards, Thiru