<jupyter_start><jupyter_text>Netherlands & Japan & CanadaWe first want to visualize the data of The Netherlands<jupyter_code>df_country_codes.head(1)
df_country_codes[(df_country_codes['country'] == 'Netherlands') | 
                 (df_country_codes['country'] == 'Japan') |
                 (df_country_codes['country'] == 'Canada')]<jupyter_output><empty_output><jupyter_text>As we can see, the country code of The Netherlands is `4210`. Therefore we will filter the *life expectancy*, *mortality* and *population datasets on this country code.<jupyter_code>NL_CODE = 4210
JP_CODE = 3160
CA_CODE = 2090<jupyter_output><empty_output><jupyter_text>At the moment the **Life expectancy** dataset contains only data for the Netherlands. The data is obtained from https://www.who.int/data/maternal-newborn-child-adolescent-ageing/indicator-explorer-new/mca/life-expectancy-at-birth. Due to the fact that the *Export* button did not work at that time, I copied it manually in the dataset.<jupyter_code>df_life_exp
nl_life_exp = df_life_exp[(df_life_exp['country'] == 'Netherlands')]
jp_life_exp = df_life_exp[(df_life_exp['country'] == 'Japan')]
ca_life_exp = df_life_exp[(df_life_exp['country'] == 'Canada')]


nl_mortality = df_mortality[(df_mortality['country code'] == NL_CODE)]
jp_mortality = df_mortality[(df_mortality['country code'] == JP_CODE)]
ca_mortality = df_mortality[(df_mortality['country code'] == CA_CODE)]

nl_population = df_population[(df_population['country code'] == NL_CODE)]
jp_population = df_population[(df_population['country code'] == JP_CODE)]
ca_population = df_population[(df_population['country code'] == CA_CODE)]<jupyter_output><empty_output><jupyter_text>So it is best to merge them into one single file. In this way we can standardize the values. You want to merge them on `year` and `sex`.Coding: `{1: male, 2: female, 3: both sexes}`<jupyter_code>life_exp_set = [nl_life_exp, jp_life_exp, ca_life_exp]
mort_set = [nl_mortality, jp_mortality, ca_mortality]
pop_set = [nl_population, jp_population, ca_population]
for df in life_exp_set:
    print(df.sex.unique())
for df in mort_set:
    print(df.sex.unique())
for df in pop_set:
    print(df.sex.unique())
ca_mortality[ca_mortality['sex'] == 9]
ca_population[ca_population['sex'] == 9]
# drop sex 9 as this is not usesful
ca_mortality = ca_mortality[~ca_mortality['sex'].isin([9])]
ca_population = ca_population[~ca_population['sex'].isin([9])]<jupyter_output><empty_output><jupyter_text>It is first important to create an aggregate of sexes for the *mortality* and *population* dataset.<jupyter_code>def generate_ICD_codes(lower, upper, symbol):
    codes = []
    for i in range(lower, upper + 1, 1):
        if i < 10:
            codes.append(f'{symbol}0{i}')
        else:
            codes.append(f'{symbol}{i}')

    return np.array(codes)


def convert_format(series, n=3):
    """Only keep the n first characters of the column"""
    return series.apply(lambda x: x[:n])


def filter_column(df: pd.DataFrame, column: str, elements):
    """


    :param df:
    :param column:
    :param elements:
    :return:
    """
    target = df[column].unique()
    found_elements = find_elements(target, elements)
    dataset = df[df[column].isin(found_elements)]

    return dataset


def groupby_sum(df, by, on):
    """Groups the dataframe by the index columns, and sums the target column and
    returns the result as a dataframe."""

    grouped = df.groupby(by, as_index=False)[on].sum()

    return grouped


def find_elements(target, elements):
    """
    Checks if the targets can be found in an arbitrary list of elements.

    :param elements:
    :param target:
    :return:
    """
    mask = np.isin(target, elements)
    found = np.where(mask, target, '')
    valid = [c for c in found if c != '']

    return valid
