<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
# reflect the tables
Base = automap_base()
Base.prepare(engine, reflect=True)
Measurement = Base.classes.measurements
Station = Base.classes.stations
# 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(bind=engine)<jupyter_output><empty_output><jupyter_text>Exploratory Precipitation Analysis<jupyter_code># Find the most recent date in the data set.
last_date = session.query(Measurement.date).order_by(Measurement.date.desc()).first()
last_date = last_date[0]
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.
precip = session.query(Measurement.date).filter(Measurement.date >= last_date)

# Calculate the date 1 year ago from today
prev_year = dt.datetime.strptime(last_date, "%Y-%m-%d")- dt.timedelta(days=365)
# Perform a query to retrieve the data and precipitation scores
precip_scores=session.query(Measurement.date,Measurement.prcp).filter(Measurement.date>=prev_year).all()

# Save the query results as a Pandas DataFrame and set the index to the date column
precip_df = pd.DataFrame(precip_scores, columns = ["date", "precipitation"])
precip_df["date"] = pd.to_datetime(precip_df["date"], format = "%Y-%m-%d")
precip_df.set_index("date", inplace = True)

# Sort the dataframe by date

precip_df = precipitation_df.sort_values(by="date", ascending = True)
precip_df

# Use Pandas Plotting with Matplotlib to plot the data

precip_df.plot(title="Precipitation")
plt.ylim(0,8)
plt.savefig("Images/Precipitation.png", bbox_inches="tight")
plt.show()
# Use Pandas to calcualte the summary statistics for the precipitation data
precip_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
session.query(Measurement.station).distinct().count()
# 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,func.count(Measurement.station)).group_by(Measurement.station).order_by(func.count(Measurement.station).desc()).all()
active_stations
# Using the most active station id from the previous query, calculate the lowest, highest, and average temperature.
most_active = 'USC00519281'
most_active

most_active_high = session.query(func.max(Measurement.tobs)).\
                    filter(Measurement.station == most_active).all()
most_active_low = session.query(func.min(Measurement.tobs)).\
                    filter(Measurement.station == most_active).all()
most_active_avg = session.query(func.avg(Measurement.tobs)).\
                    filter(Measurement.station == most_active).all()

print(f'The highest temperature observed at station {most_active} was {most_active_high[0]}')
print(f'The lowest temperature observed at station {most_active} was {most_active_low[0]}')
print(f'The average temperature observed at station {most_active} was {most_active_avg[0]}')

# 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(Measurement.tobs).\
            filter(Measurement.date >= prev_year).\
            filter(Measurement.station == most_active)

temp_data = pd.DataFrame(temp_data, columns=['2017 Temps'])



temp_data.plot.hist(title = 'Frequency V Temp')<jupyter_output><empty_output><jupyter_text>Close session<jupyter_code># Close Session
session.close()<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.10023923444976077, 'fraction_numerical': 0.006220095693779904, 'mean_word_length': 5.776337115072933, 'pattern_counts': {'":': 0, '<': 17, '<?xml version=': 0, '>': 20, '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': '327139', 'license': 'ADSL', 'n_tokens_mistral': 1286, 'n_tokens_neox': 1234, 'n_words': 358, 'path': 'climate_starter.ipynb', 'repo_name': 'jtbuckwalter/UPBC-Assignment-10-Advanced-Data-Storage-and-Retrieval', 'size': '4180'}