ArcGIS 10 - Field Calculator code block help (count and number unique values)

Question

I'm putting together a model, and the step I'm stuck on involves the calculation of two new integer fields based on an existing string field/column.  The relevant field might have rows that might look like this:
Row           Color
1                  RED
2                  RED
3                  RED
4                  BLUE
5                  GREEN
6                  GREEN
7                  ORANGE

For the next step of my model, I need to calculate two extra fields, as follows:
Row           Color           ClrCount      ClrOrder
1                  RED                  3                  1
2                  RED                  3                  2
3                  RED                  3                  3
4                  BLUE                1                  1
5                  GREEN              2                  1
6                  GREEN              2                  2
7                  ORANGE            1                  1

"ClrCount" should be the total number of rows in "Color" that share the same string, and should be the same for each of that string's rows.

"ClrOrder" should be a unique number for each of the rows that share the same string.

I don't mind if the code blocks are VBscript or python, but I don't know enough about either to put it together myself.  All I can think is that the solution might involve indexing or creating an array, and then stepping through it somehow.

Any help greatly appreciated!

UrbisMelbourne · Answer

Thanks Niklas, I've been playing around with your suggested approach a little.  I like that two-pass approach, but I think the problem is that I don't know nearly enough about Python to do what I'm trying to do (hence why I was trying to do this in model builder, via the field calculator), and I don't even know enough to Google for the correct terms.

At the moment I have something like this:import arcpy

rows = arcpy.UpdateCursor("C:\TEMP\Test.shp","","","","Color A")

for row in rows:

Not much, I know, but from there I'm pretty much lost.

I don't really understand how to express the question: "as you loop through the sorted list of colors, iterate a number in ClrOrder until you find that the color changes, then start the iteration over again" within Python.  I've found plenty of help online about using cursors, but nothing that I can apply specifically to this problem.

If you (or anyone else) could give me a hand here, even by pointing my towards the sort of things I should be looking at (e.g. does this involve creating dictionaries, or lists?), that would be great.

NiklasNorrthon · Answer

I don't think Calculate Field is the best path to follow here. The contents of ClrCount and ClrOrder depends on several other rows, possibly spread out over the entire feature class. It might be possible to create some codeblock logic involving cursors, dictionaries et cetera to get it to work, but it will defintly be messy.

If you have an ArcInfo license check if the Find Identical tool would help you. That one together with some clever join might be what you need.

If not (or maybe even with ArcInfo license) I'd create a new script tool, using an update cursor with the color field as the sort field, and just step through the feature class twice.

First time to set the color order. (start at zero and increment until a new color is found, then reset).

The second pass I'd still sort on color as the primary sort field, but add the newly created color order as the second sort field using descending sort for that value. Then update all the ClrCount field to hold the highest ClrOrder value + 1 (since it was zero based).