class Selector(ABC):

    def __init__(self, file):
        self.file = file

    @abstractmethod
    def get_selection(self):
        pass


class DataFrameSelector(Selector):

    def __init__(self, df):
        if not isinstance(df, pd.DataFrame):
            raise ValueError('Expects a pandas DataFrame.')
        super().__init__(df)

        self.selection = {}

    def filter_column(self, column: str, elements):
        target = self.file[column].unique()
        found_elements = find_elements(target, elements)
        dataset = self.file[self.file[column].isin(found_elements)]
        
        return dataset

    def split_dataframe(self, column: str, labels: List[str], selection: List[np.ndarray]):
        """Expects labels as keys and the selection to be the string to select the dataframe on.

        if unique elements are given then it searches for elements found in the selection and the unique list
        """

        for label, selector in zip(labels, selection):
            dataset = self.filter_column(column, selector)
            self.selection[label] = dataset
            
    def rename_selection(self, column, mapping):
        for k, df in self.selection.items():
            self.selection[k] = df.rename(columns={column: mapping[k]})

    def get_selection(self):
        return self.selection
class Aggregator:

    def __init__(self, df):
        self.df = df
        self.aggregation = None

    def handler(self, df):
        if self.aggregation is None:
            self.aggregation = df
        else:
            self.aggregation = self.aggregation.append(df)

    def calc_aggr(self, by, on, column='', value=None):
        # calculate aggregate and append it to the original
        if not column and not value:
            self.handler(groupby_sum(self.df, by, on))
            return

        aggr = groupby_sum(self.df, by, on)
        aggr[column] = value

        self.handler(aggr)

    def get_aggregation(self, sort_by):
        return self.aggregation.sort_values(sort_by).reset_index(drop=True)
C_codes = generate_ICD_codes(0, 97, 'C')
I_codes = generate_ICD_codes(5, 99, 'I')
E_codes = generate_ICD_codes(10, 13, 'E')
J_codes = generate_ICD_codes(40, 47, 'J')
K_codes = generate_ICD_codes(0, 93, 'K')

code_to_name_map = {
    'C': 'cancer [deaths]',
    'I': 'cardiovascular disease [deaths]',
    'E': 'diabetes mellitus [deaths]',
    'J': 'chronic respiratory diseases [deaths]',
    'K': 'diseases of digestive system [deaths]'
}

name_to_code_map = {
    'cancer [deaths]': 'C',
    'cardiovascular disease [deaths]': 'I',
    'diabetes mellitus [deaths]': 'E',
    'chronic respiratory diseases [deaths]': 'J',
    'diseases of digestive system [deaths]': 'K',
}

code_map = {
    'C': 'C',
    'I': 'I',
    'E': 'E',
    'J': 'J',
    'K': 'K'
}

codes = [C_codes, I_codes, E_codes, J_codes, K_codes]
labels = ['C', 'I', 'E', 'J', 'K']
def convert_format(items: list, n=3):
    """Only keep the n first characters of the column"""
    series = pd.Series(items)
    return series.apply(lambda x: x[:n])
nl_causes = convert_format(nl_mortality['cause'], 3)
jp_causes = convert_format(jp_mortality['cause'], 3)
ca_causes = convert_format(ca_mortality['cause'], 3)

nl_unique_causes = nl_causes.unique()
jp_unique_causes = jp_causes.unique()
ca_unique_causes = ca_causes.unique()

nl_mortality.loc[:, 'cause'] = nl_causes
jp_mortality.loc[:, 'cause'] = jp_causes
ca_mortality.loc[:, 'cause'] = ca_causes
nl_mortality
# Select correct data
nl_mortality_selector = DataFrameSelector(nl_mortality)
nl_mortality_selector.split_dataframe('cause', labels, codes)
nl_mortality_selector.rename_selection('deaths', code_to_name_map)
nl_mortality_sets = nl_mortality_selector.get_selection()

