import pandas as pd
import sqlite3

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

countries = pd.read_csv('countries.csv')
#renames columns to get rid of white spaces
countries = countries.rename(columns = {"FIPS 10-4": "FIPS-10-4"})
#renames Name column to Country
countries = countries.rename(columns = {"Name": "Country"})
countries.head()

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

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

#creating database with three tables
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)

#closing connection
conn.close()

def query_climate_database(country, year_begin, year_end, month):
    
   '''Returns a data frame containing the name, coordinates, country, year, month, and temperature
   for a specified country, year range, and month
   
   @param country: string; name of a country for which data should be returned
   @param year_begin: integer; earliest year for which data should be returned
   @param: year_end: integer; latest year for which data should be returned
   @param: month: integer; month for which data should be returned
   @return: dataframe containing data that matches specifications
   '''

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

    #selects name, latitude, and longitude from stations, country from countries, and 
    #year, month, and temp from temperatures
    #joins stations, temperatures, and countries dataframes 
    cmd = "SELECT S.name, S.latitude, S.longitude, C.country, T.year, T.month, T.temp \
            FROM temperatures T \
            LEFT JOIN stations S ON T.id = S.id \
            LEFT JOIN countries C ON SUBSTRING(T.id,1,2) = C.'FIPS-10-4' \
            WHERE (year >=" + str(year_begin) + ") AND (year <=" + str(year_end) + ") AND (month=" + str(month) + ") AND (country= \"" + str(country) + "\")"

    df = pd.read_sql(cmd, conn)

    #closing connection
    conn.close()

    #returns dataframe with specifications in place
    return df

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

query_climate_database(country="China",
                       year_begin = 2000, 
                       year_end = 2010,
                       month = 2)

from sklearn.linear_model import LinearRegression

def coef(data_group):
    '''Fits variables "Year" and "Temp" from data_group to a linear regression model 
    
    @data_group: dataframe
    @return: integer value; slope of the linear regression model
    '''
    X = data_group[["Year"]]
    y = data_group["Temp"]
    LR = LinearRegression()
    #fit to model
    LR.fit(X, y)
    slope = LR.coef_[0]
    return slope

import pandas as pd
from plotly import express as px

def temperature_coefficient_plot(country, year_begin, year_end, month, min_obs, **kwargs):
    '''Returns a map with cities in specified country plotted, color based on average yearly
    temperature change
    
    @param country: string; name of a country for which data should be returned
    @param year_begin: integer; earliest year for which data should be returned
    @param: year_end: integer; latest year for which data should be returned
    @param: month: integer; month for which data should be returned
    @param: min_obs: integer: minimum required number of years of data for any given station
    @param **kwargs: additional keyword arguments passed to px.scatter_mapbox()
    @return: map with specified data plotted 
    '''
    months = {1:"January",
             2:"February",
             3:"March",
             4:"April",
             5:"May",
             6:"June",
             7:"July",
             8:"August",
             9:"September",
             10:"October",
             11:"November",
             12:"December"}
    
    #throws value error if number of years is less than the minimum number of observations
    if (year_end - year_begin) < min_obs:
        raise ValueError("The amount of years does not satisfy number of minimum observations")
    
    #calls on function query_climate_database to get dataframe with specifications
    df = query_climate_database(country=country,
                       year_begin = year_begin, 
                       year_end = year_end,
                       month = month)
    
    #group by name, latitude, and longitude; applies coef function 
    coefs = df.groupby(["NAME", "LATITUDE", "LONGITUDE"]).apply(coef)

    coefs = coefs.reset_index()
    #rename column with slopes 
    coefs = coefs.rename(columns={0:"Estimated Yearly Increase (°C)"})
    #rounds values to nearest thousandths place
    coefs["Estimated Yearly Increase (°C)"] = round(coefs["Estimated Yearly Increase (°C)"], 3)
    
    fig = px.scatter_mapbox(coefs,  
                        #latitude of city
                        lat = "LATITUDE",
                        #lonitude of city
                        lon = "LONGITUDE", 
                        #colors map based on increase
                        color="Estimated Yearly Increase (°C)",
                        #message displayed when hovered over
                        hover_name = "NAME", 
                        #centers colorbar at 0
                        color_continuous_midpoint=0,
                        #aspect ratio
                        height = 250, 
                        #title
                        title="Estimates of yearly increase in temperature in " + months[month] + " for stations in " + country + ", years " + str(year_begin) + "-" + str(year_end),
                        #additional arguments specified by user
                        **kwargs) 

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

color_map = px.colors.diverging.RdGy_r 

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

fig = temperature_coefficient_plot("China", 2000, 2010, 2, 
                                   min_obs = 10,
                                   zoom = 2,
                                   mapbox_style="carto-positron",
                                   color_continuous_scale="inferno")

def country_temp_database(country, year_begin, year_end):
    
    '''Returns a data frame containing the name, year and temperature for a specified country in a specified year range
   
    @param country: string; name of a country for which data should be returned
    @param year_begin: integer; earliest year for which data should be returned
    @param: year_end: integer; latest year for which data should be returned
    @return: dataframe containing data that matches specifications
    '''

    conn = sqlite3.connect("temps.db")
        
    cmd = "SELECT S.name, T.month, C.country, T.year, T.temp \
            FROM temperatures T \
            LEFT JOIN stations S ON T.id = S.id \
            LEFT JOIN countries C ON SUBSTRING(T.id,1,2) = C.'FIPS-10-4' \
            WHERE (year >=" + str(year_begin) + ") AND (year <=" + str(year_end) + ") AND (country= \"" + str(country) + "\")"

    df = pd.read_sql(cmd, conn)

    #closing connection
    conn.close()

    #returns dataframe with specifications in place
    return df

