<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, inspect, func
# create engine to hawaii.sqlite
engine = create_engine("sqlite:///hawaii.sqlite")
# reflect an existing database into a new model
Base = automap_base()
Base.prepare(engine, reflect=True)
Base.classes.keys()
# reflect the tables

# View all of the classes that automap found
Measurement = Base.classes.measurement
# Save references to each table
Station = Base.classes.station
inspector = inspect(engine)
#inspector.get_table_names()
columns = inspector.get_columns('Station')
for c in columns:
    print(c['name'], c["type"])
columns = inspector.get_columns('Measurement')
for c in columns:
    print(c['name'], c["type"])

# Create our session (link) from Python to the DB
session = Session(engine)<jupyter_output><empty_output><jupyter_text>Exploratory Precipitation Analysis<jupyter_code>for row in session.query(Measurement, Measurement.date).limit(5).all():
    print(row)
# Find the most recent date in the data set.
results = session.query(Measurement.date).\
    order_by(Measurement.date.desc()).all()
print(results) 
#Another way to find it
MaxDate = session.query(func.max(Measurement.date)).all()
print(MaxDate)
import datetime as dt
Maxi = MaxDate[0]
Maxi
Maxi[0]
from datetime import datetime
a=datetime.strptime(Maxi[0], '%Y-%m-%d').date()
a
Yearago = a - dt.timedelta(days=365)
Yearago
Yearagostr=Yearago.strftime('%Y-%m-%d')
Yearagostr
YearDates = session.query(Measurement.date,Measurement.prcp).order_by(Measurement.date.desc()).\
filter(Measurement.date >= Yearagostr).all()


for row in YearDates:
    print (row)
Long = len(YearDates)
df = pd.DataFrame(YearDates[::], columns=['date', 'prep'])
df.set_index('date', inplace=True, )
df = df.sort_index ()
df.head(10)
len(df)
Data = df.dropna()
Data.index.unique()
ax= Data.plot(figsize=(20, 10))
plt.show()
Res = Data.prep.agg(["mean","median","var","std","sem"])
Res
Howmanyst = session.query(Station).count()

Howmanyst
results2 = session.query(Station.station,Station.name).\
    order_by(Station.station.desc()).all()
print(results2)<jupyter_output>[('USC00519523', 'WAIMANALO EXPERIMENTAL FARM, HI US'), ('USC00519397', 'WAIKIKI 717.2, HI US'), ('USC00519281', 'WAIHEE 837.5, HI US'), ('USC00518838', 'UPPER WAHIAWA 874.3, HI US'), ('USC00517948', 'PEARL CITY, HI US'), ('USC00516128', 'MANOA LYON ARBO 785.2, HI US'), ('USC00514830', 'KUALOA RANCH HEADQUARTERS 886.9, HI US'), ('USC00513117', 'KANEOHE 838.1, HI US'), ('USC00511918', 'HONOLULU OBSERVATORY 702.2, HI US')]<jupyter_text>Exploratory Station Analysis<jupyter_code>Sta = session.query(Measurement.station, func.count(Measurement.station)).group_by(Measurement.station).order_by(func.count(Measurement.station).desc()).all()

print(Sta)
Counttemp = session.query(func.count(Measurement.tobs)).filter_by(station="USC00519281").all()
Maxtemp = session.query(func.max(Measurement.tobs)).filter_by(station="USC00519281").all()
Mintemp = session.query(func.min(Measurement.tobs)).filter_by(station="USC00519281").all()
Avgtemp = session.query(func.avg(Measurement.tobs)).filter_by(station="USC00519281").all()
[Counttemp[0][0]]
Counttemp = Counttemp[0]
Maxtemp = Maxtemp[0]
Mintemp = Mintemp[0]
Avgtemp = Avgtemp[0]
print(f'Number of temperatures for station USC00519281: {Counttemp[0]}')
print(f'Max temperature for station USC00519281: {Maxtemp[0]}')
print(f'Min temperature for station USC00519281: {Mintemp[0]}')
print(f'Average of temperatures for station USC00519281: {Avgtemp[0]}')

Yearagostation = session.query(Measurement.date,Measurement.tobs).\
filter(Measurement.date >= Yearagostr).filter_by(station="USC00519281").all()

len(Yearagostation)
df2 = pd.DataFrame(Yearagostation[::], columns=['date','tobs'])
df2.set_index('date', inplace=True, )
df2 = df2.sort_index ()
df2.head()
len(df2)
plt.hist(df2.tobs,bins=12)
plt.xlabel("Temperatures")
plt.ylabel("Frequency")
plt.show()<jupyter_output><empty_output><jupyter_text>Close session<jupyter_code># Close Session
session.close()<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.1560434372049103, 'fraction_numerical': 0.04863078375826251, 'mean_word_length': 7.227184466019417, 'pattern_counts': {'":': 0, '<': 16, '<?xml version=': 0, '>': 18, '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': '493582', 'license': 'ADSL', 'n_tokens_mistral': 1688, 'n_tokens_neox': 1517, 'n_words': 306, 'path': 'climate_starter.ipynb', 'repo_name': 'edipasq/sqlalchemy-challenge', 'size': '4236'}