<jupyter_start><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
engine = create_engine("sqlite:///Resources/hawaii.sqlite")
# reflect an existing database into a new model
Base = automap_base()
# reflect the tables
Base.prepare(engine, reflect=True)
# We can view all of the classes that automap found
Base.classes.keys()
# Save references to each table
Measurements = Base.classes.measurement
Stations = Base.classes.station
# Create our session (link) from Python to the DB
session = Session(engine)<jupyter_output><empty_output><jupyter_text>Exploratory Climate Analysis<jupyter_code># Design a query to retrieve the last 12 months of precipitation data and plot the results
CY_precipitation = session.query(Measurements.date).filter(Measurements.date >= "2016-08-23").order_by(Measurements.date).all()
# # Calculate the date 1 year ago from the last data point in the database
LY_precipitation = session.query(Measurements.date).filter(Measurements.date).order_by(Measurements.date.desc()).first()
last_date = dt.date(2017,8,23) - dt.timedelta(days=365)

last_date
# # # Perform a query to retrieve the data and precipitation scores
last_year = session.query(Measurements.prcp, Measurements.date).order_by(Measurements.date.desc())
# # # Save the query results as a Pandas DataFrame and set the index to the date column, Sort the dataframe by date
date = []
precipitation = []
last_year_df = last_year.filter(Measurements.date >="2016-08-23")
for precip in last_year_df:
    date.append(precip.date)
    precipitation.append(precip.prcp)
LY_df = pd.DataFrame({
    "Date": date,
    "Precipitation": precipitation
})
LY_df.set_index("Date", inplace=True)
LY_df = LY_df.sort_index(ascending=True)

# # Use Pandas Plotting with Matplotlib to plot the data
LY_graph = LY_df.plot(figsize = (20,10), rot=90, title= "Hawaii Precipitation Data 8/23/16-8/23/17")
LY_graph.set_ylabel("Precipitation (in)")
plt.savefig("Images/PrecipitationAugtoAug.png")



# Use Pandas to calcualte the summary statistics for the precipitation data
LY_df.describe()
# Design a query to show how many stations are available in this dataset?
station_count = session.query(Measurements.station, func.count(Measurements.station)).\
    group_by(Measurements.station).all()

print("There are " + str(len(station_count)) + " stations in this dataset.")
# What are the most active stations? (i.e. what stations have the most rows)?
# List the stations and the counts in descending order.
active_stations = session.query(Measurements.station, func.count(Measurements.station)).\
    group_by(Measurements.station).\
    order_by(func.count(Measurements.station).desc()).all()
active_stations
# Using the station id from the previous query, calculate the lowest temperature recorded, 
# highest temperature recorded, and average temperature of the most active station?
most_active = session.query(Measurements.station).group_by(Measurements.station).order_by(func.count(Measurements.station).desc()).first()

most_active_info = session.query(Measurements.station, func.min(Measurements.tobs), func.max(Measurements.tobs), func.avg(Measurements.tobs)).\
    filter(Measurements.station == most_active[0]).all()

print("The lowest temperature at station " + most_active_info[0][0] + " is " + str(most_active_info[0][1]) + " degrees.")
print("The highest temperature at station " + most_active_info[0][0] + " is " + str(most_active_info[0][2]) + " degrees.")
print("The average temperature at station " + most_active_info[0][0] + " is " + str(round(most_active_info[0][3], 2)) + " degrees.")
# Choose the station with the highest number of temperature observations.
# Query the last 12 months of temperature observation data for this station and plot the results as a histogram
tobs_query = session.query(Measurements.station).group_by(Measurements.station).order_by(func.count(Measurements.id).desc()).first()
tobs = session.query(Measurements.station, Measurements.tobs).filter(Measurements.station == tobs_query[0]).filter(Measurements.date > last_date).all()

station_df = pd.DataFrame(tobs, columns = ['station', 'tobs'])
station_df.hist(column='tobs', bins=12)
plt.ylabel('Frequency')
plt.show()<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.11532349602724178, 'fraction_numerical': 0.01452894438138479, 'mean_word_length': 6.095008051529791, 'pattern_counts': {'":': 2, '<': 9, '<?xml version=': 0, '>': 12, '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': '2', 'dir': 'jupyter-structured-clean-dedup', 'id': '924', 'license': 'ADSL', 'n_tokens_mistral': 1387, 'n_tokens_neox': 1322, 'n_words': 362, 'path': 'climate_starter.ipynb', 'repo_name': 'gracesco/HuefnerSQLAlchemyChallenge', 'size': '4405'}