Pivot table - Get counts of values in field by key field

Question

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

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.

Answer 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!):

Output: