<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
engine = create_engine(f"sqlite:///Resources/hawaii.sqlite")
conn = engine.connect()
# reflect an existing database into a new model
Base = automap_base()
# reflect the tables
# Use the Base class to reflect the database tables
Base.prepare(engine, reflect=True)
from sqlalchemy import create_engine
# We can view all of the classes that automap found
# Print all of the classes mapped to the Base
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
from sqlalchemy.orm import Session
session = Session(bind=engine)
inspector = inspect(engine)
#I am going to Hawaii June 26, 2017 to July 10 for 15 days<jupyter_output><empty_output><jupyter_text>Exploratory Climate Analysis<jupyter_code># Design a query to retrieve the last 12 months of precipitation data and plot the results

# Calculate the date 1 year ago from the last data point in the database

most_current_date = session.query(Measurement.date).order_by(Measurement.date.desc()).first()
most_current_date = str(most_current_date)[2:-3]

print(most_current_date)
# Design a query to retrieve the last 12 months of precipitation data and plot the results

meas_columns = inspector.get_columns('measurement')
for column in meas_columns:
    print(column["name"], column["type"])
# Perform a query to retrieve the data and precipitation scores

meas_query = engine.execute('SELECT date , prcp FROM measurement')
# meas_query


# Calculate the date 1 year ago from today
   ### data provided does not allow for this ###
lastDate = session.query(func.Max(Measurement.date)).all()[0][0]
prevYear = dt.datetime.strptime(lastDate,'%Y-%m-%d') - dt.timedelta(366)

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

# # Save the query results as a Pandas DataFrame and set the index to the date column
df = pd.DataFrame(results).set_index('date')
df.head()
# Use Pandas Plotting with Matplotlib to plot the data
df = pd.DataFrame(results).set_index('date').plot()
fig = plt.gcf()
fig.set_size_inches(20, 3, forward=True)
plt.figure(figsize=(20, 3))
plt.xticks([])
plt.xticks(rotation=90)
plt.xlabel('')
plt.ylabel('Precipitaion (Inches)')
plt.tight_layout()
plt.title("Climate Analysis")
plt.show()
plt.savefig('Measurement One Year.png', dpi=100)

results = session.query(Measurement.date, Measurement.prcp).filter(Measurement.date >= prevYear).all()

most_current_date = session.query(Measurement.date).order_by(Measurement.date.desc()).first()

# Use Pandas to calcualte the summary statistics for the precipitation data
# Statistical analysis

from scipy import stats
from scipy import mean
df = pd.DataFrame(results).set_index('date').describe()
df.count()

# What are the most active stations?
# List the stations and the counts in descending order.
active_stations = [Measurement.station, 
       func.count(Measurement.station)]
activestations_query = session.query(*active_stations).\
    group_by(Measurement.station).\
    order_by(desc(func.count(Measurement.station))).all()
activestations_query
# stat_columns = inspector.get_columns('station')
# for column in stat_columns:
#     print(column["name"], column["type"])
    
stat_query = engine.execute('SELECT COUNT(station) FROM station').fetchall()
station_count = stat_query[0][0]

print(f'There are {station_count} stations in the dataset.')

date_prcp_df['date']  = pd.to_datetime(date_prcp_df['date'])
# Design a query to show how many stations are available in this dataset?
lastDate = session.query(func.Max(Stations.date)).all()[0][0]
prevYear = dt.datetime.strptime(lastDate,'%Y-%m-%d') - dt.timedelta(366)

