<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
# Path to sqlite
hawaii_database_path = "sqlit:///../Resources/hawaii.sqlite"
f"sqlite:///{hawaii_database_path}"
# create engine to hawaii.sqlite
engine = create_engine(f"sqlite:///{hawaii_database_path}")
conn = engine.connect()
# reflect the tables
engine.table_names()
hawaii_measurement = pd.read_sql("SELECT * FROM measurement", conn)
hawaii_measurement.head()
hawaii_station = pd.read_sql("SELECT * FROM station", conn)
hawaii_station.head()
# reflect an existing database into a new model

# View all of the classes that automap found
Base = automap_base()
Base

Base.prepare(engine,reflect=True)
# Save references to each table
Measurement = Base.classes.measurement
Station = Base.classes.station
# Create our session (link) from Python to the DB

from sqlalchemy.orm import Session
session = Session(bind=engine)<jupyter_output><empty_output><jupyter_text>Exploratory Precipitation Analysis<jupyter_code># Find the most recent date in the data set.
curr_date = session.query(func.max(Measurement.date)).first()
# SELECT max(date) FROM Measurement
curr_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.
last_year = dt.date(2017,8,23) - dt.timedelta(days=365)
# last_year

# Perform a query to retrieve the date and precipitation scores
# SELECT date, prcp FROM Measurement WHERE date >= last_year
precp_score = session.query(Measurement.date, Measurement.prcp).\
                filter(Measurement.date >= last_year).all()
#precp_score

# Save the query results as a Pandas DataFrame and set the index to the date column
date_df = pd.DataFrame(precp_score)
#date_df

# Sort the dataframe by date
sort_df = date_df.sort_values(["date"])
# sort_df

# Use Pandas Plotting with Matplotlib to plot the data
sort_df.plot(x = "date", y = "prcp", rot=90)
plt.ylabel("Inches")
plt.xlabel("Date")
plt.title("Precipitation Scores")
# Use Pandas to calculate the summary statistics for the precipitation data
sort_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
# SELECT count(stations) FROM Station
total_stations = session.query(func.count(Station.station)).all()
# total_stations
print(f"The total number of stations is {total_stations}.")
#from sqlalchemy import desc
# 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.
# SELECT COUNT(station) FROM Station GROUP BY station ORDER BY COUNT(station) DESC
active_list = session.query(Measurement.station, func.count(Measurement.station)).\
    group_by(Measurement.station).order_by(func.count(Measurement.station).desc()).all()
active_list
# for station in active_list:
# print(f'Station: {station[0]} / Rows: {station[1]}')
print(f'The most active station is {active_list[0][0]} with {active_list[0][1]} rows.')
# Using the most active station id from the previous query, calculate the lowest, \
# highest, and average temperature.
min_temp = session.query(func.min(Measurement.tobs)).filter(Measurement.station == 'USC00519281').all()
max_temp = session.query(func.max(Measurement.tobs)).filter(Measurement.station == 'USC00519281').all()
avg_temp = session.query(func.avg(Measurement.tobs)).filter(Measurement.station == 'USC00519281').all()
print(f"The lowest temp of the most active station (USC00519281) is{min_temp}.")
print(f"The highest temp of the most active station (USC00519281)is{max_temp}.")
print(f"The average temp of the most active station (USC00519281) is{avg_temp}.")
# Calculate the date one year from the last date in data set.
#prev_year = dt.date(2017,8,23) - dt.timedelta(days=365)
#prev_year
# 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

results = session.query(Measurement.tobs).\
    filter(Measurement.station == 'USC00519281').\
    filter(Measurement.date >= last_year).all()

# Creat the dataframe
df = pd.DataFrame(results, columns=['tobs'])


# Use Pandas Plotting with Matplotlib to plot the data
df.plot.hist(bins=12)
plt.tight_layout()
plt.xlabel("Temperature")
plt.title("Most Active Temp - 8/23/16 - 8/23/17")<jupyter_output><empty_output><jupyter_text>Close session<jupyter_code># Close Session
session.close()<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.10536198622991863, 'fraction_numerical': 0.020863759649488837, 'mean_word_length': 5.838801711840229, 'pattern_counts': {'":': 0, '<': 17, '<?xml version=': 0, '>': 20, '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': '342943', 'license': 'ADSL', 'n_tokens_mistral': 1595, 'n_tokens_neox': 1477, 'n_words': 461, 'path': 'climate_starter.ipynb', 'repo_name': 'REvans82/sqlalchemy-challenge', 'size': '4793'}