<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 func
from sqlalchemy import create_engine
from sqlalchemy import inspect
database_path = "../Resources/hawaii.sqlite"
engine = create_engine("sqlite:///{database_path}")
engine = create_engine("sqlite:///Resources/hawaii.sqlite")
# create engine to hawaii.sqlite
conn = engine.connect()
type(engine)
station_data = pd.read_sql("SELECT * FROM station", conn)
measurement_data = pd.read_sql("SELECT * FROM measurement", conn)
station_data.head()
measurement_data.head()
# 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(bind= engine)<jupyter_output><empty_output><jupyter_text>Exploratory Precipitation Analysis<jupyter_code># Create the inspector and connect it to the engine
inspector = inspect(engine)
print(inspector.get_table_names())
# Using the inspector to print the column names within the 'measurement' table and its types
columns = inspector.get_columns("measurement")
for col in columns:
    print(col["name"], col["type"])
from sqlalchemy import desc
# Find the most recent date in the data set.
recent = session.query(Measurement.date).order_by(desc('date')).first()
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.
recent_dt = dt.datetime.strptime('2017-08-23', "%Y-%m-%d")
recent_dt
antes = dt.date(recent_dt.year-1, recent_dt.month, recent_dt.day)
# Perform a query to retrieve the data and precipitation scores
lastyear = session.query(Measurement.date, Measurement.prcp).filter(Measurement.date >= (antes)).all()
lastyear
# Save the query results as a Pandas DataFrame and set the index to the date column
lastyear_df = pd.DataFrame(lastyear).set_index("date")
lastyear_df
# Sort the dataframe by date
lastyear_df = lastyear_df.sort_index(ascending =True)
lastyear_df
# Use Pandas Plotting with Matplotlib to plot the data
df = lastyear_df.plot(figsize=(20, 3))
df.set_xlabel("Date")
df.set_ylabel("PRCP (inches)")
df.set_title("PRCP vs Date")
df
# Use Pandas to calcualte the summary statistics for the precipitation data
#df.describe()
lastyear_df.describe()<jupyter_output><empty_output><jupyter_text>Exploratory Station Analysis<jupyter_code># Using the inspector to print the column names within the 'Station' table and its types
columns = inspector.get_columns("station")
for col in columns:
    print(col["name"], col["type"])
# Design a query to calculate the total number stations in the dataset
stats_tot = session.query(Station.station).group_by(Station.station).count()

stats_tot
# 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.
active_stats = session.query(Measurement.station, func.count(Measurement.id)).\
    group_by(Measurement.station).\
    order_by(func.count(Measurement.id).desc()).all()

active_stats
type(active_stats)
active_stats[0]
active_stats[0][0]
most_active = active_stats[0][0]
most_active
measurement_data.head()
# Using the most active station id from the previous query, calculate the lowest, highest, and average temperature.
lowest_temp = session.query(func.min(Measurement.tobs)).filter(Measurement.station == most_active).all()[0][0]
highest_temp = session.query(func.max(Measurement.tobs)).filter(Measurement.station == most_active).all()[0][0]
avg_temp = session.query(func.avg(Measurement.tobs)).filter(Measurement.station == most_active).all()[0][0]

print(most_active)
print(lowest_temp)
print(highest_temp)
print(avg_temp)
# 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_data = session.query(Measurement.date, Measurement.tobs).\
    filter(Measurement.station == most_active).\
    filter(Measurement.date >= (antes)).all()
most_active_df = pd.DataFrame(most_active_data)
most_active_df.head()
plt.hist(most_active_df["tobs"], bins=12)
plt.xlabel("Temperature")
plt.ylabel("Count")
plt.title(f"Histogram of Temperatures of USC00519281 and TOBS count")
plt.show()<jupyter_output><empty_output><jupyter_text>Close session<jupyter_code># Close Session
session.close()<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.10507698709945901, 'fraction_numerical': 0.007698709945900957, 'mean_word_length': 6.384024577572965, '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': '650598', 'license': 'ADSL', 'n_tokens_mistral': 1519, 'n_tokens_neox': 1450, 'n_words': 438, 'path': 'climate_starter.ipynb', 'repo_name': 'efkruis/sqlalchemy-challenge', 'size': '4806'}