I have this sample file shown in the picture 1 below. it is a sample data set of a phone survey of drivers. I am trying to find out when those drivers started to drive.
My main goal is to build a loop to go through the rows and compare each row for each unique trip id and to check between those rows when the X (dbX field) and the Y (dbY field) have changed. In my example (Picture 1) I have only two unique id trips - 55,56 but in my main data set i have alot more:
This is what I am trying to achieve in my goal data set:
First row in goal data set (see picture two) will be based on: For example in picture 1 - the X and Y values in the first row changes from OBJECTID =6, when those values change I want to delete all previous rows until row 5.
You can see an example of what I am trying to accomplish in picture 2.
Next values Until last rows - For this values, I want to keep the first row where X and Y values changed and delete all values of the same X and Y untill the last row before the next X,Y changes again. to make this step clear I want to keep only rows with OBJECTID = 6 AND 13 and (picture 2)
Keep in my mind there are some Unique rows that Won't have more rows with the same X and Y like the row with OBJECTID = 78.
- Last row - at end of each unique id I want to keep only the first row that is diffrent from the previous row in terms of x and Y : for example in picture 1 I want to keep only OBJECTID = 149 and delete OBJECTID =1 150 AND 151
Initial Data set picture 1:
This is the code I started writing but I think I am not on the right track
fcVertex ="Copy_Points_with_id_north_to_center_sample_55_57_copy" fieldsvertex = ["dbX","dbY","Trip_ID","TimeStamp"] with arcpy.da.UpdateCursor(fcVertex,fieldsvertex,sql_clause=(None, 'ORDER BY Trip_ID ASC ; ORDER BY TimeStamp ASC')) as deleteCursorfixtime: for row1 in deleteCursorfixtime: for row2 in deleteCursorfixtime: if row1 == row2: if row1== row2 and row1 == row2: if row1 != row2: deleteCursorfixtime.deleteRow()
Goal Data set picture 2: