YYYY State County Drug Report Analysis: Top Reporting Counties and States

This analysis explores drug report data by county and state for each year. The goal is to identify the county with the highest number of reports in each year and determine if the corresponding state also had the highest report count.

First, we group the data by year and county and calculate the total number of drug reports for each combination:

grouped = df.groupby(['YYYY', 'COUNTY']).sum()

Next, we identify the county with the highest number of drug reports in each year:

max_reports = grouped.groupby('YYYY')['DrugReports'].idxmax()

Then, we use the index of these top reporting counties to extract their corresponding states:

max_county_state = df.loc[max_reports, ['COUNTY', 'STATE']]

Finally, we find the state with the highest total number of reports in each year:

max_state = df.groupby('YYYY')['DrugReports'].idxmax()
max_state_reports = df.loc[max_state, ['STATE']]

By comparing max_county_state and max_state_reports, we can determine if the county with the most reports in a given year is located within the state with the most reports for that same year.