<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
# create engine to hawaii.sqlite
engine = create_engine("sqlite:///../sqlalchemy-challenge/Resources/hawaii.sqlite")
# reflect an existing database into a new model
Base = automap_base()
Base.prepare(engine, reflect=True)

# reflect the tables
Base.classes.keys()
# Save references to each table
Station = Base.classes.station
Measurement = Base.classes.measurement
# Create our session (link) from Python to the DB
session = Session(engine)<jupyter_output><empty_output><jupyter_text>Exploratory Precipitation Analysis<jupyter_code>#explore columns for 'measurment' dataset
from sqlalchemy import inspect
engine.execute('SELECT * FROM measurement LIMIT 5').fetchall()
inspector = inspect(engine)
columns = inspector.get_columns('measurement')
for c in columns:
    print(c['name'], c["type"])


#explore columns for 'station' dataset
from sqlalchemy import inspect
engine.execute('SELECT * FROM station LIMIT 5').fetchall()
inspector = inspect(engine)
columns = inspector.get_columns('station')
for c in columns:
    print(c['name'], c["type"])
# Find the most recent date in the data set.
rec_date = session.query(Measurement.date).order_by(Measurement.date.desc()).first()
rec_date
# 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.

query_date = dt.date(2017, 8, 23) - dt.timedelta(days=365)
query_date 
# Design a query to retrieve the last 12 months of precipitation data and plot the results. 

# Perform a query to retrieve the data and precipitation scores

result = session.query(Measurement.date, Measurement.prcp).filter(Measurement.date > '2016-08-23').\
        order_by(Measurement.date).all()

# Save the query results as a Pandas DataFrame and set the index to the date column
precip_df = pd.DataFrame(result)
precip_df.set_index("date", inplace = True)

# Sort the dataframe by date
precip_sort_df = precip_df.sort_index(ascending=True)
precip_nonan_df = precip_sort_df.dropna(how = 'any')

precip_nonan_df

# Use Pandas Plotting with Matplotlib to plot the data
#drop NaN and filter values > 0.2 to have a cleaner graph 
precip_nonzero_df = precip_nonan_df.loc[precip_nonan_df["prcp"] > 0.2,:]
precip_clean_df = precip_nonzero_df.dropna(how = 'any')
precip_clean_df

ax = precip_clean_df.plot(kind = "bar", figsize = (10,6), color = "g", legend=False, width  = 3)

# Make most of the ticklabels empty so the labels don't get too crowded
ticklabels = ['']*len(precip_clean_df.index)

# Every 40th ticklable shows the year, month and day
ticklabels[::40] = precip_clean_df.index[::40]

ax.xaxis.set_major_formatter(ticker.FixedFormatter(ticklabels))

plt.gcf().autofmt_xdate()
plt.xticks(fontsize=11, rotation = 90)
plt.yticks(fontsize=11)

plt.tight_layout()
plt.ylabel("Inches")
plt.title("Past 12 months precipitation data", fontsize = 13, fontweight = 'bold')

plt.savefig("../sqlalchemy-challenge/Images/precipitation.png")
plt.show()

# Use Pandas to calcualte the summary statistics for the precipitation data
precip_nonan_df.agg({"prcp": ['mean','median','min', 'max', 'var','std', ]})<jupyter_output><empty_output><jupyter_text>Exploratory Station Analysis<jupyter_code># Design a query to calculate the total number of stations in the dataset
stations_count=session.query(func.count(Station.station)).all()
#print(f' number of station is {stations_count}')
stations_count

#Design a query to find the most active stations (i.e. which stations have the most rows?).
#List the stations and observation counts in descending order.
active_stn = session.query(Measurement.station, func.count(Measurement.station)).group_by(Measurement.station).\
            order_by(func.count(Measurement.station).desc()).all()

active_stn
#Which station id has the highest number of observations?
most_active = active_stn[0][0]
print(f'The station with the highest number of observations is {most_active}')
# Using the most active station id from the previous query, calculate the lowest, highest, and average temperature.
active_st_stat = session.query(Measurement.station, Station.name, func.min(Measurement.tobs),\
        func.max(Measurement.tobs), func.avg(Measurement.tobs),func.count(Measurement.tobs)).\
        filter(Measurement.station == Station.station).\
        filter(Measurement.station == most_active).\
        group_by(Measurement.station).all()

print(f'the temperature statistics for the most active station "{active_st_stat[0][1]}" is: ')
print(f' - lowest temperature is {active_st_stat[0][2]}; ')
print(f' - highest temperature is {active_st_stat[0][3]};')
print(f' - average temperature is {active_st_stat[0][4]};')
print(f' - total number of observations is {active_st_stat[0][5]} ')

# 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
active_st_temp = session.query(Measurement.tobs).\
                filter(Measurement.date > '2016-08-23').\
                filter(Measurement.station == most_active).all()
active_st_temp

# Save the query results as a Pandas DataFrame and set the index to the date column
active_st_temp_df = pd.DataFrame(active_st_temp)
#active_st_temp_df.set_index("date", inplace = True)
active_st_temp_df.hist(bins = 12)
plt.ylabel("Frequency")
plt.xlabel("Temperature")
plt.title("Past 12 months temperature frequency distribution for " + "(" + active_st_stat[0][1] +")"+ " station", fontsize = 13, fontweight = 'bold')
plt.savefig("../sqlalchemy-challenge/Images/histogram.png")
plt.show()<jupyter_output><empty_output><jupyter_text>Close session<jupyter_code># Close Session
session.close()<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.1118683510638298, 'fraction_numerical': 0.012965425531914893, 'mean_word_length': 5.633958103638368, 'pattern_counts': {'":': 1, '<': 17, '<?xml version=': 0, '>': 21, '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': '481582', 'license': 'ADSL', 'n_tokens_mistral': 1970, 'n_tokens_neox': 1853, 'n_words': 545, 'path': 'climate_starter.ipynb', 'repo_name': 'AndriyKDA/sqlalchemy-challenge', 'size': '6016'}