import pandas as pd
temperatures = pd.read_csv("/Users/xuyan/Desktop/temps.csv")
temperatures.head()

# this is the countries data
countries_url = "https://raw.githubusercontent.com/mysociety/gaze/master/data/fips-10-4-to-iso-country-codes.csv"
countries = pd.read_csv(countries_url)
countries.head()

# this is the stations data
stations_url = "https://raw.githubusercontent.com/PhilChodrow/PIC16B/master/datasets/noaa-ghcn/station-metadata.csv"
stations = pd.read_csv(stations_url)
stations.head()

import sqlite3
conn = sqlite3.connect("temps.db")
# store the path for temperature csv file
temps = "/Users/xuyan/Desktop/temps.csv"
df_iter = pd.read_csv(temps, chunksize = 100000)
df = df_iter.__next__()

# reshape function:
def prepare_df(df):
    df = df.set_index(keys=["ID", "Year"])
    df = df.stack()
    df = df.reset_index()
    df = df.rename(columns = {"level_2"  : "Month" , 0 : "Temp"})
    df["Month"] = df["Month"].str[5:].astype(int)
    df["Temp"]  = df["Temp"] / 100
    return(df)

# reshaping process:
df = prepare_df(df)
df.head()

# 1. write temperature dataset to database
df_iter = pd.read_csv(temps, chunksize = 100000)
for df in df_iter:
    df = prepare_df(df)
    df.to_sql("temperatures", conn, if_exists = "append", index = False)

# 2. write stations dataset to database
stations.to_sql("stations", conn, if_exists = "replace", index = False)

# 3. write countries dataset to database
countries.to_sql("countries", conn, if_exists = "replace", index = False)

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

cursor.execute("SELECT sql FROM sqlite_master WHERE type='table';")

for result in cursor.fetchall():
    print(result[0])

def query_climate_database(country, year_begin, year_end, month):
    
    """
    function returning a dataset of temperature readings for
    the specified country, in the specified date range, in
    the specified month of the year.
    
    Input
    --------
    country: string, tell which country for database to return
    year_begin: int, earliest year to be returned
    year_end: int, last year to be returned
    month: int, month of the year to be returned
    
    Output
    --------
    out: dataframe of temperature readings for the specified
        country, in the specified date range, in the specified
        month of the year.
    """
    
    # Write SQL command to satisfy with the output type
    # SELECT: necessay column names
    # From & LEFT JOIN * 2: "merge" the three datasets in form of temperature dataset
    # WHERE: specified output places
    cmd = \
    f"""
    SELECT S.name, S.latitude, S.longitude, C.Name, T.year, T.month, T.temp
    FROM temperatures T
    LEFT JOIN stations S ON T.id = S.id
    LEFT JOIN countries C ON SUBSTRING(T.id, 1, 2) = C.`FIPS 10-4`
    WHERE C.name="{country}" AND T.month={month} AND T.year>={year_begin} AND T.year<={year_end} 
    """
    
    # Write dataset from SQL Query
    out = pd.read_sql_query(cmd, conn)
    
    return out

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

from sklearn.linear_model import LinearRegression
from plotly import express as px
from matplotlib import pyplot as plt

def coef(data_group):
    x = data_group[["Year"]] # 2 brackets because X should be a df
    y = data_group["Temp"]   # 1 bracket because y should be a series
    LR = LinearRegression()
    LR.fit(x, y)
    return LR.coef_[0]

