Latest Contributions by RichardFairhurst

‎06-03-2015

Laura; In general the inner cursors should come first and be converted to a new dictionary for each Search cursor. An Update Cursor follows in a completely separate loop after the first loops finish. The first loop Search cursor(s) would read the entire table into the dictionary all at once. Each Search cursor is processed in a separate loop prior to doing any data updates. Alternatively, you can include Python logic while every record is being read to only load the records that make sense into the dictionary for matching to the second separate update cursor loop. The Python logic would somewhat take the place of the iterative SQL expressions. The dictionary key set up with the SearchCursor has to make sense for the match to the other table. Two or more fields can be loaded as a Tuple key (not a list). Using the Python Tuple() and List() methods the key can be converted back and forth for use as a dictionary key or a list for iterating and modifying the data. A Dictionary key can point to a value that is a list of lists to deal with One to Many or Many to Many relationships. You may want to look at the code in this thread for my Multiple Field Key to Single Field Key tool to see an example of some more advanced options that have expanded the techniques outlined in the Blog. The tool itself may actually be able to create a Single Key that would take the place of the inner loop record matching if the purpose of the inner loop is to process a Multiple field relationship. Once the Multiple Field key is converted to a Single Field Key you can use more traditional join techniques and the Field Calculator to do data transfers. I have been using the tool do multiple field matches on two record sets containing 100K records and 800K records and the tool normally finishes in about 1 to 2 minutes.

‎06-02-2015

Try: lyr.definitionQuery = '"Arrondisse"' + "=" + "'" + arrondissement.replace("'","''") + "'" If any apostrophes exist in the field's value each apostrophe will be doubled (no matter how many apostrophes are stored in the actual field value). If no apostrophe exists the field's value will be unchanged. Then you do not need to capture the values that contain apostrophes in any separate logic.

‎05-31-2015

After reading your code again I see that currently it is not using embedded cursors like I thought it was (although you edited the code at some point and I may have seen embedded cursors in a previous version of the code). You probably can still benefit from the Cursor and Dictionary approach, since most likely once you get this working you probably intend to process many more records and use many more SQL statements than your current code shows. In any case, if your full code actually includes embedded cursors, you should remove the embedded cursors and use the principles my Blog outlined. In any case, your looping structure appears to reset the distance summation and record counter variables (SUMDISTANCE and C) for each feature class, Since you only write after processing the entire loop, the variables will have been reset so that only the record(s) of the last feature class will be in the average. I don't think that is what you want to do (but I still don't know what this code is supposed to average). If you mean to aggregate and average records across multiple feature classes then the summation and counter variable should be outside the loops. Alternatively, the write operation perhaps should be inside the loops if multiple averages are actually supposed to be written. Because you set a filter on the SearchCursor to only read FID=0, which should only be one record, probably only one record from the last feature class is being included in the average. So that explains why only the last value in your list is being reported, which I believe is what you showed in one post that you added and then deleted at some point. You need to add that post back, since none of the other posts you have in this thread actually show the numbers you think your are averaging and the result you are getting. Since you keep editing the code and example data I am commenting on my posts won't make sense to most people reading this thread, since the things I am commenting on disappear and I lose track of what I was seeing. To avoid that confusion, please add new versions of your code and examples in new posts rather than editing posts we have already commented on until your problem is found and resolved. Your code should probably look like the code below if you only intend to write one AVGDISTANCE value at the end of the loops (the fields list never changes, so that line has been removed from the loop to avoid processing it repeatedly). SUMDISTANCE = 0 C = 0 fields = ['DISTANCE', 'DURATION'] cases = ['RCs4s3s2c10_S', 'RCs4s3s2c20_S', 'RCs4s3s2c30_S', 'RCs4s3s2c40_S'] for fc in arcpy.ListFeatureClasses(): for case in cases: if fc.startswith(case): with arcpy.da.SearchCursor(fc, fields, "FID = 0") as cursor: for row in cursor: DISTANCE = row[0] DURATION = row[1] SUMDISTANCE += DISTANCE C += 1 AVGDISTANCE = SUMDISTANCE / C outFile.write('' + str(AVGDISTANCE) + '\n') I assume the code you have posted was edited from a longer script or includes code you intend to extend, since although the Duration field is stored in a variable, its value is overwritten in each loop and the values are never actually used.

