<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)<jupyter_output><empty_output><jupyter_text>Exploratory Precipitation Analysis<jupyter_code># Find the most recent date in the data set.
most_recent = session.query(Measurement.date).order_by(Measurement.date.desc()).first().date
most_recent
# 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
year_before = dt.date(2017,8,23) - dt.timedelta(days = 365)

# Perform a query to retrieve the data and precipitation scores
precipitation = session.query(Measurement.date, Measurement.prcp).filter(Measurement.date > year_before).\
order_by(Measurement.date).all()
precipitation[0]
# Save the query results as a Pandas DataFrame and set the index to the date column
precipitation_df = pd.DataFrame(precipitation)

# Clean up column names
precipitation_df = precipitation_df.rename(columns={"prcp": "Precipitation", "date": "Date"})

# Sort the dataframe by date
precipitation_df = precipitation_df.sort_values(by = "Date").dropna()
precipitation_df
# Use Pandas Plotting with Matplotlib to plot the data
precipitation_df.plot("Date", "Precipitation", rot = 70, figsize = (11,6), legend = False)
plt.title("Precipitation Analysis")
plt.xlabel("Date")
plt.ylabel("Precipitation (in inches)")
plt.savefig("Resources/precipitation_analysis.png", dpi = 75, bbox_inches = "tight")
plt.show()
# Use Pandas to calcualte the summary statistics for the precipitation data
precipitation_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 = session.query(Station).count()
print(f"The total number of stations is {Stations}")
# 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.
active_stations = (session.query(Measurement.station, func.count(Measurement.station)).\
                   group_by(Measurement.station).order_by(func.count(Measurement.station).desc()).all())

# Make the output look pretty
for j, i in active_stations:
    print("The station " + j + " had " + str(i) + " observations" )
# Using the most active station id from the previous query, calculate the lowest, highest, and average temperature.
most_active = active_stations[0][0]

session.query(func.min(Measurement.tobs), func.max(Measurement.tobs), func.avg(Measurement.tobs)).\
               filter(Measurement.station == most_active).all()
# 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
results = session.query(Measurement.date, Measurement.tobs).\
    filter(Measurement.date >= "2016-08-24").\
    filter(Measurement.date <= "2017-08-23").\
    filter(Measurement.station == most_active).all()

# convert to dataframe before plotting results
results_df = pd.DataFrame(results)

# histogram of plotted results 
results_df.plot.hist(by = "tobs", bins = 12, figsize = (11,6), legend = False)
plt.title("Station Analysis")
plt.xlabel("Temperature")
plt.ylabel("Frequency")
plt.savefig("Resources/station_analysis.png", dpi = 75, bbox_inches = "tight")
plt.show()<jupyter_output><empty_output><jupyter_text>Close session<jupyter_code># Close Session
session.close()<jupyter_output><empty_output>
StarcoderdataJupyterStructuredCleanDedup