-1

In my data, I have a table view where I am trying to group same records (PID), then filter records by date (ROW_CREATE_DATE). if the record has the date of 2019-03-05 then find records in the line # field (ROW_LINE_NUM) that = 1. Alternatively, within the group of same records (PID), if the date doesn't match 2019-03-05, I need to find the max value of the (ROW_LINE_NUM) within the group of records with the same PID and delete all records from each group that are NOT the max value.

I am a Python novice, but this one is a little bigger than I can figure out.

try:
    Current_Owner_Fields = ["ROW_CREATE_DATE","ROW_LINE_NUM"]
    # Keep any records from 3/5/19 or prior with a ROW_LINE_NUM of 1, and any records later than 3/5/19 with the max record number
    ROW_LINE_CURSOR = arcpy.da.UpdateCursor(CURRENT_OWNER_TBL_View,"ROW_LINE_NUM")
    for row in ROW_LINE_CURSOR:
        maxcursor = arcpy.da.SearchCursor(CURRENT_OWNER_TBL_View,"ROW_LINE_NUM")
        Max_Line = maxcursor
        if row[0] == ('2019-03-05') and row[1] == "1":
            pass
        groups = []
        for row in maxcursor:
            if row.Group not in groups:
                groups.append(row.Group)
        del row

        out_dict = {}
        for row in maxcursor:
            for g in groups:
                lst = []
                if row.Group == g:
                    lst.append(row.Distance)
            out_dict(g) = max(lst)
            del row, maxcursor
        for group in out_dict:
            if row[0] > ('2019-03-05'):
            append.group()
        else:
            ROW_LINE_CURSOR.deleteRow(row)
    print (" Current owner sorted from CURRENT_OWNER_TBL_View...")
except:
    print ("\n Unable to sort current owner from CURRENT_OWNER_TBL_View")
    write_log("\n Unable to sort current owner from CURRENT_OWNER_TBL_View", logfile)
    logging.exception('Got exception on sort current owner from CURRENT_OWNER_TBL_View logged at:' + str(Day) + " " + str(Time))
    raise
    sys.exit ()
0

You have not detailed what the problem with your code is. You haven't told us what it is doing (or not doing) that is unexpected.

However, rather than using multiple cursors, it can be much simpler to simply use a table view with a where_clause to include only the records required and/or to use a where_clause in the search cursor. Something like:

view20190305 = "view20190305"
viewOtherDates_max = "viewOtherDates_max"

listOfPIDs = []
with arcpy.da.SearchCursor(CURRENT_OWNER_TBL_View, ["PID"]) as cur:
    for row in cur:
        if row[0] not in listOfPIDs:
            listOfPIDs.append(row[0])

for pid in listOfPIDs:
    arcpy.MakeTableView_management(CURRENT_OWNER_TBL_View, view20190305_1, where_clause="PID = '{}' AND Date == '2019-03-05' AND Row_Line_Num == '1')".format(pid))
    with arcpy.da.SearchCursor(CURRENT_OWNER_TBL_View, ["ROW_LINE_NUM"], where_clause="PID = '{}' AND (Date <> '2019-03-05')".format(pid)) as cur:
        maxLineNum = -1
        for row in cur:
            if row[0] > maxLineNum:
                maxLineNum = row[0]
    arcpy.MakeTableView_management(CURRENT_OWNER_TBL_View, viewOtherDates_max, where_clause="PID = '{}' AND Date <> '2019-03-05' AND Row_Line_Num == '{}')".format(maxLineNum))
    #  Do stuff with found table view data here
    ...
    ...
    arcpy.Delete_management(view20190305)
    arcpy.Delete_management(viewOtherDates_max)

So for each PID, you would have two table views to do with what you want:

  • view20190305
  • viewOtherDates_max

Would something like this suit your situation?

The above code does not actually delete the non-max records, but this could easily be done without cursors by including something like:

arcpy.MakeTableView_management(CURRENT_OWNER_TBL_View, "viewOtherDates_non_max", where_clause="PID = '{}' AND Date <> '2019-03-05' AND Row_Line_Num <> '{}')".format(maxLineNum))
arcpy.DeleteRows_management("viewOtherDates_non_max")
arcpy.Delete_management("viewOtherDates_non_max")
0

You can use pandas module. Untested since I have no data to try it on, backup before you try it:

import pandas as pd
import arcpy

#Input fc and fields, change to match your data
fc = r'C:\data.gdb\somefc'
pidcol = 'PID'
datecol = 'SOMEDATECOLUMN'
rowlinenumcol = 'ROW_LINE_NUM'

#Create pandas dataframe
fields = [f.name for f in arcpy.ListFields(fc) if f.type != 'Geometry']
df = pd.DataFrame.from_records(data=arcpy.da.SearchCursor(fc,fields), columns=fields)
oidcolumn = arcpy.Describe(fc).OIDFieldname

#List oids to keep using groupby and selections
to_keep = []
for name, group in df.groupby(pidcol):
    selection = group.loc[group[datecol]=='2019-03-05' & group[rowlinenumcol]==1]
    if not selection.empty:
        to_keep.append(selection.oidcolumn)
    else:
        to_keep.append(group.loc[group[rowlinenumcol].idxmax()].oidcolumn)

#Delete rows with oids not listed
with arcpy.da.UpdateCursor(fc,'OID@', "{0} NOT IN{1}".format(arcpy.AddFieldDelimiters(fc,oidcolumn),tuple(to_keep))) as cursor:
    for row in cursor:
        cursor.deleteRow()

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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