import pandas as pd
import seaborn as sns 
from matplotlib import pyplot as plt
import numpy as np

import sqlite3

conn = sqlite3.connect("temps.db") # create a database in current directory called temps.db

def prepare_df(df):
    '''
    this function takes a dataframe with months as columns names with temperature as values
    and returns a dataframe with month and temperature as column names 
    '''
    # convert all the columns that we don't want to stack into a multi-index for the data frame
    df = df.set_index(keys=["ID", "Year"])
    # stacking
    df = df.stack()
    # recover ID and Year columns
    df = df.reset_index()
    # rename columns to make them more readable
    df = df.rename(columns = {"level_2"  : "Month" , 0 : "Temp"})
    # extract the integer part to be month value
    df["Month"] = df["Month"].str[5:].astype(int)
    df["Temp"]  = df["Temp"] / 100
    # this will be used later for joining
    df["FIPS 10-4"] = df["ID"].str[0:2]
    return(df)

# temperature table 
df_iter = pd.read_csv("temps.csv", chunksize = 100000)
for df in df_iter:
    df = prepare_df(df)
    df.to_sql("temperatures", conn, if_exists = "append", index = False)

df.head(5)

# stations table
station_url = "https://raw.githubusercontent.com/PhilChodrow/PIC16B/master/datasets/noaa-ghcn/station-metadata.csv"
stations = pd.read_csv(station_url)
stations.to_sql("stations", conn, if_exists = "replace", index = False)

stations.head(5)

# countries table 
country_url = "https://raw.githubusercontent.com/mysociety/gaze/master/data/fips-10-4-to-iso-country-codes.csv"
countries = pd.read_csv(country_url)
countries.to_sql("countries", conn, if_exists = "replace", index = False)

countries.head(5)

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])

# close the database connection
conn.close()

conn = sqlite3.connect("temps.db")

