Use SQLAlchemy to connect to and query a SQLite database. Design a Flask Application to display our analysis on a website in real-time.
We have a business venture idea to open up a Surf 'n Shake shop that will serve ice cream and sell surfboards to locals and toursists in Hawaii. We need investor backing to start this venture. We've come up with our business proposal but our investor is concerned about the weather and we need to run analytics on a weather dataset for our business meeting. There should be just enough precipitation for a green environment, but not so much that the amount of rain hampers business by ruining ideal surf and ice cream weather.
The analysis will focus on the temperature and rainfall for the past 7 years from 2010 to 2017, specifically for June and December, the two months that are apart enough to ensure that we have a good condition year-round. In order to have enough data, we analyze data from six weather stations in Oahu, Hawaii.
- Use SQLAlchemy to connect to and query a SQLite database
- Use statistics like minimum, maximum, and average to analyze data.
- Design a Flask application to highlight results for investors
- SQLite: a version of SQL that lives on a computer or phone (local) so it's smaller, faster, and doesn't have users. SQLite is the most widely used database engine in the world. It is used in smart phones, computers, and applciations like ITunes and Photoshop.
- SQLAlchemy: a query tool designed for SQLite and the integration of statistical analysis with dataframe analysis.
- Flask: a webframework that uses Python to build web pages allowing us to share our findings in real time. Building a quick Flask website to display the data is better suited for investors because they are less interested in the actual code as opposed to understanding the general weather trends we gain from the code.
- Reflect Tables into SQLAlchemy ORM
- To perform our analysis, we wrote two queries that filtered our data on the measurement tables in our database to retrieve temperatures for the months of June and December over a ten year period.
- After getting these filters, we could then convert the temperatures to a list and create a dataframe out of the list.
- Determine the Summary Statistics for June and December
- Once we have a dataframe object, we can then run summary statistics on the dataframe using the pandas function: df.describe.
After running a statistical analysis query on the database and filtering for temperatures in June and December over the last ten years, our descriptive statistic results can be found below.
- In June there are 1,700 records: the highest temperature recorded was 85 degrees and never went below 64 degrees. Temperatures in June seem to stay mainly in the 70s and the average temperature in June is 74.94 degrees.
- In December there are 1,517 records: the highest temperature recorded was 83 degrees and the lowest temperature reached 56. Temperatures in June December get a little colder than June but only by about 10 degrees and still tend to to stay mainly in the very low 70s with the average temperature at 71.
We can easily chart a box plot to graphically see the differences between the two months and identify if there are any outliers. We plot this using python's matplotlib library. Examining the box plot below, we see that December clearly has some colder days and more outliers than June does. So between the two months, June would be more ideal for our business operations. This makes sense given that December's standard deviation is 3.75 while June's standard deviation is only 3.25. But the positive news is that the two months average temperatures (between the 25th and 75th quartiles) all fall between 86 and 65 degrees Farenheit. The ideal surfing/ice cream weather!
After running our weather analysis, we conclude that Oahu, Hawaii would be the perfect location for a Surf n Ice Cream Shake Shack. Overall, the temperature on Oahu remains consistent at a comfortable range of 65°F to 85°F year-round. Based on temperature, Oahu would be an ideal location to open up a surf and shake shack. Below we can see that even with some rain, it would not be enough to hamper our ideal surf and ice cream weather.
We can use the same procedure to compare the amount of rain (in inches) over the years as we did for temperature. Below are two additional queries that filter the precipitation levels for the months of June and December (focused on finding the summary statistics for each). Looking at the data below, we see that while there is definitely rain in Hawaii year round, the rain is not so abundant that it would hamper our ideal surf/ice cream weather. June tends to have less rain than December, but they are both still fairly agreeable months.
- Overall, June in Hawaii sees a max rainful of 4.43 inches with an average daily amount of .136 inches while 25% of the time, there is no rain at all.
- Overall, December in Hawaii sees a max rainful of 6.42 inches with an average daily amount of .217 inches while 25% of the time, there is no rain at all.
