-1

I want to merge shapefiles after selection inside a loop and then save results to excel files. However, the codes could save the last result to excel files and can not save each merge file.

import arcpy
arcpy.env.workspace = "D:/STAR/GISpro.gdb"
arcpy.env.overwriteOutput = True #overwrite the exsiting files
selection1 = '"gridcode"<=14.3'
selection2 =  '"gridcode">14.3 AND "gridcode"<=28.6'
selection3 =  '"gridcode">28.6 AND "gridcode"<=42.9'
selection4 =  '"gridcode">42.9 AND "gridcode"<=57.2'
selection5 =  '"gridcode">57.2 AND "gridcode"<=71.5'
selection6 =  '"gridcode">71.5 AND "gridcode"<=85.8'
selection7 =  '"gridcode">85.8 AND "gridcode"<=100'

selections = [selection1, selection2, selection3, selection4,
          selection5, selection6, selection7]
layers = ["wqi_Conshp_clip27171","wqi_south8shp_clip27171"]

fc = "wqi_north8shp_clip27171"  

list = []  

rows = arcpy.SearchCursor(fc)  
for row in rows:  
pop = row.getValue("Shape_Area")  
list.append(pop) 

Sum_Area27171 = sum(list)

for layer in layers:

arcpy.MakeFeatureLayer_management(layer,layer)

count = 0
statisticstables = []
selectionmerge = layer + "_Merge" # UPDATE THIS

for selection in selections:
    count = count + 1
    selectionname = layer + "level" + str(count)

    statisticstables.append(selectionname)
    arcpy.SelectLayerByAttribute_management(layer,'NEW_SELECTION',selection)
    arcpy.Statistics_analysis(layer,selectionname,[["Shape_Area","sum"]])
    arcpy.AddField_management(selectionname,'rate','DOUBLE')
    arcpy.CalculateField_management(selectionname,'rate','!SUM_Shape_Area!/{}'.format(Sum_Area27171),'PYTHON_9.3') 
    print selectionname

try:
arcpy.Merge_management(statisticstables, selectionmerge) #move this line out of the loop. 
print selectionmerge
except ExecuteError as e:
print(e)
print(arcpy.GetMessages())

outpath = "D:\\STAR\\info_data_test_results"
outresult = outpath + "\\" + layer + "_Merge" + ".xls"
arcpy.TableToExcel_conversion(selectionmerge,outresult) 

As you can see, I use "merge_management" to merge all results from selection loop but could just save the last result as an excel file. Could anyone provide some suggestions about this problem?

  • What is selections set to? For tips on writing code snippets see gis.meta.stackexchange.com/questions/4312/… – PolyGeo Apr 5 at 23:18
  • Does selecationmerge exist already, and is is the same type as the output from the selection (stats table)? – smiller Apr 7 at 13:39
  • @smiller, thanks for replying. I use "arcpy.env.overwriteOutput = True" to overwrite the existing results. So, maybe "selectionmerge" is not a problem. Thanks again – Zhenyu Apr 7 at 17:37
  • As is, the layer selection merge is specifically listed in the merge input, so it must exist already during first pass of the loop .Otherwise you can create a list of the intermediate layers and merge only that list after the loop to create each table finishes. – smiller Apr 7 at 18:31
  • @smiller, thanks for your replying. I am very new to Python. Could you mind giving more specific information about how to do that? Really appreciate your help. – Zhenyu Apr 7 at 18:39
2

This is somewhat guessing since your code snippet doesn't include some of the details, however: it looks like you may be trying to merge a dataset with a dataset that doesn't exist already. The first time you go through the loop, the table you've named "selecationmerge" doesn't exist; in fact it's assigned to an empty string. Additionally it's awkward (and, likely slower) to repeatedly merge.

Two options:

  1. build a list of the tables to merge and then merge all at once
  2. Append the second (and subsequent) tables after the first table is created. Note that this would change the input layer, so if you want to keep the output separate initially it's not the best option.

