<jupyter_start><jupyter_text>HW10, SQL ALCHEMY, SURFS UP  Findings:* Overall, there isn't a strong pattern in precipitation amounts throughout the year* It looks like the middle of the chart has the least number of spikes in precip, which would be February* Dates chosen: Feb 1-10<jupyter_code># dependencies

%matplotlib inline
from matplotlib import style
style.use('fivethirtyeight')
import matplotlib.pyplot as plt
import numpy as np
import pandas as pd
import datetime as dt<jupyter_output><empty_output><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
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)
# We can 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)
# look at the columns and data types in each table

inspector = inspect(engine)
columns = inspector.get_columns('measurement')
for c in columns:
    print(c['name'], c["type"])

engine.execute('SELECT * FROM measurement LIMIT 20').fetchall()
inspector = inspect(engine)
columns = inspector.get_columns('station')
for c in columns:
    print(c['name'], c["type"])
    
engine.execute('SELECT * FROM station LIMIT 20').fetchall()<jupyter_output>id INTEGER
station TEXT
name TEXT
latitude FLOAT
longitude FLOAT
elevation FLOAT<jupyter_text>Precipitation Analysis<jupyter_code># 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

last_date = session.query(Measurement.date).order_by(Measurement.date.desc()).first()
print(last_date)

last_year = dt.date(2017, 8, 23) - dt.timedelta(days=365)
print(last_year)
# Perform a query to retrieve the data and precipitation scores

results = session.query(Measurement.date, Measurement.prcp).\
    filter(Measurement.date > last_year).\
    group_by(Measurement.date).\
    all()
results
# Save the query results as a Pandas DataFrame, set the index to the date column, sort by date

results_df = pd.DataFrame(results, columns=['date', 'precipitation'])
results_df.set_index('date').sort_values(by = 'date', ascending=True).head(10)
# Use Pandas Plotting with Matplotlib to plot the data
results_df.plot.bar(figsize=(15,10))
plt.xlabel("date")
plt.xticks([])
plt.show()
# Use Pandas to calcualte the summary statistics for the precipitation data
pd.DataFrame.describe(results_df)<jupyter_output><empty_output><jupyter_text>Station Analysis<jupyter_code># Design a query to show how many stations are available in this dataset
session.query(func.count(Station.station)).all()
# What are the most active stations? (i.e. what stations have the most rows)?
# List the stations and the counts in descending order.
station_activity = session.query(Measurement.station, func.count(Measurement.tobs)).\
    filter(Measurement.date > last_year).\
    group_by(Measurement.station).\
    order_by(func.count(Measurement.date).desc()).\
    all()
station_activity
# Using the station id from the previous query, calculate the lowest temperature recorded, 
# highest temperature recorded, and average temperature most active station

print(f'Lowest temperature is {session.query(func.min(Measurement.tobs)).first()}')
print(f'Highest temperature is {session.query(func.max(Measurement.tobs)).first()}')
print(f'Lowest temperature is {session.query(func.avg(Measurement.tobs)).first()}')
station_temps = session.query(Measurement.station, Measurement.tobs).\
    filter(Measurement.date > last_year).\
    filter(Measurement.station == 'USC00519397').\
    all()
station_temps
station_temps_df = pd.DataFrame(station_temps, columns=['station', 'tobs'])
station_temps_df.head()
plt.hist(station_temps_df['tobs'], bins=12)
plt.xlabel("TOBS")
plt.ylabel("Frequency")<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.10384706160018882, 'fraction_numerical': 0.008968609865470852, 'mean_word_length': 5.9135399673735725, 'pattern_counts': {'":': 0, '<': 16, '<?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': '442328', 'license': 'MIT', 'n_tokens_mistral': 1338, 'n_tokens_neox': 1267, 'n_words': 398, 'path': 'HW10-SurfsUp-EmilyTodd.ipynb', 'repo_name': 'emtodd11/HW10-SQL-Alchemy', 'size': '4237'}