<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, inspect, func
# create engine to hawaii.sqlite

database_path = "../Resources/hawaii.sqlite"

engine = create_engine(f"sqlite:///{database_path}")
# 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>from sqlalchemy import inspect

inspector = inspect(engine)

measurement_columns = inspector.get_columns('Measurement')
for mc in measurement_columns:
    print(mc["name"], mc["type"])
engine.execute('SELECT * FROM measurement LIMIT 5').fetchall()
station_columns = inspector.get_columns('Station')
for sc in station_columns:
    print(sc["name"], sc["type"])
engine.execute('SELECT * FROM station LIMIT 5').fetchall()
# Find the most recent date in the data set.
for recent_date in session.query(Measurement.date).order_by(Measurement.date.desc()).first():
    print(recent_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.
previous_year = dt.date(2017, 8, 23) - dt.timedelta(days=365)
print("One Year from Last Date:", previous_year)
# Perform a query to retrieve the date and precipitation scores
prev_year_prcp = session.query(Measurement.date, Measurement.prcp).filter(Measurement.date >= previous_year).all()
prev_year_prcp
# Save the query results as a Pandas DataFrame and set the index to the date column
prev_year_prcp_df = pd.DataFrame(prev_year_prcp, columns = ['Date', 'Precipitation'])
prev_year_prcp_df.set_index('Date', inplace = True)
prev_year_prcp_df.head()
# Sort the dataframe by date
df_sorted = prev_year_prcp_df.sort_values('Date')
df_sorted
# Use Pandas Plotting with Matplotlib to plot the data
# df_sorted.plot('Date', 'Precipitation', rot=90, figsize = (10, 5))
# plt.ylim(0, 8)
# plt.xlabel('Date')
# plt.ylabel('Precipitation (inches)')
# plt.title('Precipitation - 08/23/2016 - 08/23/2017')
# plt.savefig("../Images/precipitation.png")
# plt.legend(loc="best")
# #plt.show()

fig, ax = plt.subplots(figsize = (10, 5))
df_sorted.plot(ax = ax, x_compat = True, rot = 90)
ax.set_xlabel('Date')
ax.set_ylabel('Precipitation (inches)')
ax.set_title('Precipitation - 08/23/2016 - 08/23/2017')
#ax.set_xticks
plt.ylim(0, 7)
plt.savefig("../Images/precipitation.png", bbox_inches = 'tight')
#plt.tight_layout()
plt.show()
# Use Pandas to calculate the summary statistics for the precipitation data
df_sorted.describe()<jupyter_output><empty_output><jupyter_text>Exploratory Station Analysis<jupyter_code># Design a query to calculate the total number stations in the dataset
station_count = session.query(Station).filter(Station.id).count()
print(f"There are {station_count} stations in the dataset.")
# 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.

# session.execute('''
# SELECT COUNT(*), station
# FROM measurement
# GROUP BY station
# ORDER BY COUNT(*) DESC
# ''').fetchall()

station_activity = (session.query(Measurement.station, func.count(Measurement.station))\
               .group_by(Measurement.station).order_by(func.count(Measurement.station).desc()).all())
station_activity
# What stations have the most rows?

most_active = (session.query(Measurement.station)\
               .group_by(Measurement.station).order_by(func.count(Measurement.station).desc()).first())
print(f"Station {most_active} has the most amount of observations.")
# Using the most active station id from the previous query, calculate the lowest, highest, and average temperature.

tobs = [Measurement.station, 
       func.min(Measurement.tobs),
       func.max(Measurement.tobs), 
       func.avg(Measurement.tobs)]
most_active_calc = session.query(*tobs).filter(Measurement.station == "USC00519281").all()

most_active_calc_df = pd.DataFrame(most_active_calc, columns = ['Station', 'Min Temp', 'Max Temp', 'Avg Temp'])
most_active_calc_df
# Using the most active station id
# Query the last 12 months of temperature observation data for this station

prev_year_tobs = session.query(Measurement.station, Measurement.date, Measurement.tobs)\
    .filter(Measurement.date >= previous_year)\
    .filter(Measurement.station == 'USC00519281').all()
prev_year_tobs
prev_year_tobs_df = pd.DataFrame(prev_year_tobs, columns = ['Station', 'Date', 'TOBS'])
prev_year_tobs_df.set_index('Date', inplace = True)
prev_year_tobs_df.head()
tobs_df_sorted = prev_year_tobs_df.sort_values('Date')
tobs_df_sorted
# Plot the results as a histogram

tobs_df_sorted.plot.hist(by=None, bins=12)
plt.xlabel('Temperature')
plt.title('USC00519281 Temperature: 08/23/2016 - 08/23/2017')
plt.savefig("../Images/tobs.png", bbox_inches = 'tight')
plt.legend(loc="best")
plt.show()<jupyter_output><empty_output><jupyter_text>Close session<jupyter_code># Close Session
session.close()<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.12021660649819495, 'fraction_numerical': 0.018772563176895306, 'mean_word_length': 5.849196538936959, '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': '563293', 'license': 'ADSL', 'n_tokens_mistral': 1897, 'n_tokens_neox': 1767, 'n_words': 469, 'path': 'Analysis/climate_starter.ipynb', 'repo_name': 'vandrade88/sqlalchemy-challenge', 'size': '5540'}