1

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 '20 at 19:35
  • @FelixIP I have memory issues with pandas too. – Emil Brundage Mar 11 '20 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 '20 at 19:41
  • @FelixIP Hmmm I must not be using it properly. Feel free to provide an answer with Pandas if you'd like. – Emil Brundage Mar 11 '20 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 '20 at 23:42
1

Pandas does it with ease.

Script:

import arcpy
import pandas as pd
tbl=arcpy.da.TableToNumPyArray("mesh_2d",["ZONE_ID","D_CLASS"])
df=pd.DataFrame(tbl)
del tbl
df["V"]=[1]*len(df)
arcpy.AddMessage(df.head())
pTable=df.pivot_table(values='V', index=['ZONE_ID'], columns='D_CLASS', aggfunc='count')
arcpy.AddMessage('\n')
arcpy.AddMessage(pTable)
arcpy.AddMessage('\n')
pTable.to_csv ('c:/scratch/pivot.csv')

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

Output:

enter image description here

  • I get a MemoryError while attempting arcpy.da.TableToNumPyArray. The field is a text field, which 300k records. – Emil Brundage Mar 11 '20 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 '20 at 21:08
  • Alternatively run frequency on 2 fields, and use pandas on output. – FelixIP Mar 11 '20 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.