<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)
conn = engine.connect()<jupyter_output><empty_output><jupyter_text>Exploratory Precipitation Analysis<jupyter_code># Find the most recent date in the data set.
recent_date = session.query(Measurement.date).order_by(Measurement.date.desc()).first()
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. 

import datetime as dt

# Calculate the date one year from the last date in data set.
last_year = dt.date(2017, 8, 23) - dt.timedelta(days=365)

# Perform a query to retrieve the data and precipitation scores
precip_date_query = session.query(Measurement.date, Measurement.prcp)\
.filter(Measurement.date <= dt.date(2017, 8, 23), Measurement.date >= last_year)\
.order_by(Measurement.date.desc()).all()

precip_date_query


# Save the query results as a Pandas DataFrame and set the index to the date column
precip_df = pd.DataFrame(precip_date_query)

precip_df.columns=["Date", "Precipitation (inches)"]

precip_df.set_index("Date",inplace=True)

# Sort the dataframe by date
precip_df.sort_index(inplace=True)
precip_df




# Use Pandas Plotting with Matplotlib to plot the data

precip_df.plot(title="Precipitation between 8/23/16 and 8/23/17",
               xlabel="Date",
               ylabel="Precipitation (inches)",
              rot=90,
               figsize=(7,3)
              )
plt.legend(loc="upper right")

plt.show()
# Use Pandas to calculate the summary statistics for the precipitation data
precip_df.describe()<jupyter_output><empty_output><jupyter_text>Exploratory Station Analysis<jupyter_code># Design a query to calculate the total number stations in the dataset
stations_count = session.query(Station.station).count()
stations_count
# Design a query to find the most active stations (i.e. what stations have the most rows?)

stations = session.query(Measurement.station).all()
stations
stations_unique = list(np.ravel(stations))
stations_unique
from collections import Counter
most_active_station = Counter(stations).most_common(1)[0][0][0]
most_active_station
# List the stations and the counts in descending order.
station_list = Counter(stations).most_common
station_dict = {}

for key, value in Counter(stations).items():
    station_dict[key[0]] = value
    
station_df = pd.DataFrame([station_dict]).transpose()
station_df.columns=["Observation Counts"]
station_df.index.name="Station"
station_df.sort_values(by="Observation Counts", ascending=False)
# Using the most active station id from the previous query, calculate the lowest, highest, and average temperature.

station_lowest_temp = session.query(Measurement.station,Measurement.tobs).filter(Measurement.station == 'USC00519281').all()
station_lowest_temp

temp_df = pd.DataFrame(station_lowest_temp)
temp_df

temp_df.columns=["Stataion", "Temp"]
temp_min = temp_df["Temp"].min()
print(f"Min Temp: {temp_min}")

temp_max = temp_df["Temp"].max()
print(f"Max Temp: {temp_max}")

temp_avg = temp_df["Temp"].mean()
print(f"Avg Temp: {round(temp_avg,1)}")
# Using the most active station id
# Query the last 12 months of temperature observation data for this station
recent_station_date = session.query(Measurement.date)\
.filter(Measurement.station == 'USC00519281').order_by(Measurement.date.desc()).first()
recent_station_date

# Calculate the date one year from the last date in data set.
one_year_timeframe = dt.date(2017, 8, 18) - dt.timedelta(days=365)

# Perform a query to retrieve the data
temp_data = session.query(Measurement.date, Measurement.tobs)\
.filter(Measurement.date <= dt.date(2017, 8, 18), Measurement.date >= one_year_timeframe)\
.order_by(Measurement.date.desc()).all()

temp_data

# Create a DataFrame for the histogram
histogram_df = pd.DataFrame(temp_data)

histogram_df.columns=["Date", "Temp (F)"]
histogram_df

histogram_df.set_index("Date",inplace=True)
histogram_df
# Plot the results as a histogram
histogram_df.hist(bins=12, legend=True)
plt.title("Frequency of Temperature Points")
plt.xlabel("Temp (F)")
plt.ylabel("Frequency Count")
plt.show()
# Close Session
session.close()<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.10584615384615384, 'fraction_numerical': 0.01558974358974359, 'mean_word_length': 5.734806629834254, 'pattern_counts': {'":': 0, '<': 15, '<?xml version=': 0, '>': 15, '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': '3', 'dir': 'jupyter-structured-clean-dedup', 'id': '601554', 'license': 'ADSL', 'n_tokens_mistral': 1620, 'n_tokens_neox': 1512, 'n_words': 430, 'path': 'climate_analysis.ipynb', 'repo_name': 'APesch90/sqlalchemy-challenge', 'size': '4875'}