<commit_before>"""
Builds CHD indicators from FTS queries
"""

import fts_queries
import os
import datetime
import sqlite3
import pandas as pd
import pandas.io.sql as sql


# note relying on strings is fragile - could break if things get renamed in FTS
# we don't seem to have much in the way of alternatives, other than changing the FTS API
DONOR_ERF = "Emergency Response Fund (OCHA)"
DONOR_CERF = "Central Emergency Response Fund"
DONOR_CHF = "Common Humanitarian Fund"
POOLED_FUNDS = [DONOR_CERF, DONOR_ERF, DONOR_CHF]


class PooledFundCacheByYear(object):
    """
    Caches global pooled fund amounts by year
    """
    def __init__(self):
        self.year_cache = {}

    # TODO investigate why this doesn't match FTS reports exactly for all values
    def get_pooled_global_allocation_for_year(self, year):
        if year not in self.year_cache:
            global_funding_by_donor =\
                fts_queries.fetch_grouping_type_json_for_year_as_dataframe('funding', year, 'donor', 'organization')

            pooled_funds_amounts = global_funding_by_donor.funding.loc[POOLED_FUNDS]

            self.year_cache[year] = pooled_funds_amounts

        return self.year_cache[year]


class CountryFundingCacheByYear(object):
    """
    Caches total funding amounts for each country by year
    """
    def __init__(self):
        self.year_cache = {}

        self.country_iso_code_to_name = {}
        countries = fts_queries.fetch_countries_json_as_dataframe()
        for country_id, row in countries.iterrows():
            self.country_iso_code_to_name[row['iso_code_A']] = row['name']

    def get_total_country_funding_for_year(self, country_code, year):
        if year not in self.year_cache:
            funding_by_country =\
                fts_queries.fetch_grouping_type_json_for_year_as_dataframe('funding', year, 'country', 'country')

            self.year_cache[year] = funding_by_country

        # possibly no funding at all in that year
        funding_series = self.year_cache[year]
        if funding_series.empty:
            return 0

        country_name = self.country_iso_code_to_name[country_code]

        if country_name in funding_series.funding:
            return funding_series.funding.loc[country_name]
        else:
            return 0


POOLED_FUND_CACHE = PooledFundCacheByYear()
COUNTRY_FUNDING_CACHE = CountryFundingCacheByYear()

FUNDING_STATUS_PLEDGE = "Pledge"

ORG_TYPE_NGOS = 'NGOs'
ORG_TYPE_PRIVATE_ORGS = 'Private Orgs. & Foundations'
ORG_TYPE_UN_AGENCIES = 'UN Agencies'


# holds IndicatorValue objects until we are ready to put them in a dataframe
VALUES = []


class IndicatorValue(object):
    def __init__(self, indicator, region, year, value):
        self.indicator = indicator
        self.region = region
        self.year = year
        self.value = value


def add_row_to_values(indicator, region, year, value):
    # perhaps the wrong place to add this, but filter out distant future data
    if year > datetime.date.today().year + 5:
        return

    VALUES.append(IndicatorValue(indicator, region, year, value))


def get_values_as_dataframe():
    indicators = [ind_value.indicator for ind_value in VALUES]
    regions = [ind_value.region for ind_value in VALUES]
    years = [ind_value.year for ind_value in VALUES]
    values = [ind_value.value for ind_value in VALUES]

    return pd.DataFrame(
        {'indicator': indicators, 'region': regions, 'year': years, 'value': values},
        columns=['indicator', 'region', 'year', 'value']
    )


def write_values_as_scraperwiki_style_csv(base_dir):
    values = get_values_as_dataframe()
    values.replace(to_replace=[float(inf)],
                   value=['na'],
                   inplace=True)
    values['dsID'] = 'fts'
    values['is_number'] = 1
    values['source'] = ''
    values = values.rename(columns={'indicator': 'indID', 'year': 'period'})
    values = values[['dsID', 'region', 'indID', 'period', 'value', 'is_number', 'source']]

    filename = os.path.join(base_dir, 'value.csv')
    values.to_csv(filename, index=False)


def write_values_as_scraperwiki_style_sql(base_dir):
    TABLE_NAME = "value"
    values = get_values_as_dataframe()
    values['dsID'] = 'fts'
    values['is_number'] = 1
    values['source'] = ''
    values = values.rename(columns={'indicator': 'indID', 'year': 'period'})
    values = values[['dsID', 'region', 'indID', 'period', 'value', 'is_number', 'source']]

    filename = os.path.join(base_dir, 'ocha.db')
    sqlite_db = sqlite3.connect(filename)
    sqlite_db.execute("drop table if exists {};".format(TABLE_NAME))
    values = values.reset_index()
    sql.write_frame(values, TABLE_NAME, sqlite_db)
    print values

def get_values_joined_with_indicators():
    """
    Useful for debugging
    """
    values = get_values_as_dataframe()
    indicators = pd.read_csv('indicator.csv', index_col='indID')
    return pd.merge(left=values, right=indicators, left_on='indicator', right_index=True)<commit_msg>bugfix: apply to sql, 'inf' is string not var<commit_after>"""
Builds CHD indicators from FTS queries
"""

import fts_queries
import os
import datetime
import sqlite3
import pandas as pd
import pandas.io.sql as sql


