0

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).

enter image description here

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:

enter image description here

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)

  • 1
    Select negative, do summary. Repeat with positive and join results. – FelixIP Mar 1 '19 at 22:55
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:

enter image description here

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.

| improve this answer | |

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.