<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, inspect
# create engine to hawaii.sqlite
source ="sqlite:///Resources/hawaii.sqlite"
engine = create_engine(source)

# reflect the tables
# reflect an existing database into a new model
Base = automap_base()
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.asc()).all()[-1][0]
#recent_date = session.query(Measurement.date).order_by(Measurement.date.asc()).all()
print(f"The most recent date in the data set is {recent_date}.\n")
# 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.
# Perform a query to retrieve the data and precipitation scores
twelve_months_precipitation = session.query(Measurement.date, Measurement.prcp).\
    filter(Measurement.date >= (dt.datetime.strptime(recent_date,"%Y-%m-%d")-dt.timedelta(days=365))).all()
#twelve_months_precipitation
# Save the query results as a Pandas DataFrame and set the index to the date column
# Sort the dataframe by date
prcp_df = pd.DataFrame(twelve_months_precipitation,columns=["Date","Precipitation"]).sort_values("Date",ascending=False).set_index("Date").dropna()
prcp_df.head()
# Use Pandas Plotting with Matplotlib to plot the data
myplot=prcp_df.plot.line(rot=20,figsize=(15,8))
myplot.set_ylabel("Inches")
myplot.set_title("Last 12 Months of Precipitation")
plt.tight_layout()
plt.savefig("Last_12_Months_of_Precipitation.png")
plt.show()
# Use Pandas to calcualte the summary statistics for the precipitation data
prcp_summary_df=prcp_df.describe()['Precipitation'].reset_index().rename(columns={"index":"Summary Statistics"}).set_index("Summary Statistics")
prcp_summary_df<jupyter_output><empty_output><jupyter_text>Exploratory Station Analysis<jupyter_code># Design a query to calculate the total number stations in the dataset
num_stations = session.query(Measurement.station).distinct(Measurement.station).count()
#list(np.ravel(num_stations))

print(f"The total number of stations is {num_stations}.\n")
# 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.
most_active_stations=session.query(func.count(Measurement.station), Measurement.station, Station.name).\
    filter(Measurement.station == Station.station).\
    group_by(Measurement.station).\
    order_by(func.count(Measurement.station).desc())
print(f"(station points of data count, station id): {most_active_stations.all()}\n")
mas=most_active_stations.first()[1:]
#print(f"Most active weather station is {mas}.\n")
print(f"The most active weather station is {mas} with {most_active_stations.first()[0]} points of data.")
# Using the most active station id from the previous query, calculate the lowest, highest, and average temperature.
lowest_temp= session.query(func.min(Measurement.tobs)).filter(Measurement.station == mas[0])[0]
print(f"The lowest temperature for the most active weather station, {mas}, is {lowest_temp[0]} degrees F.")
highest_temp = session.query(func.max(Measurement.tobs)).filter(Measurement.station == mas[0])[0]
print(f"The highest temperature for the most active weather station, {mas}, is {highest_temp[0]} degrees F.")

all_temps=session.query(Measurement.tobs).filter(Measurement.station==mas[0])
all_real_temps=[]
for temp in all_temps:
    if type(temp[0]) == float:
        all_real_temps.append(temp[0])

avg_temp = round(mean(all_real_temps),2)
print(f"The average temperature for the most active weather station, {mas}, is {avg_temp} degrees 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
twelve_months_temperatures = session.query(Measurement.date, Measurement.tobs).\
    filter(Measurement.date > (dt.datetime.strptime(recent_date,"%Y-%m-%d")-dt.timedelta(days=365))).\
    filter(Measurement.station==mas[0]).all()
temps_df = pd.DataFrame(twelve_months_temperatures,columns=["Date","Temperatures"]).sort_values("Date",ascending=False).set_index("Date").dropna()
temps_df.head()
thist=temps_df.plot.hist(rot=20,figsize=(15,8),bins=12) #"12 Months of Temperatures"
thist.set_title("12 Months of Temperatures")
thist.set_xlabel("Temperature (Fahrenheit)")
plt.tight_layout()
plt.savefig("Last_12_Months_of_Temperatures.png")
plt.show()<jupyter_output><empty_output><jupyter_text>Close session<jupyter_code># Close Session
session.close()
engine.dispose()<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.11828778903114848, 'fraction_numerical': 0.008981463787502388, 'mean_word_length': 6.870676691729323, 'pattern_counts': {'":': 1, '<': 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': '245833', 'license': 'ADSL', 'n_tokens_mistral': 1710, 'n_tokens_neox': 1626, 'n_words': 431, 'path': 'climate_starter.ipynb', 'repo_name': 'georgiafbi/sqlalchemy-challenge', 'size': '5233'}