<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, desc
# Define database path
database_path = "Resources/hawaii.sqlite"

# Create engine to hawaii.sqlite
engine = create_engine(f"sqlite:///{database_path}")
# 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)
# Measurement columns
first_row_measurement = session.query(Measurement).first()
first_row_measurement.__dict__
# Station columns 
first_row_station = session.query(Station).first()
first_row_station.__dict__<jupyter_output><empty_output><jupyter_text>Exploratory Precipitation Analysis<jupyter_code># Find the most recent date in the data set.
most_recent_date = session.query(Measurement.date).order_by(Measurement.date.desc()).first()
most_recent_date = most_recent_date[0]
most_recent_date<jupyter_output><empty_output><jupyter_text>Design a query to retrieve the last 12 months of precipitation data and plot the results.<jupyter_code># Starting from the most recent data point in the database, calculate the date one year from the last date in data set.
recent_date = dt.strptime(most_recent_date, '%Y-%m-%d').date()
year_prior = recent_date - relativedelta(years=1)
year_prior_string = year_prior.strftime("%Y-%m-%d")
# Perform a query to retrieve the data and precipitation scores
year_data = session.query(Measurement.prcp, Measurement.date).filter(Measurement.date>=year_prior_string).all()
year_data<jupyter_output><empty_output><jupyter_text>Save the query results as a Pandas DataFrame and set the index to the date column<jupyter_code># Create empty lists
prcp = []
date = []

# Add data to list
for data in year_data:
    prcp.append(data[0])
    date.append(data[1])

# Create dataframe excluding NaN   
precipitation = pd.DataFrame({"Precipitation": prcp, "Date":date}).sort_values(by="Date").dropna()

# Save the query results as a Pandas DataFrame and set the index to the date column
precipitation = precipitation.set_index(precipitation["Date"], inplace=False)[["Precipitation"]]
# Sort the dataframe by date
precipitation.sort_values(by="Date")
# Use Pandas Plotting with Matplotlib to plot the data
prcp_plot = precipitation.plot(rot=90, figsize=(20, 10), ylabel ="Inches")
plt.savefig("precip_analysis.png")
plt.show()
# Use Pandas to calcualte the summary statistics for the precipitation data
precipitation.describe()<jupyter_output><empty_output><jupyter_text>Exploratory Station Analysis<jupyter_code># Design a query to calculate the total number stations in the dataset
total_stations = session.query(Measurement.station).distinct().all()

# print results
print(f"Total number of stations in dataset: {len(total_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, func.count(Measurement.station), Station.id).filter(Station.station==Measurement.station).group_by(Measurement.station).order_by(desc(func.count(Measurement.station))).all()

# print results
for station in active_stations:
    print(f"Station ID {station[2]} ({station[0]}): {station[1]}")
# Using the most active station id from the previous query, calculate the lowest, highest, and average temperature.
most_active_station = active_stations[0]
most_active_station_stats = session.query(func.min(Measurement.tobs), func.max(Measurement.tobs), func.avg(Measurement.tobs)).\
    filter(Station.station==Measurement.station).filter(Station.id == most_active_station[2]).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
year_most_active_station = session.query(Measurement.tobs).\
    filter(Station.station==Measurement.station).filter(Station.id == most_active_station[2]).\
    filter(Measurement.date>=year_prior_string).all()
# Flatten tuples
flattened_temps = [x[0] for x in year_most_active_station]
# Plot histogram
plt.hist(flattened_temps, bins=12)
plt.title(f"Temperature Observation Data\nfor Station ID {most_active_station[2]}")
plt.xlabel("Temperature")
plt.ylabel("Frequency")
plt.savefig("station_analysis.png")
plt.show()<jupyter_output><empty_output><jupyter_text>Close session<jupyter_code># Close Session
session.close()<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.10275533457634141, 'fraction_numerical': 0.004972032318210068, 'mean_word_length': 6.914754098360656, 'pattern_counts': {'":': 2, '<': 25, '<?xml version=': 0, '>': 27, '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': '6', 'dir': 'jupyter-structured-clean-dedup', 'id': '285666', 'license': 'ADSL', 'n_tokens_mistral': 1516, 'n_tokens_neox': 1455, 'n_words': 406, 'path': 'climate_analysis.ipynb', 'repo_name': 'Connelito/SQLAlchemy-challenge', 'size': '4827'}