[GIS] Calculate field based on most frequently occuring text/value in other fields in attribute table (ArcGIS)

arcgis-desktoparcpyfield-calculator

I was wondering if anyone knows if it is possible to calculate a field based on the most frequently occurring value in other fields.

What I have is 5 fields with crop information over 5 consecutive years. What I want is to create a new field with the most frequently occurring crop over that 5 year period.

So for example if there were fields for Crop 2008, Crop 2009, Crop 2010, Crop 2011 and Crop 2012 with respective crop types for the first polygon as Winter Wheat, Spring Barley, Winter Barley, Winter Wheat and Grass the function would return Winter Wheat because it appears twice.

I am aware there is a function called Counter in the Collections package that can do this for a list but I'm unsure how this would work within the arcpy field calculation tool?

Likewise there also needs to be a consideration for values that tie i.e. appear the same amount of times across the 5 fields.

I can't seem to find anything else from my searching about.

I'm using ArcGIS 10 and hoping to calculate fields in the python window.

Apologies for not providing any attempts at coding this so far, I really am stumped on this one!

Best Answer

you could try this in the field calculator, in case of tie it returns the smallest value.

PARSER :

PYTHON

CODEBLOCK:

def most_common(a,b,c,d,e):
    L = [a,b,c,d,e]
    newL = [x for x in L if x!=""]
    return max(set(newL), key=newL.count)

COMMAND:

most_common(!Crop2008!, !Crop2009!, !Crop2010!, !Crop2011!, !Crop2012!)

Related Solutions

[GIS] Compare attribute columns of a feature class in ArcGIS

Am I correct to assume that you have multiple rows of counts per class, much like pictured in the Excel sample you provided? And you want highest count per class or simply highest overall count? (You can actually do both, 'subtotal' highest and overall highest.)

You can use a search cursor to read the row pairs and write them (with an insert cursor) to a new table that can then be easily sorted (and ArcMap has multiple field sort functionality). You would do this, say using your 1st row as an example, by reading 'across' your 3 class example for 3 pairs of values, as in:

classVal1, count1, classVal2, count2, classVal3, count3

..and populating that in your insert table as:

(row 1): classVal1, count1, class1
(row 2): classVal2, count2, class2
(row 3): classVal3, count3, class3

Then reading successive rows, following the pattern above, the 3rd column (field) will behave as a 'counter', reflecting what original class the count values came from, if that is relevant info you need....

So, after writing the output table, you can easily sort descending by count to get the highest overall count and corresponding classVal...or sort by count and class to get the highest counts per class and corresponding classVals. Is that what you need?

And, as a further note, you can actually use a sort in conjunction with the search cursor on this result to output the highest values if max value(s) are all you wish as final output, or if you have a preference for the summary statistics or frequency tools, use them instead.

arcpy – Calculating Statistics Across Multiple Fields in arcpy

Your best bet for this approach would definitely be a cursor. You can create a python tool that takes 2 inputs:

Feature class/table in question
multi-value parameter for all the fields you are interested in

The code to grab all this information is pretty straightforward:

import math, itertools, arcpy

FC = arcpy.GetParameterAsText(0)
fields = arcpy.GetParameterAsText(1).split(";")

#Flatten a list of lists
def flatten(list_lists):    
    return list(itertools.chain.from_iterable(list_lists))

#Whatever standard dev func is applicable
def std(x):
    pass

#read values from fields and flatten
vals = flatten([r for r in row] for row in arcpy.da.SearchCursor(FC, fields))

arcpy.AddMessage("\nThe MIN is: {}".format(min(vals)))
arcpy.AddMessage("The MAX is: {}".format((max(vals))))
arcpy.AddMessage("The MEAN is: {}\n".format(sum(vals) / len(vals)))
arcpy.AddMessage("The STD is: {}".format(std(vals)))

If you want to dump the statistics to a table with a timestamp, that can be easily accomplished.

Related Question