<jupyter_start><jupyter_text>--------- AdviceBased on the finding I created the following classes:- Cleaner: to clean the data `preprocessing.py`.- Selector and Aggregator: to select data and create aggregates `preparation.py`.- Dashboard for visualization `main.py`---------  Data UnderstandingOnly analyzes the dataset of The NetherlandsData Understanding* Part 1*Life Expectancy at Birth** Part 2Country Codes* Part 3Population (POP)* Part 4Morticd10 part 1 - 5This notebook will describe the different data files and how they are built.I'll use the following files:- life_expectancy_at_birth.xlsx- country_codes- pop- Morticd10_part1- Morticd10_part2- Morticd10_part3- Morticd10_part4- Morticd10_part5<jupyter_code>from pathlib import Path
import re

import pandas as pd
import numpy as np
# use config yaml
datadir = Path('../data')<jupyter_output><empty_output><jupyter_text>1) Life Expectancy at BirthSource: https://www.who.int/data/maternal-newborn-child-adolescent-ageing/indicator-explorer-new/mca/life-expectancy-at-birthIndicator name: Life expectancy at birth (years)The indicator reflects the overall mortality level of a population. Age groups include:- Children, adolescents, adults and elderly.Definition: The average number of years that a newborn could expect to live, if he or she were to pass through life exposed to the sex- and age-specific death rates prevailing at the time of his or her birth, for a specific year, in a given country, territory, or geographic area.WHO used a specific method of estimation which can deviate from the official estimation of the Member State. I compared the data and in practise the deviations are very small (< 0.5). The WHO also provides more data, thus it outweights the cons.  i) Meaning of columns<jupyter_code>df = pd.read_excel(Path(datadir, 'netherlands_life_exp.xlsx'))
df = df.sort_values('Year')
df.head(3)<jupyter_output><empty_output><jupyter_text>Column names & their definitions:**Year**: the year of focus according to the Gregorian calendar.**WHO region**: the continent in the world.**World Bank Income Group**: economic state of the *Country*. **Country ISO Code**: the ISO code of the *Country*.**Country**: the country of focus.**Sex**: a categorical value representing the biological sex, consisting of *Both sexes*, *Female*, and *Male*.**Global**: *meaning unknown and will not be used*.**Value**: the life expectancy in that *Year*.  ii) Characteristics of ValuesBased on the description of the columns above, the following columns are important for further analysis:- General- Year- Sex- Value  General<jupyter_code>df.shape
df.isna().sum()<jupyter_output><empty_output><jupyter_text>In general there are 450 rows and 8 columns.There are no missing values that are important.Every year consists of three rows, consisting of a row for male, female and both sexes data.  Year<jupyter_code>df['Year'].dtype
df['Year'].unique().size
# Check the lowest and highest year
min_y = df['Year'].min()
max_y = df['Year'].max()

# Check if the years are sequential
if (np.arange(min_y, max_y + 1) == df['Year'].unique()).all():
    print('The years range from {} to {}.'.format(min_y, max_y))
else:
    print('THE YEARS ARE NOT SEQUENTIAL. THERE ARE MISSING YEARS!')<jupyter_output>The years range from 1950 to 2099.<jupyter_text>Sex<jupyter_code>df['Sex'].dtype
df['Sex'].unique().size
df['Sex'].unique()<jupyter_output><empty_output><jupyter_text>The `Sex` column can have one of the following values: `Both sexes`, `Female`, `Male`.  Value<jupyter_code>df['Value'].dtype
df['Value'].unique().size
min_v = df['Value'].min()
max_v = df['Value'].max()

min_i = df['Value'].argmin()
max_i = df['Value'].argmax()

