In [2]:
%matplotlib inline
from matplotlib import style
style.use('fivethirtyeight')
import matplotlib.pyplot as plt
In [3]:
import numpy as np
import pandas as pd
In [4]:
import datetime as dt
Reflect Tables into SQLAlchemy ORM¶
In [5]:
# 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
In [6]:
engine = create_engine("sqlite:///Resources/hawaii.sqlite")
In [7]:
# reflect an existing database into a new model
Base = automap_base()
# reflect the tables
Base.prepare(engine,reflect=True)
In [8]:
# We can view all of the classes that automap found
Base.classes.keys()
Out[8]:
In [9]:
# Save references to each table
Measurement = Base.classes.measurement
Station = Base.classes.station
In [10]:
# Create our session (link) from Python to the DB
session = Session(engine)
first_row_meas = session.query(Measurement).first()
first_row_stat = session.query(Station).first()
first_row_meas.__dict__,first_row_stat.__dict__
Out[10]:
Exploratory Climate Analysis¶
In [11]:
# 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
last_date = session.query(Measurement,Measurement.date).order_by(Measurement.id.desc()).first()
last_date_yr = last_date[1][0:4]
last_date_mo = last_date[1][5:7]
last_date_day = last_date[1][8:10]
last_date = dt.datetime(int(last_date_yr),int(last_date_mo),int(last_date_day))
year_ago = dt.datetime((int(last_date_yr)-1),int(last_date_mo),int(last_date_day))
# Perform a query to retrieve the data and precipitation scores
last12 = session.query(Measurement.date,Measurement.prcp).\
filter(Measurement.date>year_ago).\
order_by(Measurement.date.desc()).all()
# Save the query results as a Pandas DataFrame and set the index to the date column
# Sort the dataframe by date
df = pd.DataFrame(last12,columns=["Date","Rainfall (in)"])
df = df.set_index("Date")
df = df.sort_index(ascending=False)
mean_precip = df.groupby(df.index).mean()
# Use Pandas Plotting with Matplotlib to plot the data
plt.subplots(figsize =(25, 12))
plt.plot(mean_precip.index,mean_precip)
plt.xlabel("Date")
plt.ylabel("Precip. Inches")
plt.title("Last 12 Months of Rainfall")
plt.xticks(np.arange(0, 365, step=20),rotation=45)
#plt.xticks(np.arange(0, 20, step=1),rotation=90)
#savefig('Visualizations/avg_salaries.png')
plt.show()
In [12]:
# Use Pandas to calcualte the summary statistics for the precipitation data
df.describe()
Out[12]:
In [13]:
# Design a query to show how many stations are available in this dataset?
session.query(Measurement.station).distinct().count()
Out[13]:
In [14]:
# What are the most active stations? (i.e. what stations have the most rows)?
# List the stations and the counts in descending order.
sel = [Measurement.station,
func.count(Measurement.id)]
stat_freq = session.query(*sel).\
group_by(Measurement.station).\
order_by(func.count(Measurement.id).desc()).all()
stat_freq
Out[14]:
In [15]:
# Using the station id from the previous query, calculate the lowest temperature recorded,
# highest temperature recorded, and average temperature of the most active station?
most_active_stat = stat_freq[0][0]
sel = [Measurement.station,
func.min(Measurement.tobs),
func.max(Measurement.tobs),
func.avg(Measurement.tobs),]
tobs_most_active = session.query(*sel).\
filter(Measurement.station == most_active_stat).\
group_by(Measurement.station).\
order_by(Measurement.station).all()
tobs_most_active
Out[15]:
In [16]:
# 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
sel = [Measurement.date,
Measurement.tobs]
last12_most_active_stat = session.query(*sel).\
filter(Measurement.date>year_ago).\
filter(Measurement.station==most_active_stat)
df_last12_mostact = pd.DataFrame(last12_most_active_stat,columns=["Date","Temperature"])
df_last12_mostact["Temperature"] = df_last12_mostact["Temperature"].astype(int)
bins = list(range(55,90,3))
nameobins = list(range(55,90,3))
fig, ax = plt.subplots(figsize =(20, 10))
ax.hist(df_last12_mostact["Temperature"],bins = bins)
plt.title("Temperature Observations from Most Active Station")
plt.ylabel("Frequency")
plt.xlabel("Temperature (F)")
ax.set_xticks(bins)
ax.set_xticklabels(nameobins)
#savefig('Visualizations/salary_ranges.png')
plt.show()
My Vaca¶
In [17]:
vaca_start = dt.datetime(2017,2,26)
vaca_end = dt.datetime(2017,3,4)
sel = [Measurement.date,
Measurement.prcp]
vaca_weather = session.query(*sel).\
filter(Measurement.date>=vaca_start).filter(Measurement.date<=vaca_end)
df_vaca = pd.DataFrame(vaca_weather,columns=["Date","Precipitation (in)"])
df_vaca = df_vaca.groupby(by="Date").mean()
plt.subplots(figsize =(25, 12))
plt.plot(df_vaca.index,df_vaca["Precipitation (in)"])
plt.xlabel("Date")
plt.ylabel("Precip. Inches")
plt.title("Rainfall Chances for My Hawaiian Vacation")
#plt.xticks(np.arange(0, 365, step=20),rotation=45)
#plt.xticks(np.arange(0, 20, step=1),rotation=90)
#savefig('Visualizations/avg_salaries.png')
plt.show()
df_vaca
Out[17]: