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

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!

• What would you expect to have returned if two of your crop types occurred twice in those five years, or if five different crop types occurred? I.e. What are your rules for tie-breaking ?
– PolyGeo
Feb 12, 2014 at 12:22
• Thanks for taking the time to reply PolyGeo. I think the best option would be to select the crop type that occurs most recently i.e. in the 2012 or 2011 column. However I'm not sure how easy it would be to do this. If this isn't possible then I suppose it doesn't really matter which is chooses as they both occur the most frequent and both therefore have an equal chance if being the most likely crop in that polygon for the purposes I need it for. Feb 12, 2014 at 13:15

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!)
``````
• Thanks for the reply radouxju. That seems to work quite nicely. Would you know of any way within that code to omit missing values? I've just realised there are a couple of polygons that have more two or more missing values in some of the fields and are therefore missing in the created most frequent field. Feb 12, 2014 at 14:02
• see my updated answer. I assume that you have a text field, so missing values are empty strings. Alternatively, you can enter a list of valid values [x for x in L if x in ["crop1","crop2","crop3","etc"]] Feb 13, 2014 at 6:57
• Perfect! This worked an absolute treat. Thank you again for taking the time to answer my question. Feb 14, 2014 at 8:41

Have you tried looking at the Frequency tool found under Analysis Tools > Statistics. Here is a link to the ESRI Help 10.0 for this tool:

Frequency Analysis

• Thank you for taking the time to reply. I thought we were on to something with the frequency tool for a second (and cursing myself for not looking at the standard tools properly!) but it seems that the tool outputs which are the most common over the whole dataset. What I need is the most common in each polygon or 'row' in the attribute table. i.e. the same attribute table structure is kept in the output rather than a summary table as the frequency tool outputs. Feb 12, 2014 at 13:35