from cloudmesh.common.util import download as file_download
from cloudmesh.timeseries.source import Source
from cloudmesh.common.console import Console
from cloudmesh.common.util import banner
from cloudmesh.common.StopWatch import StopWatch
from cloudmesh.timeseries.source import Data

from pathlib import Path
import pandas as pd
import numpy as np


class CDCCity(Data):
    """
    This class downloads and formats the 500 cities data provided by CDC

    Usage:

    cdc = CDCCity()
    cdc.download()
    # cdc.eda()
    cdc.format()

    cdc.data.to_csv(Path(Source.directory, '500cities_formatted.csv'))

    Source:

    - api: https://chronicdata.cdc.gov/resource/6vp6-wxuq.csv
    - csv: https://chronicdata.cdc.gov/api/views/6vp6-wxuq/rows.csv?accessType=DOWNLOAD

    """

    # _directory = Source.directory  #Notebook moved to cdc folder, needs change
    _directory = "../../data"
    _url = "https://chronicdata.cdc.gov/api/views/6vp6-wxuq/rows.csv?accessType=DOWNLOAD"

    def __init__(self, url=_url, directory=_directory):
        Data.__init__(self)

        self.url = url
        self._directory = directory
        self.target = Path(directory, "500cities.csv")
        self.data = pd.DataFrame()

    def load(self):
        """
        Load the data into the data frame
        :return: self.data as a data frame
        """
        self.data = pd.read_csv(self.target)

        self.data = self.data[(self.data['Year'] == 2017) & (
               self.data.GeographicLevel == "Census Tract")]

    def download(self):
        """
        Downloads the data from 500cities cdc url
        :return: self.data as a data frame
        """
        StopWatch.start("download")
        file_download(self.url, self.target)
        StopWatch.stop("download")
        t = StopWatch.get("download")
        Console.ok(f"Download completed in {t} seconds for {self.target}")
        self.load()

    @staticmethod
    def _remove_nulls(df):
        """
        This method:
        1) removes unnecessary columns
        2) replaces nulls with 0 is 'Data_Value'
        :param df: input data frame
        :return: output data frame without nulls
        """
        Console.info("Removing Nulls")
        df.drop(['Data_Value_Footnote',
                 'Data_Value_Footnote_Symbol',
                 'High_Confidence_Limit',
                 'Low_Confidence_Limit',
                 'DataSource', 'Category',
                 'UniqueID',
                 'Measure',
                 'Data_Value_Unit',
                 'Data_Value_Type'],
                axis=1,
                inplace=True)
        Console.info("Replace Nulls with 0")
        df.loc[df.Data_Value.isna(), 'Data_Value'] = 0
        return df

    @staticmethod
    def _get_fips(df):
        """
        This method extracts county FIPS as first 5 digits of 'TractFIPS'
        :param df: input 500cities dataframe
        :return: 500cities dataframe with additional column FIPS
        """
        Console.info("Create FIPS column")
        df['FIPS'] = df['TractFIPS'].astype(np.int64).map(
            lambda x: f'{x:0>11}').astype(str).str[:5]
        return df

    def prepare(self):
        """
        This method prepares the data in needed format
        :return: reformatted data frame
        """
        Console.info("Data prepare start")
        self.data = (self.data
                     .pipe(self._remove_nulls)
                     .pipe(self._get_fips)
                     .pipe(self._groups)
                     .pipe(self._widen))
        Console.info("Data prepare end")

    @staticmethod
    def _groups(df):
        """
        This method groups dataset on FIPS and MeasureId to calculate measure
        value at county level
        :param df: input dataframe
        :return: a long dataframe with measure values at county level
        """
        Console.info("\n... Sum FIPS groups")
        o_list = list()
        i = 0
        for a, b in df.groupby(['FIPS', 'MeasureId']):
            o_list.append([a[0], a[1],
                           round((b['Data_Value'] * b['PopulationCount']).sum()
                                 / b['PopulationCount'].sum(), 5)])
            i += 1

        Console.ok(f"Found {i} FIPS and grouped them.")
        return pd.DataFrame(o_list, columns=['FIPS', 'VAR', 'Val'])

    @staticmethod
    def _widen(df):
        """
        This method creates a wide dataset from long dataset
        :param df: input dataframe
        :return: wide dataframe with one row per county
        """
        Console.info("\nCreate wide df")
        df = df.pivot(index='FIPS', columns='VAR', values='Val')
        return df.reset_index()

    def eda(self):
        """
        performs basic EDA on 500 cities data. Uses 'display' method to show
        output in jupyter.
        :return: displays distribution of 'CategoryID', 'MeasureId' and null
        counts of each attribute
        """
        print()
        Console.info("Check distribution of CategoryID and MeasureId:\n")
        display(self.data.groupby(['CategoryID', 'MeasureId']).size())
        print()
        Console.info("Checking null values:\n")
        display(self.show_missing())

    def show_missing(self):
        """
        Method to show count of null values in each column
        :return: dataframe with null value counts
        """
        df = self.data
        print("=" * 50, "Showing Nulls", "=" * 50)
        percent = (df.isnull().sum() / df.isnull().count() * 100).sort_values(
            ascending=False).round(2)
        sum_missing = df.isna().sum().sort_values(ascending=False)
        total = df.isnull().count()
        missing_data = pd.concat([percent, sum_missing, total], axis=1,
                                 keys=['Null Percent', "Missing Count",
                                       "Total records"], sort=False)
        print("\nChecking features for null values:\n")
        # display(missing_data.sort_values(by=['Null Percent'],
        # ascending=False))
        return missing_data.sort_values(by=['Null Percent'], ascending=False)


