# Writing a table of national park data

I'm working to produce a Lookup table using the Arcpy module that contains descriptive statistics for US National Parks.

The original data, found in tbl, has 12 million records.

Using a Search Cursor,

with arcpy.da.SearchCursor(tbl, ("*"), where_clause) as cursor:
for row in cursor:


I am working through the original data and compiling these statistics using numpy.percentile.

The portion of the code that I am seeking help with improving is the next section, where, for each Park, Year, and Month, I gather those statistics into lists, then create a new record from the lists, then delete the list at the end of each loop.

    parkLatList = []
parkLongList = []
numPplList = []
qnumList = []
qldtList = []
qdurList = []
qdisList = []
numResList = []
sumDurList = []
persNightList = []
rowCount = 0

with arcpy.da.SearchCursor(tbl, ("*"), where_clause) as cursor:
for row in cursor:

numPplList.append(int(row[12]))
parkLatList.append(float(row[15]))
parkLongList.append(float(row[16]))
qnumList.append(int(row[12]))
qldtList.append(int(row[13]))
qdurList.append(int(row[14]))
qdisList.append(float(row[19]))
numResList.append(int(len(row)))
sumDurList.append(int(row[14]))
rowCount += 1

parkLat = parkLatList[0]
parkLong = parkLongList[0]
qnum25 = float(np.percentile(qnumList, 25))
qnum50 = float(np.percentile(qnumList, 50))
qnum75 = float(np.percentile(qnumList, 75))
qldt25 = float(np.percentile(qldtList, 25))
qldt50 = float(np.percentile(qldtList, 50))
qldt75 = float(np.percentile(qldtList, 75))
qdur25 = float(np.percentile(qdurList, 25))
qdur50 = float(np.percentile(qdurList, 50))
qdur75 = float(np.percentile(qdurList, 75))
qdis25 = float(np.percentile(qdisList, 25))
qdis50 = float(np.percentile(qdisList, 50))
qdis75 = float(np.percentile(qdisList, 75))
numRes = rowCount
sumDur = int(np.sum(sumDurList))
persNight = int(np.sum(sumDurList)) * int(np.sum(numPplList))

writer.writerow([park, year, month,
parkLat, parkLong,
qnum25, qnum50, qnum75,
qldt25, qldt50, qldt75,
qdur25, qdur50, qdur75,
qdis25, qdis50, qdis75,
numRes, sumDur, persNight])

del parkLatList
del parkLongList
del numPplList
del qnumList
del qldtList
del qdurList
del qdisList
del numResList
del sumDurList
del persNightList


The end goal is to produce a table that has one row for each Park, Year and Month. So the out table will look something like:

+------------+---------+------------+---------+
|    Park    |  Year   |   Month    |  qsum25 |
+------------+---------+------------+---------+
| Acadia Ntl | 2007    |     1      |   3.25  |
| Acadia Ntl | 2007    |     2      |   2.75  |
+------------+---------+------------+---------+


Is there a more efficient way to collect these descriptive statistics, then add them to a single row, then ensure a list is cleared after an iteration than what I currently have written?

Apologies for the lack of list comprehension - I usually program in R...

import arcpy
import csv
import os
import numpy as np
from datetime import date

arcpy.env.workspace = "F:\\Parks_Paper\\QNBT\\Aggregation.gdb"

tbl = arcpy.ListTables("*")[0]
fieldList = arcpy.ListFields(tbl)

def unique_values(table, field):
with arcpy.da.SearchCursor(table, [field]) as cursor:
return sorted({row[0] for row in cursor})

print "getting unique parks..."
with open('parks.txt') as f:

years = [2007, 2008, 2009, 2010, 2011, 2012, 2013, 2014, 2015, 2016]
months =[1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12]

filename = 'F:\\Parks_Paper\\QNBT\\Analysis\\lookup.csv'
try:
os.remove(filename)
except OSError:
pass

dataCSV = open(filename, 'wb')
writer = csv.writer(dataCSV, dialect="excel")
writer.writerow(['Park', 'Year', 'Month', 'parklat', 'parklon',
'qnum25', 'qnum50', 'qnum75',
'qldt25', 'qldt50', 'qldt75',
'qdur25', 'qdur50', 'qdur75',
'qdis25', 'qdis50', 'qdis75',
'numres', 'sumdur', 'persnight'])

for park in parks:
for year in years:
for month in months:
print "Processing.... \n Park: {0} \n Year: {1} \n Month: {2}".format(park, year, month)
where_clause = """"Park" = '{0}' AND "Year" = {1} AND "Month" = {2}""".format(park, year, month)

parkLatList = []
parkLongList = []
numPplList = []
qnumList = []
qldtList = []
qdurList = []
qdisList = []
numResList = []
sumDurList = []
persNightList = []
rowCount = 0

with arcpy.da.SearchCursor(tbl, ("*"), where_clause) as cursor:
for row in cursor:

numPplList.append(int(row[12]))
parkLatList.append(float(row[15]))
parkLongList.append(float(row[16]))
qnumList.append(int(row[12]))
qldtList.append(int(row[13]))
qdurList.append(int(row[14]))
qdisList.append(float(row[19]))
numResList.append(int(len(row)))
sumDurList.append(int(row[14]))
rowCount += 1

parkLat = parkLatList[0]
parkLong = parkLongList[0]
qnum25 = float(np.percentile(qnumList, 25))
qnum50 = float(np.percentile(qnumList, 50))
qnum75 = float(np.percentile(qnumList, 75))
qldt25 = float(np.percentile(qldtList, 25))
qldt50 = float(np.percentile(qldtList, 50))
qldt75 = float(np.percentile(qldtList, 75))
qdur25 = float(np.percentile(qdurList, 25))
qdur50 = float(np.percentile(qdurList, 50))
qdur75 = float(np.percentile(qdurList, 75))
qdis25 = float(np.percentile(qdisList, 25))
qdis50 = float(np.percentile(qdisList, 50))
qdis75 = float(np.percentile(qdisList, 75))
numRes = rowCount
sumDur = int(np.sum(sumDurList))
persNight = int(np.sum(sumDurList)) * int(np.sum(numPplList))

writer.writerow([park, year, month,
parkLat, parkLong,
qnum25, qnum50, qnum75,
qldt25, qldt50, qldt75,
qdur25, qdur50, qdur75,
qdis25, qdis50, qdis75,
numRes, sumDur, persNight])

del parkLatList
del parkLongList
del numPplList
del qnumList
del qldtList
del qdurList
del qdisList
del numResList
del sumDurList
del persNightList

f.close()

• It's a little bit confusing when you post the same code twice and the first snippet looks exactly like the code in the full function. I suggest only leaving the complete code and removing the first part. Instead you can mark where this part starts with a comment. Mar 29 '18 at 22:37