<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("sqlite:///Resources/hawaii.sqlite")
data = engine.execute("SELECT * FROM Station")
for record in data: 
    print (record)

data.keys()
data = engine.execute("SELECT * FROM Measurement")
for record in data: 
    print (record)

data.keys()
# reflect an existing database into a new model
Base = automap_base()
# reflect the tables
Base.prepare(engine, reflect=True)

# We can view all of the classes that automap found
Base.classes.keys()

# Save references to each table
Measurement = Base.classes.measurement
Measurement
Station = Base.classes.station
Station
# Create our session (link) from Python to the DB
session = Session(engine)<jupyter_output><empty_output><jupyter_text>Exploratory Climate Analysis<jupyter_code>inspector=inspect(engine)
inspector.get_table_names()
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'])
session.query(func.count(Measurement.date)).all()
session.query(Measurement.date).order_by(Measurement.date.desc()).first()
# Calculate the date 1 year ago from the last data point in the database
one_year = dt.date(2017,8,23) - dt.timedelta(days=365)
one_year
# Perform a query to retrieve the data and precipitation scores
latestyear = dt.date(2017,8, 23) - dt.timedelta(days = 365)
precipitation = session.query(Measurement.date, Measurement.prcp).filter(Measurement.date > latestyear).\
order_by(Measurement.date).all()
precipitation[0]

# Design a query to retrieve the last 12 months of precipitation data and plot the results
result = session.query(Measurement.date, Measurement.prcp).filter(Measurement.date >= one_year).all()
result
# Save the query results as a Pandas DataFrame and set the index to the date column
precipitation_df = pd.DataFrame(precipitation)
precipitation_df.head(10)


# Sort the dataframe by date
precipitation_df = precipitation_df.sort_values(0)
precipitation_df.head()
# Use Pandas Plotting with Matplotlib to plot the data
precipitation_df.plot(0, 1)
plt.ylim(0,7)
plt.xlabel("Date")
plt.ylabel("Rain in Inches")
plt.title("Precipitation Analysis")
plt.legend(["Precipitation"])
plt.xticks(rotation=90)
plt.show()
# Use Pandas to calcualte the summary statistics for the precipitation data
precipitation_df.describe()
# Design a query to show how many stations are available in this dataset?
session.query(Measurement.station).group_by(Measurement.station).count()
# What are the most active stations? (i.e. what stations have the most rows)?
# List the stations and the counts in descending order.
active_station=session.query(Measurement.station,func.count(Measurement.tobs)).group_by(Measurement.station).order_by(func.count(Measurement.tobs).desc()).all()
active_station
# Using the station id from the previous query, calculate the lowest temperature recorded, 
# highest temperature recorded, and average temperature of the most active station?
session.query(func.min(Measurement.tobs),func.max(Measurement.tobs),func.avg(Measurement.tobs)).group_by(Measurement.station).filter(Measurement.station=='USC00519281').all()
# Choose the station with the highest number of temperature observations.
most_active_station=active_station[0][0]
print(f"The station with the highest number of temperature observations is: {most_active_station}")

# Query the last 12 months of temperature observation data for this station and plot the results as a histogram
tobs_last12=session.query(Measurement.tobs).filter(Measurement.date > one_year).filter(Measurement.station==most_active_station).all()
tobs_last12
prior_year = dt.date(2017, 8, 23) - dt.timedelta(days=365)
results = session.query(Measurement.tobs).\
    filter(Measurement.station == 'USC00519281').\
    filter(Measurement.date >= prior_year).all()
df = pd.DataFrame(results, columns=['tobs'])
df.plot.hist(bins=12, color = '#9932CC')
plt.xlabel("Temperature",fontsize=12)
plt.ylabel("Frequency", fontsize=12)
plt.title("1 Year Temperature for Station USC00519281",fontsize=12)
plt.tight_layout()<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.
temp_avg_last12=calc_temps('2016-08-20', '2016-08-30')
print(temp_avg_last12)
# 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)
error = temp_avg_last12[0][2]-temp_avg_last12[0][0]
avg_temp = temp_avg_last12[0][1]
min_temp = temp_avg_last12[0][0]
max_temp = temp_avg_last12[0][2]

fig, ax = plt.subplots(figsize=(4,5))
bar_chart = ax.bar(1 , avg_temp, color= '#FF1493', tick_label='',yerr=error, alpha=0.6)

ax.set_ylabel("Average Temperature (F)")
plt.title("Trip Avg Temp")

def autolabels(rects):
    for rect in rects:
        h=rect.get_height()
        
#label the bars 
autolabels(bar_chart)        
plt.ylim(0, 100)
plt.xlim(0,2)
ax.xaxis.grid()
fig.tight_layout()
plt.show()
# 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
trip_start = '2016-08-20'
trip_end = '2016-08-30'

sel = [Station.station, Station.name, Station.latitude, Station.longitude, Station.elevation, func.sum(Measurement.prcp)]
result = session.query(*sel).\
    filter(Measurement.station == Station.station).\
    filter(Measurement.date >= trip_start).\
    filter(Measurement.date <= trip_end).\
    group_by(Station.name).order_by(func.sum(Measurement.prcp).desc()).all()
print (result)

# 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`
normals=[]
# Set the start and end date of the trip
start_date = "08-20"
end_date = "08-30"
# Use the start and end date to create a range of dates
dates = [f"08-{num}" for num in range(20, 30)]

# 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
for date in dates:
    norm = daily_normals(date)
    normals.extend(norm)
    
normals
# Load the previous query results into a Pandas DataFrame and add the `trip_dates` range as the `date` index
tmin = [x[0] for x in normals]
tavg = [x[1] for x in normals]
tmax = [x[2] for x in normals]

year_dates = [f"2016-{date}" for date in dates]
year_dates

trip_normals = pd.DataFrame({"tmin":tmin, "tavg":tavg, "tmax":tmax, "date":year_dates}, index=year_dates)
trip_normals.head()
# Plot the daily normals as an area plot with `stacked=False`
trip_normals.plot(kind="area", stacked=False, color = '#8B6508')
plt.legend(loc="best")
plt.ylabel("Temperature (F)")
plt.xticks(range(len(trip_normals["date"])), trip_normals["date"], rotation="90")
plt.show()<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.11663216011042098, 'fraction_numerical': 0.023809523809523808, 'mean_word_length': 5.157932011331445, 'pattern_counts': {'":': 4, '<': 15, '<?xml version=': 0, '>': 19, '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': '520490', 'license': 'ADSL', 'n_tokens_mistral': 2976, 'n_tokens_neox': 2762, 'n_words': 832, 'path': 'climate_starter SN.ipynb', 'repo_name': 'Lafayola/sqlalchemy-challenge', 'size': '8694'}