Pivoting Tables and using Frequency in ArcGIS Desktop?

Question

I have a points layer of specimen samples. They have a field called "RESULT" which is a string field containing the results of a test, "Positive" and "Negative"(you can only see negative in the pic but trust me they're there).

I would like to determine how many positives and negatives are in each county, with each county having a COUNT_POSITIVES and COUNT_NEGATIVES column.

I've tried the Summary Statistics, Table Intersect, Pivot, and Frequency tools in ArcToolBox. The closest I've gotten is the output from the Frequency Tool:

but I'd like to "unstack" the results column so that for each county the table has a column for negatives and positives with their respective counts by county.

You can do this in about 2 seconds in Excel. Is there an easy way to do this in ArcGIS that I'm missing? This is the only response I could find but the case field in summary statistics won't work due my fields being strings (Calculate frequency of a attribute field per polygon)

Answer 1

I think the proposed way in the comments will work. But you can also use python pandas library. Once the data is in pandas you can do whatever manipulating you can think of:

import arcpy
import pandas as pd

table = r'C:\Folder\database.gdb\samples' #Change to match your data
fields = ['Species','County','Results'] #Change

df = pd.DataFrame.from_records(data=arcpy.da.SearchCursor(table,fields), columns=fields) #Read table to dataframe using da.SearchCursor
df2 = df.pivot_table(index=['Species','County'], columns='Results', aggfunc=len, fill_value=0) #Pivot and count
df2.reset_index(inplace=True) #Reset multiindex

You now have:

>>>df2
Results      Species   County  Negative  Positive
0        Prairie dog  Boulder         1         2
1        Prairie dog    Delta         0         1

Which you can export from pandas in different ways:

df2.to_clipboard()
df2.to_csv()
df2.to_excel()

It is also possible to create a file geodatabase table and use da.InsertCursor to insert the values but it requires more code.