‎05-30-2015

The code you have published won't produce the list you have shown in your later post, so it is impossible to trace the steps going on. In any case, embedded cursors should not ever be used. Embedded cursors are extremely slow and you should never use embedded cursors to process records in the same feature class in both loops. One cursor will corrupt the loop of the other cursor. I am not sure if it corrupts the inner loop or outer loop, but either way it simply never works. To solve this you should first load your read only data to a dictionary or a list and process them in memory, not with an embedded cursor. It will be much faster and not subject to loop corruption if done correctly. If the FID is the key value then you need to use that as the dictionary key, but I am not really sure what the points that are being averaged have in common from reading your code. It appears to me that you have over-complicated the looping logic by trying to do it with embedded loops. I would need you to walk me through what records need to be grouped and what controls their order. Review the principles for using a dictionary outlined in my Blog entitled Turbo Charging Data Manipulation with Python Cursors and Dictionaries. Processing two completely separate loops where the first simply reads the data into a dictionary and the second separate loop processes the records works better once you have correctly set up the dictionary key and value pairs. One to Many relationships can be handled by making the value associated with the key a list and appending items to it as you read the first cursor straight through without any SQL filtering, just if logic to only create keys and values that you want to process. When each dictionary key is processed in the second separate loop you will already have just the values you need to create your averages nicely listed under the key. In the end you will have much more readable code and you will dramatically reduce or eliminate the SQL statements required to complete the problem.

‎05-30-2015

import arcpy should be at the beginning of your file. At the very least it has to be placed before line 10 where you use the arcpy.da.SearchCursor. Your current script should always fail at line 10, but you probably don't realize what the failure is since you placed your code inside of a Try block and did not include an Except block that reports any errors. Never use a try block without an except block and some kind of error trapping report. For code development I would actually remove the Try statement entirely while debugging the script. I would rather have a complete fail with the default error report than no error report at all. It appears this code is incomplete, since some of your variables are not defined (SortedPointsWorkspace) and it appears you are using garbage data for your table name, but the Try block is still not set up correctly if you cannot identify the line where the failure is occurring. Actually, after a more careful examination, import arcpy has to go before line 1, since you use arcpy.GetParameterAsText in that line. You should get a failure immediately and get an error report telling you that line 1 produces an error, since line 1 occurs outside of the Try block

‎05-30-2015

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.

‎05-22-2015

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.

‎05-19-2015

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.

‎05-17-2015

Several approaches work. ModelBuilder does this with a Iterate Field Values iterator with the Unique Value option checked. The iterator provides the current value being processed in a variable that can be used to name files using Feature Class to Feature Class for an export. All of the steps can be done through a model without any Python scripting. Summary Statistics works also, because it exactly provides a list of strings from a source text field into a new text field with only the sorted, unique values listed if you use that field as the Case field as Owen suggested. This can be output to an in memory output in a script if you just want to read the values for naming files. A second for loop with a cursor would read the list and use a tool like Feature Class to Feature Class to export each file and do the naming. A cursor can do this also, but it is not an absolute requirement to take that approach. It is done with two for loops. A search cursor for loop would read the values into a list which could be sorted with the sort function and then made unique using the set function, then a second for loop would read the list and use a tool like Feature Class to Feature Class to export each file and do the naming. If the end goal is simply to create a table of unique values in a field all you need is the Summary Statistics tool as everyone else has said. If the goal is to create a single feature based on each of the unique attributes in a field use Dissolve with the Case Field assigned and the create multi-part features option checked.

