topic Re: GroupBy 2 columns and keep all fields in Python Questions

GroupBy 2 columns and keep all fields

JamesCrandall — Sun, 12 Dec 2021 00:26:03 GMT

Edit: here's the current code I've got so far:

itsct_nparr = arcpy.da.FeatureClassToNumPyArray(prItsct, ['FID_preproc','NAME','Shape_Area'])

#create a pandas DataFrame objects from the NumPy arrays
itsct_df = DataFrame(itsct_nparr, columns=['FID_preproc','NAME','Shape_Area'])
maxarea = itsct_df.groupby(['FID_preproc','NAME'], as_index=False).max()
maxarea.to_csv(csvout)
del itsct_nparr‍‍‍‍‍‍‍

I need to come up with a solution that allows me to summarize an input table, performing a GroupBy on 2 columns ("FID_preproc" and "Shape_Area") and keep all of the fields in the original table in the output/result. From there I'll be joining that result with another table and calculating a field using the NAME column.

From the example, I need to maintain the FID_preproc, and NAME fields (see the desired result). GroupBy on FID_preproc and MAX(Shape_Area).

Any method is acceptable (numpy, pandas, summarize table, da.SearchCursor, etc.) -- whatever you have I can use!

Input:
FID_preproc	NAME	Shape_Area
1340	A	25952.35775
1341	A	118099.5219
1341	B	305220.1244
1342	A	12053.13585

Desired Result:
FID_preproc	NAME	Shape_Area
1340	A	25952.35775
1341	B	305220.1244
1342	A	12053.13585

Re: GroupBy 2 columns and keep all fields

RebeccaStrauch__GISP — Thu, 03 Aug 2017 21:17:04 GMT

You may want to look at some of the suggestions by Joshua Bixby‌ in my post How to convert a python "collection" to a gdb table? (1-to-many)

He pointed me to the rollup tool using pandas. You may need to run it twice and then join the outputs, but that was a clean solution (if you have 10.4+ or pandas).

oh...and I would use either summary or frequency to sum the areas.

Re: GroupBy 2 columns and keep all fields

JamesCrandall — Fri, 04 Aug 2017 12:51:49 GMT

I appreciate it Rebecca. I'm still not sure that will work for this implementation (or I'm just missing the obvious). At this point I'd be happy if someone could show me how to accomplish this manually with default gp tools in ArcGIS Desktop!

Re: GroupBy 2 columns and keep all fields

JoshuaBixby — Sun, 12 Dec 2021 00:26:06 GMT

In terms of semantics, I think most people working with data think of "group by" from a SQL perspective, even if they aren't working with SQL directly. From a SQL perspective, this case isn't grouping by 2 columns but grouping by 1 column and selecting based on an aggregate function of another column, e.g., SELECT FID_preproc, MAX(Shape_Area) FROM table GROUP BY FID_preproc. I mention this because pandas also views this as grouping by 1 column like SQL.

I think the following pandas code will work for you:

import pandas

tbl = # path to table
tbl_out = # path to output table

narr = arcpy.da.TableToNumPyArray(tbl,"*")
df = pandas.DataFrame.from_records(narr)
idxs = df.groupby(by="FID_preproc", as_index=True)["Shape_Area"].idxmax()
recs = df.loc[idxs].to_records(index=False).astype(narr.dtype)
arcpy.da.NumPyArrayToTable(recs, tbl_out)
‍‍‍‍‍‍‍‍‍‍

The code above is similar to using an SQL query to select the records and then joining that result set back to the original data set to get all the columns for the selection.

Re: GroupBy 2 columns and keep all fields

JamesCrandall — Fri, 04 Aug 2017 13:26:22 GMT

Thank you Joshua! However, I'm getting:

AttributeError: 'DataFrame' object has no attribute 'loc'

Re: GroupBy 2 columns and keep all fields

JoshuaBixby — Fri, 04 Aug 2017 13:51:15 GMT

I am about to post an itertools.groupby solution that I like a bit more, but I did test the pandas code before posting it. What version of ArcGIS are you running and what version of pandas is it?

Re: GroupBy 2 columns and keep all fields

JamesCrandall — Fri, 04 Aug 2017 13:53:15 GMT

ArcGIS 10.3

Not sure what version of Pandas it is.

Also: I updated main post with current code. I thought it was important to show how I'm getting the numpy array and pandas dataframe setup.

Re: GroupBy 2 columns and keep all fields

JoshuaBixby — Sun, 12 Dec 2021 00:26:08 GMT

Seeing most of the SciPy stack, including pandas, wasn't bundled with ArcGIS until 10.4; I can't infer what version of pandas you installed. What does the following code say the pandas version is on your machine:

>>> import pandas
>>> pandas.__version__
u'0.18.1'
>>> ‍‍‍‍

I am running ArcGIS 10.5.1 that comes bundled with pandas 0.18.1, but dataframe.loc has been around for several versions of pandas.

