<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
from sqlalchemy import desc
from sqlalchemy import asc
from sqlalchemy.orm import sessionmaker
# 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
HI_prcp_Measurement = Base.classes.measurement
HI_Station = Base.classes.station
HI_Station
# Create our session (link) from Python to the DB
session = Session(engine)<jupyter_output><empty_output><jupyter_text>Exploratory Precipitation Analysis<jupyter_code>HI_prcp_Measurement
# Find the most recent date in the data set. Order database by
# Date column using .order_by and specifyinig the 'date' column from the 
# Measurement table.  Using desc() to specify the order,
# the .first() method can be used to return the most recent date
prcp_measDB = session.query(HI_prcp_Measurement).order_by(desc(HI_prcp_Measurement.date)).first()
prcp_measDict = prcp_measDB.__dict__
prcp_measDict
# using list comprehension to set the value of 
# column "date" to Last_Date
Last_Date = prcp_measDict['date']
Last_Date
# Calculate the date one year from the last date in data set.
# using .replace() the year can be adjusted to the previous year
# and set to variable "One_Year_before"
One_Year_before = Last_Date.replace("2017", "2016")
One_Year_before
# Design a query to retrieve the last 12 months of precipitation data
# Perform a query to retrieve the data and precipitation scores
# Query date and precipitation columns from data
# filter data based on dates and show all data that fits criteria
# then Sort by date using order_by() 
last_12_prcp = session.query(HI_prcp_Measurement.date, HI_prcp_Measurement.prcp).\
    filter(HI_prcp_Measurement.date >= One_Year_before).\
    filter(HI_prcp_Measurement.date <= Last_Date).\
    order_by(asc(HI_prcp_Measurement.date)).all()

# Using list comprehension to separate tuples
# last_12_prcp = [a for *a, in last_12_prcp]
# Using list comprehension to remove nested lists
# last_12_prcp = [x for l in last_12_prcp for x in l]

last_12_prcp
last_12_prcp_dict = dict(last_12_prcp)
last_12_prcp_dict

# Query date column from data
# filter data based on dates and show all data that fits criteria
# then Sort by date using order_by() 
last_12_prcp_date = session.query(HI_prcp_Measurement.date).\
    filter(HI_prcp_Measurement.date >= One_Year_before).\
    filter(HI_prcp_Measurement.date <= Last_Date).\
    order_by(asc(HI_prcp_Measurement.date)).all()

# Using list comprehension to separate tuples
last_12_prcp_date = [a for *a, in last_12_prcp_date]
# Using list comprehension to remove nested lists
last_12_prcp_date = [x for l in last_12_prcp_date for x in l]
# convert to date time format
last_12_prcp_date = pd.to_datetime(last_12_prcp_date)
last_12_prcp_date
# Query precipitation column from data
# filter data based on dates and show all data that fits criteria
# then Sort by date using order_by() 
last_12_prcp_measurement = session.query(HI_prcp_Measurement.prcp).\
    filter(HI_prcp_Measurement.date >= One_Year_before).\
    filter(HI_prcp_Measurement.date <= Last_Date).\
    order_by(asc(HI_prcp_Measurement.date)).all()

# Using list comprehension to separate tuples
last_12_prcp_measurement = [a for *a, in last_12_prcp_measurement]
# Using list comprehension to remove nested lists
last_12_prcp_measurement = [x for l in last_12_prcp_measurement for x in l]
last_12_prcp_measurement
# Save the query results as a Pandas DataFrame and set the index to the date column
last_12_prcp_df = pd.DataFrame({"date":last_12_prcp_date, "Precipitation":last_12_prcp_measurement})
last_12_prcp_df.set_index("date", inplace=True)
last_12_prcp_df.sort_index(ascending=True)
last_12_prcp_df
# plot the results. 
# Starting from the most recent data point in the database.
# Use Pandas Plotting with Matplotlib to plot the data
fig, ax = plt.subplots(figsize=(10, 10))
plt.style.use('fivethirtyeight')

# Add x-axis and y-axis
ax.bar(last_12_prcp_df.index.values,
        last_12_prcp_df['Precipitation'])

# Set title and labels for axes
ax.set(xlabel="Date",
       ylabel="Precipitation (inches)",
       title="One Year precipitation")

# Rotate tick marks on x-axis
plt.setp(ax.get_xticklabels(), rotation=45)
plt.style
plt.show()
# Use Pandas to calcualte the summary statistics for the precipitation data
last_12_prcp_df.describe()
# 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.


# Perform a query to retrieve the data and precipitation scores


# Save the query results as a Pandas DataFrame and set the index to the date column


# Sort the dataframe by date


# Use Pandas Plotting with Matplotlib to plot the data



