<jupyter_start><jupyter_text>Connect to the Database<jupyter_code>engine = create_engine("sqlite:///Resources/hawaii.sqlite")

# reflect an existing database into a new model
Base = automap_base()
# reflect the tables
Base.prepare(engine, reflect=True)

# 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)
# Inspect to see the column names for each reflected table
ins_meas = inspect(Measurement)
meas_col = [col.key for col in ins_meas.mapper.column_attrs]
ins_station = inspect(Station)
station_col = [col.key for col in ins_station.mapper.column_attrs]

# Print the columns
print('measurement columns =', meas_col)
print('station columns =', station_col)<jupyter_output>measurement columns = ['id', 'station', 'date', 'prcp', 'tobs']
station columns = ['id', 'station', 'name', 'latitude', 'longitude', 'elevation']<jupyter_text>Deliverable 1: Determine the Summary Statistics for Temperatures in June<jupyter_code># 2. Write a query that filters the Measurement table to retrieve the 
# temperatures for the month of June. 
jun_temps = (session.query(Measurement.tobs)
                     .filter(extract('month', Measurement.date) == 6)
                     .all())
#  3. Convert the June temperatures to a list.
jun_temps = list(np.ravel(jun_temps))
# 4. Create a DataFrame from the list of temperatures for the month of June.
jun_temps_df = pd.DataFrame(jun_temps, columns=['June Temps'])
# 5. Calculate and print out the summary statistics for the June temperature
# DataFrame.
jun_temps_df.describe()<jupyter_output><empty_output><jupyter_text>Deliverable 2: Determine the Summary Statistics for Temperatures in December<jupyter_code># 6. Write a query that filters the Measurement table to retrieve the
# temperatures for the month of December.
dec_temps = (session.query(Measurement.tobs)
                         .filter(extract('month', Measurement.date) == 12)
                         .all())
# 7. Convert the December temperatures to a list.
dec_temps = list(np.ravel(dec_temps))
# 8. Create a DataFrame from the list of temperatures for the month of December. 
dec_temps_df = pd.DataFrame(dec_temps, columns=['December Temps'])
# 9. Calculate and print out the summary statistics for the December
# temperature DataFrame.
dec_temps_df.describe()<jupyter_output><empty_output><jupyter_text>Additional Queries  Additional Query 1: Average Daily Temperture for June and December<jupyter_code># First create two subqueries containing the temperature and precipitation data 
# for each month grouped by the day of the month.
# June average daily temperatures and precipitations
jun_subq = (session.query(extract('day', Measurement.date).label('day'),
                          (func.sum(Measurement.tobs)/func.count(Measurement.tobs)).label('j_temp'),
                          (func.sum(Measurement.prcp)/func.count(Measurement.prcp)).label('j_prcp'))
                   .filter(extract('month', Measurement.date) == 6)
                   .group_by(extract('day', Measurement.date))
                   .subquery('jun'))
# December average daily temperatures and precipitations
dec_subq = (session.query(extract('day', Measurement.date).label('day'),
                          (func.sum(Measurement.tobs)/func.count(Measurement.tobs)).label('d_temp'),
                          (func.sum(Measurement.prcp)/func.count(Measurement.prcp)).label('d_prcp'))
                   .filter(extract('month', Measurement.date) == 12)
                   .group_by(extract('day', Measurement.date))
                   .subquery('dec'))

# Join the two subqueries on the day column
daily_averages = (session.query(dec_subq.c.day,
                                jun_subq.c.j_temp,
                                dec_subq.c.d_temp,
                                jun_subq.c.j_prcp,
                                dec_subq.c.d_prcp)
                          .outerjoin(jun_subq, dec_subq.c.day == jun_subq.c.day)
                          .all())

# Create a DataFrame from the daily_averages query
daily_averages_df = pd.DataFrame(daily_averages, columns=['day',
                                                          'jun_temp',
                                                          'dec_temp',
                                                          'jun_prcp',
                                                          'dec_prcp'])

# Use the day of the month as the index
daily_averages_df.set_index(['day'], inplace=True)<jupyter_output><empty_output><jupyter_text>Average Daily Temperature Plot<jupyter_code># Average Daily Temperature (ADT) plot
ax = daily_averages_df[['jun_temp','dec_temp']].plot(title='Average Daily Temperature',
                                                     xlabel='Day of Month',
                                                     ylabel='Temperature (°F)')
ax.legend(labels=['June', 'December'])
fig = ax.get_figure()
fig.patch.set_facecolor('white')
# fig.add_artist(Rectangle((0, 0), width=1, height=1, facecolor='none', edgecolor='black'))
fig.savefig('Resources/avg_daily_temperature.svg')<jupyter_output><empty_output><jupyter_text>Average Daily Precipitation Plot<jupyter_code># Average Daily Precipitation (ADT) plot
ax = daily_averages_df[['jun_prcp','dec_prcp']].plot(title='Average Daily Precipitation',
                                                     xlabel='Day of Month',
                                                     ylabel='Precipitation (in)')
