<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.
date = session.query(measurement.date).order_by(measurement.date.desc()).first()
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. 
date = dt.date(2017, 8, 23)

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

# Perform a query to retrieve the data and precipitation scores
query = session.query(measurement.date, measurement.prcp).filter(measurement.date >= year).all()

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

# Sort the dataframe by date
measurement_df.sort_index()
# Use Pandas Plotting with Matplotlib to plot the data
measurement_df.plot(title = 'Precipitation', figsize = (12,8), rot = 45)
plt.legend(loc = 'upper left')
plt.ylim(0,7.7)
plt.ylabel('Inches')
plt.xlabel('Date')
# Use Pandas to calcualte the summary statistics for the precipitation data
measurement_df.describe()
measurement_df<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(measurement.station).distinct().count()
print(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.id)).group_by(measurement.station).order_by(func.count(measurement.id).desc()).all()
active_stations
# Using the most active station id from the previous query, calculate the lowest, highest, and average temperature.
station_id = [measurement.station, 
      func.min(measurement.tobs),
      func.max(measurement.tobs),
      func.avg(measurement.tobs)]

most_active = session.query(*station_id).filter_by(station = "USC00519281").all()

print(f"The lowest temperature for {most_active[0][0]} is {most_active[0][1]}")
print(f"The highest temperature for {most_active[0][0]} is {most_active[0][2]}")
print(f"The average temperature for {most_active[0][0]} is {most_active[0][3]}")
# 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
months = session.query(measurement.date, measurement.tobs).filter_by(station = "USC00519281").\
    filter(measurement.date >= year).all()

months_df = pd.DataFrame(months)

months_df.plot(kind = "hist", bins = 12)
plt.xlabel('Temperature')
plt.ylabel('Frequency')
plt.title('Temps at Stations')<jupyter_output><empty_output><jupyter_text>Close session<jupyter_code># Close Session
session.close()<jupyter_output><empty_output>
StarcoderdataJupyterStructuredCleanDedup