<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
engine = create_engine("sqlite:///Resources/hawaii.sqlite")
conn = engine.connect()
# 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># 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

# Perform a query to retrieve the data and precipitation scores

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

# Sort the dataframe by date

# Use Pandas Plotting with Matplotlib to plot the data 

earliest_date = session.query(Measurement.date).order_by(Measurement.date).first()
latest_date = session.query(Measurement.date).order_by(Measurement.date.desc()).first()
print(f"Earliest: {earliest_date[0]} , Latest: {latest_date[0]}")
# Calculate the date 1 year ago from the last data point in the database
latestdate = dt.datetime.strptime(latest_date[0], '%Y-%m-%d')
querydate = dt.date(latestdate.year -1, latestdate.month, latestdate.day)
querydate
# Perform a query to retrieve the data and precipitation scores
sel = [Measurement.date,Measurement.prcp]
queryresult = session.query(*sel).filter(Measurement.date >= querydate).all()

# Save the query results as a Pandas DataFrame and set the index to the date column
precipitation = pd.DataFrame(queryresult, columns=['Date','Precipitation '])
precipitation = precipitation.dropna(how='any') # clean up non value entries
precipitation = precipitation.set_index("Date")
# Sort the dataframe by date
precipitation = precipitation.sort_values(["Date"], ascending=True)

precipitation.head()
plt.savefig("images/precipitation_by_date.png")

# Use Pandas Plotting with Matplotlib to plot the data
precipitation.plot(rot=90)
plt.title(f"Precipitation from {querydate} to {latest_date[0]}")
plt.xlabel("Date")
plt.ylabel("Inches")
plt.grid(which='major', axis='both', linestyle='-')
plt.legend()
plt.show()

plt.savefig("images/Precipitation.png")
# Use Pandas to calcualte the summary statistics for the precipitation data
precipitation.describe()

# Design a query to show how many stations are available in this dataset?
session.query(Station.id).count() # id is the primary key
""" What are the most active stations? (i.e. what stations have the most rows)?
List the stations and the counts in descending order."""

sel = [Measurement.station,func.count(Measurement.id)]
activestations = session.query(*sel).\
    group_by(Measurement.station).\
    order_by(func.count(Measurement.id).desc()).all()
activestations
""" Using the station id from the previous query, calculate the lowest temperature recorded, 
 highest temperature recorded, and average temperature of the most active station?"""
sel = [func.min(Measurement.tobs),func.max(Measurement.tobs),func.avg(Measurement.tobs)]
most_active_station_data = session.query(*sel).\
    group_by(Measurement.station).\
    order_by(func.count(Measurement.id).desc()).first()
list(most_active_station_data )
"""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 """
queryresult = session.query(Measurement.tobs).\
    filter(Measurement.station == activestations[0][0]).\
    filter(Measurement.date >= querydate).all()
temperatures = list(np.ravel(queryresult))

sel = [Station.station,Station.name,Station.latitude,Station.longitude,Station.elevation]
queryresult = session.query(*sel).all()
stations_desc = pd.DataFrame(queryresult, columns=['Station','Name','Latitude','Longitude','Elevation'])

stationname = stations_desc.loc[stations_desc["Station"] == activestations[0][0],"Name"].tolist()[0]

# n, bins, patches = plt.hist(temperatures, bins=12,alpha=0.7, rwidth=1.0,label='tobs')
plt.hist(temperatures, bins=12,rwidth=1.0,label='tobs')
plt.grid(axis='both', alpha=0.75)
plt.ylabel('Frequency')
plt.xlabel("Temperature")
plt.title(f"Temperature from {querydate} to {latest_date[0]} \nmeasured at {stationname}A")
plt.legend()
plt.savefig("images/Temperature.png")<jupyter_output><empty_output><jupyter_text>Temperature Analysis<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.
startdate = dt.date(2018, 1, 1) - dt.timedelta(days=365)
enddate = dt.date(2018, 1, 7) - dt.timedelta(days=365)

tempresult = calc_temps(startdate.strftime("%Y-%m-%d"), enddate.strftime("%Y-%m-%d"))[0]
tempresult
# 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)
x_pos = [0]
y_pos = [tempresult[1]]
error = [(tempresult[2] - tempresult[0])]

w = 3
h = 5
d = 70
plt.figure(figsize=(w, h), dpi=d)
plt.bar(x_pos,y_pos,color='orange', yerr=error)
plt.xlim(-0.75,0.75)
plt.title("Trip Avg Temp")
plt.ylabel("Temp (F)")
plt.ylim(0, 100)
plt.tick_params(axis='x',which='both',bottom=False,top=False,labelbottom=False)
plt.grid(which='major', axis='x', linestyle='')
plt.grid(which='major', axis='y', linestyle='-')
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
startdate = '2017-01-01'
enddate = '2017-01-07'

sel = [Station.station,Station.name,Station.latitude,Station.longitude,Station.elevation,func.sum(Measurement.prcp)]
queryresult = session.query(*sel).\
    filter(Station.station == Measurement.station).\
    group_by(Measurement.station).\
    filter(Measurement.date >= startdate).\
    filter(Measurement.date <= enddate).\
    order_by(func.sum(Measurement.prcp).desc()).\
    all()
  
stations_df = pd.DataFrame(queryresult, columns=['Station','Name','Latitude','Longitude','Elevation','PrcpSum'])
print(stations_df)<jupyter_output>Station                                    Name  Latitude  Longitude  \
0  USC00519523      WAIMANALO EXPERIMENTAL FARM, HI US  21.33556 -157.71139   
1  USC00516128            MANOA LYON ARBO 785.2, HI US  21.33310 -157.80250   
2  USC00514830  KUALOA RANCH HEADQUARTERS 886.9, HI US  21.52130 -157.83740   
3  USC00513117                    KANEOHE 838.1, HI US  21.42340 -157.80150   
4  USC00519281                     WAIHEE 837.5, HI US  21.45167 -157.84889   
5  USC00519397                    WAIKIKI 717.2, HI US  21.27160 -157.81680   

   Elevation  PrcpSum  
0       19.5     0.61  
1      152.4     0.60  
2        7.0     0.60  
3       14.6     0.35  
4       32.9     0.20  
5        3.0     0.00
{'fraction_non_alphanumeric': 0.12149532710280374, 'fraction_numerical': 0.03762911952779144, 'mean_word_length': 4.582017844886754, 'pattern_counts': {'":': 0, '<': 14, '<?xml version=': 0, '>': 16, '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': '41475', 'license': 'ADSL', 'n_tokens_mistral': 2860, 'n_tokens_neox': 2590, 'n_words': 735, 'path': 'my_starter_code.ipynb', 'repo_name': 'pnguendang08/sqlalchemy-challenge', 'size': '8132'}