‎05-16-2015

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.

‎05-11-2015

ArcGIS 10.3 does have it. The syntax that works in a 10.3 python toolbox is as follows: def getParameterInfo(self): """Define parameter definitions""" # First parameter param0 = arcpy.Parameter( displayName="Input Primary Table", name="in_prim_table", datatype="DETable", parameterType="Required", direction="Input") #... # Third parameter param2 = arcpy.Parameter( displayName="Primary Case Field", name="case_fields", datatype="GPValueTable", parameterType="Required", direction="Input") param2.columns = [['GPString', 'Primary Case Fields'], ['GPString', 'Secondary Case Fields']] param2.filters[0].type="ValueList" param2.filters[0].list = ["X"] # dummy list value to be replaced when user updates other parameters. param2.filters[1].type="ValueList" param2.filters[1].list=["x"] # dummy list value to be replaced when user updates other parameters. #... def updateParameters(self, parameters): """Modify the values and properties of parameters before internal validation is performed. This method is called whenever a parameter has been changed.""" if parameters[0].value and parameters[0].altered: # Return primary table tbl = parameters[0].value desc = arcpy.Describe(tbl) fields = desc.fields l=[] for f in fields: if f.type in ["String", "Text", "Short", "Long", "Float", "Single", "Double", "Integer","OID", "GUID"]: l.append(f.name) parameters[2].filters[0].list = l # first column list is updated to show field names associated with parameter 0. if parameters[1].value and parameters[1].altered: # Return secondary table tbl = parameters[1].value desc = arcpy.Describe(tbl) fields = desc.fields l=[] for f in fields: if f.type in ["String", "Text", "Short", "Long", "Float", "Single", "Double", "Integer","OID", "GUID"]: l.append(f.name) parameters[2].filters[1].list = l # second column list is updated to show field name associate with parameter1 return #...

‎05-10-2015

Too bad that the .filters parameter is only available at 10.3. I have 10.2.2 at work, but it may be a while before I have time to experiment with that version. If you come up with any alternative code to do what that parameter is doing (adding the pick lists of fields to the GPValueTable) let me know. I was thinking of adding applicable SHAPE@ tokens to the end of the field lists for tables with a geometry field, either by default or with a Boolean option. That way matching of two data sources could be done on geometry values like X and Y coordinates without having to first calculate the coordinates into double fields. Any thoughts on the code involving the schema.additionalFields that is failing to get ModelBuilder to recognize the new field added by the tool? I have tried several versions of where to place it in my logic, but nothing I have tried worked. I guess I will have to try designing a much simpler tool that just adds a field to a data source to see if I can make it work at all. It seems like it may be a bug.

‎05-09-2015

