<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 = create_engine("sqlite:///Resources/hawaii.sqlite")
# reflect an existing database into a new model
base = automap_base()
# reflect the tables
base.prepare(engine, reflect=True)
# We can view all of the classes that automap found
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)<jupyter_output><empty_output><jupyter_text>Exploratory Climate Analysis<jupyter_code># retrieve last date in data frame
session.query(measurement.date).order_by(measurement.date.desc()).first()
# Calculate the date 1 year ago from the last data point in the database
yearAgo = dt.date(2017, 8, 23)- dt.timedelta(days=365)

print(yearAgo)
# Perform a query to retrieve the data and precipitation scores
precept = session.query(measurement.date, measurement.prcp).\
filter(measurement.date > yearAgo).\
order_by(measurement.date).all()

print(precept)
# Save as a PD DataFrame and  Set the index to the date column and sort the dataframe by date
precept_df = pd.DataFrame(precept)
precept_df.set_index('date')

precept_df.head()
# Use Pandas Plotting with Matplotlib to plot the data
rain = precept_df.plot(title="Precepititation Analysis", figsize=(10,8))
rain.set_ylabel("Rain Fall in Inches ")

plt.show
# Use Pandas to calcualte the summary statistics for the precipitation data
precept_df.describe()
# Design a query to show how many stations are available in this dataset?
session.query(measurement.station).distinct().count()
# What are the most active stations? (i.e. what stations have the most rows)?
# List the stations and the counts in descending order.
mostActStations = session.query(measurement.station, func.count(measurement.station)).\
group_by(measurement.station).\
order_by(func.count(measurement.station).desc()).all()

print(mostActStations)
# Choose the station with the highest number of temperature observations.
topStation = "USC00519281"

print(topStation)
# Using the station id from the previous query, calculate the lowest temperature recorded, 
# highest temperature recorded, and average temperature of the most active station?
actTemps = session.query(func.max(measurement.tobs),func.avg(measurement.tobs), func.min(measurement.tobs)).filter(measurement.station == topStation).all()

print(actTemps)
# Choose the station with the highest number of temperature observations.

topStation = "USC00519281"

print(topStation)
# Query the last 12 months of temperature observation data for this station 
tempObs = session.query(measurement.station, measurement.tobs).filter(measurement.date >=yearAgo).filter(measurement.station == topStation).all()

print(tempObs)
# make into df 
tempObs_df = pd.DataFrame(tempObs)
tempObs_df.head()
# plot the results as a histogram
tempObs_df.plot(kind='hist', bins=12)
plt.ylabel("Frequency")
plt.xlabel("Temp")
plt.title("Temp Histogram")

plt.show()<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.0958819913952059, 'fraction_numerical': 0.010448678549477565, 'mean_word_length': 6.21729490022173, 'pattern_counts': {'":': 0, '<': 9, '<?xml version=': 0, '>': 11, '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': '2', 'dir': 'jupyter-structured-clean-dedup', 'id': '596455', 'license': 'ADSL', 'n_tokens_mistral': 1017, 'n_tokens_neox': 956, 'n_words': 308, 'path': 'Climate Analysis.ipynb', 'repo_name': 'danchavez728/sqlalchemy-challenge', 'size': '3254'}