Modifying your code from the question for the first option (merge after loop is finished):

# set your environment settings if you haven't done so already
arcpy.env.workspace = r"path_to_workspace"
count = 0
statisticstables = []
selectionmerge = r"path_to_output_table" # UPDATE THIS
for selection in selections:
    count = count + 1
    selectionname = "stats27171wqi_Conlevel" + str(count)
    print selectionname
    statisticstables.append(selectionname)
    arcpy.SelectLayerByAttribute_management('wqi_Conshp_clip27171','NEW_SELECTION',selection)
    arcpy.Statistics_analysis('wqi_Conshp_clip27171',selectionname,[["Shape_Area","sum"]])
    arcpy.AddField_management(selectionname,'rate','DOUBLE')
    arcpy.CalculateField_management(selectionname,'rate','!SUM_Shape_Area!/{}'.format(Sum_Area27171),'PYTHON_9.3') 

try:
    arcpy.Merge_management(statisticstables, selectionmerge) #move this line out of the loop. 
    print(arcpy.GetMessages())
except ExecuteError as e:
    print(e)
    print(arcpy.GetMessages())

Additional resources:

Merge: http://desktop.arcgis.com/en/arcmap/10.3/tools/data-management-toolbox/merge.htm#GUID-7D7965A6-8486-4AF1-AAFD-C0168DFD1124

Append: http://desktop.arcgis.com/en/arcmap/10.3/tools/data-management-toolbox/append.htm#C_GUID-FA9BFB50-2B63-4355-929C-E22B915A9103

  • thanks for your help. Your answer helps me a lot. I have updated my questions and add another loop outside the original one. Also, I want to save the generated results automatically. @smiller – Zhenyu Apr 8 at 23:42
  • Please revert your question and add the new question to a different thread. We have a one question per post requirement and this will make it harder for people to find and help you. – smiller Apr 9 at 2:52
  • sorry about that. I will post a new question. @smiller – Zhenyu Apr 9 at 20:16
  • Thank you. Don't forget to revert this one, and if a response answered that initial question, remember to mark it as accepted. This will help others who have a similar question find their answers. You can link your new question to this one for reference. – smiller Apr 9 at 20:38
1

It has been a while since I post this question. The script has been updated since then and I post my answer in the following part for other users.

list = []
rows = arcpy.SearchCursor(bufferclip)
SumArea = bufferclip + "Area"

for row in rows:
   area = row.getValue("Shape_Area")
   list.append(area)
   SumArea = sum(list)
print SumArea

arcpy.MakeFeatureLayer_management(bufferclip,bufferclip)

count = 0 #define a number for the name of selected name
statisticstables = []
selectionmerge = bufferclip + "_Merge" 

for selection in selections:
    count = count + 1
    selectionname = bufferclip + "level" + str(count)
    statisticstables.append(selectionname)
    ratename = "rate" + str(point)
    arcpy.SelectLayerByAttribute_management(bufferclip,'NEW_SELECTION',selection)
    arcpy.Statistics_analysis(bufferclip,selectionname,[["Shape_Area","sum"]])

    fields = ['FREQUENCY','Sum_Shape_Area'] #do not set OBJECTID
    if int(arcpy.GetCount_management(selectionname).getOutput(0)) == 0:
       icur = arcpy.da.InsertCursor(selectionname,fields)
       icur.insertRow([0,0])
       del icur
    arcpy.AddField_management(selectionname,ratename,'DOUBLE') #add a new field
    arcpy.CalculateField_management(selectionname,ratename,'!SUM_Shape_Area!/{}'.format(SumArea),'PYTHON_9.3') 
    print selectionname
    arcpy.Merge_management(statisticstables, selectionmerge) 
pathname = "scenairo" + str(i)
outpath = "D:\\WQResults\\25miles\\" + pathname
outresult = outpath + "\\" + selectionmerge + ".xls"
arcpy.TableToExcel_conversion(selectionmerge,outresult)

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.