Edit: May 30, 2015 - 5:10 AM PST - Added an Ascending or Descending Sort Order column to order the values of each field in the Case field list so that the sequence order of the Case ID numbers can be fully controlled. Edit: May 22, 2015 - 11:44 PM PST - Improved speed of the code for the Intersect option. Edit: May 22, 2015 - 4:16 PM PST - Changed code to allow for 3 different options for assigning unique Case ID numbers to the tables. It now allow you to assign them to all Primary Table Case Keys (Default), all Case Keys in both Tables (Union), or only to Case Key found in both tables (Intersection). Edit: May 19, 2015 - 1:50 PM PST - Updated code and attachment to correctly handle 3 or more case fields. Edit: May 9, 2015 9:15 PM PST - Fixed error in toolbox code for unmatched case values in Secondary Table. Attached updated toolbox. I have created a Python toolbox tool that is doing almost everything I want. The zipped python toolbox attached was designed in ArcGIS 10.3, but hopefully it will run in lower versions. The toolbox should be placed in the "%APPDATA%\ESRI\Desktop10.[3]\ArcToolbox\My Toolboxes" folder in Windows 7 (modify the items in brackets to fit your Desktop version of 10.3 or higher). I use python lists and itertools to get my sorted unique list of multiple field key values and to generate the sequential numbers associated with each key, but I convert the list into a dictionary prior to running the updateCursor so that I gain the speed or dictionary random access matching when I am writing the Case ID number values back to the data sources. Dictionary key matching is much faster than trying to locate matching items in a list. Here is the interface: The validation works to make sure that the two input data sources are not the same and that the fields used in the case field list are actually in both sources. The user can choose as many fields as they want to make up their unique multiple field case value keys. The field names do not have to be the same in both data sources. The position of the fields in the list will control the sort priority of the Case Fields (highest priority = top field) and the Sort Order column controls whether the values in each field are sorted in Ascending or Descending order. The sort order of the Case field values controls the Case ID number sequencing. The arrangement of the fields can be different from the field arrangement actually used in the sources. However, I am having a problem that I cannot seem to solve. I cannot seem to get the schema additionalFields parameter to update the field list for my outputs so that the Case ID field is shown in ModelBuilder if the field doesn't exist and is being added to the data sources by the tool. Does anyone know how to make that work or spot what I am doing wrong? Anyway, here is my code: import arcpy class Toolbox(object): def __init__(self): """Define the toolbox (the name of the toolbox is the name of the .pyt file).""" self.label = "Field Match Tools" self.alias = "" # List of tool classes associated with this toolbox self.tools = [MultiFieldKeyToSingleFieldKey] class MultiFieldKeyToSingleFieldKey(object): def __init__(self): """Define the tool (tool name is the name of the class).""" self.label = "Multiple Field Key To Single Field Key" self.description = "" self.canRunInBackground = False def getParameterInfo(self): """Define parameter definitions""" # First parameter param0 = arcpy.Parameter( displayName="Input Primary Table", name="in_prim_table", datatype="DETable", parameterType="Required", direction="Input") # Second parameter param1 = arcpy.Parameter( displayName="Input Secondary Table", name="in_sec_table", datatype="DETable", parameterType="Required", direction="Input") # Third parameter param2 = arcpy.Parameter( displayName="Case Fields", name="case_fields", datatype="GPValueTable", parameterType="Required", direction="Input") param2.columns = [['GPString', 'Primary Case Field'], ['GPString', 'Secondary Case Field'], ['GPString', 'Sort Order']] param2.filters[0].type="ValueList" param2.filters[0].list = ["X"] param2.filters[1].type="ValueList" param2.filters[1].list=["x"] param2.filters[2].type="ValueList" param2.filters[2].list=["Ascending", "Descending"] param2.parameterDependencies = [param0.name] # Fourth parameter param3 = arcpy.Parameter( displayName="Case ID Field Name", name="in_Case_ID_field", datatype="GPString", parameterType="Required", direction="Input") param3.value = "CASE_ID" # Fifth parameter param4 = arcpy.Parameter( displayName="Create unique Case ID numbers for:", name="case_key_combo_type", datatype="GPString", parameterType="Required", direction="Input") param4.filter.type = "valueList" param4.filter.list = ["all Primary keys and only matching Secondary keys","all Primary keys and all Secondary keys (Union)","only keys found in both the Primary and Secondary tables (Intersection)"] param4.value = "all Primary keys and only matching Secondary keys" newField = arcpy.Field() newField.name = param3.value newField.type = "LONG" newField.precision = 10 newField.aliasName = param3.value newField.isNullable = "NULLABLE" # Fifth parameter param5 = arcpy.Parameter( displayName="Output Primary Table", name="out_prim_table", datatype="DETable", parameterType="Derived", direction="Output") param5.parameterDependencies = [param0.name] param5.schema.clone = True param5.schema.additionalFields = [newField] # Sixth parameter param6 = arcpy.Parameter( displayName="Output Secondary Table", name="out_sec_table", datatype="DETable", parameterType="Derived", direction="Output") param6.parameterDependencies = [param1.name] param6.schema.clone = True param6.schema.additionalFields = [newField] params = [param0, param1, param2, param3, param4, param5, param6] return params def isLicensed(self): """Set whether tool is licensed to execute.""" return True def updateParameters(self, parameters): """Modify the values and properties of parameters before internal validation is performed. This method is called whenever a parameter has been changed.""" if parameters[0].value: # Return primary table tbl = parameters[0].value desc = arcpy.Describe(tbl) fields = desc.fields l=[] for f in fields: if f.type in ["String", "Text", "Short", "Long", "Float", "Single", "Double", "Integer","OID", "GUID"]: l.append(f.name) parameters[2].filters[0].list = l if parameters[1].value: # Return secondary table tbl = parameters[1].value desc = arcpy.Describe(tbl) fields = desc.fields l=[] for f in fields: if f.type in ["String", "Text", "Short", "Long", "Float", "Single", "Double", "Integer","OID", "GUID"]: l.append(f.name) parameters[2].filters[1].list = l if parameters[2].value != None: mylist = parameters[2].value for i, e in list(enumerate(mylist)): if mylist[2] != "Descending": mylist[2] = "Ascending" parameters[2].value = mylist return def updateMessages(self, parameters): """Modify the messages created by internal validation for each tool parameter. This method is called after internal validation.""" if parameters[3].value and parameters[0].value and parameters[1].value: desc = arcpy.Describe(parameters[0].value) fields = desc.fields in_primary = False is_primary_error = False is_primary_uneditable = False for f in fields: if parameters[3].value.upper() == f.name.upper(): in_primary = True if f.type != "Integer": is_primary_error = True elif not f.editable: is_primary_uneditable = False desc2 = arcpy.Describe(parameters[1].value) fields2 = desc2.fields in_secondary = False is_secondary_error = False is_secondary_uneditable = False for f2 in fields2: if parameters[3].value.upper() == f2.name.upper(): in_secondary = True if f2.type != "Integer": is_secondary_error = True elif not f2.editable: is_secondary_uneditable = False newField = arcpy.Field() newField.name = parameters[3].value newField.type = "LONG" newField.precision = 10 newField.aliasName = parameters[3].value newField.isNullable = "NULLABLE" fields1 = [] fields2 = [] order = [] for item in parameters[2].value: fields1.append(item[0].upper()) fields2.append(item[1].upper()) order.append(item[2]) if str(parameters[0].value).upper() == str(parameters[1].value).upper(): parameters[1].setErrorMessage("The Input Secondary Table {0} cannot be the same as the Input Primary Table {1} ".format(parameters[1].value, parameters[0].value)) else: parameters[1].clearMessage() if in_primary and in_secondary: if is_primary_error and is_secondary_error: parameters[3].setErrorMessage("{0} exists and is not a Long Integer field in both the Input Primary and Secondary Tables".format(parameters[3].value.upper())) elif is_primary_error: parameters[3].setErrorMessage("{0} exists and is not a Long Integer field in the Input Primary Table".format(parameters[3].value.upper())) elif is_secondary_error: parameters[3].setErrorMessage("{0} exists and is not a Long Integer field in the Input Secondary Table".format(parameters[3].value.upper())) elif parameters[3].value.upper() in fields1 and parameters[3].value.upper() in fields2: parameters[3].setErrorMessage("{0} is used as a Case Field for both the Input Primary and Secondary Tables".format(parameters[3].value.upper())) elif parameters[3].value.upper() in fields1: parameters[3].setErrorMessage("{0} is used as a Case Field for the Input Primary Table".format(parameters[3].value.upper())) elif parameters[3].value.upper() in fields2: parameters[3].setErrorMessage("{0} is used as a Case Field for the Input Secondary Table".format(parameters[3].value.upper())) elif is_primary_uneditable and is_secondary_uneditable: parameters[3].setErrorMessage("{0} exists and is not editable in both the Input Primary and Secondary Tables".format(parameters[3].value.upper())) elif is_primary_uneditable: parameters[3].setErrorMessage("{0} exists and is not editable in the Input Primary Table".format(parameters[3].value.upper())) elif is_secondary_uneditable: parameters[3].setErrorMessage("{0} exists and is not editable in the Input Secondary Table".format(parameters[3].value.upper())) else: parameters[3].setWarningMessage("{0} will be overwritten in both the Input Primary and Secondary Tables".format(parameters[3].value.upper())) elif in_primary: parameters[6].schema.additionalFields = [newField] if is_primary_error: parameters[3].setErrorMessage("{0} exists and is not a Long Integer field in the Input Primary Table".format(parameters[3].value.upper())) elif is_primary_uneditable: parameters[3].setErrorMessage("{0} exists and is not editable in the Input Primary Table".format(parameters[3].value.upper())) else: parameters[3].setWarningMessage("{0} will be overwritten in the Input Primary Table".format(parameters[3].value.upper())) elif in_secondary: parameters[5].schema.additionalFields = [newField] if is_secondary_error: parameters[3].setErrorMessage("{0} exists and is not a Long Integer field in the Input Secondary Table".format(parameters[3].value.upper())) elif is_secondary_uneditable: parameters[3].setErrorMessage("{0} exists and is not editable in the Input Secondary Table".format(parameters[3].value.upper())) else: parameters[3].setWarningMessage("{0} will be overwritten in the Input Secondary Table".format(parameters[3].value.upper())) else: parameters[5].schema.additionalFields = [newField] parameters[6].schema.additionalFields = [newField] parameters[3].clearMessage() return def execute(self, parameters, messages): """The source code of the tool.""" try: desc = arcpy.Describe(parameters[0].value) fields = desc.fields in_primary = False for f in fields: if parameters[3].value.upper() == f.name.upper(): in_primary = True if not in_primary: arcpy.AddField_management(parameters[0].value, parameters[3].value.upper(), "Long", 10) arcpy.AddMessage("Added a Case ID field to the Input Primary Table") desc2 = arcpy.Describe(parameters[1].value) fields2 = desc2.fields in_secondary = False for f2 in fields2: if parameters[3].value.upper() == f2.name.upper(): in_secondary = True if not in_secondary: arcpy.AddField_management(parameters[1].value, parameters[3].value.upper(), "Long", 10) arcpy.AddMessage("Added a Case ID field to the Input Secondary Table") tbl1 = parameters[0].value tbl2 = parameters[1].value fields1 = [] fields2 = [] order = [] for item in parameters[2].value: fields1.append(item[0]) fields2.append(item[1]) order.append(item[2]) arcpy.AddMessage("Primary Case Fields are {0}".format(str(fields1))) arcpy.AddMessage("Secondary Case Fields are {0}".format(str(fields2))) arcpy.AddMessage("Sort Orders are {0}".format(str(order))) import itertools k = list((r[0:]) for r in arcpy.da.SearchCursor(tbl1, fields1)) arcpy.AddMessage("Case Values have been read from the Input Primary Table") if parameters[4].value == "all Primary keys and all Secondary keys (Union)": j = list((r[0:]) for r in arcpy.da.SearchCursor(tbl2, fields2)) k = k + j j = None arcpy.AddMessage("Case Values have been appended from the Input Secondary Table") from operator import itemgetter for i, e in reversed(list(enumerate(order))): if order == "Descending": k.sort(key=itemgetter(i), reverse=True) else: k.sort(key=itemgetter(i)) k = list(k for k,_ in itertools.groupby(k)) if parameters[4].value == "only keys found in both the Primary and Secondary tables (Intersection)": j = {tuple(r[0:]):1 for r in arcpy.da.SearchCursor(tbl2, fields2)} arcpy.AddMessage("Case Values have been read from the Input Secondary Table") l = [] for item in k: if tuple(item) in j: l.append(item) j = None k = l l = None arcpy.AddMessage("Case Values have been matched to the Input Secondary Table") arcpy.AddMessage("A list of sorted and unique Case Values has been created") dict = {} fields1.append(parameters[3].value) fields2.append(parameters[3].value) for i in xrange(len(k)): dict[tuple(k)] = i + 1 k = None arcpy.AddMessage("A dictionary of unique Case Value keys with Case ID number values has been created") with arcpy.da.UpdateCursor(tbl1, fields1) as cursor: for row in cursor: if tuple(row[0:len(fields2)-1]) in dict: row[len(fields1)-1] = dict[tuple(row[0:len(fields1)-1])] else: row[len(fields2)-1] = -1 cursor.updateRow(row) del cursor arcpy.AddMessage("{0} values have been updated for Input Primary Table".format(parameters[3].value)) with arcpy.da.UpdateCursor(tbl2, fields2) as cursor2: for row2 in cursor2: if tuple(row2[0:len(fields2)-1]) in dict: row2[len(fields2)-1] = dict[tuple(row2[0:len(fields2)-1])] else: row2[len(fields2)-1] = -1 cursor2.updateRow(row2) del cursor2 arcpy.AddMessage("{0} values have been updated for Input Secondary Table".format(parameters[3].value)) except Exception as e: messages.addErrorMessage(e.message) return

