Hi,
I'm working on a tutorial for ArcGIS and the tutorial calls for me to manually calculate the statistics on 19 different field values. I decided to try out python in arcgis and I'm stuck on using the Statistics_analysis tool during looping.
The table i'm looping over is structured as such:
CaveName, Landuse_type, CaveLength
(u'Alachua', u'URBAN AND BUILT-UP', 73.881816365)
(u'Alachua', u'TRANSPORTATION, COMMUNICATION AND UTILITIES', 64.3660338263)
(u'Alachua', u'UPLAND FORESTS', 114.099104481)
(u'Blue Hole', u'URBAN AND BUILT-UP', 238.19075094)
(u'Bonnet', u'WETLANDS', 20.7191473933)
(u'Bonnet', u'WETLANDS', 369.700021771)
(u'Bonnet', u'AGRICULTURE', 1684.22223123)
(u'Bonnet', u'UPLAND FORESTS', 1286.75131355)
(u'Cathedral-Falmouth', u'URBAN AND BUILT-UP', 86.781214483)
(u'Cathedral-Falmouth', u'UPLAND FORESTS', 625.716794439)
(u'Cathedral-Falmouth', u'UPLAND FORESTS', 810.799478164)
(u'Cathedral-Falmouth', u'UPLAND FORESTS', 1579.5682325)
(u'Cathedral-Falmouth', u'AGRICULTURE', 16.8203685714)
(u'Cathedral-Falmouth', u'RANGELAND', 845.280368334)
(u'Cathedral-Falmouth', u'UPLAND FORESTS', 12.5138760298)
(u'Cathedral-Falmouth', u'WETLANDS', 89.6516950978)
(u'Cathedral-Falmouth', u'URBAN AND BUILT-UP', 36.1451309548)
(u'Cathedral-Falmouth', u'UPLAND FORESTS', 612.425759508)
(u'Cathedral-Falmouth', u'TRANSPORTATION, COMMUNICATION AND UTILITIES', 77.1186805005)
(u'Cathedral-Falmouth', u'UPLAND FORESTS', 452.552322917)
I want to create a table for each cave grouped by Landuse_type with the total sum length of in each Landuse_type
I've created the script below, but how do I use the statistics_analysis tool to generate the output tables for each group as if it was a selected feature?
I tried this code too:
>>> cursor = arcpy.da.SearchCursor("merged_caves_dissolved_identifed",["Cave", "LEVEL1", "Length_m"],sql_clause=(None,"GROUP BY LEVEL1"))
>>> for item in cursor:
... arcpy.Statistics_analysis("merged_caves_dissolved_identifed", "C:\Users\xxx\DesktopGIS_Training\NonpntGrndwaterContCaves_2013\NonpntGrndwaterContCaves_2013\Data\nonpoint_cont\summary_tables\"+item[0], [["Length_m","SUM"]],"LEVEL1")
Thank you!
>>> import arcpy
>>> from arcpy import env
>>> env.workspace = 'C:\Users\xxx\Desktop\GIS_Training\NonpntGrndwaterContCaves_2013\NonpntGrndwaterContCaves_2013\Student\merged_caves_dissolved_identifed.shp'
cursor = arcpy.da.SearchCursor("merged_caves_dissolved_identifed",["Cave", "LEVEL1", "Length_m"])
>>> for item in cursor:
... print(item)
(u'URBAN AND BUILT-UP', u'Alachua', 73.881816365)
(u'TRANSPORTATION, COMMUNICATION AND UTILITIES', u'Alachua', 64.3660338263)
(u'UPLAND FORESTS', u'Alachua', 114.099104481)
(u'URBAN AND BUILT-UP', u'Blue Hole', 238.19075094)
(u'WETLANDS', u'Bonnet', 20.7191473933)
(u'WETLANDS', u'Bonnet', 369.700021771)
(u'AGRICULTURE', u'Bonnet', 1684.22223123)
(u'UPLAND FORESTS', u'Bonnet', 1286.75131355)
(u'URBAN AND BUILT-UP', u'Cathedral-Falmouth', 86.781214483)
(u'UPLAND FORESTS', u'Cathedral-Falmouth', 625.716794439)
(u'UPLAND FORESTS', u'Cathedral-Falmouth', 810.799478164)
(u'UPLAND FORESTS', u'Cathedral-Falmouth', 1579.5682325)
(u'AGRICULTURE', u'Cathedral-Falmouth', 16.8203685714)
(u'RANGELAND', u'Cathedral-Falmouth', 845.280368334)
(u'UPLAND FORESTS', u'Cathedral-Falmouth', 12.5138760298)
(u'WETLANDS', u'Cathedral-Falmouth', 89.6516950978)
(u'URBAN AND BUILT-UP', u'Cathedral-Falmouth', 36.1451309548)
(u'UPLAND FORESTS', u'Cathedral-Falmouth', 612.425759508)
(u'TRANSPORTATION, COMMUNICATION AND UTILITIES', u'Cathedral-Falmouth', 77.1186805005)
(u'UPLAND FORESTS', u'Cathedral-Falmouth', 452.552322917)
(u'AGRICULTURE', u'Cathedral-Falmouth', 157.405965288)
(u'URBAN AND BUILT-UP', u'Cathedral-Falmouth', 54.4353833274)
(u'URBAN AND BUILT-UP', u'Cathedral-Falmouth', 73.3940782003)
(u'URBAN AND BUILT-UP', u'Cathedral-Falmouth', 70.4178076976)
(u'UPLAND FORESTS', u'Cathedral-Falmouth', 279.39446062)
(u'UPLAND FORESTS', u'Cathedral-Falmouth', 41.3328869801)
(u'AGRICULTURE', u'Cathedral-Falmouth', 214.833707042)
(u'URBAN AND BUILT-UP', u'Cathedral-Falmouth', 12.541521943)
(u'UPLAND FORESTS', u'Cathedral-Falmouth', 135.103836698)
(u'URBAN AND BUILT-UP', u'Cathedral-Falmouth', 486.550788021)
(u'UPLAND FORESTS', u'Cathedral-Falmouth', 142.680787059)
(u'URBAN AND BUILT-UP', u'Cathedral-Falmouth', 16.9425655046)
(u'AGRICULTURE', u'Cathedral-Falmouth', 129.110473073)
(u'TRANSPORTATION, COMMUNICATION AND UTILITIES', u'Cathedral-Falmouth', 145.470760609)
(u'RANGELAND', u'Cathedral-Falmouth', 18.1880738434)
(u'URBAN AND BUILT-UP', u'Cathedral-Falmouth', 960.670932053)
(u'UPLAND FORESTS', u'Cathedral-Falmouth', 76.7888326149)
(u'UPLAND FORESTS', u'Cathedral-Falmouth', 560.438274952)
(u'UPLAND FORESTS', u'Cathedral-Falmouth', 283.12967176)
(u'AGRICULTURE', u'Cathedral-Falmouth', 31.538145191)
(u'UPLAND FORESTS', u'Cathedral-Falmouth', 631.854305028)
(u'UPLAND FORESTS', u'Cathedral-Falmouth', 524.892684985)
(u'UPLAND FORESTS', u'Cathedral-Falmouth', 138.962975766)
(u'UPLAND FORESTS', u'Cathedral-Falmouth', 120.312867749)
(u'AGRICULTURE', u'Cathedral-Falmouth', 193.441296057)
(u'WETLANDS', u'Cathedral-Falmouth', 263.173936632)
(u'UPLAND FORESTS', u'Cathedral-Falmouth', 75.775447507)
(u'UPLAND FORESTS', u'Cathedral-Falmouth', 804.998313642)
(u'UPLAND FORESTS', u'Cathedral-Falmouth', 347.932741793)
(u'URBAN AND BUILT-UP', u'Cathedral-Falmouth', 53.3173355116)
(u'URBAN AND BUILT-UP', u'Cathedral-Falmouth', 2871.78168613)
(u'TRANSPORTATION, COMMUNICATION AND UTILITIES', u'Cathedral-Falmouth', 1.0823500953)
(u'UPLAND FORESTS', u'Cathedral-Falmouth', 153.998398239)
(u'URBAN AND BUILT-UP', u'Cathedral-Falmouth', 100.405634445)
(u'URBAN AND BUILT-UP', u'Cathedral-Falmouth', 720.141539321)
(u'WATER', u'Cathedral-Falmouth', 280.813009629)
(u'UPLAND FORESTS', u'Cathedral-Falmouth', 491.3682901)
(u'UPLAND FORESTS', u'Cathedral-Falmouth', 324.136321983)
(u'UPLAND FORESTS', u'Cathedral-Falmouth', 179.56236587)
(u'UPLAND FORESTS', u'Cathedral-Falmouth', 98.3887127433)
(u'URBAN AND BUILT-UP', u'Cathedral-Falmouth', 424.59631834)
(u'UPLAND FORESTS', u'Cathedral-Falmouth', 83.5466919064)
(u'URBAN AND BUILT-UP', u'Cathedral-Falmouth', 660.959518922)
(u'UPLAND FORESTS', u'Cathedral-Falmouth', 357.147811205)
(u'UPLAND FORESTS', u'Cathedral-Falmouth', 194.071472761)
(u'UPLAND FORESTS', u'Cathedral-Falmouth', 136.220821825)
(u'AGRICULTURE', u'Cathedral-Falmouth', 167.76282237)
(u'UPLAND FORESTS', u'Cathedral-Falmouth', 701.91994172)
(u'UPLAND FORESTS', u'Cathedral-Falmouth', 208.448478033)
(u'URBAN AND BUILT-UP', u'Cathedral-Falmouth', 104.02872636)
(u'RANGELAND', u'Cathedral-Falmouth', 276.496386852)
(u'AGRICULTURE', u'Cathedral-Falmouth', 100.629301944)
(u'UPLAND FORESTS', u'Cathedral-Falmouth', 357.845010869)
(u'UPLAND FORESTS', u'Cathedral-Falmouth', 427.406100134)
(u'RANGELAND', u'Cathedral-Falmouth', 316.483328437)
(u'TRANSPORTATION, COMMUNICATION AND UTILITIES', u'Cathedral-Falmouth', 78.8263424611)
(u'UPLAND FORESTS', u'Cathedral-Falmouth', 347.932906818)
(u'TRANSPORTATION, COMMUNICATION AND UTILITIES', u'Cathedral-Falmouth', 37.8474852006)
(u'UPLAND FORESTS', u'Cathedral-Falmouth', 331.05402839)
(u'AGRICULTURE', u'Cathedral-Falmouth', 435.117566809)
(u'TRANSPORTATION, COMMUNICATION AND UTILITIES', u'Cathedral-Falmouth', 268.780346657)
>>>
Solved! Go to Solution.
There are many ways to solve this, but do you really need a table for each landuse type or could the result be a single table and one row for each landuse type? In case a single table would do, you could do this:
import arcpy
import os
fc = r'C:\Users\xxx\Desktop\GIS_Training\NonpntGrndwaterContCaves_2013\NonpntGrndwaterContCaves_2013\Student\merged_caves_dissolved_identifed.shp'
fld_luse = 'LEVEL1'
fld_length = 'Length_m'
out_tbl = os.path.join(os.path.dirname(fc), 'LanduseStats.dbf')
arcpy.Statistics_analysis(in_table=fc, out_table=out_tbl,
statistics_fields="{0} SUM;{0} MEAN;{0} MIN;{0} MAX;{0} RANGE;{0} COUNT;{0} STD".format(fld_length),
case_field=fld_luse)
The result would will be a single table like this:
Adjust the statistic type as you please. The case field in this case will tell the tool to generate a record for each unique value in the specified field. In this case the landuse type.
If you need a table per landuse type, you would have to create a list of unique values (land use types) and loop through them create a feature layer with the where clause, define the output table and perform the summary statistics. Something like this, although I did not test it:
import arcpy
import os
fc = r'C:\Users\xxx\Desktop\GIS_Training\NonpntGrndwaterContCaves_2013\NonpntGrndwaterContCaves_2013\Student\merged_caves_dissolved_identifed.shp'
fld_luse = 'LEVEL1'
fld_length = 'Length_m'
landuses = sorted(list(set([r[0] for r in arcpy.da.SearchCursor(fc, fld_luse)])))
# [u'AGRICULTURE', u'RANGELAND', u'TRANSPORTATION, COMMUNICATION AND UTILITIES', u'UPLAND FORESTS', u'URBAN AND BUILT-UP', u'WATER', u'WETLANDS']
for landuse in landuses:
where = '"{0}" = \'{1}\''.format(fld_luse, landuse)
layer = arcpy.MakeFeatureLayer_management(fc, "lay", where)
out_tbl = os.path.join(os.path.dirname(fc), landuse.split(' ')[0]+'.dbf')
arcpy.Statistics_analysis(in_table=fc, out_table=out_tbl,
statistics_fields="{0} SUM;{0} MEAN;{0} MIN;{0} MAX;{0} RANGE;{0} COUNT;{0} STD".format(fld_length))
Another way is to not create an output table and calculate the values yourself:
import arcpy
fc = r'C:\Users\xxx\Desktop\GIS_Training\NonpntGrndwaterContCaves_2013\NonpntGrndwaterContCaves_2013\Student\merged_caves_dissolved_identifed.shp'
fld_luse = 'LEVEL1'
fld_length = 'Length_m'
data = [[r[0], r[1]] for r in arcpy.da.SearchCursor(fc, (fld_luse, fld_length))]
landuses = sorted(list(set([r[0] for r in data])))
for landuse in landuses:
data = [r[1] for r in data if r[0] == landuse]
print '\n', landuse
print 'items:', len(data)
print 'sum :', sum(data)
print 'mean :', sum(data) / float(len(data))
print 'min :', min(data)
print 'max :', max(data)
... which would print out:
AGRICULTURE
items: 10
sum : 3130.88187758
mean : 313.088187758
min : 16.8203685714
max : 1684.22223123
RANGELAND
items: 4
sum : 1456.44815747
mean : 364.112039367
min : 18.1880738434
max : 845.280368334
TRANSPORTATION, COMMUNICATION AND UTILITIES
items: 7
sum : 673.49199935
mean : 96.2131427643
min : 1.0823500953
max : 268.780346657
UPLAND FORESTS
items: 37
sum : 14051.0723213
mean : 379.758711388
min : 12.5138760298
max : 1579.5682325
URBAN AND BUILT-UP
items: 18
sum : 7045.18274852
mean : 391.399041584
min : 12.541521943
max : 2871.78168613
WATER
items: 1
sum : 280.813009629
mean : 280.813009629
min : 280.813009629
max : 280.813009629
WETLANDS
items: 4
sum : 743.244800894
mean : 185.811200224
min : 20.7191473933
max : 369.700021771
There are many ways to solve this, but do you really need a table for each landuse type or could the result be a single table and one row for each landuse type? In case a single table would do, you could do this:
import arcpy
import os
fc = r'C:\Users\xxx\Desktop\GIS_Training\NonpntGrndwaterContCaves_2013\NonpntGrndwaterContCaves_2013\Student\merged_caves_dissolved_identifed.shp'
fld_luse = 'LEVEL1'
fld_length = 'Length_m'
out_tbl = os.path.join(os.path.dirname(fc), 'LanduseStats.dbf')
arcpy.Statistics_analysis(in_table=fc, out_table=out_tbl,
statistics_fields="{0} SUM;{0} MEAN;{0} MIN;{0} MAX;{0} RANGE;{0} COUNT;{0} STD".format(fld_length),
case_field=fld_luse)
The result would will be a single table like this:
Adjust the statistic type as you please. The case field in this case will tell the tool to generate a record for each unique value in the specified field. In this case the landuse type.
If you need a table per landuse type, you would have to create a list of unique values (land use types) and loop through them create a feature layer with the where clause, define the output table and perform the summary statistics. Something like this, although I did not test it:
import arcpy
import os
fc = r'C:\Users\xxx\Desktop\GIS_Training\NonpntGrndwaterContCaves_2013\NonpntGrndwaterContCaves_2013\Student\merged_caves_dissolved_identifed.shp'
fld_luse = 'LEVEL1'
fld_length = 'Length_m'
landuses = sorted(list(set([r[0] for r in arcpy.da.SearchCursor(fc, fld_luse)])))
# [u'AGRICULTURE', u'RANGELAND', u'TRANSPORTATION, COMMUNICATION AND UTILITIES', u'UPLAND FORESTS', u'URBAN AND BUILT-UP', u'WATER', u'WETLANDS']
for landuse in landuses:
where = '"{0}" = \'{1}\''.format(fld_luse, landuse)
layer = arcpy.MakeFeatureLayer_management(fc, "lay", where)
out_tbl = os.path.join(os.path.dirname(fc), landuse.split(' ')[0]+'.dbf')
arcpy.Statistics_analysis(in_table=fc, out_table=out_tbl,
statistics_fields="{0} SUM;{0} MEAN;{0} MIN;{0} MAX;{0} RANGE;{0} COUNT;{0} STD".format(fld_length))
Another way is to not create an output table and calculate the values yourself:
import arcpy
fc = r'C:\Users\xxx\Desktop\GIS_Training\NonpntGrndwaterContCaves_2013\NonpntGrndwaterContCaves_2013\Student\merged_caves_dissolved_identifed.shp'
fld_luse = 'LEVEL1'
fld_length = 'Length_m'
data = [[r[0], r[1]] for r in arcpy.da.SearchCursor(fc, (fld_luse, fld_length))]
landuses = sorted(list(set([r[0] for r in data])))
for landuse in landuses:
data = [r[1] for r in data if r[0] == landuse]
print '\n', landuse
print 'items:', len(data)
print 'sum :', sum(data)
print 'mean :', sum(data) / float(len(data))
print 'min :', min(data)
print 'max :', max(data)
... which would print out:
AGRICULTURE
items: 10
sum : 3130.88187758
mean : 313.088187758
min : 16.8203685714
max : 1684.22223123
RANGELAND
items: 4
sum : 1456.44815747
mean : 364.112039367
min : 18.1880738434
max : 845.280368334
TRANSPORTATION, COMMUNICATION AND UTILITIES
items: 7
sum : 673.49199935
mean : 96.2131427643
min : 1.0823500953
max : 268.780346657
UPLAND FORESTS
items: 37
sum : 14051.0723213
mean : 379.758711388
min : 12.5138760298
max : 1579.5682325
URBAN AND BUILT-UP
items: 18
sum : 7045.18274852
mean : 391.399041584
min : 12.541521943
max : 2871.78168613
WATER
items: 1
sum : 280.813009629
mean : 280.813009629
min : 280.813009629
max : 280.813009629
WETLANDS
items: 4
sum : 743.244800894
mean : 185.811200224
min : 20.7191473933
max : 369.700021771
Thanks for your response. I would need to keep the tables separate, as I need to generate the statistics for each Cave. This gets me going in the right direction. Thanks!