# ---
# jupyter:
#   jupytext:
#     text_representation:
#       extension: .py
#       format_name: light
#       format_version: '1.5'
#       jupytext_version: 1.14.4
#   kernel_info:
#     name: python3
#   kernelspec:
#     display_name: Python 3
#     language: python
#     name: python3
# ---

# %matplotlib inline
from matplotlib import style
style.use('fivethirtyeight')
import matplotlib.pyplot as plt

import numpy as np
import pandas as pd
import datetime as dt

# # Reflect Tables into SQLAlchemy ORM

# 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:///./Resources/hawaii.sqlite")

# +
# reflect an existing database into a new model

Base = automap_base()
# inspector = inspect(engine)

# reflect the tables
Base.prepare(engine, reflect=True)

# +
# View all of the classes that automap found

Base.classes.keys()

# +
# columns = inspector.get_columns('measurement')
# for c in columns:
#     print(c['name'], c["type"])

# columns = inspector.get_columns('station')
# for c in columns:
#     print(c['name'], c["type"])

# +
# 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)

# # Exploratory Precipitation Analysis

# +
import pandas as pd 

measurement_df = pd.DataFrame(session.query(Measurement.id, Measurement.station, Measurement.date, Measurement.prcp, Measurement.tobs).all())
print(measurement_df)

station_df = pd.DataFrame(session.query(Station.id, Station.station, Station.name, Station.latitude, Station.longitude,Station.elevation).all())
print(station_df)

measurement_df[measurement_df['date'] == '2016-08-23']

# +
# Find the most recent date in the data set.

most_recent_date = session.query(func.max(Measurement.date)).one()
most_recent_date = most_recent_date[0]
print(most_recent_date)

# +
import datetime
from dateutil.relativedelta import relativedelta
from datetime import datetime
# 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. 

most_recent_date

# Calculate the date one year from the last date in data set.

last_year_date = (datetime.strptime(most_recent_date, "%Y-%m-%d") + relativedelta(years=-1)).date()
# print(last_year_date)

# # Perform a query to retrieve the data and precipitation scores

data_between_dates = session.query(Measurement.date, Measurement.prcp).\
    filter(Measurement.date <= most_recent_date).\
    filter(Measurement.date >= last_year_date).\
    order_by(Measurement.date).all()

# # Save the query results as a Pandas DataFrame and set the index to the date column

data_between_dates_df = pd.DataFrame(data_between_dates).dropna()
data_between_dates_df.set_index('date', inplace=True)

# # Sort the dataframe by date

data_between_dates_df = data_between_dates_df.sort_values('date')
data_between_dates_df

# Use Pandas Plotting with Matplotlib to plot the data

import matplotlib.pyplot as plt
import matplotlib
import numpy as np

data_between_dates_df.plot(rot=90,figsize=(11,6),color='deepskyblue')
plt.show()

# +
# Use Pandas to calcualte the summary statistics for the precipitation data

data_between_dates_df.describe()
# -

# # Exploratory Station Analysis

# +
# Design a query to calculate the total number stations in the dataset

session.query(Station).count()

# +
# 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.

from sqlalchemy import desc

station_list = session.query(Measurement.station,func.count(Measurement.station).label('station count')).\
                group_by(Measurement.station).\
                order_by(desc('station count')).all()
station_list

station_list_df = pd.DataFrame(station_list, columns=['station', 'count'])

station_list_df

# +
# Using the most active station id from the previous query, calculate the lowest, highest, and average temperature.

most_active_station_id = station_list[0][0]
print(f'Most Active Station Id: {most_active_station_id}')

temp_stats = session.query(func.min(Measurement.tobs), func.max(Measurement.tobs), func.avg(Measurement.tobs)).\
            filter(Measurement.station == most_active_station_id).all()

print(f'Lowest temp. for {most_active_station_id}: {temp_stats[0][0]}')
print(f'Highest temp. for {most_active_station_id}: {temp_stats[0][1]}')
print(f'Average temp. for {most_active_station_id}: {round(temp_stats[0][2],1)}')


# +
# 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

temp_observation_data = session.query(Measurement.tobs).\
    filter(Measurement.station == most_active_station_id).\
    filter(Measurement.date <= most_recent_date).\
    filter(Measurement.date >= last_year_date).order_by(Measurement.date)

temp_observation_data_df = pd.DataFrame(temp_observation_data)
temp_observation_data_df

temp_observation_data_df.plot.hist(bins=12, figsize=(7,5),color='deepskyblue')
plt.tight_layout()
plt.legend()
plt.ylabel('Frequency',fontsize="13")
plt.xlabel('Temperature',fontsize="13")
plt.title(f'Temperature Statistics for {most_active_station_id}',fontsize="13")

plt.show()

# -

# # Close session

# Close Session
session.close()



StarcoderdataJupyterScriptsDedupFiltered