Frequency missing a case field

[curtvprice]

A task I find myself having to do requires functionality that existed in ArcInfo Workstation's FREQUENCY command. That tool worked pretty much like the ArcGIS Frequency tool except there was an option for a {case_field} that would be added to the input table to make it easy to join the frequency table to the input table later using a single case field.

Does anyone have a cookbook example of how to generate such a case field in the input table for each unique combination found?

Thanks.

(Posted early 2011; updated 5/2015)

[RichardFairhurst]

Edit - May 23, 2015 - 12:09 AM PST - Improved speed of the code for the intersect option.  See 10.3 version.

Edit - May 22, 2015 - 6:11 PM PST - Updated options for Case ID numbers.   See 10.3 version.

[Edit - May 19, 2015 - 1:30 PM PST - Revised tool execution code to correctly handle more than case two fields]

I have attached a version of my tool where I have attempted to create an interface for the tool that will work for Desktop 10.2.  The 10.3 tool interface I posted previously works better overall and should be used if you have Desktop 10.3, but for those still using Desktop 10.2 hopefully this interface will work reasonably well.

The key objectives of the interface is to aid the user in creating a valid multiple field case field list from the table inputs and to let the user choose the order of the fields affecting the sort of the Case ID values independently of the field order within the source table.  To do this I have used both a checkbox pick list and a string to get field inputs from the user for the tool.

In order to choose fields in the 10.2 interface you should check fields in the checkbox lists shown for each input table.  The checked fields will be added to a case field text list string, and the string is used to determine the sort order of the Case ID values.  The fields in the case field text list string only updates after the user clicks with the left mouse button outside of the fields checklist.  If the user clicks outside of the ckeckbox list before choosing their next field for a table, the next field is added to the end of the text list string and can make the text list string order different from the checkbox field list order.  Unchecking fields will remove fields from the text list string without disturbing the order of the rest of the fields in the string.

A screen shot of the interface is shown below.  (Note that I was able to choose the fields from the checkbox lists in a way that changed the order of the fields in the Text List strings without having to type the fields in the string.)

There are several drawbacks to this interface that the 10.3 interface overcomes.  The biggest drawback is that when the 10.2 version tool is chosen from the Results tab, the field checkbox list and field text list string are blanked out and the user has to choose the field list again before running the tool.  The 10.3 version remembers the fields chosen and can be run again immediately from the Results tab.  This difference is due to differences in the ways the tool inputs are validated.  The 10.3 version also has better validation to make sure that every field chosen from one table has a matching field from the other table.  In the 10.2 version I am relying on the user to keep track of that.  The user can also type invalid information into the Field Text List strings in the 10.2 version, but they can;t choose invalid fields in the 10.3 version.  However, despite these drawbacks the 10.2 interface is still usable.

Both tools still do not update the field list to include the Case ID field in ModelBuilder when that field is added by the tool.  I have not yet contacted Esri to find out if that can be fixed or if this behavior is a bug.

[curtvprice]

Richard, I really appreciate your careful work on this, especially because I have found python toolboxes fairly opaque and I have this practical example to learn from. I feel like I'm finally getting why I want to bother learning how to develop with them!

[RichardFairhurst]

Curtis:

I have revised the 10.2 and 10.3 versions to correctly handle more than 2 case fields.  The original code failed to work when 3 or more fields were selected, but the new attachments uploaded today do work for 3 or more fields.

[RichardFairhurst]

I have been using this tool a lot over the past few days to do matching of values between two tables and I have found it useful to add another option to my tool. As a result, I have modified the 10.2 and 10.3 versions to provide 3 different options for assigning Case ID numbers to the records in the two tables.  The attachment, screenshot and code in my previous post on the 10.2 and 10.3 tools has been updated.

The default option assigns positive Case ID numbers to all unique case values in the Primary Table. The Secondary Table is assigned a positive Case ID numbers only if it matches a case value found in the Primary Table.  Unmatched values in the Secondary Table are assigned a Case ID value of -1.