‎05-09-2015

No, he can't. Summary Statistics adds nothing to the source and output data that permits a standard many-to-one join to work when a multi-field case field key is used. He wants a single field to represent the unique combination of values from 2 or more fields in both the source and output data so that a standard join will work to create an in memory many-to-one tableview. To do what he wants there is no help from Esri other than the failed Make Query Table tool (its performance is unacceptably bad and it does not support outer joins or work between different geodatabases or data directories like a standard join does). So each user is left on their own to write their own program to use a dictionary and cursor to track the multi-field unique values and write back an ID to both the source and output data (with no interface that makes their code reusable for other data source/field combinations), or create their own field that contains the concatenation of all of the values that make up the multi-field key to be able to do such a join (the option I normally use if I can modify the source data). I have written programs that can match multi-field keys using dictionaries and cursors and create a join field that does what he wants, but I have never developed the code to support a tool interface that can be added to a geoprocessing workflow that would allow the user to configure any input source, output table names and field configurations they want without having to do any code modifications. However, I have been thinking more and more that perhaps I should try. However, I do not think I will make the tool do the frequency or summary, just add the single join field to two data sources that share a multi-field match after such outputs are created. That way it will work no matter how the two data sources were created or even if the sources share a many-to-many relationship.

‎04-21-2015

The problem is that you cannot use del(row). You have to use cursor.deleteRow(row).

Online Status	Offline
Date Last Visited	4 weeks ago

My Ideas

Latest Contributions by RichardFairhurst

Re: Script running slow, how to speed up?

Re: Defintion query with apostrophe name

Re: Iterating feature classes and writting in a text file

Re: Iterating feature classes and writting in a text file

Re: Iterating feature classes and writting in a text file

Re: Frequency missing a case field

Re: Frequency missing a case field

Re: Frequency missing a case field

Re: arcpy function for attributes in a field?

Re: Frequency missing a case field

Re: Python Toolbox Value Table with list

Re: Frequency missing a case field

Re: Frequency missing a case field

Re: Frequency missing a case field

Re: Updating the rows in one table based on values in another table or using nested cursors

Add Dynamic, Epoch-Aware Spatial Reference Support

Add a Bearing WKID System Parallel to Spatial Refe...

What Does ArcGIS Pro 4.0 Really Need To Be For Dev...

Re: Turbo Charging Data Manipulation with Python C...

Re: Turbo Charging Data Manipulation with Python C...

Addressing