Data Visualization with NOAA Climate Data Set

import sqlite3

# make a connection
conn = sqlite3.connect("noaa.db")

import pandas as pd

url = "https://raw.githubusercontent.com/PhilChodrow/PIC16B/master/datasets/noaa-ghcn/station-metadata.csv"
stations = pd.read_csv(url)
stations.head()

stations.to_sql("stations", conn, if_exists = "replace", index = False)

url = "https://raw.githubusercontent.com/mysociety/gaze/master/data/fips-10-4-to-iso-country-codes.csv"
countries = pd.read_csv(url)
# change the column "Name" for easier reference later on
countries = countries.rename(columns = {"Name": "Country"})
countries.head()

# we don't need ISO column, so we will drop it
countries = countries.drop(["ISO 3166"], axis = 1)
# rename the column with name with space in it
countries = countries.rename(columns = {"FIPS 10-4": "FIPS"})
countries.head()

countries.to_sql("countries", conn, if_exists = "replace", index = False)

temperatures = pd.read_csv("temps.csv")
temperatures.head()

# set index
temperatures = temperatures.set_index(keys = ["ID", "Year"])
# stack data based on months
temperatures = temperatures.stack()
# reset index
temperatures = temperatures.reset_index()
# let's take a look
temperatures.head()

# rename some of the columns
temperatures = temperatures.rename(columns = {"level_2": "Month", 0: "Temp"})
# change VALUE (of month) to integer represent month in a year
temperatures["Month"] = temperatures["Month"].str[5:].astype(int)
# add a FIPS column for querying database later on
temperatures["FIPS"] = temperatures["ID"].str[:2]
# convert temperature to (C)
temperatures["Temp"] = temperatures["Temp"] / 100
temperatures.head()

temperatures.to_sql("temperatures", conn, if_exists = "replace", index = False)

cursor = conn.cursor()
cursor.execute("SELECT name FROM sqlite_master WHERE type='table'")
print(cursor.fetchall())

conn.close()

def query_climate_database(country, year_begin, year_end, month):
    """
    FUNCTION
    --------
    Generate a dataframe which provides us with information of stations,
    latitude, longitude, and average temperatures from a set of conditions
    set by the user
    
    PARAMETERS
    ----------
    country   : name of a country (string)
    year_begin: starting year (int)
    year_end  : ending year (int)
    month     : the only month included in the dataframe
    
    RETURN
    ------
    A dataframe filled with info specified by the user along with the
    corresponding data/columns (station, latitude, longitude, country,
    year, month, temperature)
    """
    
    cmd = \
    f"""
    SELECT S.name, S.latitude, S.longitude, C.country, T.year, T.month, T.temp
    FROM temperatures T
    LEFT JOIN stations S ON T.id = S.id
    LEFT JOIN countries C ON T.fips = C.fips
    WHERE C.country = ?
        AND T.year BETWEEN ? AND ?
        AND month = ?
    """
    # remember to open the connection before accessing the database
    conn = sqlite3.connect("noaa.db")
    # query the database
    df = pd.read_sql_query(cmd, conn, params=(country, year_begin, year_end, month))
    # close the connection
    conn.close()
    return df

query_climate_database(country = 'India', 
                       year_begin = 1980, 
                       year_end = 2020,
                       month = 1)

from sklearn.linear_model import LinearRegression
import numpy as np

def coef(df):
    """
    Estimate the coefficient, which is also the estimate of the average
    yearly change in temperature.
    -------------------------------
    Input: a pandas dataframe
    Ouput: the linear coefficient
    """
    
    # X needs to be a pandas dataframe
    X = df[["Year"]]
    # y needs to be a pandas series
    y = df["Temp"]
    LR = LinearRegression()
    LR.fit(X, y)
    return np.round(LR.coef_[0], 3)

# choose a colormap
color_map_1 = px.colors.sequential.Turbo