def temperature_coefficient_plot(country, year_begin, year_end, month, min_obs, **kwargs):
    
    """
    plot heat-colored map of stations in a specific country
    and specified month, with specified time interval
    
    Input
    --------
    country: string, tell which country for database to return
    year_begin: int, earliest year to be returned
    year_end: int, last year to be returned
    month: int, month of the year to be returned
    min_obs: the minimum years of observations
    **kwargs: random keyword arguments to be applied in the plotting
    
    Output
    --------
    The temperature plot 
    """
    
    # 1. write desired dataset from database with query function
    df = query_climate_database(country, year_begin, year_end, month)
    
    # 2. only keep stations with years more than min_obs
    # store the number of years considered as year_num variable for each row entry
    num_years = df.groupby(["NAME"])["Year"].transform("count")
    # cut the dataset with those stations only greater than min_obs
    df = df[num_years >= min_obs]
    
    # 3. get yearly temperature average increase dataset
    yearly_temp_increase = df.groupby(["NAME", "Month"]).apply(coef).reset_index()
    # rename column as yearly_temperature_increase
    yearly_temp_increase = yearly_temp_increase.rename(columns={0: "Estimated Yearly Increase (°C)"})
    
    # 4. append geographic spotpoints (LATI & LONG)
    # create temporary dataset with only geographic INFO
    spots = df[["NAME", "LATITUDE", "LONGITUDE"]].drop_duplicates()
    # reset indexing
    spots = spots.reset_index().drop(["index"], axis = 1)
    # append spotpoints locations by merging two datasets
    final = pd.merge(yearly_temp_increase, spots, on = ["NAME"])
    
    # 5. create the graph
    fig = px.scatter_mapbox(final, hover_name = "NAME", lat = "LATITUDE", lon = "LONGITUDE",
                            color = "Estimated Yearly Increase (°C)",
                            **kwargs)
    return fig

color_map = px.colors.diverging.RdGy_r # choose a colormap
fig = temperature_coefficient_plot("India", 1980, 2020, 1, 
                                   min_obs = 10,
                                   zoom = 2,
                                   mapbox_style="carto-positron",
                                   color_continuous_scale=color_map)
fig.show()

import seaborn as sns

def yearly_time_series_temperature(country, year_begin, year_end):
    """
    plot yearly time series plot of stations in a specific country
    with specified month, within a specified time interval (year)
    **mark: suppose the yearly temperature counted as January
    
    Input
    --------
    country: string, tell which country for database to return
    year_begin: int, earliest year to be returned
    year_end: int, last year to be returned
    
    Output
    --------
    The yearly time series plot
    """
    
    # write desired dataset from database with query function
    df = query_climate_database(country, year_begin, year_end, 1)
    # datetime transform (lecture note)
    df["Date"] = df["Year"].astype(str) + "-" + df["Month"].astype(str)
    df["Date"] = pd.to_datetime(df["Date"])
    
    # set title name:
    title_str = "Time Series of Yearly Temperature of " + country + ", year " + str(year_begin) + " - " + str(year_end)

    # create and set figures
    fig = sns.lineplot(data = df, x = "Date", y = "Temp", hue = "NAME")
    # switch legend place
    fig.legend(bbox_to_anchor= (1.03, 1))
    # set title, names of axis
    fig.set(xlabel = 'Year', ylabel = 'Temperature (°C)',
           title = title_str)   

    return fig

yearly_time_series_temperature("Switzerland", 2000, 2020)

from matplotlib import pyplot as plt
import numpy as np

# z-score function def
def z_score(x):
    m = np.mean(x)
    s = np.std(x)
    return (x - m)/s

def station_anomalies_plot(country, year_begin, year_end, station):
    
    """
    plot station temperature time series together with anomalies
    within the given period, need to know country of the station
    
    Input
    --------
    country: string, tell which country for database to return
    year_begin: int, earliest year to be returned
    year_end: int, last year to be returned
    station: string, tell which station to look at
    
    Output
    --------
    The temperature time series of a given station with anomalies
    """
    
    # write desired dataset from database with query function
    df = query_climate_database(country, year_begin, year_end, 1)
    # datetime transform (lecture note)
    df["Date"] = df["Year"].astype(str) + "-" + df["Month"].astype(str)
    df["Date"] = pd.to_datetime(df["Date"])
    
    # calculate z-score and find anomalies
    df["z"] = df.groupby(["NAME", "Month"])["Temp"].transform(z_score)
    anomalies = df[np.abs(df["z"]) > 2]
    
    #plotting
    # create plot
    fig = plt.subplots(figsize = (15, 4))
    # time series line plot
    sns.lineplot(data = df[df["NAME"] == station], 
                 x = "Date",
                 y = "Temp",
                 color = "lightgrey")
    # dot plotting anomalies
    sns.scatterplot(data = anomalies[anomalies["NAME"] == station], 
                    x = "Date", 
                    y = "Temp", 
                    zorder = 100, # show the dots on top
                    hue = "z",
                    s = 30, 
                    palette = "vlag")
    # set title
    plt.gca().set(title = f"Temperature Anomalies at {station}, year {year_begin} - {year_end}")
    sns.despine()

station_anomalies_plot("Switzerland", 1960, 2020, "SAENTIS")

conn.close()