<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
#engine
engine = create_engine("sqlite:///Resources/hawaii.sqlite")
# reflect an existing database into a new model
base = automap_base()
base.prepare(engine, reflect = True)

# reflect the tables
# We can view all of the classes that automap found (2)
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)
# get max date to count 12 months backwards
max_date=session.query(measurement.date).order_by((measurement.date).desc()).first()

max_date = dt.datetime.strptime(max_date[0], '%Y-%m-%d')

# Calculate the date 1 year ago from the last data point in the database
# Perform a query to retrieve the data and precipitation scores
# Design a query to retrieve the last 12 months of precipitation data and plot the results
prc_12 = session.query(measurement.date, measurement.prcp).\
    filter(measurement.date >= max_date-dt.timedelta(days=365)).group_by(measurement.date).all()
# Save the query results as a Pandas DataFrame and set the index to the date column
# Sort the dataframe by date
prc_df = pd.DataFrame(prc_12, columns=['date', 'prc']).sort_values(by=['date'])

# Use Pandas Plotting with Matplotlib to plot the data
axis = prc_df.plot(kind='bar', figsize=(11,8))
plt.locator_params(axis='x', nbins=12)
plt.title('Percipitation 12 months, by month')
plt.xlabel('Date')
plt.ylabel('Rain, Inches')
plt.show()
# Use Pandas to calcualte the summary statistics for the precipitation data
prc_df.describe()
# Design a query to show how many stations are available in this dataset?
session.query(station).count()
# What are the most active stations? (i.e. what stations have the most rows)?
# List the stations and the counts in descending order.
stn_list=session.query(measurement.station, func.count(measurement.station)).\
            group_by(measurement.station).\
            order_by(func.count(measurement.station).desc()).all()
# Using the station id from the previous query, calculate the lowest temperature recorded, 
# highest temperature recorded, and average temperature of the most active station?
session.query(func.min(measurement.tobs), func.max(measurement.tobs), func.avg(measurement.tobs)).\
    filter(measurement.station == stn_list[0][0]).all()
# Choose the station with the highest number of temperature observations.
# Query the last 12 months of temperature observation data for this station and plot the results as a histogram
prc_12_stn = session.query(measurement.station, measurement.tobs).\
    filter(measurement.date >= max_date-dt.timedelta(days=365)).\
        filter(measurement.station==stn_list[0][0]).all()

prc_12_stn_df = pd.DataFrame(prc_12_stn).set_index('station')
ax = prc_12_stn_df.plot.hist(bins=12, figsize=(11,9))
plt.xlabel('Temperature measurements')
plt.show()<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.10552922590837283, 'fraction_numerical': 0.013586097946287519, 'mean_word_length': 5.501026694045175, 'pattern_counts': {'":': 0, '<': 5, '<?xml version=': 0, '>': 7, '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': '1', 'dir': 'jupyter-structured-clean-dedup', 'id': '59622', 'license': 'ADSL', 'n_tokens_mistral': 996, 'n_tokens_neox': 945, 'n_words': 307, 'path': 'climate_starter.ipynb', 'repo_name': 'eerofeeva/sqlalchemy-challenge', 'size': '3165'}