Take the 2-minute tour ×
Geographic Information Systems Stack Exchange is a question and answer site for cartographers, geographers and GIS professionals. It's 100% free, no registration required.

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.

share|improve this question
    
What is your source data format (shapefile, file GDB, personal GDB, SQL Server, PostGIS, etc)? You may very well be able to do all of this outside of ArcGIS, depending on the source. –  RyanDalton Jul 9 '13 at 19:20
    
apologies for not clarifying, all of our data is stored as File GDB. –  mapadelphia Jul 10 '13 at 14:19

2 Answers 2

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: enter image description here

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()
share|improve this answer
    
Hi Paul, Thank you so much for your help! This is fantastic. We are going to take a stab at editing your script a bit to allow for scenarios where instead of Dem: Y/N the field is Party: 1/2/3/4 (categorical variables). That way we can have one output field for each categorical variable. Thank you again!! –  mapadelphia Jul 10 '13 at 14:13
    
There is plenty room for improvement in the script. It was just something I hacked out in 45 minutes so it definitely can be optimized. If you have some questions about something, don't hesitate to ask! I'm glad to help. –  Paul Jul 10 '13 at 15:29

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() 
share|improve this answer
    
You should look into nesting a for loop to drastically shorten all that code. Other than that, I don't really see why this was posted as it's my exact code with just the variables changed. –  Paul Aug 23 '13 at 21:36
    
Hi Paul! Thanks again for posting your script! It was invaluable to us while working on this project. We are referencing your script (and how it helped us) in a blog post about working with Sunlight political contribution data. We wanted to also include the actual script we ran for the data (to assist others that are working with that data). Thanks Again! We really appreciate the help. –  mapadelphia Aug 29 '13 at 17:18

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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