import sqlite3
import pandas as pd
import numpy as np

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

#temperatures = pd.read_csv('temps.csv')
stations = pd.read_csv('station-metadata.csv')
countries = pd.read_csv('countries.csv')
stations['ID_'] = stations['ID'].str[0:2]

countries.head(5)

stations.head(5)

temperatures = pd.read_csv('temps.csv')
#temperatures = prepare_df(temperatures)

temperatures.head(5)

def prepare_df(df):
    df = df.set_index(keys=["ID", "Year"])#convert all the columns we don't want to stack into multi-indices
    df = df.stack()#separate out values in each row into single entries
    df = df.reset_index()#giving each entry ID, Year, and Name columns
    df = df.rename(columns = {"level_2"  : "Month" , 0 : "Temp"})#rename to create proper column names
    df["Month"] = df["Month"].str[5:].astype(int)
    df["Temp"]  = df["Temp"] / 100
    df["FIPS 10-4"] = df["ID"].str[0:2]
    return(df)

temperatures = prepare_df(temperatures)
temperatures.head(5)

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)

stations.to_sql("stations", conn, if_exists = "replace", index = False)
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())

conn.close()

def query_climate_database(country, year_begin, year_end, month):
    '''param country: a string of the country for which data should be returned;
       year_begin, year_end: two integers specifying the time interval of data be returned;
       month: an integer giving the month of data to be returned.
       @return a Pandas dataframe of temperature readings for the specified country, 
       in the specified date range, in the specified month of the year
    '''
    #first create a connection with the database
    conn = sqlite3.connect("temps.db")
    #basic queries
    #containing SELECT, specifies column(s) to be returned;
    #FROM, from which table(s)
    #LEFT JOIN, merge datasets for specific outcomes
    #WHERE, row specification
    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 S.id_ = C.`fips 10-4`
    WHERE C.name = ? AND (T.year BETWEEN ? AND ?) AND T.month = ?
    """
    
    #the '?' syntax in WHERE enables parameterization in a query,
    #and we'll supply the parameters sequentially here
    param = (country, year_begin, year_end, month,)
    
    #the following line reads a query and returns the dataset from the database 
    df = pd.read_sql_query(cmd, conn, params=param)
    conn.close()#make sure to close the connection after use
    return df

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

df

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

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):
    '''country:  string of the country for which data should be returned;
        year_begin, year_end: two integers specifying the time interval of data be returned;
        min_obs: minimum required number of years of data for any given station. 
        (only data for stations with at least min_obs years worth of data would be considered);
        **kwargs: additional keyword arguments passed to px.scatter_mapbox(). 
    '''
    #first obtain the dataframe from above function:
    df = query_climate_database(country = country, 
                       year_begin = year_begin, 
                       year_end = year_end,
                       month = month)
    #add a new column to df with number of years of data there are for that station
    df["size"] = df.groupby(["NAME"])['NAME'].transform(lambda x: x.shape[0])
    #filter out stations without enough yearly data, inclusive
    df_ = df[df['size'] >= min_obs]
    
    #estimate yearly increase for each station
    coefs = df_.groupby(["NAME", "Month"]).apply(coef)#
    coefs = coefs.reset_index()
    
    #merge the estimates with stations,
    #therefore each estimate would have there corresponding latitude and longitude
    coefs_ = pd.merge(coefs, stations, on = ["NAME"])
    #rename coef column to "Estimated Yearly Increase(Celsius)" and round the coefs
    coefs_ = coefs_.rename(columns = {0:"Estimated Yearly Increase(Celsius)"})
    coefs_ = coefs_[["Estimated Yearly Increase(Celsius)", 'NAME', 'LATITUDE','LONGITUDE']]
    coefs_['Estimated Yearly Increase(Celsius)'] = coefs_['Estimated Yearly Increase(Celsius)'].round(4)
    
    #create plotly plot! add titles and hover overs
    fig = px.scatter_mapbox(coefs_, 
                        lat = "LATITUDE",
                        lon = "LONGITUDE", 
                        hover_name = "NAME", 
                        color = "Estimated Yearly Increase(Celsius)", 
                        title="Estimates of yearly increase in Temperature in January for stations in "
                            + country +", years " + str(year_begin) + " - " + str(year_end),
                        **kwargs)

    #fig.update_layout(margin={"r":0,"t":0,"l":0,"b":0})
    return fig
    

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

#run the function to generate plotly plot
fig = temperature_coefficient_plot("India", 1980, 2020, 1, 
                                   min_obs = 10,
                                   zoom = 2,
                                   mapbox_style="carto-positron",
                                   color_continuous_scale=color_map)

fig.show()

from plotly.io import write_html

write_html(fig, "india_temp_esti.html")

#conn = sqlite3.connect("temps.db")
def query_compare_latitude(lat_low, lat_high, year):
    '''returns a dataframe containing monthly temps at stations
    for the specified latitude from two hemispheres
    '''
    #neg_lat = np.negative(lat)
    conn = sqlite3.connect("temps.db")
    cmd = \
    """
    SELECT T.id, T.month, T.temp, S.latitude, S.longitude, S.name, T.year
    FROM temperatures T
    LEFT JOIN stations S ON T.id = S.id
    WHERE ((S.latitude < ? AND S.latitude > ?) OR (S.latitude < ? AND S.latitude > ?) )AND T.year = ?
    """
    params = (lat_high, lat_low, -lat_low, -lat_high , year )

    df = pd.read_sql_query(cmd,conn, params = params)
    #df_south = pd.read_sql_query(cmd_s, conn)
    conn.close()
    return df

#select temp measurements for points in year 1954
#latitude in both hemispheres between 20 - 40 degrees
df = query_compare_latitude(20, 25, 1954)

df

from matplotlib import pyplot as plt
#import seaborn as sns 

def plot_line(df):
    #return sns.lineplot(x="Month", y="Temp",
             #style="NAME", hue = 'LATITUDE', palette = 'vlag',
             #data=df)
    df['LATITUDE'] = df['LATITUDE'].astype(int)# cast to int for approximation, for ease to generate plot
    df['hemi'] = np.where( (df['LATITUDE'] >= 0), 'northern hemisphere', 'southern hemisphere')
    return px.line(df, x="Month", y="Temp", color = 'hemi',
                  title="Monthly temperature(celsius) in both hemispheres between 20 - 40 latitudes"
                  ,line_group="NAME",
                 hover_name="NAME")
    #fig.show()

fig = plot_line(df)

write_html(fig, "p4_1_real.html")

CCcode = pd.read_csv('ContinentsCountries.csv')
#https://coronadatasource.org/open-data/geo-data/world-regions-continents-countries/

CCcode = CCcode[['Country Name (usual)', 'Continent' , 'Continent Code']]

CCcode

CCcode['Continent'].unique()

conn = sqlite3.connect("temps.db")
CCcode.to_sql("CCcode", conn, if_exists = "replace", index = False)
conn.close()

def query_temp_continent(year):
    '''return a table with countries' yearly temperature, marked by continent,
    in a specific year
    '''
    conn = sqlite3.connect("temps.db")
    cmd = \
    """
    SELECT T.id, T.month, T.temp, C.name, T.year, O.continent
    FROM temperatures T
    LEFT JOIN stations S ON T.id = S.id
    LEFT JOIN countries C ON S.id_ = C.`fips 10-4`
    LEFT JOIN cccode O ON O.`country name (usual)` = C.name
    WHERE T.year =?
    """
    params = (year , )

    df = pd.read_sql_query(cmd,conn, params = params )
    #df_south = pd.read_sql_query(cmd_s, conn)
    conn.close()
    return df

df3 = query_temp_continent(1969)

df3

def plot_line_cont2(df):
    cont = df['Continent'].unique()
    year = df['Year'].unique()
    fig = px.scatter(df, x="Month", y="Temp", color="Name",
               hover_name="Name",
        title = 'Monthly avg Temperature of countries(& regions) in ' + cont[0] + ' in ' + str(year))
    fig.show()
    write_html(fig, cont[0] + ".html")

df3.groupby(['Continent']).apply(plot_line_cont2)