<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
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)
# We can 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 Climate Analysis<jupyter_code># Design a query to retrieve the last 12 months of precipitation data and plot the results
latest_date = (session.query(Measurement.date)
                     .order_by(Measurement.date.desc())
                     .first())

latest_date = list(np.ravel(latest_date))[0]
latest_date = dt.datetime.strptime(latest_date, '%Y-%m-%d')
latest_year = int(dt.datetime.strftime(latest_date, '%Y'))
latest_month = int(dt.datetime.strftime(latest_date, '%m'))
latest_day = int(dt.datetime.strftime(latest_date, '%d'))

# Calculate the date 1 year ago from the last data point in the database
year_ago = dt.date(latest_year, latest_month, latest_day) - dt.timedelta(days=365)

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

# Save the query results as a Pandas DataFrame and set the index to the date column
precipitation_df = pd.DataFrame(precipitation_data)
precipitation_df = precipitation_df.set_index('date')

# Sort the dataframe by date
precipitation_df = precipitation_df.sort_index(ascending=True)

# Use Pandas Plotting with Matplotlib to plot the data
precipitation_df.plot(title="Precipitation", figsize=(12,6))
plt.xlabel("Date")
plt.xticks(rotation="45")
plt.show()
# Use Pandas to calcualte the summary statistics for the precipitation data
precipitation_df.describe()
# Design a query to show how many stations are available in this dataset?
station_count = session.query(Station).count()
print(f"Station Count: {station_count}")
# What are the most active stations? (i.e. what stations have the most rows)?
# List the stations and the counts in descending order.

station_counts = (session.query(Measurement.station, func.count(Measurement.station))
                        .group_by(Measurement.station)
                        .order_by(func.count(Measurement.station).desc())
                        .all())

station = station_counts[0][0]

station_name = (session.query(Station.name)
                      .filter_by(station = station))
station_name = station_name[0][0]
print("Most active stations:")

station_counts
# Using the station id from the previous query, calculate the lowest temperature recorded, 
# highest temperature recorded, and average temperature most active station?

lowest_temp = (session.query(Measurement.tobs)
                     .filter(Measurement.station == station)
                     .order_by(Measurement.tobs.asc())
                     .first())
lowest_temp = lowest_temp[0]

highest_temp = (session.query(Measurement.tobs)
                      .filter(Measurement.station == station)
                      .order_by(Measurement.tobs.desc())
                      .first())
highest_temp = highest_temp[0]

average_temp = (session.query(func.avg(Measurement.tobs))
                  .filter(Measurement.station == station))
average_temp = '{0:.3}'.format(average_temp[0][0])

print(f"Lowest Temperature: {lowest_temp}")
print(f"Highest Temperature: {highest_temp}")
print(f"Average Temperature: {average_temp}")
# Choose the station with the highest number of temperature observations.
# Query the last 12 months of temperature observation data for this station and plot the results as a histogram

#Perform query temperature data
temperature = (session.query(Measurement.date, Measurement.tobs)
                   .filter(Measurement.date > year_ago)
                   .filter(Measurement.station == station)
                   .order_by(Measurement.date)
                   .all())

#Convert query object to data frame
temp_df = pd.DataFrame(temperature)
# Set index
temp_df = temp_df.set_index('date')
temp_df = temp_df.sort_index(ascending=True)

temp_df.plot(kind='hist', bins=12)
plt.show()<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.09888936351986331, 'fraction_numerical': 0.005553182400683468, 'mean_word_length': 3.8378099173553717, 'pattern_counts': {'":': 0, '<': 9, '<?xml version=': 0, '>': 11, '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': '2', 'dir': 'jupyter-structured-clean-dedup', 'id': '656956', 'license': 'ADSL', 'n_tokens_mistral': 1408, 'n_tokens_neox': 1326, 'n_words': 370, 'path': 'climate_starter.ipynb', 'repo_name': 'mmacadangdang/sqlalchemy-challenge', 'size': '4682'}