import pandas as pd
import numpy as np
import sqlite3

temps = pd.read_csv("temps_stacked.csv")
temps.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", "ISO 3166": "ISO_3166"})
countries.head()

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

# open a connection to temps.db so that you can 'talk' to it using python
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)
# always close your connection
conn.close()

def query_climate_database(country, year_begin, year_end, month) :
    """
    Function to return a Pandas dataframe of temperature readings for the 
    specified country, in the specified date range, in the specified month of the year,
    by querying an sqlite database
    
    Parameters
    ----------
    country: str - the country to pull for
    year_begin: str - start year for the query
    year_end: str - end year for the query
    month: str - month of the year to query for across specified year range
    
    Returns
    -------
    df - pandas dataframe constructed by function
    """
    
    conn = sqlite3.connect("temps.db") #open connection to temps database
    
    #condition the sql command to include specified columns
    cmd = \
    """
    SELECT C.name, S.name, S.latitude, S.longitude, 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 >= (?) AND T.year <= (?) AND T.month = (?)
    """
    
    #read data for our given command and connection to a pandas dataframe
    df = pd.read_sql(cmd, conn, params=(country, year_begin, year_end, month))
    conn.close() #close connection
    
    return df #return our new dataframe
    

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

import plotly.io as pio #only needed if saving figures
from plotly import express as px

from sklearn.linear_model import LinearRegression #import LR model from sklearn


def coef(data_group):
    """
    Function to return first order coefficient of model given a grouped df
    
    Parameters
    ----------
    data_group - the given grouped df to process
    
    Returns
    -------
    slope - LR coefficient for year and temp of grouped, input df
    """
    
    X = data_group[["Year"]]
    y = data_group["Temp"]
    LR = LinearRegression()
    LR.fit(X, y)
    slope = LR.coef_[0]
    return slope

def temperature_coefficient_plot(country, year_begin, year_end, month, min_obs, **kwargs):
    
    """
    Function to create an interactive, geographic plot of estimated average increase
    in temperature over a provided year range, for a given country, for a given month
    
    Parameters
    ----------
    country: str - the country to pull and plot for
    year_begin: str - start year for the query
    year_end: str - end year for the query
    month: str - month of the year to query for across specified year range
    min_obs: int - minimum number of observations for a station to be included
    kwargs: variable arguments fed to the plotly graphing function
    
    Returns
    -------
    fig - the plotly fig generated by the function
    
    """
    
    #use prior defined query function to build our df
    df = query_climate_database(country, year_begin, year_end, month)
    
    #group by station name, find size of group (# of observations at station), add col
    df["obs_count"] = df.groupby(["NAME"])["NAME"].transform(np.size)
    
    #drop all entries where the total observations for that station < minimum observations
    df = df.drop(df[df.obs_count < min_obs].index)
    
    #group our df for a given country, in a given month, over a range of years by station
    coefs = df.groupby(["NAME", "LATITUDE", "LONGITUDE"]).apply(coef)
    coefs = coefs.reset_index()
    
    #rename new column 
    coefs = coefs.rename(columns= {0: "ESTIMATED YEAR INCREASE (C)"})
    
    #round temp values
    coefs = coefs.round(4)
    
    #pass dataframe and parameters to plot the scatter map_box 
    fig = px.scatter_mapbox(coefs, #our df w/ first order coeficients for yearly temp change
                            lat = "LATITUDE", #column name for latitude informataion
                            lon = "LONGITUDE", #column name for longitude information
                            hover_name = "NAME", #hover will display station name
                            **kwargs, #all other keyword args for scatter_mapbox()
                            color = "ESTIMATED YEAR INCREASE (C)",
                            color_continuous_midpoint=0)
    
    #tweak layout and add title
    str_month = (str(month))
    str_start = (str(year_begin))
    str_end = (str(year_end))
    fig.update_layout(title = "Estimates of yearly increase in temperature in " 
                     + country + " years " + str_start + " - " + str_end) 
    
    return fig #return figure to calling scope
                       

# assumes you have imported necessary packages
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()
pio.write_html(fig,"hw1_geo1") #comment out if not saving

#now for another country
color_map = px.colors.diverging.RdGy_r # choose a colormap

fig = temperature_coefficient_plot("Mexico", 1975, 2020, 1, 
                                   min_obs = 10,
                                   zoom = 2,
                                   mapbox_style="carto-positron",
                                   color_continuous_scale=color_map)

fig.show()
pio.write_html(fig,"hw1_geo2") #comment out if not saving

#function to query accross a latitude range
def lat_query(lat_start, lat_end):
    """
    Function to return a Pandas dataframe of temperature readings for a specified
    latitude range by querying an sqlite database
    
    Parameters
    ----------
    lat_start - int: the start of the latitude range to query for
    lat_end - int: the end of the latitude range to query for
    
    Returns
    -------
    df - pandas dataframe constructed by function
    """
    conn = sqlite3.connect("temps.db") #open connection to temps database
    
    #write command 
    cmd = \
    """
    SELECT C.name, S.name, S.latitude, S.longitude, 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 S.latitude > (?) AND S.latitude < (?) 
    """
    #"""
    #SELECT S.name, S.latitude, S.longitude, T.year, T.month, T.temp
    #FROM temperatures T
    #LEFT JOIN stations S ON T.id = S.id
    #WHERE S.latitude > (?) AND S.latitude < (?)
    #"""
    
    #read data for our given command and connection to a pandas dataframe
    df = pd.read_sql(cmd, conn, params=(lat_start, lat_end))
    conn.close() #close connection
    
    return df #return our new dataframe

