1_data_preparation.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "8ed726a8-a0dd-4ae9-b657-94f39e319221",
   "metadata": {},
   "source": [
    "# Capstone Project with Udacity\n",
    "\n",
    "# Create a Customer Segmentation Report for Arvato Financial Services\n",
    "\n",
    "In this project, I will analyze demographics data for customers of a mail-order sales company in Germany, comparing it against demographics information for the general population of Germany. I'll use unsupervised learning techniques to perform customer segmentation, identifying the parts of the population that best describe the core customer base of the company. Then, I'll apply what I've learned from the segmentation on a third dataset with demographics information for targets of a marketing campaign for the company, and use a model to **predict which individuals are most likely to convert into becoming customers for the company**. The data that I'll be using was provided by Bertelsmann Arvato Analytics, and represents a real-life data science task."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "ebbe54ac-3fd4-4e48-a895-f5e9ae2ae24b",
   "metadata": {},
   "outputs": [],
   "source": [
    "# setting random state for reproducibility\n",
    "random_state = 22\n",
    "\n",
    "# import libraries\n",
    "import numpy as np\n",
    "import pandas as pd\n",
    "import matplotlib.pyplot as plt\n",
    "import seaborn as sns\n",
    "\n",
    "from sklearn.impute import SimpleImputer"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "cb29337a-5c73-4d3a-8321-fc97899ed058",
   "metadata": {},
   "source": [
    "## Part 1: Get to Know the Data\n",
    "\n",
    "**In this first notebook, I'll be performing the cleaning and pre-processing of the data.**\n",
    "\n",
    "There are four data files associated with this project:\n",
    "\n",
    "- `sample_AZDIAS.csv`: Demographics data for the general population of Germany; 222 805 persons (rows) x 366 features (columns).\n",
    "- `sample_CUSTOMERS.csv`: Demographics data for customers of a mail-order company; 47 913 persons (rows) x 369 features (columns).\n",
    "- `Udacity_MAILOUT_052018_TRAIN.csv`: Demographics data for individuals who were targets of a marketing campaign; 42 982 persons (rows) x 367 (columns).\n",
    "- `Udacity_MAILOUT_052018_TEST.csv`: Demographics data for individuals who were targets of a marketing campaign; 42 833 persons (rows) x 366 (columns).\n",
    "\n",
    "Each row of the demographics files represents a single person, but also includes information outside of individuals, including information about their household, building, and neighborhood. I'll be using the information from the first two files to figure out how customers (`CUSTOMERS`) are similar to or differ from the general population at large (`AZDIAS`), then use the analysis to make predictions on the other two files (`MAILOUT`), predicting which recipients are most likely to become a customer for the mail-order company.\n",
    "\n",
    "The `CUSTOMERS` file contains three extra columns (`CUSTOMER_GROUP`, `ONLINE_PURCHASE`, and `PRODUCT_GROUP`), which provide broad information about the customers depicted in the file. The original `MAILOUT` file included one additional column, `RESPONSE`, which indicated whether or not each recipient became a customer of the company. For the `TRAIN` subset, this column has been retained, but in the `TEST` subset it has been removed; it is against that withheld column that the final predictions will be assessed in the Kaggle competition.\n",
    "\n",
    "Otherwise, all of the remaining columns are the same between the three data files. For more information about the columns depicted in the files, there are two Excel spreadsheets provided in the files that can be refered to:\n",
    "\n",
    "- `DIAS Information Levels - Attributes 2017.xlsx`:  is a top-level list of attributes and descriptions, organized by informational category.\n",
    "- `DIAS Attributes - Values 2017.xlsx`:  is a detailed mapping of data values for each feature in alphabetical order."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "91203d81-e412-47ef-9e45-479079858edd",
   "metadata": {},
   "source": [
    "### Load and check rows and columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "0048c95f-5d20-4ffb-858b-4a621d06fcbb",
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "C:\\Users\\Qesh\\anaconda3\\lib\\site-packages\\IPython\\core\\interactiveshell.py:3437: DtypeWarning: Columns (19,20) have mixed types.Specify dtype option on import or set low_memory=False.\n",
      "  exec(code_obj, self.user_global_ns, self.user_ns)\n"
     ]
    }
   ],
   "source": [
    "# load in the data\n",
    "azdias = pd.read_csv('data/sample_AZDIAS.csv')\n",
    "customers = pd.read_csv('data/sample_CUSTOMERS.csv')\n",
    "\n",
    "del azdias['Unnamed: 0']\n",
    "del customers['Unnamed: 0']"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "36d312ec-a023-44d6-9353-597313633efd",
   "metadata": {},
   "source": [
    "Looks like there's something off about columns 18 and 19. We'll check that out later on."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "6db27ee5-379b-4cad-8a2e-94a20c31651c",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "AZDIAS:      (222805, 366)\n",
      "CUSTOMERS:   (47913, 369)\n"
     ]
    }
   ],
   "source": [
    "# check the datasets sizes\n",
    "print('AZDIAS:     ',azdias.shape)\n",
    "print('CUSTOMERS:  ',customers.shape)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "463ac816-b018-4422-bcb0-75556a826c59",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "18538"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "azdias.ALTER_HH.isnull().sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "c54b3438-98a5-4918-a88e-54ee6109872a",
   "metadata": {},
   "outputs": [],
   "source": [
    "azdias.ALTER_HH.replace(['None', 'nan'], np.nan, inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "64807f39-4917-4da9-944b-0bbcf755223b",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0          NaN\n",
       "1          NaN\n",
       "2          NaN\n",
       "3          NaN\n",
       "4          NaN\n",
       "          ... \n",
       "222800     NaN\n",
       "222801     NaN\n",
       "222802    18.0\n",
       "222803     NaN\n",
       "222804     NaN\n",
       "Name: ALTER_KIND1, Length: 222805, dtype: float64"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "azdias.ALTER_KIND1"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "9bb90602-8413-425a-8216-19441609082e",
   "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>LNR</th>\n",
       "      <th>AGER_TYP</th>\n",
       "      <th>AKT_DAT_KL</th>\n",
       "      <th>ALTER_HH</th>\n",
       "      <th>ALTER_KIND1</th>\n",
       "      <th>ALTER_KIND2</th>\n",
       "      <th>ALTER_KIND3</th>\n",
       "      <th>ALTER_KIND4</th>\n",
       "      <th>ALTERSKATEGORIE_FEIN</th>\n",
       "      <th>ANZ_HAUSHALTE_AKTIV</th>\n",
       "      <th>...</th>\n",
       "      <th>VHN</th>\n",
       "      <th>VK_DHT4A</th>\n",
       "      <th>VK_DISTANZ</th>\n",
       "      <th>VK_ZG11</th>\n",
       "      <th>W_KEIT_KIND_HH</th>\n",
       "      <th>WOHNDAUER_2008</th>\n",
       "      <th>WOHNLAGE</th>\n",
       "      <th>ZABEOTYP</th>\n",
       "      <th>ANREDE_KZ</th>\n",
       "      <th>ALTERSKATEGORIE_GROB</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>406859</td>\n",
       "      <td>2</td>\n",
       "      <td>1.0</td>\n",
       "      <td>19.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>13.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>9.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>3</td>\n",
       "      <td>2</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>548618</td>\n",
       "      <td>-1</td>\n",
       "      <td>5.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1.0</td>\n",
       "      <td>...</td>\n",
       "      <td>2.0</td>\n",
       "      <td>6.0</td>\n",
       "      <td>7.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>6.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>7.0</td>\n",
       "      <td>3</td>\n",
       "      <td>1</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>546543</td>\n",
       "      <td>-1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>3</td>\n",
       "      <td>2</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>554058</td>\n",
       "      <td>-1</td>\n",
       "      <td>1.0</td>\n",
       "      <td>21.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>21.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>...</td>\n",
       "      <td>1.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>7.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>9.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>426052</td>\n",
       "      <td>-1</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>25.0</td>\n",
       "      <td>12.0</td>\n",
       "      <td>...</td>\n",
       "      <td>3.0</td>\n",
       "      <td>6.0</td>\n",
       "      <td>9.0</td>\n",
       "      <td>10.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>5.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>5</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 366 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "      LNR  AGER_TYP  AKT_DAT_KL  ALTER_HH  ALTER_KIND1  ALTER_KIND2  \\\n",
       "0  406859         2         1.0      19.0          NaN          NaN   \n",
       "1  548618        -1         5.0       0.0          NaN          NaN   \n",
       "2  546543        -1         NaN       NaN          NaN          NaN   \n",
       "3  554058        -1         1.0      21.0          NaN          NaN   \n",
       "4  426052        -1         1.0       0.0          NaN          NaN   \n",
       "\n",
       "   ALTER_KIND3  ALTER_KIND4  ALTERSKATEGORIE_FEIN  ANZ_HAUSHALTE_AKTIV  ...  \\\n",
       "0          NaN          NaN                  13.0                  NaN  ...   \n",
       "1          NaN          NaN                   NaN                  1.0  ...   \n",
       "2          NaN          NaN                   NaN                  NaN  ...   \n",
       "3          NaN          NaN                  21.0                  2.0  ...   \n",
       "4          NaN          NaN                  25.0                 12.0  ...   \n",
       "\n",
       "   VHN  VK_DHT4A  VK_DISTANZ  VK_ZG11  W_KEIT_KIND_HH  WOHNDAUER_2008  \\\n",
       "0  NaN       2.0         3.0      3.0             2.0             9.0   \n",
       "1  2.0       6.0         7.0      4.0             6.0             4.0   \n",
       "2  NaN       NaN         NaN      NaN             NaN             NaN   \n",
       "3  1.0       4.0         7.0      4.0             3.0             9.0   \n",
       "4  3.0       6.0         9.0     10.0             NaN             5.0   \n",
       "\n",
       "   WOHNLAGE ZABEOTYP ANREDE_KZ ALTERSKATEGORIE_GROB  \n",
       "0       NaN        3         2                    3  \n",
       "1       7.0        3         1                    4  \n",
       "2       NaN        3         2                    3  \n",
       "3       3.0        1         1                    1  \n",
       "4       1.0        5         2                    1  \n",
       "\n",
       "[5 rows x 366 columns]"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "azdias.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "b0c08b33-c30f-4433-bdbe-37abc4d5c1bc",
   "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>LNR</th>\n",
       "      <th>AGER_TYP</th>\n",
       "      <th>AKT_DAT_KL</th>\n",
       "      <th>ALTER_HH</th>\n",
       "      <th>ALTER_KIND1</th>\n",
       "      <th>ALTER_KIND2</th>\n",
       "      <th>ALTER_KIND3</th>\n",
       "      <th>ALTER_KIND4</th>\n",
       "      <th>ALTERSKATEGORIE_FEIN</th>\n",
       "      <th>ANZ_HAUSHALTE_AKTIV</th>\n",
       "      <th>...</th>\n",
       "      <th>VK_ZG11</th>\n",
       "      <th>W_KEIT_KIND_HH</th>\n",
       "      <th>WOHNDAUER_2008</th>\n",
       "      <th>WOHNLAGE</th>\n",
       "      <th>ZABEOTYP</th>\n",
       "      <th>PRODUCT_GROUP</th>\n",
       "      <th>CUSTOMER_GROUP</th>\n",
       "      <th>ONLINE_PURCHASE</th>\n",
       "      <th>ANREDE_KZ</th>\n",
       "      <th>ALTERSKATEGORIE_GROB</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>13824</td>\n",
       "      <td>0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>8.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>8.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>...</td>\n",
       "      <td>1.0</td>\n",
       "      <td>6.0</td>\n",
       "      <td>9.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>3</td>\n",
       "      <td>COSMETIC</td>\n",
       "      <td>SINGLE_BUYER</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>97051</td>\n",
       "      <td>-1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>3</td>\n",
       "      <td>COSMETIC_AND_FOOD</td>\n",
       "      <td>MULTI_BUYER</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>88334</td>\n",
       "      <td>1</td>\n",
       "      <td>1.0</td>\n",
       "      <td>7.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>6.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>...</td>\n",
       "      <td>2.0</td>\n",
       "      <td>6.0</td>\n",
       "      <td>9.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>4</td>\n",
       "      <td>FOOD</td>\n",
       "      <td>SINGLE_BUYER</td>\n",
       "      <td>0</td>\n",
       "      <td>2</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>10491</td>\n",
       "      <td>-1</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.0</td>\n",
       "      <td>7.0</td>\n",
       "      <td>...</td>\n",
       "      <td>8.0</td>\n",
       "      <td>6.0</td>\n",
       "      <td>9.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>3</td>\n",
       "      <td>COSMETIC_AND_FOOD</td>\n",
       "      <td>MULTI_BUYER</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>167860</td>\n",
       "      <td>-1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>3</td>\n",
       "      <td>COSMETIC_AND_FOOD</td>\n",
       "      <td>MULTI_BUYER</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 369 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "      LNR  AGER_TYP  AKT_DAT_KL  ALTER_HH  ALTER_KIND1  ALTER_KIND2  \\\n",
       "0   13824         0         1.0       8.0          NaN          NaN   \n",
       "1   97051        -1         NaN       NaN          NaN          NaN   \n",
       "2   88334         1         1.0       7.0          NaN          NaN   \n",
       "3   10491        -1         1.0       0.0          NaN          NaN   \n",
       "4  167860        -1         NaN       NaN          NaN          NaN   \n",
       "\n",
       "   ALTER_KIND3  ALTER_KIND4  ALTERSKATEGORIE_FEIN  ANZ_HAUSHALTE_AKTIV  ...  \\\n",
       "0          NaN          NaN                   8.0                  3.0  ...   \n",
       "1          NaN          NaN                   NaN                  NaN  ...   \n",
       "2          NaN          NaN                   6.0                  1.0  ...   \n",
       "3          NaN          NaN                   0.0                  7.0  ...   \n",
       "4          NaN          NaN                   NaN                  NaN  ...   \n",
       "\n",
       "   VK_ZG11  W_KEIT_KIND_HH  WOHNDAUER_2008  WOHNLAGE  ZABEOTYP  \\\n",
       "0      1.0             6.0             9.0       2.0         3   \n",
       "1      NaN             NaN             NaN       NaN         3   \n",
       "2      2.0             6.0             9.0       2.0         4   \n",
       "3      8.0             6.0             9.0       3.0         3   \n",
       "4      NaN             NaN             NaN       NaN         3   \n",
       "\n",
       "       PRODUCT_GROUP  CUSTOMER_GROUP ONLINE_PURCHASE ANREDE_KZ  \\\n",
       "0           COSMETIC    SINGLE_BUYER               0         1   \n",
       "1  COSMETIC_AND_FOOD     MULTI_BUYER               0         1   \n",
       "2               FOOD    SINGLE_BUYER               0         2   \n",
       "3  COSMETIC_AND_FOOD     MULTI_BUYER               0         1   \n",
       "4  COSMETIC_AND_FOOD     MULTI_BUYER               0         1   \n",
       "\n",
       "  ALTERSKATEGORIE_GROB  \n",
       "0                    4  \n",
       "1                    2  \n",
       "2                    4  \n",
       "3                    4  \n",
       "4                    2  \n",
       "\n",
       "[5 rows x 369 columns]"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "customers.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "id": "d59691e8-9201-41c2-87c9-75a0796d51c7",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "AZDIAS:\n",
      " 222805 = 222805\n",
      " True\n",
      "\n",
      "CUSTOMERS:\n",
      " 47913 = 47913\n",
      " True\n"
     ]
    }
   ],
   "source": [
    "# double check on number of unique rows representing unique people\n",
    "print(f'AZDIAS:\\n {azdias.shape[0]} = {azdias.LNR.nunique()}\\n {azdias.shape[0] == azdias.LNR.nunique()}')\n",
    "print(f'\\nCUSTOMERS:\\n {customers.shape[0]} = {customers.LNR.nunique()}\\n {customers.shape[0] == customers.LNR.nunique()}')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "id": "16cf8c52-053e-4d91-a5bf-2acf9176b968",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "AZDIAS columns:     366\n",
      "CUSTOMERS columns:  369\n",
      "3 extra columns in CUSTOMERS are: ['PRODUCT_GROUP' 'CUSTOMER_GROUP' 'ONLINE_PURCHASE']\n"
     ]
    }
   ],
   "source": [
    "# check the columns\n",
    "print(f'AZDIAS columns:     {azdias.shape[1]}')\n",
    "print(f'CUSTOMERS columns:  {customers.shape[1]}')\n",
    "\n",
    "azdias_columns = list(azdias.columns)\n",
    "customers_columns = list(customers.columns)\n",
    "extra_customers_columns = np.setdiff1d(customers_columns, azdias_columns, assume_unique=True)\n",
    "\n",
    "print(f'{customers.shape[1] - azdias.shape[1]} extra columns in CUSTOMERS are: {extra_customers_columns}')"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "07887776-d2a1-4e0e-aeab-c3b3b8f902f0",
   "metadata": {},
   "source": [
    "Now that we've verified that the columns and rows are ok, let's start to check each column to really understand our data."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "586ec8c2-6a4f-4c2d-b43b-97fb7cd3d041",
   "metadata": {},
   "source": [
    "Let's start by diving a bit on the extra columns of customers dataset:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "id": "25ee97f1-08ae-460d-ba65-7630e1200bd2",
   "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>PRODUCT_GROUP</th>\n",
       "      <th>CUSTOMER_GROUP</th>\n",
       "      <th>ONLINE_PURCHASE</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>COSMETIC</td>\n",
       "      <td>SINGLE_BUYER</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>COSMETIC_AND_FOOD</td>\n",
       "      <td>MULTI_BUYER</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>FOOD</td>\n",
       "      <td>SINGLE_BUYER</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>COSMETIC_AND_FOOD</td>\n",
       "      <td>MULTI_BUYER</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>COSMETIC_AND_FOOD</td>\n",
       "      <td>MULTI_BUYER</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "       PRODUCT_GROUP CUSTOMER_GROUP  ONLINE_PURCHASE\n",
       "0           COSMETIC   SINGLE_BUYER                0\n",
       "1  COSMETIC_AND_FOOD    MULTI_BUYER                0\n",
       "2               FOOD   SINGLE_BUYER                0\n",
       "3  COSMETIC_AND_FOOD    MULTI_BUYER                0\n",
       "4  COSMETIC_AND_FOOD    MULTI_BUYER                0"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "customers[extra_customers_columns].head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "id": "b096ae21-42a2-4a9a-a1a2-570adf1d34ee",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 47913 entries, 0 to 47912\n",
      "Data columns (total 3 columns):\n",
      " #   Column           Non-Null Count  Dtype \n",
      "---  ------           --------------  ----- \n",
      " 0   PRODUCT_GROUP    47913 non-null  object\n",
      " 1   CUSTOMER_GROUP   47913 non-null  object\n",
      " 2   ONLINE_PURCHASE  47913 non-null  int64 \n",
      "dtypes: int64(1), object(2)\n",
      "memory usage: 1.1+ MB\n"
     ]
    }
   ],
   "source": [
    "# check the summary for these extra columns\n",
    "customers[extra_customers_columns].info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "id": "28f44cce-affa-49aa-8e44-8ac589ff77ed",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array(['COSMETIC', 'COSMETIC_AND_FOOD', 'FOOD'], dtype=object)"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "customers[extra_customers_columns].PRODUCT_GROUP.unique()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "id": "c3430a86-79ba-43f3-a4f3-2e331679fd11",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array(['SINGLE_BUYER', 'MULTI_BUYER'], dtype=object)"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "customers[extra_customers_columns].CUSTOMER_GROUP.unique()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "id": "5b7ca008-0ea8-4860-8814-08c4dffc9c13",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array([0, 1], dtype=int64)"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "customers[extra_customers_columns].ONLINE_PURCHASE.unique()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "510b8fce-c5a9-4787-ab1a-fbd90b61c037",
   "metadata": {},
   "source": [
    "As we can see, there's no missing values in these three columns and all of them are categorical columns."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "e14e8a85-8cbe-4254-b331-f51b5ae9bad6",
   "metadata": {},
   "source": [
    "We'll import the excel sheets that contain information about the datasets attributes so we can make better judgments regarding the columns."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "id": "3b13ae47-c9af-4576-96b2-4919013c9728",
   "metadata": {},
   "outputs": [],
   "source": [
    "data_att = pd.read_excel('data/data_description/DIAS Attributes - Values 2017.xlsx', header=1)\n",
    "data_info = pd.read_excel('data/data_description/DIAS Information Levels - Attributes 2017.xlsx', header=1)\n",
    "\n",
    "del data_att['Unnamed: 0']\n",
    "del data_info['Unnamed: 0']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "id": "6e03131c-6d61-4f51-be32-fcccab83109c",
   "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>Attribute</th>\n",
       "      <th>Description</th>\n",
       "      <th>Value</th>\n",
       "      <th>Meaning</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>AGER_TYP</td>\n",
       "      <td>best-ager typology</td>\n",
       "      <td>-1</td>\n",
       "      <td>unknown</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "      <td>no classification possible</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1</td>\n",
       "      <td>passive elderly</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2</td>\n",
       "      <td>cultural elderly</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>3</td>\n",
       "      <td>experience-driven elderly</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>ALTERSKATEGORIE_GROB</td>\n",
       "      <td>age classification through prename analysis</td>\n",
       "      <td>-1, 0</td>\n",
       "      <td>unknown</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1</td>\n",
       "      <td>&lt; 30 years</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2</td>\n",
       "      <td>30 - 45 years</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "              Attribute                                   Description  Value  \\\n",
       "0              AGER_TYP                            best-ager typology     -1   \n",
       "1                   NaN                                           NaN      0   \n",
       "2                   NaN                                           NaN      1   \n",
       "3                   NaN                                           NaN      2   \n",
       "4                   NaN                                           NaN      3   \n",
       "5  ALTERSKATEGORIE_GROB  age classification through prename analysis   -1, 0   \n",
       "6                   NaN                                           NaN      1   \n",
       "7                   NaN                                           NaN      2   \n",
       "\n",
       "                      Meaning  \n",
       "0                     unknown  \n",
       "1  no classification possible  \n",
       "2             passive elderly  \n",
       "3            cultural elderly  \n",
       "4   experience-driven elderly  \n",
       "5                     unknown  \n",
       "6                  < 30 years  \n",
       "7               30 - 45 years  "
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data_att.head(8)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "id": "8839ac31-f306-43c3-ae20-b96d0d8723cd",
   "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>Information level</th>\n",
       "      <th>Attribute</th>\n",
       "      <th>Description</th>\n",
       "      <th>Additional notes</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>NaN</td>\n",
       "      <td>AGER_TYP</td>\n",
       "      <td>best-ager typology</td>\n",
       "      <td>in cooperation with Kantar TNS; the informatio...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Person</td>\n",
       "      <td>ALTERSKATEGORIE_GROB</td>\n",
       "      <td>age through prename analysis</td>\n",
       "      <td>modelled on millions of first name-age-referen...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>NaN</td>\n",
       "      <td>ANREDE_KZ</td>\n",
       "      <td>gender</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>NaN</td>\n",
       "      <td>CJT_GESAMTTYP</td>\n",
       "      <td>Customer-Journey-Typology relating to the pref...</td>\n",
       "      <td>relating to the preferred information, marketi...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>NaN</td>\n",
       "      <td>FINANZ_MINIMALIST</td>\n",
       "      <td>financial typology: low financial interest</td>\n",
       "      <td>Gfk-Typology based on a representative househo...</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  Information level             Attribute  \\\n",
       "0               NaN              AGER_TYP   \n",
       "1            Person  ALTERSKATEGORIE_GROB   \n",
       "2               NaN             ANREDE_KZ   \n",
       "3               NaN         CJT_GESAMTTYP   \n",
       "4               NaN     FINANZ_MINIMALIST   \n",
       "\n",
       "                                         Description  \\\n",
       "0                                 best-ager typology   \n",
       "1                      age through prename analysis    \n",
       "2                                             gender   \n",
       "3  Customer-Journey-Typology relating to the pref...   \n",
       "4         financial typology: low financial interest   \n",
       "\n",
       "                                    Additional notes  \n",
       "0  in cooperation with Kantar TNS; the informatio...  \n",
       "1  modelled on millions of first name-age-referen...  \n",
       "2                                                NaN  \n",
       "3  relating to the preferred information, marketi...  \n",
       "4  Gfk-Typology based on a representative househo...  "
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data_info.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "671f0e6e-baf5-4ab6-b375-7f981ca91ed1",
   "metadata": {},
   "source": [
    "To be able to use these datasets we'll be filling the NaNs with the forward value."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "id": "9b79e306-cf0b-4bf2-aeea-d08e1ffc0dbb",
   "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>Attribute</th>\n",
       "      <th>Description</th>\n",
       "      <th>Value</th>\n",
       "      <th>Meaning</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>AGER_TYP</td>\n",
       "      <td>best-ager typology</td>\n",
       "      <td>-1</td>\n",
       "      <td>unknown</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>AGER_TYP</td>\n",
       "      <td>best-ager typology</td>\n",
       "      <td>0</td>\n",
       "      <td>no classification possible</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>AGER_TYP</td>\n",
       "      <td>best-ager typology</td>\n",
       "      <td>1</td>\n",
       "      <td>passive elderly</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>AGER_TYP</td>\n",
       "      <td>best-ager typology</td>\n",
       "      <td>2</td>\n",
       "      <td>cultural elderly</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>AGER_TYP</td>\n",
       "      <td>best-ager typology</td>\n",
       "      <td>3</td>\n",
       "      <td>experience-driven elderly</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>ALTERSKATEGORIE_GROB</td>\n",
       "      <td>age classification through prename analysis</td>\n",
       "      <td>-1, 0</td>\n",
       "      <td>unknown</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>ALTERSKATEGORIE_GROB</td>\n",
       "      <td>age classification through prename analysis</td>\n",
       "      <td>1</td>\n",
       "      <td>&lt; 30 years</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>ALTERSKATEGORIE_GROB</td>\n",
       "      <td>age classification through prename analysis</td>\n",
       "      <td>2</td>\n",
       "      <td>30 - 45 years</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "              Attribute                                   Description  Value  \\\n",
       "0              AGER_TYP                            best-ager typology     -1   \n",
       "1              AGER_TYP                            best-ager typology      0   \n",
       "2              AGER_TYP                            best-ager typology      1   \n",
       "3              AGER_TYP                            best-ager typology      2   \n",
       "4              AGER_TYP                            best-ager typology      3   \n",
       "5  ALTERSKATEGORIE_GROB  age classification through prename analysis   -1, 0   \n",
       "6  ALTERSKATEGORIE_GROB  age classification through prename analysis       1   \n",
       "7  ALTERSKATEGORIE_GROB  age classification through prename analysis       2   \n",
       "\n",
       "                      Meaning  \n",
       "0                     unknown  \n",
       "1  no classification possible  \n",
       "2             passive elderly  \n",
       "3            cultural elderly  \n",
       "4   experience-driven elderly  \n",
       "5                     unknown  \n",
       "6                  < 30 years  \n",
       "7               30 - 45 years  "
      ]
     },
     "execution_count": 19,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data_att.fillna(method=\"ffill\", inplace=True)\n",
    "data_att.head(8)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "id": "c511ff5b-b740-4aa7-9635-7799afef2eb9",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Attribute      0\n",
       "Description    0\n",
       "Value          0\n",
       "Meaning        0\n",
       "dtype: int64"
      ]
     },
     "execution_count": 20,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data_att.isna().sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "id": "3208a78e-2770-429a-b9c8-a61a8bd80d98",
   "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>Information level</th>\n",
       "      <th>Attribute</th>\n",
       "      <th>Description</th>\n",
       "      <th>Additional notes</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>NaN</td>\n",
       "      <td>AGER_TYP</td>\n",
       "      <td>best-ager typology</td>\n",
       "      <td>in cooperation with Kantar TNS; the informatio...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Person</td>\n",
       "      <td>ALTERSKATEGORIE_GROB</td>\n",
       "      <td>age through prename analysis</td>\n",
       "      <td>modelled on millions of first name-age-referen...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Person</td>\n",
       "      <td>ANREDE_KZ</td>\n",
       "      <td>gender</td>\n",
       "      <td>modelled on millions of first name-age-referen...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Person</td>\n",
       "      <td>CJT_GESAMTTYP</td>\n",
       "      <td>Customer-Journey-Typology relating to the pref...</td>\n",
       "      <td>relating to the preferred information, marketi...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Person</td>\n",
       "      <td>FINANZ_MINIMALIST</td>\n",
       "      <td>financial typology: low financial interest</td>\n",
       "      <td>Gfk-Typology based on a representative househo...</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  Information level             Attribute  \\\n",
       "0               NaN              AGER_TYP   \n",
       "1            Person  ALTERSKATEGORIE_GROB   \n",
       "2            Person             ANREDE_KZ   \n",
       "3            Person         CJT_GESAMTTYP   \n",
       "4            Person     FINANZ_MINIMALIST   \n",
       "\n",
       "                                         Description  \\\n",
       "0                                 best-ager typology   \n",
       "1                      age through prename analysis    \n",
       "2                                             gender   \n",
       "3  Customer-Journey-Typology relating to the pref...   \n",
       "4         financial typology: low financial interest   \n",
       "\n",
       "                                    Additional notes  \n",
       "0  in cooperation with Kantar TNS; the informatio...  \n",
       "1  modelled on millions of first name-age-referen...  \n",
       "2  modelled on millions of first name-age-referen...  \n",
       "3  relating to the preferred information, marketi...  \n",
       "4  Gfk-Typology based on a representative househo...  "
      ]
     },
     "execution_count": 21,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data_info.fillna(method=\"ffill\", inplace=True)\n",
    "data_info.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "id": "57388218-3b6e-4fb6-bbf7-df5d7c06cce0",
   "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>Information level</th>\n",
       "      <th>Attribute</th>\n",
       "      <th>Description</th>\n",
       "      <th>Additional notes</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>NaN</td>\n",
       "      <td>AGER_TYP</td>\n",
       "      <td>best-ager typology</td>\n",
       "      <td>in cooperation with Kantar TNS; the informatio...</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  Information level Attribute         Description  \\\n",
       "0               NaN  AGER_TYP  best-ager typology   \n",
       "\n",
       "                                    Additional notes  \n",
       "0  in cooperation with Kantar TNS; the informatio...  "
      ]
     },
     "execution_count": 22,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data_info[data_info['Information level'].isna()]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "id": "e69ad795-0496-4a5e-94e7-2559fa2a8491",
   "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>Information level</th>\n",
       "      <th>Attribute</th>\n",
       "      <th>Description</th>\n",
       "      <th>Additional notes</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Person</td>\n",
       "      <td>AGER_TYP</td>\n",
       "      <td>best-ager typology</td>\n",
       "      <td>in cooperation with Kantar TNS; the informatio...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Person</td>\n",
       "      <td>ALTERSKATEGORIE_GROB</td>\n",
       "      <td>age through prename analysis</td>\n",
       "      <td>modelled on millions of first name-age-referen...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Person</td>\n",
       "      <td>ANREDE_KZ</td>\n",
       "      <td>gender</td>\n",
       "      <td>modelled on millions of first name-age-referen...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Person</td>\n",
       "      <td>CJT_GESAMTTYP</td>\n",
       "      <td>Customer-Journey-Typology relating to the pref...</td>\n",
       "      <td>relating to the preferred information, marketi...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Person</td>\n",
       "      <td>FINANZ_MINIMALIST</td>\n",
       "      <td>financial typology: low financial interest</td>\n",
       "      <td>Gfk-Typology based on a representative househo...</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  Information level             Attribute  \\\n",
       "0            Person              AGER_TYP   \n",
       "1            Person  ALTERSKATEGORIE_GROB   \n",
       "2            Person             ANREDE_KZ   \n",
       "3            Person         CJT_GESAMTTYP   \n",
       "4            Person     FINANZ_MINIMALIST   \n",
       "\n",
       "                                         Description  \\\n",
       "0                                 best-ager typology   \n",
       "1                      age through prename analysis    \n",
       "2                                             gender   \n",
       "3  Customer-Journey-Typology relating to the pref...   \n",
       "4         financial typology: low financial interest   \n",
       "\n",
       "                                    Additional notes  \n",
       "0  in cooperation with Kantar TNS; the informatio...  \n",
       "1  modelled on millions of first name-age-referen...  \n",
       "2  modelled on millions of first name-age-referen...  \n",
       "3  relating to the preferred information, marketi...  \n",
       "4  Gfk-Typology based on a representative househo...  "
      ]
     },
     "execution_count": 23,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data_info.fillna(method=\"bfill\", inplace=True)\n",
    "data_info.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "id": "5d2f993b-4c8f-48a9-81bb-3c514dd956cb",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Information level    0\n",
       "Attribute            0\n",
       "Description          0\n",
       "Additional notes     0\n",
       "dtype: int64"
      ]
     },
     "execution_count": 24,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data_info.isna().sum()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "7738fc8f-27a9-4963-bf8a-b75e614461ed",
   "metadata": {},
   "source": [
    "### Data preparation\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "2281e973-0116-4656-90e2-0613451b7040",
   "metadata": {},
   "source": [
    "The first thing we're going to do is to drop the extra columns from the `customers` dataset so every data wrangling we do, will be done to both datasets (`azdias` - the full population dataset, and `customers` - dataset containing only the already customers).\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "id": "83481236-c49e-45c7-9204-cdb9b4cd1178",
   "metadata": {},
   "outputs": [],
   "source": [
    "customers.drop(columns= extra_customers_columns, inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "id": "0d3574fa-5dac-488a-bed0-f32719205b0e",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(47913, 366)"
      ]
     },
     "execution_count": 26,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "customers.shape"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "4cec93bd-28a0-4f37-9a32-1833a094ae41",
   "metadata": {},
   "source": [
    "Let's take a look at the two columns that got a `DtypeWarning` when we loaded the data."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "id": "522b67e1-a581-4296-806d-83ac8958fe71",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Column CAMEO_DEUG_2015:\n",
      "[nan 3.0 4.0 8.0 9.0 2.0 5.0 7.0 1.0 6.0 '3.0' '2' '8.0' '1' '4.0' '3'\n",
      " '7.0' '9.0' '5' '8' '9' '7' '5.0' '1.0' '4' '6' '2.0' '6.0' 'X']\n",
      "\n",
      "Column CAMEO_INTL_2015:\n",
      "[nan 24.0 51.0 55.0 13.0 33.0 54.0 41.0 15.0 14.0 25.0 35.0 43.0 22.0 31.0\n",
      " 23.0 32.0 44.0 34.0 52.0 12.0 45.0 '25.0' '14' '51.0' '13' '24.0' '25'\n",
      " '54.0' '23.0' '24' '32' '54' '41.0' '22.0' '51' '31' '41' '12' '34.0'\n",
      " '13.0' '52' '55' '35' '14.0' '45' '43' '23' '55.0' '31.0' '44' '43.0'\n",
      " '33' '15.0' '15' '34' '52.0' '45.0' '22' 'XX' '32.0' '44.0' '33.0' '12.0'\n",
      " '35.0']\n",
      "\n"
     ]
    }
   ],
   "source": [
    "error_columns = list(azdias.iloc[:, 18:20].columns)\n",
    "\n",
    "for c in error_columns:\n",
    "    print(f'Column {c}:\\n{azdias[c].unique()}\\n')"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "93a8999a-11b3-46e1-b9b1-49df8b000358",
   "metadata": {},
   "source": [
    "As we can see, there are strange values (X and XX) for both these columns. Let's check the `data_att` to understand what kind of entries are expected for these columns."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "id": "aae4711c-e6b4-47d0-badc-bb59cd02cdb6",
   "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>Attribute</th>\n",
       "      <th>Description</th>\n",
       "      <th>Value</th>\n",
       "      <th>Meaning</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>51</th>\n",
       "      <td>CAMEO_DEUG_2015</td>\n",
       "      <td>CAMEO classification 2015 - Uppergroup</td>\n",
       "      <td>-1</td>\n",
       "      <td>unknown</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>52</th>\n",
       "      <td>CAMEO_DEUG_2015</td>\n",
       "      <td>CAMEO classification 2015 - Uppergroup</td>\n",
       "      <td>1</td>\n",
       "      <td>upper class</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>53</th>\n",
       "      <td>CAMEO_DEUG_2015</td>\n",
       "      <td>CAMEO classification 2015 - Uppergroup</td>\n",
       "      <td>2</td>\n",
       "      <td>upper middleclass</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>54</th>\n",
       "      <td>CAMEO_DEUG_2015</td>\n",
       "      <td>CAMEO classification 2015 - Uppergroup</td>\n",
       "      <td>3</td>\n",
       "      <td>established middleclasse</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>55</th>\n",
       "      <td>CAMEO_DEUG_2015</td>\n",
       "      <td>CAMEO classification 2015 - Uppergroup</td>\n",
       "      <td>4</td>\n",
       "      <td>consumption-oriented middleclass</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>56</th>\n",
       "      <td>CAMEO_DEUG_2015</td>\n",
       "      <td>CAMEO classification 2015 - Uppergroup</td>\n",
       "      <td>5</td>\n",
       "      <td>active middleclass</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>57</th>\n",
       "      <td>CAMEO_DEUG_2015</td>\n",
       "      <td>CAMEO classification 2015 - Uppergroup</td>\n",
       "      <td>6</td>\n",
       "      <td>low-consumption middleclass</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>58</th>\n",
       "      <td>CAMEO_DEUG_2015</td>\n",
       "      <td>CAMEO classification 2015 - Uppergroup</td>\n",
       "      <td>7</td>\n",
       "      <td>lower middleclass</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>59</th>\n",
       "      <td>CAMEO_DEUG_2015</td>\n",
       "      <td>CAMEO classification 2015 - Uppergroup</td>\n",
       "      <td>8</td>\n",
       "      <td>working class</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>60</th>\n",
       "      <td>CAMEO_DEUG_2015</td>\n",
       "      <td>CAMEO classification 2015 - Uppergroup</td>\n",
       "      <td>9</td>\n",
       "      <td>urban working class</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "          Attribute                             Description Value  \\\n",
       "51  CAMEO_DEUG_2015  CAMEO classification 2015 - Uppergroup    -1   \n",
       "52  CAMEO_DEUG_2015  CAMEO classification 2015 - Uppergroup     1   \n",
       "53  CAMEO_DEUG_2015  CAMEO classification 2015 - Uppergroup     2   \n",
       "54  CAMEO_DEUG_2015  CAMEO classification 2015 - Uppergroup     3   \n",
       "55  CAMEO_DEUG_2015  CAMEO classification 2015 - Uppergroup     4   \n",
       "56  CAMEO_DEUG_2015  CAMEO classification 2015 - Uppergroup     5   \n",
       "57  CAMEO_DEUG_2015  CAMEO classification 2015 - Uppergroup     6   \n",
       "58  CAMEO_DEUG_2015  CAMEO classification 2015 - Uppergroup     7   \n",
       "59  CAMEO_DEUG_2015  CAMEO classification 2015 - Uppergroup     8   \n",
       "60  CAMEO_DEUG_2015  CAMEO classification 2015 - Uppergroup     9   \n",
       "\n",
       "                              Meaning  \n",
       "51                            unknown  \n",
       "52                        upper class  \n",
       "53                  upper middleclass  \n",
       "54           established middleclasse  \n",
       "55  consumption-oriented middleclass   \n",
       "56                active middleclass   \n",
       "57        low-consumption middleclass  \n",
       "58                  lower middleclass  \n",
       "59                      working class  \n",
       "60                urban working class  "
      ]
     },
     "execution_count": 28,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data_att[data_att.Attribute == 'CAMEO_DEUG_2015']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "id": "be49c671-0452-4540-85b5-b5dc5d08b114",
   "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>Attribute</th>\n",
       "      <th>Description</th>\n",
       "      <th>Value</th>\n",
       "      <th>Meaning</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "Empty DataFrame\n",
       "Columns: [Attribute, Description, Value, Meaning]\n",
       "Index: []"
      ]
     },
     "execution_count": 29,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data_att[data_att.Attribute == 'CAMEO_INTL_2015']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "id": "05db3214-07c5-46dd-9746-44f070bc669c",
   "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>Information level</th>\n",
       "      <th>Attribute</th>\n",
       "      <th>Description</th>\n",
       "      <th>Additional notes</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "Empty DataFrame\n",
       "Columns: [Information level, Attribute, Description, Additional notes]\n",
       "Index: []"
      ]
     },
     "execution_count": 30,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data_info[data_info.Attribute == 'CAMEO_INTL_2015']"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "be826822-08cb-4f06-9686-b64e2a7d4ed2",
   "metadata": {},
   "source": [
    "Something is wrong with this second column. It looks like we can't find it in neither of the descriptions datasets.\n",
    "\n",
    "Let's see if we can figure it out."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "id": "2de4de87-d84c-46bf-96a3-eab73b60b04f",
   "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>Information level</th>\n",
       "      <th>Attribute</th>\n",
       "      <th>Description</th>\n",
       "      <th>Additional notes</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>85</th>\n",
       "      <td>Microcell (RR4_ID)</td>\n",
       "      <td>CAMEO_DEUG_2015</td>\n",
       "      <td>CAMEO_4.0: uppergroup</td>\n",
       "      <td>New German CAMEO Typology established together...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>86</th>\n",
       "      <td>Microcell (RR4_ID)</td>\n",
       "      <td>CAMEO_DEU_2015</td>\n",
       "      <td>CAMEO_4.0: specific group</td>\n",
       "      <td>New German CAMEO Typology established together...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>87</th>\n",
       "      <td>Microcell (RR4_ID)</td>\n",
       "      <td>CAMEO_DEUINTL_2015</td>\n",
       "      <td>CAMEO_4.0: international classification</td>\n",
       "      <td>New German CAMEO Typology established together...</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     Information level           Attribute  \\\n",
       "85  Microcell (RR4_ID)     CAMEO_DEUG_2015   \n",
       "86  Microcell (RR4_ID)      CAMEO_DEU_2015   \n",
       "87  Microcell (RR4_ID)  CAMEO_DEUINTL_2015   \n",
       "\n",
       "                                Description  \\\n",
       "85                    CAMEO_4.0: uppergroup   \n",
       "86                CAMEO_4.0: specific group   \n",
       "87  CAMEO_4.0: international classification   \n",
       "\n",
       "                                     Additional notes  \n",
       "85  New German CAMEO Typology established together...  \n",
       "86  New German CAMEO Typology established together...  \n",
       "87  New German CAMEO Typology established together...  "
      ]
     },
     "execution_count": 31,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data_info[data_info.Attribute.str.startswith('CAMEO_')]"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "cd9d2f4e-882f-451b-b176-c57064b14066",
   "metadata": {},
   "source": [
    "It looks like `CAMEO_INTL_2015` is named `CAMEO_DEUINTL_2015` on the `data_att` and `data_info` datasets. Let's try again."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "id": "6f84c46c-cd7d-45e6-939f-4c5e0788d523",
   "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>Attribute</th>\n",
       "      <th>Description</th>\n",
       "      <th>Value</th>\n",
       "      <th>Meaning</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>105</th>\n",
       "      <td>CAMEO_DEUINTL_2015</td>\n",
       "      <td>CAMEO classification 2015 - international typo...</td>\n",
       "      <td>-1</td>\n",
       "      <td>unknown</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>106</th>\n",
       "      <td>CAMEO_DEUINTL_2015</td>\n",
       "      <td>(each German CAMEO code belongs to one interna...</td>\n",
       "      <td>11</td>\n",
       "      <td>Wealthy Households-Pre-Family Couples &amp; Singles</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>107</th>\n",
       "      <td>CAMEO_DEUINTL_2015</td>\n",
       "      <td>(each German CAMEO code belongs to one interna...</td>\n",
       "      <td>12</td>\n",
       "      <td>Wealthy Households-Young Couples With Children</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>108</th>\n",
       "      <td>CAMEO_DEUINTL_2015</td>\n",
       "      <td>(each German CAMEO code belongs to one interna...</td>\n",
       "      <td>13</td>\n",
       "      <td>Wealthy Households-Families With School Age Ch...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>109</th>\n",
       "      <td>CAMEO_DEUINTL_2015</td>\n",
       "      <td>(each German CAMEO code belongs to one interna...</td>\n",
       "      <td>14</td>\n",
       "      <td>Wealthy Households-Older Families &amp;  Mature Co...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>110</th>\n",
       "      <td>CAMEO_DEUINTL_2015</td>\n",
       "      <td>(each German CAMEO code belongs to one interna...</td>\n",
       "      <td>15</td>\n",
       "      <td>Wealthy Households-Elders In Retirement</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>111</th>\n",
       "      <td>CAMEO_DEUINTL_2015</td>\n",
       "      <td>(each German CAMEO code belongs to one interna...</td>\n",
       "      <td>21</td>\n",
       "      <td>Prosperous Households-Pre-Family Couples &amp; Sin...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>112</th>\n",
       "      <td>CAMEO_DEUINTL_2015</td>\n",
       "      <td>(each German CAMEO code belongs to one interna...</td>\n",
       "      <td>22</td>\n",
       "      <td>Prosperous Households-Young Couples With Children</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>113</th>\n",
       "      <td>CAMEO_DEUINTL_2015</td>\n",
       "      <td>(each German CAMEO code belongs to one interna...</td>\n",
       "      <td>23</td>\n",
       "      <td>Prosperous Households-Families With School Age...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>114</th>\n",
       "      <td>CAMEO_DEUINTL_2015</td>\n",
       "      <td>(each German CAMEO code belongs to one interna...</td>\n",
       "      <td>24</td>\n",
       "      <td>Prosperous Households-Older Families &amp; Mature ...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>115</th>\n",
       "      <td>CAMEO_DEUINTL_2015</td>\n",
       "      <td>(each German CAMEO code belongs to one interna...</td>\n",
       "      <td>25</td>\n",
       "      <td>Prosperous Households-Elders In Retirement</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>116</th>\n",
       "      <td>CAMEO_DEUINTL_2015</td>\n",
       "      <td>(each German CAMEO code belongs to one interna...</td>\n",
       "      <td>31</td>\n",
       "      <td>Comfortable Households-Pre-Family Couples &amp; Si...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>117</th>\n",
       "      <td>CAMEO_DEUINTL_2015</td>\n",
       "      <td>(each German CAMEO code belongs to one interna...</td>\n",
       "      <td>32</td>\n",
       "      <td>Comfortable Households-Young Couples With Chil...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>118</th>\n",
       "      <td>CAMEO_DEUINTL_2015</td>\n",
       "      <td>(each German CAMEO code belongs to one interna...</td>\n",
       "      <td>33</td>\n",
       "      <td>Comfortable Households-Families With School Ag...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>119</th>\n",
       "      <td>CAMEO_DEUINTL_2015</td>\n",
       "      <td>(each German CAMEO code belongs to one interna...</td>\n",
       "      <td>34</td>\n",
       "      <td>Comfortable Households-Older Families &amp; Mature...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>120</th>\n",
       "      <td>CAMEO_DEUINTL_2015</td>\n",
       "      <td>(each German CAMEO code belongs to one interna...</td>\n",
       "      <td>35</td>\n",
       "      <td>Comfortable Households-Elders In Retirement</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>121</th>\n",
       "      <td>CAMEO_DEUINTL_2015</td>\n",
       "      <td>(each German CAMEO code belongs to one interna...</td>\n",
       "      <td>41</td>\n",
       "      <td>Less Affluent Households-Pre-Family Couples &amp; ...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>122</th>\n",
       "      <td>CAMEO_DEUINTL_2015</td>\n",
       "      <td>(each German CAMEO code belongs to one interna...</td>\n",
       "      <td>42</td>\n",
       "      <td>Less Affluent Households-Young Couples With Ch...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>123</th>\n",
       "      <td>CAMEO_DEUINTL_2015</td>\n",
       "      <td>(each German CAMEO code belongs to one interna...</td>\n",
       "      <td>43</td>\n",
       "      <td>Less Affluent Households-Families With School ...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>124</th>\n",
       "      <td>CAMEO_DEUINTL_2015</td>\n",
       "      <td>(each German CAMEO code belongs to one interna...</td>\n",
       "      <td>44</td>\n",
       "      <td>Less Affluent Households-Older Families &amp; Matu...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>125</th>\n",
       "      <td>CAMEO_DEUINTL_2015</td>\n",
       "      <td>(each German CAMEO code belongs to one interna...</td>\n",
       "      <td>45</td>\n",
       "      <td>Less Affluent Households-Elders In Retirement</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>126</th>\n",
       "      <td>CAMEO_DEUINTL_2015</td>\n",
       "      <td>(each German CAMEO code belongs to one interna...</td>\n",
       "      <td>51</td>\n",
       "      <td>Poorer Households-Pre-Family Couples &amp; Singles</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>127</th>\n",
       "      <td>CAMEO_DEUINTL_2015</td>\n",
       "      <td>(each German CAMEO code belongs to one interna...</td>\n",
       "      <td>52</td>\n",
       "      <td>Poorer Households-Young Couples With Children</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>128</th>\n",
       "      <td>CAMEO_DEUINTL_2015</td>\n",
       "      <td>(each German CAMEO code belongs to one interna...</td>\n",
       "      <td>53</td>\n",
       "      <td>Poorer Households-Families With School Age Chi...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>129</th>\n",
       "      <td>CAMEO_DEUINTL_2015</td>\n",
       "      <td>(each German CAMEO code belongs to one interna...</td>\n",
       "      <td>54</td>\n",
       "      <td>Poorer Households-Older Families &amp; Mature Couples</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>130</th>\n",
       "      <td>CAMEO_DEUINTL_2015</td>\n",
       "      <td>(each German CAMEO code belongs to one interna...</td>\n",
       "      <td>55</td>\n",
       "      <td>Poorer Households-Elders In Retirement</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "              Attribute                                        Description  \\\n",
       "105  CAMEO_DEUINTL_2015  CAMEO classification 2015 - international typo...   \n",
       "106  CAMEO_DEUINTL_2015  (each German CAMEO code belongs to one interna...   \n",
       "107  CAMEO_DEUINTL_2015  (each German CAMEO code belongs to one interna...   \n",
       "108  CAMEO_DEUINTL_2015  (each German CAMEO code belongs to one interna...   \n",
       "109  CAMEO_DEUINTL_2015  (each German CAMEO code belongs to one interna...   \n",
       "110  CAMEO_DEUINTL_2015  (each German CAMEO code belongs to one interna...   \n",
       "111  CAMEO_DEUINTL_2015  (each German CAMEO code belongs to one interna...   \n",
       "112  CAMEO_DEUINTL_2015  (each German CAMEO code belongs to one interna...   \n",
       "113  CAMEO_DEUINTL_2015  (each German CAMEO code belongs to one interna...   \n",
       "114  CAMEO_DEUINTL_2015  (each German CAMEO code belongs to one interna...   \n",
       "115  CAMEO_DEUINTL_2015  (each German CAMEO code belongs to one interna...   \n",
       "116  CAMEO_DEUINTL_2015  (each German CAMEO code belongs to one interna...   \n",
       "117  CAMEO_DEUINTL_2015  (each German CAMEO code belongs to one interna...   \n",
       "118  CAMEO_DEUINTL_2015  (each German CAMEO code belongs to one interna...   \n",
       "119  CAMEO_DEUINTL_2015  (each German CAMEO code belongs to one interna...   \n",
       "120  CAMEO_DEUINTL_2015  (each German CAMEO code belongs to one interna...   \n",
       "121  CAMEO_DEUINTL_2015  (each German CAMEO code belongs to one interna...   \n",
       "122  CAMEO_DEUINTL_2015  (each German CAMEO code belongs to one interna...   \n",
       "123  CAMEO_DEUINTL_2015  (each German CAMEO code belongs to one interna...   \n",
       "124  CAMEO_DEUINTL_2015  (each German CAMEO code belongs to one interna...   \n",
       "125  CAMEO_DEUINTL_2015  (each German CAMEO code belongs to one interna...   \n",
       "126  CAMEO_DEUINTL_2015  (each German CAMEO code belongs to one interna...   \n",
       "127  CAMEO_DEUINTL_2015  (each German CAMEO code belongs to one interna...   \n",
       "128  CAMEO_DEUINTL_2015  (each German CAMEO code belongs to one interna...   \n",
       "129  CAMEO_DEUINTL_2015  (each German CAMEO code belongs to one interna...   \n",
       "130  CAMEO_DEUINTL_2015  (each German CAMEO code belongs to one interna...   \n",
       "\n",
       "    Value                                            Meaning  \n",
       "105    -1                                            unknown  \n",
       "106    11    Wealthy Households-Pre-Family Couples & Singles  \n",
       "107    12     Wealthy Households-Young Couples With Children  \n",
       "108    13  Wealthy Households-Families With School Age Ch...  \n",
       "109    14  Wealthy Households-Older Families &  Mature Co...  \n",
       "110    15            Wealthy Households-Elders In Retirement  \n",
       "111    21  Prosperous Households-Pre-Family Couples & Sin...  \n",
       "112    22  Prosperous Households-Young Couples With Children  \n",
       "113    23  Prosperous Households-Families With School Age...  \n",
       "114    24  Prosperous Households-Older Families & Mature ...  \n",
       "115    25         Prosperous Households-Elders In Retirement  \n",
       "116    31  Comfortable Households-Pre-Family Couples & Si...  \n",
       "117    32  Comfortable Households-Young Couples With Chil...  \n",
       "118    33  Comfortable Households-Families With School Ag...  \n",
       "119    34  Comfortable Households-Older Families & Mature...  \n",
       "120    35        Comfortable Households-Elders In Retirement  \n",
       "121    41  Less Affluent Households-Pre-Family Couples & ...  \n",
       "122    42  Less Affluent Households-Young Couples With Ch...  \n",
       "123    43  Less Affluent Households-Families With School ...  \n",
       "124    44  Less Affluent Households-Older Families & Matu...  \n",
       "125    45      Less Affluent Households-Elders In Retirement  \n",
       "126    51     Poorer Households-Pre-Family Couples & Singles  \n",
       "127    52      Poorer Households-Young Couples With Children  \n",
       "128    53  Poorer Households-Families With School Age Chi...  \n",
       "129    54  Poorer Households-Older Families & Mature Couples  \n",
       "130    55             Poorer Households-Elders In Retirement  "
      ]
     },
     "execution_count": 32,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data_att[data_att.Attribute == 'CAMEO_DEUINTL_2015']"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "008599b8-34af-4f19-98a3-3a3ae97a4fc2",
   "metadata": {},
   "source": [
    "Alright, that worked!\n",
    "\n",
    "So we can see that both of them are categorical columns and they should be integers.\n",
    "\n",
    "Let's then replace the `X` and `XX` values we found with `-1` that is the correct value for `unknown`."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "id": "3c240690-0051-4493-b83e-bf1926357f43",
   "metadata": {},
   "outputs": [],
   "source": [
    "def replace_wrong_values(df, columns_to_fix, wrong_values, correct_value):\n",
    "    \"\"\"\n",
    "    Method to fix wrong entry values of dataframes' columns.\n",
    "    \n",
    "    Args:\n",
    "        df: dataframe to have columns corrected\n",
    "        columns_to_fix (list): list of columns names that will be fixed\n",
    "        wrong_values (list): list of values to be replaced\n",
    "        correct_value: value to replace the wrong ones\n",
    "\n",
    "    Returns:\n",
    "        df: fixed dataframe \n",
    "    \"\"\"\n",
    "    for wv in wrong_values:\n",
    "        df[columns_to_fix] = df[columns_to_fix].replace(wv, correct_value)\n",
    "    \n",
    "    df[columns_to_fix] = df[columns_to_fix].astype(float)\n",
    "        \n",
    "    return df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "id": "4c3f1926-cec8-43ed-a6fb-772626c8ef5c",
   "metadata": {},
   "outputs": [],
   "source": [
    "azdias = replace_wrong_values(azdias, error_columns, ['X', 'XX'], -1)\n",
    "customers = replace_wrong_values(customers, error_columns, ['X', 'XX'], -1)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "749b6c9f-ad40-4f99-8706-8d2cbd6a4b84",
   "metadata": {},
   "source": [
    "Now it would be helpful if we took the columns with NaNs and replaced them with `-1` that is the correct value for `unknown`.\n",
    "\n",
    "For that, we'll search for columns that have NaNs and columns that have the `unknown` attribute meaning and get the intersection between them, wich means, get only the columns that satisfy both of these conditions and then go ahead and replace them."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "id": "a05ee21a-78a7-447b-81e3-718999a70384",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "232 columns have the unknown attribute meaning\n"
     ]
    }
   ],
   "source": [
    "has_unknown_att = list(data_att[data_att.Meaning == 'unknown'].Attribute)\n",
    "print(len(has_unknown_att), 'columns have the unknown attribute meaning')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "id": "c0387277-bf05-4785-ba59-09a5e805987b",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "273 columns have NaN values\n"
     ]
    }
   ],
   "source": [
    "na_columns = list(azdias.columns[(azdias.isna().sum()>0)])\n",
    "print(len(na_columns), 'columns have NaN values')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "id": "ca063743-78f7-406c-bf6e-d3b58f2bd0df",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "194 columns have the unknown attribute meaning AND NaN values\n",
      "These are the columns we can replace the NaNs with -1 (that mean unknown)\n"
     ]
    }
   ],
   "source": [
    "na_and_unknown = np.intersect1d(has_unknown_att, na_columns)\n",
    "print(len(na_and_unknown), 'columns have the unknown attribute meaning AND NaN values\\nThese are the columns we can replace the NaNs with -1 (that mean unknown)')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "id": "28dfb90d-81f8-4802-b8f5-4bb70d119aa7",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "79 columns have NaN values but don't have the unknown attribute meaning.\n",
      "We'll need to dig a little deeper to figure out what todo in this cases.\n"
     ]
    }
   ],
   "source": [
    "na_not_unknown = np.setdiff1d(na_columns, has_unknown_att)\n",
    "print(len(na_not_unknown), \"columns have NaN values but don't have the unknown attribute meaning.\\nWe'll need to dig a little deeper to figure out what todo in this cases.\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 39,
   "id": "0d7b2f74-3ad6-48ab-8d5b-c6f30ad7f1c9",
   "metadata": {},
   "outputs": [],
   "source": [
    "def replace_NaNs_with_unknown(df, data_att):\n",
    "    \"\"\"\n",
    "    Method to replace NaNs with -1 (meaning that the value is unknown).\n",
    "    \n",
    "    Args:\n",
    "        df: dataframe to have NaNs replaced\n",
    "        data_att: dataframe with attributes description\n",
    "\n",
    "    Returns:\n",
    "        df: replaced dataframe \n",
    "    \"\"\"\n",
    " \n",
    "    # make a list of columns for both situations\n",
    "    has_unknown_att = list(data_att[data_att.Meaning == 'unknown'].Attribute)\n",
    "    na_columns = list(df.columns[(df.isna().sum()>0)])\n",
    "    \n",
    "    # get the intersection list of columns\n",
    "    na_and_unknown = np.intersect1d(has_unknown_att, na_columns)\n",
    "    \n",
    "    # replace all NaNs with -1\n",
    "    df[na_and_unknown] = df[na_and_unknown].fillna(-1)\n",
    "        \n",
    "    return df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 40,
   "id": "d70510be-6a4d-49ab-841d-a3b842817d89",
   "metadata": {},
   "outputs": [],
   "source": [
    "azdias = replace_NaNs_with_unknown(azdias, data_att)\n",
    "customers = replace_NaNs_with_unknown(customers, data_att)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "8a6d4be3-4a4c-448d-bf44-215eba91d509",
   "metadata": {},
   "source": [
    "#### Missing data"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "b1611d1e-db10-4195-8ebc-3bbc77597d6a",
   "metadata": {},
   "source": [
    "Now that we have dealt with missing data on columns where `unknown` was an attribute option (in that case, we're considering that having an unknown value means something because it was given as an option to the categorical columns), we need to look deeper into columns with actual missing values."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 41,
   "id": "04cc645f-8ea1-4e9a-bbb2-8611d3b99500",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index(['ALTER_KIND1', 'ALTER_KIND2', 'ALTER_KIND3', 'ALTER_KIND4', 'EXTSEL992',\n",
       "       'KK_KUNDENTYP'],\n",
       "      dtype='object')"
      ]
     },
     "execution_count": 41,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "azdias.columns[(azdias.isnull().sum()/azdias.shape[0])>0.4]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 42,
   "id": "c8383d59-206b-40e7-97f3-3fad935a05d3",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index(['ALTER_KIND1', 'ALTER_KIND2', 'ALTER_KIND3', 'ALTER_KIND4', 'EXTSEL992',\n",
       "       'KK_KUNDENTYP'],\n",
       "      dtype='object')"
      ]
     },
     "execution_count": 42,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "customers.columns[(customers.isnull().sum()/customers.shape[0])>0.4]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 43,
   "id": "52b8f807-8a11-4dc3-beb2-7707d25eec3d",
   "metadata": {},
   "outputs": [],
   "source": [
    "# make lists of columns that are above the percentage threshold of 40%\n",
    "azdias_null_cols = list(azdias.columns[(azdias.isnull().sum()/azdias.shape[0])>0.4])\n",
    "customers_null_cols = list(customers.columns[(customers.isnull().sum()/customers.shape[0])>0.4])\n",
    "\n",
    "# get the intersection list of columns\n",
    "common_null_cols = list(np.intersect1d(azdias_null_cols, customers_null_cols))"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "835a2375-4768-40e3-ab3a-212c49fc717b",
   "metadata": {},
   "source": [
    "Now that we've selected columns with over 40% of missing values to drop, there's still a lot of columns we will need to inspect.\n",
    "\n",
    "For this task, let's start taking a look at what these columns mean so we can understand how to best deal with them."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 44,
   "id": "b87004fd-1e82-4590-8f4e-1d406517a775",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Number of cols with missing values: 79\n"
     ]
    }
   ],
   "source": [
    "print('Number of cols with missing values:', len(azdias.columns[(azdias.isnull().sum()/azdias.shape[0])>0]))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 45,
   "id": "19065c4f-0e62-48cd-9ddc-ddf35dee43eb",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Number of rows with over 20% of missing values: 0\n",
      "Number of rows with over 18% of missing values: 3\n",
      "Number of rows with over 15% of missing values: 18549\n"
     ]
    }
   ],
   "source": [
    "print('Number of rows with over 20% of missing values:', len(azdias.index[(azdias.isnull().sum(axis=1)/azdias.shape[1])>0.20]))\n",
    "print('Number of rows with over 18% of missing values:', len(azdias.index[(azdias.isnull().sum(axis=1)/azdias.shape[1])>0.18]))\n",
    "print('Number of rows with over 15% of missing values:', len(azdias.index[(azdias.isnull().sum(axis=1)/azdias.shape[1])>0.15]))"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "4e3b4d0a-6248-45c1-b01f-6d0042c8e383",
   "metadata": {},
   "source": [
    "Looking at missing values in rows, there's not a significant amount of rows with over 18% of missing values.\n",
    "\n",
    "When we lower that percentage a bit, we can see that the amount of rows start to to up. Rows that have over 15% of missing values are way more significant.\n",
    "\n",
    "In this situation it's better to handle missing values column wise instead of row wise, so we don't end up losing entry data. When we handle the columns missing values, it will fix the rows missing values as well."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 46,
   "id": "2eca50da-4c31-4e5e-b1c7-93ede05a572c",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Null values on azdias dataframe: 2911777\n",
      "Percentage of null values on azdias dataframe: 28.00579508664297\n"
     ]
    }
   ],
   "source": [
    "print('Null values on azdias dataframe:', azdias.isnull().sum().sum())\n",
    "print('Percentage of null values on azdias dataframe:', azdias.shape[0]*azdias.shape[1] / azdias.isnull().sum().sum())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 47,
   "id": "a45eee56-0d26-471d-81fa-08c5b095afa9",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "ALTER_KIND4                    0.998730\n",
       "ALTER_KIND3                    0.993353\n",
       "ALTER_KIND2                    0.967052\n",
       "ALTER_KIND1                    0.909104\n",
       "EXTSEL992                      0.734382\n",
       "KK_KUNDENTYP                   0.658096\n",
       "ALTERSKATEGORIE_FEIN           0.296165\n",
       "D19_SOZIALES                   0.289406\n",
       "D19_TELKO_ONLINE_QUOTE_12      0.289406\n",
       "D19_GESAMT_ONLINE_QUOTE_12     0.289406\n",
       "D19_KONSUMTYP                  0.289406\n",
       "D19_LETZTER_KAUF_BRANCHE       0.289406\n",
       "D19_LOTTO                      0.289406\n",
       "D19_BANKEN_ONLINE_QUOTE_12     0.289406\n",
       "D19_VERSAND_ONLINE_QUOTE_12    0.289406\n",
       "D19_VERSI_ONLINE_QUOTE_12      0.289406\n",
       "MOBI_REGIO                     0.150445\n",
       "VHN                            0.136824\n",
       "PLZ8_BAUMAX                    0.131204\n",
       "HH_DELTA_FLAG                  0.121146\n",
       "dtype: float64"
      ]
     },
     "execution_count": 47,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "((azdias.isnull().sum()/azdias.shape[0])).sort_values(ascending=False).head(20)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "95c4faea-e2e4-421b-a2a3-d8561321095e",
   "metadata": {},
   "source": [
    "After some digging into each column, we came up with the columns to make different inputations.\n",
    "\n",
    "Some of the columns we'll simply imput the value for `unknown` as we could see in the `data_att` dataframe.\n",
    "\n",
    "Other columns we could identify a pattern between it and a similar column, so I decided to imput 0 on them as that was the corresponding value for the meaning of the similar column."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 48,
   "id": "eef1f783-d9f3-40ca-9028-79bafc38afef",
   "metadata": {},
   "outputs": [],
   "source": [
    "# columns to make inputations\n",
    "cols_to_drop = common_null_cols\n",
    "cols_to_drop.append('D19_SOZIALES')\n",
    "\n",
    "cols_to_input_zero = ['ALTERSKATEGORIE_FEIN',\n",
    "                      'D19_LOTTO',\n",
    "                      'HH_DELTA_FLAG',\n",
    "                      'D19_TELKO_ONLINE_QUOTE_12',\n",
    "                      'D19_GESAMT_ONLINE_QUOTE_12',\n",
    "                      'D19_BANKEN_ONLINE_QUOTE_12',\n",
    "                      'D19_VERSAND_ONLINE_QUOTE_12',\n",
    "                      'D19_VERSI_ONLINE_QUOTE_12']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 49,
   "id": "c0b4dfc6-ca70-4cad-bc62-3a60f69cddec",
   "metadata": {},
   "outputs": [],
   "source": [
    "def handle_missing_data_cols(df, cols_to_drop, cols_to_input_zero):\n",
    "    \"\"\"\n",
    "    Method to handle columns that have missing data.\n",
    "    \n",
    "    Args:\n",
    "        df: dataframe to have missing data handled\n",
    "        cols_to_drop (list): list of columns to be dropped from dataframe\n",
    "        columns_to_input_zero (list): list of columns to input zero (unknown)\n",
    "\n",
    "    Returns:\n",
    "        df: dataframe with missing data handled\n",
    "    \"\"\"\n",
    "    \n",
    "    # drop columns from df\n",
    "    print(f'Dropping {len(cols_to_drop)} columns for having over 40% of null values')\n",
    "    df = df.drop(columns=cols_to_drop)\n",
    "    \n",
    "        \n",
    "    # input fixed value to columns\n",
    "    values = {\n",
    "        'D19_KONSUMTYP': 9,\n",
    "        'D19_LETZTER_KAUF_BRANCHE': 'D19_UNBEKANNT',\n",
    "        'MOBI_REGIO': 6\n",
    "    }\n",
    "    \n",
    "    # adding columns to input zero to the dict\n",
    "    for col in cols_to_input_zero:\n",
    "        values[col] = 0\n",
    "    \n",
    "    print(f'Imputting fixed values to {len(values.keys())} columns.\\n')\n",
    "    df.fillna(value=values, inplace=True)\n",
    "    \n",
    "    return df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 50,
   "id": "f452f3d5-0790-4730-a6fc-5b19ba5ecfd0",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Dropping 7 columns for having over 40% of null values\n",
      "Imputting fixed values to 11 columns.\n",
      "\n",
      "Dropping 7 columns for having over 40% of null values\n",
      "Imputting fixed values to 11 columns.\n",
      "\n"
     ]
    }
   ],
   "source": [
    "azdias = handle_missing_data_cols(azdias, cols_to_drop, cols_to_input_zero)\n",
    "customers = handle_missing_data_cols(customers, cols_to_drop, cols_to_input_zero)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "d3e3498a-0fa4-44e7-ad76-7635ce991a3d",
   "metadata": {},
   "source": [
    "Now we'll take the remaining columns with missing values and imput the most frequent value. Because they're categorical columns, it makes sense to input the most frequent and not the mean or the median, for exemple, as that would input a non existing value that would make no sense as a category."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 51,
   "id": "edd7493e-48b6-4ad1-ab0d-540b2bbf4478",
   "metadata": {},
   "outputs": [],
   "source": [
    "def make_imputations(df):\n",
    "    \"\"\"\n",
    "    Method to imput most frequent value to the remaining columns with missing data.\n",
    "    \n",
    "    Args:\n",
    "        df: dataframe to have missing data handled\n",
    "\n",
    "    Returns:\n",
    "        df: dataframe with missing data handled\n",
    "    \"\"\"\n",
    "    \n",
    "    # make sure all NaNs are actually NaNs\n",
    "    df.replace(['None', 'nan'], np.nan, inplace=True)\n",
    "    \n",
    "    # transform columns dtype object to string\n",
    "    df['OST_WEST_KZ'] = df['OST_WEST_KZ'].astype('str')\n",
    "    \n",
    "    # input most common value to remaining missing columns\n",
    "    remaining_null_cols = list(df.columns[(df.isnull().sum())>0])\n",
    "    print(f'Imputting most frequent values to remaining {len(remaining_null_cols)} columns\\nThis might take a while...\\n')\n",
    "    \n",
    "    imputer = SimpleImputer(missing_values = np.nan, strategy=\"most_frequent\")\n",
    "    df = pd.DataFrame(imputer.fit_transform(df), columns = df.columns, index = df.index)\n",
    "    \n",
    "    return df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 52,
   "id": "17c5651c-2b44-4908-a630-44015c535dbd",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Imputting most frequent values to remaining 61 columns\n",
      "This might take a while...\n",
      "\n",
      "Imputting most frequent values to remaining 61 columns\n",
      "This might take a while...\n",
      "\n"
     ]
    }
   ],
   "source": [
    "azdias = make_imputations(azdias)\n",
    "customers = make_imputations(customers)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "128f877b-5e5c-4346-b49a-8f628483cd69",
   "metadata": {},
   "source": [
    "Now let's check how our missing values are."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 53,
   "id": "77800b8e-d7e3-4cbb-8627-bd02bab4b3cb",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Null values on azdias dataframe:     0\n",
      "Null values on customers dataframe:  0\n"
     ]
    }
   ],
   "source": [
    "print('Null values on azdias dataframe:    ', azdias.isnull().sum().sum())\n",
    "print('Null values on customers dataframe: ', azdias.isnull().sum().sum())"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "badc4fdd-38a3-47e3-9d96-0ab840e0caf5",
   "metadata": {},
   "source": [
    "We're all done dealing with missing data now!"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "c2f46534-c009-48e1-b036-07565ae87209",
   "metadata": {},
   "source": [
    "#### Feature Selection"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "cbeb1fb6-1e7c-4704-aae7-1dfa7f093308",
   "metadata": {},
   "source": [
    "It's time to take a look at the columns available and to choose witch ones to keep for our purpose here."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 54,
   "id": "d04916f5-ec16-4ab3-b619-81de0f599dda",
   "metadata": {},
   "outputs": [],
   "source": [
    "unwanted_cols = [\n",
    "    'ALTER_HH', 'ALTERSKATEGORIE_FEIN', 'ANZ_STATISTISCHE_HAUSHALTE', 'CAMEO_DEU_2015',\n",
    "    'CAMEO_INTL_2015', 'D19_LETZTER_KAUF_BRANCHE', 'EINGEFUEGT_AM', 'LP_LEBENSPHASE_FEIN']\n",
    "\n",
    "# anything above 10 will be considered 10\n",
    "cols_change_max_10 = ['ANZ_HH_TITEL']\n",
    "\n",
    "# anything above 10 will be considered 5\n",
    "cols_change_max_5 = ['ANZ_PERSONEN']\n",
    "\n",
    "# group column according to data_att\n",
    "# 1-2, 3-5, 6-7, 8-9, 10\n",
    "cols_to_group = ['LP_STATUS_GROB']"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "10572790-1b59-47f8-bd08-b01115139613",
   "metadata": {},
   "source": [
    "After digging into the columns, there are some final changes we need to make before we prepare our datasets for the task.\n",
    "\n",
    "We've selected a couple more columns to drop due to redundance or because it doesn't show potential to the task. Others were selected to be cleaned up a bit more in terms of entry values."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 55,
   "id": "f5f4c52b-69bc-4a07-a3ce-3e946ee28215",
   "metadata": {},
   "outputs": [],
   "source": [
    "def more_data_cleaning(df):\n",
    "    \"\"\"\n",
    "    Method to handle final data cleaning columns.\n",
    "    \n",
    "    Args:\n",
    "        df: dataframe to be cleaned\n",
    "\n",
    "    Returns:\n",
    "        df: cleaned dataframe\n",
    "    \"\"\"\n",
    "    \n",
    "    # drop unwanted columns from df\n",
    "    print(f'Dropping {len(unwanted_cols)} columns.')\n",
    "    df = df.drop(columns=unwanted_cols)\n",
    "    \n",
    "    # changing values to match a maximum of 10\n",
    "    print(f'Changing values to 10 on {len(cols_change_max_10)} column.')\n",
    "    df[cols_change_max_10] = np.where(df[cols_change_max_10]>=10.0 , 10.0, df[cols_change_max_10])\n",
    "    \n",
    "    # changing values to match a maximum of 5\n",
    "    print(f'Changing values to 5 on {len(cols_change_max_5)} column.')\n",
    "    df[cols_change_max_5] = np.where(df[cols_change_max_5]>=5.0 , 5.0, df[cols_change_max_5])\n",
    "    \n",
    "    # changing values of column according to data_att\n",
    "    print(f'Changing values to group common entries on {len(cols_to_group)} column.')\n",
    "    df[cols_to_group] = np.where(df[cols_to_group]==2.0 , 1.0, df[cols_to_group])\n",
    "    df[cols_to_group] = np.where(df[cols_to_group]==4.0 , 3.0, df[cols_to_group])\n",
    "    df[cols_to_group] = np.where(df[cols_to_group]==5.0 , 3.0, df[cols_to_group])\n",
    "    df[cols_to_group] = np.where(df[cols_to_group]==7.0 , 6.0, df[cols_to_group])\n",
    "    df[cols_to_group] = np.where(df[cols_to_group]==9.0 , 8.0, df[cols_to_group])\n",
    "    \n",
    "    return df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 56,
   "id": "be82dfee-cd19-45ea-b0c0-b3047420b3dd",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Dropping 8 columns.\n",
      "Changing values to 10 on 1 column.\n",
      "Changing values to 5 on 1 column.\n",
      "Changing values to group common entries on 1 column.\n",
      "Dropping 8 columns.\n",
      "Changing values to 10 on 1 column.\n",
      "Changing values to 5 on 1 column.\n",
      "Changing values to group common entries on 1 column.\n"
     ]
    }
   ],
   "source": [
    "azdias = more_data_cleaning(azdias)\n",
    "customers = more_data_cleaning(customers)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 57,
   "id": "21069d99-cd3c-4d21-88e9-6dd84df024a9",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(222805, 351)"
      ]
     },
     "execution_count": 57,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "azdias.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 58,
   "id": "33de9077-25ca-4095-a9ec-9232f4c212c1",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(47913, 351)"
      ]
     },
     "execution_count": 58,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "customers.shape"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "d093eddf-c70c-466a-b194-fb1ae2a2c757",
   "metadata": {},
   "source": [
    "#### Categorical Features"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "2251d43a-3ab0-487a-bcab-1a8beb3211bc",
   "metadata": {},
   "source": [
    "Let's handle some of the categorical features that are in innapropriate formats."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 59,
   "id": "7fb38fbd-1de8-46ab-bbd2-2608e9de9734",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "DSL_FLAG\n",
      "GREEN_AVANTGARDE\n",
      "HH_DELTA_FLAG\n",
      "KBA13_ANTG4\n",
      "KONSUMZELLE\n",
      "LP_STATUS_GROB\n",
      "OST_WEST_KZ\n",
      "SOHO_KZ\n",
      "STRUKTURTYP\n",
      "UNGLEICHENN_FLAG\n",
      "VERS_TYP\n",
      "ANREDE_KZ\n",
      "['DSL_FLAG', 'GREEN_AVANTGARDE', 'HH_DELTA_FLAG', 'KBA13_ANTG4', 'KONSUMZELLE', 'LP_STATUS_GROB', 'OST_WEST_KZ', 'SOHO_KZ', 'STRUKTURTYP', 'UNGLEICHENN_FLAG', 'VERS_TYP', 'ANREDE_KZ']\n"
     ]
    }
   ],
   "source": [
    "# check for columns with less than 3 categories\n",
    "\n",
    "binary_cat_cols = []\n",
    "\n",
    "for col in azdias.columns:\n",
    "    if azdias[col].nunique() <= 3:\n",
    "        binary_cat_cols.append(col)\n",
    "        print(col)\n",
    "        \n",
    "print(binary_cat_cols)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 60,
   "id": "89b837a8-c3a8-4bc1-b6a1-053e21c94bae",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "1.0    216372\n",
      "0.0      6433\n",
      "Name: DSL_FLAG, dtype: int64\n",
      "\n",
      "\n",
      "0    178809\n",
      "1     43996\n",
      "Name: GREEN_AVANTGARDE, dtype: int64\n",
      "\n",
      "\n",
      "0.0    204652\n",
      "1.0     18153\n",
      "Name: HH_DELTA_FLAG, dtype: int64\n",
      "\n",
      "\n",
      "0.0    121899\n",
      "1.0     69411\n",
      "2.0     31495\n",
      "Name: KBA13_ANTG4, dtype: int64\n",
      "\n",
      "\n",
      "0.0    175977\n",
      "1.0     46828\n",
      "Name: KONSUMZELLE, dtype: int64\n",
      "\n",
      "\n",
      "1.0    142241\n",
      "3.0     80564\n",
      "Name: LP_STATUS_GROB, dtype: int64\n",
      "\n",
      "\n",
      "W     157385\n",
      "O      41997\n",
      "-1     23423\n",
      "Name: OST_WEST_KZ, dtype: int64\n",
      "\n",
      "\n",
      "0.0    221109\n",
      "1.0      1696\n",
      "Name: SOHO_KZ, dtype: int64\n",
      "\n",
      "\n",
      "3.0    163145\n",
      "1.0     32002\n",
      "2.0     27658\n",
      "Name: STRUKTURTYP, dtype: int64\n",
      "\n",
      "\n",
      "0.0    204572\n",
      "1.0     18233\n",
      "Name: UNGLEICHENN_FLAG, dtype: int64\n",
      "\n",
      "\n",
      " 2    99437\n",
      " 1    95336\n",
      "-1    28032\n",
      "Name: VERS_TYP, dtype: int64\n",
      "\n",
      "\n",
      "2    116435\n",
      "1    106370\n",
      "Name: ANREDE_KZ, dtype: int64\n",
      "\n",
      "\n"
     ]
    }
   ],
   "source": [
    "for col in binary_cat_cols:\n",
    "    print(azdias[col].value_counts())\n",
    "    print('\\n')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 61,
   "id": "b74dcfdc-56dc-4486-a0fa-7ee39e3c049c",
   "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>Attribute</th>\n",
       "      <th>Description</th>\n",
       "      <th>Value</th>\n",
       "      <th>Meaning</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2013</th>\n",
       "      <td>OST_WEST_KZ</td>\n",
       "      <td>flag indicating the former GDR/FRG</td>\n",
       "      <td>-1</td>\n",
       "      <td>unknown</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2014</th>\n",
       "      <td>OST_WEST_KZ</td>\n",
       "      <td>flag indicating the former GDR/FRG</td>\n",
       "      <td>O</td>\n",
       "      <td>East (GDR)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2015</th>\n",
       "      <td>OST_WEST_KZ</td>\n",
       "      <td>flag indicating the former GDR/FRG</td>\n",
       "      <td>W</td>\n",
       "      <td>West (FRG)</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "        Attribute                         Description Value     Meaning\n",
       "2013  OST_WEST_KZ  flag indicating the former GDR/FRG    -1     unknown\n",
       "2014  OST_WEST_KZ  flag indicating the former GDR/FRG     O  East (GDR)\n",
       "2015  OST_WEST_KZ  flag indicating the former GDR/FRG     W  West (FRG)"
      ]
     },
     "execution_count": 61,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data_att[data_att.Attribute.str.contains('OST_WEST_KZ')]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 62,
   "id": "884b2e75-7c46-4960-a7e8-90657b5ac5e9",
   "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>Attribute</th>\n",
       "      <th>Description</th>\n",
       "      <th>Value</th>\n",
       "      <th>Meaning</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2216</th>\n",
       "      <td>VERS_TYP</td>\n",
       "      <td>insurance typology</td>\n",
       "      <td>-1</td>\n",
       "      <td>unknown</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2217</th>\n",
       "      <td>VERS_TYP</td>\n",
       "      <td>insurance typology</td>\n",
       "      <td>1</td>\n",
       "      <td>social-safety driven</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2218</th>\n",
       "      <td>VERS_TYP</td>\n",
       "      <td>insurance typology</td>\n",
       "      <td>2</td>\n",
       "      <td>individualistic-accepting risks</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     Attribute          Description Value                          Meaning\n",
       "2216  VERS_TYP  insurance typology     -1                          unknown\n",
       "2217  VERS_TYP  insurance typology      1             social-safety driven\n",
       "2218  VERS_TYP  insurance typology      2  individualistic-accepting risks"
      ]
     },
     "execution_count": 62,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data_att[data_att.Attribute.str.contains('VERS_TYP')]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 63,
   "id": "247cf3d9-06b8-4882-afe9-dccbd0db727b",
   "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>Attribute</th>\n",
       "      <th>Description</th>\n",
       "      <th>Value</th>\n",
       "      <th>Meaning</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>33</th>\n",
       "      <td>ANREDE_KZ</td>\n",
       "      <td>gender</td>\n",
       "      <td>-1, 0</td>\n",
       "      <td>unknown</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>34</th>\n",
       "      <td>ANREDE_KZ</td>\n",
       "      <td>gender</td>\n",
       "      <td>1</td>\n",
       "      <td>male</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>35</th>\n",
       "      <td>ANREDE_KZ</td>\n",
       "      <td>gender</td>\n",
       "      <td>2</td>\n",
       "      <td>female</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    Attribute Description  Value  Meaning\n",
       "33  ANREDE_KZ      gender  -1, 0  unknown\n",
       "34  ANREDE_KZ      gender      1     male\n",
       "35  ANREDE_KZ      gender      2   female"
      ]
     },
     "execution_count": 63,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data_att[data_att.Attribute.str.contains('ANREDE_KZ')]"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "0259094d-1d07-4c2e-9177-1b359a1320dd",
   "metadata": {},
   "source": [
    "Here's what we'll be doing to these three columns:\n",
    "\n",
    "`OST_WEST_KZ`: will turn `O` and `W` into `0` and `1`.\n",
    "\n",
    "`VERS_TYP` and `ANREDE_KZ`: will turn `2` into `0`."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 64,
   "id": "0bc56b0e-40b0-44c4-8b5d-98da5cdebfbf",
   "metadata": {},
   "outputs": [],
   "source": [
    "def fix_binary_cols(df):\n",
    "    \"\"\"\n",
    "    Method to fix binary category columns.\n",
    "    \n",
    "    Args:\n",
    "        df: dataframe to be fixed\n",
    "\n",
    "    Returns:\n",
    "        df: fixed dataframe\n",
    "    \"\"\"\n",
    "    \n",
    "    df['OST_WEST_KZ'] = df['OST_WEST_KZ'].replace({'W': 1,\n",
    "                                                   'O': 0})\n",
    "    \n",
    "    df['VERS_TYP'] = df['VERS_TYP'].replace(2, 0)\n",
    "    \n",
    "    df['ANREDE_KZ'] = df['ANREDE_KZ'].replace(2, 0)\n",
    "    \n",
    "    return df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 65,
   "id": "e4674b43-1ca6-48e9-9754-8da43a69dd30",
   "metadata": {},
   "outputs": [],
   "source": [
    "azdias = fix_binary_cols(azdias)\n",
    "customers = fix_binary_cols(customers)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 66,
   "id": "f9504feb-c48c-4687-83c5-951fb4057f12",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "LNR\n",
      "ANZ_HAUSHALTE_AKTIV\n",
      "EINGEZOGENAM_HH_JAHR\n",
      "GEBURTSJAHR\n",
      "GFK_URLAUBERTYP\n",
      "KBA13_ANZAHL_PKW\n",
      "LP_FAMILIE_FEIN\n",
      "LP_LEBENSPHASE_GROB\n",
      "MIN_GEBAEUDEJAHR\n",
      "PRAEGENDE_JUGENDJAHRE\n",
      "VERDICHTUNGSRAUM\n",
      "VK_DISTANZ\n",
      "['LNR', 'ANZ_HAUSHALTE_AKTIV', 'EINGEZOGENAM_HH_JAHR', 'GEBURTSJAHR', 'GFK_URLAUBERTYP', 'KBA13_ANZAHL_PKW', 'LP_FAMILIE_FEIN', 'LP_LEBENSPHASE_GROB', 'MIN_GEBAEUDEJAHR', 'PRAEGENDE_JUGENDJAHRE', 'VERDICHTUNGSRAUM', 'VK_DISTANZ']\n"
     ]
    }
   ],
   "source": [
    "# check for columns with over 10 categories\n",
    "\n",
    "many_cat_cols = []\n",
    "\n",
    "for col in azdias.columns:\n",
    "    if azdias[col].nunique() > 11:\n",
    "        many_cat_cols.append(col)\n",
    "        print(col)\n",
    "        \n",
    "print(many_cat_cols)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 67,
   "id": "1d8e35f9-5e9e-4ebc-afcd-5a90cc17daee",
   "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>Attribute</th>\n",
       "      <th>Description</th>\n",
       "      <th>Value</th>\n",
       "      <th>Meaning</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2054</th>\n",
       "      <td>PRAEGENDE_JUGENDJAHRE</td>\n",
       "      <td>dominating movement in the person's youth (ava...</td>\n",
       "      <td>-1, 0</td>\n",
       "      <td>unknown</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2055</th>\n",
       "      <td>PRAEGENDE_JUGENDJAHRE</td>\n",
       "      <td>dominating movement in the person's youth (ava...</td>\n",
       "      <td>1</td>\n",
       "      <td>40ies - war years (Mainstream, O+W)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2056</th>\n",
       "      <td>PRAEGENDE_JUGENDJAHRE</td>\n",
       "      <td>dominating movement in the person's youth (ava...</td>\n",
       "      <td>2</td>\n",
       "      <td>40ies - reconstruction years (Avantgarde, O+W)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2057</th>\n",
       "      <td>PRAEGENDE_JUGENDJAHRE</td>\n",
       "      <td>dominating movement in the person's youth (ava...</td>\n",
       "      <td>3</td>\n",
       "      <td>50ies - economic miracle (Mainstream, O+W)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2058</th>\n",
       "      <td>PRAEGENDE_JUGENDJAHRE</td>\n",
       "      <td>dominating movement in the person's youth (ava...</td>\n",
       "      <td>4</td>\n",
       "      <td>50ies - milk bar / Individualisation (Avantgar...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2059</th>\n",
       "      <td>PRAEGENDE_JUGENDJAHRE</td>\n",
       "      <td>dominating movement in the person's youth (ava...</td>\n",
       "      <td>5</td>\n",
       "      <td>60ies - economic miracle (Mainstream, O+W)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2060</th>\n",
       "      <td>PRAEGENDE_JUGENDJAHRE</td>\n",
       "      <td>dominating movement in the person's youth (ava...</td>\n",
       "      <td>6</td>\n",
       "      <td>60ies - generation 68 / student protestors (Av...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2061</th>\n",
       "      <td>PRAEGENDE_JUGENDJAHRE</td>\n",
       "      <td>dominating movement in the person's youth (ava...</td>\n",
       "      <td>7</td>\n",
       "      <td>60ies - opponents to the building of the Wall ...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2062</th>\n",
       "      <td>PRAEGENDE_JUGENDJAHRE</td>\n",
       "      <td>dominating movement in the person's youth (ava...</td>\n",
       "      <td>8</td>\n",
       "      <td>70ies - family orientation (Mainstream, O+W)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2063</th>\n",
       "      <td>PRAEGENDE_JUGENDJAHRE</td>\n",
       "      <td>dominating movement in the person's youth (ava...</td>\n",
       "      <td>9</td>\n",
       "      <td>70ies - peace movement (Avantgarde, O+W)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2064</th>\n",
       "      <td>PRAEGENDE_JUGENDJAHRE</td>\n",
       "      <td>dominating movement in the person's youth (ava...</td>\n",
       "      <td>10</td>\n",
       "      <td>80ies - Generation Golf (Mainstream, W)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2065</th>\n",
       "      <td>PRAEGENDE_JUGENDJAHRE</td>\n",
       "      <td>dominating movement in the person's youth (ava...</td>\n",
       "      <td>11</td>\n",
       "      <td>80ies - ecological awareness (Avantgarde, W)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2066</th>\n",
       "      <td>PRAEGENDE_JUGENDJAHRE</td>\n",
       "      <td>dominating movement in the person's youth (ava...</td>\n",
       "      <td>12</td>\n",
       "      <td>80ies - FDJ / communist party youth organisati...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2067</th>\n",
       "      <td>PRAEGENDE_JUGENDJAHRE</td>\n",
       "      <td>dominating movement in the person's youth (ava...</td>\n",
       "      <td>13</td>\n",
       "      <td>80ies - Swords into ploughshares (Avantgarde, O)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2068</th>\n",
       "      <td>PRAEGENDE_JUGENDJAHRE</td>\n",
       "      <td>dominating movement in the person's youth (ava...</td>\n",
       "      <td>14</td>\n",
       "      <td>90ies - digital media kids (Mainstream, O+W)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2069</th>\n",
       "      <td>PRAEGENDE_JUGENDJAHRE</td>\n",
       "      <td>dominating movement in the person's youth (ava...</td>\n",
       "      <td>15</td>\n",
       "      <td>90ies - ecological awareness (Avantgarde, O+W)</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                  Attribute  \\\n",
       "2054  PRAEGENDE_JUGENDJAHRE   \n",
       "2055  PRAEGENDE_JUGENDJAHRE   \n",
       "2056  PRAEGENDE_JUGENDJAHRE   \n",
       "2057  PRAEGENDE_JUGENDJAHRE   \n",
       "2058  PRAEGENDE_JUGENDJAHRE   \n",
       "2059  PRAEGENDE_JUGENDJAHRE   \n",
       "2060  PRAEGENDE_JUGENDJAHRE   \n",
       "2061  PRAEGENDE_JUGENDJAHRE   \n",
       "2062  PRAEGENDE_JUGENDJAHRE   \n",
       "2063  PRAEGENDE_JUGENDJAHRE   \n",
       "2064  PRAEGENDE_JUGENDJAHRE   \n",
       "2065  PRAEGENDE_JUGENDJAHRE   \n",
       "2066  PRAEGENDE_JUGENDJAHRE   \n",
       "2067  PRAEGENDE_JUGENDJAHRE   \n",
       "2068  PRAEGENDE_JUGENDJAHRE   \n",
       "2069  PRAEGENDE_JUGENDJAHRE   \n",
       "\n",
       "                                            Description  Value  \\\n",
       "2054  dominating movement in the person's youth (ava...  -1, 0   \n",
       "2055  dominating movement in the person's youth (ava...      1   \n",
       "2056  dominating movement in the person's youth (ava...      2   \n",
       "2057  dominating movement in the person's youth (ava...      3   \n",
       "2058  dominating movement in the person's youth (ava...      4   \n",
       "2059  dominating movement in the person's youth (ava...      5   \n",
       "2060  dominating movement in the person's youth (ava...      6   \n",
       "2061  dominating movement in the person's youth (ava...      7   \n",
       "2062  dominating movement in the person's youth (ava...      8   \n",
       "2063  dominating movement in the person's youth (ava...      9   \n",
       "2064  dominating movement in the person's youth (ava...     10   \n",
       "2065  dominating movement in the person's youth (ava...     11   \n",
       "2066  dominating movement in the person's youth (ava...     12   \n",
       "2067  dominating movement in the person's youth (ava...     13   \n",
       "2068  dominating movement in the person's youth (ava...     14   \n",
       "2069  dominating movement in the person's youth (ava...     15   \n",
       "\n",
       "                                                Meaning  \n",
       "2054                                            unknown  \n",
       "2055                40ies - war years (Mainstream, O+W)  \n",
       "2056     40ies - reconstruction years (Avantgarde, O+W)  \n",
       "2057         50ies - economic miracle (Mainstream, O+W)  \n",
       "2058  50ies - milk bar / Individualisation (Avantgar...  \n",
       "2059         60ies - economic miracle (Mainstream, O+W)  \n",
       "2060  60ies - generation 68 / student protestors (Av...  \n",
       "2061  60ies - opponents to the building of the Wall ...  \n",
       "2062       70ies - family orientation (Mainstream, O+W)  \n",
       "2063           70ies - peace movement (Avantgarde, O+W)  \n",
       "2064            80ies - Generation Golf (Mainstream, W)  \n",
       "2065       80ies - ecological awareness (Avantgarde, W)  \n",
       "2066  80ies - FDJ / communist party youth organisati...  \n",
       "2067   80ies - Swords into ploughshares (Avantgarde, O)  \n",
       "2068       90ies - digital media kids (Mainstream, O+W)  \n",
       "2069     90ies - ecological awareness (Avantgarde, O+W)  "
      ]
     },
     "execution_count": 67,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data_att[data_att.Attribute.str.contains('PRAEGENDE_JUGENDJAHRE')]"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "7d6af0a5-dc74-4543-ad8b-1623e54d929d",
   "metadata": {},
   "source": [
    "The column above has too many categories so we'll split it into two columns: one regarding the movement (avantgard or mainstream) and the decade of infuence."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 68,
   "id": "aaaf9693-a0aa-43c5-a823-d16a5a789c7b",
   "metadata": {},
   "outputs": [],
   "source": [
    "decade = {\n",
    "    1: 1,\n",
    "    2: 1,\n",
    "    3: 2,\n",
    "    4: 2,\n",
    "    5: 3,\n",
    "    6: 3,\n",
    "    7: 3,\n",
    "    8: 4,\n",
    "    9: 4,\n",
    "    10: 5,\n",
    "    11: 5,\n",
    "    12: 5,\n",
    "    13: 5,\n",
    "    14: 6,\n",
    "    15: 6\n",
    "}\n",
    "\n",
    "mainstream = {\n",
    "    1: 1,\n",
    "    2: 0,\n",
    "    3: 1,\n",
    "    4: 0,\n",
    "    5: 1,\n",
    "    6: 0,\n",
    "    7: 0,\n",
    "    8: 1,\n",
    "    9: 0,\n",
    "    10: 1,\n",
    "    11: 0,\n",
    "    12: 1,\n",
    "    13: 0,\n",
    "    14: 1,\n",
    "    15: 0\n",
    "}"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 69,
   "id": "50e130b9-237a-42be-a6a7-d4e7eb42dd1a",
   "metadata": {},
   "outputs": [],
   "source": [
    "def create_decade_movement(df):\n",
    "    \"\"\"\n",
    "    Method to create two columns out of a columns with too many categories.\n",
    "    \n",
    "    Args:\n",
    "        df: dataframe to be fixed\n",
    "\n",
    "    Returns:\n",
    "        df: fixed dataframe\n",
    "    \"\"\"\n",
    "    \n",
    "    df['PRAEGENDE_JUGENDJAHRE_DECADE'] = df['PRAEGENDE_JUGENDJAHRE'].replace(decade)\n",
    "    df['PRAEGENDE_JUGENDJAHRE_MAINSTREAM'] = df['PRAEGENDE_JUGENDJAHRE'].replace(mainstream)\n",
    "    df.drop(columns='PRAEGENDE_JUGENDJAHRE', inplace=True)\n",
    "    \n",
    "    return df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 70,
   "id": "8ea11111-997d-48db-b96a-b1414f48d93c",
   "metadata": {},
   "outputs": [],
   "source": [
    "azdias = create_decade_movement(azdias)\n",
    "customers = create_decade_movement(customers)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "2f164983-c204-4620-8ea7-07b58195f5f9",
   "metadata": {},
   "source": [
    "#### Columns types"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "928f1cb2-112a-4b8c-8932-8a560740a199",
   "metadata": {},
   "source": [
    "It's important that we make sure our columns are numerical so that the unsupervised learning models for clustering works."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 71,
   "id": "b4d9bd0d-07d9-49aa-a41b-6c5e7ef12017",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 222805 entries, 0 to 222804\n",
      "Columns: 352 entries, LNR to PRAEGENDE_JUGENDJAHRE_MAINSTREAM\n",
      "dtypes: int64(4), object(348)\n",
      "memory usage: 598.4+ MB\n"
     ]
    }
   ],
   "source": [
    "azdias.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 72,
   "id": "e83e09f0-d800-422e-8e2e-ba8cac4bafce",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 47913 entries, 0 to 47912\n",
      "Columns: 352 entries, LNR to PRAEGENDE_JUGENDJAHRE_MAINSTREAM\n",
      "dtypes: int64(4), object(348)\n",
      "memory usage: 128.7+ MB\n"
     ]
    }
   ],
   "source": [
    "customers.info()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "202fa0df-440c-4154-bb3e-093d13812f5f",
   "metadata": {},
   "source": [
    "Most of our columns are object dtype. As we've alredy made all necessary changes previously we can go ahead and change the columns to numeric dtype."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 73,
   "id": "09f7dfcd-aba1-4aa6-9f5c-5eb074e1a872",
   "metadata": {},
   "outputs": [],
   "source": [
    "def change_cols_dtypes(df):\n",
    "    \"\"\"\n",
    "    Method to change columns dtypes to numeric.\n",
    "    \n",
    "    Args:\n",
    "        df: dataframe to be fixed\n",
    "\n",
    "    Returns:\n",
    "        df: fixed dataframe\n",
    "    \"\"\"\n",
    "    \n",
    "    df = df.apply(pd.to_numeric)\n",
    "    \n",
    "    return df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 74,
   "id": "e6015665-3f0d-488c-88de-97193f0dfab9",
   "metadata": {},
   "outputs": [],
   "source": [
    "azdias = change_cols_dtypes(azdias)\n",
    "customers = change_cols_dtypes(customers)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 75,
   "id": "9efd5c69-d929-4f56-bc90-7a9d0e11993f",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 222805 entries, 0 to 222804\n",
      "Columns: 352 entries, LNR to PRAEGENDE_JUGENDJAHRE_MAINSTREAM\n",
      "dtypes: float64(257), int64(95)\n",
      "memory usage: 598.4 MB\n"
     ]
    }
   ],
   "source": [
    "azdias.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 76,
   "id": "d01c341c-1b61-4808-99ff-326cc68bb6b5",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 47913 entries, 0 to 47912\n",
      "Columns: 352 entries, LNR to PRAEGENDE_JUGENDJAHRE_MAINSTREAM\n",
      "dtypes: float64(257), int64(95)\n",
      "memory usage: 128.7 MB\n"
     ]
    }
   ],
   "source": [
    "customers.info()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "4ed764cc-7893-4859-84cf-70e1c0d85cc6",
   "metadata": {},
   "source": [
    "#### More feature selection"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "8b87034a-c6f8-420b-96f1-0b7b13f83d09",
   "metadata": {},
   "source": [
    "We're gonna check out some columns that are highly correlated between them. We should avoid having those because it can interfere on the clustering due to redundancy."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 77,
   "id": "976e338c-8dd1-4247-904a-1fd850c998b5",
   "metadata": {},
   "outputs": [],
   "source": [
    "def get_high_corr_cols(df, t):\n",
    "    \"\"\"\n",
    "    Method to get the columns with chigh correlation coeficient (above the threshold).\n",
    "    \n",
    "    Args:\n",
    "        df: dataframe to be fixed\n",
    "        t: threshold for correlation coeficient\n",
    "\n",
    "    Returns:\n",
    "        df: fixed dataframe\n",
    "    \"\"\"\n",
    "\n",
    "    # select the upper triangle from the correlation matrix\n",
    "    upper = df.corr(method='spearman').abs().where(np.triu(np.ones(azdias.corr(method='spearman').abs().shape), k=1).astype(bool))\n",
    "\n",
    "    # find columns with correlation coeficient higher than threshold\n",
    "    pos_high_corr_cols = [column for column in upper.columns if any(upper[column] > t)]\n",
    "    neg_high_corr_cols = [column for column in upper.columns if any(upper[column] < -t)]\n",
    "    \n",
    "    high_corr_cols = pos_high_corr_cols + neg_high_corr_cols\n",
    "    \n",
    "    return high_corr_cols"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 78,
   "id": "a19b1fb7-cf2a-4100-bfac-3d278a32d9ee",
   "metadata": {},
   "outputs": [],
   "source": [
    "high_corr_cols = get_high_corr_cols(azdias, 0.85)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 79,
   "id": "775bc718-85a2-4c92-a95c-c11b5c6e466a",
   "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>CJT_TYP_2</th>\n",
       "      <th>D19_BANKEN_ONLINE_DATUM</th>\n",
       "      <th>D19_BANKEN_ONLINE_QUOTE_12</th>\n",
       "      <th>D19_GESAMT_ANZ_24</th>\n",
       "      <th>D19_GESAMT_DATUM</th>\n",
       "      <th>D19_GESAMT_ONLINE_DATUM</th>\n",
       "      <th>D19_KONSUMTYP</th>\n",
       "      <th>D19_KONSUMTYP_MAX</th>\n",
       "      <th>D19_VERSAND_ANZ_12</th>\n",
       "      <th>D19_VERSAND_ANZ_24</th>\n",
       "      <th>...</th>\n",
       "      <th>KBA13_SEG_KLEINWAGEN</th>\n",
       "      <th>KBA13_SEG_VAN</th>\n",
       "      <th>KBA13_VW</th>\n",
       "      <th>LP_FAMILIE_FEIN</th>\n",
       "      <th>LP_FAMILIE_GROB</th>\n",
       "      <th>LP_LEBENSPHASE_GROB</th>\n",
       "      <th>ORTSGR_KLS9</th>\n",
       "      <th>PLZ8_BAUMAX</th>\n",
       "      <th>PLZ8_HHZ</th>\n",
       "      <th>PRAEGENDE_JUGENDJAHRE_DECADE</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2.0</td>\n",
       "      <td>6</td>\n",
       "      <td>0.0</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>6</td>\n",
       "      <td>2.0</td>\n",
       "      <td>3</td>\n",
       "      <td>1</td>\n",
       "      <td>3</td>\n",
       "      <td>...</td>\n",
       "      <td>-1.0</td>\n",
       "      <td>-1.0</td>\n",
       "      <td>-1.0</td>\n",
       "      <td>11.0</td>\n",
       "      <td>5.0</td>\n",
       "      <td>12.0</td>\n",
       "      <td>-1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>-1.0</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1.0</td>\n",
       "      <td>10</td>\n",
       "      <td>0.0</td>\n",
       "      <td>2</td>\n",
       "      <td>5</td>\n",
       "      <td>8</td>\n",
       "      <td>3.0</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>...</td>\n",
       "      <td>3.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>5.0</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>5.0</td>\n",
       "      <td>10</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0</td>\n",
       "      <td>10</td>\n",
       "      <td>10</td>\n",
       "      <td>9.0</td>\n",
       "      <td>9</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>-1.0</td>\n",
       "      <td>-1.0</td>\n",
       "      <td>-1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>-1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>-1.0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4.0</td>\n",
       "      <td>10</td>\n",
       "      <td>0.0</td>\n",
       "      <td>2</td>\n",
       "      <td>5</td>\n",
       "      <td>5</td>\n",
       "      <td>1.0</td>\n",
       "      <td>4</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>...</td>\n",
       "      <td>4.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>5.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>6.0</td>\n",
       "      <td>5.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>5.0</td>\n",
       "      <td>7</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1</td>\n",
       "      <td>7</td>\n",
       "      <td>7</td>\n",
       "      <td>5.0</td>\n",
       "      <td>3</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>2.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>7.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>6</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",
       "      <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>222800</th>\n",
       "      <td>3.0</td>\n",
       "      <td>10</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0</td>\n",
       "      <td>10</td>\n",
       "      <td>10</td>\n",
       "      <td>9.0</td>\n",
       "      <td>9</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>-1.0</td>\n",
       "      <td>-1.0</td>\n",
       "      <td>-1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>-1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>-1.0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>222801</th>\n",
       "      <td>4.0</td>\n",
       "      <td>10</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0</td>\n",
       "      <td>9</td>\n",
       "      <td>10</td>\n",
       "      <td>9.0</td>\n",
       "      <td>8</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>3.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>5.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>6.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>5.0</td>\n",
       "      <td>6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>222802</th>\n",
       "      <td>5.0</td>\n",
       "      <td>10</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>3.0</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>...</td>\n",
       "      <td>2.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>5.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>6.0</td>\n",
       "      <td>9.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>222803</th>\n",
       "      <td>2.0</td>\n",
       "      <td>10</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0</td>\n",
       "      <td>8</td>\n",
       "      <td>10</td>\n",
       "      <td>9.0</td>\n",
       "      <td>8</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>3.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>10.0</td>\n",
       "      <td>5.0</td>\n",
       "      <td>12.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>222804</th>\n",
       "      <td>5.0</td>\n",
       "      <td>10</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0</td>\n",
       "      <td>9</td>\n",
       "      <td>9</td>\n",
       "      <td>9.0</td>\n",
       "      <td>8</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>3.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>6</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>222805 rows × 42 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "        CJT_TYP_2  D19_BANKEN_ONLINE_DATUM  D19_BANKEN_ONLINE_QUOTE_12  \\\n",
       "0             2.0                        6                         0.0   \n",
       "1             1.0                       10                         0.0   \n",
       "2             5.0                       10                         0.0   \n",
       "3             4.0                       10                         0.0   \n",
       "4             5.0                        7                         0.0   \n",
       "...           ...                      ...                         ...   \n",
       "222800        3.0                       10                         0.0   \n",
       "222801        4.0                       10                         0.0   \n",
       "222802        5.0                       10                         0.0   \n",
       "222803        2.0                       10                         0.0   \n",
       "222804        5.0                       10                         0.0   \n",
       "\n",
       "        D19_GESAMT_ANZ_24  D19_GESAMT_DATUM  D19_GESAMT_ONLINE_DATUM  \\\n",
       "0                       4                 4                        6   \n",
       "1                       2                 5                        8   \n",
       "2                       0                10                       10   \n",
       "3                       2                 5                        5   \n",
       "4                       1                 7                        7   \n",
       "...                   ...               ...                      ...   \n",
       "222800                  0                10                       10   \n",
       "222801                  0                 9                       10   \n",
       "222802                  1                 2                        2   \n",
       "222803                  0                 8                       10   \n",
       "222804                  0                 9                        9   \n",
       "\n",
       "        D19_KONSUMTYP  D19_KONSUMTYP_MAX  D19_VERSAND_ANZ_12  \\\n",
       "0                 2.0                  3                   1   \n",
       "1                 3.0                  2                   2   \n",
       "2                 9.0                  9                   0   \n",
       "3                 1.0                  4                   2   \n",
       "4                 5.0                  3                   0   \n",
       "...               ...                ...                 ...   \n",
       "222800            9.0                  9                   0   \n",
       "222801            9.0                  8                   0   \n",
       "222802            3.0                  2                   1   \n",
       "222803            9.0                  8                   0   \n",
       "222804            9.0                  8                   0   \n",
       "\n",
       "        D19_VERSAND_ANZ_24  ...  KBA13_SEG_KLEINWAGEN  KBA13_SEG_VAN  \\\n",
       "0                        3  ...                  -1.0           -1.0   \n",
       "1                        2  ...                   3.0            3.0   \n",
       "2                        0  ...                  -1.0           -1.0   \n",
       "3                        2  ...                   4.0            3.0   \n",
       "4                        0  ...                   2.0            2.0   \n",
       "...                    ...  ...                   ...            ...   \n",
       "222800                   0  ...                  -1.0           -1.0   \n",
       "222801                   0  ...                   3.0            3.0   \n",
       "222802                   1  ...                   2.0            3.0   \n",
       "222803                   0  ...                   3.0            4.0   \n",
       "222804                   0  ...                   3.0            1.0   \n",
       "\n",
       "        KBA13_VW  LP_FAMILIE_FEIN  LP_FAMILIE_GROB  LP_LEBENSPHASE_GROB  \\\n",
       "0           -1.0             11.0              5.0                 12.0   \n",
       "1            2.0              1.0              1.0                  3.0   \n",
       "2           -1.0              0.0              0.0                  0.0   \n",
       "3            3.0              5.0              3.0                  6.0   \n",
       "4            3.0              1.0              1.0                  1.0   \n",
       "...          ...              ...              ...                  ...   \n",
       "222800      -1.0              1.0              1.0                  2.0   \n",
       "222801       4.0              5.0              3.0                  6.0   \n",
       "222802       2.0              5.0              3.0                  6.0   \n",
       "222803       3.0             10.0              5.0                 12.0   \n",
       "222804       4.0              1.0              1.0                  1.0   \n",
       "\n",
       "        ORTSGR_KLS9  PLZ8_BAUMAX  PLZ8_HHZ  PRAEGENDE_JUGENDJAHRE_DECADE  \n",
       "0              -1.0          1.0      -1.0                             4  \n",
       "1               3.0          1.0       5.0                             3  \n",
       "2              -1.0          1.0      -1.0                             0  \n",
       "3               5.0          1.0       3.0                             6  \n",
       "4               7.0          2.0       4.0                             6  \n",
       "...             ...          ...       ...                           ...  \n",
       "222800         -1.0          1.0      -1.0                             0  \n",
       "222801          4.0          1.0       5.0                             6  \n",
       "222802          9.0          3.0       3.0                             6  \n",
       "222803          1.0          1.0       1.0                             0  \n",
       "222804          4.0          1.0       4.0                             6  \n",
       "\n",
       "[222805 rows x 42 columns]"
      ]
     },
     "execution_count": 79,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "azdias[high_corr_cols]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 80,
   "id": "3d84a23d-0d0f-4066-8b11-16ee803e41c2",
   "metadata": {},
   "outputs": [],
   "source": [
    "def drop_high_corr_cols(df, high_corr_cols):\n",
    "    \"\"\"\n",
    "    Method to drop highly correlated columns.\n",
    "    \n",
    "    Args:\n",
    "        df: dataframe to be fixed\n",
    "\n",
    "    Returns:\n",
    "        df: fixed dataframe\n",
    "    \"\"\"\n",
    "\n",
    "    df.drop(columns=high_corr_cols, inplace=True)\n",
    "    \n",
    "    return df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 81,
   "id": "f9f09ef0-7672-4127-b7b2-6c5c4d18f8f9",
   "metadata": {},
   "outputs": [],
   "source": [
    "azdias = drop_high_corr_cols(azdias, high_corr_cols)\n",
    "customers = drop_high_corr_cols(customers, high_corr_cols)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "b1e3e582-270c-4dd8-b008-0679fdd848ba",
   "metadata": {},
   "source": [
    "And finally, we'll drop `LNR` column as it's a sort of id column, so it won't do us any good for now."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 82,
   "id": "0109ccf9-6637-46ac-82b8-e5bf9b8b9fcd",
   "metadata": {},
   "outputs": [],
   "source": [
    "def drop_id_col(df):\n",
    "    \"\"\"\n",
    "    Method to drop column LNR that is an id column.\n",
    "    \n",
    "    Args:\n",
    "        df: dataframe to be fixed\n",
    "\n",
    "    Returns:\n",
    "        df: fixed dataframe\n",
    "    \"\"\"\n",
    "    df.drop(columns='LNR', inplace=True)\n",
    "    \n",
    "    return df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 83,
   "id": "1e9c8197-6134-4d11-b5ec-223b8ed88779",
   "metadata": {},
   "outputs": [],
   "source": [
    "azdias = drop_id_col(azdias)\n",
    "customers = drop_id_col(customers)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "087363f8-a3c1-44c6-82a6-053462322c6d",
   "metadata": {},
   "source": [
    "Now let's go ahead and apply all those steps to both the TRAIN and TEST files provided as we'll need them for the last part of the project."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 84,
   "id": "8e39667e-2e2b-41eb-a80a-aa16ab9a7aa6",
   "metadata": {},
   "outputs": [],
   "source": [
    "# load in the data\n",
    "df_train = pd.read_csv('data/Udacity_MAILOUT_052018_TRAIN.csv', sep=';', low_memory=False)\n",
    "df_test = pd.read_csv('data/Udacity_MAILOUT_052018_TEST.csv', sep=';', low_memory=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 85,
   "id": "82530060-e6e3-4dd7-8d19-d23a8bf1f5e3",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "TRAIN:  (42962, 367)\n",
      "TEST:   (42833, 366)\n"
     ]
    }
   ],
   "source": [
    "# check the datasets sizes\n",
    "print('TRAIN: ', df_train.shape)\n",
    "print('TEST:  ', df_test.shape)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 86,
   "id": "ac59386e-95b4-411d-8676-00b02b8219bf",
   "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>LNR</th>\n",
       "      <th>AGER_TYP</th>\n",
       "      <th>AKT_DAT_KL</th>\n",
       "      <th>ALTER_HH</th>\n",
       "      <th>ALTER_KIND1</th>\n",
       "      <th>ALTER_KIND2</th>\n",
       "      <th>ALTER_KIND3</th>\n",
       "      <th>ALTER_KIND4</th>\n",
       "      <th>ALTERSKATEGORIE_FEIN</th>\n",
       "      <th>ANZ_HAUSHALTE_AKTIV</th>\n",
       "      <th>...</th>\n",
       "      <th>VK_DHT4A</th>\n",
       "      <th>VK_DISTANZ</th>\n",
       "      <th>VK_ZG11</th>\n",
       "      <th>W_KEIT_KIND_HH</th>\n",
       "      <th>WOHNDAUER_2008</th>\n",
       "      <th>WOHNLAGE</th>\n",
       "      <th>ZABEOTYP</th>\n",
       "      <th>RESPONSE</th>\n",
       "      <th>ANREDE_KZ</th>\n",
       "      <th>ALTERSKATEGORIE_GROB</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1763</td>\n",
       "      <td>2</td>\n",
       "      <td>1.0</td>\n",
       "      <td>8.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>8.0</td>\n",
       "      <td>15.0</td>\n",
       "      <td>...</td>\n",
       "      <td>5.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>6.0</td>\n",
       "      <td>9.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>3</td>\n",
       "      <td>0</td>\n",
       "      <td>2</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1771</td>\n",
       "      <td>1</td>\n",
       "      <td>4.0</td>\n",
       "      <td>13.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>13.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>...</td>\n",
       "      <td>1.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>9.0</td>\n",
       "      <td>7.0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>2</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1776</td>\n",
       "      <td>1</td>\n",
       "      <td>1.0</td>\n",
       "      <td>9.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>7.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>...</td>\n",
       "      <td>6.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>9.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>3</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1460</td>\n",
       "      <td>2</td>\n",
       "      <td>1.0</td>\n",
       "      <td>6.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>6.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>...</td>\n",
       "      <td>8.0</td>\n",
       "      <td>11.0</td>\n",
       "      <td>11.0</td>\n",
       "      <td>6.0</td>\n",
       "      <td>9.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>3</td>\n",
       "      <td>0</td>\n",
       "      <td>2</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1783</td>\n",
       "      <td>2</td>\n",
       "      <td>1.0</td>\n",
       "      <td>9.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>9.0</td>\n",
       "      <td>53.0</td>\n",
       "      <td>...</td>\n",
       "      <td>2.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>6.0</td>\n",
       "      <td>9.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>3</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 367 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "    LNR  AGER_TYP  AKT_DAT_KL  ALTER_HH  ALTER_KIND1  ALTER_KIND2  \\\n",
       "0  1763         2         1.0       8.0          NaN          NaN   \n",
       "1  1771         1         4.0      13.0          NaN          NaN   \n",
       "2  1776         1         1.0       9.0          NaN          NaN   \n",
       "3  1460         2         1.0       6.0          NaN          NaN   \n",
       "4  1783         2         1.0       9.0          NaN          NaN   \n",
       "\n",
       "   ALTER_KIND3  ALTER_KIND4  ALTERSKATEGORIE_FEIN  ANZ_HAUSHALTE_AKTIV  ...  \\\n",
       "0          NaN          NaN                   8.0                 15.0  ...   \n",
       "1          NaN          NaN                  13.0                  1.0  ...   \n",
       "2          NaN          NaN                   7.0                  0.0  ...   \n",
       "3          NaN          NaN                   6.0                  4.0  ...   \n",
       "4          NaN          NaN                   9.0                 53.0  ...   \n",
       "\n",
       "   VK_DHT4A  VK_DISTANZ  VK_ZG11  W_KEIT_KIND_HH  WOHNDAUER_2008  WOHNLAGE  \\\n",
       "0       5.0         2.0      1.0             6.0             9.0       3.0   \n",
       "1       1.0         2.0      1.0             4.0             9.0       7.0   \n",
       "2       6.0         4.0      2.0             NaN             9.0       2.0   \n",
       "3       8.0        11.0     11.0             6.0             9.0       1.0   \n",
       "4       2.0         2.0      1.0             6.0             9.0       3.0   \n",
       "\n",
       "   ZABEOTYP RESPONSE ANREDE_KZ ALTERSKATEGORIE_GROB  \n",
       "0         3        0         2                    4  \n",
       "1         1        0         2                    3  \n",
       "2         3        0         1                    4  \n",
       "3         3        0         2                    4  \n",
       "4         3        0         1                    3  \n",
       "\n",
       "[5 rows x 367 columns]"
      ]
     },
     "execution_count": 86,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_train.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 87,
   "id": "720cbcc0-fe76-44fc-a745-683a5b498261",
   "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>LNR</th>\n",
       "      <th>AGER_TYP</th>\n",
       "      <th>AKT_DAT_KL</th>\n",
       "      <th>ALTER_HH</th>\n",
       "      <th>ALTER_KIND1</th>\n",
       "      <th>ALTER_KIND2</th>\n",
       "      <th>ALTER_KIND3</th>\n",
       "      <th>ALTER_KIND4</th>\n",
       "      <th>ALTERSKATEGORIE_FEIN</th>\n",
       "      <th>ANZ_HAUSHALTE_AKTIV</th>\n",
       "      <th>...</th>\n",
       "      <th>VHN</th>\n",
       "      <th>VK_DHT4A</th>\n",
       "      <th>VK_DISTANZ</th>\n",
       "      <th>VK_ZG11</th>\n",
       "      <th>W_KEIT_KIND_HH</th>\n",
       "      <th>WOHNDAUER_2008</th>\n",
       "      <th>WOHNLAGE</th>\n",
       "      <th>ZABEOTYP</th>\n",
       "      <th>ANREDE_KZ</th>\n",
       "      <th>ALTERSKATEGORIE_GROB</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1754</td>\n",
       "      <td>2</td>\n",
       "      <td>1.0</td>\n",
       "      <td>7.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>6.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>...</td>\n",
       "      <td>4.0</td>\n",
       "      <td>5.0</td>\n",
       "      <td>6.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>6.0</td>\n",
       "      <td>9.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>3</td>\n",
       "      <td>1</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1770</td>\n",
       "      <td>-1</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.0</td>\n",
       "      <td>20.0</td>\n",
       "      <td>...</td>\n",
       "      <td>1.0</td>\n",
       "      <td>5.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>6.0</td>\n",
       "      <td>9.0</td>\n",
       "      <td>5.0</td>\n",
       "      <td>3</td>\n",
       "      <td>1</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1465</td>\n",
       "      <td>2</td>\n",
       "      <td>9.0</td>\n",
       "      <td>16.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>11.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>...</td>\n",
       "      <td>3.0</td>\n",
       "      <td>9.0</td>\n",
       "      <td>6.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>9.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>3</td>\n",
       "      <td>2</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1470</td>\n",
       "      <td>-1</td>\n",
       "      <td>7.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>...</td>\n",
       "      <td>2.0</td>\n",
       "      <td>6.0</td>\n",
       "      <td>6.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>9.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>3</td>\n",
       "      <td>2</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1478</td>\n",
       "      <td>1</td>\n",
       "      <td>1.0</td>\n",
       "      <td>21.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>13.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>...</td>\n",
       "      <td>1.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>9.0</td>\n",
       "      <td>7.0</td>\n",
       "      <td>4</td>\n",
       "      <td>2</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 366 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "    LNR  AGER_TYP  AKT_DAT_KL  ALTER_HH  ALTER_KIND1  ALTER_KIND2  \\\n",
       "0  1754         2         1.0       7.0          NaN          NaN   \n",
       "1  1770        -1         1.0       0.0          NaN          NaN   \n",
       "2  1465         2         9.0      16.0          NaN          NaN   \n",
       "3  1470        -1         7.0       0.0          NaN          NaN   \n",
       "4  1478         1         1.0      21.0          NaN          NaN   \n",
       "\n",
       "   ALTER_KIND3  ALTER_KIND4  ALTERSKATEGORIE_FEIN  ANZ_HAUSHALTE_AKTIV  ...  \\\n",
       "0          NaN          NaN                   6.0                  2.0  ...   \n",
       "1          NaN          NaN                   0.0                 20.0  ...   \n",
       "2          NaN          NaN                  11.0                  2.0  ...   \n",
       "3          NaN          NaN                   0.0                  1.0  ...   \n",
       "4          NaN          NaN                  13.0                  1.0  ...   \n",
       "\n",
       "   VHN  VK_DHT4A  VK_DISTANZ  VK_ZG11  W_KEIT_KIND_HH  WOHNDAUER_2008  \\\n",
       "0  4.0       5.0         6.0      3.0             6.0             9.0   \n",
       "1  1.0       5.0         2.0      1.0             6.0             9.0   \n",
       "2  3.0       9.0         6.0      3.0             2.0             9.0   \n",
       "3  2.0       6.0         6.0      3.0             NaN             9.0   \n",
       "4  1.0       2.0         4.0      3.0             3.0             9.0   \n",
       "\n",
       "   WOHNLAGE ZABEOTYP ANREDE_KZ ALTERSKATEGORIE_GROB  \n",
       "0       3.0        3         1                    4  \n",
       "1       5.0        3         1                    4  \n",
       "2       4.0        3         2                    4  \n",
       "3       2.0        3         2                    4  \n",
       "4       7.0        4         2                    4  \n",
       "\n",
       "[5 rows x 366 columns]"
      ]
     },
     "execution_count": 87,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_test.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 88,
   "id": "1699332e-2d73-494e-bdc4-cb26226eef2e",
   "metadata": {},
   "outputs": [],
   "source": [
    "# applying all cleaning functions to train and test datasets\n",
    "df_train = replace_wrong_values(df_train, error_columns, ['X', 'XX'], -1)\n",
    "df_test = replace_wrong_values(df_test, error_columns, ['X', 'XX'], -1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 89,
   "id": "74e1e260-8496-42b2-b301-d2b236467a87",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_train = replace_NaNs_with_unknown(df_train, data_att)\n",
    "df_test = replace_NaNs_with_unknown(df_test, data_att)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 90,
   "id": "69df63ff-9957-461d-93d2-98dd7b7d2c11",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Dropping 7 columns for having over 40% of null values\n",
      "Imputting fixed values to 11 columns.\n",
      "\n",
      "Dropping 7 columns for having over 40% of null values\n",
      "Imputting fixed values to 11 columns.\n",
      "\n"
     ]
    }
   ],
   "source": [
    "df_train = handle_missing_data_cols(df_train, cols_to_drop, cols_to_input_zero)\n",
    "df_test = handle_missing_data_cols(df_test, cols_to_drop, cols_to_input_zero)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 91,
   "id": "19584e39-05f2-4ef4-9b0a-572ca8a2a982",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Imputting most frequent values to remaining 61 columns\n",
      "This might take a while...\n",
      "\n",
      "Imputting most frequent values to remaining 61 columns\n",
      "This might take a while...\n",
      "\n"
     ]
    }
   ],
   "source": [
    "df_train = make_imputations(df_train)\n",
    "df_test = make_imputations(df_test)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 92,
   "id": "49e83181-df22-4fef-afcd-06395bf9dbba",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Dropping 8 columns.\n",
      "Changing values to 10 on 1 column.\n",
      "Changing values to 5 on 1 column.\n",
      "Changing values to group common entries on 1 column.\n",
      "Dropping 8 columns.\n",
      "Changing values to 10 on 1 column.\n",
      "Changing values to 5 on 1 column.\n",
      "Changing values to group common entries on 1 column.\n"
     ]
    }
   ],
   "source": [
    "df_train = more_data_cleaning(df_train)\n",
    "df_test = more_data_cleaning(df_test)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 93,
   "id": "4928de93-50f7-43c1-a324-4ade04ab708e",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_train = fix_binary_cols(df_train)\n",
    "df_test = fix_binary_cols(df_test)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 94,
   "id": "04781349-b355-434e-abef-57d407c4e50c",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_train = create_decade_movement(df_train)\n",
    "df_test = create_decade_movement(df_test)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 95,
   "id": "409a8f7b-9ea2-462d-8f50-e13198ed30fb",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_train = change_cols_dtypes(df_train)\n",
    "df_test = change_cols_dtypes(df_test)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 96,
   "id": "1d9502ee-b717-4ac9-a51f-882553ac785a",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_train = drop_high_corr_cols(df_train, high_corr_cols)\n",
    "df_test = drop_high_corr_cols(df_test, high_corr_cols)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 97,
   "id": "511bd808-fda8-4c01-a636-de140048752a",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_train = drop_id_col(df_train)\n",
    "df_test = drop_id_col(df_test)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 98,
   "id": "3301f85e-5903-42a1-843a-6a23b7ecd5d9",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Final AZDIAS:     (222805, 309)\n",
      "Final CUSTOMERS:  (47913, 309)\n",
      "Final TRAIN:      (42962, 310)\n",
      "Final TEST:       (42833, 309)\n",
      "\n",
      "----> All datasets were exported successfully!\n"
     ]
    }
   ],
   "source": [
    "# final datasets\n",
    "print('Final AZDIAS:    ',azdias.shape)\n",
    "print('Final CUSTOMERS: ',customers.shape)\n",
    "print('Final TRAIN:     ',df_train.shape)\n",
    "print('Final TEST:      ',df_test.shape)\n",
    "\n",
    "azdias.to_csv('data/clean_AZDIAS.csv')\n",
    "customers.to_csv('data/clean_CUSTOMERS.csv')\n",
    "df_train.to_csv('data/clean_TRAIN.csv')\n",
    "df_test.to_csv('data/clean_TEST.csv')\n",
    "\n",
    "print('\\n----> All datasets were exported successfully!')"
   ]
  }
 ],
 "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.8.8"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}