I hope I get the terminilogy right! Correct me if I mess something up. I need to perform some calculations for a scoring system for different habitat types. The Problem is that I have two different tables in two different shapefiles. They need to be divided through each others somehow (yet again, all needs to be done automatically in the modelbuilder).

For the calculation I need to Sum up the Size of all the Types with a common/matching Name in Table A (I tried to clarify this through the field Sum).

Shapefile A with Table A
Name    Type    Size   Sum
A       BO      10     ?
A       BE      20     ?
C       BA      30     ?
D       BO      10     ?
D       FW      50     ?

The Sum for Name A would be 30 (10+20), for D it would be `60 (50+10).

In my second table, I do need not to do any calculations. It looks likes this:

Shapefile B with Table B
Name    Type    Size
A       BO      20
A       BE      50
C       BA      60
D       BO      90
D       FW      10

I know the Size of each Type in in each Name.

The Size of Table B needs to be divided through the Sum of all the Types for each Name in Table A. I hope you can see the problem I am running in. How do I tell the Modelbuilder to summarize all the Size of all Types that have a matching Name? And how would I populate Fields with this outcome for further calculations?

  • 1
    While this probably can be done in MB, it would be a LOT simpler to use Python. In fact, it could probably be done in about 20 lines of code. MB is great and all, but it's not really prime for tasks like this. – Paul Jul 18 '14 at 17:21
  • 1
    Along the python track, take a look into arcpy.SearchCursor (resources.arcgis.com/en/help/main/10.1/index.html#//…). I think that is where @Paul was going with this. It makes parsing over the tables way, way easier. – Branco Jul 18 '14 at 17:26
  • Hey. Thanks. Unfortunately there is no way around MB. Its a prototype which will be used on other areas. I was thinking about using python. But that will be a hell of a code (thats what I thought). So many if statements?! – Stophface Jul 18 '14 at 17:40
  • @Stophface, it's a lot simpler than you think in Python. if statements are great when used correctly, but not so much here. – Paul Jul 18 '14 at 17:48
  • @Stophface You are allowed to answer your own question after 8 hours. It would be better to create an answer out of the solution you edited into your question rather than leave it there. – Chris W Jul 19 '14 at 0:42

I actually just did something very similar to this in another script, so I'll drop this here if anyone finds it useful. It's 10 lines if you remove the comments.

import collections

#Summarizes the second number in a list of tuples by the occurrences in the first
#e.g., [["a", 1], ["b", 2], ["a", 3], ["c", 7], ["d", 0], ["c", -1]] ->
#{'a': 4, 'c': 6, 'b': 2, 'd': 0}
def sumdict(list_tuples):
    #Initialize default dictionary
    dic = collections.defaultdict(list)

    #Add keys, list of values to dictionary and sum each
    [dic[x].append(y) for x,y in list_tuples]
    return {i: sum(j) for i, j in dic.iteritems()}

#Summarize two fields, creating dictionary
summarized = sumdict((row[0], row[1]] for row in
                     arcpy.da.SearchCursor(FC_in, ("Name", "Size")))

#Update output shapefile
with arcpy.da.UpdateCursor(FC_out, ("Name", "Size", "newfield")) as cursor:
    for _ in summarized:
        #Divide the size by the sum of the values from the dictionary.
        row[2] = row[1] / summarized.get(row[0])
  • Hey Paul. Thanks a lot. I do not understand much since I just started recently to learn Python and everything I wrote which goes beyond the fieldcalculator never worked properly... An idea just popped into my mind how to solve that without using python. I will post it here if it works. – Stophface Jul 18 '14 at 17:51
  • @Stophface Yes, please do! And yeah, it's a bit overwhelming when you first learn Python But a lot of stuff is reusable for plenty of problems, like the sumdict() function I wrote based on a similar function from Stack overflow. I use it regularly now. – Paul Jul 18 '14 at 17:54
  • added my solution :) – Stophface Jul 18 '14 at 18:32
  • 1
    @Stophface, I hope it works for you. Sounds really complex though. :) How does Merge solve the problem? Sounds more like a dissolve and summarize. – Paul Jul 18 '14 at 18:37
  • Typo. I meant the dissolve tool :) Sounds complex but we are looking at six tools that do the job in an iterator. I have the dissolved shapefile already created a lot of steps earlier in this model so I used this. But for better comprehension I added this step. Of course, python is way more elegant. Maybe in a month or so, when I know more :) – Stophface Jul 18 '14 at 18:44

I came up with a solution. It sounds complicated but we are looking at six tools and an iteration here!

I copy my Shapefile A, use the dissolve Tool to dissolve shapefile A by Name. Then I use this created Shapefile as a in the Split Tool as the Split feature for Shapefile A. I insert into my model Iterate Feature and chose my Split output as a Workspace for the iterator. I have to Add field (area_ha) to each of the "splitted output" and calculate the area (!shape.area@hecatres!) for Type. Then I use Summarize Statistics Tool and SUM of area_ha as an argument (make sure you chose Casefield -> Name). This gives me a for each Name a table with the Name and the Sum of the Size as an output. Through Collect Values I collect all the values and use the Merge Tool to merge my collected values!

Doing this, I "reduced" my table from

Shapefile A with Table A
Name    Type    Size   Sum
A       BO      10     ?
A       BE      20     ?
C       BA      30     ?
D       BO      10     ?
D       FW      50     ?


Shapefile A with Table A
Name    Type    Size   Sum
A        -      -      30
C        -      -      30
D        -      -      60

Now I can use the Join Field Toolto join my Table A based on one common attribute (Name) to my Table B and add another field to do the calculations I need.

  • This solution may work best for you, since you are the only one who knows how it fits within your overall model. As commented by others, it does seem a bit complex and to be doing more than your original question is asking. As written it appears the areas are already present in the data at this stage and you just want to do the summary and calculation. Your solution implies that they are not (hence the dissolving and splitting and calculating area). There may be ways to improve the efficiency of your model, but that would be a different question and we would need an overview of the process. – Chris W Jul 19 '14 at 21:30

It seems that:

  1. Use Summary Statistics on "Shapefile A" with Statistics Field Size and Case Field Name to generate a table with Name frequency and sum_size. This table can be reused as an input in your iteration if you have multiple "Shapefile B"s.
  2. Use Join Field to join the summary statistics output table to "Shapefile B" based on Name, adding only the sum_size attribute. This will be a many-to-one join, so that every record in "Shapefile B" with Name = 'A' (and so on) will get the same value in a new permanent attribute. This could also be done with a join, but the results would have to be exported for that value to be permanent - if you just want the calculation results of the next step, permanence isn't necessary.
  3. Create a new field in "Shapefile B" to hold the calculation result of B's Name + Type combinations (ie, each record) size divided by the overall Name size from A. Use Calculate Field to divide Size by sum_size to populate this new field.

will do the job in ModelBuilder.

Step 1 of this answer does the table reduction per your solution/answer. The other two steps take care of your last sentence.

  • I think this is the foundation of a good answer, particularly since it's well-suited to ModelBuilder and a bit simpler/more elegant than the current solution. But it could be expanded a little to be more clear - perhaps filling out the outline with the actual variables given in the question and the sub-steps. As written, it is a bit on the low quality side of things. I might also suggest a Join Field tool instead of just a Join in order to make the attribute permanent to use in future calcs per the question. – Chris W Jul 19 '14 at 0:56
  • non-English speaker. Bullet points is my work around. Actually there is missing line 'Select matching records' before calculate field. – FelixIP Jul 19 '14 at 2:08
  • I understand the language barrier issue. I've edited your answer to fill it out as I suggested. If you disagree with my changes, let me know and I can work with you to fix them, or please feel free to roll back the edit and I can post a separate answer. Also note that, if I understood your process correctly, it isn't necessary to select matching records before doing the field calculation (because of the way the many-to-one join would work). – Chris W Jul 19 '14 at 21:20
  • Many thanks for editing my answer. Not sure though that highly detailed solution does best to recepient, similar to highly processed food. Felix – FelixIP Jul 20 '14 at 2:43
  • 1
    Full spectrum answer didn't help on exactly the same question a couple of days ago. – FelixIP Jul 20 '14 at 5:21

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.