print('Lowest life expectancy measured: {} in {}'.format(min_v, df['Year'].iloc[min_i]))
print('Highest life expectancy measured: {} in {}'.format(max_v, df['Year'].iloc[max_i]))<jupyter_output>Lowest life expectancy measured: 70.236 in 1950
Highest life expectancy measured: 92.776 in 2099<jupyter_text>The life expectancy was lowest in 1950 and will be expected to be the highest in 2099. WHO has extrapolated the life expectancy to 2099. This means, it is just an estimation and if values in the future will be used, then it must be used with caution.The `Value` column name is not descriptive enough. It is advices to change it to `Life expectancy at birth`.------------ **CONCLUSION**:Every year that is measured consists of 3 rows:- Row 1 contains the life expectancy for Males and Females together (mean).- Row 2 contains the life expectancy for Females.- Row 3 contains the life expectancy for Males.There are *NO* missing values.The years range from `1950` till `2099` using extrapolation.The `Value` column is not descriptive. Change the name to `Life expectancy at birth`.------------  2) Country Codes* Part 1Life Expectancy at Birth* Part 2*Country Codes** Part 3Population (POP)* Part 4Morticd10 part 1 - 5<jupyter_code>df = pd.read_csv(Path(datadir, 'country_codes'))
df.head(3)<jupyter_output><empty_output><jupyter_text>Column names & their definitions:**country**: The country number.**name**: the name of the country.<jupyter_code>df[df['name'] == 'Netherlands']<jupyter_output><empty_output><jupyter_text>--------**CONCLUSION**:The Netherlands has country code `4210`.------  3) Population POP* Part 1Life Expectancy at Birth* Part 2Country Codes* Part 3*Population POP** Part 4Morticd10 part 1 - 5<jupyter_code>df = pd.read_csv(Path(datadir, 'pop'))
df.head(3)<jupyter_output><empty_output><jupyter_text>Column names & their definitions:**Country**: the country code, as defined in part 2.**Admin1**: specified pertinent. If blank, data refers to the country.**SubDiv**: Category of data, Annex Table 2 in the manual. If blank, data refers to the country.**Year**: The year to which data refer.**Sex**: categorical value where 1 means male and 2 means female.**Frmat**: Age-group format for breakdown, see Annex Table 1 in the manual.**Pop1**: Population at all ages.**Pop2**: Population at age 0.**Pop3**: Poplatuion at age 1.**Pop4**: Poplatuion at age 2.**Pop5**: Poplatuion at age 3.**Pop6**: Poplatuion at age 4.**Pop7**: Poplatuion at age 5-9.**Pop8**: Poplatuion at age 10-14**Pop9**: Poplatuion at age 15-19**Pop10**: Poplatuion at age 20-24**Pop11**: Poplatuion at age 25-29**Pop12**: Poplatuion at age 30-34**Pop13**: Poplatuion at age 35-39.**Pop14**: Poplatuion at age 40-44**Pop15**: Poplatuion at age 45-49**Pop16**: Poplatuion at age 50-54**Pop17**: Poplatuion at age 55-59**Pop18**: Poplatuion at age 60-64**Pop19**: Poplatuion at age 65-69**Pop20**: Poplatuion at age 70-74**Pop21**: Poplatuion at age 75-79**Pop22**: Poplatuion at age 80-84**Pop23**: Poplatuion at age 85-89**Pop24**: Poplatuion at age 90-94**Pop25**: Poplatuion at age 95 and over**Pop26**: Poplatuion at age unspecified**Lb**: Live births. *What this actually means is unknown.  ii) Characteristics of ValuesBased on the description of the columns above, the following columns are important for further analysis:- General- Year- Sex- Pop1, we are only interested in the whole population.  General<jupyter_code>nl = df[df['Country'] == 4210]
nl.head()
nl.shape
nl.isna().sum()<jupyter_output><empty_output><jupyter_text>The columns that are important do not have missing values. The dataframe consists of two rows for each year, where one row given information about males and the other about females.  Year<jupyter_code>nl['Year'].head(10)
# Check the lowest and highest year
min_y = nl['Year'].min()
max_y = nl['Year'].max()

# Check if the years are sequential
if (np.arange(min_y, max_y + 1) == nl['Year'].unique()).all():
    print('The years range from {} to {}.'.format(min_y, max_y))
else:
    print('THE YEARS ARE NOT SEQUENTIAL. THERE ARE MISSING YEARS!')<jupyter_output>The years range from 1950 to 2018.<jupyter_text>Sex<jupyter_code>nl['Sex'].dtype
