Changing my script to run through one row in a CSV table to multiple rows?

1083
6
01-23-2017 01:30 PM
AndrewRowlands
New Contributor

I've set up an arcpy script that does the following things after the user selects a workspace and a CSV file through a couple Get Parameter as Text options in ArcMap:

  • Creates an XY data file based on data in the CSV file
  • Creates 3 different buffers on that point file
  • Creates 3 different drive time trade areas on that point file
  • Does a spatial append on those buffer files and the drive time files
  • Pull nearby competition within the drive time areas
  • Creates and exports that data to an Excel table

Here is the script.

#import arcpy and arcview library; overwrite any previous output
import arcpy
import arcview
arcpy.env.overwriteOutput = True

#set workspace to GetParameter field
myWorkspace = arcpy.GetParameterAsText(0)
arcpy.env.workspace = myWorkspace
print myWorkspace
print arcpy.env.workspace

#acquire extensions for Business Analyst and Network Analyst for use
arcpy.CheckOutExtension("BusinessPrem")
arcpy.CheckOutExtension("Network")

#set the spatial reference; 4269 is code for GCS_North_American_1983
spatialRef = arcpy.SpatialReference(4269)

#in order to use its tools, add Business Analyst toolbox to script
arcpy.AddToolbox("C:\Program Files (x86)\ArcGIS\Desktop10.3\Business Analyst\ArcToolbox\Toolboxes\Business Analyst Tools.tbx")

#create GetParameter for input csv table that has store location and lat/long
inputTable = arcpy.GetParameterAsText(1)

#assign variables for input table
xCoord = "Long"
yCoord = "Lat"
outLayer = "LocationXY_"

#create XY layer for location
arcpy.MakeXYEventLayer_management(inputTable, xCoord, yCoord, outLayer, spatialRef)
print(arcpy.GetCount_management(outLayer))

#create cursor to access csv table; acquire name of site location
cursor = arcpy.SearchCursor(outLayer)
for row in cursor:
    location = row.Location
print location

#create file geodatabase for site's spatial data
folderPath = myWorkspace
geoDBname = "{0}.gdb".format(location)
print geoDBname
arcpy.CreateFileGDB_management(folderPath, geoDBname)
arcpy.env.workspace = geoDBname
print myWorkspace
print arcpy.env.workspace

#create layer name for converting XY layer to feature class data
fcLocation = outLayer + location
print fcLocation
arcpy.CopyFeatures_management(outLayer, fcLocation)
print ("FC Conv Done")

#create layer name for buffer data
#perform multi-ring buffer analysis on location
bufferString = "Buffer_"
fcBuffer = bufferString + fcLocation
arcpy.MultipleRingBuffer_analysis(fcLocation, fcBuffer, [0.5, 1, 3], "Miles",)
print ("Buff Done")

#create layer name for drive time data
#perform drive time analysis on location
driveTimeString = "DriveTime_"
fcDriveTime = driveTimeString + fcLocation
arcpy.DriveTime_ba(fcLocation, "Location", "All", "2.5; 5; 10", "Minutes", fcDriveTime)
print ("DT Done")

#Create variables for spatial overlay function
inputLayer = "C:\ArcGIS\Business Analyst\US_2015\Data\Demographic Data\esri_bg.bds"
demogFields = "INDMGMT_CY; A25I25_CY; A35I150_CY; A45I75_CY; MEDIA55_CY; \
                MEDDIA35CY; A15NW250CY; AVGNWA65CY; MEDVAL_CY; POPGRWCYFY; \
                N14_EMP; N17_BUS; N18_BUS; N34_EMP"

#create layer name for buffer append; append spatial data to buffers
bufferAppendString = "BufferAppend_"
fcBufferAppend = bufferAppendString + fcLocation
arcpy.SpatialOverlay_ba(inputLayer, fcBuffer, demogFields, fcBufferAppend)
print ("Buff Append Done")

#create layer name for drive time append; append spatial data to drive time
driveTimeAppendString = "DriveTimeAppend_"
fcDriveTimeAppend = driveTimeAppendString + fcLocation
arcpy.SpatialOverlay_ba(inputLayer, fcDriveTime, demogFields, fcDriveTimeAppend)
print ("DT Append Done")

#create layer name for buffer table export; export buffer data to Excel
#change workspace back to folder for Excel data
excelExtension = ".xls"
bufferAppendExcelString = "BufferAppendTable_"
bufferAppendTable = myWorkspace + "/" + bufferAppendExcelString + fcLocation + excelExtension
arcpy.TableToExcel_conversion(fcBufferAppend, bufferAppendTable)
print ("Buffer Table Done")

#create layer name for drive time export; export drive time data to Excel
driveTimeAppendExcelString = "DriveTimeAppendTable_"
driveTimeAppendTable = myWorkspace + "/" + driveTimeAppendExcelString + fcLocation + excelExtension
arcpy.TableToExcel_conversion(fcDriveTimeAppend, driveTimeAppendTable)
print ("Drive Time Table Done")

#create layers for summarize within function; pull number of c-stores within drive time polygon
joinSpatialConvStores = r'Y:\BI\KalibrateCompLoc\KGCompAllMarkets_Aug2016.shp'
targetFeaturePoly = fcDriveTimeAppend
businessPointsString = "CStoreCount_"
outPolyCStorePoints = businessPointsString + fcLocation
arcpy.SpatialJoin_analysis(targetFeaturePoly, joinSpatialConvStores, outPolyCStorePoints)
print ("CStore Count Done")

