I have a large table with 150000 features. Every feature has one key and a keyword that I want to use to make a new field with. Below is an example:

|Field A|Field b|Shape_area|
|key 1  |Type 1 |300       |
|key 1  |Type 2 |4500      |
|key 2  |Type 1 |100       |
|key 2  |Type 1 |300       |

I want to summarize it like this:

|Key 1|300   |4500  |
|Key 2|400   |0     |

Is this possible in ArcGIS Pro?

I've tried the summarize statistics, but I only get the keys in a long table.

  1. Pivot table with FieldA as Input Field, Field B as Pivot Field and Area as Value Field: enter image description here
  2. Then use Summary Statistics with type1 and 2 as statistics fields and field a as case field: enter image description here

Or use python pandas module:

import arcpy
import pandas as pd

fc = r'C:\data.gdb\table'
fieldA = 'Field A'
fieldB = 'Field B'
area = 'Shape_area'

fieldlist = [fieldA,fieldB,area]
df = pd.DataFrame.from_records(data=arcpy.da.SearchCursor(fc,fieldlist), columns=fieldlist)
table = pd.pivot_table(data=df, values=area, index=fieldA, columns=fieldB, 
                      aggfunc='sum', fill_value=0)
  • The OP requested a solution in ArcGIS Pro -- would you be able to duplicate this procedure using that version? – Rob May 3 at 21:38
  • Yes I know. I dont post solutions that does not work. – BERA May 4 at 6:55
  • 1
    Thank you very much @BERA. The pivot table tool was what I was looking for! – birks May 6 at 6:48

Taking @BERA's solution and providing ArcGIS Pro Desktop interface examples:

enter image description here

1. Use the Pivot Table Data Management Tool

  • Field A = Input Field
  • Field B = Pivot Field
  • Shape Area = Value Field

enter image description here

enter image description here

enter image description here

2. Use the "Summary Stastistics" Analysis tool:

  • Statistics Fields = Type 1 (Statistics Type Sum) & Type 2 (Statistics Type Sum)
  • Case Field = Field A

enter image description here

Final Results:

enter image description here

Personal notes about the process: Although this solution does provide the desired results, it does create some "waste" as we need to create a intermediary table in order to utilize the Summary Statistics tool to get the final results. Essentially the Pivot Table has a single use and then after that it takes up space. If this is something that is done on a regular basis, I would recommend creating a Model which stores the Pivot Table in memory.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.