import pandas as pd

temperatures = pd.read_csv(data_path+"temps.csv")
stations = pd.read_csv(data_path+"station-metadata.csv")
countries = pd.read_csv(data_path+"countries.csv")

# Checking the temperatures data
temperatures.head()

# Checking the shape of the temperatures data frame
temperatures.shape

# Checking the stations data
stations.head()

# Checking the shape of the stations data frame
stations.shape

# Checking the countries data
countries.head()

# Checking the shape of the countries data frame
countries.shape

import sqlite3
# create a database in current directory called data.db
# to store temperatures, stations, and countries tables
conn = sqlite3.connect("data.db") 

temperatures.head()

temperatures['ID'] = temperatures["ID"].str[0:2]
temperatures.head()

temperatures = temperatures.set_index(keys=["ID", "Year"])
temperatures = temperatures.stack() # stacking the months and temperatures
temperatures = temperatures.reset_index()
temperatures.head()

temperatures = temperatures.rename(columns = {"level_2" : "Month", 0 : "Temp"})
temperatures.head()

temperatures["Month"] = temperatures["Month"].str[5:].astype(int)
# Dividing the temperatures by 100 to extract temperatures of the correct unit.
temperatures["Temp"]  = temperatures["Temp"] / 100
temperatures.head()

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

# Checking that we have a "temperatures" table within the database
cursor = conn.cursor()
cursor.execute("SELECT name FROM sqlite_master WHERE type='table'")
print(cursor.fetchall())

stations.head()

stations['ID'] = stations["ID"].str[0:2]
stations.head()

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

countries.head()

# dropping the ISO 3166 column
countries = countries.drop('ISO 3166', axis=1)
countries.head()

countries = countries.rename(columns = {"FIPS 10-4" : "ID", "ISO 3166" : "ISO-3166"})
countries.head()

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

conn.close()

def query_climate_database(country, year_begin, year_end, month):
    """Function returns a dataframe of temperature readings for the 
    specified country, in the specific date range, in the specified month
    of the year
    
    Parameters
    ----------
    country : string
        the name of a country for which data should be returned
    year_begin : integer
        the earliest year for which data should be returned
    year_end : integer
        the latest year for which data should be returned
    month : integer
        month of the year for which data should be returned
    
    Returns
    ----------
    DataFrame 
        Pandas dataframe of temperature readings for the specified country, 
        in the specified date range, in the specified month of the year
    """
    # opening the database
    conn = sqlite3.connect("data.db")
    cmd = \
    f"""
    SELECT s.NAME, s.LATITUDE, s.LONGITUDE, c.Name AS Country, t.Year, t.Month, t.Temp
    FROM temperatures t
    LEFT JOIN countries c ON t.ID = c.ID
    LEFT JOIN stations s ON t.ID = s.ID
    WHERE t.Year>={str(year_begin)} AND t.Year<={str(year_end)} AND t.Month={str(month)} AND c.Name='{country}'
    """
    df = pd.read_sql_query(cmd, conn) # extracting the appropriate dataframe
    conn.close() # closing the database
    
    return df

# TEST CASE
country = "India"
year_begin = 1980
year_end = 2020
month = 1 # The month of January

query_climate_database(country, year_begin, year_end, month)

from plotly import express as px
from sklearn.linear_model import LinearRegression

# Other potentially useful libraries
import numpy as np
import seaborn as sns
from matplotlib import pyplot as plt

def coef(data_group):
    # extracting x and y for linear regression
    x = data_group[['Year']]
    y = data_group['Temp']
    # The linear regression model
    LR = LinearRegression()
    LR.fit(x,y)
    # returning the coefficients
    return LR.coef_[0]

def temperature_coefficient_plot(country, year_begin, year_end, month, min_obs, **kwargs):
    """Function generates an interactive geographic scatterplot,
    with a point for each station, such that the color of the point reflects 
    an estimate of the yearly change in temperature during the specified 
    month and time period at that station.
    
    Parameters
    ----------
    country : string
        the name of a country for which data should be returned
    year_begin : integer
        the earliest year for which data should be returned
    year_end : integer
        the latest year for which data should be returned
    month : integer
        month of the year for which data should be returned
    min_obs : integer
        minimum required number of years of data for any given station
    **kwargs 
        additional keyword arguments passed to px.scatter_mapbox(). 
        These can be used to control the colormap used, the mapbox style, etc.
    
    Returns
    ----------
    scatterplot
        An interactive geographic scatterplot, constructed using Plotly Express, 
        with a point for each station, such that the color of the point reflects 
        an estimate of the yearly change in temperature during the specified 
        month and time period at that station
    """
    # extracting the temperature reading data frame for the given
    # country, month, and years
    df = query_climate_database(country, year_begin, year_end, month)
    
    # extracting a list of unique station names
    station_names = df['NAME'].unique() 
    stations_to_keep = []
    
    for name in station_names: 
        temp_df = df[df['NAME'] == name]
        # checking the years of data available for
        # the specified month
        number_of_years = temp_df['Year'].nunique()
        if number_of_years >= min_obs:
            stations_to_keep.append(name)
    
    # Now that we have a list of the stations to keep
    # we create another data frame 
    df_new = pd.DataFrame()
    for station_name in stations_to_keep:
        tempdf = df[df['NAME'] == station_name]
        # concatenating the temporary data frame
        # with the new data frame
        df_new = pd.concat([df_new, tempdf])
        
    # Extracting the average temperatures per month, per year
    df_new = df_new.groupby(["NAME", "LATITUDE", "LONGITUDE", "Country", 'Year', "Month"])["Temp"].aggregate(np.mean)
    df_new = pd.DataFrame(df_new) # appending the above to a data frame
    df_new = df_new.reset_index() # and resetting the index appropriately
    
    # array of station names from the new data frame
    new_station_names = df_new['NAME'].unique()
    # Arrays to store the coefficients, latitudes, and longitudes
    coefs = np.array([])
    lats = np.array([])
    longs = np.array([])
    
    for sname in station_names:
        df_temp = df_new[df_new['NAME'] == sname]
        lats = np.append(lats, df_temp['LATITUDE'].unique()[0])
        longs = np.append(longs, df_temp['LONGITUDE'].unique()[0])
        coefs = np.append(coefs, coef(df_temp))
    
    # Creating a data frame of coefficients representing yearly temp change
    coef_df = pd.DataFrame()
    coef_df['NAME'] = station_names
    coef_df['LATITUDE'] = lats
    coef_df['LONGITUDE'] = longs
    coef_df['Estimated Yearly Increase (°C)'] = np.round(coefs, 5)
    
    # list of months
    months = ["January", "February", "March", "April", "May", "June", "July", "August", "September", "October", "November", "December"]
    
    # Generating the scatter plot
    fig = px.scatter_mapbox(coef_df, 
                            title = "Estimated Yearly Increase in Temperature in "+months[month-1],
                            lat = "LATITUDE",
                            lon = "LONGITUDE", 
                            hover_name = "NAME", 
                            color = "Estimated Yearly Increase (°C)",
                            **kwargs)

    fig.update(layout={"title": {"x": 0.85, "y": 0.97}})
    fig.update_layout(margin={"r":0,"t":30,"l":0,"b":10})
    return fig

