<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
# create engine to hawaii.sqlite
engine = create_engine("sqlite:///Resources/hawaii.sqlite")
inspector = inspect(engine)
#inspector
# 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.
#inspector.get_table_names()
columns = inspector.get_columns('measurement')
columns
datesdf = pd.DataFrame(session.query(measurement.date).all())
datesdf['date'] = datesdf['date'].astype('datetime64[ns]')
datesdf.info()
# Starting from the most recent data point in the database. 
last = datesdf.date.max()
last20 = last.date()
print(last20)
#another method of finding the last date
last2 = session.query(func.max(measurement.date)).scalar()
print(last2)
#playing around with datetime
from datetime import datetime
last22 = datetime.strptime(last2, '%Y-%m-%d')
pyd = last22 - dt.timedelta(days=365)
pyd
py = last22 - dt.timedelta(days=365)
py_date = py.strftime('%Y-%m-%d')
py_date
py = last20 - dt.timedelta(days=365)
py_date = py.strftime('%Y-%m-%d')
results = session.query(measurement.date, measurement.prcp).\
        filter(measurement.date >= py_date).all()
results
# Save the query results as a Pandas DataFrame and set the index to the date column
df1 = pd.DataFrame(results, columns=['date','precipitation'])
df1.set_index('date', inplace=True)
df1 = df1.sort_index()
df1.head()

# 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.
py = last20 - dt.timedelta(days=365)
py_date = py.strftime('%Y-%m-%d')

# Perform a query to retrieve the data and precipitation scores
results = session.query(measurement.date, measurement.prcp).\
        filter(measurement.date >= py_date).all()

# Save the query results as a Pandas DataFrame and set the index to the date column
df1 = pd.DataFrame(results, columns=['date','precipitation'])
df1.set_index('date', inplace=True)


# df1.head()

# Sort the dataframe by date
df1 = df1.sort_index()





#auto generate ticks
i = 10
n = int(365/i)
ticks = [date.strftime('%Y-%m-%d') for date in [last20 - dt.timedelta(days=n*d) for d in range(i)]]
ticks
# Use Pandas Plotting with Matplotlib to plot the data

fig1, ax1 = plt.subplots(figsize=(13, 13))
i = 10
n = int(365/i)
ticks = [date.strftime('%Y-%m-%d') for date in [last20 - dt.timedelta(days=n*d) for d in range(i)]]
x = df1.index
y = df1['precipitation']
plt.plot(x, y, color = 'purple')
plt.xticks(ticks, rotation=69)
plt.yticks(size = 13)
plt.ylabel("Inches of Precipitation",font='Georgia',fontsize=13)
plt.xlabel("Date",font='Georgia',fontsize=13)
plt.title(f"Precipitaion (inches) for Honolulu, Hawaii from {py_date} to {last20}",font='Papyrus',fontsize=19.69)
plt.legend(['precipitation'], loc="upper right",fontsize=13)

plt.xlim(0,369)
plt.ylim(-.69,7.69)

#plt.savefig("./Images/HonoluluPrecipitaion.png")

plt.show()

# Use Pandas to calcualte the summary statistics for the precipitation data
df1.describe()<jupyter_output><empty_output><jupyter_text>Exploratory Station Analysis<jupyter_code>columns2 = inspector.get_columns('station')
df3 = pd.DataFrame(columns2)
df3
# Design a query to calculate the total number stations in the dataset
session.query(station.id).count()
sdf = pd.DataFrame(session.query(station).all())
sdf


for class_instance in session.query(station).all():
    print(vars(class_instance))
engine.execute('SELECT * FROM station').fetchall()
session.query(station).all()
sel = [station.station, station.name, func.count(measurement.station)]
s = session.query(*sel).filter(station.station == measurement.station).group_by(station.station).order_by(func.count(measurement.station).desc()).all()
sdf = pd.DataFrame(s, columns=['station','name','count'])
sdf
# 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.
sel = [station.station, station.name, func.count(measurement.station)]
s = session.query(*sel).filter(station.station == measurement.station).group_by(station.station).order_by(func.count(measurement.station).desc()).all()
sdf = pd.DataFrame(s, columns=['station','name','count'])
sdf.set_index('station',inplace=True)
sdf
sdf.index[0]
# Using the most active station id from the previous query, calculate the lowest, highest, and average temperature.
session.query(func.min(measurement.tobs),func.max(measurement.tobs),func.avg(measurement.tobs)).\
                filter(measurement.station == sdf.index[0]).all()
# 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

results1 = session.query(measurement.tobs).\
        filter(measurement.date.between(py_date,last20), measurement.station == sdf.index[0]).all()
df2 = pd.DataFrame(results1)
df2.head()
#plot the results as a histogram

fig, ax = plt.subplots(figsize=(13,13))
n, bins, patches = plt.hist(x=df2.tobs, bins=13, color='magenta',alpha=0.69, rwidth=0.85)
plt.grid(axis='y', alpha=0.69)
plt.xlabel('Temperature',font='Georgia',fontsize='19')
plt.ylabel('Frequency in days',font='Georgia',fontsize='19')
plt.title(f'Temperatures for station {sdf.name[0]} from {py_date} to {last20}',font='Papyrus',fontsize='25')
plt.legend(['tobs'], loc = 'upper right')
plt.yticks(size=13)
plt.xticks(size=13)
#plt.savefig("./Images/topstationhist.png")
plt.show()
sdf.name[0]<jupyter_output><empty_output><jupyter_text>Close session<jupyter_code># Close Session
session.close()<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.13948292371528886, 'fraction_numerical': 0.021704436642195978, 'mean_word_length': 5.978841870824054, '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': '475850', 'license': 'MIT', 'n_tokens_mistral': 2211, 'n_tokens_neox': 2078, 'n_words': 535, 'path': 'climate_starter.ipynb', 'repo_name': 'yash5OG/SQLAlchemyChallenge-UCB-Y5', 'size': '6266'}