df = lat_query(-30, 30)
df

def temperature_coef_lat_plot(lat_start, lat_end, delta_threshold, **kwargs):
    
    """
    Function to create an interactive, geographic plot of estimated average increase
    in temperature over a provided latitude range
    
    Parameters
    ----------
    lat_start - int: the start of the latitude range to query for
    lat_end - int: the end of the latitude range to query for
    delta_threshold - int: threshold for inclusion in the plot (avoids say, every weather
    station in America from being plotted)
    kwargs: variable arguments fed to the plotly graphing function
    
    Returns
    -------
    fig - the plotly fig generated by the function
    """
    
    #use prior defined query function to build our df
    df = lat_query(lat_start, lat_end)
    
    #group our df by station
    coefs = df.groupby(["NAME", "LATITUDE", "LONGITUDE"]).apply(coef)
    coefs = coefs.reset_index()
    
    #rename new column 
    coefs = coefs.rename(columns= {0: "ESTIMATED YEAR INCREASE (C)"})
    
    #round temp values
    coefs = coefs.round(4)
    
    #drop all entries where the average yearly temp delta is below our threshold
    coefs = coefs.drop(coefs["ESTIMATED YEAR INCREASE (C)"][coefs["ESTIMATED YEAR INCREASE (C)"] < delta_threshold].index)
    
    #pass dataframe and parameters to plot the scatter map_box 
    fig = px.scatter_mapbox(coefs, #our df w/ first order coeficients for yearly temp change
                            lat = "LATITUDE", #column name for latitude informataion
                            lon = "LONGITUDE", #column name for longitude information
                            hover_name = "NAME", #hover will display station name
                            **kwargs, #all other keyword args for scatter_mapbox()
                            color = "ESTIMATED YEAR INCREASE (C)",
                           color_continuous_midpoint=0)
    
    #tweak layout and add title
    str_start = (str(lat_start))
    str_end = (str(lat_end))
    str_thres = (str(delta_threshold))
    fig.update_layout(title = "Estimates of Yearly Increase in Temperature from "
                      + str_start + " to " + str_end + " Degrees Latitude")
    
    return fig #return figure to calling scope
   
    
    #Note: I wrote one of the "coefs" above as "coef" and thus kept getting a "local 
    #variable referenced before assignment" for .apply(coef)...most baffling single letter
    #typo turned bug I've had in a long time...it thought coefs the function was the local
    #scope variable typo many lines down...I only rewrote half of it before I realized...

fig = temperature_coef_lat_plot(60, 90, .2, zoom = 1,
                                   mapbox_style="carto-positron",
                                   color_continuous_scale=color_map)

fig.show()
pio.write_html(fig,"hw1_geo3")

def temperature_heat_map(lat_start, lat_end, delta_threshold, **kwargs):
    """
    Function to create an interactive, geographic plot of estimated average increase
    in temperature over a provided latitude range using a heat density map  wherein
    spatial resolution, and by proxy the grouping of temp delta averaging, via the 
    manipulation of bin counts and latitude ranges
    
    Parameters
    ----------
    lat_start - int: the start of the latitude range to query for
    lat_end - int: the end of the latitude range to query for
    delta_threshold - int: threshold for inclusion in the plot (avoids say, every weather
    station in America from being plotted if you're looking for outliers only over a narrow
    latitude band)
    kwargs: variable arguments fed to the plotly graphing function
    
    Returns
    -------
    fig - the plotly fig generated by the function
    """
    
    #use prior defined query function to build our df
    df = lat_query(lat_start, lat_end)
    
    #group our df by station
    coefs = df.groupby(["NAME", "LATITUDE", "LONGITUDE"]).apply(coef)
    coefs = coefs.reset_index()
    
    #rename new column 
    coefs = coefs.rename(columns= {0: "ESTIMATED YEAR INCREASE (C)"})
    
    #round temp values
    coefs = coefs.round(4)
    
    #output strings
    str_start = str(lat_start)
    str_end = str(lat_end)
    
    fig = px.density_heatmap(coefs, x="LONGITUDE", y="LATITUDE", 
                             z="ESTIMATED YEAR INCREASE (C)", histfunc="avg", 
                             color_continuous_midpoint=0,
                             title="Estimates of Yearly Change in Temperature from " 
                             + str_start + " to " + str_end + " Degrees Latitude", 
                             **kwargs)
    

    return fig

#call function
fig = temperature_heat_map(-90, 90, 0, nbinsx = 72, nbinsy = 36, 
                          color_continuous_scale="rdbu_r")
#show figure
fig.show()

