nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

Need Python Example: sort rows, subtract current from next, load in new field

NatalieLepsky

Please help! I am new to Python.

I created a model in ModelBuilder. The last steps are to grab the attribute table of the feature class, sort MEAS column in ascending order and then subtract next value from current and load those values in the new field. (I have an empty field in the attribute table).

I wrote a simple script, but all I could do is to sort the MEAS column and print values out.
Could anybody be so kind and get me an example code?....

Find more posts tagged with

Accepted answers

All comments

AliceThomas-Smyth

Hi, I found this thread because I am looking to do the same thing as the OP.
I tried the codes from both #9 and #10 and both times I got the same error:
Runtime error <type 'exceptions.TypeError'>: unsupported operand type(s) for -: 'int' and 'NoneType'

I have limited Python experience so I don't know for sure what that error means, I've interpreted it as that it does not recognize the variable 'previous' as something that can be subtracted. Do I need to somehow define 'previous' before the conditional?
Thanks!

RussellKallstrom

---
You have to know how the date field comes to you.

Sorry, what I wrote is confusing, it is not actually a date type field; I'd like to clarify the timestamp field is actually a text/string type field.

I've found that date fields generally tend to be difficult to deal with in ArcGIS, often resulting in more trouble than they're worth. As a result, it's often more convenient to save them as text string fields. Our data is coming off a tracklog saved from DNR Garmin, where the LTIME field was created and stored as a text field, length 20 characters. You might want to use the text field approach unless you have other reasons for sticking to the date data type.

RussellKallstrom

Please help! I am new to Python.

I created a model in ModelBuilder. The last steps are to grab the attribute table of the feature class, sort MEAS column in ascending order and then subtract next value from current and load those values in the new field. (I have an empty field in the attribute table).

I wrote a simple script, but all I could do is to sort the MEAS column and print values out.
Could anybody be so kind and get me an example code?....

---
Natalie- I've done something similar in the 9.3 scripting environment using some of Richard Crissup's code (he had an ArcScript posting on June 27, 2008 called date_diff.py). Basically, you start with a GPS generated track point shapefile with a time stamp field. To that, the python script adds a numeric double field, and in the new field, calculates the difference in hours between the timestamp field in each record and the next, so one can sum up the amount of time spent in an area. You have to know how the date field comes to you. The script takes a while to execute, but gets the job done.

I'm new to Python, too, so please people, go easy on me, too:

import arcgisscripting, sys, os, re
import time, calendar, string, decimal

def func_check_format(time_string):
 if time_string.find("/") == -1:
  print "Error: time string doesn't contain any '/' expected format \
  is month/day/year hour:minutes:seconds"
 elif time_string.find(":") == -1:
  print "Error: time string doesn't contain any ':' expected format \
  is month/day/year hour:minutes:seconds"
 
 list = time_string.split()
 if (len(list)) <> 2:
  print "Error time string doesn't contain and date and time separated \
  by a space. Expected format is 'month/day/year hour:minutes:seconds'"


def func_parse_time(time_string):
    '''
        take the time value and make it into a tuple with 9 values
        example = "2004/03/01 23:50:00". If the date values don't look like this
        then the script will fail. 
    '''
    year=0;month=0;day=0;hour=0;minute=0;sec=0;
    time_string = str(time_string)
    l=time_string.split()
    if not len(l) == 2:
        gp.AddError("Error: func_parse_time, expected 2 items in list l got" + str(len(l)) + "time field value = " + time_string)
        raise Exception 
    cal=l[0];cal=cal.split("/")
    if not len(cal) == 3:
        gp.AddError("Error: func_parse_time, expected 3 items in list cal got " + str(len(cal)) + "time field value = " + time_string)
        raise Exception
    ti=l[1];ti=ti.split(":")
    if not len(ti) == 3:
        gp.AddError("Error: func_parse_time, expected 3 items in list ti got " + str(len(ti)) + "time field value = " + time_string)
        raise Exception
    if int(len(cal[0]))== 4:
        year=int(cal[0])
        month=int(cal[1])
        day=int(cal[2])
    else:
        year=int(cal[2])
        month=int(cal[0])
        day=int(cal[1])       
    hour=int(ti[0])
    minute=int(ti[1])
    sec=int(ti[2])
    #formated tuple to match input for time functions
    result=(year,month,day,hour,minute,sec,0,0,0)
    return result
 
 
