import pandas as pd
import sqlite3

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

stations = pd.read_csv('station-metadata.csv')
stations.head()

countries = pd.read_csv('countries.csv')
countries.head()

# whitespaces in column names are bad for SQL
countries = countries.rename(columns= {"FIPS 10-4": "FIPS_10-4"})
countries = countries.rename(columns= {"ISO 3166": "ISO_3166"})
countries.head()

# open a connection to temps.db 
conn = sqlite3.connect("temps.db")
temps.to_sql("temperatures", conn, if_exists="replace", index=False)
countries.to_sql("countries", conn, if_exists="replace", index=False)
stations.to_sql("stations", conn, if_exists="replace", index=False)
# close your connection
conn.close()

def query_climate_database(country, year_begin, year_end, month):
    conn = sqlite3.connect("temps.db")
    cmd = \
    """
    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(S.id,1,2) = C."fips_10-4"
    WHERE C.name = ? AND (T.year BETWEEN ? AND ?) AND T.month = ? 
    """
    df = pd.read_sql_query(cmd, conn, params=(country, year_begin, year_end, month))
    df = df.rename(columns= {"Name": "Country"})
    conn.close()
    return df
    

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

from sklearn.linear_model import LinearRegression
def coef(data_group):
    """
    This function is to find the slope of the line and that 
    corresponds to "average change in temp per year".
    """
    X = data_group[["Year"]]
    y = data_group["Temp"]
    LR = LinearRegression()
    LR.fit(X, y)
    slope = LR.coef_[0]
    return slope

from plotly import express as px
import numpy as np
def temperature_coefficient_plot(country, year_begin, year_end, month, min_obs, **kwargs):
    df=query_climate_database(country, year_begin, year_end, month)
    df = df[df.groupby(["NAME"])["Year"].transform(len) >= min_obs]
    df_1 = df.groupby(["NAME"])["LATITUDE"].aggregate(np.mean)
    df_2 = df.groupby(["NAME"])["LONGITUDE"].aggregate(np.mean)
    coefs = df.groupby(["NAME"]).apply(coef)
    df_3 = pd.DataFrame(coefs)
    df_4 = pd.concat([df_1,df_2,df_3], axis = 1)
    df_4.rename(columns = {0: "Estimated Yearly Increase (C)"}, inplace = True)
    df_4["Estimated Yearly Increase (C)"] = round(df_4["Estimated Yearly Increase (C)"],4)
    month_dict = {1:"January", 2:"February", 3:"March", 4:"April", 5:" May", 6:"June", 7:"July", 8:"August", 9:"September", 10:"October", 11:"November", 12:"December"}
    fig = px.scatter_mapbox(df_4, # data for the points you want to plot
                        lat = "LATITUDE", # column name for latitude informataion
                        lon = "LONGITUDE", # column name for longitude information
                        hover_name = "Estimated Yearly Increase (C)", # what's the bold text that appears when you hover over
                        color = "Estimated Yearly Increase (C)",
                        **kwargs)
    fig.update_layout(title = f"Estimates of yearly increase in temperature in {month_dict[1]} <br> for stations in {country}, years {year_begin} - {year_end}")
    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()

def query_climate_database_2(year_begin, year_end):
    conn = sqlite3.connect("temps.db")
    cmd = \
    """
    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(S.id,1,2) = C."fips_10-4"
    WHERE T.year BETWEEN ? AND ?
    """
    df = pd.read_sql_query(cmd, conn, params=(year_begin, year_end))
    df = df.rename(columns= {"Name": "Country"})
    conn.close()
    return df

df = query_climate_database_2(year_begin = 2015, year_end = 2020)
df

def temperature_std_plot(year_begin, year_end,  **kwargs):
    df = query_climate_database_2(year_begin, year_end)
    # to get the mean temperature for each year
    df['mean_temp'] = df.groupby(["NAME", "Year"])["Temp"].transform(np.mean)
    # to get the standard deviation for each year
    df["std"] = df.groupby(["NAME", "Year"])["Temp"].transform(np.std)
    df["std"] = round(df["std"],4)
    # drop the row that has same NAME and Year
    df_1 = df.drop_duplicates(subset=['NAME', 'Year'])
    # drop the column
    df_2 = df_1.drop(["Month","Temp"], axis=1)
    # drop the row that has NaN
    df_2 = df_2.dropna()
    fig = px.scatter_geo(df_2,
                     lat = "LATITUDE",
                     lon = "LONGITUDE",
                     color ="Country",
                     hover_name ="NAME",
                     size = "std",
                     size_max = 10,
                     animation_frame ="Year",
                     projection ="natural earth")
    fig.update_layout(title = f"The standard deviation of temperature in different country from {year_begin} to {year_end}.")
    return fig

fig = temperature_std_plot(2015, 2020)
fig.show()

def f(country, year):
    """
    This function is to get the data from the certain year in a country,
    and plot the temperature for each city in a country at a certain year.
    """
    conn = sqlite3.connect("temps.db")
    cmd = \
    """
    SELECT S.name, C.name , T.year,  T.temp, T.month
    FROM temperatures T
    LEFT JOIN stations S ON T.id = S.id
    LEFT JOIN countries C ON SUBSTRING(S.id,1,2) = C."fips_10-4"
    WHERE C.name = ? AND T.year = ? 
    """
    df = pd.read_sql_query(cmd, conn, params=(country, year))
    df = df.rename(columns= {"Name": "Country"})
    conn.close()
    fig = px.line(data_frame = df, # data that needs to be plotted
                 x = "Month", # column name for x-axis
                 y = "Temp", # column name for y-axis
                 color = "NAME", # column name for color coding
                 title = f"In {year},the temperature for each city in {country} "
                )
    return fig
fig = f(country = "Italy", year = 2020)
fig.show()

def g(country_1, country_2):
    """
    This function is to get the data from two countries,and compute 
    the mean temperature of each month in each city from these two 
    countries.Then graph the box plot.
    """
    conn = sqlite3.connect("temps.db")
    cmd = \
    """
    SELECT S.name,S.latitude, C.name , T.year,  T.temp, T.month
    FROM temperatures T
    LEFT JOIN stations S ON T.id = S.id
    LEFT JOIN countries C ON SUBSTRING(S.id,1,2) = C."fips_10-4"
    WHERE C.name IN (?,?)
    """
    df = pd.read_sql_query(cmd, conn, params=(country_1,country_2))
    df = df.rename(columns= {"Name": "Country"})
    conn.close()
    df = df.groupby(["Country","NAME","Month"])["Temp"].aggregate(np.mean)
    df = df.reset_index()
    df["Temp"] = round(df["Temp"],4)
    df = df.rename(columns= {"Temp": "mean_temp"})
    fig = px.box(df, 
             x = "Month", 
             y = "mean_temp",
             facet_row = "Country",
             width = 1000,
             height = 500,
             title = f"The mean temperature for each month in {country_1} and {country_2}")
    return fig
fig = g(country_1 = "Italy", country_2 = "Japan")
fig.show()