<jupyter_start><jupyter_text>SQLAlchemy Homework - Surfs Up!  Import Modules<jupyter_code>#### %matplotlib inline
from matplotlib import style
style.use('fivethirtyeight')
import matplotlib.pyplot as plt

import numpy as np
import pandas as pd
import datetime as dt
import matplotlib.pyplot as plt<jupyter_output><empty_output><jupyter_text>Reflect Tables into SQLAlchemy ORM<jupyter_code># Python SQL toolkit and Object Relational Mapper
import sqlalchemy
from sqlalchemy.ext.automap import automap_base
from sqlalchemy.orm import Session
from sqlalchemy import create_engine, func, inspect<jupyter_output><empty_output><jupyter_text>Use SQLAlchemy create_engine to connect to your sqlite database.<jupyter_code># create engine to hawaii.sqlite
engine = create_engine("sqlite:///Resources/hawaii.sqlite")
# Reflect an existing database into a new model
inspector = inspect(engine)

# Reflect the tables
inspector.get_table_names()

# Get table headers of table 'measurement'
data = engine.execute("PRAGMA table_info(measurement);")

for record in data:
    print(record)
# Get table headers of table 'measurement'
data = engine.execute("PRAGMA table_info(station);")

for record in data:
    print(record)<jupyter_output>(0, 'id', 'INTEGER', 1, None, 1)
(1, 'station', 'TEXT', 0, None, 0)
(2, 'name', 'TEXT', 0, None, 0)
(3, 'latitude', 'FLOAT', 0, None, 0)
(4, 'longitude', 'FLOAT', 0, None, 0)
(5, 'elevation', 'FLOAT', 0, None, 0)<jupyter_text>Use SQLAlchemy `automap_base()` to reflect your tables into classes and save a reference to those classes called `Station` and `Measurement`.<jupyter_code># View all of the classes that automap found

# Reflect Database 'measurement' into ORM class
Base = automap_base()
Base.prepare(engine, reflect=True)

# Save references to each table
Measurement = Base.classes.measurement
Station = Base.classes.station<jupyter_output><empty_output><jupyter_text>Link Python to the database by creating an SQLAlchemy session.<jupyter_code># Create our session (link) from Python to the DB
session = Session(engine)<jupyter_output><empty_output><jupyter_text>Exploratory Precipitation Analysis  Find the most recent date in the data set.<jupyter_code>most_recent = session.query(Measurement.date).\
order_by(Measurement.date.desc()).first()
most_recent
#print(f; most_recent<jupyter_output><empty_output><jupyter_text>Retrieve the last 12 months of precipitation data by querying the 12 preceding months of data.<jupyter_code>last_twelve = (dt.datetime.strptime(most_recent[0], '%Y-%m-%d') - dt.timedelta(days=365)).date()
last_twelve<jupyter_output><empty_output><jupyter_text>Select only the `date` and `prcp` values* Sort the values by `date`<jupyter_code># Design a query to retrieve the last 12 months of precipitation data and plot the results
rain_data = session.query(Measurement.date, Measurement.prcp).filter(Measurement.date >= last_twelve ).order_by(Measurement.date).all()

rain_data<jupyter_output><empty_output><jupyter_text>Load the query results into a Pandas DataFrame * Set the index to the date column* Clean data, dropping all null values<jupyter_code># Create Pandas DataFrame
rain_df = pd.DataFrame(rain_data).dropna().set_index('date')
rain_df<jupyter_output><empty_output><jupyter_text>Plot the results using the DataFrame `plot` method.<jupyter_code># Plot the results using the DataFrame `plot` method.
ax = rain_df.plot(rot=90, x_compat=True)
figsize=(30,10)
plt.xlabel('Dates')
plt.ylabel('Precipitation')
plt.title('Twelve Months of Precipitation')
plt.grid(True)

plt.savefig("Output/monthly_prcp_data")

# Display histogram
plt.show()<jupyter_output><empty_output><jupyter_text>Use Pandas to calculate the summary statistics for the precipitation data<jupyter_code>rain_df.describe()<jupyter_output><empty_output><jupyter_text>Exploratory Station Analysis  Design a query to calculate the total number stations in the dataset<jupyter_code># Method 1
locations = session.query(Station)
print("Total Number Stations: {}".format(total_stations.count()))