The second option assigns positive Case ID numbers to all unique case values found in either of the two tables.  This option represents a Union of case values in the two tables.  No Case Field values in either table will have -1 assigned as a Case ID value.

The new third option will only assign positive Case ID numbers when the case value is found in both tables.  This option represents the Intersection of case values found in the two tables.  In this option any unmatched case value in either table that is not found in the other table will be assigned a Case ID value of -1.

The inclusion of Case ID values that are assigned -1 in these different ways makes it much faster to select the set of values not found in one table or the other than using a join and selecting Null records in the joined table.

[RichardFairhurst]

I have made another revision to the 10.3 version of the tool.  I have added the option to sort the values in any field in Ascending or Descending order to control the Case ID numbering sequence.  The GPValueTable parameter control at 10.3 allows me to just add a new Sort Order column with a filtered drop down list, making it easy for the user to set up and see the relationship between the field names and the sort order option.

I have not added this option to the 10.2 version of the tool, because I believe the interface limitations at 10.2 would place too much of a burden on the user to maintain a relationship between the two Case field lists and a third Sort Order option list.

[SalmanAhmed]

Hello, I find myself in a situation needing to use this nice tool again. But I want to create case IDs based on two fields that have been joined to the base table. I know that for such fields this tool doesn't work. So I created two new fields and just transferred the values from those two joined fields into the new ones using field calculator. But still these newly created fields also don't show up in the tool dialogue box. Is there anyway to make this work with this tool?

[RichardFairhurst]

If you created new fields and calculated them over with the field calculator then I see no reason why they would not show up.  Each time the tool runs it should recognize any newly added fields and their values in the single table.  It works for me that way.  So I would need screen shots to understand what you are seeing if it the new fields do not show up, since I find it hard to believe that newly added fields with values will not be recognized by the tool.

Joined tables are very hard to set up and work with in ArcObjects.  The qualified field names are very tricky to use even when you can get them to work in ArcObjects.  I don't know what complications in creates when using Python.

I set the tool up to use a Table directly and not a Layer/Tableview.  The inputs would have to be converted to a Layer/Tableview to detect any kind of a Join.  But all the rest of the code may still fail to deal with the fully qualified table names correctly if I made that change.  You would need to provide a more detailed explanation of what fields you are joining for me to know whether or not I want to even try to do what you want.

[SalmanAhmed]

I think I know what the problem was. The newly created fields were created by opening the table from the table of contents. And I was running the tool on the original table in the database. So I think these new fields were not there in the table in the database. So I need to export this table first to the database to be able to see the new fields in the table. I stupidly assumed that any changes I make in the table by opening it from the table of contents will be transferred to the respective table of that shapefile in the database.

[RichardFairhurst]

I did a quick experiment by changing the inputs to allow the use of GPTableView instead of DETable.  The Join fields are recognized in the set up.

However, the AddField_management tool required to add the case field fails, because a Joined table is not allowed as an input to that tool.  So I would have to write a subroutine to extract all of the join properties, break the join, add the field and then recreate the join.  I suspect that if I overcame that problem then there would be problems with the update cursor, since I would have to keep track of the unqualified and fully qualified field name at different stages in the code.  I also have concerns with the cursor performance if a join is in place.  If you are joining two or more tables at once then I am not interested in helping you, since I avoid that configuration at all costs.  So, you will have to give me a much more detailed description of the real life scenario you need to solve, since I am not willing to work out every possible complication that could arise with every possible join set up.

[curtvprice]

The toolbox should be placed in the "C:\Users\[UserName]\AppData\Roaming\ESRI\Desktop10.[3]\ArcToolbox\My Toolboxes" folder in Windows 7 (modify the items in brackets to fit your user name and Desktop version).

BTW a good way to find this path to the Windows user profile folder (which can vary depending on how your Windows profile has been configured as well as the Windows version) is to use the path %APPDATA%\ESRI and navigate from there.