# ---
# 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 [conda env:Pythondata] *
#     language: python
#     name: conda-env-Pythondata-py
# ---

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

# create engine to hawaii.sqlite
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)


# -

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

# # Exploratory Precipitation Analysis

# Find the most recent date in the data set.
session.query(Measurement.date).order_by(Measurement.date.desc()).first()

# +
# 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.
year = dt.date(2017, 8, 23) - dt.timedelta(days=365)

# Perform a query to retrieve the data and precipitation scores
prcp_year = session.query(Measurement.date, Measurement.prcp).\
    filter(Measurement.date >= year).all()


# Save the query results as a Pandas DataFrame and set the index to the date column
prcp_year_df = pd.DataFrame(prcp_year, columns = ["Date", "Precipitation"])
clean_prcp_year = prcp_year_df.dropna()
#prcp_year_df = prcp_year_df.set_index("Date")

# Sort the dataframe by date
clean_prcp_year.sort_values(by=["Date"])
clean_prcp_year.dtypes

# +
 
# Use Pandas Plotting with Matplotlib to plot the data

clean_prcp_year.plot(x = "Date", y = 'Precipitation', rot=45)
plt.xlabel('Date')
plt.ylabel('Precipitation')
plt.title('Yearly Rainfall')

# -

# Use Pandas to calcualte the summary statistics for the precipitation data
clean_prcp_year.describe()


# # Exploratory Station Analysis

# Design a query to calculate the total number stations in the dataset
session.query(Measurement).group_by(Measurement.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.
station_count = session.query(Measurement.station, func.count(Measurement.id)).group_by(Measurement.station).order_by(func.count(Measurement.id).desc()).all()
station_count

# +
# Using the most active station id from the previous query, calculate the lowest, highest, and average temperature.
station_obs = session.query(Measurement.station, func.min(Measurement.tobs), func.max(Measurement.tobs), func.avg(Measurement.tobs)).\
        filter(Measurement.station == 'USC00519281').all()

station_obs
# -

# 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
active_station = session.query(Measurement.station, Measurement.date, Measurement.tobs).\
        filter(Measurement.station == 'USC00519281').\
        filter(Measurement.date >= year).\
        order_by(Measurement.date).all()
active_station

#make into df
active_station = pd.DataFrame(active_station, columns = ["Station", "Date", "Temperature"])
active_station.describe()

# Query the last 12 months of temperature observation data for this station and plot the results as a histogram
active_station['Temperature'].plot(kind='hist',
                  title = "Temperature Observation data",
                  fontsize=15,
                  color= 'b',
                  legend=True)
plt.xlabel("Temperature")
plt.ylabel("Frequency")
plt.show()

# # Close session

# Close Session
session.close()



StarcoderdataJupyterScriptsDedupFiltered