<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
Measure = 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 Climate Analysis<jupyter_code>max_date = session.query(func.max(func.strftime("%Y-%m-%d", Measure.date))).limit(5).all()
max_date[0][0]

# Design a query to retrieve the last 12 months of precipitation data and plot the results
# Calculate the date 1 year ago from the last data point in the database
# Perform a query to retrieve the data and precipitation scores

rain_data = session.query(func.strftime("%Y-%m-%d", Measure.date), Measure.prcp).\
    filter(func.strftime("%Y-%m-%d", Measure.date) >= dt.date(2016, 8, 23)).all()

# Save the query results as a Pandas DataFrame and set the index to the date column
rain_df = pd.DataFrame(rain_data, columns = ['date', 'precipitation'])
rain_df.set_index('date', inplace = True)

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

# Use Pandas Plotting with Matplotlib to plot the data
fig, ax = plt.subplots(figsize = (20, 10))
rain_df.plot(ax = ax, x_compat = True)

ax.set_xlabel('Date')
ax.set_ylabel('Precipitation (in.)')
ax.set_title("Yearly Precipitation")

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

# Most active station are USC00519281, USC00519397, and USC00513117
# Using the station id from the previous query, calculate the lowest temperature recorded, 
# highest temperature recorded, and average temperature of the most active station?
active = 'USC00519281'

low_temp = session.query(func.min(Measure.tobs)).filter(Measure.station == active).all()
low_temp
high_temp = session.query(func.max(Measure.tobs)).filter(Measure.station == active).all()
high_temp
avg_temp = session.query(func.avg(Measure.tobs)).filter(Measure.station == active).all()
avg_temp
# 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
temp_obs = session.query(Measure.date, Measure.tobs).filter(Measure.station == active).\
    filter(func.strftime("%Y-%m-%d", Measure.date) >= dt.date(2016, 8, 23)).all()

temp_obs_df = pd.DataFrame(temp_obs, columns = ['date', 'temperature'])

fig, ax = plt.subplots()
temp_obs_df.plot.hist(bins = 12, ax = ax)

ax.set_xlabel('Temperature')
ax.set_ylabel('Occurence')

plt.tight_layout()
plt.show()<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.11319195813825393, 'fraction_numerical': 0.01652437345083999, 'mean_word_length': 5.073578595317726, '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': '400870', 'license': 'ADSL', 'n_tokens_mistral': 1188, 'n_tokens_neox': 1092, 'n_words': 343, 'path': 'climate_starter.ipynb', 'repo_name': 'Zannyo/SQLAlchemy-Homework', 'size': '3631'}