<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")
# 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># Display the row's columns and data in dictionary format

first_row_measurement = session.query(measurement).first()
first_row_measurement.__dict__
# Display the row's columns and data in dictionary format

first_row_station = session.query(station).first()
first_row_station.__dict__
# 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

# Find Max Date of dataset using orderby descending
precip=session.query(measurement.date,measurement.prcp).\
    order_by(measurement.date.desc()).all()

precip
# Display of precipitation from only the last year

twelvemonths="2016,08,23"

lastyear=session.query(measurement.date,measurement.prcp).\
    filter(func.strftime("%Y,%m,%d",measurement.date)>=twelvemonths).all()

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

df = pd.DataFrame(lastyear, columns=['date','prcp'])
index=df.set_index('date', inplace=True)
index
# Sort the dataframe by date

sortdf = pd.DataFrame(lastyear, columns=['date','prcp'])
sortdf=sortdf.sort_values(['date'],ascending=True)

# Drop NAN values
sortdf.dropna(how='any')
# Use Pandas Plotting with Matplotlib to plot the data

sortdf.plot('date','prcp',figsize=(10,6))
plt.xlabel('Date')
plt.ylabel("Inches")
plt.title("Precipitation by Date 8/23/2016 through 8/23/2017")
plt.xticks(rotation=90)
plt.tight_layout
plt.show()
# Use Pandas to calcualte the summary statistics for the precipitation data
sortdf.describe()
# Design a query to show how many stations are available in this dataset?
stationcount=session.query(func.count(station.station)).all()
stationcount
# Create dataframe for precipitation data needed for next analysis

allprecip=session.query(measurement.station,measurement.date,measurement.prcp,measurement.tobs)
precip_df=pd.DataFrame(allprecip,columns=['station','date','prcp','tobs'])
precip_df
# Create dataframe for station data needed for next analysis

allstations=session.query(station.station,station.name,station.id)
allstations_df=pd.DataFrame(allstations,columns=['station','name','id'])
allstations_df
# Merge dataframes

merge_df=pd.merge(precip_df,allstations_df,on="station")
merge_df
# What are the most active stations? (i.e. what stations have the most rows)?
# List the stations and the counts in descending order.

group_df=merge_df[['station','id']]

group=group_df.groupby(['station']).count()
group

sorted_df=group.sort_values(['id'],ascending=False)
sorted_df
# Using the station id from the previous query, calculate the lowest temperature recorded, 
# highest temperature recorded, and average temperature of the most active station?

# Create new dataframe with only data needed for this analysis
temp=merge_df[['id','tobs','station']]

# Calculate min, max, mean

lowesttemp=temp.loc[(temp['station']=='USC00519281')]
lowesttemp_min=lowesttemp['tobs'].min()
lowesttemp_max=lowesttemp['tobs'].max()
lowesttemp_avg=lowesttemp['tobs'].mean()

# Create dataframe to display min, max, mean and hide the index

analysis=pd.DataFrame({"Minimum Temp":[lowesttemp_min],"Maximum Temp":[lowesttemp_max],"Average Temp":[lowesttemp_avg]})
analysis
analysis.style.hide_index()
# 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

# Create new dataframe with only data needed for the histogram

highest_no=merge_df[['date','tobs','station']]

# Filter for last year of data for specific station

lstyr=highest_no.loc[(highest_no['date']>='2016-08-23')&(highest_no['station']=='USC00519281')]

lstyr.hist(column='tobs')<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.10823733970875897, 'fraction_numerical': 0.012171267115844382, 'mean_word_length': 7.017421602787456, 'pattern_counts': {'":': 3, '<': 9, '<?xml version=': 0, '>': 11, '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': '392702', 'license': 'ADSL', 'n_tokens_mistral': 1472, 'n_tokens_neox': 1379, 'n_words': 411, 'path': 'climate_starter.ipynb', 'repo_name': 'walaha2002/sqlalchemy-challenge', 'size': '4601'}