<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
# create engine to hawaii.sqlite
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)
# View all of the classes that automap found
Base.classes.keys()
# Save references to each table
Station = Base.classes.station
Measurement = Base.classes.measurement
# Create our session (link) from Python to the DB
session = Session(engine)

stations = engine.execute("SELECT * FROM Station")
print(stations.keys())
stations.fetchall()
measurements = engine.execute("SELECT * FROM Measurement LIMIT 10")
print(measurements.keys())
measurements.fetchall()<jupyter_output><empty_output><jupyter_text>Exploratory Precipitation Analysis<jupyter_code># Find the most recent date in the data set.
most_recent= session.query(Measurement.date).order_by(Measurement.date.desc()).first()
most_recent
# Design a query to retrieve the last 12 months of precipitation data and plot the results. 
# Starting from the most recent data point in the database. 
# Calculate the date one year from the last date in data set.
one_year_ago = dt.date(2017, 8, 23)- dt.timedelta(days = 365)
one_year_ago

# Perform a query to retrieve the data and precipitation scores
one_year = session.query(Measurement.date, Measurement.prcp).\
    filter(Measurement.date >= one_year_ago).all()

# Save the query results as a Pandas DataFrame and set the index to the date column
prcp_df = pd.DataFrame(one_year, columns = ["Date", "precipitation"])
prcp_df.set_index("Date")
# Sort the dataframe by date
ordered_prcpdf = prcp_df.sort_values('Date').set_index("Date")
ordered_prcpdf

# Use Pandas Plotting with Matplotlib to plot the data
ordered_prcpdf.plot()
plt.ylabel("Inches")
plt.yticks(size = 10)
plt.xticks(size= 10, rotation=90)
plt.legend(loc= "upper right", bbox_to_anchor=(1.1, 1))
plt.savefig('precipitation.png', bbox_inches = 'tight')
plt.show()

# Use Pandas to calcualte the summary statistics for the precipitation data
ordered_prcpdf.describe()<jupyter_output><empty_output><jupyter_text>Exploratory Station Analysis<jupyter_code># Design a query to calculate the total number stations in the dataset
station_total = session.query(Station.station).distinct().count()
print(f"There are {station_total} stations in the dataset.")
# Design a query to find the most active stations (i.e. what stations have the most rows?)
# List the stations and the counts in descending order.
station_pop= session.query(Measurement.station, func.count(Measurement.station)).\
    group_by(Measurement.station).\
    order_by(func.count(Measurement.station).desc()).all()

station_pop
# Using the most active station id from the previous query, calculate the lowest, highest, and average temperature.
most_active = session.query(Measurement.station, func.min(Measurement.tobs), func.max(Measurement.tobs), func.avg(Measurement.tobs)).\
    filter(Measurement.station == "USC00519281").all()
# Using the most active station id
# Query the last 12 months of temperature observation data for this station and plot the results as a histogram
most_active_df = pd.DataFrame(most_active, columns = ('station', 'lowest temp', 'highest temp', 'avg temp')).set_index('station')
most_active_df
# Using the most active station id
# Query the last 12 months of temperature observation data for this station and plot the results as a histogram
most_recent_date = session.query(Measurement.date).\
    filter(Measurement.station =="USC00519281").\
    order_by(Measurement.date.desc()).first()

print(most_recent_date)
year_ago = dt.date(2017, 8, 18) - dt.timedelta(days = 365)
year_ago
tobs_data = session.query(Measurement.date, Measurement.tobs).\
    filter((Measurement.station == "USC00519281"), (Measurement.date >= year_ago)).\
    order_by(Measurement.date.desc()).all()

tobs_df = pd.DataFrame(tobs_data, columns=('Date', 'tobs')).set_index('Date')
tobs_df.plot.hist(bins= 12)
plt.xlabel('Temperature', size = 12)
plt.ylabel('Frequency',size= 12)
plt.savefig('Most_Active_Station_Temps.png', bbox_inches = 'tight')
plt.show()<jupyter_output><empty_output><jupyter_text>Close session<jupyter_code># Close Session
session.close()<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.10844976713240187, 'fraction_numerical': 0.014859170547793302, 'mean_word_length': 6.113564668769716, 'pattern_counts': {'":': 0, '<': 17, '<?xml version=': 0, '>': 19, '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': '4', 'dir': 'jupyter-structured-clean-dedup', 'id': '257338', 'license': 'ADSL', 'n_tokens_mistral': 1477, 'n_tokens_neox': 1376, 'n_words': 411, 'path': 'climate_starter.ipynb', 'repo_name': 'TDMcgowan/sqlalchemy-challenge', 'size': '4509'}