<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:///hawaii.sqlite")
# reflect an existing database into a new model
Base = automap_base()
# reflect the tables into classes
Base.prepare(engine, reflect = True)
# View all of the classes that automap found
Base.classes.keys()
# Building objects--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># In measurement table, find the most recent date in the data set.
# View columns and datatype in measurement table

from sqlalchemy import inspect,func
inspector = inspect(engine)
columns = inspector.get_columns('measurement')
for c in columns:
    print(c['name'], c["type"])
# View the first ten rows in table measurement
engine.execute('SELECT * FROM measurement LIMIT 10').fetchall()
# get the first row
for row in session.query(Measurement, Measurement.date).limit(1).all():
    print(row)
# Get the most recent date
recent_date = session.query(Measurement.date).order_by(Measurement.date.desc()).first()
recent_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. 
recent_date = session.query(Measurement.date).order_by(Measurement.date.desc()).first()
recent_date
# The earliest date
earliest_date = session.query(Measurement.date).order_by(Measurement.date).first()
earliest_date
# Calculate the date 1 year ago from the last data point in the measurement table

year_ago = dt.datetime(2017, 8, 23) - dt.timedelta(days=366)
print(year_ago)
# Perform a query to retrieve the data and precipitation scores
results=session.query(Measurement.date, Measurement.prcp).order_by(Measurement.date.asc()).\
        filter(Measurement.date>=year_ago).all()

# Save the query results as a Pandas DataFrame and set the index to the date column
df_results=pd.DataFrame(results)
df_results=df_results.rename(columns={"prcp":"precipitation"})

# Sort the dataframe by date
df_results.sort_values(by=['date'], ascending=True)
df_results=df_results.dropna()
df_results=df_results.reset_index(drop=True)

# Use Pandas Plotting with Matplotlib to plot the data
ax=df_results.plot(color='b',figsize=(12,10),legend=True,linewidth=9.0,grid=True)
#Removing xticks
plt.setp(ax.get_xticklabels(), visible=False)
plt.tight_layout()
plt.xlabel("date")
plt.show()


# Use Pandas to calcualte the summary statistics for the precipitation data
df_results.describe()<jupyter_output><empty_output><jupyter_text>Exploratory Station Analysis<jupyter_code># In station table, find columns and datatype
inspector = inspect(engine)
columns = inspector.get_columns('station')
for c in columns:
    print(c['name'], c["type"])

# View the first five rows in table measurement
engine.execute('SELECT * FROM station LIMIT 5').fetchall()
# Design a query to calculate the total number stations in the dataset
station_count=session.query(func.count(Station.id)).all()
station_count
# Design a station_Measurment_join=session.query(Measurement.station,func.count(Measurement.station)).\
# List the stations and the counts in descending order.
station_Measurment_join=session.query(Measurement.station,func.count(Measurement.station)).\
                       group_by(Measurement.station).\
                       order_by(func.count(Measurement.station).desc()).all()
station_Measurment_join
# Using the most active station id from the previous query, 
# `calculate the lowest, highest, and average temperature.
results_temp=session.query(func.min(Measurement.tobs), func.max(Measurement.tobs), func.avg(Measurement.tobs)).\
             filter(Measurement.station==station_Measurment_join[0][0]).all()
results_temp
# Design a query to retrieve the last 12 months of temperature observation data (TOBS).
# Filter by the station with the highest number of observations.
results_date=session.query(Measurement.date).order_by(Measurement.date.desc()).\
             filter(Measurement.station==station_Measurment_join[0][0]).first()
str_date=list(np.ravel(results_date))[0]
latest_date=dt.datetime.strptime(str_date,"%Y-%m-%d")
year_back=latest_date-dt.timedelta(days=366)

# Query the last 12 months of temperature observation data for this station.

results_temp=session.query(Measurement.tobs).\
             filter(Measurement.station==station_Measurment_join[0][0]).\
             filter(Measurement.date>=year_back).all()
df_results=pd.DataFrame(results_temp)


# Plot the results as a histogram with `bins=12`.

hist = df_results['tobs'].hist(bins=12,figsize=(8,5), color='b', zorder=2)
# Add title
hist.set_title("Histogram for temperatue observation",weight='bold', size=15)
# Add y-axis
hist.set_ylabel("Frequency", labelpad=20, weight='bold', size=12)
plt.show()<jupyter_output><empty_output><jupyter_text>Close session<jupyter_code># Close Session
session.close()<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.10875281743050338, 'fraction_numerical': 0.009391435011269721, 'mean_word_length': 6.015810276679842, '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': '640091', 'license': 'ADSL', 'n_tokens_mistral': 1669, 'n_tokens_neox': 1594, 'n_words': 431, 'path': 'SQLAlchemy_homework.ipynb', 'repo_name': 'Avocadott93/sqlalchemy-challenge', 'size': '5324'}