<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")
# 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)
inspector = inspect(engine)
inspector.get_table_names()<jupyter_output><empty_output><jupyter_text>Exploratory Climate Analysis<jupyter_code>#Exploring the data
engine.execute('SELECT * FROM measurement LIMIT 5').fetchall()
#Exploring the data
engine.execute('SELECT * FROM station LIMIT 5').fetchall()
columns = inspector.get_columns('station')
for c in columns:
    print(c['name'], c["type"])
# Get a list of column names and types
columns = inspector.get_columns('measurement')
for c in columns:
    print(c['name'], c["type"])
#Which is the last date recorded?
last_date = session.query(Measurement.date).order_by(Measurement.date.desc()).first()
last_date
# Earliest Date?
session.query(Measurement.date).order_by(Measurement.date).first()
# Calculate the date 1 year ago from the last data point in the database
query_limit = dt.date(2017,8,23) - dt.timedelta(days=365)
print("Query Date: ", query_limit)
# Design a query to retrieve the last 12 months of precipitation data and plot the results
# 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
prcp =(session.query(Measurement.station, Measurement.date, Measurement.prcp).filter(Measurement.date >= query_limit).all())

df = pd.DataFrame(prcp, columns=['station','date', 'prcp'])
df = df.dropna()
df['date'] = pd.to_datetime(df['date'])
df.set_index('date', inplace=True)

df.head()

# Use Pandas Plotting with Matplotlib to plot the data
#df.plot(kind="bar", figsize=(10,6), legend = False)
#plt.title("Precipitation Hawaii 2016-08-23 to 2017-08-23").set_size(20)
#plt.xlabel("Date").set_size(20)

#plt.ylabel("Precipitation").set_size(20)
#plt.grid()
#plt.tick_params(
 #   axis='x',          # changes apply to the x-axis
  #  which='both',      # both major and minor ticks are affected
   # bottom=False,      # ticks along the bottom edge are off
    #top=False,         # ticks along the top edge are off
    #labelbottom=False) # labels along the bottom edge are off
#plt.savefig("prcp.png")
#plt.show()
#plt.close()
prcp_list = df["prcp"].tolist()
#Another way to create this figure
fig, ax = plt.subplots()
ax.bar(df.index.get_values(), prcp_list,align='center', label="Precipitation")
ax.xaxis_date()
fig.autofmt_xdate()
plt.title('Precipitation Hawaii 2016-08-23 to 2017-08-23')
plt.xlabel('Date')
plt.ylabel("Precipitation")
plt.tight_layout()
plt.savefig("prcp_sb.png")
plt.show()
df.describe()
# Design a query to show how many stations are available in this dataset?
number_stations = session.query(Measurement.station).distinct(Measurement.station).count()
print("Total number of stations is "+ str(number_stations))
# What are the most active stations? (i.e. what stations have the most rows)?
# List the stations and the counts in descending order.
active_stations = session.query(Measurement.station, func.count(Measurement.station)).group_by(Measurement.station).order_by(func.count(Measurement.prcp).desc()).all()
active_stations

top_station = session.query(Measurement.station).group_by(Measurement.station).order_by(func.count(Measurement.station).desc()).limit(1).scalar()
print ( "Station which has the highest number of observations is  " + str(top_station))
# Using the station id from the previous query, calculate the lowest temperature recorded, 
# highest temperature recorded, and average temperature most active station?

data_top_station = session.query(Measurement.station, Measurement.tobs).filter(Measurement.station == top_station).all()
data_top_station
#Highest temperature
max_top_station = session.query(func.max(Measurement.tobs)).filter(Measurement.station == top_station).all()
max_top_station
#Lowesttemperature
min_top_station = session.query(func.min(Measurement.tobs)).filter(Measurement.station == top_station).all()
min_top_station
#Average temperature
#Highest temperature
avg_top_station = session.query(func.avg(Measurement.tobs)).filter(Measurement.station == top_station).all()
avg_top_station
# Choose the station with the highest number of temperature observations.
top_temp_station = session.query(Measurement.station).group_by(Measurement.station).order_by(func.count(Measurement.tobs).desc()).limit(1).scalar()
print ( "Station which has the highest number of observations is  " + str(top_temp_station))
# Query the last 12 months of temperature observation data for this station and plot the results as a histogram
#Which is the last date recorded?
last_date_top = session.query(Measurement.date).filter(Measurement.station == top_station).order_by(Measurement.date.desc()).first()
last_date_top
# Calculate the date 1 year ago from the last data point in the database
query_limit_top = dt.date(2017,8,18) - dt.timedelta(days=365)
print("Query Date: ", query_limit_top)
temp =(session.query(Measurement.date, Measurement.tobs).filter(Measurement.date >= query_limit_top).all())

#df_temp = pd.DataFrame(temp, columns=['date', 'prcp'])
#df_temp = df_temp.dropna()

#df_temp.head()
temp
temp_df=pd.DataFrame(temp)
plt.hist(temp_df['tobs'],12)
plt.xlabel("Temperature")
plt.ylabel("Number of Observations")
plt.title("Station Analysis (8/18/16 to 8/18/17) for Station " + top_temp_station)
plt.savefig("temp_sb.png")
plt.show()
# 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
# 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=[]
start_date = '2017-08-11'
end_date = '2017-08-18'
def calc_temps(start_date, end_date):
    results=session.query(func.max(Measurement.tobs).label("max_tobs"), \
                          func.min(Measurement.tobs).label("min_tobs"),\
                          func.avg(Measurement.tobs).label("avg_tobs")).\
                          filter(Measurement.date.between(start_date , end_date))  
    res = results.one()
    res
    tmax = res.max_tobs
    tmin= res.min_tobs
    tavg= res.avg_tobs
    temp.append(tmax)
    temp.append(tmin)
    temp.append(tavg)
calc_temps(start_date, end_date)
temp
# 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)
tavg=temp[2]
tmin=temp[1]
tmax=temp[0]
fig = plt.figure()
plt.bar( 1,tavg, color = 'salmon', yerr = tmax-tmin, align='center')
plt.xlim(-0.2, 2.2)
plt.ylim([0, 110])
plt.axes().get_xaxis().set_visible(False)
plt.title('Expected average temperature for my trip to Hawaii')
plt.ylabel("Temp (°F)")
plt.tight_layout()
plt.show()
fig.savefig('Trip_temp_sb.png')<jupyter_output>C:\Users\Jojo\Anaconda3\envs\PythonData\lib\site-packages\matplotlib\cbook\deprecation.py:107: MatplotlibDeprecationWarning: Adding an axes using the same arguments as a previous axes currently reuses the earlier instance.  In a future version, a new instance will always be created and returned.  Meanwhile, this warning can be suppressed, and the future behavior ensured, by passing a unique label to each axes instance.
  warnings.warn(message, mplDeprecation, stacklevel=1)
{'fraction_non_alphanumeric': 0.11906556141672947, 'fraction_numerical': 0.01456920371765888, 'mean_word_length': 5.442556634304207, 'pattern_counts': {'":': 0, '<': 8, '<?xml version=': 0, '>': 10, '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': '2', 'dir': 'jupyter-structured-clean-dedup', 'id': '353509', 'license': 'FSFAP', 'n_tokens_mistral': 2631, 'n_tokens_neox': 2478, 'n_words': 706, 'path': 'The_surf_ is_ up!_seaborn.ipynb', 'repo_name': 'JoannePeel/The_Surf_is_Up', 'size': '7962'}