cod_cleanup.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Import Dependancies"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import json\n",
    "import gzip"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Extract and Read CSVs"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "def csv_to_df(csv_path):\n",
    "    df = pd.read_csv(csv_path, compression='gzip', dtype=object)\n",
    "    return df"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Decoding JSONs "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "#Decoding JSON\n",
    "def code_map(json_path):\n",
    "    with open(json_path, \"r\") as f_open:\n",
    "        code_map = json.load(f_open)\n",
    "    return code_map"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Cleaning up Data\n",
    "1. Find out Column info\n",
    "2. Drop irrelavent columns \n",
    "3. Replace the Blanks in the Columns \n",
    "4. Replace values with the codes on JSON\n",
    "5. Rename Columns to improve readability"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [],
   "source": [
    "def reduce_df(df):\n",
    "    #Drop Columns that are irrelavent to hypothesis\n",
    "    reduced_df = df.drop(columns=['113_cause_recode', '39_cause_recode','resident_status',\n",
    "                                          'education_1989_revision', 'age_recode_52', 'age_recode_27', 'detail_age',\n",
    "                                          'education_reporting_flag', 'age_substitution_flag', 'infant_age_recode_22',\n",
    "                                          'place_of_death_and_decedents_status', 'injury_at_work', 'manner_of_death',\n",
    "                                          'method_of_disposition', 'autopsy', 'activity_code', 'race',\n",
    "                                          'place_of_injury_for_causes_w00_y34_except_y06_and_y07_',\n",
    "                                          'bridged_race_flag', 'race_imputation_flag', '130_infant_cause_recode',\n",
    "                                          'race_recode_3', 'hispanic_origin',\n",
    "                                          'detail_age_type', 'number_of_entity_axis_conditions', 'entity_condition_1',\n",
    "                                          'entity_condition_2', 'entity_condition_3', 'entity_condition_4',\n",
    "                                          'entity_condition_5', 'entity_condition_6', 'entity_condition_7',\n",
    "                                          'entity_condition_8', 'entity_condition_9', 'entity_condition_10',\n",
    "                                          'entity_condition_11', 'entity_condition_12', 'entity_condition_13',\n",
    "                                          'entity_condition_14', 'entity_condition_15', 'entity_condition_16',\n",
    "                                          'entity_condition_17', 'entity_condition_18', 'entity_condition_19',\n",
    "                                          'entity_condition_20', 'number_of_record_axis_conditions', 'record_condition_1',\n",
    "                                          'record_condition_2', 'record_condition_3', 'record_condition_4',\n",
    "                                          'record_condition_5', 'record_condition_6', 'record_condition_7',\n",
    "                                          'record_condition_8', 'record_condition_9', 'record_condition_10',\n",
    "                                          'record_condition_11', 'record_condition_12', 'record_condition_13',\n",
    "                                          'record_condition_14', 'record_condition_15', 'record_condition_16',\n",
    "                                          'record_condition_17', 'record_condition_18', 'record_condition_19',\n",
    "                                          'record_condition_20'])\n",
    "    #Drop the NAN and unknown values\n",
    "    reduced_df = reduced_df.dropna(subset=['education_2003_revision'])\n",
    "    reduced_df = reduced_df.loc[reduced_df['education_2003_revision'] != 'Unknown']\n",
    "    return reduced_df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [],
   "source": [
    "#Using JSON decoder, replace values in the dataframes. \n",
    "#Go through ALL columns\n",
    "def recode_values(df, code_map):\n",
    "    recoded_df = df.copy()\n",
    "    recoded_df['education_2003_revision'] = recoded_df['education_2003_revision'].apply(\n",
    "        lambda x: code_map['education_2003_revision'][x])\n",
    "    recoded_df['month_of_death'] = recoded_df['month_of_death'].apply(\n",
    "        lambda x: code_map['month_of_death'][x])\n",
    "    recoded_df['age_recode_12'] = recoded_df['age_recode_12'].apply(\n",
    "    lambda x: code_map['age_recode_12'][x])\n",
    "    recoded_df['marital_status'] = recoded_df['marital_status'].apply(\n",
    "        lambda x: code_map['marital_status'][x])\n",
    "    recoded_df['race_recode_5'] = recoded_df['race_recode_5'].apply(\n",
    "        lambda x: code_map['race_recode_5'][x])\n",
    "    recoded_df['hispanic_originrace_recode'] = recoded_df['hispanic_originrace_recode'].apply(\n",
    "        lambda x: code_map['hispanic_originrace_recode'][x])\n",
    "    recoded_df['day_of_week_of_death'] = recoded_df['day_of_week_of_death'].apply(\n",
    "        lambda x: code_map['day_of_week_of_death'][x])\n",
    "    recoded_df['358_cause_recode'] = recoded_df['358_cause_recode'].apply(\n",
    "        lambda x: code_map['358_cause_recode'][x].split('(')[0].replace('=',\"'\"))\n",
    "    return recoded_df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [],
   "source": [
    "def icd_chapters(icd_code):\n",
    "    icd_letter = icd_code[0]\n",
    "    icd_num = int(icd_code[1:3])\n",
    "    if icd_letter == 'A' or icd_letter == 'B':\n",
    "        return 'Certain infectious and parasitic diseases'\n",
    "    elif icd_letter == 'C':\n",
    "        return 'Neoplasms'\n",
    "    elif icd_letter == 'D':\n",
    "        if icd_num <= 49:\n",
    "            return 'Neoplasms'\n",
    "        else:\n",
    "            return 'Diseases of the blood and blood-forming organs and certain disorders involving the immune mechanism'\n",
    "    elif icd_letter == 'E':\n",
    "        return 'Endocrine, nutritional and metabolic diseases'\n",
    "    elif icd_letter == 'F':\n",
    "        return 'Mental, behavioral and neurodevelopmental disorders'\n",
    "    elif icd_letter == 'G':\n",
    "        return 'Diseases of the nervous system'\n",
    "    elif icd_letter == 'H':\n",
    "        if icd_num <= 59:\n",
    "            return 'Diseases of the eye and adnexa'\n",
    "        else:\n",
    "            return 'Diseases of the ear and mastoid process'\n",
    "    elif icd_letter == 'I':\n",
    "        return 'Diseases of the circulatory system'\n",
    "    elif icd_letter == 'J':\n",
    "        return 'Diseases of the respiratory system'\n",
    "    elif icd_letter == 'K':\n",
    "        return 'Diseases of the digestive system'\n",
    "    elif icd_letter == 'L':\n",
    "        return 'Diseases of the skin and subcutaneous tissue'\n",
    "    elif icd_letter == 'M':\n",
    "        return 'Diseases of the musculoskeletal system and connective tissue'\n",
    "    elif icd_letter == 'N':\n",
    "        return 'Diseases of the genitourinary system'\n",
    "    elif icd_letter == 'O':\n",
    "        return 'Pregnancy, childbirth and the puerperium'\n",
    "    elif icd_letter == 'P':\n",
    "        return 'Certain conditions originating in the perinatal period'\n",
    "    elif icd_letter == 'Q':\n",
    "        return 'Congenital malformations, deformations and chromosomal abnormalities'\n",
    "    elif icd_letter == 'R':\n",
    "        return 'Symptoms, signs and abnormal clinical and laboratory findings, not elsewhere classified'\n",
    "    elif icd_letter == 'S' or icd_letter == 'T':\n",
    "        return 'Injury, poisoning and certain other consequences of external causes'\n",
    "    elif icd_letter == 'U':\n",
    "        return 'Codes for special purposes'\n",
    "    elif icd_letter == 'V' or icd_letter == 'W' or icd_letter == 'X' or icd_letter == 'Y':\n",
    "        return 'External causes of morbidity and mortality'\n",
    "    elif icd_letter == 'Z':\n",
    "        return 'Factors influencing health status and contact with health services'\n",
    "    else:\n",
    "        return float(\"NaN\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [],
   "source": [
    "#icd_code_10th_revision column changes to icd_code_10 in 2012\n",
    "def replace_icd_chapters(df):\n",
    "    icd_code_chapters = []\n",
    "    icd_replaced_df = df.copy()\n",
    "    for index, row in icd_replaced_df.iterrows():\n",
    "        icd_code = row['icd_code_10th_revision']\n",
    "        icd_code_chapters.append(icd_chapters(icd_code))\n",
    "    icd_replaced_df['Cause of Death Category'] = icd_code_chapters\n",
    "    return icd_replaced_df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [],
   "source": [
    "#Rename Columns for additional readability\n",
    "def rename_columns(df):\n",
    "    renamed_df = df.copy()\n",
    "    renamed_df = renamed_df.rename(columns={\"education_2003_revision\": \"Education Level\", \n",
    "                                                      \"month_of_death\": \"Month of Death\", \n",
    "                                                      \"sex\": \"Sex/Gender\",  \n",
    "                                                      \"age_recode_12\": \"Age Groups\", \n",
    "                                                      \"infant_age_recode_22\": \"Infant Age Groups\", \n",
    "                                                      \"marital_status\": \"Marital Status\", \n",
    "                                                      \"current_data_year\": \"Year\", \n",
    "                                                      \"icd_code_10th_revision\": \"ICD Code\",\n",
    "                                                      \"icd_code_10\": \"ICD Code\",\n",
    "                                                      \"358_cause_recode\": \"Cause of Death\",\n",
    "                                                      \"race_recode_5\": \"Race\",\n",
    "                                                      \"hispanic_originrace_recode\": \"Hispanic Origin\",\n",
    "                                                      \"day_of_week_of_death\": \"Day of Week\"})\n",
    "    return renamed_df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Education Level</th>\n",
       "      <th>Month of Death</th>\n",
       "      <th>Sex/Gender</th>\n",
       "      <th>Age Groups</th>\n",
       "      <th>Marital Status</th>\n",
       "      <th>Day of Week</th>\n",
       "      <th>Year</th>\n",
       "      <th>ICD Code</th>\n",
       "      <th>Cause of Death</th>\n",
       "      <th>Race</th>\n",
       "      <th>Hispanic Origin</th>\n",
       "      <th>Cause of Death Category</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>8th grade or less</td>\n",
       "      <td>June</td>\n",
       "      <td>M</td>\n",
       "      <td>85 years and over</td>\n",
       "      <td>Married</td>\n",
       "      <td>Saturday</td>\n",
       "      <td>2005</td>\n",
       "      <td>I251</td>\n",
       "      <td>All other forms of chronic ischemic heart dise...</td>\n",
       "      <td>White</td>\n",
       "      <td>Mexican</td>\n",
       "      <td>Diseases of the circulatory system</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>9 - 12th grade, no diploma</td>\n",
       "      <td>January</td>\n",
       "      <td>F</td>\n",
       "      <td>45 - 54 years</td>\n",
       "      <td>Married</td>\n",
       "      <td>Saturday</td>\n",
       "      <td>2005</td>\n",
       "      <td>J449</td>\n",
       "      <td>Other chronic obstructive pulmonary disease</td>\n",
       "      <td>White</td>\n",
       "      <td>Non - Hispanic white</td>\n",
       "      <td>Diseases of the respiratory system</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>high school graduate or GED completed</td>\n",
       "      <td>January</td>\n",
       "      <td>F</td>\n",
       "      <td>65 - 74 years</td>\n",
       "      <td>Widowed</td>\n",
       "      <td>Sunday</td>\n",
       "      <td>2005</td>\n",
       "      <td>C349</td>\n",
       "      <td>Of trachea, bronchus and lung</td>\n",
       "      <td>White</td>\n",
       "      <td>Non - Hispanic white</td>\n",
       "      <td>Neoplasms</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>high school graduate or GED completed</td>\n",
       "      <td>January</td>\n",
       "      <td>M</td>\n",
       "      <td>55 - 64 years</td>\n",
       "      <td>Married</td>\n",
       "      <td>Monday</td>\n",
       "      <td>2005</td>\n",
       "      <td>X72</td>\n",
       "      <td>Intentional self-harm</td>\n",
       "      <td>White</td>\n",
       "      <td>Non - Hispanic white</td>\n",
       "      <td>External causes of morbidity and mortality</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>high school graduate or GED completed</td>\n",
       "      <td>January</td>\n",
       "      <td>M</td>\n",
       "      <td>75 - 84 years</td>\n",
       "      <td>Married</td>\n",
       "      <td>Sunday</td>\n",
       "      <td>2005</td>\n",
       "      <td>I64</td>\n",
       "      <td>Stroke, not specified as hemorrhage or infarct...</td>\n",
       "      <td>White</td>\n",
       "      <td>Non - Hispanic white</td>\n",
       "      <td>Diseases of the circulatory system</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19525729</th>\n",
       "      <td>8th grade or less</td>\n",
       "      <td>May</td>\n",
       "      <td>M</td>\n",
       "      <td>Under 1 year (includes not stated infant ages)</td>\n",
       "      <td>Never married, single</td>\n",
       "      <td>Saturday</td>\n",
       "      <td>2015</td>\n",
       "      <td>P072</td>\n",
       "      <td>Disorders related to short gestation and low b...</td>\n",
       "      <td>Black</td>\n",
       "      <td>Hispanic origin unknown</td>\n",
       "      <td>Certain conditions originating in the perinata...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19525730</th>\n",
       "      <td>Unknown</td>\n",
       "      <td>December</td>\n",
       "      <td>F</td>\n",
       "      <td>45 - 54 years</td>\n",
       "      <td>Marital Status unknown</td>\n",
       "      <td>Friday</td>\n",
       "      <td>2015</td>\n",
       "      <td>I251</td>\n",
       "      <td>All other forms of chronic ischemic heart dise...</td>\n",
       "      <td>White</td>\n",
       "      <td>Non - Hispanic white</td>\n",
       "      <td>Diseases of the circulatory system</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19525731</th>\n",
       "      <td>Unknown</td>\n",
       "      <td>December</td>\n",
       "      <td>M</td>\n",
       "      <td>25 - 34 years</td>\n",
       "      <td>Marital Status unknown</td>\n",
       "      <td>Sunday</td>\n",
       "      <td>2015</td>\n",
       "      <td>F102</td>\n",
       "      <td>Mental and behavioral disorders due to use of ...</td>\n",
       "      <td>White</td>\n",
       "      <td>Other or unknown Hispanic</td>\n",
       "      <td>Mental, behavioral and neurodevelopmental diso...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19525732</th>\n",
       "      <td>Unknown</td>\n",
       "      <td>July</td>\n",
       "      <td>M</td>\n",
       "      <td>75 - 84 years</td>\n",
       "      <td>Marital Status unknown</td>\n",
       "      <td>Saturday</td>\n",
       "      <td>2015</td>\n",
       "      <td>I250</td>\n",
       "      <td>Atherosclerotic cardiovascular disease, so des...</td>\n",
       "      <td>Black</td>\n",
       "      <td>Non - Hispanic black</td>\n",
       "      <td>Diseases of the circulatory system</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19525733</th>\n",
       "      <td>Unknown</td>\n",
       "      <td>December</td>\n",
       "      <td>M</td>\n",
       "      <td>75 - 84 years</td>\n",
       "      <td>Marital Status unknown</td>\n",
       "      <td>Monday</td>\n",
       "      <td>2015</td>\n",
       "      <td>I119</td>\n",
       "      <td>Hypertensive heart disease</td>\n",
       "      <td>Black</td>\n",
       "      <td>Hispanic origin unknown</td>\n",
       "      <td>Diseases of the circulatory system</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>19525734 rows × 12 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                                Education Level Month of Death Sex/Gender  \\\n",
       "0                             8th grade or less           June          M   \n",
       "1                    9 - 12th grade, no diploma        January          F   \n",
       "2         high school graduate or GED completed        January          F   \n",
       "3         high school graduate or GED completed        January          M   \n",
       "4         high school graduate or GED completed        January          M   \n",
       "...                                         ...            ...        ...   \n",
       "19525729                      8th grade or less            May          M   \n",
       "19525730                                Unknown       December          F   \n",
       "19525731                                Unknown       December          M   \n",
       "19525732                                Unknown           July          M   \n",
       "19525733                                Unknown       December          M   \n",
       "\n",
       "                                              Age Groups  \\\n",
       "0                                      85 years and over   \n",
       "1                                          45 - 54 years   \n",
       "2                                          65 - 74 years   \n",
       "3                                          55 - 64 years   \n",
       "4                                          75 - 84 years   \n",
       "...                                                  ...   \n",
       "19525729  Under 1 year (includes not stated infant ages)   \n",
       "19525730                                   45 - 54 years   \n",
       "19525731                                   25 - 34 years   \n",
       "19525732                                   75 - 84 years   \n",
       "19525733                                   75 - 84 years   \n",
       "\n",
       "                  Marital Status Day of Week  Year ICD Code  \\\n",
       "0                        Married    Saturday  2005     I251   \n",
       "1                        Married    Saturday  2005     J449   \n",
       "2                        Widowed      Sunday  2005     C349   \n",
       "3                        Married      Monday  2005      X72   \n",
       "4                        Married      Sunday  2005      I64   \n",
       "...                          ...         ...   ...      ...   \n",
       "19525729   Never married, single    Saturday  2015     P072   \n",
       "19525730  Marital Status unknown      Friday  2015     I251   \n",
       "19525731  Marital Status unknown      Sunday  2015     F102   \n",
       "19525732  Marital Status unknown    Saturday  2015     I250   \n",
       "19525733  Marital Status unknown      Monday  2015     I119   \n",
       "\n",
       "                                             Cause of Death   Race  \\\n",
       "0         All other forms of chronic ischemic heart dise...  White   \n",
       "1              Other chronic obstructive pulmonary disease   White   \n",
       "2                            Of trachea, bronchus and lung   White   \n",
       "3                                    Intentional self-harm   White   \n",
       "4         Stroke, not specified as hemorrhage or infarct...  White   \n",
       "...                                                     ...    ...   \n",
       "19525729  Disorders related to short gestation and low b...  Black   \n",
       "19525730  All other forms of chronic ischemic heart dise...  White   \n",
       "19525731  Mental and behavioral disorders due to use of ...  White   \n",
       "19525732  Atherosclerotic cardiovascular disease, so des...  Black   \n",
       "19525733                        Hypertensive heart disease   Black   \n",
       "\n",
       "                    Hispanic Origin  \\\n",
       "0                           Mexican   \n",
       "1              Non - Hispanic white   \n",
       "2              Non - Hispanic white   \n",
       "3              Non - Hispanic white   \n",
       "4              Non - Hispanic white   \n",
       "...                             ...   \n",
       "19525729    Hispanic origin unknown   \n",
       "19525730       Non - Hispanic white   \n",
       "19525731  Other or unknown Hispanic   \n",
       "19525732       Non - Hispanic black   \n",
       "19525733    Hispanic origin unknown   \n",
       "\n",
       "                                    Cause of Death Category  \n",
       "0                        Diseases of the circulatory system  \n",
       "1                        Diseases of the respiratory system  \n",
       "2                                                 Neoplasms  \n",
       "3                External causes of morbidity and mortality  \n",
       "4                        Diseases of the circulatory system  \n",
       "...                                                     ...  \n",
       "19525729  Certain conditions originating in the perinata...  \n",
       "19525730                 Diseases of the circulatory system  \n",
       "19525731  Mental, behavioral and neurodevelopmental diso...  \n",
       "19525732                 Diseases of the circulatory system  \n",
       "19525733                 Diseases of the circulatory system  \n",
       "\n",
       "[19525734 rows x 12 columns]"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "cod_df = pd.DataFrame()\n",
    "years = [str(2000+x) for x in range(5,16)]\n",
    "for year in years:\n",
    "    csv_path = f\"Raw_CSVs/kaggle_data/{year}_data.csv.gz\"\n",
    "    json_path = f\"Raw_CSVs/kaggle_data/{year}_codes.json\"\n",
    "    \n",
    "    df = csv_to_df(csv_path)\n",
    "    column_map = code_map(json_path)\n",
    "    \n",
    "    if year == '2012':\n",
    "        df = df.rename(columns={\"icd_code_10\": \"icd_code_10th_revision\"})\n",
    "    \n",
    "    df = reduce_df(df)\n",
    "    df = recode_values(df, column_map)\n",
    "    df = replace_icd_chapters(df)\n",
    "    df = rename_columns(df)\n",
    "    \n",
    "    cod_df = pd.concat([cod_df, df], ignore_index=True)\n",
    "cod_df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Education Level</th>\n",
       "      <th>Month of Death</th>\n",
       "      <th>Sex/Gender</th>\n",
       "      <th>Age Groups</th>\n",
       "      <th>Marital Status</th>\n",
       "      <th>Day of Week</th>\n",
       "      <th>Year</th>\n",
       "      <th>ICD Code</th>\n",
       "      <th>Cause of Death</th>\n",
       "      <th>Race</th>\n",
       "      <th>Hispanic Origin</th>\n",
       "      <th>Cause of Death Category</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>8th grade or less</td>\n",
       "      <td>June</td>\n",
       "      <td>M</td>\n",
       "      <td>85 years and over</td>\n",
       "      <td>Married</td>\n",
       "      <td>Saturday</td>\n",
       "      <td>2005</td>\n",
       "      <td>I251</td>\n",
       "      <td>All other forms of chronic ischemic heart dise...</td>\n",
       "      <td>White</td>\n",
       "      <td>Mexican</td>\n",
       "      <td>Diseases of the circulatory system</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>9 - 12th grade, no diploma</td>\n",
       "      <td>January</td>\n",
       "      <td>F</td>\n",
       "      <td>45 - 54 years</td>\n",
       "      <td>Married</td>\n",
       "      <td>Saturday</td>\n",
       "      <td>2005</td>\n",
       "      <td>J449</td>\n",
       "      <td>Other chronic obstructive pulmonary disease</td>\n",
       "      <td>White</td>\n",
       "      <td>Non - Hispanic white</td>\n",
       "      <td>Diseases of the respiratory system</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>high school graduate or GED completed</td>\n",
       "      <td>January</td>\n",
       "      <td>F</td>\n",
       "      <td>65 - 74 years</td>\n",
       "      <td>Widowed</td>\n",
       "      <td>Sunday</td>\n",
       "      <td>2005</td>\n",
       "      <td>C349</td>\n",
       "      <td>Of trachea, bronchus and lung</td>\n",
       "      <td>White</td>\n",
       "      <td>Non - Hispanic white</td>\n",
       "      <td>Neoplasms</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>high school graduate or GED completed</td>\n",
       "      <td>January</td>\n",
       "      <td>M</td>\n",
       "      <td>55 - 64 years</td>\n",
       "      <td>Married</td>\n",
       "      <td>Monday</td>\n",
       "      <td>2005</td>\n",
       "      <td>X72</td>\n",
       "      <td>Intentional self-harm</td>\n",
       "      <td>White</td>\n",
       "      <td>Non - Hispanic white</td>\n",
       "      <td>External causes of morbidity and mortality</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>high school graduate or GED completed</td>\n",
       "      <td>January</td>\n",
       "      <td>M</td>\n",
       "      <td>75 - 84 years</td>\n",
       "      <td>Married</td>\n",
       "      <td>Sunday</td>\n",
       "      <td>2005</td>\n",
       "      <td>I64</td>\n",
       "      <td>Stroke, not specified as hemorrhage or infarct...</td>\n",
       "      <td>White</td>\n",
       "      <td>Non - Hispanic white</td>\n",
       "      <td>Diseases of the circulatory system</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                         Education Level Month of Death Sex/Gender  \\\n",
       "0                      8th grade or less           June          M   \n",
       "1             9 - 12th grade, no diploma        January          F   \n",
       "2  high school graduate or GED completed        January          F   \n",
       "3  high school graduate or GED completed        January          M   \n",
       "4  high school graduate or GED completed        January          M   \n",
       "\n",
       "          Age Groups Marital Status Day of Week  Year ICD Code  \\\n",
       "0  85 years and over        Married    Saturday  2005     I251   \n",
       "1      45 - 54 years        Married    Saturday  2005     J449   \n",
       "2      65 - 74 years        Widowed      Sunday  2005     C349   \n",
       "3      55 - 64 years        Married      Monday  2005      X72   \n",
       "4      75 - 84 years        Married      Sunday  2005      I64   \n",
       "\n",
       "                                      Cause of Death   Race  \\\n",
       "0  All other forms of chronic ischemic heart dise...  White   \n",
       "1       Other chronic obstructive pulmonary disease   White   \n",
       "2                     Of trachea, bronchus and lung   White   \n",
       "3                             Intentional self-harm   White   \n",
       "4  Stroke, not specified as hemorrhage or infarct...  White   \n",
       "\n",
       "        Hispanic Origin                     Cause of Death Category  \n",
       "0               Mexican          Diseases of the circulatory system  \n",
       "1  Non - Hispanic white          Diseases of the respiratory system  \n",
       "2  Non - Hispanic white                                   Neoplasms  \n",
       "3  Non - Hispanic white  External causes of morbidity and mortality  \n",
       "4  Non - Hispanic white          Diseases of the circulatory system  "
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "condition_1 = (cod_df['Cause of Death'] != 'Other ill-defined and unknown causes of mortality ')\n",
    "condition_2 = (cod_df['Cause of Death'] != 'Ill-defined and unknown causes of mortality ')\n",
    "#condition_3 = (cod_df['Education Level'] != 'Unknown') 660221\n",
    "#condition_4 = (cod_df['Age Groups'] != 'Age not stated') 1790\n",
    "#condition_5 = (cod_df['Marital Status'] != 'Marital Status unknown') 252702\n",
    "#condition_6 = (cod_df['Day of Week'] != 'Unknown') 1068\n",
    "#condition_7 = (cod_df['Hispanic Origin'] != 'Hispanic origin unknown') 252702\n",
    "cod_df = cod_df.loc[(condition_1 & condition_2)]\n",
    "cod_df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [],
   "source": [
    "cod_df.to_csv('data/cod_clean.csv.gz', index=False, header=True, compression='gzip')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import gzip"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "csv_path = \"data/cod_clean.csv.gz\"\n",
    "cod_df = pd.read_csv(csv_path, compression='gzip', dtype=object)\n",
    "cod_df.to_csv('data/cod_clean.csv', index=False, header=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.7.6"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}