<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
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># Finding the last date
last_date = session.query(Measurement.date).order_by(Measurement.date.desc()).first()
print(last_date)

# Design a query to retrieve the last 12 months of precipitation data and plot the results
# Calculate the date 1 year ago from the last data point in the database
precipitation_scores = session.query(Measurement.date, Measurement.prcp).\
                        filter(Measurement.date <= '2017-08-23').filter(Measurement.date >= '2016-08-23')

# Save the query results as a Pandas DataFrame and set the index to the date column
# Sort the dataframe by date
df = pd.DataFrame(precipitation_scores, columns=['date','prcp'])\
                .set_index('date').sort_values(by='date',ascending=True)
df.head()
# Use Pandas Plotting with Matplotlib to plot the data
plt.figure(figsize=(10,5))
plt.bar(df.index,df['prcp'],width=2,label="Precipitation")
plt.legend(loc='upper center')
plt.xticks([])
plt.xlabel("Date")
plt.tight_layout()
plt.show()
# Use Pandas to calcualte the summary statistics for the precipitation data
prcp_stats = session.query(Measurement.prcp).\
                        filter(Measurement.date <= '2017-08-23').filter(Measurement.date >= '2016-08-23')
df_prcp_stats = pd.DataFrame(prcp_stats)
df_prcp_stats.describe()
# Design a query to show how many stations are available in this dataset?
num_station = session.query(Station).group_by(Station.station).count()
print(num_station)
# What are the most active stations? (i.e. what stations have the most rows)?
# List the stations and the counts in descending order.
station_freq = session.query(Measurement.station,func.count(Measurement.station)).group_by(Measurement.station,).\
                    order_by(func.count(Measurement.station).desc())
for row in station_freq:
    print(row)
# Using the station id from the previous query, calculate the lowest temperature recorded, 
# highest temperature recorded, and average temperature most active station?
session.query(func.min(Measurement.tobs),func.max(Measurement.tobs),func.avg(Measurement.tobs)).\
                            filter(Measurement.station == station_freq[0][0]).all()
# 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
highest_temp = session.query(Measurement.station, Measurement.tobs).\
                            filter(Measurement.date <= '2017-08-23').\
                            filter(Measurement.date >= '2016-08-23').\
                            filter(Measurement.station == station_freq[0][0]).all()
df_hist = pd.DataFrame(highest_temp).set_index('station')
df_hist.head()
# plot the results as a histogram

df_hist.plot.hist(by='station',bins=12)
plt.show()
;<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.10625345877144439, 'fraction_numerical': 0.017432208079690092, 'mean_word_length': 4.460725075528701, 'pattern_counts': {'":': 0, '<': 12, '<?xml version=': 0, '>': 12, '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': '217628', 'license': 'ADSL', 'n_tokens_mistral': 1113, 'n_tokens_neox': 1026, 'n_words': 304, 'path': 'Climate_Homework.ipynb', 'repo_name': 'imanuelcg/sqlalchemy-challenge', 'size': '3614'}