# TEST CASE
country = "India"
year_begin = 1980
year_end = 2020
month = 1
min_obs = 10

# Implementing the function
fig = temperature_coefficient_plot(country, year_begin, year_end, month, min_obs, zoom = 3, 
                                   mapbox_style = "carto-positron")
fig.show()

def query_climate_database_v2(country, year_begin, year_end):
    """Function returns a dataframe of temperature readings for the 
    specified country, in the specific date range, in the specified month
    of the year
    
    Parameters
    ----------
    country : string
        the name of a country for which data should be returned
    year_begin : integer
        the earliest year for which data should be returned
    year_end : integer
        the latest year for which data should be returned
    
    Returns
    ----------
    DataFrame 
        Pandas dataframe of temperature readings for the specified country, 
        in the specified date range. 
    """
    # opening the database
    conn = sqlite3.connect("data.db")
    cmd = \
    f"""
    SELECT s.LATITUDE, s.LONGITUDE, c.Name AS Country, s.NAME, t.Year, t.Month, t.Temp
    FROM temperatures t
    LEFT JOIN countries c ON t.ID = c.ID
    LEFT JOIN stations s ON t.ID = s.ID
    WHERE t.Year>={str(year_begin)} AND t.Year<{str(year_end)} AND c.Name='{country}'
    """
    df = pd.read_sql_query(cmd, conn) # extracting the appropriate dataframe
    conn.close() # closing the database
    
    return df

# TEST CASE: Let us pick a country that falls on the equator, like Brazil!
df2 = query_climate_database_v2("Brazil", 1990, 2020)
df2

# Extracting the average temperatures per month, per year
df2 = df2.groupby(["LATITUDE", "LONGITUDE", "Country", "NAME", 'Year', "Month"])["Temp"].aggregate(np.mean)
df2 = pd.DataFrame(df2) # appending the above to a data frame
df2 = df2.reset_index() # and resetting the index appropriately
df2

# Add a column to determine whether the point is above or
# below the equator
latitudes = np.array(df2['LATITUDE'])

# above or below or on equator? 
equator = np.array([])

for lat in latitudes:
    if lat < 0:
        equator = np.append(equator, 'below')
    elif lat > 0: 
        equator = np.append(equator, 'above')
    else: 
        equator = np.append(equator, 'on')
        
df2['Position Relative to Equator (0°N)'] = equator
df2.head()

fig = px.scatter(df2,
                 title = "Yearly Temperature Trends (1990 - 2020)"
                 x = "Month", 
                 y = "Temp",
                 color = "Year",
                 width = 800,
                 height = 400,
                 opacity = 0.5, 
                 facet_col = "Position Relative to Equator (0°N)")


# reduce whitespace
fig.update(layout={"title": {"x": 0.5, "y": 0.97}})
fig.update_layout(margin={"r":0,"t":50,"l":0,"b":0})
# show the plot
fig.show()

fig = px.histogram(df2,
                   title = "Distribution of Temperatures Below & Above the Equator",
                   x = "Temp", 
                   color = "Position Relative to Equator (0°N)",
                   opacity = 0.2, 
                   nbins = 35, 
                   barmode='stack',
                   width = 700,
                   height = 500)

# reduce whitespace
fig.update(layout={"title": {"x": 0.7, "y": 0.98}})
fig.update_layout(margin={"r":0,"t":0,"l":0,"b":0})
# show the plot
fig.show()

fig = px.box(df2, 
             title = "Temperature Distributions Below & Above the Equator Over Ten Years",
             x = "Year", 
             y = "Temp",
             color = "Position Relative to Equator (0°N)",
             width = 1000,
             height = 400)

# reduce whitespace
fig.update(layout={"title": {"x": 0.7, "y": 0.97}})
fig.update_layout(margin={"r":0,"t":0,"l":0,"b":0})
# show the plot
fig.show()