def query_climate_database(country, year_begin, year_end, month):
    '''
    this function gives the temperature of a country within specified year range in the specific month.
    inputs: 
    country, a string giving the name of a country for which data should be returned.
    year_begin and year_end, two integers giving the earliest and latest years for which should be returned.
    month, an integer giving the month of the year for which should be returned.
    ouput:
    a Pandas dataframe of temperature readings for a country within specified year range in the specific month
    '''
    
    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 T.'fips 10-4'= C.'fips 10-4'
    WHERE T.year BETWEEN ? AND ?
    AND T.month = ?
    AND C.name = ?
    """
    
    df = pd.read_sql_query(cmd, conn, params = (year_begin, year_end, month, country))
    # rename the column of country name to Country
    df.rename(columns={"Name": "Country"},inplace = True)
    return(df)

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

from plotly import express as px
import calendar #convert number to month name 

from sklearn.linear_model import LinearRegression

def coef(data_group):
    '''
    this function computes the first coefficient of the linear model Year vs. Temp
    '''
    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):
    '''
    this functions generates an interactive geographic scatterplot to visualize yearly average change in temperature 
    in a specific month in a given country during specified years.
    Inputs:
    country, a string giving the name of a country for which data should be returned.
    year_begin and year_end, two integers giving the earliest and latest years for which should be returned.
    month, an integer giving the month of the year for which should be returned.
    min_obs:　the minimum required number of years of data for any given station.
    output:
    a geographic scatterplot
    '''
    
    # create a dataframe containing all the information needed
    df = query_climate_database(country, year_begin, year_end, month)
    # count the number of years of data for each temperature station
    df['freq'] = df.groupby('NAME')['NAME'].transform('count')
    # only keep data for stations with at least min_obs years worth of data  
    df = df[df['freq']>= min_obs]
    c = df.groupby(["NAME", "LATITUDE", "LONGITUDE"]).apply(coef).reset_index()    
    # add a new column to store coefficients round up to 4 decimal places
    z = "Estimated Yearly Increase " + u"(\N{DEGREE SIGN}C)"
    c[z] = c[0].round(decimals = 4)
    
    title = "Estimte of yearly increase in temperature in "+ list(calendar.month_name)[month] +" <br>for stations in "+ country +" , years "+ str(year_begin)+" - "+str(year_end)
    fig = px.scatter_mapbox(c, 
                        lat = "LATITUDE",
                        lon = "LONGITUDE", 
                        hover_name = "NAME",
                        color = z,
                        title = title,
                        **kwargs)
    return(fig)

color_map = px.colors.diverging.RdGy_r # choose a colormap

fig1 = temperature_coefficient_plot("India", 1980, 2020, 1, 
                                   min_obs = 10,
                                   zoom = 2,
                                   mapbox_style="carto-positron",
                                   color_continuous_midpoint = 0, # 0 corresponds to the center of colorbar
                                   color_continuous_scale=color_map)

fig1.show()

from plotly.io import write_html
write_html(fig1, "geo_scatter.html")

def elevation_query(latitude_min, latitude_max, month):
    '''
    this function gives the temperature of countries within specified latitude range in the specific month.
    inputs: 
    latitude_min and latitude_max, two doubles giving the smallest and largest latitude.
    month, an integer giving the month of the year for which should be returned.
    ouput:
    a Pandas dataframe of temperature readings for countrys within specified latitude range in the specific month
    '''
    
    cmd = \
    """
    SELECT S.name, T.year, T.month, T.temp, S.STNELEV, C.name
    FROM temperatures T
    LEFT JOIN stations S ON T.id = S.id
    LEFT JOIN countries C ON T.'fips 10-4'= C.'fips 10-4'
    WHERE S.latitude BETWEEN ? AND ?
    AND T.month = ?
    """

    df = pd.read_sql_query(cmd, conn, params = (latitude_min, latitude_max, month))
    # rename the column containing country names to avoid confusion
    df.rename(columns={"Name": "Country"},inplace = True)
    return(df)

def elevation_temp_scatter(latitude_min, latitude_max, month, min_obs, **kwargs):
    '''
    this functions generates an interactive scatterplot to visualize yearly average change in temperature v.s.elevation
    in a specific month for countries within specified latitude range.
    Inputs:
    latitude_min and latitude_max, two doubles giving the smallest and largest latitude.
    month, an integer giving the month of the year for which should be returned.
    min_obs:　the minimum required number of stations for any given country.
    output:
    a scatterplot
    '''
    
    # create a dataframe containing all the information needed
    df = elevation_query(latitude_min, latitude_max, month)
    # obtain the yearly change in temeprature
    c = df.groupby(["NAME","STNELEV", "Country"]).apply(coef).reset_index()
    # rename columns for convenience
    z = "Yearly change in Temperatures " + u"(\N{DEGREE SIGN}C)"
    c.rename(columns = {"NAME": "Station", 0:z, "STNELEV": "Elevation"},inplace = True)
    # compute the number of different stations within each country and store the info as a new col
    c['freq'] = c.groupby('Country')['Country'].transform('count')
    # only keep data for countries with at least min_obs many stations 
    c = c[c['freq']>= min_obs] 
    # name for y_axis
    y_axis = "Yearly change in Temperatures " + u"(\N{DEGREE SIGN}C) between latitude "+ str(latitude_min)+" and "+str(latitude_max)+ " in "+ list(calendar.month_name)[month]

    fig = px.scatter(data_frame = c, 
                     x = "Elevation", 
                     y = z,
                     labels = {"Elevation" : "Elevation",
                               z : y_axis,
                               'Country' : 'Countries'},
                     hover_name = "Station",
                     hover_data = ["Elevation", z],
                     color = "Country",
                     **kwargs)
    
    fig.update_layout(margin={"r":0,"t":0,"l":0,"b":0})
    return(fig)

fig2 = elevation_temp_scatter(10,15,1, min_obs = 10)
fig2.show()

write_html(fig2, "scatter.html")

def country_query(country1, country2):
    '''
    this function gives the temperature of two chosen countries
    inputs: 
    country 1 and country2: two strings that gives the names of two different countries
    ouput:
    a Pandas dataframe of temperature readings for the two chosen countrys
    '''
    
    cmd = \
    """
    SELECT S.name, T.year, T.month, T.temp, C.name
    FROM temperatures T
    LEFT JOIN stations S ON T.id = S.id
    LEFT JOIN countries C ON T.'fips 10-4'= C.'fips 10-4'
    WHERE C.name IN (?,?)    
    """

    df = pd.read_sql_query(cmd, conn, params = (country1, country2))
    # rename the column containing country names to avoid confusion
    df.rename(columns={"Name": "Country"},inplace = True)
    return(df)

def country_comparison(country1, country2, **kwargs):
    '''
    this functions generates an interactive boxplot to visualize yearly average change in temperature 
    in two given countries for each month.
    Inputs:
    country 1 and country2: two strings that gives the names of two different countries
    output:
    a boxplot
    '''
    df = country_query(country1, country2)
    # yearly change in temperature over years for each month
    c = df.groupby(["NAME", "Month", "Country"]).apply(coef).reset_index()
    z = "Yearly change in Temperatures " + u"(\N{DEGREE SIGN}C)"
    c.rename(columns = {"NAME": "Station", 0: z},inplace = True)
    fig = px.box(c, 
                 x = "Month", 
                 y = z,
                 color = "Country")
    return(fig)

fig3 = country_comparison("India", "Afghanistan")

fig3.show()

write_html(fig3, "boxplot.html")

conn.close()