<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:///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
Measure = 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.
recent_date = session.query(Measure.date).order_by(Measure.date.desc()).first()
recent_date_2 = dt.datetime.strptime(recent_date[0],'%Y-%m-%d')
recent_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. #

# Calculate the date one year from the last date in data set.
date_year_ago = recent_date_2 - dt.timedelta(days=365)
date_year_ago_2 = date_year_ago.strftime('%Y-%m-%d')

# Perform a query to retrieve the data and precipitation scores
precip_data = session.query(Measure.date,Measure.prcp).\
              filter(Measure.date >= date_year_ago_2).all()

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

# Sort the dataframe by date
precip_data.sort_values(by=['date'])
precip_data = precip_data.dropna(how='any')
precip_data

# Use Pandas Plotting with Matplotlib to plot the data
fig = plt.figure()
ax = fig.add_axes([1, 1, 1, 1]) #main axes
plt.grid(True,which='major',color='#445577', linestyle='-')
plt.bar(precip_data['date'],precip_data['prcp'],width=3)

# Sets x ticker locations and labels
ax.set_xticks([0,40,80,120,160,200,240,280,320])
ax.set_xticklabels(['2016-08-23','2016-10-01','2016-11-09',\
        '2016-12-19','2017-01-28','2017-03-09',\
        '2017-04-18','2017-05-31','2017-07-10'])

#Rotates x tickers
plt.xticks(rotation = 90)

#Title, legend, ylabel, xlabel
plt.title('Precipitation from 2016-08-23 to 2017-08-23')
plt.legend(['Precipitation'],loc=[0.8,0.8])
plt.ylabel('Inches')
ax.set_xlabel('Date')
plt.show()

# Change figure color
plt.figure(facecolor='#E6EAE7') 
# Use Pandas to calculate the summary statistics for the precipitation data
round(precip_data.describe(),2)<jupyter_output><empty_output><jupyter_text>Exploratory Station Analysis<jupyter_code># Design a query to calculate the total number stations in the dataset
station_cnt = session.query(Station).count()
station_cnt
# 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_station = session.query(Station.station,Station.name,func.count(Measure.station)).\
              filter(Measure.station == Station.station).group_by(Station.station).order_by(func.count(Measure.station).desc()).\
              first()
active_station              
# Using the most active station id from the previous query, calculate the lowest, highest, and average temperature.
#Higest temp
max_temp = session.query(func.max(Measure.tobs)).\
           filter(Measure.station == active_station[0]).all()[0][0]

#Lowest temp
min_temp = session.query(func.min(Measure.tobs)).\
           filter(Measure.station == active_station[0]).all()[0][0]

#Average temp
avg_temp = round(session.query(func.avg(Measure.tobs)).\
           filter(Measure.station == active_station[0]).all()[0][0],2)
# 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
temp_data = session.query(Measure.date,Measure.tobs,Measure.station).\
              filter(Measure.date >= date_year_ago_2).\
              filter(Measure.station == 'USC00519281').all()

#Plot historgram with temperature data for USC00519281
#using Matplotlib
temp_data = pd.DataFrame(temp_data,columns = ['date','tobs','station'])
num_bins = 12
temp_data
temp_data.plot.hist(bins = num_bins)
plt.title('Frequency of Temperature for USC00519281')
plt.legend(['Temperature Frequency'])
plt.ylabel('Frequency')
plt.xlabel('Temperature (F)')

# Change figure color
plt.figure(facecolor='#E6EAE7') 

plt.show()<jupyter_output><empty_output><jupyter_text>Close session<jupyter_code># Close Session
engine.dispose()
session.close()<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.12429141297501575, 'fraction_numerical': 0.038211211421373084, 'mean_word_length': 5.301587301587301, 'pattern_counts': {'":': 0, '<': 17, '<?xml version=': 0, '>': 19, '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': '229017', 'license': 'ADSL', 'n_tokens_mistral': 1670, 'n_tokens_neox': 1505, 'n_words': 396, 'path': 'PP_climate_starter.ipynb', 'repo_name': 'pramodphilip/sqlalchemy-challenge', 'size': '4763'}