jp_mortality_selector = DataFrameSelector(jp_mortality)
jp_mortality_selector.split_dataframe('cause', labels, codes)
jp_mortality_selector.rename_selection('deaths', code_to_name_map)
jp_mortality_sets = jp_mortality_selector.get_selection()

ca_mortality_selector = DataFrameSelector(ca_mortality)
ca_mortality_selector.split_dataframe('cause', labels, codes)
ca_mortality_selector.rename_selection('deaths', code_to_name_map)
ca_mortality_sets = ca_mortality_selector.get_selection()

# create aggregates
for k, df in nl_mortality_sets.items():
    aggr = Aggregator(df)
    aggr.calc_aggr(by=['year', 'sex'], on=code_to_name_map[k])
    aggr.calc_aggr(by=['year'], on=code_to_name_map[k], column='sex', value=3)
    
    data = aggr.get_aggregation(sort_by='year')
    data['country'] = 'Netherlands' 
    
    nl_mortality_sets[k] = data
    
    
for k, df in jp_mortality_sets.items():
    aggr = Aggregator(df)
    aggr.calc_aggr(by=['year', 'sex'], on=code_to_name_map[k])
    aggr.calc_aggr(by=['year'], on=code_to_name_map[k], column='sex', value=3)
    
    data = aggr.get_aggregation(sort_by='year')
    data['country'] = 'Japan'
    
    jp_mortality_sets[k] = data
    
for k, df in ca_mortality_sets.items():
    aggr = Aggregator(df)
    aggr.calc_aggr(by=['year', 'sex'], on=code_to_name_map[k])
    aggr.calc_aggr(by=['year'], on=code_to_name_map[k], column='sex', value=3)
    
    data = aggr.get_aggregation(sort_by='year')
    data['country'] = 'Canada'
    
    ca_mortality_sets[k] = data
nl_mortality_sets['C'].head()
jp_mortality_sets['C'].head()
ca_mortality_sets['C'].head()<jupyter_output><empty_output><jupyter_text>Population Population is already properly formatted, thus no need to make a selection.<jupyter_code>aggr = Aggregator(nl_population)
aggr.calc_aggr(by=['year', 'sex'], on='population')
aggr.calc_aggr(by=['year'], on='population', column='sex', value=3)
data = aggr.get_aggregation(sort_by='year')
data['country'] = 'Netherlands'

nl_pop_agg = data
nl_pop_agg.isna().sum()
aggr = Aggregator(jp_population)
aggr.calc_aggr(by=['year', 'sex'], on='population')
aggr.calc_aggr(by=['year'], on='population', column='sex', value=3)
data = aggr.get_aggregation(sort_by='year')
data['country'] = 'Japan'

jp_pop_agg = data
jp_pop_agg.isna().sum()
aggr = Aggregator(ca_population)
aggr.calc_aggr(by=['year', 'sex'], on='population')
aggr.calc_aggr(by=['year'], on='population', column='sex', value=3)
data = aggr.get_aggregation(sort_by='year')
data['country'] = 'Canada'

ca_pop_agg = data
ca_pop_agg.isna().sum()<jupyter_output><empty_output><jupyter_text>Combine all<jupyter_code># nl_pop_agg = nl_pop_agg[nl_pop_agg['year'] >= 1996]
# nl_life_exp = nl_life_exp[(nl_life_exp['year'] >= 1996) & (nl_life_exp['year'] <= 2018)]

# jp_pop_agg = jp_pop_agg[jp_pop_agg['year'] >= 1996]
# jp_life_exp = jp_life_exp[(jp_life_exp['year'] >= 1996) & (jp_life_exp['year'] <= 2018)]

