<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
engine = create_engine("sqlite:///../../../working/10-Advanced-Data-Storage-and-Retrieval/Homework/Instructions/Resources/hawaii.sqlite")
Base = automap_base()
#Reflect existing database into new model
Base.prepare(engine, reflect=True)
#Print all classes mapped to Base
Base.classes.keys()
#Inspecting Database
inspector = inspect(engine)
inspector.get_table_names()
#Save references to each table
Measurement = Base.classes.measurement
Station = Base.classes.station
# Create a session
session = Session(engine)
#Create a connection for our dataframe
conn = engine.connect()
# Query all records in the the Database
measurement_df = pd.read_sql("SELECT * FROM measurement", conn)
measurement_df
# Using the inspector to print the column names within the 'measurement' table and each column's types
columns = inspector.get_columns('measurement')
for column in columns:
    print(column["name"], column["type"])<jupyter_output>id INTEGER
station TEXT
date TEXT
prcp FLOAT
tobs FLOAT<jupyter_text>Precipitation Analysis<jupyter_code>#Finding most recent data
session.query(Measurement.date).order_by(Measurement.date.desc()).first()
#Getting precipitation for given months (last year)
year_precip = session.query(Measurement.date, Measurement.prcp).filter(Measurement.date < '2017-08-23').filter(Measurement.date >= '2016-08-23').all()
year_precip
#Creating new dataframe from our query
new_df = pd.DataFrame(year_precip)
new_df['date'] = pd.to_datetime(new_df['date'])

#Dropping any null values
new_df = new_df.dropna()

#Sorting by date
new_df = new_df.sort_values(by = 'date')

#Setting date as our index
new_df = new_df.set_index('date')
new_df
#Graphing our dataframe
prcp_graph = new_df.plot(rot = 90)
plt.xlabel("Date")
plt.ylabel("Inches")
prcp_graph
#Printing summary statistics 
summary_stats = new_df.describe()
summary_stats<jupyter_output><empty_output><jupyter_text>Station Analysis<jupyter_code>#Creating dataframe from station table
station_df = pd.read_sql("SELECT * FROM station", conn)
station_df
#Calculating total number of stations in dataset
num_of_stations = session.query(Station).filter(Station.id).count()
print(num_of_stations)
#Finding most active stations (which stations have the most rows?)
stations = session.query(Measurement.station, func.count(Measurement.station)).group_by(Measurement.station).order_by(func.count(Measurement.station).desc()).all()
stations
#Which station has highest number of observations?
most_active_id = stations[0][0]
most_active_id
#Most active station lowest temp
lowest_temp = session.query(func.min(Measurement.tobs)).filter_by(station = most_active_id).all()
lowest_temp
#Most active station highest temp
highest_temp = session.query(func.max(Measurement.tobs)).filter_by(station = most_active_id).all()
highest_temp
#Most active station avg temp
avg_temp = session.query(func.avg(Measurement.tobs)).filter_by(station = most_active_id).all()
avg_temp
#Changing date column to a datetime column
measurement_df['date'] = pd.to_datetime(measurement_df['date'])

#Finding our most recent date
start_date = measurement_df['date'].max()
end_date = start_date - dt.timedelta(days=365)

#Putting in correct format
start_date = start_date.strftime("%Y-%m-%d")
end_date = end_date.strftime("%Y-%m-%d")
print(start_date)
print(end_date)
#Getting temperatures for the last 12 months
last_year_temp = session.query(Measurement.date, Measurement.tobs).filter(Measurement.date < start_date).filter(Measurement.date >= end_date).filter_by(station = most_active_id).all()
last_year_temp
#Putting data from our query in a dataframe
date_tobs_df = pd.DataFrame(last_year_temp)
date_tobs_df.head()
#Plotting histogram
date_tobs_hist = date_tobs_df.hist(bins = 12)
plt.xlabel("Temperature")
plt.ylabel("Frequency")
date_tobs_hist
#Close the session
session.close()<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.10888942423512407, 'fraction_numerical': 0.006986268369067694, 'mean_word_length': 7.205533596837944, 'pattern_counts': {'":': 0, '<': 14, '<?xml version=': 0, '>': 14, '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': '3', 'dir': 'jupyter-structured-clean-dedup', 'id': '568559', 'license': 'ADSL', 'n_tokens_mistral': 1380, 'n_tokens_neox': 1315, 'n_words': 325, 'path': 'sqlalchemy_challenge.ipynb', 'repo_name': 'carolinelarry/sqlalchemy_hw', 'size': '4151'}