<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, inspect
# create engine to hawaii.sqlite
engine = create_engine("sqlite:///./Resources/hawaii.sqlite")
inspector = inspect(engine)
# 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)
print("Measurement Columns")
columns = inspector.get_columns('Measurement')
for column in columns:
    print(column["name"], column["type"])

print("----------")
print("Station Columns")
columns = inspector.get_columns('Station')
for column in columns:
    print(column["name"], column["type"])<jupyter_output>Measurement Columns
id INTEGER
station TEXT
date TEXT
prcp FLOAT
tobs FLOAT
----------
Station Columns
id INTEGER
station TEXT
name TEXT
latitude FLOAT
longitude FLOAT
elevation FLOAT<jupyter_text>Exploratory Precipitation Analysis<jupyter_code># Find the most recent date in the data set.
recent_date = session.query(func.max(Measurement.date)).first()
print(recent_date)
# 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 = dt.date(2017, 8, 23) - dt.timedelta(days = 365)
print(one_year)
# Perform a query to retrieve the data and precipitation scores
in_last_year = session.query(Measurement.date, Measurement.prcp).\
    filter(Measurement.date > one_year).all()

# Save the query results as a Pandas DataFrame and set the index to the date column
df = pd.DataFrame(in_last_year, columns = ["Date", "Precipitation"])
df.set_index("Date", inplace = True)
df
# Sort the dataframe by date
df = df.sort_index()
# Use Pandas Plotting with Matplotlib to plot the data
df.plot(rot = 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># Design a query to calculate the total number stations in the dataset
session.query(func.count(Station.id)).all()
# 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.prcp)).\
    group_by(Measurement.station).order_by(func.count(Measurement.prcp).desc()).all()

# Using the most active station id from the previous query, calculate the lowest, highest, and average temperature.
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
temp_station = session.query(Measurement.tobs).\
filter(Measurement.station == "USC00519281").filter(Measurement.date > one_year).all()

#Pandas DataFrame
df = pd.DataFrame(temp_station, columns = ["Tempature"])
df
#Histogram
df.plot.hist(bins = 12)
plt.tight_layout()<jupyter_output><empty_output><jupyter_text>Close session<jupyter_code># Close Session
session.close()<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.10029980921231943, 'fraction_numerical': 0.009266830198964295, 'mean_word_length': 5.73394495412844, 'pattern_counts': {'":': 0, '<': 16, '<?xml version=': 0, '>': 18, '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': '18638', 'license': 'MIT', 'n_tokens_mistral': 1146, 'n_tokens_neox': 1082, 'n_words': 366, 'path': 'climate_starter.ipynb', 'repo_name': 'J-Schea29/sqlalchemy-challenge', 'size': '3669'}