Re: GroupBy 2 columns and keep all fields

JamesCrandall — Fri, 04 Aug 2017 14:15:36 GMT

0.10.0

To exacerbate the issue, user's are on a mix of Citrix and local installs of ArcGIS desktop!

I've got a 10.4 install that has pandas 0.16.1 that will run the .loc and I'll see if your example works.

Re: GroupBy 2 columns and keep all fields

JamesCrandall — Fri, 04 Aug 2017 14:22:05 GMT

I was able to get your suggestion to run. However, it only outputs 2 columns and not what the desired result in my OP.

Re: GroupBy 2 columns and keep all fields

JoshuaBixby — Sun, 12 Dec 2021 00:26:11 GMT

As powerful as pandas is, I find getting data into and out of it cumbersome at times and unnecessary at other times. If the data is already stored where SQL can be used, like a geodatabase, I like pairing ArcPy Data Access cursors, itertools, and SQL to keep the data in place and do my analysis. My code from Deleting records from tabular intersect table based on 'PERCENTAGE' field can be easily modified to work here:

>>> from itertools import groupby
>>> from operator import itemgetter
>>> 
>>> fc = # path to feature class or table
>>> case_fields = ["FID_preproc"]
>>> max_field = "Shape_Area"
>>> sql_orderby = "ORDER BY {}, {} DESC".format(", ".join(case_fields), max_field)
>>> with arcpy.da.UpdateCursor(fc, "*", sql_clause=(None, sql_orderby)) as cur:
...     case_func = itemgetter(
...         *(cur.fields.index(fld) for fld in case_fields)
...     )
...     for key, group in groupby(cur, case_func):
...         next(group)
...         for extra in group:
...             cur.deleteRow()
...             
>>> del cur
>>> ‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

NOTE: the above code modifies the table in-place so make sure to test it on a copy.

Re: GroupBy 2 columns and keep all fields

JoshuaBixby — Fri, 04 Aug 2017 14:31:32 GMT

Pandas 0.10.0 is over 4 years old, a lifetime for the SciPy stack. Pandas 0.15.1 was released a month before ArcGIS 10.3, which raises the question of why your Citrix environments are running such an old version of pandas.

Re: GroupBy 2 columns and keep all fields

JoshuaBixby — Fri, 04 Aug 2017 14:33:48 GMT

Can you post the code back that you are using? When I tested the code on my end, I was able to output all columns in the original table, including the original ObjectID. I am not sure why you would only get 2 columns.

Re: GroupBy 2 columns and keep all fields

JamesCrandall — Fri, 04 Aug 2017 14:37:31 GMT

I will be processing this feature class in the in_memory workspace -- not sure if that sql will work there. Your pandas solution above will work great.

This solution looks good too! But not sure if that ORDER BY clause will work outside of RDMBS sources.

Re: GroupBy 2 columns and keep all fields

JamesCrandall — Fri, 04 Aug 2017 14:47:20 GMT

Ah -- that was my goof. Yes, you are correct it generates the desired output.

Double award points for questioning my obvious flawed assessment!

Re: GroupBy 2 columns and keep all fields

JoshuaBixby — Fri, 04 Aug 2017 14:48:36 GMT

I did a quick check using an in-memory table, and the code works the same as having the table in a file geodatabase. As I mentioned before, I am running 10.5.1, which shouldn't make a difference in terms of SQL support with in-memory workspaces, but one never knows. I know in-memory workspaces don't support as much SQL as file geodatabases, but ORDER BY appears to work fine.

Re: GroupBy 2 columns and keep all fields

JamesCrandall — Fri, 04 Aug 2017 15:09:24 GMT

Excellent! I'll give it a go as well.

Re: GroupBy 2 columns and keep all fields

JoshuaBixby — Sun, 12 Dec 2021 00:26:14 GMT

If you want to create a table view of the table and select records, the following works without modifying the original table:

from itertools import groupby
from operator import itemgetter

tbl = # path to table
case_fields = ["FID_preproc"]
max_field = "Shape_Area"
sql_orderby = "ORDER BY {}, {} DESC".format(", ".join(case_fields), max_field)

vw = arcpy.MakeTableView_management(tbl, "tbl_vw").getOutput(0)
oidField = arcpy.Describe(vw).OIDFieldName
oids = []

with arcpy.da.SearchCursor(vw, "*", sql_clause=(None, sql_orderby)) as cur:
    oidField = cur.fields.index(oidField)
    case_func = itemgetter(
        *(cur.fields.index(fld) for fld in case_fields)
    )
    for key, group in groupby(cur, case_func):
        oids.append(next(group)[oidField])
        continue

vw.setSelectionSet("NEW_SELECTION", oids)‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

Re: GroupBy 2 columns and keep all fields

JamesCrandall — Fri, 04 Aug 2017 17:47:39 GMT

I'll be using the grouped table to join back in with the feature class and calculating a field.