AnsweredAssumed Answered

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

Question asked by MollyKFoley on Jan 25, 2018
Latest reply on Jan 25, 2018 by Dan_Patterson

Like • Show 0 Likes0
Comment • 4

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.

MollyKFoley Jan 25, 2018 10:49 AM

Correct Answer

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

See the reply in context

No one else had this question

Outcomes

rvburton Jan 25, 2018 9:57 AM

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
'''

2 people found this helpful

Actions

bixb0012

Jan 25, 2018 10:23 AM

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

2 people found this helpful

Actions

MollyKFoley @ bixb0012 on Jan 25, 2018 10:49 AM

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

1 person found this helpful

Actions

Dan_Patterson

Jan 25, 2018 10:59 AM

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

2 people found this helpful

Actions

GeoNet

The Esri Community

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

Outcomes

Related Content

Recommended Content