#create layer name for c-store count export; export c-store count to Excel
convStoreCountString = "CStoreCountTable_"
convStoreCountTable = myWorkspace + "/" + convStoreCountString + fcLocation + excelExtension
arcpy.TableToExcel_conversion(outPolyCStorePoints, convStoreCountTable)
print ("CStore Table Done")

#return Business Analyst and Network Analyst extensions
arcpy.CheckInExtension("BusinessPrem")
arcpy.CheckInExtension("Network")

print ("FINISHED")‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

The point of this script is to run a site through ESRI in order to get all of the key demographic and competition data for a potential location in order to run through a regression model that we use to predict success. 

At this point in time, we can only run a CSV file that has one row (location) through the Python tool. What I want to do is update the script to be iterative and be able to run multiple rows (locations) through the tool at once. Any thoughts on easy changes to make this possible? I've made a few attempts at this and have encountered quite a few errors. I can upload that script if people would like to see what I've tried.

Tags (2)
0 Kudos
6 Replies
DanPatterson_Retired
MVP Esteemed Contributor

pretty hard to dissect but, anything that needs to be done repetively is pulled out as a separate def or put within a while/for loop.  For example, where you extract the data, that should be done once within a searchcursor.  Once collected, process/modify it as you like.  Create the output file, then use an insertcursor to process the data out to a single destination.  It would save wading through the lines if you can step back and identify those aspects.  For exaple lines 35-38 you do have a cursor but you don't really do anything with it.  The only location that will be retained is the last one, since you just cycle through the csv

AndrewRowlands
New Contributor

What I'm struggling with is how to create different locations/feature classes from the CSV table, and then have the script go through the above bullet-list process for each of those stores. So I wrote this script to access the input table with a cursor. But two issues:

  1. I'm not sure if that's the proper way to use the cursor function for this process.
  2. Even if it is, how do I then name each of those rows (locations) in a way to be able to run the process on all 10, 50, whatever number of locations there are?
    #create cursor to access csv table;
    cursor = arcpy.SearchCursor(inputTable)
    for row in cursor:
        location = row.Location
        xCoord = row.Long
        yCoord = row.Lat
    print location
    print xCoord
    print yCoord
0 Kudos
DanPatterson_Retired
MVP Esteemed Contributor

Since you set your environment workspace, then the line that you had...

geoDBname = "{0}.gdb".format(location)

will create the geodatabase, you can employ the same strategy if you decide to change the output locations

>>> location = "here"
>>> outfolder = r"c:\project"
some_file = "{}\{}.csv".format(outfolder, location)
>>> some_file
"c:\\project\\here.csv"‍‍‍‍‍
FC_Basson
MVP Regular Contributor

To get the field value use val = row.getValue(FieldName).

If you want to print the location value for each point, make sure the print statement is inside the for loop.

cursor = arcpy.SearchCursor(inputTable)
for row in cursor:
   location = row.getValue('Location')
   xCoord = row.getValue('Long')
   yCoord = row.getValue('Lat')
   print location
   print xCoord
   print yCoord
AndrewRowlands
New Contributor

I'm still having the same issue even with the code snippet that you just posted. This is the code I created.

#import arcpy and arcview library; overwrite any previous output
import arcpy
import arcview
arcpy.env.overwriteOutput = True

#set workspace to GetParameter field
#myWorkspace = arcpy.GetParameterAsText(0)
myWorkspace = r'X:\Users\Andy.Rowlands\AHR.Data\REF.Requests\January2017\SaltLakeCity_Projections'
arcpy.env.workspace = myWorkspace
print myWorkspace
print arcpy.env.workspace

#acquire extensions for Business Analyst and Network Analyst for use
arcpy.CheckOutExtension("Business")
arcpy.CheckOutExtension("Network")

#set the spatial reference; 4269 is code for GCS_North_American_1983
spatialRef = arcpy.SpatialReference(4269)

#in order to use its tools, add Business Analyst toolbox to script
arcpy.AddToolbox("C:\Program Files (x86)\ArcGIS\Desktop10.4\Business Analyst\ArcToolbox\Toolboxes\Business Analyst Tools.tbx")

#create GetParameter for input csv table that has store location and lat/long
##inputTable = arcpy.GetParameterAsText(1)
inputTable = r"X:\Users\Andy.Rowlands\AHR.Data\REF.Requests\January2017\SaltLakeCity_Projections\SaltLakeCity_latLong.csv"

###set variables for lat/long table
##location = "Location"
##xCoord = "Long"
##yCoord = "Lat"
##outlayer = "LocationXY"

#create cursor to access csv table;
cursor = arcpy.SearchCursor(inputTable)
for row in cursor:
    location = row.getValue('Location')
    xCoord = row.getValue('Long')
    yCoord = row.getValue('Lat')
print location
print xCoord
print yCoord

In rows, 33-41, I'm trying to create the cursor to read the CSV table. My print statements show that the location I'm printing is still just the last row of the table. Here's my print statements.

X:\Users\Andy.Rowlands\AHR.Data\REF.Requests\January2017\SaltLakeCity_Projections
X:\Users\Andy.Rowlands\AHR.Data\REF.Requests\January2017\SaltLakeCity_Projections
Heber_1200_SouthMain
-111.413542
40.492089

How do I go through and create a variable for each location (row)? And then how would I run analysis on each of those locations?

0 Kudos
DanPatterson_Retired
MVP Esteemed Contributor

that is because the last three lines need to be indented... otherwise they will just print the last value that was obtained after the rows have been looped in the cursor... this is a real case of 'being out of the loop'