country_temp_database("Greece", 1990, 2005)

def avg_city_temp(country, year_begin, year_end, n, **kwargs):
    '''Returns line graph of average temperatures of cities in specified country over specified time rangr
    
    @param country: string; name of a country for which data should be returned
    @param year_begin: integer; earliest year for which data should be returned
    @param year_end: integer; latest year for which data should be returned
    @param n: integer; n number of cities occuring most in data to be used in graph
    @param **kwargs: additional keyword arguments passed to px.scatter_mapbox()
    @return: line graph with specifications plotted
    '''
    
    #returns list of top n cities that occur the most in the data
    cities = country_temp_database(country, year_begin, year_end).groupby(["NAME"])["Country"].count().sort_values(ascending=False).head(n).index.to_list()
    
    #returns dataframe of name, country, year, temp for specified country in specified year range
    data = country_temp_database(country, year_begin, year_end)
    
    #groups data by city by year
    grouped_data = data[data['NAME'].isin(cities)].groupby(["NAME", "Year"])["Temp"].mean()
    
    #save to CSV file; while transform was an option, it resulted in a strange graph
    grouped_data.to_csv(country + ".csv")
    
    #read CSV file
    new_data = pd.read_csv(country + ".csv")
    
    #create line graph
    fig = px.line(new_data, 
                  x="Year", 
                  y="Temp", 
                  #each line is dif color corresponding to city
                  color="NAME", 
                  #title specific to country
                  title="Average Temperature of Top " + str(n) + " Occuring Cities in " +  country + " Data per Year from " + str(year_begin) + "-" + str(year_end),
                  **kwargs)
    
    fig.show()

avg_city_temp("Greece", 1990, 2005, 10)

def stations_database(country1, country2, country3, year):
    
    '''Returns a data frame containing the country, cities, station levels and temperature for 3 countries chosen by user
   
    @param country1: string; country chosen by user
    @param country2: string; country chosen by user
    @param country3: string; country chosen by user
    @param year: integer; year
    @return: dataframe containing data that matches specifications
    '''

    conn = sqlite3.connect("temps.db")
    
    cmd = "SELECT C.country, S.name, S.STNELEV, 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 (year =" + str(year) + ") AND (country= \"" + str(country1) + "\") OR (country= \"" + str(country2) + "\") OR (country= \"" + str(country3) + "\")"

    df = pd.read_sql(cmd, conn)

    #closing connection
    conn.close()

    #returns dataframe with specifications in place
    return df

stations_database("China", "India", "Greece", 1990)

def temp_per_stn(country1, country2, country3, year, n, **kwargs):
    '''Returns 3 scatterplots for 3 countries chosen by the user. Each plot shows the mean temperature vs. station level (log).
   
    @param country1: string; country chosen by user
    @param country2: string; country chosen by user
    @param country3: string; country chosen by user
    @param year: integer; year
    @param n: number of cities in each country user would like to see (first n cities in data)
    @return: Scatterplots that match specifications
    '''
    
    ## clean data to use for plot
    #call on stations_database for three countries
    df = stations_database(country1, country2, country3, year)
    #get mean for each station level, make new column
    df["mean_temp"] = df.groupby(['NAME'])["Temp"].transform(np.mean)
    #same mean for each city, so drop city if already in dataframe
    df = df.drop_duplicates(subset=['NAME'])

    #create three different dataframes based on country, n=# of cities user wants to see
    df1 = df[df["Country"] == country1].head(n)
    df2 = df[df["Country"] == country2].head(n)
    df3 = df[df["Country"] == country3].head(n)
    
    #make subplots
    fig = sp.make_subplots(rows=1, cols=3) 
    
    #add trace for first country
    fig.add_trace(go.Scatter(x=df1['STNELEV'], 
                             y=df1["mean_temp"], 
                             mode='markers', 
                             name=country1),row=1, col=1
    )

    #add trace for second country
    fig.add_trace(go.Scatter(x=df2['STNELEV'], 
                               y=df2["mean_temp"], 
                               mode='markers', 
                               name=country2),row=1, col=2
    )

    #add trace for third country
    fig.add_trace(go.Scatter(x=df3['STNELEV'], 
                               y=df3["mean_temp"], 
                               mode='markers', 
                               name=country3),row=1, col=3
    )
    
    #add x-axis title for each plot
    for i in [1,2,3]:
        fig.update_xaxes(title_text="Station Level (log)", type="log", row=1, col=i)
        
    #add y-axis title
    fig.update_yaxes(title_text="Mean Temperature", row=1, col=1)
    
    #update title and legend
    fig.update_layout(title_text="Mean Temperature per Station Level for " + str(n) + " Cities in " + country1 + ", " + country2 + " and " + country3,
                     showlegend=False)
    
    fig.update_layout(margin={"r":0, "l": 0, "b":0}) 

    fig.show()

temp_per_stn("China", "India", "Greece", 1990, 10)