Compare values in a table based on year

AmyKlug

I have an original table with values like this:
Line ID    Year       Code
0001       2002        300
0001       2003        300
0001       2004        500

I want to find the year the code changes for each line and add that into a table that only shows the year the lines that had code changes. Example:

Line ID     Year_Change
0001         2004

I was thinking of creating 2 dictionary’s {LID: Year} and {LID: Code} and inserting the values into a new table like this:

Line ID      Code2002     Code2003    Code2004
0001           300                300                  500

Then using if statements, such as
If row[1] = row[2]
    Next
Elif: row[2] = row[3]
    next

Else:
    Create dictionary entry {LID: YearChange} which will be inserted into a table.

I think I am in a coding rut and need fresh ideas! I’d like to work with the original table. Anyone know of another way to create a loop comparing a previous record based on a 2 variable (LID and Year) expression?

FilipKrál

How about something like the code below?

# input table (must be geodatabase to allow sorting)
tbl = r'C:\TEMP\tdb.gdb\sorting'

# add column to record the change
arcpy.management.AddField(tbl, "changed", "SHORT")
orderby = 'ORDER BY line_id, theyear, thecode'
last_id, last_code = None, None
with arcpy.da.UpdateCursor(tbl, ['line_id', 'theyear', 'thecode', 'changed'], sql_clause=(None, orderby)) as uc:
    for row in uc:
        this_id, this_year, this_code, changed = row
        if this_id == last_id:
            if last_code != this_code:
                newrow = [this_id, this_year, this_code, 1] # change!
                uc.updateRow(newrow) #save 
        last_id, last_code = this_id, this_code