I need to be able to search through a set of data points, each with a time stamp, and insert rows between two data points that are more than 5 seconds apart. The first pass with the cursor is used to turn the time stamp into an integer so that I can work with it, but after that it won't reset and attempts to open another cursor, even after deleting the first, don't work. Any ideas of how to do this?
It is good practice to only attempt one pass with an update cursor to completion, then after that use a new cursor to do any inserts. You do not have to create a new field to work with the time stamp as an integer, and could instead do this within a python dictionary or list in memory, so that you would use a search cursor to load it all into memory, analyze it there, then use an insert cursor to create the new points. That way no conflict between cursors would occur. But without more info I cannot propose any code. Post the code you are attempting so we have some idea of what you are actually trying.
a few lines of the table would also be interesting as well. Given your description, it doesn't appear that you will be joining the table back to anything, since the row insertion would foul up the current ID system. options abound
Thanks!
Here is what I have so far. It creates a report of where the gaps are, but I can't get a second cursor to exist.
def process_table(): | #create the hms and day fields, create lists and totals for the summaries | ||||||
#Creates a cursor to iterate through the table, grabs the field we are interested in | |||||||
rows = arcpy.UpdateCursor(table) | |||||||
#Create initial conditions for the previous row, and a bool for the first iteration | |||||||
prev_t = 0 | |||||||
prev_x = 0 | |||||||
prev_y = 0 | |||||||
prev_day = "" | |||||||
first = True | |||||||
dic = {} | |||||||
total_gaps = 0 | |||||||
over_2 = 0 | |||||||
over_5 = 0 | |||||||
over_100_meters = 0 | |||||||
delta_t = 0 | |||||||
extremes_lst = [] | |||||||
for row in rows: | |||||||
full_date = row.getValue(date_fld) | #Get the date and time | ||||||
date_lst = full_date.split("T") | #Split into date and time | ||||||
row.setValue("Day", str(date_lst[0])) | #Set the date to be the first half | ||||||
full_time = date_lst[1] | #Full time is the second | ||||||
time_lst = full_time.split(":") | #Split the time | ||||||
hr = int(time_lst[0]) | #into hours | ||||||
mn = int(time_lst[1]) | #into minutes | ||||||
sec = int(time_lst[2][0:2]) | #into seconds, cut off the "Z" | ||||||
hms = 3600*hr + 60*mn + sec | #make the integer time | ||||||
row.setValue("HMS", hms) | #store the integer time | ||||||
rows.updateRow(row) | #save the row | ||||||
if first != True: | |||||||
if hms - prev_t> 5: |
distance = calculate_distance(prev_x, row.getValue("Longitude"), prev_y, row.getValue("Latitude")) | ||||
rows_to_add = (hms-prev_t)/5 - 1 | ||||
total_gaps += rows_to_add | ||||
delta_t = str((hms-prev_t)/60) + " minutes and " + str((hms-prev_t)%60) + " seconds" | ||||
to_add = (rows_to_add, delta_t, distance) | ||||
fid_lst.append( (int(row.getValue("FID"))-1, rows_to_add, (prev_x, row.getValue("Longitude")), (prev_y, row.getValue("Latitude")), distance) ) | ||||
#0: Row's FID | ||||
#1: Number of rows to add | ||||
#2: Tuple of x's | ||||
#3: Tuple of y's | ||||
#4: Distance | ||||
if distance > 100: | |||||||
over_100_meters +=1 | |||||||
extremes_lst.append(to_add) | |||||||
if rows_to_add > 24: | |||||||
over_2 +=1 | |||||||
if rows_to_add > 60: | |||||||
over_5 +=1 | |||||||
if to_add not in extremes_lst: | |||||||
extremes_lst.append(to_add) |
if rows_to_add not in dic: | |||||
dic[rows_to_add] = 1 | |||||
else: | |||||
dic[rows_to_add] += 1 | |||||
#Update the previous parameters | ||
first = False | ||
prev_t = hms | ||
prev_x = row.getValue("Longitude") | ||
prev_y = row.getValue("Latitude") | ||
prev_day = row.getValue("Day") | ||
return extremes_lst, dic, over_2, over_5, over_100_meters, total_gaps, fid_lst |
From this I've been trying to use fid_lst to find where to insert rows. Something like:
insert = arcpy.InsertCursor(table)
for row in insert:
if row.getValue("FID") == fid_lst[0][0]: #the fid
rows_to_add = fid_lst[0][1] #the rows to add
for x in range(1, rows_to_add + 1)
#add new rows
fid_lst.pop(0)
for i in fid_lst:
i[0] += rows_to_add #accounts for the shift in FID from adding the new rows
Any advice is appreciated!
Chris
I actually just went back through and researched the arcpy.da.InsertCursor object. It inserts rows now, but is there a way to insert the row in the middle of the table instead of at the end?
first Code Formatting... the basics++
too hard to dissect what is going on
You should also change any search or update cursor to the da version, since they are 10 times faster than the old style Python cursors.
Unfortunately the answer is no, you can't insert new records between existing records within an existing feature class. ObjectIDs are issued consecutively and cannot be shifted and if deleted they cannot be reused. The only safe way to get the records organized based on attributes is by using the Sort tool, which will create a new feature class. This tool resets all ObjectIDs in the new feature class to fit the new order and removes any ObjectID gaps that were created by deleted records. You then would replace the original FC with the new sorted FC.
If you really insist on not creating a new feature class and are willing to put all of your data at risk, then after inserting every record, you could load everything to a sorted dictionary or list and then use an update cursor to overwrite every record, setting all editable fields with the data of the appropriate record that fits the order you want. ObjectID gaps from deleted records would remain gaps after using this approach, but the records would be reordered without creating a new FC. Aside from the risk to your data, if you have GlobalIDs or other non-editable fields that don't reset based on the changes you make then you probably don't want to do this, since there is no way their field values could be kept with the original record they were associated with.
#Chris Lowrie #Refactoring 1 #Bluespace Report and Row Creation Script #import the necessary bits import arcpy, math, os #Get the points to use table = arcpy.GetParameterAsText(0) name = arcpy.GetParameterAsText(1) fields = ["Latitude","Longitude", "HMS", "Day"] date_fld = "DateTimeS" arcpy.env.workspace = "C:\Users\project-user\Desktop\BlueSpace" f = open(name + "_Bluespace_report.txt", "w") fid_lst = [] #Calculate the distance between two points near Wellington, New Zealand #Can be made more accurate if desired def calculate_distance(x0, x1, y0, y1): #111,200 meters per degree of latitude #67,097 meters per degree of longitude at 41 degrees latitude (Wellington) dx = abs(x1-x0)*67097 dy = abs(y1-y0)*111200 distance = math.sqrt(math.pow(dx,2) + math.pow(dy,2)) return distance def fill_gap(row, rows_to_add): edit.startOperation() insert_cursor = arcpy.da.InsertCursor(table, fields) f.write("Insert") i = 1 f.write(str(rows_to_add)) while i < rows_to_add+1: to_insert = (row[0], row[1], row[2]+5*i, row[3]) insert_cursor.insertRow(to_insert) i += 1 edit.stopOperation() def process_table(): #Creates an update cursor rows = arcpy.da.SearchCursor(table, fields) prev_t = 0 prev_x = 0 prev_y = 0 total_gaps = 0 over_1 = 0 over_5 = 0 over_100_meters = 0 delta_t = 0 first = True dic = {} extremes_lst = [] fid_lst = [] #Iterator for row in rows: #the time hms = row[2] #If not the first pass if first != True: if hms - prev_t > 5: distance = calculate_distance(prev_x, row[1], prev_y, row[0]) delta_t = hms-prev_t f.write(str(delta_t)+"\n") delta_t_str = str(delta_t/60) + " minutes " + str(delta_t%60) + " seconds " rows_to_add = delta_t/5 - 1 fid_lst.append(prev_row) fill_gap(prev_row, rows_to_add) first = False prev_t = hms prev_x = row[1] prev_y = row[0] prev_row = row workspace = os.path.dirname(table) edit = arcpy.da.Editor(workspace) edit.startEditing(False, False) process_table() edit.stopEditing(True)
Thanks, much needed formatting tutorial. It now adds rows with the values that I want, but I'd like them to be added directly beneath the row I am sending to fill_gap.
Chris your formatting is still off. your fill_gap def has no indentation in the while loop, so it won't work and your spacing changes from 2 to 4 spaces within the function. Can you confirm that the above is what you are working with or is it just a bad copy
Maybe one of the db gurus can step in here, but I think there is no concept of ordering or gaps in a database table.
All inserts will be appended to the end of the table.
The only way to get them to be ordered is to reprocess and recreate the data.
You can obviously impose an ordering on your view of the data (like sorting the table in ArcMap), but this is doing nothing to the actual order on disc.