Confluence Retirement

Due to the feedback from stakeholders and our commitment to not adversely impact USGS science activities that Confluence supports, we are extending the migration deadline to January 2023.

In an effort to consolidate USGS hosted Wikis, myUSGS’ Confluence service is targeted for retirement. The official USGS Wiki and collaboration space is now SharePoint. Please migrate existing spaces and content to the SharePoint platform and remove it from Confluence at your earliest convenience. If you need any additional information or have any concerns about this change, please contact myusgs@usgs.gov. Thank you for your prompt attention to this matter.
Skip to end of metadata
Go to start of metadata

This function is a supercharged version of the mostly-ModelBuilder tool Get Field Value. It has nice little features to quickly get min, max, count, etc.

GetFieldValue.py
def GetFieldValue(tbl, field=None, where_expr=None, value_type="FIRST"):
    """Get Field Value from a table
      tbl          input table of table view
      field        input field (optional)
                   Default is the table's ObjectID field
      where_expr   select expression (optional)
      value_type   value to return
                   FIRST    first value found, no sorting (default)
                   DATA     return True (one or more rows) or False (no rows)
                   MIN      minimum value (Can be None)
                   MAX      maximum value (Can be None)
                   COUNT    row count (input field is ignored)
                   SUM      sum of non-null values
                   For all options but DATA and COUNT, if no rows are
                   found, an exception is raised.
    """
    try:
        value_type_name = value_type
        value_type = value_type.upper()
        if not field:
            field = "OID@"
        # are there any records at all?
        try:
            fvalue = arcpy.da.SearchCursor(tbl, field, where_expr).next()[0]
        except StopIteration:
            # just for value_type == DATA or COUNT
            # return boolean (True - one or more rows found)
            if value_type == "DATA":
                return False
            elif value_type == "COUNT":
                return 0
            else:
                raise Exception("No rows in table")
        if value_type == "DATA":
            return True
        elif value_type == "FIRST":
            return fvalue
        elif value_type[:3] == "MIN":
            return sorted(arcpy.da.SearchCursor(tbl, field, where_expr))[0][0]
        elif value_type[:3] == "MAX":
            return sorted(arcpy.da.SearchCursor(tbl, field, where_expr),
                          reverse=True)[0][0]
        elif value_type == "COUNT":
            # count rows
            c = 0
            with arcpy.da.SearchCursor(tbl, "OID@", where_expr) as rows:
                for row in rows: c += 1
            return c
        elif value_type == "SUM":
            # sum rows
            s = 0
            with arcpy.da.SearchCursor(tbl, field, where_expr) as rows:
                for row in rows:
                    if row[0]: s += row[0]
            return s
        else:
            raise Exception("Invalid value_type: " + repr(value_type_name))
    except Exception as xmsg:
        raise Exception(str(xmsg))
  • No labels

6 Comments

  1. That's pretty neat how on line 43 and line 45, you can actually get a value by instantiating the arcpy.da.SearchCursor() without actually getting the row (or dumping the whole table out with arcpy.da.TableToNumPyArray())!

  2. For the "COUNT" option, is iterating through quicker than using arcpy.GetCount_management()? Answer might be different if one needs to apply a "where_expr" first, which would mean making a table view with the where_expr first (which could easily double compute-time).

    1. Unknown User (cprice@usgs.gov)

      No need to create a table view, you can apply a where-expression to any table or table view when creating the cursor. I haven't tested this but I seem to remember a discussion on stack exchange where arcpy.da.cursors were pretty close, unless there was a really big number of records.

  3. For the "SUM" option, wondering if arcpy.da.TableToNumPyArray() and np.sum() is slower or comparable to iterating with the arcpy.da.SearchCursor().

    1. Unknown User (cprice@usgs.gov)

      Numpy may be faster but not as scalable as the cursor doesn't need to load the table into memory. 

      1. good point! seems like using things like iterators and enumerators (and arcpy.da.*cursors) are really handy for dealing w/data sets that stretch memory limits. Might have to get some examples up...