# note relying on strings is fragile - could break if things get renamed in FTS
# we don't seem to have much in the way of alternatives, other than changing the FTS API
DONOR_ERF = "Emergency Response Fund (OCHA)"
DONOR_CERF = "Central Emergency Response Fund"
DONOR_CHF = "Common Humanitarian Fund"
POOLED_FUNDS = [DONOR_CERF, DONOR_ERF, DONOR_CHF]


class PooledFundCacheByYear(object):
    """
    Caches global pooled fund amounts by year
    """
    def __init__(self):
        self.year_cache = {}

    # TODO investigate why this doesn't match FTS reports exactly for all values
    def get_pooled_global_allocation_for_year(self, year):
        if year not in self.year_cache:
            global_funding_by_donor =\
                fts_queries.fetch_grouping_type_json_for_year_as_dataframe('funding', year, 'donor', 'organization')

            pooled_funds_amounts = global_funding_by_donor.funding.loc[POOLED_FUNDS]

            self.year_cache[year] = pooled_funds_amounts

        return self.year_cache[year]


class CountryFundingCacheByYear(object):
    """
    Caches total funding amounts for each country by year
    """
    def __init__(self):
        self.year_cache = {}

        self.country_iso_code_to_name = {}
        countries = fts_queries.fetch_countries_json_as_dataframe()
        for country_id, row in countries.iterrows():
            self.country_iso_code_to_name[row['iso_code_A']] = row['name']

    def get_total_country_funding_for_year(self, country_code, year):
        if year not in self.year_cache:
            funding_by_country =\
                fts_queries.fetch_grouping_type_json_for_year_as_dataframe('funding', year, 'country', 'country')

            self.year_cache[year] = funding_by_country

        # possibly no funding at all in that year
        funding_series = self.year_cache[year]
        if funding_series.empty:
            return 0

        country_name = self.country_iso_code_to_name[country_code]

        if country_name in funding_series.funding:
            return funding_series.funding.loc[country_name]
        else:
            return 0


POOLED_FUND_CACHE = PooledFundCacheByYear()
COUNTRY_FUNDING_CACHE = CountryFundingCacheByYear()

FUNDING_STATUS_PLEDGE = "Pledge"

ORG_TYPE_NGOS = 'NGOs'
ORG_TYPE_PRIVATE_ORGS = 'Private Orgs. & Foundations'
ORG_TYPE_UN_AGENCIES = 'UN Agencies'


# holds IndicatorValue objects until we are ready to put them in a dataframe
VALUES = []


class IndicatorValue(object):
    def __init__(self, indicator, region, year, value):
        self.indicator = indicator
        self.region = region
        self.year = year
        self.value = value


def add_row_to_values(indicator, region, year, value):
    # perhaps the wrong place to add this, but filter out distant future data
    if year > datetime.date.today().year + 5:
        return

    VALUES.append(IndicatorValue(indicator, region, year, value))


def get_values_as_dataframe():
    indicators = [ind_value.indicator for ind_value in VALUES]
    regions = [ind_value.region for ind_value in VALUES]
    years = [ind_value.year for ind_value in VALUES]
    values = [ind_value.value for ind_value in VALUES]

    return pd.DataFrame(
        {'indicator': indicators, 'region': regions, 'year': years, 'value': values},
        columns=['indicator', 'region', 'year', 'value']
    )


def write_values_as_scraperwiki_style_csv(base_dir):
    values = get_values_as_dataframe()
    values.replace(to_replace=[float('inf')],
                   value=['na'],
                   inplace=True)
    values['dsID'] = 'fts'
    values['is_number'] = 1
    values['source'] = ''
    values = values.rename(columns={'indicator': 'indID', 'year': 'period'})
    values = values[['dsID', 'region', 'indID', 'period', 'value', 'is_number', 'source']]

    filename = os.path.join(base_dir, 'value.csv')
    values.to_csv(filename, index=False)


def write_values_as_scraperwiki_style_sql(base_dir):
    TABLE_NAME = "value"
    values = get_values_as_dataframe()
    values.replace(to_replace=[float('inf')],
                   value=['na'],
                   inplace=True)
    values['dsID'] = 'fts'
    values['is_number'] = 1
    values['source'] = ''
    values = values.rename(columns={'indicator': 'indID', 'year': 'period'})
    values = values[['dsID', 'region', 'indID', 'period', 'value', 'is_number', 'source']]

    filename = os.path.join(base_dir, 'ocha.db')
    sqlite_db = sqlite3.connect(filename)
    sqlite_db.execute("drop table if exists {};".format(TABLE_NAME))
    values = values.reset_index()
    sql.write_frame(values, TABLE_NAME, sqlite_db)
    print values

def get_values_joined_with_indicators():
    """
    Useful for debugging
    """
    values = get_values_as_dataframe()
    indicators = pd.read_csv('indicator.csv', index_col='indID')
    return pd.merge(left=values, right=indicators, left_on='indicator', right_index=True)
{'fraction_non_alphanumeric': 0.1055851588711269, 'fraction_numerical': 0.001381488059996053, 'mean_word_length': 3.5387371249440216, 'pattern_counts': {'":': 0, '<': 3, '<?xml version=': 0, '>': 5, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'dir': 'git-commits-cleaned', 'id': '6599118', 'n_tokens_mistral': 3335, 'n_tokens_neox': 3204, 'n_words': 766}