#----------------------------------------------------------------------------

def func_time_diff(start_t,end_t):
    '''
        Take the two numbers that represent seconds
        since Jan 1 1970 and return the difference of
        those two numbers in hours. There are 3600 seconds
        in an hour. 60 secs * 60 min   '''
    
    start_secs = calendar.timegm(start_t)
    end_secs = calendar.timegm(end_t)
    
    x=abs(end_secs - start_secs)
    #diff = number hours difference
    #as ((x/60)/60)
    diff = float(x)/float(3600)   
    return diff

#----------------------------------------------------------------------------

print "Executing getnextLTIME.py script..."

try:
    gp = arcgisscripting.create(9.3)
    
    # set parameter to what user drags in
    fcdrag = gp.GetParameterAsText(0)
    psplit = os.path.split(fcdrag)
    
    folder = str(psplit[0]) #containing folder
    fc = str(psplit[1]) #feature class
    fullpath = str(fcdrag)

    gp.Workspace = folder

    fldA = gp.GetParameterAsText(1) # Timestamp field
    fldDiff = gp.GetParameterAsText(2) # Hours field
    
    # set the toolbox for adding the field to data managment
    gp.Toolbox = "management"
    # add the user named hours field to the feature class
    gp.addfield (fc,fldDiff,"double")
    #gp.addindex(fc,fldA,"indA","NON_UNIQUE", "ASCENDING")
    
    desc = gp.describe(fullpath)
    updateCursor = gp.UpdateCursor(fullpath, "", desc.SpatialReference, fldA+"; "+fldDiff, fldA)
    row = updateCursor.Next()
    count = 0
    oldtime = str(row.GetValue(fldA))
    #check datetime to see if parseable
    func_check_format(oldtime)
    gp.addmessage("Calculating " + fldDiff + " field...")
    
    while row <> None:
        if count == 0:
            row.SetValue(fldDiff, 0)
        else:
            start_t = func_parse_time(oldtime)
            b = str(row.GetValue(fldA))
            end_t = func_parse_time(b)
            diff_hrs = func_time_diff(start_t, end_t)
            row.SetValue(fldDiff, diff_hrs)
            oldtime = b
            
        count += 1
        updateCursor.UpdateRow(row)
        row = updateCursor.Next()

    gp.addmessage("Updated " +str(count+1)+ " rows.")
    #gp.removeindex(fc,"indA")
    del updateCursor
    del row
    
except Exception, ErrDesc:
    import traceback;traceback.print_exc()
    
print "Script complete."

TerrySilveus

That code worked, thanks! But when I define the 'NEW' field as date - I get 12:00 AM for all rows. When I define 'NEW' as double - it comes out as 0. Which is correct - 0 days. I tried to multiply by 24 to get hours - still zeros. Is there any way to solve that?

You'll need to have a field that recognizes time to get that information.

DarrenWiens1

I was wrestling with time fields yesterday (they were all getting reset to 12:00am). Apparently, shapefiles do not understand time, so they become 12:00am (or zero). The time data is gone, it's not being hidden by the datatype, so multiplying by 24 will not help. I ended up working in a Personal Geodatabase - they understand time.

NatalieLepsky

That code worked, thanks! But when I define the 'NEW' field as date - I get 12:00 AM for all rows. When I define 'NEW' as double - it comes out as 0. Which is correct - 0 days. I tried to multiply by 24 to get hours - still zeros. Is there any way to solve that?

TerrySilveus

Try something like this

import arcpy
import datetime, time #not sure if you need both of these


myFile = "c:/gis/temp.shp"
sortField = "theDate"
newField = "NEW"

rows = arcpy.UpdateCursor(myFile,"","","",sortField + " A") # sort by sort field
firstTime = True
for row in rows:
   if firstTime:
      previous = row.getValue(sortField) #in your first iteration you will not have anything to update you just need to get the value for the next iteration
      firstTime = False
   else:
       #row.setValue(newField, row.getValue(sortField) - previous)
       current = row.getValue(sortField)
       difference = current-previous # datetime.timedelta object (time difference)
       row.setValue(newField, difference.days) # number of days
       rows.updateRow(row)
       previous = current  #get the value for the next iteration

NatalieLepsky

