I'm trying to create a pivot table which displays counts of unique values per a key field:

enter image description here

I've tried a combo of PivotTable and Statistics but the results are not correct. The PivotTable doesn't produce what I expected. The pivot table produces rows with empty key field values, which shouldn't exist as all input rows have a key value. I'm confused as to what inputs are what for the tool. Statistics does work as expected.

#this code doesn't produce what I was hoping - adding field with key and new fields for each summary value
pivTab = arcpy.PivotTable_management (inputTable, keyFld, summaryFld, summaryFld, pivotTab) [0]
##note the tool produces empty strings for values where they should be null
##I fix this here before stats
sumStatsFc = arcpy.Statistics_analysis (pivotTab, sumStatsTab, "valX COUNT; valY COUNT; valZ COUNT", keyFld) [0]

I don't need the python code for the answer; just which tools to use and what their inputs are.

Note that using cursors, dictionaries, etc isn't ideal as data sets are large and memory issues arise.

  • Use pandas, pivot != one in Excel.
    – FelixIP
    Mar 11, 2020 at 19:35
  • @FelixIP I have memory issues with pandas too. Mar 11, 2020 at 19:36
  • Strange. I use it on 3.5 m records, for summary statistics after ArcGis froze at sorting stage. Took under 2 mins.
    – FelixIP
    Mar 11, 2020 at 19:41
  • @FelixIP Hmmm I must not be using it properly. Feel free to provide an answer with Pandas if you'd like. Mar 11, 2020 at 19:43
  • Did you actually try a cursor + dictionary? I bet it'd be more memory efficient than loading everything into an array to use pandas
    – mikewatt
    Mar 11, 2020 at 23:42

1 Answer 1


Pandas does it with ease.


import arcpy
import pandas as pd
del tbl
pTable=df.pivot_table(values='V', index=['ZONE_ID'], columns='D_CLASS', aggfunc='count')
pTable.to_csv ('c:/scratch/pivot.csv')

Message (note time when run from mxd!): enter image description here


enter image description here

  • I get a MemoryError while attempting arcpy.da.TableToNumPyArray. The field is a text field, which 300k records. Mar 11, 2020 at 21:04
  • It was my suspicion. Very long text. I'd replace it with acronym. In my example one if the fields is text as well and not very short.
    – FelixIP
    Mar 11, 2020 at 21:08
  • Alternatively run frequency on 2 fields, and use pandas on output.
    – FelixIP
    Mar 11, 2020 at 21:25

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.