nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

GroupBy 2 columns and keep all fields

JamesCrandall

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

itsct_nparr <SPAN class="operator token">=</SPAN> arcpy<SPAN class="punctuation token">.</SPAN>da<SPAN class="punctuation token">.</SPAN>FeatureClassToNumPyArray<SPAN class="punctuation token">(</SPAN>prItsct<SPAN class="punctuation token">,</SPAN> <SPAN class="punctuation token">[</SPAN><SPAN class="string token">'FID_preproc'</SPAN><SPAN class="punctuation token">,</SPAN><SPAN class="string token">'NAME'</SPAN><SPAN class="punctuation token">,</SPAN><SPAN class="string token">'Shape_Area'</SPAN><SPAN class="punctuation token">]</SPAN><SPAN class="punctuation token">)</SPAN>

<SPAN class="comment token">#create a pandas DataFrame objects from the NumPy arrays</SPAN>
itsct_df <SPAN class="operator token">=</SPAN> DataFrame<SPAN class="punctuation token">(</SPAN>itsct_nparr<SPAN class="punctuation token">,</SPAN> columns<SPAN class="operator token">=</SPAN><SPAN class="punctuation token">[</SPAN><SPAN class="string token">'FID_preproc'</SPAN><SPAN class="punctuation token">,</SPAN><SPAN class="string token">'NAME'</SPAN><SPAN class="punctuation token">,</SPAN><SPAN class="string token">'Shape_Area'</SPAN><SPAN class="punctuation token">]</SPAN><SPAN class="punctuation token">)</SPAN>
maxarea <SPAN class="operator token">=</SPAN> itsct_df<SPAN class="punctuation token">.</SPAN>groupby<SPAN class="punctuation token">(</SPAN><SPAN class="punctuation token">[</SPAN><SPAN class="string token">'FID_preproc'</SPAN><SPAN class="punctuation token">,</SPAN><SPAN class="string token">'NAME'</SPAN><SPAN class="punctuation token">]</SPAN><SPAN class="punctuation token">,</SPAN> as_index<SPAN class="operator token">=</SPAN><SPAN class="token boolean">False</SPAN><SPAN class="punctuation token">)</SPAN><SPAN class="punctuation token">.</SPAN>max<SPAN class="punctuation token">(</SPAN><SPAN class="punctuation token">)</SPAN>
maxarea<SPAN class="punctuation token">.</SPAN>to_csv<SPAN class="punctuation token">(</SPAN>csvout<SPAN class="punctuation token">)</SPAN>
<SPAN class="keyword token">del</SPAN> itsct_nparr<SPAN class="line-numbers-rows"><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN></SPAN>

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

Find more posts tagged with

Accepted answers

JoshuaBixby

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:

<SPAN class="keyword token">import</SPAN> pandas

tbl <SPAN class="operator token">=</SPAN> <SPAN class="comment token"># path to table</SPAN>
tbl_out <SPAN class="operator token">=</SPAN> <SPAN class="comment token"># path to output table</SPAN>

narr <SPAN class="operator token">=</SPAN> arcpy<SPAN class="punctuation token">.</SPAN>da<SPAN class="punctuation token">.</SPAN>TableToNumPyArray<SPAN class="punctuation token">(</SPAN>tbl<SPAN class="punctuation token">,</SPAN><SPAN class="string token">"*"</SPAN><SPAN class="punctuation token">)</SPAN>
df <SPAN class="operator token">=</SPAN> pandas<SPAN class="punctuation token">.</SPAN>DataFrame<SPAN class="punctuation token">.</SPAN>from_records<SPAN class="punctuation token">(</SPAN>narr<SPAN class="punctuation token">)</SPAN>
idxs <SPAN class="operator token">=</SPAN> df<SPAN class="punctuation token">.</SPAN>groupby<SPAN class="punctuation token">(</SPAN>by<SPAN class="operator token">=</SPAN><SPAN class="string token">"FID_preproc"</SPAN><SPAN class="punctuation token">,</SPAN> as_index<SPAN class="operator token">=</SPAN><SPAN class="token boolean">True</SPAN><SPAN class="punctuation token">)</SPAN><SPAN class="punctuation token">[</SPAN><SPAN class="string token">"Shape_Area"</SPAN><SPAN class="punctuation token">]</SPAN><SPAN class="punctuation token">.</SPAN>idxmax<SPAN class="punctuation token">(</SPAN><SPAN class="punctuation token">)</SPAN>
recs <SPAN class="operator token">=</SPAN> df<SPAN class="punctuation token">.</SPAN>loc<SPAN class="punctuation token">[</SPAN>idxs<SPAN class="punctuation token">]</SPAN><SPAN class="punctuation token">.</SPAN>to_records<SPAN class="punctuation token">(</SPAN>index<SPAN class="operator token">=</SPAN><SPAN class="token boolean">False</SPAN><SPAN class="punctuation token">)</SPAN><SPAN class="punctuation token">.</SPAN>astype<SPAN class="punctuation token">(</SPAN>narr<SPAN class="punctuation token">.</SPAN>dtype<SPAN class="punctuation token">)</SPAN>
arcpy<SPAN class="punctuation token">.</SPAN>da<SPAN class="punctuation token">.</SPAN>NumPyArrayToTable<SPAN class="punctuation token">(</SPAN>recs<SPAN class="punctuation token">,</SPAN> tbl_out<SPAN class="punctuation token">)</SPAN>
<SPAN class="line-numbers-rows"><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN></SPAN>

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.

