nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

Using count and group by in makeQueryTable_Management

DaveTaylor

I know you can't use the count and group by in the where clause of the MakeQueryTable_Management function but is there a way using python to count the rows that are the same once done?

I have the function working so that it exports to a .dbf file but instead of having them grouped by ID's it just has them all listed separately. For instance:

ID
1234
2345
2346
1234
2345

What I want is this:

ID       COUNT
1234       2
2345       2
2346       1

Is there something I can use in python to either group all the same id's together and add a new column to my dbf or is there something with ESRI I can use?

My end result is I want to join this to a shapefile I already have and color code them by counts and have it update on the fly.

Thanks in advance!

Find more posts tagged with

Accepted answers

All comments

DaveTaylor

nevermind I think I got it!

Thanks that appears to work perfectly!

DaveTaylor

Unless I'm missing the point, calling the Summary Statistics tool will do this in one line of code?
arcpy.Statistics_analysis("Export_Output","in_memory/x",[["ID","COUNT"]],"ID")
This creates a table called X in the in_memory workspace and returns a count on a field called ID.

How do I get this out of memory and saved as an actual .dbf file?

DouglasSands

It would. I overthought the issue initially, although cursors are usually faster with large data sets. The join back is necessary so that the features can be symbolized by the count.

Hornbydd

Unless I'm missing the point, calling the Summary Statistics tool will do this in one line of code?

arcpy.Statistics_analysis("Export_Output","in_memory/x",[["ID","COUNT"]],"ID")

This creates a table called X in the in_memory workspace and returns a count on a field called ID.

DouglasSands

You could also accomplish the same thing by using Summary Statistics, and then joining the resulting DBF table back to your source dataset. This could be easily done in model builder also.

DouglasSands

Using your example, to execute the function keep what I originally posted and add the following at the end:

count_duplicates("I/live_data.dbf",'id')

Since the code is defined as a function, you need to call it at the end rather than replacing the parameters. Also to be sure you have the correct file path you can navigate to it in Windows Explorer and hold shift, then right click on the file. This enables the option "Copy as text".

DaveTaylor

Thanks. This is what I put and I am getting a syntax error

def count_duplicates("I/live_data.dbf",'id'):
    #Add the count field
    data_layer = arcpy.MakeFeatureLayer_management("I:/live_data.dbf", 'data')
    arcpy.AddField_management(data_layer, 'COUNT', 'LONG')
    arcpy.Delete_management(data_layer)
    del data_layer

#Do the counting:
    value_list = []
    with arcpy.da.SearchCursor("I/live_data.dbf", ['id']) as search_rows:
        for row in search_rows:
            value_list.append(row[0])

    counts = Counter(value_list)
    del value_list

    with arcpy.da.UpdateCursor("I/live_data.dbf", ['id', 'COUNT']) as update_rows:
        for row in udpate_rows:
            row[1] = counts[row[0]]
            update_rows.updateRow(row)

DouglasSands

Something along the lines of the following code should work:

from collections import Counter

def count_duplicates(path_to_data, field_to_count):
    #Add the count field
    data_layer = arcpy.MakeFeatureLayer_management(path_to_data, 'data')
    arcpy.AddField_management(data_layer, 'COUNT', 'LONG')
    arcpy.Delete_management(data_layer)
    del data_layer

    #Do the counting:
    value_list = []
    with arcpy.da.SearchCursor(path_to_data, [field_to_count]) as search_rows:
        for row in search_rows:
            value_list.append(row[0])

    counts = Counter(value_list)
    del value_list
    
    with arcpy.da.UpdateCursor(path_to_data, [field_to_count, 'COUNT']) as update_rows:
        for row in udpate_rows:
            row[1] = counts[row[0]]
            update_rows.updateRow(row)

Join the Conversation

Signed in members can post, follow updates, and more. New here? Register a free account.

Quick Links

Community Help
Find useful guides, FAQs, and documents to help you navigate and make the most of Esri Community.