<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:///hawaii.sqlite")
# 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>inspector = inspect(engine)
columns = inspector.get_columns('measurement')
for column in columns:
    print(column["name"], column["type"])
# Find the most recent date in the data set.
engine.execute('SELECT max(date) FROM measurement LIMIT 10').fetchall()
engine.execute('SELECT * FROM measurement LIMIT 10').fetchall()
# 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.

# Perform a query to retrieve the data and precipitation scores

precip_results = session.query(Measurement.date, Measurement.prcp).filter(Measurement.date >= "2016-08-24").\
    filter(Measurement.date <= "2017-08-23").all()

# Save the query results as a Pandas DataFrame and set the index to the date column
precip_results_df = pd.DataFrame(precip_results).set_index('date')

# Sort the dataframe by date
precip_results_df= precip_results_df.sort_values(by= 'date') 

# Use Pandas Plotting with Matplotlib to plot the data

precip_results_df.plot(rot=90)
plt.show()


# Use Pandas to calcualte the summary statistics for the precipitation data
precip_results_df.describe()<jupyter_output><empty_output><jupyter_text>Exploratory Station Analysis<jupyter_code>inspector = inspect(engine)
columns = inspector.get_columns('station')
for column in columns:
    print(column["name"], column["type"])
# Design a query to calculate the total number stations in the dataset

station_count = session.query(Station.station).count()

print({station_count})
# 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.

station_data = session.query(Station.station, Station.name).order_by(Station.name.desc()).all()

station_data_df = pd.DataFrame(station_data)

station_data_df.head()

station_mode = station_data_df.describe()

station_mode
# Using the most active station id from the previous query, calculate the lowest, highest, and average temperature.

station = 'USC00511918'

active_station = session.query(func.min(Measurement.tobs), func.max(Measurement.tobs), func.avg(Measurement.tobs))\
    .filter(Measurement.station == station).all()

active_station
# 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

station = 'USC00511918'

station_precip_results = session.query(Measurement.station, Measurement.date, Measurement.tobs).\
filter(Measurement.station == station).\
filter(Measurement.date >= "2014-10-30").\
filter(Measurement.date <= "2015-10-30").all()

station_precip_results_df = pd.DataFrame(station_precip_results)
station_precip_results_df

station_precip_results_df = pd.DataFrame(station_precip_results)

station_precip_results_df.plot.hist(bins=15)<jupyter_output><empty_output><jupyter_text>Close session<jupyter_code># Close Session
session.close()<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.09331946971666233, 'fraction_numerical': 0.015596568754873927, 'mean_word_length': 6.165735567970205, 'pattern_counts': {'":': 0, '<': 19, '<?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': '343002', 'license': 'ADSL', 'n_tokens_mistral': 1209, 'n_tokens_neox': 1132, 'n_words': 345, 'path': 'climate_starter.ipynb', 'repo_name': 'nathanPM104/sqlalchemy-challenge', 'size': '3847'}