<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
# create engine to hawaii.sqlite
engine = create_engine("sqlite:///hawaii.sqlite")
conn = engine.connect()
# reflect an existing database into a new model
Base = automap_base()

# reflect the tables
Base.prepare(engine, reflect=True)




# 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 Precipitation Analysis<jupyter_code># Find the most recent date in the data set.
recent_date = session.query(func.max(Measurement.date)).first()
recent_date
# Design a query to retrieve the last 12 months of precipitation data and plot the results. 
# Starting from the most recent data point in the database. 

# Calculate the date one year from the last date in data set.
last_year = dt.date(2017,8,23)-dt.timedelta(days=365)
last_year
# Perform a query to retrieve the data and precipitation scores
result = session.query(Measurement.date, Measurement.prcp).filter(Measurement.date >= last_year).all()
result

# Save the query results as a Pandas DataFrame and set the index to the date column
df = pd.DataFrame(result,columns = ["Date","Precipitation"])


# Sort the dataframe by date
sorted_df = df.sort_values("Date")

# Use Pandas Plotting with Matplotlib to plot the data
sorted_df.plot(x = "Date", y = "Precipitation")
plt.xticks(rotation = 90);
plt.xlabel("Date")
plt.ylabel("Inches")
plt.title("Precipitation Score by Date")


# Use Pandas to calcualte the summary statistics for the precipitation data
sorted_df.describe()<jupyter_output><empty_output><jupyter_text>Exploratory Station Analysis<jupyter_code>data_count = func.count(Measurement.station)
# Design a query to calculate the total number stations in the dataset
total_count_stations = session.query(func.count((Measurement.station).distinct())).all()
total_count_stations
# Design a query to find the most active stations (i.e. what stations have the most rows?)
# List the stations and the counts in descending order.
count_station = session.query(Measurement.station,data_count).group_by(Measurement.station).order_by(data_count.desc()).all()
count_station
most_active = count_station[0][0]
most_active
# Using the most active station id from the previous query, calculate the lowest, highest, and average temperature.
temp_min = session.query(func.min(Measurement.tobs)).group_by(Measurement.station).order_by(data_count.desc()).first()
temp_max= session.query(func.max(Measurement.tobs)).group_by(Measurement.station).order_by(data_count.desc()).first()
temp_avg = session.query(func.round(func.avg(Measurement.tobs),1)).group_by(Measurement.station).order_by(data_count.desc()).first()
print(f'Lowest tempterature is {temp_min[0]}')
print(f'Highest tempterature is {temp_max[0]}')
print(f'Average tempterature is {temp_avg[0]}')

# Using the most active station id
# Query the last 12 months of temperature observation data for this station and plot the results as a histogram
station_result = session.query(Measurement.date, Measurement.tobs).filter(Measurement.date >= last_year,Measurement.station == most_active).all()
station_result

station_df = pd.DataFrame(station_result,columns = ["Date","Temperature"])


# Sort the dataframe by date
station_sorted_df = station_df.sort_values("Date")

# Use Pandas Plotting with Matplotlib to plot the data
station_sorted_df.hist(column = "Temperature",bins=12)
plt.xticks(rotation = 90);
plt.xlabel("Date")
plt.ylabel("Frequency")
plt.title("Temperature observation")
plt.legend(['tobs'],loc="upper right")<jupyter_output><empty_output><jupyter_text>Close session<jupyter_code># Close Session
session.close()<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.10368550368550368, 'fraction_numerical': 0.0063882063882063885, 'mean_word_length': 6.724857685009487, 'pattern_counts': {'":': 0, '<': 17, '<?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': '4', 'dir': 'jupyter-structured-clean-dedup', 'id': '386205', 'license': 'ADSL', 'n_tokens_mistral': 1287, 'n_tokens_neox': 1223, 'n_words': 356, 'path': 'climate_starter.ipynb', 'repo_name': 'AlexanderLorin/sqlalchemy-challenge', 'size': '4070'}