<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", echo=False)
# 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
Measurement = Base.classes.measurement
Station = Base.classes.station
# Create our session (link) from Python to the DB
session = Session(engine)<jupyter_output><empty_output><jupyter_text>Exploratory Precipitation Analysis<jupyter_code># Find the most recent date in the data set.
recent = session.query(Measurement.date).order_by((Measurement.date).desc()).first()[0]
print(f'the most recent date in the data is {startDate}')
# 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. 
startDate = dt.datetime.strptime(recent,"%Y-%m-%d").date()

# Calculate the date one year from the last date in data set.
endDate = startDate - dt.timedelta(days=365)
print(f'Most recent date: {startDate}\nOne year earlier: {endDate}')
# Perform a query to retrieve the data and precipitation scores
results = session.query(Measurement.date, Measurement.prcp).\
    filter(Measurement.date > endDate).\
    order_by((Measurement.date).desc()).all()

# Save the query results as a Pandas DataFrame and set the index to the date column
df = pd.DataFrame(results, columns=['date', 'prcp'])

# Use Pandas Plotting with Matplotlib to plot the data
dates = df['date']
x = [dt.datetime.strptime(d,"%Y-%m-%d").date() for d in dates]
y = df['prcp']
fig, ax = plt.subplots()
ax.bar(x, y, 6)
ax.set_xlabel("Date")
ax.set_ylabel("Precipitation")
ax.set_title("Year of Precipitation")
ax.legend("precipitation")
plt.xticks(rotation=90)
# Use Pandas to calcualte the summary statistics for the precipitation data
df.describe()<jupyter_output><empty_output><jupyter_text>Exploratory Station Analysis<jupyter_code>session.query(Station).first().__dict__
session.query(Measurement).first().__dict__
# Design a query to calculate the total number stations in the dataset
stationCount = session.query(func.count(Station.id)).all()
print(f'Total number of stations: {stationCount[0][0]}')
# 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.
session.query(Measurement.station, func.count(Measurement.station)).\
    group_by(Measurement.station).\
    order_by((func.count(Measurement.station)).desc()).all()
# Using the most active station id from the previous query, calculate the lowest, highest, and average temperature.
station_mostActive = session.query(Measurement.station, func.count(Measurement.station)).\
    group_by(Measurement.station).\
    order_by((func.count(Measurement.station)).desc()).first()[0]

station_df = pd.DataFrame(session.query(Measurement.tobs).\
    filter(Measurement.station == station_mostActive).all(), columns=['tobs'])
print(f'Min: {station_df.min()[0]}')
print(f'Max: {station_df.max()[0]}')
print(f'Avg: {station_df.mean()[0]}')
# 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
tobs_df = pd.DataFrame(session.query(Measurement.date, Measurement.tobs).\
    filter(Measurement.date > endDate).\
    filter(Measurement.station == station_mostActive).all(), columns=['date','temperature'])

tobs_df.plot.hist(bins=12)
plt.xlabel('Temperature')<jupyter_output><empty_output><jupyter_text>Close session<jupyter_code># Close Session
session.close()<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.12099823544239979, 'fraction_numerical': 0.004789513486261659, 'mean_word_length': 6.047957371225578, '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': '454802', 'license': 'ADSL', 'n_tokens_mistral': 1247, 'n_tokens_neox': 1200, 'n_words': 353, 'path': '.ipynb_checkpoints/climate-checkpoint.ipynb', 'repo_name': 'hydnhntn/sqlalchemy-challenge', 'size': '3967'}