# Perform a query to retrieve the data and precipitation scores
results = session.query(Measurement.date, Measurement.prcp).filter(Measu
# Perform a query to retrieve the data and precipitation scores

stat_query = engine.execute('SELECT date , prcp FROM measurement')
# meas_query

# Save the query results as a Pandas DataFrame and set the index to the date column
# Calculate the date 1 year ago from today
   ### data provided does not allow for this ###
lastDate = session.query(func.Max(Measurement.date)).all()[0][0]
prevYear = dt.datetime.strptime(lastDate,'%Y-%m-%d') - dt.timedelta(366)

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

# # Save the query results as a Pandas DataFrame and set the index to the date column
df = pd.DataFrame(results).set_index('date')
df.head()
   ### trying mask ###
measurement_df = pd.DataFrame(meas_query)
measurement_df = measurement_df.rename(columns={0: 'date', 1: 'prcp'})
measurement_df['date'] =  pd.to_datetime(measurement_df['date'], format='%Y-%m-%d')
measurement_df.head()
mask = (measurement_df['date'] > '2016-08-23') & (measurement_df['date'] <= '2017-08-23')
measurement_df_1year = measurement_df.loc[mask]
measurement_df_1year = measurement_df_1year.set_index(['date'])

# Sort the dataframe by date
measurement_df_1year.sort_index()
measurement_df_1year.head()
# measurement_df_1year.count()
# Summary statistics for the percipitation df. 
results = session.query(Measurement.date, Measurement.prcp).filter(Measurement.date >= prevYear).all()

date_prcp_df['date']  = pd.to_datetime(date_prcp_df['date'])
# What are the most active stations?
# List the stations and the counts in descending order.
actv_stations = [Measurement.station, 
       func.count(Measurement.station)]
activestations_query = session.query(*active_stations).\
    group_by(Measurement.station).\
    order_by(desc(func.count(Measurement.station))).all()
activestations_query
# What are the most active stations? (i.e. what stations have the most rows)?
# List the stations and the counts in descending order.
# Calculate the date 1 year ago from today
   ### data provided does not allow for this ###
lastDate = session.query(func.Max(Measurement.date)).all()[0][0]
prevYear = dt.datetime.strptime(lastDate,'%Y-%m-%d') - dt.timedelta(366)

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

# # Save the query results as a Pandas DataFrame and set the index to the date column
df = pd.DataFrame(results).set_index('date')
df.head()
# Using the station id from the previous query, calculate the lowest temperature recorded, 
# highest temperature recorded, and average temperature of the most active station?
station_boolean = session.query(Measurement.station).distinct().count()== session.query(Station.station).distinct().count()
if station_boolean:
    num_station = session.query(Measurement.station).distinct().count()
    print(f'The number of stations = {num_station}')
else:
    print('your database is not NSYNC')
# Choose the station with the highest number of temperature observations.
# Query the last 12 months of temperature observation data for this station and plot the results as a histogram
temp_obsv = engine.execute('select station, count(station) from measurement group by station order by count(station) desc').fetchall()
temp_obsv<jupyter_output><empty_output><jupyter_text>Bonus Challenge Assignment<jupyter_code># This function called `calc_temps` will accept start date and end date in the format '%Y-%m-%d' 
# and return the minimum, average, and maximum temperatures for that range of dates
def calc_temps(start_date, end_date):
    """TMIN, TAVG, and TMAX for a list of dates.
    
    Args:
        start_date (string): A date string in the format %Y-%m-%d
        end_date (string): A date string in the format %Y-%m-%d
        
    Returns:
        TMIN, TAVE, and TMAX
    """
    
    return session.query(func.min(Measurement.tobs), func.avg(Measurement.tobs), func.max(Measurement.tobs)).\
        filter(Measurement.date >= start_date).filter(Measurement.date <= end_date).all()

# function usage example
print(calc_temps('2012-02-28', '2012-03-05'))
# Use your previous function `calc_temps` to calculate the tmin, tavg, and tmax 
# for your trip using the previous year's data for those same dates.

# Plot the results from your previous query as a bar chart. 
# Use "Trip Avg Temp" as your Title
# Use the average temperature for the y value
# Use the peak-to-peak (tmax-tmin) value as the y error bar (yerr)

# Calculate the total amount of rainfall per weather station for your trip dates using the previous year's matching dates.
# Sort this in descending order by precipitation amount and list the station, name, latitude, longitude, and elevation


# Create a query that will calculate the daily normals 
# (i.e. the averages for tmin, tmax, and tavg for all historic data matching a specific month and day)

def daily_normals(date):
    """Daily Normals.
    
    Args:
        date (str): A date string in the format '%m-%d'
        
    Returns:
        A list of tuples containing the daily normals, tmin, tavg, and tmax
    
    """
    
    sel = [func.min(Measurement.tobs), func.avg(Measurement.tobs), func.max(Measurement.tobs)]
    return session.query(*sel).filter(func.strftime("%m-%d", Measurement.date) == date).all()
    
daily_normals("01-01")
# calculate the daily normals for your trip
# push each tuple of calculations into a list called `normals`

# Set the start and end date of the trip

# Use the start and end date to create a range of dates

# Stip off the year and save a list of %m-%d strings

# Loop through the list of %m-%d strings and calculate the normals for each date

# Load the previous query results into a Pandas DataFrame and add the `trip_dates` range as the `date` index

# Plot the daily normals as an area plot with `stacked=False`<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.1055943368400354, 'fraction_numerical': 0.009733556189165275, 'mean_word_length': 5.04756242568371, 'pattern_counts': {'":': 0, '<': 15, '<?xml version=': 0, '>': 20, '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': '210014', 'license': 'ADSL', 'n_tokens_mistral': 3142, 'n_tokens_neox': 3000, 'n_words': 1030, 'path': 'Instructions/climate.ipynb', 'repo_name': 'satoran1/sqlalchemy-challenge', 'size': '10171'}