# ---
# 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 matplotlib.ticker as mtick 

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

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

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

engine.execute('select * from measurement limit 5').fetchall()

# Create our session (link) from Python to the DB
session = Session(engine)


# # Exploratory Precipitation Analysis

#Total dates present
session.query(func.count(measurement.date)).all()


# Find the most recent date in the data set.
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. 

#query_date
# Calculate the date one year from the last date in data set.
#The strptime() method creates a datetime object from the given string.
cdate=dt.datetime.strptime(recent_date.date,  "%Y-%m-%d")
query_date=(cdate - dt.timedelta(days=365))
query_date

# +

# Perform a query to retrieve the data and precipitation scores
#month=func.date_trunc('month', measurement.date)
result=session.query(measurement.date, measurement.prcp ).\
    filter(measurement.date > query_date).\
    group_by(measurement.date).all()

# Save the query results as a Pandas DataFrame and set the index to the date column
precip_df=pd.DataFrame(result, columns=['Date', 'Precipitation'])
precip_df.set_index('Date', inplace=True)

# Sort the dataframe by date
precip_df=precip_df.sort_values(by=['Date'])
precip_df.head(10)



# -

# Use Pandas Plotting with Matplotlib to plot the data
x = precip_df.plot(kind='bar', width=4, figsize=(12,14))
plt.locator_params(axis='x', nbins=8)
ax.grid(True)
plt.legend(fontsize="12")
plt.title("Precipitation in last 12 Months")
plt.ylabel("Precipitation in Inches")
plt.xlabel("Date", size=12)
plt.xticks(rotation=65)
plt.yticks(np.arange(0, 3, 0.25))
plt.savefig("output/Precipitation.png")
plt.show()

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

precip_df.describe()
# -

# # Exploratory Station Analysis

#Station data from station
columns = inspector.get_columns('station')
for c in columns:
    print(c['name'], c["type"])
print()    
print("Count of stations from station table: ")
session.query(func.count(station.id)).all()

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

session.query(func.count(func.distinct(measurement.station))).all()
# -

# 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.
# sele = [station.station, station.name, measurement.station]
same_station=session.query(measurement.station,\
    func.count(measurement.station)).\
    group_by(measurement.station).\
    order_by(func.count(measurement.station).desc()).all()
same_station


# Using the most active station id from the previous query, calculate the lowest, highest, and average temperature.
#Assuming that the temperature in this data is tobs
most_active=same_station[0][0]
#session.query(func.max(most_active).scalar())
#Maximum
session.query(measurement.station, measurement.tobs ).\
    filter(measurement.station == most_active).\
    order_by(measurement.tobs.desc()).\
    first()
max_tobs= session.query(func.min(measurement.tobs),\
    func.max(measurement.tobs),
    func.avg(measurement.tobs)).\
    filter(measurement.station == most_active).all()
max_tobs   
print(f'Minimum temperature : {max_tobs[0][0]}')
print(f'Maximum temperature : {max_tobs[0][1]}')
print(f'Average temperature : {max_tobs[0][2]:.2f}')


# 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
query_res=session.query(measurement.date, measurement.tobs ).\
    filter(measurement.station == most_active).\
    filter(measurement.date >= query_date).\
    order_by(measurement.date).\
    all()
df=pd.DataFrame(query_res, columns=['Date', 'Temperature'])   
df.set_index('Date', inplace=True)
df.head(10)

#Histogram
plt.hist(df['Temperature'], 10, label="Temperature", color='cyan', edgecolor="k")
plt.locator_params(axis='x', nbins=12)
plt.xlabel("Temperature")
plt.ylabel("Frequency")
plt.xticks(rotation=65)
plt.legend() 
plt.show()

#
# # Close session

# Close Session
session.close()



StarcoderdataJupyterScriptsDedupFiltered