# ca_pop_agg = ca_pop_agg[ca_pop_agg['year'] >= 1996]
# ca_life_exp = ca_life_exp[(ca_life_exp['year'] >= 1996) & (ca_life_exp['year'] <= 2018)]
nl_pop_agg.isna().sum()
jp_pop_agg.isna().sum()
ca_pop_agg.isna().sum()
nl_dfs = []
jp_dfs = []
ca_dfs = []

nl_dfs.extend([df for df in nl_mortality_sets.values()])
jp_dfs.extend([df for df in jp_mortality_sets.values()])
ca_dfs.extend([df for df in ca_mortality_sets.values()])

nl_dfs.append(nl_pop_agg)
nl_dfs.append(nl_life_exp)
jp_dfs.append(jp_pop_agg)
jp_dfs.append(jp_life_exp)
ca_dfs.append(ca_pop_agg)
ca_dfs.append(ca_life_exp)
def multi_merge(dfs, on):
    merge = partial(pd.merge, on=on, how='outer')
    dataset = reduce(merge, dfs)
    
    return dataset
nl_dataset = multi_merge(nl_dfs, on=['year', 'sex', 'country']).sort_values('year')
nl_dataset = dataset[(dataset['year'] > 1995) & (dataset['year'] < 2019)]

nl_dataset['non-communicable chronic disease [deaths]'] = 0
for disease in name_to_code_map.keys():
    nl_dataset['non-communicable chronic disease [deaths]'] += dataset[disease]
    
nl_dataset = nl_dataset.sort_values(['year', 'sex']).reset_index(drop=True)
jp_dataset = multi_merge(jp_dfs, on=['year', 'sex', 'country']).sort_values('year')
jp_dataset = dataset[(dataset['year'] > 1995) & (dataset['year'] < 2019)]

jp_dataset['non-communicable chronic disease [deaths]'] = 0
for disease in name_to_code_map.keys():
    jp_dataset['non-communicable chronic disease [deaths]'] += dataset[disease]
    
jp_dataset = jp_dataset.sort_values(['year', 'sex']).reset_index(drop=True)
ca_dataset = multi_merge(ca_dfs, on=['year', 'sex', 'country']).sort_values('year')
ca_dataset = dataset[(dataset['year'] > 1995) & (dataset['year'] < 2019)]

ca_dataset['non-communicable chronic disease [deaths]'] = 0
for disease in name_to_code_map.keys():
    ca_dataset['non-communicable chronic disease [deaths]'] += dataset[disease]
    
ca_dataset = ca_dataset.sort_values(['year', 'sex']).reset_index(drop=True)
full_dataset = pd.concat([nl_dataset, jp_dataset, ca_dataset]).reset_index(drop=True)
full_dataset
full_dataset.to_csv('../data/prepared/full_dataset.csv', index=False)
cols = ['cancer [deaths]',
       'cardiovascular disease [deaths]', 'diabetes mellitus [deaths]',
       'chronic respiratory diseases [deaths]',
       'diseases of digestive system [deaths]',
       'non-communicable chronic disease [deaths]']
full_dataset_standardized = full_dataset
full_dataset_standardized[cols] = full_dataset[cols].divide(full_dataset['population'], axis=0) * 100000

full_dataset_standardized

full_dataset_standardized.to_csv('../data/prepared/full_dataset_standardized.csv', index=False)<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.14311745501480524, 'fraction_numerical': 0.009718320552729481, 'mean_word_length': 4.898790864308106, 'pattern_counts': {'":': 0, '<': 36, '<?xml version=': 0, '>': 38, 'https://': 1, 'lorem ipsum': 0, 'www.': 1, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 1, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '7', 'dir': 'jupyter-structured-clean-dedup', 'id': '494668', 'license': 'Apache-2.0', 'n_tokens_mistral': 4914, 'n_tokens_neox': 4561, 'n_words': 971, 'path': 'src/notebooks/Data selection.ipynb', 'repo_name': 'ddlatumalea/disease_and_life', 'size': '13171'}