Field calculation with python

[KONPETROV]

Hello, i am very new in Python and i need a help at a script i want to build. I want to take the first value from a specific column and put it in the column b, and after that to substact a value = 5 from each row. So as to have this:

a               b

50               50

30               45

60               40

29               35

.

Any help at that? Thank you!

[DarrenWiens2]

If your OIDs are in order and consecutive (0,1,2,3,...), you could apply a field calculation like:

ColumnA - (OID*5)

The better, more complicated way, not relying on OIDs, would be to use an UpdateCursor and a counter to update each row value. Inside the cursor, you would have something like:

row[0] = row[1] - (counter * 5)
counter = counter + 1
cursor.updateRow(row)

[KONPETROV]

with update cursor with your example won't i get

50 - 0

30 - 5

60 - 10?

[DarrenWiens2]

Oh, you're right, yes, you would. Read too fast.

As Blake is getting at, you would first have to either collect the first or max value (whichever you want) into a variable and then run through the cursor:

row[0] = maxValue - (counter * 5)  
counter = counter + 1  
cursor.updateRow(row) 

[BlakeTerhune]

Saying you want the "first" value is very relative; it depends on how the table is sorted. Do you really just want whatever value is in the first row or do you want a max/min?

[JoshuaBixby]

To follow on what Blake T says, most if not all DBMSes do not guarantee the ordering of results unless you use an ORDER BY clause.  Even if you are not using GROUP BY clauses, you should use ORDER BY if you are processing results based on some type of order.  Relying on an index to return a certain order without using ORDER BY is basically relying on chance, even if chance has worked most of the time in the past.

[KONPETROV · Accepted solution]

Or i can do that. My question here is can i put 10 as a parameter that user will put? Because i tried it unsuccesfully until now.

fields = ['FID', 'a', 'b']

    with arcpy.da.UpdateCursor(fc, fields) as cursor:

        for row in cursor:

            if (row[0] == 0):

                row[2] = row[1]

                a= row[2]

            elif (row[0] > 0):

                row[2] = a - 10

                a = row[2]

         cursor.updateRow(row)

[DarrenWiens2]

most if not all DBMSes do not guarantee the ordering of results unless you use an ORDER BY clause

I've seen this statement before and I don't doubt that, technically, it may be true. However, I've never seen an example demonstrating this, never witnessed an Update Cursor returning records out of order myself, and never been provided with any measure of how often this may occur.

I have just created 1 million records in a shapefile (of course, this creates an autonumbered FID), numbered them separately with an Update Cursor counter ("order not guaranteed"), and calculated the difference to a new field. Repeated 10x. In all cases, every single row returned difference of zero.

I'm not sure: perfect out of ten million seems good to me, and I do realize that "big data" wants to be perfect every time (I don't want to be a credit card company's billionth transaction that messes up), but for static data that you can check, I'd just trust an Update Cursor.

[JoshuaBixby]

Looking at some relevant DBMS documentation:

• Microsoft SQL Server
  • ORDER BY Clause (Transact-SQL):  "The order in which rows are returned in a result set are not guaranteed unless an ORDER BY clause is specified."
• Microsoft Access
  • ORDER BY Clause (Microsoft Access SQL): "ORDER BY is optional. However, if you want your data displayed in sorted order, then you must use ORDER BY."
• PostgreSQL
  • SELECT (PostgreSQL):  "If ORDER BY is not given, the rows are returned in whatever order the system finds fastest to produce."
• IBM DB2
  • Formatting the result table (Programming for DB2):  "To retrieve rows in a specific order, use the ORDER BY clause. Using ORDER BY is the only way to guarantee that your rows are ordered as you want them."

I know Oracle has similar statements too, but the Oracle online help was obtuse enough I couldn't find a reference right away.

It is pretty clear the DBMSes themselves don't guarantee a result set order without using the ORDER BY clause.  That said, do the ArcPy cursors (original and data access) do some default ordering after getting the result set from the DBMS?  I have no idea because I have yet to find any ArcPy documentation that addresses the issue.  Given that DBMS vendors clearly state there is no guaranteed ordering without ORDER BY, and Esri doesn't say anything about whether they order the cursors after getting the result sets, I always use the ORDER BY clause if I need the data to be in a certain order.

Complexity isn't just defined by the number of records.  If all one works with is file-based data, like shapefiles or file geodatabases, ordering of result sets might be predictable, but enterprise DBMSes allow for complex workflows along with plenty of data so I prefer being explicit rather than implicit when wanting a certain order.

[KONPETROV]

yes exactly