# Method 2
#method_2 = engine.execute('SELECT station, count(station) FROM Station').fetchall() 
#method_2<jupyter_output>Total Number Stations: 9<jupyter_text>Design a query to find the most active stations (i.e. what stations have the most rows?)<jupyter_code>results = session.query(Measurement.station, func.count(Measurement.station)).\
        group_by(Measurement.station).order_by(func.count(Measurement.station).desc()).all()

most_active_station = results[0][0]

print(f' Most active Station: ')
print(f' --------------------- ')
print(f' Station Details: {most_active_station}')<jupyter_output>Most active Station: 
 --------------------- 
 Station Details: USC00519281<jupyter_text>Identify which station id has the highest number of observations<jupyter_code>station_observations = results[0][-1]

print(f' Most observations: ')
print(f' --------------------- ')
print(f' Station Details: {station_observations}')<jupyter_output>Most observations: 
 --------------------- 
 Station Details: 2772<jupyter_text>List the stations and the counts in descending order<jupyter_code>station_list = [result[0] for result in results[::]]
observation_counts = [result[1] for result in results[::]]
station_observations = pd.DataFrame(station_counts_desc,columns=['Station Name', 'Count'])
station_observations<jupyter_output><empty_output><jupyter_text>Using the most active station id, calculate the lowest, highest, and average temperature.* Hint: You will need to use a function such as `func.min`, `func.max`, `func.avg`, and `func.count` in your queries.<jupyter_code>temperature_frequencies = session.query(func.min(Measurement.tobs), func.max(Measurement.tobs), func.avg(Measurement.tobs)).\
    filter(Measurement.station == most_active_station).all()
print(f"The Lowest, Highest & the average temperature for most active station {most_active_station} is :")
print(f" Low  : {temperature_frequencies[0][0]}")
print(f" High : {temperature_frequencies[0][1]}")
print(f" Avg  : {temperature_frequencies[0][2]}")<jupyter_output>The Lowest, Highest & the average temperature for most active station USC00519281 is :
 Low  : 54.0
 High : 85.0
 Avg  : 71.66378066378067<jupyter_text>Design a query to retrieve the last 12 months of temperature observation data (TOBS).<jupyter_code># Step 1: Identify Latest Date
latest_date = session.query(Measurement.date, Measurement.tobs).order_by(Measurement.date.desc()).first()
latest_date
# Step 2: Calculate a time delta
query_date = dt.date(2017, 8, 23) - dt.timedelta(days=365)

# Use print function to display year, month, day and remove datetime.date object
print(query_date)
# Step 3: Retrieve last 12 months of temperature observation data
session.query(Measurement.date, Measurement.tobs).\
    filter(Measurement.date > '2016-08-23').\
    order_by(Measurement.date).all()<jupyter_output><empty_output><jupyter_text>Filter by the station with the highest number of observations.<jupyter_code># Retrieve previously created variable
most_active_station<jupyter_output><empty_output><jupyter_text>Query the last 12 months of temperature observation data for this station<jupyter_code>temps_most_active = session.query(Measurement.date, Measurement.tobs).filter(
    Measurement.station == most_active_station).filter(
    Measurement.date >= query_date).group_by(Measurement.date).all()

temps_most_active<jupyter_output><empty_output><jupyter_text>Plot the results as a histogram with 'bins =12'<jupyter_code># Step 1: Transfer data into DataFrame for initial set-up
temps_most_active_df = pd.DataFrame(temps_most_active).dropna().set_index('date')
temps_most_active_df

# Step 2: Plot DataFrame as a histogram (bins=12)
plt.hist(temps_most_active_df['tobs'],12, label="tobs")
plt.ylabel("Frequency", fontsize=11)
plt.legend()

# Save Image
plt.savefig("Output/Temp_for_active station_last_12months.png")

# Display histogram
plt.show()<jupyter_output><empty_output><jupyter_text>Close session<jupyter_code># Close Session
session.close()<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.1191542288557214, 'fraction_numerical': 0.016417910447761194, 'mean_word_length': 6.658095238095238, 'pattern_counts': {'":': 0, '<': 80, '<?xml version=': 0, '>': 83, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '21', 'dir': 'jupyter-structured-clean-dedup', 'id': '464252', 'license': 'ADSL', 'n_tokens_mistral': 2719, 'n_tokens_neox': 2553, 'n_words': 675, 'path': 'Resources/climate_starter.ipynb', 'repo_name': 'alisonsadel/sqlalchemy-challenge', 'size': '8040'}