All comments

JamesCrandall

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

JoshuaBixby

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.

JamesCrandall

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

Double award points for questioning my obvious flawed assessment!

JamesCrandall

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.

JoshuaBixby

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.

JoshuaBixby

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:

<SPAN class="operator token">>></SPAN><SPAN class="operator token">></SPAN> <SPAN class="keyword token">from</SPAN> itertools <SPAN class="keyword token">import</SPAN> groupby
<SPAN class="operator token">>></SPAN><SPAN class="operator token">></SPAN> <SPAN class="keyword token">from</SPAN> operator <SPAN class="keyword token">import</SPAN> itemgetter
<SPAN class="operator token">>></SPAN><SPAN class="operator token">></SPAN> 
<SPAN class="operator token">>></SPAN><SPAN class="operator token">></SPAN> fc <SPAN class="operator token">=</SPAN> <SPAN class="comment token"># path to feature class or table</SPAN>
<SPAN class="operator token">>></SPAN><SPAN class="operator token">></SPAN> case_fields <SPAN class="operator token">=</SPAN> <SPAN class="punctuation token">[</SPAN><SPAN class="string token">"FID_preproc"</SPAN><SPAN class="punctuation token">]</SPAN>
<SPAN class="operator token">>></SPAN><SPAN class="operator token">></SPAN> max_field <SPAN class="operator token">=</SPAN> <SPAN class="string token">"Shape_Area"</SPAN>
<SPAN class="operator token">>></SPAN><SPAN class="operator token">></SPAN> sql_orderby <SPAN class="operator token">=</SPAN> <SPAN class="string token">"ORDER BY {}, {} DESC"</SPAN><SPAN class="punctuation token">.</SPAN>format<SPAN class="punctuation token">(</SPAN><SPAN class="string token">", "</SPAN><SPAN class="punctuation token">.</SPAN>join<SPAN class="punctuation token">(</SPAN>case_fields<SPAN class="punctuation token">)</SPAN><SPAN class="punctuation token">,</SPAN> max_field<SPAN class="punctuation token">)</SPAN>
<SPAN class="operator token">>></SPAN><SPAN class="operator token">></SPAN> <SPAN class="keyword token">with</SPAN> arcpy<SPAN class="punctuation token">.</SPAN>da<SPAN class="punctuation token">.</SPAN>UpdateCursor<SPAN class="punctuation token">(</SPAN>fc<SPAN class="punctuation token">,</SPAN> <SPAN class="string token">"*"</SPAN><SPAN class="punctuation token">,</SPAN> sql_clause<SPAN class="operator token">=</SPAN><SPAN class="punctuation token">(</SPAN>None<SPAN class="punctuation token">,</SPAN> sql_orderby<SPAN class="punctuation token">)</SPAN><SPAN class="punctuation token">)</SPAN> <SPAN class="keyword token">as</SPAN> cur<SPAN class="punctuation token">:</SPAN>
<SPAN class="punctuation token">.</SPAN><SPAN class="punctuation token">.</SPAN><SPAN class="punctuation token">.</SPAN>&nbsp;&nbsp;&nbsp;&nbsp; case_func <SPAN class="operator token">=</SPAN> itemgetter<SPAN class="punctuation token">(</SPAN>
<SPAN class="punctuation token">.</SPAN><SPAN class="punctuation token">.</SPAN><SPAN class="punctuation token">.</SPAN>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; <SPAN class="operator token">*</SPAN><SPAN class="punctuation token">(</SPAN>cur<SPAN class="punctuation token">.</SPAN>fields<SPAN class="punctuation token">.</SPAN>index<SPAN class="punctuation token">(</SPAN>fld<SPAN class="punctuation token">)</SPAN> <SPAN class="keyword token">for</SPAN> fld <SPAN class="keyword token">in</SPAN> case_fields<SPAN class="punctuation token">)</SPAN>
<SPAN class="punctuation token">.</SPAN><SPAN class="punctuation token">.</SPAN><SPAN class="punctuation token">.</SPAN>&nbsp;&nbsp;&nbsp;&nbsp; <SPAN class="punctuation token">)</SPAN>
<SPAN class="punctuation token">.</SPAN><SPAN class="punctuation token">.</SPAN><SPAN class="punctuation token">.</SPAN>&nbsp;&nbsp;&nbsp;&nbsp; <SPAN class="keyword token">for</SPAN> key<SPAN class="punctuation token">,</SPAN> group <SPAN class="keyword token">in</SPAN> groupby<SPAN class="punctuation token">(</SPAN>cur<SPAN class="punctuation token">,</SPAN> case_func<SPAN class="punctuation token">)</SPAN><SPAN class="punctuation token">:</SPAN>
<SPAN class="punctuation token">.</SPAN><SPAN class="punctuation token">.</SPAN><SPAN class="punctuation token">.</SPAN>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; next<SPAN class="punctuation token">(</SPAN>group<SPAN class="punctuation token">)</SPAN>
<SPAN class="punctuation token">.</SPAN><SPAN class="punctuation token">.</SPAN><SPAN class="punctuation token">.</SPAN>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; <SPAN class="keyword token">for</SPAN> extra <SPAN class="keyword token">in</SPAN> group<SPAN class="punctuation token">:</SPAN>
<SPAN class="punctuation token">.</SPAN><SPAN class="punctuation token">.</SPAN><SPAN class="punctuation token">.</SPAN>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; cur<SPAN class="punctuation token">.</SPAN>deleteRow<SPAN class="punctuation token">(</SPAN><SPAN class="punctuation token">)</SPAN>
<SPAN class="punctuation token">.</SPAN><SPAN class="punctuation token">.</SPAN><SPAN class="punctuation token">.</SPAN>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 
<SPAN class="operator token">>></SPAN><SPAN class="operator token">></SPAN> <SPAN class="keyword token">del</SPAN> cur
<SPAN class="operator token">>></SPAN><SPAN class="operator token">></SPAN> <SPAN class="line-numbers-rows"><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN></SPAN>

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

