Food Establishment
Inspection Scores in Austin, Texas

To import the cleaned data into SQL, I used the Import Data Wizard feature in Azure Data Studio. I created a new database and table to store the data and then mapped the columns from the Google Sheets file to the appropriate columns in the SQL table. Once the data was imported, I began exploring the data using SQL queries. I ran several queries to gain insights into the data, including:

The average inspection score of all food establishments in Austin
Scores of all the McDonald's in the city
The best inspection averages by zip code
The number of inspections conducted in each year

These queries helped me better understand the data and identify areas of focus for my subsequent analysis.

To collect the data for this analysis, we obtained the food establishment inspection scores dataset from the City of Austin's Open Data Portal. The dataset includes information about the inspection scores, location, and type of food establishments in Austin, Texas, from 2020 to 2023. After downloading the dataset, we imported it into Google Sheets for data cleaning. To ensure the accuracy and completeness of the data, we placed the facility ID as the primary key in the leftmost column, deleted unnecessary columns, trimmed all whitespace, and checked for duplicates. Additionally, we formatted the longitude and latitude values into separate columns using an extension called Geocode by SmartMonkey. These cleaning steps resulted in a cleaned dataset that contained 25,557 records of food establishments. With this cleaned dataset shown below, we can move on to the next step of our analysis, which is exploring the data using SQL queries.

Exploring the Data with SQL

Data Collection and Cleaning

Food safety is a critical concern for consumers, and ensuring that food establishments adhere to health and safety standards is a top priority for regulatory agencies. In this report, we will analyze the food establishment inspection scores dataset from the City of Austin's Open Data Portal to gain insights into the inspection scores, location, and types of food establishments in Austin, Texas.

To perform this analysis, we obtained the dataset and cleaned the data in Google Sheets. We then imported the cleaned data into Azure Data Studios and explored the data using SQL queries. Next, we prepared the data for visualization in Tableau by creating several SQL views that filtered and aggregated the data to the necessary level of detail.

Finally, we created various data visualizations in Tableau to analyze and present the data in a clear and concise manner. These visualizations provided insights into the inspection scores and locations of food establishments in Austin, allowing us to identify areas of focus for improving food safety in the city.

Taking these Queries into Tableau

To prepare the data for Tableau in SQL, I created several views that filtered and aggregated the data to the necessary level of detail. Specifically, I created a view that shows the zip codes and inspection averages per year, a view that includes the restaurant name along with their longitude and latitude, a view that shows the city's average score per year, and a view that includes the restaurant score and their inspection by date.

In addition to SQL views, I also prepared the data in Google Sheets to supplement the SQL views. I created a separate sheet that contained the longitude and latitude for each establishment, which I used to create a geographic map visualization in Tableau. Overall, this preparation process allowed me to ensure that the data was in the appropriate format for Tableau and that I had all the necessary information to create the desired visualizations.

Creating the Visuals with Tableau

To create data visualizations in Tableau, we first connected to the SQL database and loaded in the necessary views, including a view that showed the restaurant name and their inspection score average by location. Using Tableau's drag-and-drop interface, we created several visualizations, including a map that showed the location of all establishments with color-coded markers indicating their inspection score averages, which could be filtered by establishment type or zip code. We also created a map that showed the average inspection score by zip code, color-coded from average scores, as well as a couple of text tables that showed the city inspection average and best and worst zip code inspection averages. Lastly, we created a highlight table based on the restaurant's inspection score per year.

In addition to creating visualizations, we used Tableau to create interactive dashboards that allowed users to filter the data based on various criteria, such as establishment type or zip code. These dashboards were useful for presenting the data in a clear and intuitive way and allowed viewers to explore the data on their own.

Overall, Tableau provided us with a powerful tool for creating data visualizations and dashboards that helped us to better understand and communicate the insights from our analysis.

Analysis

Next, we analyzed the results of our data analysis to draw insights and conclusions about the food establishment inspection scores in Austin, Texas. Our analysis showed that the average inspection score for all food establishments in Austin was 91.01 out of 100, indicating that the majority of establishments were meeting or exceeding the minimum inspection standards. However, there were some areas of concern, such as zip codes with consistently lower inspection score averages, indicating a need for increased attention from health inspectors.

Our analysis also revealed that some establishments had multiple inspections over the course of the dataset, with some showing improvement or decline in their scores over time. This highlights the importance of regular inspections and the need for establishments to maintain consistently high inspection scores.