<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.
engine.execute('select max( date) from Measurement').fetchall()
# Alternative: Find the most recent date in the data set.
last_date_str = session.query(Measurement.date).order_by(Measurement.date.desc()).first()
last_date_x = dt.datetime.strptime( last_date_str[0], '%Y-%m-%d')
last_date = dt.date(last_date_x.year, last_date_x.month, last_date_x.day)
print( f'Most recent date: {last_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.
start_date = dt.date(last_date.year -1, last_date.month, last_date.day)
print( f'Data analysis starting date = {start_date}')

# Perform a query to retrieve the data and precipitation scores
pcp_data = session.query(Measurement.date, Measurement.prcp)\
            .filter(Measurement.date >= start_date)\
            .order_by(Measurement.date).all()

# Save the query results as a Pandas DataFrame and set the index to the date column
pcp_df = pd.DataFrame(pcp_data, columns=['Date', 'Precipitation']).sort_values('Date').dropna().set_index('Date')
pcp_df.head()
# Use Pandas to calcualte the summary statistics for the precipitation data
pcp_df.describe()
# Use Pandas Plotting with Matplotlib to plot the data
ax = pcp_df.plot(color='deepskyblue', figsize=(9, 5), rot=90);
plt.title( f'Honolulu Precipitation from {start_date} to {last_date}');
ax.set_ylabel('Precipitation (inches)');
ax.legend(loc='upper right')

plt.savefig('Output/Precipitation.png')
plt.show()<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(Station).count()

print(f'Number of Stations: {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, Station.name, func.count(Measurement.id))\
    .filter(Measurement.station == Station.station)\
    .group_by(Measurement.station)\
    .order_by(func.count(Measurement.id).desc())\
    .all()
)

print( 'The most active stations are: ')
pprint(active_stations)
# Using the most active station id from the previous query, calculate the lowest, highest, and average temperature.
most_active_stats = (
    session.query(
        func.min(Measurement.tobs),
        func.max(Measurement.tobs),
        func.avg(Measurement.tobs),
    )\
    .filter(Measurement.station == active_stations[0][0])\
    .all()
)

print(f'The most active station is {active_stations[0][0]} - {active_stations[0][1]}. This station\'s temperature recordings are: ')
print(f'Low: {most_active_stats[0][0]}F')
print(f'High: {most_active_stats[0][1]}F')
print(f'Avg: {most_active_stats[0][2] :,.1f}F')
# 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
station_temps = (
    session.query(Measurement.date, Measurement.tobs)\
    .filter(Measurement.date > start_date)\
    .filter(Measurement.station == active_stations[0][0])\
    .order_by(Measurement.date)\
    .all()
)

# Convert SQL query to Pandas dataframe
temp_df = pd.DataFrame( station_temps)
# Create histogram
temp_df.plot(kind='hist', color='deepskyblue', bins=12, alpha=0.85, figsize=(9, 5))
plt.title( f'Average Temperatures from {start_date} to {last_date}\n Station: {active_stations[0][0]}')
plt.xlabel( 'Temperature (F)')
plt.legend(['tobs'])

plt.savefig( 'Output/Histogram.png')<jupyter_output><empty_output><jupyter_text>Close session<jupyter_code># Close Session
session.close()<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.11749515816655907, 'fraction_numerical': 0.0073165483107381106, 'mean_word_length': 5.446601941747573, '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': '265154', 'license': 'ADSL', 'n_tokens_mistral': 1528, 'n_tokens_neox': 1475, 'n_words': 419, 'path': 'climate_analysis.ipynb', 'repo_name': 'mattbjensen/sqlalchemy-challenge', 'size': '4647'}