Using SearchCursor to read two columns and process

Thiru_P · ‎08-16-2018

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"
        
    ‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

Thiru_P · ‎08-16-2018

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]

RandyBurton · ‎08-17-2018

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])
‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

RandyBurton · ‎08-17-2018

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
‍‍‍‍‍‍‍‍‍

DanPatterson_Retired · ‎08-17-2018

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

Thiru_P · ‎08-20-2018

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