nl['Sex'].unique()<jupyter_output><empty_output><jupyter_text>Where 1 means male, and 2 means female. It is possible to change this to categorical values as a string with a mapping.  Pop1<jupyter_code>nl['Pop1'].dtype
# Total population in 2010
# CBS returns 16 580 000
# The World Bank returns 16 620 000
nl[nl['Year'] == 2010]['Pop1'].sum()<jupyter_output><empty_output><jupyter_text>There is a slight divergence between the data from WHO and CBS, but it is negligible. ---------**CONCLUSION**:Every year consists of two rows, namely:- Row 1 represents the male population.- Row 2 represents the female population.Year ranges from `1950` to `2018`.'Sex' consists of a mapping: 1 -> Male, 2 -> Female.---------  4) Morticd10* Part 1Life Expectancy at Birth* Part 2Country Codes* Part 3Population POP* Part 4*Morticd10* In total, there are five files that make up the mortality rate.- Morticd10_part1- Morticd10_part2- Morticd10_part3- Morticd10_part4- Morticd10_part5  i) Meaning of columnsAll files contain the same columns.<jupyter_code>df = pd.read_csv(Path(datadir, 'Morticd10_part1'))
df.head(3)
df['Year']<jupyter_output><empty_output><jupyter_text>Column names & their definitions:**Country**: the country code, as defined in part 2.**Admin1**: specified pertinent. If blank, data refers to the country.**SubDiv**: Category of data, Annex Table 2 in the manual. If blank, data refers to the country.**Year**: The year to which data refer.**List**: List of ICD revision used, see Annex Table 2 in the manual.**Cause**: Code of Cause of Death.**Sex**: categorical value where 1 is male, 2 is female, and 9 is unspecified.**Frmat**: Age-group format breakdown of deaths, see Annex Table 1 in the manual.**IM_Frmat**: Age format for breakdown of infant deaths (0 year). see Annex Table 1 in the manual.**Deaths1**: Deaths at all ages.**Deaths2**: Deaths at age 0.**Deaths3**: Deaths at age 1.**Deaths4**: Deaths at age 2.**Deaths5**: Deaths at age 3.**Deaths6**: Deaths at age 4.**Deaths7**: Deaths at age 5-9.**Deaths8**: Deaths at age 10-14.**Deaths9**: Deaths at age 15-19.**Deaths10**: Deaths at age 20-24.**Deaths11**: Deaths at age 25-29.**Deaths12**: Deaths at age 30-34.**Deaths13**: Deaths at age 35-39.**Deaths14**: Deaths at age 40-44.**Deaths15**: Deaths at age 45-49.**Deaths16**: Deaths at age 50-54.**Deaths17**: Deaths at age 55-59.**Deaths18**: Deaths at age 60-64.**Deaths19**: Deaths at age 65-69.**Deaths20**: Deaths at age 70-74.**Deaths21**: Deaths at age 75-79.**Deaths22**: Deaths at age 80-84.**Deaths23**: Deaths at age 85-89.**Deaths24**: Deaths at age 90-94.**Deaths25**: Deaths at age 95 years and above**Deaths26**: Deaths at age unspecified**IM_Deaths1**: Infant deaths at age 0 day**IM_Deaths2**: Infant deaths at age 1-6 days**IM_Deaths3**: Infant deaths at age 7-27 days**IM_Deaths4**: Infant deaths at age 28-364 days  ii) Characteristics of ValuesBased on the description of the columns above, the following Factors are important for further analysis:- General- Year- List- Sex- Cause- Deaths1 As the data consists of 5 part, they should be concatenated.<jupyter_code>df1 = pd.read_csv(Path(datadir, 'Morticd10_part1'))
df2 = pd.read_csv(Path(datadir, 'Morticd10_part2'))
df3 = pd.read_csv(Path(datadir, 'Morticd10_part3'))
df4 = pd.read_csv(Path(datadir, 'Morticd10_part4'))
df5 = pd.read_csv(Path(datadir, 'Morticd10_part5'))

data = [df1, df2, df3, df4, df5]
# select The Netherlands
for i, d in enumerate(data):
    data[i] = d[d['Country'] == 4210]
# concatenate them into one
df = pd.concat(data).sort_values('Year')<jupyter_output><empty_output><jupyter_text>General<jupyter_code>df.shape
df[df['Sex'] == 1].shape[0] + df[df['Sex'] == 2].shape[0]
df.isna().sum()<jupyter_output><empty_output><jupyter_text>The columns that are important do not have missing values.  Year<jupyter_code>df['Year'].dtype
df['Year'].unique()
# Check the lowest and highest year
min_y = df['Year'].min()
max_y = df['Year'].max()

# Check if the years are sequential
if (np.arange(min_y, max_y + 1) == df['Year'].unique()).all():
    print('The years range from {} to {}.'.format(min_y, max_y))
else:
    print('THE YEARS ARE NOT SEQUENTIAL. THERE ARE MISSING YEARS!')<jupyter_output>The years range from 1996 to 2018.<jupyter_text>ListList of ICD revision used, see Annex Table 2 in the manual.<jupyter_code>df['List'].dtype
