# Best strategy to find frequency of value by multiple values in another field?

618
4
01-25-2018 08:57 AM Regular Contributor

I've got a table that looks something like this:

CodeValue
110001
120000
130001
110000
110000
110000
120000
120001
130001

I want to find a result that groups by the "code" column and finds which number is most frequently occurring in the "value" column. Result would look something like this:

CodeValue
110000
120000
130001

I've explored the summary statistics tool and unfortunately there's no option to find the mode (frequency) that a value occurs in a field. Frequency_analysis doesn't do the grouping either. Is there any tool out there made for this already or is this something I'll have to come up with my own code for? It's certainly doable, was just hoping for an easy option already.

Tags (2)
1 Solution

Accepted Solutions Regular Contributor

You're right. I figured it out after I posted this. I was using the summary_field parameter when I shouldn't have, I should have just used both fields in the frequency_field parameter.

``arcpy.Frequency_analysis(inTable, outTable, ['Code', 'Value'])‍‍``
4 Replies by MVP Regular Contributor

One quick idea (but there's probably one better):

``````import collections

table = [
[11000, 1],
[12000, 0],
[13000, 1],
[11000, 0],
[11000, 0],
[11000, 0],
[12000, 0],
[12000, 1],
[13000, 1]
]

d = {} # dictionary for counting

for row in table:
dictValue = "{}:{}".format(row,row)
# print dictValue
if dictValue not in d.keys():
d[dictValue] = 1 # insert key into dictionary and set value to 1

else:
d[dictValue] += 1 #increment value in dictionary

od = collections.OrderedDict(sorted(d.items()))

for k, v in od.iteritems():
print k.split(':'), k.split(':'), v

'''  Results:
11000 0 3
11000 1 1
12000 0 2
12000 1 1
13000 1 2
'''‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍`````` by MVP Esteemed Contributor

What parameters are you passing to the Frequency tool, it does grouping. Regular Contributor

You're right. I figured it out after I posted this. I was using the summary_field parameter when I shouldn't have, I should have just used both fields in the frequency_field parameter.

``arcpy.Frequency_analysis(inTable, outTable, ['Code', 'Value'])‍‍`` by MVP Esteemed Contributor

I even produced Table Tools which includes a 'Free Frequency' to overcome this.

But..

If you use TableToNumPyArray in arcpy.da, you will get an array representing your table.  Numpy has many useful functions for determining 'uniqueness' and counts.  Once you have your results, you simply can use 'ExtendTable to join an array (column) to your existing table or produce a new table using NumPyArrayToTable.

Demonstration...

``````# ---- your data in array world,  I took the liberty to create field names
#      before bringing it over to the other side

a  # ---- the array
array([(11000, 1), (12000, 0), (13000, 1), (11000, 0), (11000, 0),
(11000, 0), (12000, 0), (12000, 1), (13000, 1)],
dtype=[('Class', '<i4'), ('Count', '<i4')])

uni, idx, rev, cnt = np.unique(a, True, True, True)  # ---- the 'unique' function

# ---- returns the unique values, index to first occurrance, how to reverse the
#      result... and lastly... the count of each unique class, in this case, based
#      on two fields, 'Class' and 'Count'

uni  # the unique classes
array([(11000, 0), (11000, 1), (12000, 0), (12000, 1), (13000, 1)],
dtype=[('Class', '<i4'), ('Count', '<i4')])

cnt  # the counts for the above
Out: array([3, 1, 2, 1, 2], dtype=int64)‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍``````

Then if you need the result as a table, you can put it back together and bring it back into ArcMap or Pro 