JamesCrandall

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

JamesCrandall

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.

JoshuaBixby

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?

JamesCrandall

Thank you Joshua! However, I'm getting:

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

JoshuaBixby

I think the following pandas code will work for you:

<SPAN class="keyword token">import</SPAN> pandas

tbl <SPAN class="operator token">=</SPAN> <SPAN class="comment token"># path to table</SPAN>
tbl_out <SPAN class="operator token">=</SPAN> <SPAN class="comment token"># path to output table</SPAN>

narr <SPAN class="operator token">=</SPAN> arcpy<SPAN class="punctuation token">.</SPAN>da<SPAN class="punctuation token">.</SPAN>TableToNumPyArray<SPAN class="punctuation token">(</SPAN>tbl<SPAN class="punctuation token">,</SPAN><SPAN class="string token">"*"</SPAN><SPAN class="punctuation token">)</SPAN>
df <SPAN class="operator token">=</SPAN> pandas<SPAN class="punctuation token">.</SPAN>DataFrame<SPAN class="punctuation token">.</SPAN>from_records<SPAN class="punctuation token">(</SPAN>narr<SPAN class="punctuation token">)</SPAN>
idxs <SPAN class="operator token">=</SPAN> df<SPAN class="punctuation token">.</SPAN>groupby<SPAN class="punctuation token">(</SPAN>by<SPAN class="operator token">=</SPAN><SPAN class="string token">"FID_preproc"</SPAN><SPAN class="punctuation token">,</SPAN> as_index<SPAN class="operator token">=</SPAN><SPAN class="token boolean">True</SPAN><SPAN class="punctuation token">)</SPAN><SPAN class="punctuation token">[</SPAN><SPAN class="string token">"Shape_Area"</SPAN><SPAN class="punctuation token">]</SPAN><SPAN class="punctuation token">.</SPAN>idxmax<SPAN class="punctuation token">(</SPAN><SPAN class="punctuation token">)</SPAN>
recs <SPAN class="operator token">=</SPAN> df<SPAN class="punctuation token">.</SPAN>loc<SPAN class="punctuation token">[</SPAN>idxs<SPAN class="punctuation token">]</SPAN><SPAN class="punctuation token">.</SPAN>to_records<SPAN class="punctuation token">(</SPAN>index<SPAN class="operator token">=</SPAN><SPAN class="token boolean">False</SPAN><SPAN class="punctuation token">)</SPAN><SPAN class="punctuation token">.</SPAN>astype<SPAN class="punctuation token">(</SPAN>narr<SPAN class="punctuation token">.</SPAN>dtype<SPAN class="punctuation token">)</SPAN>
arcpy<SPAN class="punctuation token">.</SPAN>da<SPAN class="punctuation token">.</SPAN>NumPyArrayToTable<SPAN class="punctuation token">(</SPAN>recs<SPAN class="punctuation token">,</SPAN> tbl_out<SPAN class="punctuation token">)</SPAN>
<SPAN class="line-numbers-rows"><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN></SPAN>

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.

JamesCrandall

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!

RebeccaStrauch__GISP

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.

Join the Conversation

Signed in members can post, follow updates, and more. New here? Register a free account.

Quick Links

Community Help
Find useful guides, FAQs, and documents to help you navigate and make the most of Esri Community.