I have a shapefile representing points along a line. I have the downstream distance of each point on their respective line. What I would like to do is sequence the two fields in the attribute table based on the spatially joined stream id arcid
and downstream distance DSDistance
. I would then like to run an operation such that the difference in 'DSDistance' between two points with the same stream id is calculated:
fid arcid DSDistance to fid arcid DSDistance Distance
1 1 10 1 1 10 10
2 5 10 4 1 15 5
3 3 7 3 3 7 7
4 1 15 2 5 10 10
5 5 20 5 5 20 10
6 5 27 6 5 27 7
The code I have written to do this operation is below:
fields = ["arcid","DSDistance"]
sql_orderby = "ORDER BY" + ", ".join("{} ASC".format(field) for field in fields)
with arcpy.UpdateCursor(home + '/PourPoints1.shp',"",sql_clause=(None, sql_orderby)) as cursor:
firstTime = True
for row in cursor:
if firstTime:
previous = row.getValue('arcid')
previous1 = row.getValue('DSDistance')
firstTime = False
else:
current = row.getValue('arcid')
if current == previous:
row.setValue('Distance', row.getValue('DSDistance') - previous1)
rows.updateRow(row)
previous1 = row.getValue('DSDistance')
else:
row.setValue('Distance', row.getValue('DSDistance'))
rows.updateRow(row)
del row
del rows
I have written this code from sort rows, subtract current from next, ... and updating field on previous row.
Update using the solution below I receive an error:
Traceback (most recent call last): File "H:\Projects\260\Storm Water\GIS\PythonScript\table\BasinScript.py", line 267, in inCursor.insertRow(newRow) SystemError: error return without exception set
My workspace destination is a folder
home = r'H:/Projects/260/Storm Water/GIS/PythonScript/Workspace'
# Path to input table
tblPath = home + '/PourPoints1.shp'
# Create a defaultdict
dd = collections.defaultdict(list)
dd1 = collections.defaultdict(list)
# create an output table
print("Creating output table...")
arcpy.env.overwriteOutput=True
arcpy.CreateTable_management(home,"tblOutput.dbf")
arcpy.AddField_management(home + '/tblOutput.dbf',"arcid","LONG")
arcpy.AddField_management(home+ '/tblOutput.dbf',"DSDistance","LONG")
arcpy.AddField_management(home+ '/tblOutput.dbf',"Dist","LONG")
arcpy.AddField_management(home+ '/tblOutput.dbf',"TARGET_FID","LONG")
# read data into dictionary
print("Reading input table...")
with arcpy.da.SearchCursor(tblPath,["arcid","DSDistance","TARGET_FID"]) as cursor:
for row in cursor:
arcid = row[0]
dist = row[1]
dd[arcid].append(dist)
fid = row[2]
dd1[arcid].append(fid)
for k, v in dd1.items():
dd[k] = [dd[k], v]
# sort dictionary by key into tuples of lists
sortedlist = sorted(dd.items())
# Process sortedlist
print("Writing to output table...")
with arcpy.da.InsertCursor(home + '/tblOutput.dbf',["arcid","DSDistance","Dist","TARGET_FID"]) as inCursor:
for tup in sortedlist:
arcid = tup[0] # e.g. 5
disList = sorted(tup[1][0]) # e.g [10,20,27]
distList1 = tup[1][1]
if len(disList) > 1:
# create a second list that is an offset of disList
offList = list(disList) # Copy list
offList.insert(0,0) # insert zero at start of list
offList.pop() # remove last value, so using example above you will have [0,10,20]
# Compute offset value
zipobj = zip(disList,offList)
diffList = list()
for i1,i2 in zipobj:
diffList.append(i1-i2)
# Write output to table, here we are accessing the values in the list by the index position i
for i in range(len(disList)):
newRow = (arcid,disList[i],diffList[i],disList1[i])
inCursor.insertRow(newRow)
else:
# Just one entry in input table so write it out
newRow = (arcid,disList[0],disList[0],disList1[0])
inCursor.insertRow(newRow)
Update:
Adding the tblOutput path extension .dbf
successfully produced a table with the solution I was looking for. Modified the solution to bring across a unique field ID to make joining the output table to the shape file easier.
Update: FelixIP's solution sets a warning:
extract["DELTA"] = extract[chainageField].shift(-1)-extract[chainageField] SettingWithCopyWarning: A value is trying to be set on a copy of a slice from a DataFrame. Try using .loc[row_indexer,col_indexer] = value instead
Update: Updated the solution to bring a unique field from input table over to output table.