<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
from sqlalchemy.orm import create_session
from sqlalchemy.schema import Table, MetaData
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column, Integer, String, Float
from sqlalchemy import create_engine, inspect, func
engine = create_engine("sqlite:///hawaii2.sqlite")
conn = engine.connect()
# reflect an existing database into a new model
Base = automap_base()
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)

# Use the Inspector to explore the database and print the table names
inspector = inspect(engine)
inspector.get_table_names()

measurement_col = []

columns = inspector.get_columns('measurement')
for c in columns:
    measurement_col.append(c['name'])
    print(c['name'], c["type"])
    
    measurement_col
# top 5 measurements
engine.execute('SELECT * FROM Measurement LIMIT 5').fetchall()<jupyter_output><empty_output><jupyter_text>Exploratory Climate Analysis<jupyter_code># last date
session.query(Measurement.date).order_by((Measurement.date.desc())).first()


#today date
print(dt.date.today())
print(dt.date(2017, 8 ,23))
# Design a query to retrieve the last 12 months of precipitation data and plot the results
session.query(Measurement.date).order_by(Measurement.date.desc()).first
# Calculate the date 1 year ago from the last data point in the database
one_year = dt.date(2017,8,23) -dt.timedelta(days=365)
print(one_year)
#Perform a query to retrieve the data and precipitation scores

results = session.query(Measurement.date, Measurement.prcp).filter(Measurement.date >= one_year).all()

precipitation_last_12 = session.query(Measurement.date,Measurement.prcp).\
              filter(func.datetime(Measurement.date) >= one_year).\
                 order_by(Measurement.date).all()
precipitation_last_12
#Save the query results as a Pandas DataFrame and set the index to the date column
df = pd.DataFrame(results, columns=['date', 'precipitation'])
df.set_index(df['date'], inplace=True)
df.tail()

precipitation_pd = pd.DataFrame(precipitation_last_12)
precipitation_pd= precipitation_pd.dropna()


#Sort the dataframe by date
precipitation_pd.describe()
# date and pcp top 5
precipitation_pd.head()
#count of date and Precipitation
precipitation_pd.count()
# Create Date vs. Prcp Plot scatter plot

#precipitation_pd.date.set_index('date',inplace=True,sort_columns=True,use_index=True, legend=True,grid=True, color='g')
precipitation_pd=precipitation_pd.sort_values('date')
precipitation_pd.plot(x='date',y='prcp',rot=90)

# Set title/
plt.title("Date vs Precipitation")
# Set x axis label
#FIX DATES
plt.xlabel("Date")
# Set y axis label
plt.ylabel("Precipitation")
# Set grid line
plt.grid(linestyle='-', linewidth=1, alpha = 1.5)
plt.savefig('date_precip_12.png')
# Design a query to show how many stations are available in this dataset?
session.query(Measurement.station, func.sum(Measurement.station))\
.group_by(Measurement.station).all()

stationcount = session.query(Measurement).distinct(Measurement.station).group_by(Measurement.station).count()
stationcount
# What are the most active stations? (i.e. what stations have the most rows)?
#print('Total Station Number:',Measurement.station)
sel = [Measurement.station, func.count(Measurement.tobs)]
query = session.query(*sel).\
    group_by(Measurement.station).\
    order_by(func.count(Measurement.tobs).desc())
q_df = pd.DataFrame(query, columns=['station_id','total_count']).set_index('station_id')
store_station=q_df.index[0]
q_df
plt.savefig('total_count_df.png')
# store min,max and avg for the active station
sel= [func.min(Measurement.tobs), func.max(Measurement.tobs),func.avg(Measurement.tobs)]
query = session.query(*sel).\
     filter(Measurement.station==store_station).all()
q_df=pd.DataFrame(query,columns=['low','hgh','avg'])
q_df
#histogram of Tobs
sel=[Measurement.date, Measurement.tobs]
query = session.query(*sel).\
     filter(Measurement.station==store_station).\
     filter(func.strftime(Measurement.date)>=one_year).\
     group_by(Measurement.date).\
     order_by(func.count(Measurement.tobs).desc())