df['List'].unique()<jupyter_output><empty_output><jupyter_text>`104` is saved as two seperate datatypes, namely as an `int` and a `str`. Therefore, convert the column to type `str`. Two types of revisions are used, namelijk the `10M` and `104` revision. The revisions have the following description according to the documentation:- `10M`: CD10 3 and 4 (detailed) character list. Pls note that when a 4th character code is given it is therefore not included in a 3 character code. All records are mutually exclusive.- `104`: ICD10 4  (detailed) character list  Sexcategorical value where 1 is male, 2 is female, and 9 is unspecified.<jupyter_code>df['Sex'].dtype
df['Sex'].unique()<jupyter_output><empty_output><jupyter_text>The Netherlands has data for male and female sexes.  CauseCode of Cause of Death.These codes can be found in the ICD<jupyter_code>df['Cause'].dtype
df['Cause'].unique().size<jupyter_output><empty_output><jupyter_text>There are in total 5243 reported causes of death. Only the noncommunicable diseases are relevant to answer the research question. Code source for noncommunicable disease: https://www.euro.who.int/__data/assets/pdf_file/0007/350278/Fact-sheet-SDG-NCD-FINAL-25-10-17.pdfIn fact, a non-communicable disease may also be defined as a non-transmissible disease. This disease can be chronic or acute. In this research the focus is on chronic non-comunicable diseases that causes relatively the highest mortality rates.It is not clearly defined when a disease is chronic or not. For this reason, all diseases that are specifically annotated as 'Acute' are left out.The diseases have the following codes:- Cardiovascular disease: ICD-10 codes `I05-I99`- Cancer: ICD-10 codes `C00-C97`- Diabetes mellitus: ICD-10 codes `E10-E13`- Chronic respiratory diseases: ICD-10 codes `J40-47`- Diseases of digestive system: ICD-10 codes `K00-K93`The codes are formatted with 3 characters or 4 characters. In this case, we only need the first 3 characters.<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 get_unique_codes(df, symbol, column_name='Cause'):
    return list(df[df[column_name].str.contains(symbol)][column_name].unique())

def test_codes(codes):
    """Some codes can have ascii symbols, but first test if they are integer only.
    That makes the code somewhat easier."""
    # remove the first symbol
    codes = [code[1:] for code in codes]
    
    try:
        [int(code) for code in codes]
        print('Codes are valid.')
    except ValueError as e:
        print('Expected integers: ', e)
    except:
        print('Something else went wrong.')
        
def convert_format(series, n):
    """Only keep the n first characters of the column"""
    return series.apply(lambda x: x[:3])

def find_codes(codes, series):
    mask = np.isin(codes, series)
    found = np.where(mask, codes, '')
    valid = [c for c in found if c != '']
    
    return valid

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')

causes_3 = convert_format(df['Cause'], 3)
causes_3 = causes_3.unique()<jupyter_output><empty_output><jupyter_text>**Cancer**<jupyter_code>data_C_codes = get_unique_codes(df, 'C')

test_codes(data_C_codes)
# Codes belonging to cancer
C_N = C_codes.size 

C_valid = find_codes(C_codes, causes_3)

print("Found in total {} codes of {} possible codes for cancer.".format(len(C_valid), C_N))<jupyter_output>Found in total 86 codes of 98 possible codes for cancer.<jupyter_text>**Cardiovascular disease**<jupyter_code>data_I_codes = get_unique_codes(df, 'I')

test_codes(data_I_codes)
I_N = I_codes.size

valid = find_codes(I_codes, causes_3)

print("Found in total {} codes of {} possible codes for cardiovascular disease.".format(len(valid), I_N))<jupyter_output>Found in total 61 codes of 95 possible codes for cardiovascular disease.<jupyter_text>**Diabetes mellitus: ICD-10 codes E10-E13**<jupyter_code>data_E_codes = get_unique_codes(df, 'E')

test_codes(data_E_codes)
E_N = E_codes.size

valid = find_codes(E_codes, causes_3)

print("Found in total {} codes of {} possible codes for diabetes mellitus.".format(len(valid), E_N))<jupyter_output>Found in total 4 codes of 4 possible codes for diabetes mellitus.<jupyter_text>**Chronic respiratory diseases: ICD-10 codes J40-47**<jupyter_code>data_J_codes = get_unique_codes(df, 'J')

test_codes(data_J_codes)
J_N = J_codes.size

valid = find_codes(J_codes, causes_3)

print("Found in total {} codes of {} possible codes for chronic respiratory diseases.".format(len(valid), J_N))<jupyter_output>Found in total 8 codes of 8 possible codes for chronic respiratory diseases.<jupyter_text>**Diseases of digestive system: ICD-10 codes K00-K93**<jupyter_code>data_K_codes = get_unique_codes(df, 'K')

test_codes(data_K_codes)
K_N = K_codes.size

valid = find_codes(K_codes, causes_3)

print("Found in total {} codes of {} possible codes for diseases of digestive system.".format(len(valid), K_N))<jupyter_output>Found in total 64 codes of 94 possible codes for diseases of digestive system.
{'fraction_non_alphanumeric': 0.12242341418972782, 'fraction_numerical': 0.037125612117070945, 'mean_word_length': 5.20820077765995, 'pattern_counts': {'":': 0, '<': 98, '<?xml version=': 0, '>': 98, 'https://': 2, 'lorem ipsum': 0, 'www.': 2, '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}}
{'chain_length': '26', 'dir': 'jupyter-structured-clean-dedup', 'id': '138408', 'license': 'Apache-2.0', 'n_tokens_mistral': 6112, 'n_tokens_neox': 5431, 'n_words': 1996, 'path': 'src/notebooks/Data understanding.ipynb', 'repo_name': 'ddlatumalea/disease_and_life', 'size': '17562'}