<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
# create engine to hawaii.sqlite
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)
# 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)
# Display the MEASUREMENT row's columns and data in dictionary format
first_row = session.query(Measurement).first()
first_row.__dict__
# Display the STATION row's columns and data in dictionary format
first_row = session.query(Station).first()
first_row.__dict__<jupyter_output><empty_output><jupyter_text>Exploratory Precipitation Analysis<jupyter_code># Find the most recent date in the data set.
most_recent = session.query(Measurement.date).order_by(Measurement.date.desc()).all()
most_recent = most_recent[0][0]
print(f"The most recent date in the dataset is: {most_recent}")
recent_year = int(most_recent[0:4])
recent_month = int(most_recent[5:7])
recent_day = int(most_recent[8:10])

print(f"The year is {recent_year}")
print(f"The month is {recent_month}")
print(f"The day is {recent_day}")
# 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.
start_date = dt.date(recent_year - 1, recent_month, recent_day)
print(start_date)

# Perform a query to retrieve the date and precipitation scores
scores = session.query(Measurement.date, Measurement.prcp).where(Measurement.date >= start_date)

# Save the query results as a Pandas DataFrame and set the index to the date column
scores_df = pd.DataFrame(scores, columns=["Date", "Precipitation"])
scores_df = scores_df.set_index("Date")

# Sort the dataframe by date
scores_df.head()
scores_df.dtypes
# Use Pandas Plotting with Matplotlib to plot the data
plot = scores_df.plot(kind="bar", figsize=(14, 5), width=15)
plt.xticks([])
plt.xlabel("Date from 2016-08-23 to 2017-08-23")
plt.ylabel("Precipitation")
plt.show()
# Use Pandas to calcualte the summary statistics for the precipitation data
summary_df = scores_df.describe()
summary_df<jupyter_output><empty_output><jupyter_text>Exploratory Station Analysis<jupyter_code># Design a query to calculate the total number stations in the dataset
stations = session.query(Station.station)
num_stations = len(pd.DataFrame(stations))
print(F"There are {num_stations} stations!")
# 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.
activity = session.query(Measurement.station)
activity_df = pd.DataFrame(activity, columns=["Count"])
activity_df = pd.DataFrame(activity_df["Count"].value_counts())
activity_df
activity_df.reset_index(inplace=True)
activity_df
# Using the most active station id from the previous query, calculate the lowest, highest, and average temperature.
values = session.query(func.min(Measurement.tobs), func.avg(Measurement.tobs), func.max(Measurement.tobs))\
        .filter(Measurement.station == 'USC00519281').all()

lowest_temp = round(values[0][0], 2)
average_temp = round(values[0][1], 2)
max_temp = round(values[0][2], 2)

print(f"Lowest temperature = {lowest_temp}")
print(f"Average temperature = {average_temp}")
print(f"Highest temperature = {max_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
last_months = session.query(Measurement.tobs).where(Measurement.date >= start_date)
pd.DataFrame(last_months, columns=["Temperature"]).hist(bins=12)
plt.tight_layout()<jupyter_output><empty_output><jupyter_text>Close session<jupyter_code># Close Session
session.close()<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.10193945127719962, 'fraction_numerical': 0.012771996215704825, 'mean_word_length': 6.565295169946332, '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': '262989', 'license': 'ADSL', 'n_tokens_mistral': 1373, 'n_tokens_neox': 1291, 'n_words': 401, 'path': 'climate_starter.ipynb', 'repo_name': 'omorales12/sqlalchemy-challenge', 'size': '4228'}