q_df=pd.DataFrame(query[:][:],columns=['date', 'tobs'])
q_df.plot.hist(bins=12, color='green')
plt.tight_layout()
plt.show()
plt.savefig('stat_temp.png')<jupyter_output><empty_output><jupyter_text>Bonus Challenge Assignment<jupyter_code># This function called `calc_temps` 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
def calc_temps(start_date, end_date):
    """TMIN, TAVG, and TMAX for a list of dates.
    
    Args:
        start_date (string): A date string in the format %Y-%m-%d
        end_date (string): A date string in the format %Y-%m-%d
        
    Returns:
        TMIN, TAVE, and TMAX
    """
    
    return session.query(func.min(Measurement.tobs), func.avg(Measurement.tobs), func.max(Measurement.tobs)).\
        filter(Measurement.date >= start_date).filter(Measurement.date <= end_date).all()

# function usage example
print(calc_temps('2012-02-28', '2012-03-05'))
# Use your previous function `calc_temps` to calculate the tmin, tavg, and tmax 
# for your trip using the previous year's data for those same dates.
prev_year_start = dt.date(2018, 1, 1) - dt.timedelta(days=365)
prev_year_end = dt.date(2018, 1, 7) - dt.timedelta(days=365)

tmin, tavg, tmax = calc_temps(prev_year_start.strftime("%Y-%m-%d"), prev_year_end.strftime("%Y-%m-%d"))[0]
print(tmin, tavg, tmax)
# Plot the results from your previous query as a bar chart. 
# Use "Trip Avg Temp" as your Title
# Use the average temperature for the y value
# Use the peak-to-peak (tmax-tmin) value as the y error bar (yerr)
fig, ax = plt.subplots(figsize=plt.figaspect(2.))
xpos = 1
yerr = tmax-tmin

bar = ax.bar(xpos, tmax, yerr=yerr, alpha=0.5, color='coral', align="center")
ax.set(xticks=range(xpos), xticklabels="a", title="Trip Avg Temp", ylabel="Temp (F)")
ax.margins(.2, .2)
# fig.autofmt_xdate()
fig.tight_layout()
fig.show()
plt.savefig('trip_avg_temp.png')
# Calculate the total amount of rainfall per weather station for your trip dates using the previous year's matching dates.
# Sort this in descending order by precipitation amount and list the station, name, latitude, longitude, and elevation
start_date = '2012-01-01'
end_date = '2012-01-07'

sel = [Measurement.station, station.name, station.latitude, 
       station.longitude, station.elevation, func.sum(Measurement.prcp)]

results = session.query(*sel).\
    filter(Measurement.station == station.station,
    func.strftime(Measurement.date) >= start_date,
    func.strftime(Measurement.date) <= end_date).\
    group_by(Measurement.station).order_by(func.sum(Measurement.prcp).desc())
q_df=pd.DataFrame(results,columns=['station_id','station_name','total_precip','lat_station','Lng_station','elevation_station']).set_index('station_id')
q_df
# Create a query that will calculate the daily normals 
#disp# (i.e. the averages for tmin, tmax, and tavg for all historic data matching a specific month and day)

def daily_normals(date):
    """Daily Normals.
    
    Args:
        date (str): A date string in the format '%m-%d'
        
    Returns:
        A list of tuples containing the daily normals, tmin, tavg, and tmax
    
    """
    
    sel = [func.min(Measurement.tobs), func.avg(Measurement.tobs), func.max(Measurement.tobs)]
    return session.query(*sel).filter(func.strftime("%m-%d", Measurement.date) == date).all()
    
daily_normals("01-01")
# calculate the daily normals for your trip
# push each tuple of calculations into a list called `normals`

# Set the start and end date of the trip
trip_start = '2018-01-01'
trip_end = '2018-01-07'
# Use the start and end date to create a range of dates
trip_dates = pd.date_range(trip_start, trip_end, freq='D')
# Stip off the year and save a list of %m-%d strings
trip_month_day = trip_dates.strftime('%m-%d')
# Loop through the list of %m-%d strings and calculate the normals for each date
normals = []
for date in trip_month_day:
    normals.append(*daily_normals(date))
    
normals
# Load the previous query results into a Pandas DataFrame and add the `trip_dates` range as the `date` index
df_trip = pd.DataFrame(normals, columns=['tmin', 'tavg', 'tmax'])
df_trip['date'] = trip_dates
df_trip.set_index(['date'],inplace=True)
df_trip.head()
# Plot the daily normals as an area plot with `stacked=False`
df_trip.plot(kind='area', stacked=False, x_compat=True, alpha=.2)
plt.tight_layout()
plt.savefig('area_count_df.png')<jupyter_output><empty_output>
StarcoderdataJupyterStructuredCleanDedup