Calculate statistics for loop

303
2
Jump to solution
12-09-2017 09:08 PM
JasonCarter
New Contributor III

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

0 Kudos
1 Solution

Accepted Solutions
XanderBakker
Esri Esteemed Contributor

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

View solution in original post

2 Replies
XanderBakker
Esri Esteemed Contributor

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
JasonCarter
New Contributor III

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!