topic Best strategy to find frequency of value by multiple values in another field? in Python Questions

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

MKF62 — Thu, 25 Jan 2018 16:57:01 GMT

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

Code	Value
11000	1
12000	0
13000	1
11000	0
11000	0
11000	0
12000	0
12000	1
13000	1

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:

Code	Value
11000	0
12000	0
13000	1

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.

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

RandyBurton — Sat, 11 Dec 2021 12:23:51 GMT

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[0],row[1])
    # 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(':')[0], k.split(':')[1], v

'''  Results:
11000 0 3
11000 1 1
12000 0 2
12000 1 1
13000 1 2
'''‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

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

JoshuaBixby — Thu, 25 Jan 2018 18:23:40 GMT

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

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

MKF62 — Thu, 25 Jan 2018 18:47:24 GMT

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'])‍‍

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

DanPatterson_Retired — Sat, 11 Dec 2021 12:23:54 GMT

Sadly for some... Frequency.... requires an Advanced license. I have complained about this before.

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[21]: 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