3

I've got a shapefile that was made by the mother of all Unions. Because of that, it's got about 400 columns, and I want to sum about 100 of them (specifically, the "Score" from each). Is there an easier way to do it than to manually type a formula adding each score value?

Before this project is done, there will be about 600 scores that need to be added to one thing or another so any kind of automation would be helpful. I've thought about setting up some kind of iteration through the column names (they're sequentially numbered) but I don't know how to do that. I've also thought about turning off all the fields other than the scores (no fun) and then summing all values for each row (which I also don't know how to do).

  • if their naming is consistent, e.g. Score_01, Score_02 etc, very basic python wiil do – FelixIP Jun 10 '15 at 20:16
  • 3
    A conformant shapefile is limited to 100 columns, but 255 is possible by filling the byte reserved for column count. 400 columns should not be possible. – Vince Jun 10 '15 at 20:32
  • @FelixIP Thanks for the comfort, but that's not that helpful. I don't know how to change variable names in Python in an iterative way. Is there someway to use a string as a variable name that I don't know? – BabbA Jun 10 '15 at 20:41
  • I'm not 100% clear, are you trying to summarize by column ('... about 400 columns, and I want to sum about 100 of them...) or by row ('...and then summing all values for each row...). – DWynne Jun 10 '15 at 21:02
  • 1
    @DWynne I want each row to have a sum of the values within those 100 columns. Which I'm 99% sure means I want to sum by row. – BabbA Jun 10 '15 at 21:04
5

Export your table to a text format. Grab the header row and paste it to a new file. Delete irrelevant column headings (could also turn them off prior to export to avoid this step), and then use find/replace functions to change the delimiting characters to proper syntax for the formula (ie quotes around field names, a plus sign in the middle). Copy and paste that back to your field calculation. Not sure if Field Calculator will handle that long of a formula.

  • You're my hero! This worked perfectly once I got the find-and-replace working. It also let me weight the scores by another column pretty easily, which I wasn't sure how I was going to pull off. Thanks again! – BabbA Jun 10 '15 at 21:30
  • @BabbA I would strongly suggest you check/verify results. It shouldn't even be possible to have that many fields in a shapefile as Vince mentioned, and even if you do or it's a different format, I'm afraid that things might be getting truncated with no obvious warnings or errors. 100 field names seems like a very long string to paste into a dialog box which is why I gave that last warning. This method works to a point, but the script based solutions are a more proper way to do it in general, never mind with that many fields. – Chris W Jun 11 '15 at 4:09
  • I haven't picked through the work line by line, but the end result looks like what I was expecting. I looked through the statistics for the output and everything is within limits (not too high or too low). I could be missing something, but I'm about to do another pass with a similar process, so I'll check more closely then. (I don't know that this would have any impact, but the vast majority of cells were just 0s, due to the nature of Union-ing shapefiles that are fairly distinct from each other) – BabbA Jun 11 '15 at 17:19
  • I just went through and did a little bit of spot checking and even with a few steps between this question and the final product, it looks like it all checks out! I can't explain why it worked, but it definitely looks like it did – BabbA Jun 11 '15 at 19:05
2

you could use some Python scripting, I assume that the field "resultfield" exists and that you can identify the fields to sum based on one common string (i.e. "score") :

import arcpy
allfields = arcpy.ListFields("featureclassname")

scorefields = [x.name for x in allfields if x.name.find("score")>-1]

with arcpy.da.UpdateCursor("featureclassname", ["resultfield"] + scorefields) as cursor:
    for row in cursor:
        row[0] = sum(row[1:]) #store in "resultfield" the sum of all other fields
        cursor.updateRow(row)
  • I don't know that I know enough about python in ArcMap to pull this off, but it looks like it would work to me. I literally started using VBScript and Python for more than just little field calculators yesterday but maybe with some reading I can get this to work – BabbA Jun 10 '15 at 20:59
1

Field calculator (Python)

def TotalTHem(fid, layerName,wildcard):
  mxd = arcpy.mapping.MapDocument("CURRENT")
  lr=arcpy.mapping.ListLayers(mxd, layerName )[0]
  destFields=arcpy.ListFields(lr)
  dNames=[row.name.upper() for row in destFields]
  list2add=[]
  for fname in dNames: 
     if wildcard in fname:list2add.append(fname)
  with arcpy.da.SearchCursor(lr,list2add,r'"FID"='+str(fid)) as cursor:
      for row in cursor:
         return sum(row)

Expression:

TotalTHem( !FID!, 'MOTHER', 'SCORE')
  • Try moving some of the arcpy.mapping calls out of the function and making them global. Right now they would get called once for each record, it's a bit of overhead. – DWynne Jun 11 '15 at 5:09
  • Wow! Never thought it's possible with field calculator. Just above def? Is it? Are you able to edit my solution please – FelixIP Jun 11 '15 at 5:41
1

Here's a way to implement Chris W's answer without the need for ArcGIS. (It does require gdal/ogr, free and open source.) When gdal/ogr is installed, run the code below from a command terminal (mac/linux/windows).

To get the list of fields Chris mentions:

ogr2ogr -f csv  fields.csv union.shp -where "0=1"

Search + replace that text to create the math expression you want. Then you can add a column:

ogrinfo -sql "ALTER TABLE union ADD COLUMN sum_field NUMERIC" union.shp

And calculate the sum_field (use your expression of columns in place of a+b+c) :

ogrinfo -dialect sqlite -sql "UPDATE union SET sum_field = a + b + c" union.shp

These sql statements could also work in PostGIS or via python.

0

First create a field on your table to summarize the values in. Then take a look at Calculate Field.

In the below, update the Expression parameter to include all the field names you want to sum per row.

enter image description here

  • 1
    I'm wondering if this expression (and using sum_all) would pay attention to whether fields are on or not. If so, you could use the layer properties Fields tab to do some quick on/off manipulation and then run this. Otherwise I'm guessing you'd need a formula or something to get only the desired fields (similar to Felix's answer), and they'd have to have something all unique and in common (ie, all have "score", and not need just 6 of 10 "score"s). Else you're back to manually picking field names from a long list. – Chris W Jun 11 '15 at 1:24
  • Anything manual is not going to work if BabbA is serious about that number of fields. Switching unwanted fields off (I use it all the time working with 'wide' tables) might help slightly with @ChrisW solution, but it is a nightmare to pick more than 10 required fields. The only feasible are mine and radoxju. My requires less skills (no need in custom toolbox, adding script, etc), radoxju's is much faster, credits to him – FelixIP Jun 11 '15 at 3:57
  • @FelixIP. Right, but he has only loosely defined what fields he wants to summarize. – DWynne Jun 11 '15 at 5:06

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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