/SQLAlchemy

Climate analysis

Primary LanguageJupyter Notebook

SQLAlchemy

Table of Contents

Background

Prior to a trip to Honolulu, Hawaii, a climate analysis about the area was performed. The two major components consisted of a Climate Analysis and Exploration and Climate App.

Part 1: Analyze and Explore the Climate Data

Through the use of Python and SQLAlchemy, a basic climate analysis and data exploration of the climate database was completed. Specifically, SQLAlchemy ORM queries, Pandas and Matplotlib was utilized.

The following steps were completed prior to analysis:

  1. Utilized the SQLAlchemy create_engine() function to connect to the SQLite database.
  2. Utilized the SQLAlchemy automap_base() function to reflect tables into classes, station and measurement.
  3. Linked Python to the database by creating a SQLAlchemy session.
  4. Performed preciptiation analysis and station analysis in the following two subsections.

Preciptation Analysis

  1. Found the most recent date in the dataset.
  2. Utilizing the most recent date, retrieved the previous 12 months of precipitation data by querying the previous 12 months of data.
  3. Selected only the "date" and "prcp" (precipitation) values.
  4. Loaded the query results into a Pandas DataFrame and set index to "date" column.
  5. Sorted DataFrame values by "date".
  6. Plotted the precipitation results over the last year by using the DataFrame plot method.
  7. Utilized Pandas to print summary statistics for the precipitation data.

Station Analysis

  1. Designed a query to calculate the total number of stations in the dataset.
  2. Designed a query to find the most-active weather stations and list the stations and observation counts in descending order.
  3. Answered the following question: Which station id has the greatest number of observations?
  4. Designed a query that calculates the lowest, highest and average temperatures that filters on the most-active station ID found in the previous query.
  5. Designed a query to retrieve the previous 12 months of temperature observation (TOBS) data. Then plotted these results as a histogram with bins=12.
  6. Closed session.

Part 2: Design the Climate App

Designed a Flask API based on the queries above using several SQLAlchemy precipitation and temperature queries in an API using a Python Flask App. Dates can only be chosen between 01-01-2010 and 08-23-2017.

/

  • Home page

/api/v1.0/precipitation

  • JSON representation of the dictionary of the yearly precipitation analysis from the most recent date

/api/v1.0/stations

  • JSON list of all the weather stations and their characteristics from the dataset

/api/v1.0/tobs

  • JSON list of temperature observations for the previous year

/api/v1.0/MM-DD-YYYY

  • JSON list of minimum, average & maximum temperatures for the range beginning with the provided start date through to 08-23-2017

/api/v1.0/MM-DD-YYYY/MM-DD-YYYY

  • JSON list of minimum, average & maximum temperatures for the range beginning with the provided start and end date range

References

Menne, M.J., I. Durre, R.S. Vose, B.E. Gleason, and T.G. Houston, 2012: An overview of the Global Historical Climatology Network-Daily Database. Journal of Atmospheric and Oceanic Technology, 29, 897-910, https://doi.org/10.1175/JTECH-D-11-00103.1

  • Dataset provided by edX UofT Data Analytics, which had been generated by Trilogy Education Services, LLC.