Take the 2-minute tour ×
Geographic Information Systems Stack Exchange is a question and answer site for cartographers, geographers and GIS professionals. It's 100% free, no registration required.

Similar to this post (ArcGIS median value from a layer) I am looking for a toolbox to get the median value for a data field by a case. For example, say I have dataset with percent tree cover for each county for each state; I would like to summarize the data by calculating the median percent forest cover by state.

The only reason the answer to the previous post doesn't work for my needs is that I am using ArcGIS 10.1 and the toolbox/script (link below) doesn't work in 10.1 I have attempted to update the script associated with the previous tool for use in 10.1, but haven't been successful. Is there an easy way to do this? The tool created by Ryan Dalton is available here: http://resources.arcgis.com/gallery/file/geoprocessing/details?entryID=A0CD1751-1422-2418-882E-001EE0DC0D35 ....

share|improve this question

2 Answers 2

up vote 2 down vote accepted

I got the toolbox to work in 10.1 by updating the script. Your comments were helpful Ryan, so thanks for those, as were the links you provided.

If you use the code below within the existing toolbox created by Ryan Dalton (http://resources.arcgis.com/gallery/file/geoprocessing/details?entryID=A0CD1751-1422-2418-882E-001EE0DC0D35) it should work in 10.1. I tested the tool and it worked for me. The only thing I have noticed is that the case field has to be a string and cannot be too long. Plenty of ways to workaround that though. Enjoy!

#-------------------------------------------------------------------------------
# Name:        MedianStats.py
# Purpose:     Calculate the Median value from a data layer or table, and save
#              the results in a separate table. Allows you to calculate the median
#              value for a case. Updated for use in 10.1
#
# Author:      P. Johnson modified a script written by R.Dalton for use in 10.1. 
#              R. Dalton's script was modified version of a script by C.Denninger. 
#              C. Denninger's script was a modified version of a script by Chris Snyder     
# 
# Modified:     July 3, 2013


#-------------------------------------------------------------------------------
#!/usr/bin/env python

def uniqify(seq):
    # Not order preserving
    uset = set(seq)
    return list(uset)


import sys, arcpy, string, os


# Load required toolboxes...

inputTable = arcpy.GetParameterAsText(0)
statField = arcpy.GetParameterAsText(1) #this needs to be a numeric field
caseFields = arcpy.GetParameterAsText(2) #this needs to be a double or float
outTable = arcpy.GetParameterAsText(3)

arcpy.AddMessage("Analyzing median values for Table = " + inputTable + ":")
arcpy.AddMessage("  Statistics field = " + statField + " & Case field = " + caseFields)



# Needed outTable Schema = OID,caseField,Frequency,Med_statField

outFolder = os.path.dirname(outTable)
outFile = os.path.basename(outTable)
arcpy.CreateTable_management(outFolder, outFile, "", "")

caseList = string.split(caseFields,";")
if len(caseList) >1:
    #arcpy.AddMessage("more than 1 case fields")
    for cI in caseList:
        ##Output table field name
        outCaseField = cI[:10]
        ##Only add the case field if it was selected
        if outCaseField <> "":
            arcpy.AddField_management(outTable, outCaseField, "TEXT", "", "", "30", "", "NULLABLE", "NON_REQUIRED", "")

else:
    ##Output table fields
    outCaseField = caseFields[:10]

    ##Only add the case field if it was selected
    if outCaseField <> "":
        arcpy.AddField_management(outTable, outCaseField, "TEXT", "", "", "30", "", "NULLABLE", "NON_REQUIRED", "")


##Add the following fields in all cases
outCountField = "Count"
arcpy.AddField_management(outTable, outCountField, "LONG", "", "", "", "", "", "NON_REQUIRED", "")
outMedField = "MED_" + statField[:5]
arcpy.AddField_management(outTable, outMedField, "DOUBLE", "", "", "9", "", "", "NON_REQUIRED", "")

##Delete the "Field1" field created by default
arcpy.DeleteField_management (outTable, "Field1")


if caseFields == "":
    # Read the values from the Source Table
    valueList = []
    searchRows = arcpy.SearchCursor(inputTable)
    for searchRow in searchRows:
       searchRowValue = searchRow.getValue(statField)
       if searchRowValue == None:
          pass #don't add a null value to the list!
       else:
          valueList.append(searchRowValue)
    del searchRow
    del searchRows


    valueList.sort()
    listLength = len(valueList)
    if listLength == 0:
       print "Every value was null! Exiting script..."; sys.exit()
    elif listLength % 2 == 0: #even, so get the mean of the 2 center values
       ##medianValue = (valueList[listLength / 2]) + valueList[listLength / 2 - 1]) / 2.0
       medianValue = valueList[listLength / 2]
    else: #odd, so it's easy!
       medianValue = valueList[listLength / 2]



    #Write the values to the output table
    listCount = listLength

    insertRows = arcpy.InsertCursor(outTable)
    insertRow = insertRows.NewRow()
    insertRow.setvalue(outCountField, listCount)
    insertRow.setvalue(outMedField, medianValue)
    insertRows.InsertRow(insertRow)

    del valueList
    del listCount
    del medianValue





elif caseFields <> "":

    # Read the values from the Source Table
    cList = []
    caseRows = arcpy.SearchCursor(inputTable)
    caseRow = caseRows.next()
    while caseRow:
        cFList = ""
        for cF in string.split(caseFields,";"):
            ##arcpy.AddMessage(cF)
            caseRowValue = caseRow.getValue(cF)
            ##arcpy.AddMessage(caseRowValue)

            if caseRowValue == None:
               pass #don't add a null value to the list!
            else:
               cFList = cFList + ";" + caseRowValue

        cFList = cFList.strip(";")
        ##arcpy.AddMessage("list of data=" + cFList)
        cList.append(cFList)
        caseRow = caseRows.next()
    del caseRow
    del caseRows


    caseVals = uniqify(cList)
    caseVals.sort()

    for c in caseVals:
        #arcpy.AddMessage(c)

        whereClause = ""
        vaList = string.split(c,";")
        for i in range(len(vaList)):
            wf = caseList[i]
            wv = vaList[i]
            wherei = "\"" + wf + "\"='" + wv + "'"
            whereClause = whereClause + " AND " + wherei
            #arcpy.AddMessage(whereClause)

        whereClause = whereClause.strip(" AND ")
        arcpy.AddMessage(whereClause)


        # Read the values from the Source Table
        valueList = []
        searchRows = arcpy.SearchCursor(inputTable,whereClause)
        searchRow = searchRows.next()
        while searchRow:
           searchRowValue = searchRow.getValue(statField)
           if searchRowValue == None:
              pass #don't add a null value to the list!
           else:
              valueList.append(searchRowValue)
           searchRow = searchRows.next()
        del searchRow
        del searchRows


        valueList.sort()
        listLength = len(valueList)
        if listLength == 0:
           print "Every value was null! Exiting script..."; sys.exit()
        elif listLength % 2 == 0: #even, so get the mean of the 2 center values
           ##medianValue = (valueList[listLength / 2]) + valueList[listLength / 2 - 1]) / 2.0
           medianValue = valueList[listLength / 2]
        else: #odd, so it's easy!
               medianValue = valueList[listLength / 2]


        #Write the values to the output table
        listCount = listLength

        insertRows = arcpy.InsertCursor(outTable)
        insertRow = insertRows.newRow()
        for i in range(len(vaList)):
            outCaseField = caseList[i]
            outCaseVar = vaList[i]
            insertRow.setValue(outCaseField, outCaseVar)
        insertRow.setValue(outCountField, listCount)
        insertRow.setValue(outMedField, medianValue)
        insertRows.insertRow(insertRow)

        del valueList
        del listCount
        del medianValue
share|improve this answer
    
When you get a chance be sure to Accept your Answer to your own Question which is quite permissible to do and encouraged in such cases. –  PolyGeo Jul 4 '13 at 2:25
1  
I cannot recall the source of the original code, but the length of the CASE field is based on the assumption that the layer is a shapefile, which only supports field names up to 11 characters long. If you want to allow longer field names (in a geodatabase), you can easily modify this by removing the constraints towards the beginning of the code that truncates the field names to a max length, such as outCaseField = cI[:10], outCaseField = caseFields[:10], and outMedField = "MED_" + statField[:5]. –  RyanDalton Jul 4 '13 at 3:57
    
By the way, nice work on this and thanks for translating the code to 10.x using ArcPy. –  RyanDalton Jul 4 '13 at 3:59
    
Thanks Ryan, happy to do it.@PolyGeo thanks for the information. I will accept later on today. –  Pat Johnson Jul 4 '13 at 14:11

That tool was written with python in ArcGIS 9.3, which used the "arcgisscripting" library. With 10.x, you may need to convert the library over to "arcpy".

More than likely, you can replace the following and it will just work:

  • import arcgisscripting becomes import arcpy
  • remove gp = arcgisscripting.create(9.3) entirely
  • replace all gp references with arcpy

If that doesn't work, it should get you a long way there. I would also recommend looking at the following questions, which are very similar:

share|improve this answer
    
thanks for the answer Ryan, and for the initial work you did on creating the toolbox. I had made the changes you suggested, but it still didn't work. I read elsewhere that I should check capitalization because some functions became case-sensitive (e.g gp.addmessage becomes arcpy.AddMessage). That didn't get it to work either. I can give it another look tomorrow and if I get working I will post the script here. That is if it's alright by you, since you wrote it. –  Pat Johnson Jul 2 '13 at 23:21
    
Ah, yes. Capitalization definitely matters. Yes, please feel free to re-post any new code you come up with. –  RyanDalton Jul 3 '13 at 2:16
1  
I got it working. I will post the python script later today. –  Pat Johnson Jul 3 '13 at 12:54

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.