fig = temperature_coefficient_plot("India", 1980, 2020, 1, 
                                   min_obs = 10,
                                   zoom = 2.4,
                                   mapbox_style = "carto-positron",
                                   color_continuous_scale = color_map_1)
fig.show()

def query_climate_database_3d(year, lat_min, lat_max, lon_min, lon_max):
    """
    Generate a pandas dataframe by querying the database with sql
    restricted to a certain set of conditions set by the user.
    ------------
    Input
    year   : year (int)
    lat_min: minimum latitude (float)
    lat_max: maximum latitude (float)
    lon_min: minimum longitude (float)
    lon_max: maximum longitude (float)
    
    Output
    A pandas dataframe that satisfies those conditions
    """
    cmd = \
    f"""
    SELECT S.latitude, S.longitude, T.temp, T.month
    FROM temperatures T
    LEFT JOIN stations S ON T.id = S.id
    WHERE T.year = ?
        AND S.latitude BETWEEN ? AND ?
        AND S.longitude BETWEEN ? AND ?
    """
    # open the connection
    conn = sqlite3.connect("noaa.db")
    # query the database
    df = pd.read_sql_query(cmd, conn, 
                           params=(year, lat_min, lat_max, lon_min, lon_max))
    # close the connection
    conn.close()
    return df

color_map_2 = px.colors.diverging.Portland
fig = geo_temp_3d_plot(2000, -8, 8, -8, 8,
                 opacity = .7,
                 height = 530,
                 color_continuous_scale = color_map_2)
fig.show()

def query_station(country, station, year_begin, year_end):
    """
    Generate a panadas dataframe of a specified station for a specified
    period of time
    --------------
    Input
    country   : name of a country (string)
    station   : name of a station within that country (string)
    year_begin: beginning year (int)
    year_end  : ending year (int)
    
    Output
    A pandas dataframe that satisfies those conditions
    """
    b = pd.DataFrame()
    # open the connection
    conn = sqlite3.connect("noaa.db")
    # 12 months in a year
    for i in range(1, 13):
        a = query_climate_database(country, year_begin, year_end, i)
        b = pd.concat([b, a])
    b = b[b["NAME"] == station].sort_values(by=["Year", "Month"], ignore_index=True)
    # close the connection
    conn.close()
    return b

import seaborn as sns
sns.set_theme(style="dark")
def mult_line_plot_station(country, station, year_begin, year_end):
    """
    FUNCTION
    --------
    Construct a group of plots of a station's temperatures within
    a given period of time
    
    PARAMETERS
    ----------
    country   : name of a country to inspect (string)
    station   : name of a station in that country (string)
    year_begin: beginning year (int)
    year_end  : ending year (int)
    
    RETURN
    ------
    A facet grids that showcases temperatures of the station
    within the given time period
    """
    
    # get the data
    s = query_station(country, station, year_begin, year_end)
    # plot the relational plot for each year
    g = sns.relplot(data=s, x="Month", y="Temp", col="Year", hue="Year",
                    kind="line", palette="husl", linewidth=2.5, zorder=5,
                    col_wrap=3, height=2.2, aspect=1.6, legend=False)
    
    # plot the the other year in the background for comparison
    for year, ax in g.axes_dict.items():
        # denote year (bold) at the top right of the plot
        ax.text(.81, .9, year, transform=ax.transAxes, fontweight="bold")
        # Plot every year's time series in the background
        sns.lineplot(data=s, x="Month", y="Temp", units="Year",
                     estimator=None, color=".75", linewidth=1, ax=ax)

    # set x-tick labels
    ax.set_xticks(ax.get_xticks()[::2])

    # add title, axis label and annotation
    g.fig.suptitle("Temperatures of " + station + " station"
                   + " from " + str(year_begin) + " to " + str(year_end))
    g.set_titles("")
    g.set_axis_labels("Month", "Temperature (C)")
    g.tight_layout()

mult_line_plot_station("Vietnam", "TAN_SON_HOA", 1999, 2010)