Recall that the violation data is organized such that an inspection that resulted in 5 violations produces 5 records in our Violations layer. Each inspection is assigned a unique identifier (found in the encounter field). So one way we can extend our visualization further is to summarize on the encounter field, deriving a count of the violations associated with each inspection. We can then symbolize the inspections based on the violation count.
- Return to your ArcGIS Pro project and open the violations_fc attribute table.
- Right-click on the encounter field's header and select Summarize.
The Summarize tool will create a stand-alone table containing the unique values in the encounter field along with a count of the features having each unique encounter value. Each record in the table will represent a single inspection. But if we want to be able to map the inspections, we'll want to also include the x and y fields in the grouping (as Case Fields). We'll also want to include facility_name, id (the facility id), municipal, inspect_dt, bus_cl_date, and url to support all the same sort of functionality we built around the violation data.
- Specify that the output table should go in your project geodatabase, giving it a name of inspections_tbl.
The output is going to give us a count of the rows associated with each grouping automatically, which is all we're really looking for here, but we need to specify at least one statistic field regardless.
- Under Statistics Fields, select id as the Field and Count as the Statistic Type.
- Under Case Fields, select each of the fields noted above -- encounter, facility_name, id, municipal, inspect_dt, url, x, and y.
- Click OK to generate the summary table.
- Open the new table (it will be added to your project) and note that the 405k+ violation records were summarized down to 90k+ inspections.
As noted above, the output contains redundant counts (the auto-generated FREQUENCY field and the COUNT_id field, which was based on the statistic settings we made).
- Delete the FREQUENCY field.