How to get the field position of maximun value in each row?

WilliamYu811

Hi there,

I'm doing my bachelor thesis research about land use change,
since our national land use survey is vector data,
I would like to convert it to grid data for the next steps.

First, I used the "Create Fishnet" tool to generate a 20m*20m grid,
then I used the "Tabulate Area" tool to calculate the area of each land use type in each grid,
but the resulting table still needs to decide the types of the grid by
determining the maximum area in each grid.

After searching the web,
I found this Excel function that returns the column with the largest data in each field
to determine the land use type for that cell:

=ADDRESS(1,MATCH(MAX(Column Start:Column End),Column Start:Column End,0)+1)

When the Excel determination is complete,
import the new table into ArcGIS Pro and run the "Join field" tool to complete this session.

But the problem is that my tabulated table exceeds Excel's limit,
so I can't use Excel to do this,
then I was wondering if there is a better way to do this with the "Caculate Field" tool or Python syntax?
I'm trying MS Access but the SQL syntax also makes me exhausted as a coding newbie.
I thought it would be simple, but even ChatGPT couldn't give me any syntax to run it correctly.

 

The table after "Tabulated" looks like:

Table after tabulated

The result I want:
The result I want

 

Best regards,
William.

DanPatterson

Arcpy and numpy conversion

• TableToNumPyArray—ArcGIS Pro | Documentation to get your table into a structured array

Took the liberty to fake some of your data, array `z`

z 
array([(0, 89, 79, 56, 43, 39, 39,  6, 31, 75),
       (1, 38, 54, 38, 13, 23, 76, 21, 25,  2),
       (2, 73, 66, 39, 68, 79, 87, 34, 50, 95),
       (3, 65, 18, 49, 25, 95, 47, 38, 68, 75),
       (4, 25,  3, 70, 12, 76, 18, 34, 17, 95),
       (5, 46, 44, 99, 53, 40, 56, 95, 74, 62),
       (6, 83, 73, 45, 81, 99, 57, 43, 10, 22),
       (7, 22, 32, 28, 12, 81, 83, 52, 89, 11),
       (8, 42, 60, 67, 98, 37, 65, 17, 99, 37),
       (9, 65, 15, 15, 68, 50, 61, 52, 49, 18)],
      dtype=[('SOID', '<i8'), ('A_01', '<i8'), ('A_02', '<i8'),
	     ('A_03', '<i8'), ('A_04', '<i8'), ('A_05', '<i8'),
	     ('A_06', '<i8'), ('A_07', '<i8'), ('A_08', '<i8'),
             ('A_09', '<i8')])

 Now structured arrays are good for somethings, but since you are working with integers, it is easier to work with an array of a uniform dtype (aka, data type, with no 'names')

Conversion within numpy

Introducing you to my pal stu... converts from structured to unstructured arrays (aka, arrays with names vs arrays with a uniform data type.

# -- now get the dtype of the array and convert it to an unstructured array

from numpy.lib.recfunctions import structured_to_unstructured as stu
arr = stu(z)

# -- get where the max values are
whr = np.argmax(arr[:, 1:], axis=1)
whr
array([0, 5, 8, 4, 8, 2, 4, 7, 7, 3], dtype=int64)
# -- slice out the values using `fancy` indexing (that is what it is called)
vals = arr[np.arange(0, 10), whr + 1]
vals
array([89, 76, 95, 95, 95, 99, 99, 89, 99, 68], dtype=int64)

That was fun... now add the results back to the array in preparation for bringing back to Pro

# -- more magic imports and some reading for you
from numpy.lib.recfunctions import rec_append_fields
# -- append the values to the array
rec_append_fields(z, "max_class", vals)

rec.array([(0, 89, 79, 56, 43, 39, 39,  6, 31, 75, 89),
           (1, 38, 54, 38, 13, 23, 76, 21, 25,  2, 76),
           (2, 73, 66, 39, 68, 79, 87, 34, 50, 95, 95),
           (3, 65, 18, 49, 25, 95, 47, 38, 68, 75, 95),
           (4, 25,  3, 70, 12, 76, 18, 34, 17, 95, 95),
           (5, 46, 44, 99, 53, 40, 56, 95, 74, 62, 99),
           (6, 83, 73, 45, 81, 99, 57, 43, 10, 22, 99),
           (7, 22, 32, 28, 12, 81, 83, 52, 89, 11, 89),
           (8, 42, 60, 67, 98, 37, 65, 17, 99, 37, 99),
           (9, 65, 15, 15, 68, 50, 61, 52, 49, 18, 68)],
          dtype=[('SOID', '<i8'), ('A_01', '<i8'), ('A_02', '<i8'),
                 ('A_03', '<i8'), ('A_04', '<i8'), ('A_05', '<i8'),
                 ('A_06', '<i8'), ('A_07', '<i8'), ('A_08', '<i8'),
                 ('A_09', '<i8'), ('max_class', '<i8')])

Now back to Pro

NumPyArrayToTable—ArcGIS Pro | Documentation

 

Alternatives that you can use are Arrow tables (TableToArrowTable), xarray, Pandas (via the arcgis module) or cursors.

The nice thing about learning numpy is that is the base of the scientific stack and everything plays well with it.

NumPy user guide — NumPy v1.25.dev0 Manual

Xarray documentation

PyArrow - Apache Arrow Python bindings — Apache Arrow v11.0.0

pandas - Python Data Analysis Library (pydata.org)

plus others.