<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, inspect, func, distinct
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)
# We can 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)<jupyter_output><empty_output><jupyter_text>Exploratory Climate Analysis<jupyter_code>inspector = inspect(engine)

print("---------STATION-------------")
columns= inspector.get_columns('station')
for column in columns:
    print(column['name'], column["type"])

print("----------MEASUREMENT------------")

columns= inspector.get_columns('measurement')
for column in columns:
    print(column['name'], column["type"])
### 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

latest_date = session.query(Measurement.date).order_by(Measurement.date.desc()).first()[0]
formated_latest_date = dt.datetime.strptime(latest_date,"%Y-%m-%d").date()

year_ago = formated_latest_date - dt.timedelta(days=365)

# Perform a query to retrieve the data and precipitation scores

one_year_measurement = session.query(Measurement.date, Measurement.prcp).filter(Measurement.date > year_ago).order_by(Measurement.date).all()

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

precipitation_df = pd.DataFrame(one_year_measurement, columns=["Date", "Precipitation"])

# Sort the dataframe by date

precipitation_df = precipitation_df.sort_values(by="Date").set_index("Date")

# Use Pandas Plotting with Matplotlib to plot the data
import matplotlib.dates as mdates

inches = list(precipitation_df["Precipitation"])
date =  list(precipitation_df.index)

axt = plt.gca()
timet = [dt.datetime.strptime(d,"%Y-%m-%d").date() for d in date]
formatter = mdates.DateFormatter("%Y-%m-%d")
axt.xaxis.set_major_formatter(formatter)

locator = mdates.MonthLocator()
axt.xaxis.set_major_locator(locator)

Bar = plt.bar(timet,inches)
plt.xlabel("date")
plt.ylabel("inches")
plt.title("Precipitation (inches) per day")
plt.xticks(rotation=90)
plt.grid()
plt.plot()
# 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?

stations_count = session.query(distinct(Station.station)).count()

print(f"There are {stations_count} stations")
# What are the most active stations? (i.e. what stations have the most rows)?
# List the stations and the counts in descending order.

stations_count = session.query(Measurement.station, func.count(Measurement.station)).\
                        group_by(Measurement.station).\
                        order_by(func.count(Measurement.station).desc()).all()
                                                                    
most_active_station = session.query(Measurement.station, func.count(Measurement.station)).\
                        group_by(Measurement.station).\
                        order_by(func.count(Measurement.station).desc()).all()[0][0]
print(f"Station {most_active_station}, has the highest number of observation")
     
# Using the station id from the previous query, calculate the lowest temperature recorded, 
# minimun, highest temperature recorded, and average temperature of the most active station?

min_temperature = session.query(func.min(Measurement.tobs)).\
    filter(Measurement.station == most_active_station).all()[0][0]

max_temperature = session.query( func.max(Measurement.tobs)).\
    filter(Measurement.station == most_active_station).all()[0][0]

avg_temperature = session.query(func.avg(Measurement.tobs)).\
    filter(Measurement.station == most_active_station).all()[0][0]


print(f"The minimun temperature in station {most_active_station} is {min_temperature}")
print(f"The maximum temperature in station {most_active_station} is {max_temperature}")
print(f"The average temperature in station {most_active_station} is {round(avg_temperature, 2)}")
# 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

one_year_temperature = session.query(Measurement.tobs).\
                    filter(Measurement.date <= formated_latest_date).\
                    filter(Measurement.date >= year_ago).\
                    order_by(Measurement.date).all()

temperature_df = pd.DataFrame(one_year_temperature, columns = ["Temperature"])

temperature_df.hist(column = "Temperature")
plt.xlabel("Temperature (F)")
plt.ylabel("Frequency")
plt.show()<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):

    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.

#Trip dates June 2, 2018 to June 12, 2018

[(tmin, tavg, tmax)] = calc_temps('2017-06-02', '2017-06-12')

print(f"The minimum temperature between the dates is: {tmin}")
print(f"The average temperature between the dates is: {round(tavg, 2)}")
print(f"The maximum temperature between the dates is: {tmax}")
# 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)

fig,ax = plt.subplots(figsize=(3,5))

ax.bar(np.arange(1), tavg, yerr=tmax-tmin, align='center', alpha=0.3, ecolor='black' )
ax.set_xticks(np.arange(1))

ax.set_xticklabels(['Hawaii'])
ax.set_ylabel("Temperature (F)")
ax.set_title('Trip Average Temp')
plt.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

rain_fall = session.query(func.sum(Measurement.prcp), Station.name, Station.station, Station.latitude, Station.longitude, Station.elevation).\
                filter(Station.station == Measurement.station).order_by(func.sum(Measurement.prcp)).\
                filter(Measurement.date >= '2017-06-02').\
                filter(Measurement.date <= '2017-06-12').\
                group_by(Station.station).all()
rain_fall 
for x in rain_fall:
    
    print(f"The total amount of rainfall in {x[1]} station is {x[0]}")
# 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):
 
    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 string

i = 0
dates = []
while i < 11: 
    travel_date = dt.date(2017,6,2) + dt.timedelta(days = i)
    dates.append(travel_date.strftime("%m-%d"))
    i = i + 1

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

normals = []
for date in dates:
    normals.append(daily_normals(date)[0])
    
print(normals)  
# Load the previous query results into a Pandas DataFrame and add the `trip_dates` range as the `date` index
vacation_tmp_df = pd.DataFrame(normals, columns = ["Min", "Avg", "Max"])
vacation_tmp_df["Date"] = dates
vacation_tmp_df = vacation_tmp_df.set_index("Date")
vacation_tmp_df.head(12)
# Plot the daily normals as an area plot with `stacked=False`
vacation_tmp_df.plot(kind='area', stacked=False)
plt.legend(loc='lower center')
plt.ylabel("Temperature (F)")
plt.title("Vacation Daily Normals")
plt.tight_layout()
plt.show()<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.11877094972067039, 'fraction_numerical': 0.011731843575418994, 'mean_word_length': 4.5700062227753575, 'pattern_counts': {'":': 0, '<': 17, '<?xml version=': 0, '>': 17, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 2, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '3', 'dir': 'jupyter-structured-clean-dedup', 'id': '422448', 'license': 'ADSL', 'n_tokens_mistral': 2863, 'n_tokens_neox': 2692, 'n_words': 798, 'path': 'climate_starter.ipynb', 'repo_name': 'angelasivirich/sqlalchemy-challenge', 'size': '8950'}