# Use Pandas to calcualte the summary statistics for the precipitation data<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(func.count(HI_Station.station)).all()
Station_Count = [a for *a, in Station_Count]
Station_Count = [x for l in Station_Count for x in l]
Station_Count = Station_Count[0]
Station_Count
# 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.query(HI_prcp_Measurement.station,func.count(HI_prcp_Measurement.station)).\
group_by(HI_prcp_Measurement.station).\
order_by(func.count(HI_prcp_Measurement.station).desc()).all()
Most_Active = session.query(HI_prcp_Measurement.station,func.count(HI_prcp_Measurement.station)).\
group_by(HI_prcp_Measurement.station).\
order_by(func.count(HI_prcp_Measurement.station).desc()).first()

Most_Active
Most_Active_Station = Most_Active[0]
Most_Active_Station
tobs_meas_MAStation = session.query(HI_prcp_Measurement).\
filter(HI_prcp_Measurement.station == Most_Active_Station).\
order_by(desc(HI_prcp_Measurement.date)).first()
tobs_meas_MAStation_Dict = tobs_meas_MAStation.__dict__
tobs_meas_MAStation_Dict
# using list comprehension to set the value of 
# column "date" to Last_Date
Last_Date_MAStation = tobs_meas_MAStation_Dict['date']
Last_Date_MAStation
# using .replace() the year can be adjusted to the previous year
# and set to variable "One_Year_before"
One_Year_before_MAStation = Last_Date_MAStation.replace("2017", "2016")
One_Year_before_MAStation
# Query date and precipitation columns from data
# filter data based on dates and show all data that fits criteria
# then Sort by date using order_by() 
last_12_tobs_MAStation = session.query(HI_prcp_Measurement.date, HI_prcp_Measurement.tobs).\
    filter(HI_prcp_Measurement.station == Most_Active_Station).\
    filter(HI_prcp_Measurement.date >= One_Year_before_MAStation).\
    filter(HI_prcp_Measurement.date <= Last_Date_MAStation).\
    order_by(asc(HI_prcp_Measurement.date)).all()

# Using list comprehension to separate tuples
last_12_tobs_MAStation = [a for *a, in last_12_tobs_MAStation]
# Using list comprehension to remove nested lists
last_12_prcp = [x for l in last_12_prcp for x in l]
last_12_tobs_MAStation
# Query date column from data
# filter data based on dates and show all data that fits criteria
# then Sort by date using order_by() 
last_12_prcp_date_MAStation = session.query(HI_prcp_Measurement.date).\
    filter(HI_prcp_Measurement.station == Most_Active_Station).\
    filter(HI_prcp_Measurement.date >= One_Year_before_MAStation).\
    filter(HI_prcp_Measurement.date <= Last_Date_MAStation).\
    order_by(asc(HI_prcp_Measurement.date)).all()

# Using list comprehension to separate tuples
last_12_prcp_date_MAStation = [a for *a, in last_12_prcp_date_MAStation]
# Using list comprehension to remove nested lists
last_12_prcp_date_MAStation = [x for l in last_12_prcp_date_MAStation for x in l]
# convert to date time format
last_12_prcp_date_MAStation = pd.to_datetime(last_12_prcp_date_MAStation)
last_12_prcp_date_MAStation
# Query precipitation column from data
# filter data based on dates and show all data that fits criteria
# then Sort by date using order_by() 
last_12_tobs_measurement_MAStation = session.query(HI_prcp_Measurement.tobs).\
    filter(HI_prcp_Measurement.station == Most_Active_Station).\
    filter(HI_prcp_Measurement.date >= One_Year_before_MAStation).\
    filter(HI_prcp_Measurement.date <= Last_Date_MAStation).\
    order_by(asc(HI_prcp_Measurement.date)).all()

# Using list comprehension to separate tuples
last_12_tobs_measurement_MAStation = [a for *a, in last_12_tobs_measurement_MAStation]
# Using list comprehension to remove nested lists
last_12_tobs_measurement_MAStation = [x for l in last_12_tobs_measurement_MAStation for x in l]
last_12_tobs_measurement_MAStation
# 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_12_tobs_df_MAStation = pd.DataFrame({"date":last_12_prcp_date_MAStation, "Temp. Obervations":last_12_tobs_measurement_MAStation})
last_12_tobs_df_MAStation.set_index("date", inplace=True)
last_12_tobs_df_MAStation.sort_index(ascending=True)
last_12_tobs_df_MAStation
last_12_tobs_df_MAStation.plot.hist(bins=12)<jupyter_output><empty_output><jupyter_text>Close session<jupyter_code># Close Session
session.close()<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.0735503560528993, 'fraction_numerical': 0.015259409969481181, 'mean_word_length': 6.042263610315186, 'pattern_counts': {'":': 4, '<': 23, '<?xml version=': 0, '>': 23, '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': '513625', 'license': 'ADSL', 'n_tokens_mistral': 3328, 'n_tokens_neox': 3145, 'n_words': 928, 'path': 'climate_starter.ipynb', 'repo_name': 'Zone6Mars/sqlalchemy-challenge', 'size': '9830'}