2

I'm using arcpy.Statistics_analysis to count records and it takes about 30/35min for a table with about 6.8 million records, in a bigger script, this is a big chunk of the whole time. The result of the 6.8M records is a 4.3M table, I was wondering if there's a faster alternative, maybe with some cursors?

arcpy.Statistics_analysis(FC, OUTPUT,[["Field1","COUNT"],["Field2","SUM"]],["Field1"])

FC looks like:

Field0 Field1 Field2
1 1 1
2 1 0
3 4 1
4 3 1
5 2 0
6 3 1
7 1 1

OUTPUT would look like, with Statistics

Field1 FREQUENCY SUM_Field2
1 3 2
2 1 0
3 2 2
4 1 1
2
  • 2
    Did you try building an index on field1,field2 first? In a PostgreSQL database, a GROUP BY sum query with an index on 7M rows would take just seconds. Then there's the memory workspace option. Lots of ways to do this., even a DA SearchCursor with a dictionary.
    – Vince
    Aug 17, 2022 at 4:54
  • 1
    Sounds very much like the bottle neck is not the tool but the data, I would expect the tool to complete within 1min with that number of rows. I would explore adding attribute indices and where it is you are reading the data from and writing too. In memory would be quickest as would a local SSD drive.
    – Hornbydd
    Aug 17, 2022 at 7:23

1 Answer 1

1

This is why I am using Pandas, to do a job quickly:

enter image description here

Output:

enter image description here

Script:

import arcpy
from arcpy import env
inTable=arcpy.GetParameterAsText(0)
caseField=arcpy.GetParameterAsText(1)
theFields=arcpy.GetParameterAsText(2).split(";")
outTable=arcpy.GetParameterAsText(3)
usePandas=arcpy.GetParameterAsText(4)
env.overwriteOutput = True
aTypes=["SUM"]

if usePandas=='false':
    arcpy.AddMessage("Using ArcGIS")
    if len(theFields[0]):
        stats=[[field, "SUM"] for field in theFields]
        arcpy.analysis.Statistics(inTable,outTable,stats,caseField)
    else:
        arcpy.analysis.Frequency(inTable,outTable,caseField)
else:
    arcpy.AddMessage("Using Pandas")
    fldSource=arcpy.ListFields(inTable)
    sourceNames=[row.name for row in fldSource]
    i=sourceNames.index(caseField)
    fld=fldSource[i]
    theType = fld.type
    import pandas as pd
    import string, random,os
    csv=''.join(random.choice(string.ascii_uppercase + string.digits) for _ in range(10))
    outCSV='F:/scratch/%s.csv'%csv
    if len(theFields[0]):
        tbl=arcpy.da.TableToNumPyArray(inTable,[caseField]+theFields)
        df=pd.DataFrame(tbl);del tbl
        df["Frequency"]=1
        fList=["Frequency"]+theFields
    else:
        tbl=arcpy.da.TableToNumPyArray(inTable,caseField)
        df=pd.DataFrame(tbl);del tbl
        df["Frequency"]=1
        fList=["Frequency"]
    sums=df.groupby([caseField])[fList].sum()
    del df
    if theType=='String':
        outINI='F:/scratch/schema.ini'
        f = open(outINI, "w")
        f.write('[%s.csv]\n' %csv)
        f.write('Col1=%s Text' %caseField)
        f.close()            
    arcpy.AddMessage(sums.head())
    sums.to_csv (outCSV,header=True)
    GDB,tbl=os.path.split(outTable)


    arcpy.conversion.TableToTable(outCSV, GDB, tbl)
    arcpy.management.Delete(outCSV)
       

The code could be much-much shorter, but ArcGIS has a mysterious ways of dealing with CSV files.

For a set of about 800Krecords, Pandas is 5.5 times faster, but slightly slower for tiny datasets.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.