<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
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)
# 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)
conn = engine.connect()<jupyter_output><empty_output><jupyter_text>Exploratory Climate 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
latest = session.query(measurement.date).order_by(measurement.date.desc()).first()
print(f"Latest: {latest[0]}")
# Perform a query to retrieve the data and precipitation scores
last_twelve = engine.execute('SELECT date, prcp AS precipitation FROM measurement WHERE date >= "2016-08-23"').fetchall()
# Preview the Data
df = pd.DataFrame(last_twelve, columns=['date', 'precipitation'])
# Save the query results as a Pandas DataFrame and set the index to the date column
date_df = df.set_index(['date'])
# Sort the dataframe by date
last_twelve_df = date_df.sort_values(by=['date'], ascending=True)
#drop 'none' values
clean_df = last_twelve_df.dropna(how='any')
clean_df.head()
# Use Pandas Plotting with Matplotlib to plot the data
ax= clean_df.plot.bar(figsize=(16,8), width=20)
ax.set_xlabel("date", fontsize=14)
ax.set_ylabel("inches", fontsize=14)
ax.set_xticklabels([])
plt.legend(['precipitation'], fontsize=20)
ax.get_legend().set_bbox_to_anchor((0.6, 1))
plt.tight_layout()
plt.show()
# Use Pandas to calcualte the summary statistics for the precipitation data
clean_df.describe()
# Design a query to show how many stations are available in this dataset?
station_count = session.query(func.count(station.id)).first()
station_count
# What are 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()
active_stations
# Using the station id from the previous query, calculate the lowest temperature recorded, 
# highest temperature recorded, and average temperature of the most active station?
first_station = session.query(measurement.station).group_by(measurement.station).\
                               order_by(func.count(measurement.station).desc()).first()
first_station
session.query(measurement.station, func.min(measurement.tobs), func.max(measurement.tobs), func.avg(measurement.tobs)).\
    filter(measurement.station=='USC00519281').all()
# Choose the station with the highest number of temperature observations.
# Query the last 12 months of temperature observation data for this station and plot the results as a histogram
tobs_data =  session.query(measurement.date,measurement.tobs).\
                    filter(measurement.station == 'USC00519281').\
                    filter(measurement.date > '2016-08-23').all()
tobs_data[:10]
tobs_df = pd.DataFrame(tobs_data, columns=['date', 'tobs'])
plt.figure(figsize=(12,8))
plt.hist(tobs_df["tobs"], bins=12)
plt.ylabel("frequency")
plt.xlabel("temperature")
plt.legend(["tobs"])
plt.tight_layout()
plt.show()<jupyter_output><empty_output>
StarcoderdataJupyterStructuredCleanDedup