Looking for ArcPy tool for calculating statistics on field with summary field AND boolean fields

Question

What I'm searching for is a tool that will sum a field by a secondary unique field AND booleans...

Let's say I have 12 table each with 4mil records and the following fields:

• Donation_Amount ($ amount of a donation)
• County_FIPS (county from which the donation was made)
• Republican (y/n)
• Democrat (y/n)
• Independent (y/n)
• In_district (y/n)
• Individual (y/n)
• PAC (y/n)

I want to summarize donations per boolean for each unqiue county fips.

Ideally the result I'd like is a table of all unique FIPS and an amount sum and count for Repub, Dem, Ind, In_District, Indiv, PAC.

Tried to get it to work with iterate in model builder and couldnt... Also read about summarize and frequency tools but Summarize seems to be limited with how many secondary fields you can use, and Frequency tool is only for ArcInfo (I need for ArcView 10.1)

Essentially I know this is simply a database process to summarize data based on secondary fields... I have heard of tools in R that do this (in Plyr and Reshape2: cast/melt) but would ideally like to do it in a program with GUI and even more ideally, in ArcMap so it wouldnt require too much shuffling of data...

Thanks all! Much appreciated.

Answer 1

If you have a strong desire to do it in ArcMap, it would be relatively straightforward with a script. I would tackle it thusly:

1. Write attribute table to list of lists
2. Create list of unique FIPS codes
3. Use filter() inside of for loop to loop over the codes and create lists of attribute info for each FIPS code
4. Then it's a simple sum on the lists and you can dump all of that to a text file if desired

Granted, there might be a tool (or combo of tools) that can already do this. Like you mentioned, there are other suites designed to handle this type of statistics, so it really boils down to whether or not you want to go the pure ArcMap route.

Edit: I wrote a script to produce the following. Will document if someone wants explanation:

import arcpy

shpin = arcpy.GetParameterAsText(0)
basefield = str(arcpy.GetParameterAsText(1))
sumfield = str(arcpy.GetParameterAsText(2))
boolfields = arcpy.GetParameterAsText(3).split(";")
textout = arcpy.GetParameterAsText(4)

boolfields = map(str, boolfields)
boolfields.insert(0, basefield)
boolfields.insert(0, sumfield)    

finfo = [[r for r in row] for row in arcpy.da.SearchCursor(shpin, boolfields)]

unique = list(set(zip(*finfo)[1]))
unique.sort()

outfile = open(textout, "w")    
outfile.write(",".join(boolfields[1:])+ "\n")

arcpy.SetProgressor("step","Summarizing fields...",0,len(unique)-1,1)
for i,val in enumerate(unique):
    arcpy.SetProgressorPosition(i)
    stats = filter(lambda f: f[1] == val, finfo)    

    outfile.write(str(val))

    for j in xrange(len(boolfields)-2):
        try:        
            outfile.write(",{}".format(str(sum(zip(*filter(lambda f: f[j+2] == "Y", stats))[0]))))
        except:
            outfile.write(",0.0")

    outfile.write("\n")

outfile.close()

Answer 2

I'm working on the project with mapadelphia, and here's our slight edit to the script. It works for categorical values 1-11 and uses NULL instead of 0.0.

import arcpy

shpin = arcpy.GetParameterAsText(0)
basefield = str(arcpy.GetParameterAsText(1))
sumfield = str(arcpy.GetParameterAsText(2))
boolfields = arcpy.GetParameterAsText(3).split(";")
textout = arcpy.GetParameterAsText(4)

boolfields = map(str, boolfields)
boolfields.insert(0, basefield)
boolfields.insert(0, sumfield)    

finfo = [[r for r in row] for row in arcpy.da.SearchCursor(shpin, boolfields)]

unique = list(set(zip(*finfo)[1]))
unique.sort()

outfile = open(textout, "w")    
outfile.write(",".join(boolfields[1:])+ "\n")

arcpy.SetProgressor("step","Summarizing fields...",0,len(unique)-1,1)
for i,val in enumerate(unique):
    arcpy.SetProgressorPosition(i)
    stats = filter(lambda f: f[1] == val, finfo)    

    outfile.write(str(val))

    for j in xrange(len(boolfields)-2):
        try:        
            outfile.write(",{}".format(str(sum(zip(*filter(lambda f: f[j+2] == 11, stats))[0]))))
        except:
            outfile.write(",<Null>")
        try:        
            outfile.write(",{}".format(str(sum(zip(*filter(lambda f: f[j+2] == 10, stats))[0]))))
        except:
            outfile.write(",<Null>")
        try:        
            outfile.write(",{}".format(str(sum(zip(*filter(lambda f: f[j+2] == 9, stats))[0]))))
        except:
            outfile.write(",<Null>")
        try:        
            outfile.write(",{}".format(str(sum(zip(*filter(lambda f: f[j+2] == 8, stats))[0]))))
        except:
            outfile.write(",<Null>")
        try:        
            outfile.write(",{}".format(str(sum(zip(*filter(lambda f: f[j+2] == 7, stats))[0]))))
        except:
            outfile.write(",<Null>")
        try:        
            outfile.write(",{}".format(str(sum(zip(*filter(lambda f: f[j+2] == 6, stats))[0]))))
        except:
            outfile.write(",<Null>")
        try:        
            outfile.write(",{}".format(str(sum(zip(*filter(lambda f: f[j+2] == 5, stats))[0]))))
        except:
            outfile.write(",<Null>")
        try:        
            outfile.write(",{}".format(str(sum(zip(*filter(lambda f: f[j+2] == 4, stats))[0]))))
        except:
            outfile.write(",<Null>")
        try:        
            outfile.write(",{}".format(str(sum(zip(*filter(lambda f: f[j+2] == 3, stats))[0]))))
        except:
            outfile.write(",<Null>")
        try:        
            outfile.write(",{}".format(str(sum(zip(*filter(lambda f: f[j+2] == 2, stats))[0]))))
        except:
            outfile.write(",<Null>")
        try:        
            outfile.write(",{}".format(str(sum(zip(*filter(lambda f: f[j+2] == 1, stats))[0]))))
        except:
            outfile.write(",<Null>")
    outfile.write("\n")

outfile.close()