Update Cursor

812
5
Jump to solution
06-21-2018 02:25 PM
deleted-user-qpvAI3Fo0MKR
Occasional Contributor III

I'd like to use a cursor to iterate through a bunch of rows. If a valid value (not blank, not null) is encountered, skip the row and move on to the next row.

If a blank or null is encountered, then insert the value found in the row directly above.

Here at the top of the table the cursor will identify valid values and will skip and updates, but immediately in row 2, it'll identify nulls and will update the row with the values above, in this case (T10144, 480 and 150 respectively).

Further down in the table the cursor will identify that the value in the row it is not blank and not null. It'll skip the update on this row, but immediately on the next row it'll update using the values above.

Tags (1)
0 Kudos
1 Solution

Accepted Solutions
JoshuaBixby
MVP Esteemed Contributor

I think the following will work for you:

import arcpy

tbl = # path to table
flds = # list of fields for populating

with arcpy.da.UpdateCursor(tbl, flds) as cur:
    fill = next(cur)
    for row in cur:
        if all(row):
            fill = row
        else:
            row = [ i if i else j for i,j in zip(row, fill)]
            cur.updateRow(row)

del fill, row, cur

The code above assumes a couple of things:

  • The first row is fully populated.  The code will work if the first row isn't fully populated, but it will fill in NULL in some cases, which is what you appear to not want.
  • If a row (or row as defined by fields passed to cursor) is partially populated, the original value in the row is kept and only the NULL or empty values are updated.  In effect, the code merges the values of the current row and the fill row.

Although my code doesn't show it, if you are processing a table via a cursor and the order of records is important, it is critical to use SQL ORDER BY to ensure record order.

View solution in original post

5 Replies
JoshuaChisholm
Regular Contributor

Hello Schwartz,

I think you need to use an update cursor. Something like this might work: (built in ArcMap 10.4.1)

import arcpy

#Input table:
table=r"C:\...\mytable.dbf"

#Get list of fields
fields=[]
for field in arcpy.ListFields(table):
    fields.append(field.name)

#Update cursor
with arcpy.da.UpdateCursor(table, fields) as cursor:
    #Set up dictionary to store previous non-null non-empty values
    lastNonNullVals={}
    for field in fields:
        lastNonNullVals[field]="" #set a starting value for each field
    for row in cursor:
        for field in fields:
            #Check if the current cell is null or empty
            if row[fields.index(field)] in [None,"", " "]:
                #Check if the previous value for the field exists
                if lastNonNullVals[field] not in [None,"", " "]:
                    #Set the cell to the previous non-null non-empty value
                    row[fields.index(field)]=lastNonNullVals[field]
            else:
                #if there is a value for the cell, set it so it can be used in the next row(s)
                lastNonNullVals[field]=row[fields.index(field)]
        cursor.updateRow(row)
‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

Let me know if it works out!

deleted-user-qpvAI3Fo0MKR
Occasional Contributor III

Worked like a charm. Many thanks!

0 Kudos
JoshuaBixby
MVP Esteemed Contributor

I think the following will work for you:

import arcpy

tbl = # path to table
flds = # list of fields for populating

with arcpy.da.UpdateCursor(tbl, flds) as cur:
    fill = next(cur)
    for row in cur:
        if all(row):
            fill = row
        else:
            row = [ i if i else j for i,j in zip(row, fill)]
            cur.updateRow(row)

del fill, row, cur

The code above assumes a couple of things:

  • The first row is fully populated.  The code will work if the first row isn't fully populated, but it will fill in NULL in some cases, which is what you appear to not want.
  • If a row (or row as defined by fields passed to cursor) is partially populated, the original value in the row is kept and only the NULL or empty values are updated.  In effect, the code merges the values of the current row and the fill row.

Although my code doesn't show it, if you are processing a table via a cursor and the order of records is important, it is critical to use SQL ORDER BY to ensure record order.

deleted-user-qpvAI3Fo0MKR
Occasional Contributor III

This also works, many thanks Joshua!

0 Kudos
JoshuaChisholm
Regular Contributor

Hey Joshua,

I like your solution to Joshua's problem. Very elegant.

Cheers,

Joshua

0 Kudos