#comment out if you do not wish to save you fig
pio.write_html(fig,"hw4_heatdensity_map")

def temperature_heat_map_faceted(lat_start, lat_end, start_year, end_year, 
                                 div_period, **kwargs):
    """
    Function to create an interactive, geographic plot of estimated average increase
    in temperature over a provided latitude range, for each of a specificied subdivision
    duration of a specified year span using a faceted heat density map  wherein
    spatial resolution, and by proxy the grouping of temp delta averaging, via the 
    manipulation of bin counts and latitude ranges
    
    Parameters
    ----------
    lat_start - int: the start of the latitude range to query for
    lat_end - int: the end of the latitude range to query for
    start_year: str - start year for the query
    start_year: str - end year for the query
    div_period: int - length in years of each subdivision to plot
    kwargs: variable arguments fed to the plotly graphing function
    
    Returns
    -------
    fig - the plotly fig generated by the function
    """    
    
    
    #query using prior function
    df = lat_query(lat_start, lat_end)
    
    #year range filter
    df = df[df["Year"] >= start_year]
    df = df[df["Year"] <= end_year]


    #loop through periods, moving year window
    start = start_year
    end = start_year + div_period


    #build first period in column
    period_string = str(start) + "-" + str(end)
    df['Period'] = [period_string if (year >= start and year < end) else " " for year in df['Year']]

    #update window
    start = end
    end = end + div_period 

    while(end <= end_year):
        #logical index to fill periods
        df.loc[((df["Year"] >= start) & (df["Year"] < end), 'Period')] = str(start) + "-" + str(end)
        #update windows
        start = end
        end = end + div_period  
    
    #just in case, drop any unfilled vals
    df = df[df["Period"] != " "]
    
    #group our df for a given station, 
    coefs = df.groupby(["NAME", "LATITUDE", "LONGITUDE", "Period"]).apply(coef)
    coefs = coefs.reset_index()
    
    #rename new column 
    coefs = coefs.rename(columns= {0: "ESTIMATED YEAR INCREASE (C)"})
    
    #round temp values
    coefs = coefs.round(4)
    
    #make output strings
    str_start = str(start_year)
    str_end = str(end_year)
    str_div = str(div_period)
    fig = px.density_heatmap(coefs, x="LONGITUDE", y="LATITUDE", 
                             z="ESTIMATED YEAR INCREASE (C)", histfunc="avg", 
                             color_continuous_midpoint=0, facet_row="Period",
                             title="Estimated Yearly Temp. Increase from " + str_start +
                             " to " + str_end + " by " + str_div + " Year Periods",
                             **kwargs)
    
    #shorten color bar (it's quite long otherwise)
    fig.update_layout(coloraxis_colorbar=dict(
    len=0.5, ))

    return fig

fig = temperature_heat_map_faceted(-90, 90, start_year=1980, end_year=2020, div_period=10,
                                    width = 1000, height = 1500, nbinsx = 72, nbinsy = 36,
                                    color_continuous_scale="rdbu_r")

fig.show()
pio.write_html(fig,"hw1_hd_faceted1") #comment out if not saving

def lat_bar(lat_start, lat_end, start_year, end_year, **kwargs):
    
    """
    Function to create an interactive, histogram of estimated average increase
    in temperature over a provided latitude range, for a  specified time span 
    in years.
    
    Parameters
    ----------
    lat_start - int: the start of the latitude range to query for
    lat_end - int: the end of the latitude range to query for
    start_year: str - start year for the query
    start_year: str - end year for the query
    kwargs: variable arguments fed to the plotly graphing function
    
    Returns
    -------
    fig - the plotly fig generated by the function
    """    
    
    #use prior defined query function to build our df
    df = lat_query(lat_start, lat_end)
    
    #year range filter
    df = df[df["Year"] >= start_year]
    df = df[df["Year"] <= end_year]
    
    #group our df by station
    coefs = df.groupby(["NAME", "LATITUDE", "LONGITUDE"]).apply(coef)
    coefs = coefs.reset_index()
    
    #rename new column 
    coefs = coefs.rename(columns= {0: "ESTIMATED YEAR INCREASE (C)"})
    
    #round temp values
    coefs = coefs.round(4)
    
    #rename new column 
    coefs = coefs.rename(columns= {0: "ESTIMATED YEAR INCREASE (C)"})
    
    #round temp values
    coefs = coefs.round(4)
    
    #output strings
    str_lat1 = str(lat_start)
    str_lat2 = str(lat_end)
    str_start = str(start_year)
    str_end = str(end_year)
    
    fig = px.histogram(coefs, x="LATITUDE", y="ESTIMATED YEAR INCREASE (C)",
            nbins = (abs(lat_end - lat_start)//10), histfunc='avg', 
                    title="Estimated Yearly Temp. Change by Latitude from " + str_start 
                    + " to " + str_end + " between " + str_lat1 + " and "
                    + str_lat2 + '\u00b0')
    
    return fig
    

fig = lat_bar(lat_start=-90, lat_end=0, start_year=1960, end_year=2010)

fig.show()
pio.write_html(fig,"hw1_hist1") #comment out if not saving