4

Can you help me to compare fields in two tables? I have two tables, which contain ID and Measure fields. I need to take stroke by stroke values from field1(table1) and find all values in field1(table2) which are less than sought-for value. And then take largest of them and put it on stroke field2(table1). Example showed on picture below.

It's will be great, if it would be python script.

enter image description here

6
  • I don't understand the logic you're using in your example excel table. Can you explain more clearly how you declare values for field 2 in your results table? This question may help you.
    – Roy
    Commented Sep 4, 2013 at 12:48
  • 1
    As far as I can tell, he wants to find the largest value in table 2 that is less than the current value in table 1.
    – Paul
    Commented Sep 4, 2013 at 12:59
  • Yes. And put in on field2 of 1st table ( stroke by stroke)
    – user21700
    Commented Sep 4, 2013 at 13:29
  • I assume these tables are much larger than shown. What happens if the value of table1 is less than all values in table2?
    – Roy
    Commented Sep 4, 2013 at 13:37
  • then field2 will equal field1
    – user21700
    Commented Sep 4, 2013 at 13:41

1 Answer 1

3

I'm a GIS guy, not a professional programmer so I'm sure there's a more efficient way to write this. I tested the following code on the dummy data you provided. You'll have to make sure it works on an extended dataset (hence the extensive comments so you can modify it).

Pseudo Code:

  1. Create a python list of all values in table 2 using a search cursor.
  2. Loop through table 1 with an Update cursor testing against each value in your new list.
  3. If current value in list is less than the value of table one, store it in a variable.
  4. If current value in list is greater than the value in table one, use the variable from the last iteration to update field 2.

Script:

import arcpy

# Put the absolute path to your tables / feature classes
table1 = r"C:\SomePathHere" 
table2 = r"C:\SomeOtherPathHere" 

# Create empty Python list
table2list = []

# Arcpy Search cursor
rows = arcpy.SearchCursor(table2)

# Get a list of all values in table2 field1
for row in rows:
    table2list.append(row.Field1)

# Delete cursor objs
del row, rows

# Sort the list in numeric order, get the highest value
table2list = sorted(table2list)
highestVal = table2list[len(table2list)-1]

# Create update cursor for table1
rows = arcpy.UpdateCursor(table1)

# Nested loop -- for each row in table1 we loop through our list of table2 values
for row in rows:
    # Reset stored value var for each loop
    storedValue = 0
    # If Field1 is less than the smallest value in table2, Field2 is equal to Field1
    if (row.Field1 < table2list[0] and row.Field < 11):
        row.Field2 = 0
        rows.updateRow(row)
    elif (row.Field1 < table2list[0] and row.Field > 10):
        row.Field2 = row.Field1
        rows.updateRow(row)
    # If Field1 is greater than the highest value, Field2 equals highest value
    elif row.Field1 > highestVal:
        row.Field2 = highestVal
        rows.updateRow(row)

    # When the value in the list is greater than Field1, use the last value to update Field2
    for i in table2list:
        if i < row.Field1:
            storedValue = i
        else: # This is where we're actually changing the value of Field2
            row.Field2 = storedValue
            rows.updateRow(row)

del row, rows
13

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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