<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 ,extract 

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)
# We can view all of the classes that automap found
Base.classes.keys()
# Save references to each table # so you dont have to write base.classes all the time
Measurement = Base.classes.measurement
Station = Base.classes.station
dir(Measurement)

dir(Station)
# Create our session (link) from Python to the DB
session = Session(engine)
results = session.query(Measurement.date,Measurement.prcp).all()
for result in results:
    print(result)<jupyter_output>('2010-01-01', 0.08)
('2010-01-02', 0.0)
('2010-01-03', 0.0)
('2010-01-04', 0.0)
('2010-01-06', None)
('2010-01-07', 0.06)
('2010-01-08', 0.0)
('2010-01-09', 0.0)
('2010-01-10', 0.0)
('2010-01-11', 0.01)
('2010-01-12', 0.0)
('2010-01-14', 0.0)
('2010-01-15', 0.0)
('2010-01-16', 0.0)
('2010-01-17', 0.0)
('2010-01-18', 0.0)
('2010-01-19', 0.0)
('2010-01-20', 0.0)
('2010-01-21', 0.0)
('2010-01-22', 0.0)
('2010-01-23', 0.0)
('2010-01-24', 0.01)
('2010-01-25', 0.0)
('2010-01-26', 0.04)
('2010-01-27', 0.12)
('2010-01-28', 0.0)
('2010-01-30', None)
('2010-01-31', 0.03)
('2010-02-01', 0.01)
('2010-02-03', None)
('2010-02-04', 0.01)
('2010-02-05', 0.0)
('2010-02-06', 0.0)
('2010-02-07', 0.0)
('2010-02-08', 0.0)
('2010-02-09', 0.0)
('2010-02-11', 0.0)
('2010-02-12', 0.02)
('2010-02-13', 0.01)
('2010-02-14', 0.0)
('2010-02-15', 0.0)
('2010-02-16', 0.0)
('2010-02-17', 0.0)
('2010-02-19', None)
('2010-02-20', 0.03)
('2010-02-21', 0.0)
('2010-02-22', 0.0)
('2010-02-23', 0.0)
('2010-02-24', 0.0)
('20[...]<jupyter_text>Exploratory Climate Analysis<jupyter_code>june_10start = dt.date(2010,6,1) 
june_10end = dt.date(2010,6,30)

alljune_10 =(june_10end-june_10start)
alljune_10
# use extract inside filter to 
results = []
# Perform a query to retrieve the data and precipitation scores
results = session.query(Measurement.date, Measurement.tobs ,Station.station).\
filter(extract('month',Measurement.date) == 6).all()
#print(results)
# Save the query results as a Pandas DataFrame and set the index to the date column
june_df = pd.DataFrame(results, columns=['date','temp','station'])
#inplace means mod the df where it is dont make a new one (which would be =false)
#june_2010_df.set_index(df['date'], inplace=True)
# Sort the dataframe by date
#june_2010_df = df.sort_index()
june_df.describe()

#june_df.plot()
# December temp averages 

results = []
# Perform a query to retrieve the data and precipitation scores
results = session.query(Measurement.date, Measurement.tobs ,Station.station).\
filter(extract('month',Measurement.date) == 12).all()
#print(results)
# Save the query results as a Pandas DataFrame and set the index to the date column
Dec_df = pd.DataFrame(results, columns=['date','temp','station'])
#inplace means mod the df where it is dont make a new one (which would be =false)
#Dec_df_df.set_index(df['date'], inplace=True)
# Sort the dataframe by date
#june_2010_df = df.sort_index()
Dec_df.describe()
Dec_df.plot()
# Design a query to retrieve the last 12 months of precipitation data and plot the results. 
#Starting from the last data point in the database. 
june_10start = dt.date(2010,6,1) 
june_10end = dt.date(2010,6,30) 
june_11start = dt.date(2011,6,1) 
june_11end = dt.date(2011,6,30)
# Calculate the date one year from the last date in data set.
results = []
# Perform a query to retrieve the data and precipitation scores
results = session.query(Measurement.date, Measurement.prcp).\
filter(Measurement.date = june_10start).\
filter(Measurement.date <= june_10end).all()
#filter(Measurement.station == 'USC00519281').\
#filter(Measurement.date >= june_11start).\
#filter(Measurement.date <= june_11end).all()
# filter(Measurement.date >= june_12start).\
# filter(Measurement.date <= june_12end).\
# filter(Measurement.date >= june_13start).\
# filter(Measurement.date <= june_13end).\
# filter(Measurement.date >= june_14start).\
# filter(Measurement.date <= june_14end).\
# filter(Measurement.date >= june_15start).\
# filter(Measurement.date <= june_15end).\
# filter(Measurement.date >= june_16start).\
# filter(Measurement.date <= june_16end).\
# filter(Measurement.date >= june_17start).\
# filter(Measurement.date <= june_17end)

#print(results)
# Save the query results as a Pandas DataFrame and set the index to the date column
june_2010_df = pd.DataFrame(results, columns=['date','precipitation'])
#inplace means mod the df where it is dont make a new one (which would be =false)
#june_2010_df.set_index(df['date'], inplace=True)
# Sort the dataframe by date
#june_2010_df = df.sort_index()
june_2010_df.describe()
june_2010_df.plot()
# Use Pandas Plotting with Matplotlib to plot the data
june_11start = dt.date(2011,6,1) 
june_11end = dt.date(2011,6,30)
results = []
results = session.query(Measurement.date, Measurement.prcp).\
filter(Measurement.date >= june_11start).\
filter(Measurement.date <= june_11end).all()
#filter(Measurement.station == 'USC00519281').all()
print(results)
june_2011_df = pd.DataFrame(results, columns=['date','precipitation'])
june_2011_df.describe()
june_2011_df.plot()
july = dt.date(2017, 7, 1) - dt.timedelta(days=30)
july 
# Design a query to retrieve the last 12 months of precipitation data and plot the results. 
#Starting from the last data point in the database. 
prev_year = dt.date(2017, 8, 23) - dt.timedelta(days=365)
# Calculate the date one year from the last date in data set.
results = []
# Perform a query to retrieve the data and precipitation scores
results = session.query(Measurement.date, Measurement.prcp).filter(Measurement.date >= prev_year).all()
print(results)
# Save the query results as a Pandas DataFrame and set the index to the date column
df = pd.DataFrame(results, columns=['date','precipitation'])
#inplace means mod the df where it is dont make a new one (which would be =false)
df.set_index(df['date'], inplace=True)
# Sort the dataframe by date

# Use Pandas Plotting with Matplotlib to plot the data

df = pd.DataFrame(results, columns=['date','precipitation'])
df.set_index(df['date'], inplace=True)
df = df.sort_index()
print(df.to_string(index=False))

# this is per day with inches on the y and dates on the x , 
#table and columns would be good 
df.plot()
# Use Pandas to calcualte the summary statistics for the precipitation data
df.describe()
# How many stations are available in this dataset?
session.query(func.count(Station.station)).all()
# What are the most active stations?
session.query(Measurement.station, func.count(Measurement.station)).\
group_by(Measurement.station).\
order_by(func.count(Measurement.station).desc()).all()
# List the stations and the counts in descending order.

# Using the station id from the previous query, calculate the lowest temperature recorded, 
# highest temperature recorded, and average temperature most active station?
session.query(func.min(Measurement.tobs), func.max(Measurement.tobs), func.avg(Measurement.tobs)).\
filter(Measurement.station == 'USC00519281').all()
# 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
results = session.query(Measurement.tobs).\
filter(Measurement.station == 'USC00519281').\
filter(Measurement.date >= prev_year).all()
print(results)
df = pd.DataFrame(results, columns=['tobs'])
df
#df = pd.DataFrame(results, columns=['date','precipitation'])
#inplace means mod the df where it is dont make a new one (which would be =false)
#df.set_index(df['date'], inplace=True)

#columns= (['date'],['tobs']) 
#usually want somewhere between 5 and 20 bins, gives different resolutions

df.plot.hist(bins=20)
plt.tight_layout()
# Write a function called `calc_temps` that will accept start date and end date in the format '%Y-%m-%d' 
# and return the minimum, average, and maximum temperatures for that range of dates<jupyter_output><empty_output><jupyter_text>Challenge<jupyter_code># Design a query to retrieve june of precipitation data and plot the results. 
#Starting from the last data point in the database. 
june_data = dt.date(2017, 6, 1) - dt.timedelta(days=1)#dt.date(2017,6,1) # // dt.timedelta(days=30)
# Calculate the date one year from the last date in data set.
results = []
# Perform a query to retrieve the data and precipitation scores
results = session.query(Measurement.date, Measurement.tobs).\
filter(Measurement.date >= june_data).\
filter(Measurement.station == 'USC00519281').all()
print(results)
# Save the query results as a Pandas DataFrame and set the index to the date column
df = pd.DataFrame(results, columns=['date','tobs'])
#inplace means mod the df where it is dont make a new one (which would be =false)
df.set_index(df['date'], inplace=True)
# Sort the dataframe by date
df
# Use Pandas Plotting with Matplotlib to plot the data

june_data = dt.date(2017, 7, 1) - dt.date(2017,6,1)#dt.timedelta(days=30)# dt.date(2017,6,1)
june_data

print(len(results))

df.plot.hist(bins=20)
plt.tight_layout()<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.14580453046899924, 'fraction_numerical': 0.080931617568861, 'mean_word_length': 5.256819693945443, 'pattern_counts': {'":': 0, '<': 21, '<?xml version=': 0, '>': 23, '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': '3', 'dir': 'jupyter-structured-clean-dedup', 'id': '501847', 'license': 'MIT', 'n_tokens_mistral': 3779, 'n_tokens_neox': 3231, 'n_words': 950, 'path': 'climate_analysis.ipynb', 'repo_name': 'misterrustia/surfs_up', 'size': '9403'}