<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
old_db = automap_base()
# reflect the tables
old_db.prepare(engine, reflect=True)
# View all of the classes that automap found
old_db.classes.keys()
# Save references to each table
meas = old_db.classes.measurement
stat = old_db.classes.station
# Create our session (link) from Python to the DB
sess = Session(engine)<jupyter_output><empty_output><jupyter_text>Exploratory Precipitation Analysis<jupyter_code># Find the most recent date in the data set.
engine.execute('SELECT max(date) FROM Measurement').fetchall()
# 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.
# Perform a query to retrieve the data and precipitation scores

annual_data = sess.query(meas.date, meas.prcp).filter(meas.date >= "2016-08-23").\
    filter(meas.date <= "2017-08-23").all()
# Save the query results as a Pandas DataFrame and set the index to the date column
annual_df = pd.DataFrame(annual_data).set_index('date')

# Sort the dataframe by date
annual_df=annual_df.rename(columns={'prcp':'Precipitation'})
annual_df=annual_df.sort_values(by='date').dropna()
annual_df.head()
# Use Pandas Plotting with Matplotlib to plot the data
annual_df.plot(rot=90)
plt.title("Precipitation")
plt.xlabel("Date")
plt.ylabel("Inches")
plt.legend('upper right')
plt.savefig("Precipitation.png")
plt.show()
# Use Pandas to calcualte the summary statistics for the precipitation data
annual_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
station_count = sess.query(stat.id).count() 
print(f'There are {station_count} 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.
top_station = sess.query(meas.station, func.count(meas.station)).\
group_by(meas.station).order_by(func.count(meas.station).desc()).all()
top_station
# Using the most active station id from the previous query, calculate the lowest, highest, and average temperature.
best_boy = 'USC00519281'
sess.query(func.min(meas.tobs),
            func.max(meas.tobs),
            func.avg(meas.tobs)).filter(meas.station == best_boy).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
best_boy_data = top_station[0][0]

results = sess.query(meas.date, meas.tobs).\
                        filter(meas.date >= "2016-08-24").\
                        filter(meas.date <= "2017-08-23").\
                        filter(meas.station == best_boy_data).all()

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

# historgram 
results_df.plot.hist(bins=12)
plt.xlabel("Temp")<jupyter_output><empty_output><jupyter_text>Close session<jupyter_code># Close Session
sess.close()<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.10357870235670644, 'fraction_numerical': 0.014547570555717195, 'mean_word_length': 5.0, 'pattern_counts': {'":': 0, '<': 19, '<?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': '307722', 'license': 'ADSL', 'n_tokens_mistral': 1140, 'n_tokens_neox': 1053, 'n_words': 322, 'path': 'climate.ipynb', 'repo_name': 'azreallpw/sqlalchemy', 'size': '3437'}