<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")
# 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>wholetable=session.query(measurement)
for date in wholetable:
    print(date.date)
# Find the most recent date in the data set.
m_recent=session.query(measurement.date).order_by(measurement.date.desc()).first()
m_recent
# m_recent=m_recent.replace(",","")
# 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..
# 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

# lastyear=dt.date(m_recent)-dt.timedelta(weeks=52)
lastyear=dt.date(2017,8,23)-dt.timedelta(weeks=52)
lastyear

lastyearprcp=session.query(measurement.date,measurement.prcp).filter(measurement.date>=lastyear).all()
lastyearprcp



df=pd.DataFrame(lastyearprcp, columns=['date','prcp'])
df.head()

date_prcp=df.set_index('date',inplace=True)
# date_prcp=date_prcp.iloc[:20]
df.plot(kind = 'line', figsize=(30,4),rot=45)
# date_prcp_plot=date_prcp.plot(kind = 'bar', figsize=(30,4))
# date_prcp_plot.set_xticklabels(date_prcp['date'],rotation=45)


plt.title("Precipitation in last year")
plt.xlabel('Date')
plt.ylabel('Precipiation in (unit)')

plt.show()
plt.tight_layout()



# df.set_index(df['date'],inplace=True)
# df.head()

# df.plot(x='date', y='prcp',kind='bar')
# plt.tight_layout()


# Use Pandas to calcualte the summary statistics for the precipitation dataplt.

# df.agg()
df.describe()<jupyter_output><empty_output><jupyter_text>Exploratory Station Analysis<jupyter_code># Design a query to calculate the total number stations in the dataset
# Design a query to find the most active stations (i.e. what stations have the most rows?)

# session.query(station).count()
session.query(measurement.station).count()


# 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.

m_a_station=session.query(measurement.station, func.count(measurement.station))\
.group_by(measurement.station)\
.order_by(func.count(measurement.station)\
.desc()).first()
m_act_st=m_a_station[0]
m_act_st
# Using the most active station id from the previous query, calculate the lowest, highest, and average temperature.
m_act_agg = session.query(measurement.station, func.min(measurement.tobs), func.max(measurement.tobs),func.avg(measurement.tobs))\
.filter(measurement.station == m_act_st)\
.all()

m_act_agg

session.query(measurement.date)\
.filter(measurement.station == m_act_st).order_by(measurement.date.desc()).first()
lastyear_m_act=dt.date(2017,8,18)-dt.timedelta(weeks=52)
lastyear_m_act
# 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

m_act_query = session.query(measurement.date, measurement.tobs)\
.filter(measurement.station == m_act_st)\
.filter(measurement.date >= lastyear_m_act)\
.filter(measurement.date <= dt.date(2017,8,18))\
.order_by(measurement.date).all()

m_act_df=pd.DataFrame(m_act_query)
m_act_df
m_act_df.rename(columns={0:'date',1:'tobs'},inplace=True)
m_act_df
m_act_df.set_index('date',inplace=True)
m_act_df.head()

plt.hist(m_act_df["tobs"], bins = 12, label = "tobs")
plt.xlabel("temperature")
plt.ylabel("count")
plt.title(f"{m_act_st} Temperature Histogram")
plt.legend()
plt.show()<jupyter_output><empty_output><jupyter_text>Close session<jupyter_code># Close Session
session.close()<jupyter_output><empty_output>
StarcoderdataJupyterStructuredCleanDedup