I need to identify the highest value of an ID
example
ID | value | High_Value |
1 | 50 | 0 |
1 | 120 | 1 |
1 | 119 | 0 |
2 | 100 | 0 |
2 | 101 | 1 |
3 | 52 | 1 |
3 | 51 | 0 |
3 | 4 | 0 |
Solved! Go to Solution.
@JoshuaBixby certainly has a good point, otherwise you might get an answer that you don't fully understand or isn't implemented in a fashion that you need for the data that you have.
The key is in the splitting the data rows by the id key and finding the maximum value in the second column. Once found, assign 1 to that row.
This can be implemented using other approaches of course. I will leave that for others.
In any event... my way
Convert your table to a numpy array (arcpy.da.TableToNumPyArray), convert the structured dtype as an a simple integer dtype.
'a' is the array from above
'z' is the array to work with
'final' is the result
z = np.zeros((a.shape[0], 3), dtype=np.int64)
z[:, :2] = a
z[np.lexsort((z[:,1], z[:, 0]))]
w = np.nonzero((z[1:, 0] - z[:-1, 0]))[0]
spl = np.array_split(z, w + 1, 0)
out = []
for s0 in spl:
w = np.argmax(s0[:, 1])
s0[w, -1] = 1
out.append(s0)
final = np.concatenate(out, axis=0)
Inputs and results
a # --- your structured array as a uniform integer type
array([[ 1, 50],
[ 1, 119],
[ 1, 120],
[ 2, 100],
[ 2, 101],
[ 3, 52],
[ 3, 51],
[ 3, 4]])
z # --- make a zeros array and assign the first two columns
array([[ 1, 50, 0],
[ 1, 119, 0],
[ 1, 120, 0],
[ 2, 100, 0],
[ 2, 101, 0],
[ 3, 52, 0],
[ 3, 51, 0],
[ 3, 4, 0]], dtype=int64)
final # --- results
array([[ 1, 50, 0],
[ 1, 119, 0],
[ 1, 120, 1],
[ 2, 100, 0],
[ 2, 101, 1],
[ 3, 52, 1],
[ 3, 51, 0],
[ 3, 4, 0]], dtype=int64)
Then back to a geodatabase table using arcpy.da.NumPyArrayToTable.
This kind of question is asked fairly regularly on GeoNet/Community, were you not able to find any of those posts while searching? Also, have you tried anything specific yet? If so, what isn't working with it?
@JoshuaBixby certainly has a good point, otherwise you might get an answer that you don't fully understand or isn't implemented in a fashion that you need for the data that you have.
The key is in the splitting the data rows by the id key and finding the maximum value in the second column. Once found, assign 1 to that row.
This can be implemented using other approaches of course. I will leave that for others.
In any event... my way
Convert your table to a numpy array (arcpy.da.TableToNumPyArray), convert the structured dtype as an a simple integer dtype.
'a' is the array from above
'z' is the array to work with
'final' is the result
z = np.zeros((a.shape[0], 3), dtype=np.int64)
z[:, :2] = a
z[np.lexsort((z[:,1], z[:, 0]))]
w = np.nonzero((z[1:, 0] - z[:-1, 0]))[0]
spl = np.array_split(z, w + 1, 0)
out = []
for s0 in spl:
w = np.argmax(s0[:, 1])
s0[w, -1] = 1
out.append(s0)
final = np.concatenate(out, axis=0)
Inputs and results
a # --- your structured array as a uniform integer type
array([[ 1, 50],
[ 1, 119],
[ 1, 120],
[ 2, 100],
[ 2, 101],
[ 3, 52],
[ 3, 51],
[ 3, 4]])
z # --- make a zeros array and assign the first two columns
array([[ 1, 50, 0],
[ 1, 119, 0],
[ 1, 120, 0],
[ 2, 100, 0],
[ 2, 101, 0],
[ 3, 52, 0],
[ 3, 51, 0],
[ 3, 4, 0]], dtype=int64)
final # --- results
array([[ 1, 50, 0],
[ 1, 119, 0],
[ 1, 120, 1],
[ 2, 100, 0],
[ 2, 101, 1],
[ 3, 52, 1],
[ 3, 51, 0],
[ 3, 4, 0]], dtype=int64)
Then back to a geodatabase table using arcpy.da.NumPyArrayToTable.