ax.legend(labels=['June', 'December'])
fig = ax.get_figure()
fig.patch.set_facecolor('white')
# fig.add_artist(Rectangle((0, 0), width=1, height=1, facecolor='none', edgecolor='black'))
fig.savefig('Resources/avg_daily_precipitation.svg')<jupyter_output><empty_output><jupyter_text>Additional Query 2: Determine the Summary Statistics for Precipitation in June<jupyter_code># Filter the Measurement table to retrieve the precipitation for the month of 
# June. 
jun_prcp = (session.query(Measurement.prcp)
                    .filter(extract('month', Measurement.date) == 6)
                    .all())

# Convert the June precipitations to a list.
jun_prcp = list(np.ravel(jun_prcp))

# Create a DataFrame from the list of precipitations for the month of June.
jun_prcp_df = pd.DataFrame(jun_prcp, columns=['June Precipitation'])

# Calculate and print out the summary statistics for the June precipitation
# DataFrame.
jun_prcp_stats_df = jun_prcp_df.describe()
jun_prcp_stats_df<jupyter_output><empty_output><jupyter_text>Additional Query 3: Determine the Summary Statistics for Precipitation in December<jupyter_code># Filter the Measurement table to retrieve the precipitation for the month of 
# December. 
dec_prcp = (session.query(Measurement.prcp)
                        .filter(extract('month', Measurement.date) == 12)
                        .all())

# Convert the December precipitations to a list.
dec_prcp = list(np.ravel(dec_prcp))

# Create a DataFrame from the list of precipitations for the month of December.
dec_prcp_df = pd.DataFrame(dec_prcp, columns=['December Precipitation'])

# Calculate and print out the summary statistics for the December precipitation
# DataFrame.
dec_prcp_stats_df = dec_prcp_df.describe()
dec_prcp_stats_df<jupyter_output><empty_output><jupyter_text>Combine Table AQ2 and AQ3 for Reporting<jupyter_code># Use the indexes of each DataFrame as the keys for merging
prcp_stats_df = jun_prcp_stats_df.merge(dec_prcp_stats_df, left_index=True, right_index=True)
prcp_stats_df<jupyter_output><empty_output><jupyter_text>Additional Query 4: Min/Max Temperature for June and December<jupyter_code># June min/max (mm) temperatures for all years
jun_mm_temp = (session.query(extract('day', Measurement.date),
                             func.max(Measurement.tobs),
                             func.min(Measurement.tobs))
                      .filter(extract('month', Measurement.date) == 6)
                      .group_by(extract('day', Measurement.date))
                      .all())

# Create a DataFrame
jun_mm_temp_df = pd.DataFrame(jun_mm_temp, columns=['day',
                                                    'jun_max_temp',
                                                    'jun_min_temp'])

# Set the index to the day of the month
jun_mm_temp_df.set_index(['day'], inplace=True)

# Plot the min/max values
ax = jun_mm_temp_df.plot(title='Min/Max Daily Temperature in June',
                         xlabel='Day of Month',
                         ylabel='Temperature (°F)')
ax.legend(labels=['Maximums', 'Minimums'])
fig = ax.get_figure()
fig.patch.set_facecolor('white')
# fig.add_artist(Rectangle((0, 0), width=1, height=1, facecolor='none', edgecolor='black'))
fig.savefig('Resources/min_max_daily_temperature_june.svg')
# December min/max (mm) temperatures for all years
dec_mm_temp = (session.query(extract('day', Measurement.date),
                             func.max(Measurement.tobs),
                             func.min(Measurement.tobs))
                      .filter(extract('month', Measurement.date) == 12)
                      .group_by(extract('day', Measurement.date))
                      .all())

# Create a DataFrame
dec_mm_temp_df = pd.DataFrame(dec_mm_temp, columns=['day',
                                                    'dec_max_temp',
                                                    'dec_min_temp'])

# Set the index to the day of the month
dec_mm_temp_df.set_index(['day'], inplace=True)

# Plot the min/max values
ax = dec_mm_temp_df.plot(title='Average Daily Temperature in December',
                         xlabel='Day of Month',
                         ylabel='Temperature (°F)')
ax.legend(labels=['Maximums', 'Minimums'])
fig = ax.get_figure()
fig.patch.set_facecolor('white')
# fig.add_artist(Rectangle((0, 0), width=1, height=1, facecolor='none', edgecolor='black'))
fig.savefig('Resources/min_max_daily_temperature_december.svg')<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.10792731629392971, 'fraction_numerical': 0.00439297124600639, 'mean_word_length': 2.8438219493476593, 'pattern_counts': {'":': 0, '<': 40, '<?xml version=': 0, '>': 40, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 24, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '10', 'dir': 'jupyter-structured-clean-dedup', 'id': '391088', 'license': 'Apache-2.0', 'n_tokens_mistral': 3027, 'n_tokens_neox': 2822, 'n_words': 703, 'path': 'SurfsUp_Challenge.ipynb', 'repo_name': 'cdpeters/surf-weather-sqlalchemy-flask', 'size': '10016'}