There is something else that I need help with....
I used the example (thread #9), and it worked for the fields where sortField and newField were both 'double' type. When I tried to do the calculations on the Date field mm/dd/yyyy hh:mm:ss AM/PM I get an error message. It says 'ValueError: Row: Invalid input value for SetValue'. My resulting field is 'double'. Do the fields have to be the same data type? Is it possible to use Date type is the calculations?

NatalieLepsky

Everything worked! Thank you all so much!

DarrenWiens2

Oops - in my example I never gave it any commands for what to do if it was the first time around the loop, so the counter stayed at 0 and never got into the update part. Here's what it should be (you could also add an updateRow to the else part if you want the very first row to have a value - this way it will just be skipped over).

for row in rows:
    if count != 0: # if this isn't the first time in the loop (ie. if you have a number to subtract)
        count = count + 1
        row.blankcolumnname = row.MEAS - previous # make the blank column = current - previous
        rows.updateRow(row) # updates the whole row, now with a value for the blank column
        previous = row.MEAS # sticks the current value into the previous variable for the next time through the loop
    else: # do this if it is the first time in the loop
        count = 1
        previous = row.MEAS

TerrySilveus

It probably won't update if the table is open...
I tested this code below and it works.

import arcpy

myFile = "c:/gis/temp.shp"
sortField = "MEAS"
newField = "NEW"

rows = arcpy.UpdateCursor(myFile,"","","",sortField + " A") # sort by sort field
firstTime = True
for row in rows:
   if firstTime:
      previous = row.getValue(sortField) #in your first iteration you will not have anything to update you just need to get the value for the next iteration
      firstTime = False
   else:
       row.setValue(newField, row.getValue(sortField) - previous)
       rows.updateRow(row)
       previous = row.getValue(sortField)  #get the value for the next iterationexcept:

del row
del rows

NatalieLepsky

Fixed a typo and got no more errors. Thanks! But the field in my table didn't get updated...
I ran exactly what you sent me. What else could it be?

# Import the arcpy
import arcpy
# Input feature class.
rows = arcpy.UpdateCursor("C:/model_build/ModelBuildGeodatabase.mdb/locate_feature_layer_sort")
count = 0
for row in rows:
    if count != 0:
        count = count + 1
        row.MEAS_DIST = row.MEAS - previous
        rows.updateRow(row)
        previous = row.MEAS
del row
del rows

DarrenWiens2

start with:

import arcpy

and the actual update cursor statement should be like:

rows = arcpy.UpdateCursor(FILL IN YOUR PARAMETERS HERE)

NatalieLepsky

Thanks so much!
But I ru the code I get an error: 'module' object has no attribute 'Update'
What would that be?

DarrenWiens2

rows = UpdateCursor(...)

count = 0

for row in rows:
    if count != 0: # if this isn't the first time in the loop (ie. if you have a number to subtract)
        count = count + 1
        row.blankcolumnname = row.MEAS - previous # make the blank column = current - previous
        rows.updateRow(row) # updates the whole row, now with a value for the blank column
        previous = row.MEAS # sticks the current value into the previous variable for the next time through the loop

del row
del rows # remove row locks

TerrySilveus

put the value into a variable for later use. i.e. air code below

firstTime = True
for row in rows:
   if firstTime:
      myMeas = row.getValue('MEAS') #in your first iteration you will not have anything to update you just need to get the value for the next iteration
      firstTime = False
   else:
      row.setValue('MEASCalc', row.getValue('MEAS') - myMeas
      rows.updateRow(row)
      myMeas = row.getValue('MEAS')  #get the value for the next iteration

NOTE: I fairly unfamiliar with python so you may have to tweak the syntax to make this work

NatalieLepsky

Thanks for your reply! I created a new dataset with sorted rows using a simple script. But still have problems with UpdateCursor. I try to retain the current value, but nothing works. Do you have a similar looping example?

DarrenWiens2

You should use an UpdateCursor (there's an example). In the UpdateCursor statement, you specify which fields to sort. Then you loop through all your rows, each time sticking the current value into a variable to save it for the next row where you update the empty column with that value.

Join the Conversation

Signed in members can post, follow updates, and more. New here? Register a free account.

Quick Links

Community Help
Find useful guides, FAQs, and documents to help you navigate and make the most of Esri Community.