I have an Excel table of observational data.
Loc Latitude Longitude Date Survey? Observed? YDL 42.20833333 -83.62344444 8/15/16 NO YES YDL 42.20833333 -83.62344444 8/16/16 NO YES YDL 42.20833333 -83.62344444 8/17/16 YES YES YDL 42.20833333 -83.62344444 8/18/16 YES NO Courthouse 42.24027778 -83.61861111 8/15/16 YES NO Courthouse 42.24027778 -83.61861111 8/16/16 YES YES Courthouse 42.24027778 -83.61861111 8/17/16 YES NO Courthouse 42.24027778 -83.61861111 8/18/16 YES YES EMU 42.24027778 -83.61861111 8/15/16 NO NO EMU 42.24027778 -83.61861111 8/16/16 NO NO EMU 42.24027778 -83.61861111 8/17/16 NO NO EMU 42.24027778 -83.61861111 8/18/16 YES YES
I have converted this to a feature class and added it to a map:
I want these points to be proportional symbols, based on the count of 'Yes' values in the 'Observed' column. For example, YDL has 3 YES values, so it should be larger than EMU (2 YES) and EMU (1 YES). I managed to do this by basing the symbology off the Observed column, and changing the symbols based on the 'Count' column in the symbology tab of each category:
Here's the catch. I want to somehow weight those counts against the number of values in the SURVEY column, because we surveyed some locations more frequently than others. For instance, we only surveyed once at EMU, but we surveyed 4 times at Courthouse, which gives a higher probability of observing something at the Courthouse.
Can I do this in ArcMap? It would be nice to not have to restructure my data, but maybe it would be easier if I just had one row for each point, and a separate column to create a weighted count as described above?