<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

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.

recent_date = session.query(Measurement.date).order_by(Measurement.date.desc()).first()
print(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.
year_ago = dt.datetime(2017,8,23) - dt.timedelta(days = 365)
print(year_ago)

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

dates = [result[0] for result in results]
prcps = [result[1] for result in results]


# Save the query results as a Pandas DataFrame and set the index to the date column
data={'Date': dates,
     'Percipitation': prcps}
df = pd.DataFrame(data)
df = df.set_index('Date')

# Sort the dataframe by date
df = df.sort_index()

# Use Pandas Plotting with Matplotlib to plot the data
ax = df.plot(figsize=(9, 6))
ax.set_xlabel("Date")
ax.set_ylabel("Inches")
ax.set_title('Percipitation (Inches) by Date')
plt.xticks(rotation='vertical')
plt.tight_layout()
plt.show()

# Use Pandas to calcualte the summary statistics for the precipitation data

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
num_of_stations = session.query(Station).count()

print(f'Number of Sations: {num_of_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.
observation_counts = session.query(Measurement.station, func.count(Measurement.station)).\
    group_by(Measurement.station).\
    order_by(func.count(Measurement.station).desc()).all()

most_active_station = observation_counts[0][0]

print('Observation count for each stations')
for o in observation_counts:
    print(o)
print()

print(f'Station with Highest number of observations: {most_active_station}')
# Using the most active station id from the previous query, calculate the lowest, highest, and average temperature.
min = session.\
    query(func.min(Measurement.tobs)).\
    filter(Measurement.station == most_active_station).first()
max = session.\
    query(func.max(Measurement.tobs)).\
    filter(Measurement.station == most_active_station).first()
avg = session.\
    query(func.avg(Measurement.tobs)).\
    filter(Measurement.station == most_active_station).first()


temp_min = min[0]
temp_max = max[0]
temp_avg = avg[0]

print(f'Minimum Temperature: {temp_min}')
print(f'Maximum Temperature: {temp_max}')
print(f'Temperature average: {temp_avg}')
# 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
results = session.query(Measurement.date, Measurement.tobs).\
    filter(Measurement.date >= year_ago).\
    filter(Measurement.station == most_active_station).\
    order_by(Measurement.date).all()

dates = [result[0] for result in results]
temps = [result[1] for result in results]

# Save the query results as a Pandas DataFrame and set the index to the date column
data={'Date': dates,
     'tobs': temps}
df = pd.DataFrame(data)
df = df.set_index('Date')

# Sort the dataframe by date
df = df.sort_index()
df.head()

# Use Pandas Plotting with Matplotlib to plot the data
ax = df.plot.hist(bins=12,figsize=(9, 6))
plt.xlabel('Temperature')
plt.show()
df<jupyter_output><empty_output><jupyter_text>Close session<jupyter_code># Close Session
session.close()<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.10522832561217736, 'fraction_numerical': 0.0063975292300904475, 'mean_word_length': 5.367977528089888, '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': '55479', 'license': 'ADSL', 'n_tokens_mistral': 1463, 'n_tokens_neox': 1402, 'n_words': 439, 'path': 'climate_starter.ipynb', 'repo_name': 'rjsoriano1990/sqlalchemy-challenge', 'size': '4533'}