Data Cleaning.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Data Assesing "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "# import libraries here; add more as necessary\n",
    "import numpy as np\n",
    "import pandas as pd\n",
    "import matplotlib.pyplot as plt\n",
    "import seaborn as sns\n",
    "from tools import*\n",
    "# magic word for producing visualizations in notebook\n",
    "%matplotlib inline"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/home/shapiro/anaconda3/lib/python3.7/site-packages/IPython/core/interactiveshell.py:3146: DtypeWarning: Columns (18,19) have mixed types.Specify dtype option on import or set low_memory=False.\n",
      "  interactivity=interactivity, compiler=compiler, result=result)\n"
     ]
    }
   ],
   "source": [
    "azdias = pd.read_csv('Udacity_AZDIAS_052018.csv', sep=';')\n",
    "customers = pd.read_csv('Udacity_CUSTOMERS_052018.csv', sep=';')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Lets see the shape of our dataframes first"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Population contains 891221 with 366 feature\n"
     ]
    }
   ],
   "source": [
    "print(\"Population contains {} with {} feature\".format(azdias.shape[0],azdias.shape[1]))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Costumers contains 191652 with 369 feature\n"
     ]
    }
   ],
   "source": [
    "print(\"Costumers contains {} with {} feature\".format(customers.shape[0],customers.shape[1]))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We can see from above that customers contains 3 more features than the population , let's check them out "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "{'CUSTOMER_GROUP', 'ONLINE_PURCHASE', 'PRODUCT_GROUP'}"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "customers_spec_cols=set(customers.columns) - set(azdias.columns)\n",
    "customers_spec_cols #specific columns only for customers"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "It makes sense , since they're already customers they should have some features with respect to the company "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [],
   "source": [
    "#loading the description excel sheet\n",
    "# here we are making a multi index of of the value and the name of the column \n",
    "description_full = pd.read_excel(\"DIAS Attributes - Values 2017.xlsx\",sheet_name=\"Tabelle1\",header=1,index_col=[1],keep_default_na=False)\n",
    "desc=description_full.copy()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Let's take a look at the missing values in both data sets "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Looking at missing values in both the population and the customers data may give us some intuition about data that company may consider valuable to save about the customer"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "But we have a problem we should solve first, according to the excel sheet that describing the features of both data sets , we can see that missing values in some columns are encoding as either 0 , -1 not NaN , so we should construct of a dictionary containg the column name as a key and value as missing value encoding"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "For example , if column `x` in both the customers and population dataset is missing more than of 80% of it's values it doesn't seem to be important"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "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>Unnamed: 0</th>\n",
       "      <th>Description</th>\n",
       "      <th>Value</th>\n",
       "      <th>Meaning</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Attribute</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>AGER_TYP</th>\n",
       "      <td></td>\n",
       "      <td>best-ager typology</td>\n",
       "      <td>-1</td>\n",
       "      <td>unknown</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>ALTERSKATEGORIE_GROB</th>\n",
       "      <td></td>\n",
       "      <td>age classification through prename analysis</td>\n",
       "      <td>-1, 0</td>\n",
       "      <td>unknown</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>ALTER_HH</th>\n",
       "      <td></td>\n",
       "      <td>main age within the household</td>\n",
       "      <td>0</td>\n",
       "      <td>unknown / no main age detectable</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>ANREDE_KZ</th>\n",
       "      <td></td>\n",
       "      <td>gender</td>\n",
       "      <td>-1, 0</td>\n",
       "      <td>unknown</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>BALLRAUM</th>\n",
       "      <td></td>\n",
       "      <td>distance to next urban centre</td>\n",
       "      <td>-1</td>\n",
       "      <td>unknown</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>WOHNDAUER_2008</th>\n",
       "      <td></td>\n",
       "      <td>length of residence</td>\n",
       "      <td>-1, 0</td>\n",
       "      <td>unknown</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>WOHNLAGE</th>\n",
       "      <td></td>\n",
       "      <td>residential-area</td>\n",
       "      <td>-1</td>\n",
       "      <td>unknown</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>WACHSTUMSGEBIET_NB</th>\n",
       "      <td></td>\n",
       "      <td>growing area (population growth in the last 5 ...</td>\n",
       "      <td>-1, 0</td>\n",
       "      <td>unknown</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>W_KEIT_KIND_HH</th>\n",
       "      <td></td>\n",
       "      <td>likelihood of a child present in this household</td>\n",
       "      <td>-1, 0</td>\n",
       "      <td>unknown</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>ZABEOTYP</th>\n",
       "      <td></td>\n",
       "      <td>typification of energy consumers</td>\n",
       "      <td>-1, 9</td>\n",
       "      <td>unknown</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>233 rows × 4 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                     Unnamed: 0  \\\n",
       "Attribute                         \n",
       "AGER_TYP                          \n",
       "ALTERSKATEGORIE_GROB              \n",
       "ALTER_HH                          \n",
       "ANREDE_KZ                         \n",
       "BALLRAUM                          \n",
       "...                         ...   \n",
       "WOHNDAUER_2008                    \n",
       "WOHNLAGE                          \n",
       "WACHSTUMSGEBIET_NB                \n",
       "W_KEIT_KIND_HH                    \n",
       "ZABEOTYP                          \n",
       "\n",
       "                                                            Description  \\\n",
       "Attribute                                                                 \n",
       "AGER_TYP                                             best-ager typology   \n",
       "ALTERSKATEGORIE_GROB       age classification through prename analysis    \n",
       "ALTER_HH                                  main age within the household   \n",
       "ANREDE_KZ                                                        gender   \n",
       "BALLRAUM                                 distance to next urban centre    \n",
       "...                                                                 ...   \n",
       "WOHNDAUER_2008                                      length of residence   \n",
       "WOHNLAGE                                              residential-area    \n",
       "WACHSTUMSGEBIET_NB    growing area (population growth in the last 5 ...   \n",
       "W_KEIT_KIND_HH          likelihood of a child present in this household   \n",
       "ZABEOTYP                               typification of energy consumers   \n",
       "\n",
       "                      Value                           Meaning  \n",
       "Attribute                                                      \n",
       "AGER_TYP                 -1                           unknown  \n",
       "ALTERSKATEGORIE_GROB  -1, 0                           unknown  \n",
       "ALTER_HH                  0  unknown / no main age detectable  \n",
       "ANREDE_KZ             -1, 0                           unknown  \n",
       "BALLRAUM                 -1                           unknown  \n",
       "...                     ...                               ...  \n",
       "WOHNDAUER_2008        -1, 0                           unknown  \n",
       "WOHNLAGE                 -1                           unknown  \n",
       "WACHSTUMSGEBIET_NB    -1, 0                           unknown  \n",
       "W_KEIT_KIND_HH        -1, 0                           unknown  \n",
       "ZABEOTYP              -1, 9                           unknown  \n",
       "\n",
       "[233 rows x 4 columns]"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "desc=desc[desc.Meaning.str.match('unknown')] # now we filtered to only values who has the word known/unkown is it's meaning \n",
    "desc"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We can see 294 rows with 294 attributes and thier missing values , but let's do further checking to see if thier any duplicate values "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "unknown                             232\n",
       "unknown / no main age detectable      1\n",
       "Name: Meaning, dtype: int64"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "desc.Meaning.value_counts() "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "As we can see above the all of the meanings refere that there's actually missing value , whether its unmeasurable or people refused to respond or even don't have it"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "So let's make our dictionary "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [],
   "source": [
    "uk_encodings=desc.reset_index().set_index('Attribute')['Value'].to_dict() #getting the encodings"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "233"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "len(uk_encodings)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "33492923"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "azdias.isna().sum().sum()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Let's apply them to the data set "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [],
   "source": [
    "for col in azdias.columns:\n",
    "    if col in uk_encodings.keys():\n",
    "        azdias[col].replace(uk_encodings.get(col),np.nan,inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "34740782"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "azdias.isna().sum().sum()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "As we can see above the value of the NaN increase by approximatly 13,000"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "2445"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "import gc #garbage collector for memory \n",
    "gc.enable() \n",
    "gc.collect() "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pickle as pk \n",
    "pk.dump(uk_encodings,open(\"Uknown_Encodings.pkl\",\"wb\")) #for later loading in the testing and training data set "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Let's do this to the customers dataframe as well "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Before 13864522 , After 14125750\n"
     ]
    }
   ],
   "source": [
    "cust_nan_before=customers.isna().sum().sum()\n",
    "for col in customers.columns:\n",
    "    if col in uk_encodings.keys():\n",
    "        customers[col].replace(uk_encodings.get(col),np.nan,inplace=True)\n",
    "        \n",
    "cust_nan_after=customers.isna().sum().sum()\n",
    "\n",
    "print(\"Before {} , After {}\".format(cust_nan_before,cust_nan_after))\n",
    "\n",
    "cust_nan_after=cust_nan_before=None"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Instead of Explicit NaNs , we may have some outliers in ourdata set"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "136"
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "different_columns_union=list(set(set(azdias.columns)-(set(description_full.index))).union(set(description_full.index)-(set(azdias.columns))))\n",
    "len(different_columns_union)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "94"
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "different_columns_azdias=set(set(azdias.columns)-(set(description_full.index)))\n",
    "len(different_columns_azdias)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We can see from above that we have **94** feature in azdias that are not in the description sheet\n",
    "and **136** feature in total that are in azdias and not in description or vice versa"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Due to above , we will only work with the intersection between both to identify potential outliers"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "272"
      ]
     },
     "execution_count": 19,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "inter_cols=list(set(azdias.columns).intersection(set(description_full.index)))\n",
    "len(inter_cols)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Since the above columns are'nt mentioned in the information dataset , the final decision of whether to drop them or not will be according to the percentage of thier missing values so we can check them later"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Column UMFELD_JUNG\n",
      "Values that cannot be parsed to Float []\n",
      "NaNs = 97786\n",
      "Sample of values = [4.0, 3.0, 5.0, 2.0, 1.0]\n",
      "Propotion  of NaNs = 0.10972138223852446\n",
      "==========================================\n",
      "Column RT_KEIN_ANREIZ\n",
      "Values that cannot be parsed to Float []\n",
      "NaNs = 4854\n",
      "Sample of values = [4.0, 2.0, 5.0, 3.0, 1.0]\n",
      "Propotion  of NaNs = 0.005446460529992\n",
      "==========================================\n",
      "Column D19_RATGEBER\n",
      "Values that cannot be parsed to Float []\n",
      "NaNs = 0\n",
      "Sample of values = [4, 3, 6, 7, 1]\n",
      "Propotion  of NaNs = 0.0\n",
      "==========================================\n",
      "Column EINGEZOGENAM_HH_JAHR\n",
      "Values that cannot be parsed to Float []\n",
      "NaNs = 73499\n",
      "Sample of values = [2011.0, 2017.0, 2005.0, 2014.0, 2003.0]\n",
      "Propotion  of NaNs = 0.08247000463409188\n",
      "==========================================\n",
      "Column D19_SCHUHE\n",
      "Values that cannot be parsed to Float []\n",
      "NaNs = 0\n",
      "Sample of values = [2, 6, 3, 0, 7]\n",
      "Propotion  of NaNs = 0.0\n",
      "==========================================\n",
      "Column D19_WEIN_FEINKOST\n",
      "Values that cannot be parsed to Float []\n",
      "NaNs = 0\n",
      "Sample of values = [4, 5, 0, 3, 1]\n",
      "Propotion  of NaNs = 0.0\n",
      "==========================================\n",
      "Column D19_HAUS_DEKO\n",
      "Values that cannot be parsed to Float []\n",
      "NaNs = 0\n",
      "Sample of values = [1, 7, 3, 0, 5]\n",
      "Propotion  of NaNs = 0.0\n",
      "==========================================\n",
      "Column KBA13_ANTG3\n",
      "Values that cannot be parsed to Float []\n",
      "NaNs = 105800\n",
      "Sample of values = [3.0, 2.0, 0.0, 1.0]\n",
      "Propotion  of NaNs = 0.11871354018812394\n",
      "==========================================\n",
      "Column D19_KOSMETIK\n",
      "Values that cannot be parsed to Float []\n",
      "NaNs = 0\n",
      "Sample of values = [1, 2, 6, 3]\n",
      "Propotion  of NaNs = 0.0\n",
      "==========================================\n",
      "Column EXTSEL992\n",
      "Values that cannot be parsed to Float []\n",
      "NaNs = 654153\n",
      "Sample of values = [52.0, 42.0, 10.0, 46.0]\n",
      "Propotion  of NaNs = 0.7339963937115486\n",
      "==========================================\n",
      "Column D19_VERSICHERUNGEN\n",
      "Values that cannot be parsed to Float []\n",
      "NaNs = 0\n",
      "Sample of values = [0, 7, 6, 5]\n",
      "Propotion  of NaNs = 0.0\n",
      "==========================================\n",
      "Column D19_VERSI_ONLINE_DATUM\n",
      "Values that cannot be parsed to Float []\n",
      "NaNs = 0\n",
      "Sample of values = [9, 6, 10, 8]\n",
      "Propotion  of NaNs = 0.0\n",
      "==========================================\n",
      "Column D19_ENERGIE\n",
      "Values that cannot be parsed to Float []\n",
      "NaNs = 0\n",
      "Sample of values = [6, 2, 0, 1]\n",
      "Propotion  of NaNs = 0.0\n",
      "==========================================\n",
      "Column UMFELD_ALT\n",
      "Values that cannot be parsed to Float []\n",
      "NaNs = 97786\n",
      "Sample of values = [4.0, 2.0, 3.0, 5.0]\n",
      "Propotion  of NaNs = 0.10972138223852446\n",
      "==========================================\n",
      "Column ALTER_KIND3\n",
      "Values that cannot be parsed to Float []\n",
      "NaNs = 885051\n",
      "Sample of values = [7.0, 18.0, 8.0, 9.0]\n",
      "Propotion  of NaNs = 0.9930769135826019\n",
      "==========================================\n",
      "Column D19_NAHRUNGSERGAENZUNG\n",
      "Values that cannot be parsed to Float []\n",
      "NaNs = 0\n",
      "Sample of values = [4, 0, 3, 1]\n",
      "Propotion  of NaNs = 0.0\n",
      "==========================================\n",
      "Column CJT_TYP_6\n",
      "Values that cannot be parsed to Float []\n",
      "NaNs = 4854\n",
      "Sample of values = [5.0, 1.0, 4.0, 2.0]\n",
      "Propotion  of NaNs = 0.005446460529992\n",
      "==========================================\n",
      "Column D19_BANKEN_DIREKT\n",
      "Values that cannot be parsed to Float []\n",
      "NaNs = 0\n",
      "Sample of values = [2, 1, 7, 5]\n",
      "Propotion  of NaNs = 0.0\n",
      "==========================================\n",
      "Column ANZ_STATISTISCHE_HAUSHALTE\n",
      "Values that cannot be parsed to Float []\n",
      "NaNs = 93148\n",
      "Sample of values = [94.0, 129.0, 174.0, 156.0]\n",
      "Propotion  of NaNs = 0.10451728583594866\n",
      "==========================================\n",
      "Column D19_REISEN\n",
      "Values that cannot be parsed to Float []\n",
      "NaNs = 0\n",
      "Sample of values = [5, 4, 2, 1]\n",
      "Propotion  of NaNs = 0.0\n",
      "==========================================\n",
      "Column UNGLEICHENN_FLAG\n",
      "Values that cannot be parsed to Float []\n",
      "NaNs = 73499\n",
      "Sample of values = [0.0, 1.0]\n",
      "Propotion  of NaNs = 0.08247000463409188\n",
      "==========================================\n",
      "Column D19_BUCH_CD\n",
      "Values that cannot be parsed to Float []\n",
      "NaNs = 0\n",
      "Sample of values = [1, 6]\n",
      "Propotion  of NaNs = 0.0\n",
      "==========================================\n",
      "Column RT_SCHNAEPPCHEN\n",
      "Values that cannot be parsed to Float []\n",
      "NaNs = 4854\n",
      "Sample of values = [5.0, 2.0]\n",
      "Propotion  of NaNs = 0.005446460529992\n",
      "==========================================\n",
      "Column KBA13_ANTG1\n",
      "Values that cannot be parsed to Float []\n",
      "NaNs = 105800\n",
      "Sample of values = [2.0, 3.0]\n",
      "Propotion  of NaNs = 0.11871354018812394\n",
      "==========================================\n",
      "Column D19_KONSUMTYP_MAX\n",
      "Values that cannot be parsed to Float []\n",
      "NaNs = 0\n",
      "Sample of values = [3, 2]\n",
      "Propotion  of NaNs = 0.0\n",
      "==========================================\n",
      "Column D19_BEKLEIDUNG_REST\n",
      "Values that cannot be parsed to Float []\n",
      "NaNs = 0\n",
      "Sample of values = [5, 4]\n",
      "Propotion  of NaNs = 0.0\n",
      "==========================================\n",
      "Column D19_VERSI_ONLINE_QUOTE_12\n",
      "Values that cannot be parsed to Float []\n",
      "NaNs = 257113\n",
      "Sample of values = [0.0, 5.0]\n",
      "Propotion  of NaNs = 0.2884952217239046\n",
      "==========================================\n",
      "Column KOMBIALTER\n",
      "Values that cannot be parsed to Float []\n",
      "NaNs = 0\n",
      "Sample of values = [3, 2]\n",
      "Propotion  of NaNs = 0.0\n",
      "==========================================\n",
      "Column KBA13_HHZ\n",
      "Values that cannot be parsed to Float []\n",
      "NaNs = 105800\n",
      "Sample of values = [4.0, 2.0]\n",
      "Propotion  of NaNs = 0.11871354018812394\n",
      "==========================================\n",
      "Column EINGEFUEGT_AM\n",
      "Values that cannot be parsed to Float ['2013-11-11 00:00:00', '2014-05-09 00:00:00', '1994-03-29 00:00:00', '2009-07-02 00:00:00', '2009-05-22 00:00:00', '2011-02-16 00:00:00', '2001-04-19 00:00:00', '1994-07-18 00:00:00', '2013-08-21 00:00:00', '2009-01-16 00:00:00']\n",
      "NaNs = 93148\n",
      "Sample of values = ['2002-08-14 00:00:00', '2000-06-28 00:00:00']\n",
      "Propotion  of NaNs = 0.10451728583594866\n",
      "==========================================\n",
      "Column SOHO_KZ\n",
      "Values that cannot be parsed to Float []\n",
      "NaNs = 73499\n",
      "Sample of values = [0.0, 1.0]\n",
      "Propotion  of NaNs = 0.08247000463409188\n",
      "==========================================\n",
      "Column D19_TECHNIK\n",
      "Values that cannot be parsed to Float []\n",
      "NaNs = 0\n",
      "Sample of values = [4, 1]\n",
      "Propotion  of NaNs = 0.0\n",
      "==========================================\n",
      "Column D19_VERSAND_REST\n",
      "Values that cannot be parsed to Float []\n",
      "NaNs = 0\n",
      "Sample of values = [6, 1]\n",
      "Propotion  of NaNs = 0.0\n",
      "==========================================\n",
      "Column D19_VERSI_OFFLINE_DATUM\n",
      "Values that cannot be parsed to Float []\n",
      "NaNs = 0\n",
      "Sample of values = [2, 10]\n",
      "Propotion  of NaNs = 0.0\n",
      "==========================================\n",
      "Column STRUKTURTYP\n",
      "Values that cannot be parsed to Float []\n",
      "NaNs = 97274\n",
      "Sample of values = [2.0, 1.0]\n",
      "Propotion  of NaNs = 0.1091468894920564\n",
      "==========================================\n",
      "Column ALTER_KIND2\n",
      "Values that cannot be parsed to Float []\n",
      "NaNs = 861722\n",
      "Sample of values = [3.0, 14.0]\n",
      "Propotion  of NaNs = 0.9669004657655059\n",
      "==========================================\n",
      "Column D19_BANKEN_GROSS\n",
      "Values that cannot be parsed to Float []\n",
      "NaNs = 0\n",
      "Sample of values = [5, 0]\n",
      "Propotion  of NaNs = 0.0\n",
      "==========================================\n",
      "Column D19_TELKO_MOBILE\n",
      "Values that cannot be parsed to Float []\n",
      "NaNs = 0\n",
      "Sample of values = [0, 3]\n",
      "Propotion  of NaNs = 0.0\n",
      "==========================================\n",
      "Column D19_BEKLEIDUNG_GEH\n",
      "Values that cannot be parsed to Float []\n",
      "NaNs = 0\n",
      "Sample of values = [7, 6]\n",
      "Propotion  of NaNs = 0.0\n",
      "==========================================\n",
      "Column D19_TIERARTIKEL\n",
      "Values that cannot be parsed to Float []\n",
      "NaNs = 0\n",
      "Sample of values = [4, 2]\n",
      "Propotion  of NaNs = 0.0\n",
      "==========================================\n",
      "Column D19_FREIZEIT\n",
      "Values that cannot be parsed to Float []\n",
      "NaNs = 0\n",
      "Sample of values = [1, 3]\n",
      "Propotion  of NaNs = 0.0\n",
      "==========================================\n",
      "Column D19_BIO_OEKO\n",
      "Values that cannot be parsed to Float []\n",
      "NaNs = 0\n",
      "Sample of values = [0, 7]\n",
      "Propotion  of NaNs = 0.0\n",
      "==========================================\n",
      "Column D19_BANKEN_LOKAL\n",
      "Values that cannot be parsed to Float []\n",
      "NaNs = 0\n",
      "Sample of values = [5, 6]\n",
      "Propotion  of NaNs = 0.0\n",
      "==========================================\n",
      "Column FIRMENDICHTE\n",
      "Values that cannot be parsed to Float []\n",
      "NaNs = 93155\n",
      "Sample of values = [5.0, 4.0]\n",
      "Propotion  of NaNs = 0.10452514022896678\n",
      "==========================================\n",
      "Column KBA13_GBZ\n",
      "Values that cannot be parsed to Float []\n",
      "NaNs = 105800\n",
      "Sample of values = [3.0, 5.0]\n",
      "Propotion  of NaNs = 0.11871354018812394\n",
      "==========================================\n",
      "Column KBA13_ANTG4\n",
      "Values that cannot be parsed to Float []\n",
      "NaNs = 105800\n",
      "Sample of values = [1.0, 2.0]\n",
      "Propotion  of NaNs = 0.11871354018812394\n",
      "==========================================\n",
      "Column D19_TELKO_ONLINE_QUOTE_12\n",
      "Values that cannot be parsed to Float []\n",
      "NaNs = 257113\n",
      "Sample of values = [7.0, 0.0]\n",
      "Propotion  of NaNs = 0.2884952217239046\n",
      "==========================================\n",
      "Column VK_DHT4A\n",
      "Values that cannot be parsed to Float []\n",
      "NaNs = 75917\n",
      "Sample of values = [7.0, 8.0]\n",
      "Propotion  of NaNs = 0.08518313639377888\n",
      "==========================================\n",
      "Column VK_ZG11\n",
      "Values that cannot be parsed to Float []\n",
      "NaNs = 75917\n",
      "Sample of values = [6.0, 3.0]\n",
      "Propotion  of NaNs = 0.08518313639377888\n",
      "==========================================\n",
      "Column D19_BANKEN_REST\n",
      "Values that cannot be parsed to Float []\n",
      "NaNs = 0\n",
      "Sample of values = [2, 0]\n",
      "Propotion  of NaNs = 0.0\n",
      "==========================================\n",
      "Column RT_UEBERGROESSE\n",
      "Values that cannot be parsed to Float []\n",
      "NaNs = 51226\n",
      "Sample of values = [0.0, 2.0]\n",
      "Propotion  of NaNs = 0.05747844810658636\n",
      "==========================================\n",
      "Column CJT_TYP_4\n",
      "Values that cannot be parsed to Float []\n",
      "NaNs = 4854\n",
      "Sample of values = [4.0, 2.0]\n",
      "Propotion  of NaNs = 0.005446460529992\n",
      "==========================================\n",
      "Column ALTERSKATEGORIE_FEIN\n",
      "Values that cannot be parsed to Float []\n",
      "NaNs = 262947\n",
      "Sample of values = [24.0, 10.0]\n",
      "Propotion  of NaNs = 0.29504129727643313\n",
      "==========================================\n",
      "Column D19_BILDUNG\n",
      "Values that cannot be parsed to Float []\n",
      "NaNs = 0\n",
      "Sample of values = [2, 0]\n",
      "Propotion  of NaNs = 0.0\n",
      "==========================================\n",
      "Column D19_DIGIT_SERV\n",
      "Values that cannot be parsed to Float []\n",
      "NaNs = 0\n",
      "Sample of values = [5, 2]\n",
      "Propotion  of NaNs = 0.0\n",
      "==========================================\n",
      "Column CJT_TYP_1\n",
      "Values that cannot be parsed to Float []\n",
      "NaNs = 4854\n",
      "Sample of values = [4.0, 2.0]\n",
      "Propotion  of NaNs = 0.005446460529992\n",
      "==========================================\n",
      "Column CJT_TYP_2\n",
      "Values that cannot be parsed to Float []\n",
      "NaNs = 4854\n",
      "Sample of values = [2.0, 1.0]\n",
      "Propotion  of NaNs = 0.005446460529992\n",
      "==========================================\n",
      "Column D19_DROGERIEARTIKEL\n",
      "Values that cannot be parsed to Float []\n",
      "NaNs = 0\n",
      "Sample of values = [0, 4]\n",
      "Propotion  of NaNs = 0.0\n",
      "==========================================\n",
      "Column D19_SONSTIGE\n",
      "Values that cannot be parsed to Float []\n",
      "NaNs = 0\n",
      "Sample of values = [6, 0]\n",
      "Propotion  of NaNs = 0.0\n",
      "==========================================\n",
      "Column ALTER_KIND1\n",
      "Values that cannot be parsed to Float []\n",
      "NaNs = 810163\n",
      "Sample of values = [5.0, 11.0]\n",
      "Propotion  of NaNs = 0.9090483729624863\n",
      "==========================================\n",
      "Column KBA13_BAUMAX\n",
      "Values that cannot be parsed to Float []\n",
      "NaNs = 105800\n",
      "Sample of values = [2.0, 5.0]\n",
      "Propotion  of NaNs = 0.11871354018812394\n",
      "==========================================\n",
      "Column D19_GARTEN\n",
      "Values that cannot be parsed to Float []\n",
      "NaNs = 0\n",
      "Sample of values = [3, 5]\n",
      "Propotion  of NaNs = 0.0\n",
      "==========================================\n",
      "Column KBA13_ANTG2\n",
      "Values that cannot be parsed to Float []\n",
      "NaNs = 105800\n",
      "Sample of values = [3.0, 4.0]\n",
      "Propotion  of NaNs = 0.11871354018812394\n",
      "==========================================\n",
      "Column CJT_KATALOGNUTZER\n",
      "Values that cannot be parsed to Float []\n",
      "NaNs = 4854\n",
      "Sample of values = [1.0, 4.0]\n",
      "Propotion  of NaNs = 0.005446460529992\n",
      "==========================================\n",
      "Column CJT_TYP_3\n",
      "Values that cannot be parsed to Float []\n",
      "NaNs = 4854\n",
      "Sample of values = [2.0, 4.0]\n",
      "Propotion  of NaNs = 0.005446460529992\n",
      "==========================================\n",
      "Column D19_VOLLSORTIMENT\n",
      "Values that cannot be parsed to Float []\n",
      "NaNs = 0\n",
      "Sample of values = [6, 2]\n",
      "Propotion  of NaNs = 0.0\n",
      "==========================================\n",
      "Column D19_LETZTER_KAUF_BRANCHE\n",
      "Values that cannot be parsed to Float ['D19_RATGEBER', 'D19_SCHUHE', 'D19_HAUS_DEKO', 'D19_WEIN_FEINKOST', 'D19_BANKEN_REST', 'D19_KOSMETIK', 'D19_VERSICHERUNGEN', 'D19_BILDUNG', 'D19_DIGIT_SERV', 'D19_ENERGIE']\n",
      "NaNs = 257113\n",
      "Sample of values = ['D19_REISEN', 'D19_HAUS_DEKO']\n",
      "Propotion  of NaNs = 0.2884952217239046\n",
      "==========================================\n",
      "Column D19_LEBENSMITTEL\n",
      "Values that cannot be parsed to Float []\n",
      "NaNs = 0\n",
      "Sample of values = [5, 6]\n",
      "Propotion  of NaNs = 0.0\n",
      "==========================================\n",
      "Column D19_LOTTO\n",
      "Values that cannot be parsed to Float []\n",
      "NaNs = 257113\n",
      "Sample of values = [5.0, 4.0]\n",
      "Propotion  of NaNs = 0.2884952217239046\n",
      "==========================================\n",
      "Column D19_SAMMELARTIKEL\n",
      "Values that cannot be parsed to Float []\n",
      "NaNs = 0\n",
      "Sample of values = [0, 7]\n",
      "Propotion  of NaNs = 0.0\n",
      "==========================================\n",
      "Column CAMEO_INTL_2015\n",
      "Values that cannot be parsed to Float ['XX']\n",
      "NaNs = 98979\n",
      "Sample of values = [15.0, 55.0]\n",
      "Propotion  of NaNs = 0.11105999522004081\n",
      "==========================================\n",
      "Column D19_VERSI_DATUM\n",
      "Values that cannot be parsed to Float []\n",
      "NaNs = 0\n",
      "Sample of values = [4, 1]\n",
      "Propotion  of NaNs = 0.0\n",
      "==========================================\n",
      "Column D19_HANDWERK\n",
      "Values that cannot be parsed to Float []\n",
      "NaNs = 0\n",
      "Sample of values = [7, 1]\n",
      "Propotion  of NaNs = 0.0\n",
      "==========================================\n",
      "Column ARBEIT\n",
      "Values that cannot be parsed to Float []\n",
      "NaNs = 97216\n",
      "Sample of values = [5.0, 1.0]\n",
      "Propotion  of NaNs = 0.10908181023562057\n",
      "==========================================\n",
      "Column VK_DISTANZ\n",
      "Values that cannot be parsed to Float []\n",
      "NaNs = 75917\n",
      "Sample of values = [2.0, 8.0]\n",
      "Propotion  of NaNs = 0.08518313639377888\n",
      "==========================================\n",
      "Column VHA\n",
      "Values that cannot be parsed to Float []\n",
      "NaNs = 73499\n",
      "Sample of values = [0.0, 3.0]\n",
      "Propotion  of NaNs = 0.08247000463409188\n",
      "==========================================\n",
      "Column HH_DELTA_FLAG\n",
      "Values that cannot be parsed to Float []\n",
      "NaNs = 107602\n",
      "Sample of values = [1.0, 0.0]\n",
      "Propotion  of NaNs = 0.12073548536221655\n",
      "==========================================\n",
      "Column D19_SOZIALES\n",
      "Values that cannot be parsed to Float []\n",
      "NaNs = 257113\n",
      "Sample of values = [0.0, 5.0]\n",
      "Propotion  of NaNs = 0.2884952217239046\n",
      "==========================================\n",
      "Column ANZ_KINDER\n",
      "Values that cannot be parsed to Float []\n",
      "NaNs = 73499\n",
      "Sample of values = [0.0, 7.0]\n",
      "Propotion  of NaNs = 0.08247000463409188\n",
      "==========================================\n",
      "Column MOBI_RASTER\n",
      "Values that cannot be parsed to Float []\n",
      "NaNs = 93148\n",
      "Sample of values = [1.0, 3.0]\n",
      "Propotion  of NaNs = 0.10451728583594866\n",
      "==========================================\n",
      "Column AKT_DAT_KL\n",
      "Values that cannot be parsed to Float []\n",
      "NaNs = 73499\n",
      "Sample of values = [5.0, 8.0]\n",
      "Propotion  of NaNs = 0.08247000463409188\n",
      "==========================================\n",
      "Column KBA13_KMH_210\n",
      "Values that cannot be parsed to Float []\n",
      "NaNs = 105800\n",
      "Sample of values = [4.0, 3.0]\n",
      "Propotion  of NaNs = 0.11871354018812394\n",
      "==========================================\n",
      "Column D19_KINDERARTIKEL\n",
      "Values that cannot be parsed to Float []\n",
      "NaNs = 0\n",
      "Sample of values = [1, 3]\n",
      "Propotion  of NaNs = 0.0\n",
      "==========================================\n",
      "Column KBA13_CCM_1401_2500\n",
      "Values that cannot be parsed to Float []\n",
      "NaNs = 105800\n",
      "Sample of values = [3.0, 1.0]\n",
      "Propotion  of NaNs = 0.11871354018812394\n",
      "==========================================\n",
      "Column GEMEINDETYP\n",
      "Values that cannot be parsed to Float []\n",
      "NaNs = 97274\n",
      "Sample of values = [50.0, 40.0]\n",
      "Propotion  of NaNs = 0.1091468894920564\n",
      "==========================================\n",
      "Column ALTER_KIND4\n",
      "Values that cannot be parsed to Float []\n",
      "NaNs = 890016\n",
      "Sample of values = [15.0, 16.0]\n",
      "Propotion  of NaNs = 0.9986479223447383\n",
      "==========================================\n",
      "Column CJT_TYP_5\n",
      "Values that cannot be parsed to Float []\n",
      "NaNs = 4854\n",
      "Sample of values = [5.0, 4.0]\n",
      "Propotion  of NaNs = 0.005446460529992\n",
      "==========================================\n",
      "Column KK_KUNDENTYP\n",
      "Values that cannot be parsed to Float []\n",
      "NaNs = 584612\n",
      "Sample of values = [6.0, 3.0]\n",
      "Propotion  of NaNs = 0.6559674873011295\n",
      "==========================================\n",
      "Column VHN\n",
      "Values that cannot be parsed to Float []\n",
      "NaNs = 121196\n",
      "Sample of values = [3.0, 2.0]\n",
      "Propotion  of NaNs = 0.1359887166034014\n",
      "==========================================\n",
      "Column VERDICHTUNGSRAUM\n",
      "Values that cannot be parsed to Float []\n",
      "NaNs = 97274\n",
      "Sample of values = [11.0, 20.0]\n",
      "Propotion  of NaNs = 0.1091468894920564\n",
      "==========================================\n",
      "Column KONSUMZELLE\n",
      "Values that cannot be parsed to Float []\n",
      "NaNs = 93155\n",
      "Sample of values = [0.0, 1.0]\n",
      "Propotion  of NaNs = 0.10452514022896678\n",
      "==========================================\n",
      "Column LNR\n",
      "Values that cannot be parsed to Float []\n",
      "NaNs = 0\n",
      "Sample of values = [780736, 803004]\n",
      "Propotion  of NaNs = 0.0\n",
      "==========================================\n",
      "Column D19_TELKO_REST\n",
      "Values that cannot be parsed to Float []\n",
      "NaNs = 0\n",
      "Sample of values = [6, 0]\n",
      "Propotion  of NaNs = 0.0\n",
      "==========================================\n",
      "Column DSL_FLAG\n",
      "Values that cannot be parsed to Float []\n",
      "NaNs = 93148\n",
      "Sample of values = [0.0, 1.0]\n",
      "Propotion  of NaNs = 0.10451728583594866\n",
      "==========================================\n"
     ]
    }
   ],
   "source": [
    "nan_df=summarize_columns(different_columns_azdias,azdias,return_nan_df=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "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>Nan_prop</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>ALTER_KIND4</th>\n",
       "      <td>0.998648</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>ALTER_KIND3</th>\n",
       "      <td>0.993077</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>ALTER_KIND2</th>\n",
       "      <td>0.966900</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>ALTER_KIND1</th>\n",
       "      <td>0.909048</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>EXTSEL992</th>\n",
       "      <td>0.733996</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>KK_KUNDENTYP</th>\n",
       "      <td>0.655967</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "              Nan_prop\n",
       "ALTER_KIND4   0.998648\n",
       "ALTER_KIND3   0.993077\n",
       "ALTER_KIND2   0.966900\n",
       "ALTER_KIND1   0.909048\n",
       "EXTSEL992     0.733996\n",
       "KK_KUNDENTYP  0.655967"
      ]
     },
     "execution_count": 26,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "nan_df.sort_values(by=\"Nan_prop\",ascending=False,inplace=True)\n",
    "nan_df[nan_df.Nan_prop >= 0.5]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Those will be droped later , but let's first check the rest "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "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>Unnamed: 0</th>\n",
       "      <th>Description</th>\n",
       "      <th>Value</th>\n",
       "      <th>Meaning</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Attribute</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>AGER_TYP</th>\n",
       "      <td></td>\n",
       "      <td>best-ager typology</td>\n",
       "      <td>-1</td>\n",
       "      <td>unknown</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>AGER_TYP</th>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>0</td>\n",
       "      <td>no classification possible</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>AGER_TYP</th>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>1</td>\n",
       "      <td>passive elderly</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>AGER_TYP</th>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>2</td>\n",
       "      <td>cultural elderly</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>AGER_TYP</th>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>3</td>\n",
       "      <td>experience-driven elderly</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "          Unnamed: 0         Description Value                     Meaning\n",
       "Attribute                                                                 \n",
       "AGER_TYP              best-ager typology    -1                     unknown\n",
       "AGER_TYP                                     0  no classification possible\n",
       "AGER_TYP                                     1             passive elderly\n",
       "AGER_TYP                                     2            cultural elderly\n",
       "AGER_TYP                                     3   experience-driven elderly"
      ]
     },
     "execution_count": 27,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "description_full.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Broke at ANZ_TITEL\n",
      "Broke at GEBURTSJAHR\n",
      "Broke at MIN_GEBAEUDEJAHR\n",
      "Broke at ANZ_HH_TITEL\n",
      "Broke at ANZ_PERSONEN\n",
      "Broke at KBA13_ANZAHL_PKW\n",
      "Broke at ANZ_HAUSHALTE_AKTIV\n"
     ]
    }
   ],
   "source": [
    "values_dict={}\n",
    "broken=[]\n",
    "for col in set(inter_cols):\n",
    "    try:\n",
    "        \n",
    "        values_dict[col]=list(description_full[~description_full.Meaning.str.match('unknown')].loc[col,\"Value\"].values)\n",
    "    \n",
    "    except:\n",
    "        print(\"Broke at {}\".format(col))\n",
    "        broken.append(col)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "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>Unnamed: 0</th>\n",
       "      <th>Description</th>\n",
       "      <th>Value</th>\n",
       "      <th>Meaning</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Attribute</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>ANZ_TITEL</th>\n",
       "      <td></td>\n",
       "      <td>number of professional title holder in household</td>\n",
       "      <td>…</td>\n",
       "      <td>numeric value (typically coded from 1-10)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>GEBURTSJAHR</th>\n",
       "      <td></td>\n",
       "      <td>year of birth</td>\n",
       "      <td>…</td>\n",
       "      <td>numeric value</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>MIN_GEBAEUDEJAHR</th>\n",
       "      <td></td>\n",
       "      <td>year the building was first mentioned in our d...</td>\n",
       "      <td>…</td>\n",
       "      <td>numeric value</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>ANZ_HH_TITEL</th>\n",
       "      <td></td>\n",
       "      <td>number of academic title holder in building</td>\n",
       "      <td>…</td>\n",
       "      <td>numeric value (typically coded from 1-10)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>ANZ_PERSONEN</th>\n",
       "      <td></td>\n",
       "      <td>number of adult persons in the household</td>\n",
       "      <td>…</td>\n",
       "      <td>numeric value (typically coded from 1-3)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>KBA13_ANZAHL_PKW</th>\n",
       "      <td></td>\n",
       "      <td>number of cars in the PLZ8</td>\n",
       "      <td>…</td>\n",
       "      <td>numeric value</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>ANZ_HAUSHALTE_AKTIV</th>\n",
       "      <td></td>\n",
       "      <td>number of households in the building</td>\n",
       "      <td>…</td>\n",
       "      <td>numeric value (typically coded from 1-10)</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                    Unnamed: 0  \\\n",
       "Attribute                        \n",
       "ANZ_TITEL                        \n",
       "GEBURTSJAHR                      \n",
       "MIN_GEBAEUDEJAHR                 \n",
       "ANZ_HH_TITEL                     \n",
       "ANZ_PERSONEN                     \n",
       "KBA13_ANZAHL_PKW                 \n",
       "ANZ_HAUSHALTE_AKTIV              \n",
       "\n",
       "                                                           Description Value  \\\n",
       "Attribute                                                                      \n",
       "ANZ_TITEL            number of professional title holder in household      …   \n",
       "GEBURTSJAHR                                              year of birth     …   \n",
       "MIN_GEBAEUDEJAHR     year the building was first mentioned in our d...     …   \n",
       "ANZ_HH_TITEL               number of academic title holder in building     …   \n",
       "ANZ_PERSONEN                  number of adult persons in the household     …   \n",
       "KBA13_ANZAHL_PKW                            number of cars in the PLZ8     …   \n",
       "ANZ_HAUSHALTE_AKTIV               number of households in the building     …   \n",
       "\n",
       "                                                       Meaning  \n",
       "Attribute                                                       \n",
       "ANZ_TITEL            numeric value (typically coded from 1-10)  \n",
       "GEBURTSJAHR                                      numeric value  \n",
       "MIN_GEBAEUDEJAHR                                 numeric value  \n",
       "ANZ_HH_TITEL         numeric value (typically coded from 1-10)  \n",
       "ANZ_PERSONEN          numeric value (typically coded from 1-3)  \n",
       "KBA13_ANZAHL_PKW                                 numeric value  \n",
       "ANZ_HAUSHALTE_AKTIV  numeric value (typically coded from 1-10)  "
      ]
     },
     "execution_count": 29,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "description_full.loc[broken,:]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "metadata": {},
   "outputs": [],
   "source": [
    "for i in broken:\n",
    "    inter_cols.remove(i)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We can see that the columns we couldn't identify thier values are all numerical "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "metadata": {
    "collapsed": true,
    "jupyter": {
     "outputs_hidden": true
    }
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "{'KBA05_MOD8': [0, 1, 2, 3],\n",
       " 'KBA13_CCM_2000': [0, 1, 2, 3, 4, 5],\n",
       " 'TITEL_KZ': [1, 2, 3, 4, 5],\n",
       " 'KBA13_SEG_GELAENDEWAGEN': [0, 1, 2, 3, 4, 5],\n",
       " 'ONLINE_AFFINITAET': [0, 1, 2, 3, 4, 5],\n",
       " 'KBA13_HALTER_45': [0, 1, 2, 3, 4, 5],\n",
       " 'KBA13_SEG_VAN': [0, 1, 2, 3, 4, 5],\n",
       " 'KBA05_SEG6': [0, 1],\n",
       " 'KBA13_SEG_MINIWAGEN': [0, 1, 2, 3, 4, 5],\n",
       " 'FINANZ_UNAUFFAELLIGER': [1, 2, 3, 4, 5],\n",
       " 'FINANZTYP': [1, 2, 3, 4, 5, 6],\n",
       " 'FINANZ_MINIMALIST': [1, 2, 3, 4, 5],\n",
       " 'KBA13_HALTER_30': [0, 1, 2, 3, 4, 5],\n",
       " 'SEMIO_PFLICHT': [1, 2, 3, 4, 5, 6, 7],\n",
       " 'D19_TELKO_ONLINE_DATUM': [1, 2, 3, 4, 5, 6, 7, 8, 9, 10],\n",
       " 'KBA05_KRSHERST3': [1, 2, 3, 4, 5],\n",
       " 'KBA13_HALTER_25': [0, 1, 2, 3, 4, 5],\n",
       " 'KBA13_SITZE_6': [0, 1, 2, 3, 4, 5],\n",
       " 'KBA05_ZUL1': [1, 2, 3, 4, 5],\n",
       " 'SEMIO_MAT': [1, 2, 3, 4, 5, 6, 7],\n",
       " 'KBA13_KW_110': [0, 1, 2, 3, 4, 5],\n",
       " 'D19_VERSAND_DATUM': [1, 2, 3, 4, 5, 6, 7, 8, 9, 10],\n",
       " 'KBA05_MOD3': [1, 2, 3, 4, 5],\n",
       " 'KBA13_SEG_OBEREMITTELKLASSE': [0, 1, 2, 3, 4, 5],\n",
       " 'KBA05_MOD4': [0, 1, 2, 3, 4, 5],\n",
       " 'KBA05_SEG5': [0, 1, 2, 3, 4],\n",
       " 'KBA13_CCM_2500': [0, 1, 2, 3, 4, 5],\n",
       " 'KBA13_CCM_1000': [0, 1, 2, 3, 4, 5],\n",
       " 'D19_GESAMT_ANZ_24': [0, 1, 2, 3, 4, 5, 6],\n",
       " 'VERS_TYP': [1, 2],\n",
       " 'KBA13_SEG_WOHNMOBILE': [0, 1, 2, 3, 4, 5],\n",
       " 'FINANZ_ANLEGER': [1, 2, 3, 4, 5],\n",
       " 'D19_VERSAND_OFFLINE_DATUM': [1, 2, 3, 4, 5, 6, 7, 8, 9, 10],\n",
       " 'KBA13_BJ_2009': [0, 1, 2, 3, 4, 5],\n",
       " 'LP_STATUS_FEIN': [1, 2, 3, 4, 5, 6, 7, 8, 9, 10],\n",
       " 'KBA05_CCM1': [1, 2, 3, 4, 5],\n",
       " 'KBA05_ZUL4': [0, 1, 2, 3, 4, 5],\n",
       " 'KBA13_KMH_251': [0, 1, 2, 3, 4, 5],\n",
       " 'KBA13_OPEL': [0, 1, 2, 3, 4, 5],\n",
       " 'KBA05_MAXHERST': [1, 2, 3, 4, 5],\n",
       " 'KBA13_SEG_MINIVANS': [0, 1, 2, 3, 4, 5],\n",
       " 'KBA13_CCM_1600': [0, 1, 2, 3, 4, 5],\n",
       " 'KBA13_FIAT': [0, 1, 2, 3, 4, 5],\n",
       " 'KBA13_HERST_AUDI_VW': [0, 1, 2, 3, 4, 5],\n",
       " 'KBA05_ANTG3': [0, 1, 2, 3],\n",
       " 'KBA05_KRSOBER': [1, 2, 3],\n",
       " 'KBA05_SEG10': [0, 1, 2, 3, 4],\n",
       " 'KBA13_SEG_KOMPAKTKLASSE': [0, 1, 2, 3, 4, 5],\n",
       " 'KBA13_SEG_GROSSRAUMVANS': [0, 1, 2, 3, 4, 5],\n",
       " 'CAMEO_DEUG_2015': [1, 2, 3, 4, 5, 6, 7, 8, 9],\n",
       " 'KBA13_CCM_2501': [0, 1, 2, 3, 4, 5],\n",
       " 'PLZ8_BAUMAX': [1, 2, 3, 4, 5],\n",
       " 'W_KEIT_KIND_HH': [1, 2, 3, 4, 5, 6],\n",
       " 'KBA13_SEG_MITTELKLASSE': [0, 1, 2, 3, 4, 5],\n",
       " 'ZABEOTYP': [1, 2, 3, 4, 5, 6],\n",
       " 'KBA05_SEG3': [1, 2, 3, 4, 5],\n",
       " 'KBA05_FRAU': [1, 2, 3, 4, 5],\n",
       " 'KBA13_ALTERHALTER_60': [0, 1, 2, 3, 4, 5],\n",
       " 'GFK_URLAUBERTYP': [1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12],\n",
       " 'KBA05_MAXBJ': [1, 2, 3, 4],\n",
       " 'KBA13_VORB_0': [0, 1, 2, 3, 4, 5],\n",
       " 'KBA13_KRSHERST_AUDI_VW': [0, 1, 2, 3, 4, 5],\n",
       " 'KKK': [1, 2, 3, 4],\n",
       " 'KBA13_MERCEDES': [0, 1, 2, 3, 4, 5],\n",
       " 'KBA13_SEG_KLEINWAGEN': [0, 1, 2, 3, 4, 5],\n",
       " 'KBA05_HERST3': [0, 1, 2, 3, 4, 5],\n",
       " 'KBA05_MOTRAD': [0, 1, 2, 3],\n",
       " 'KBA05_MODTEMP': [1, 2, 3, 4, 5],\n",
       " 'KBA13_BJ_2008': [0, 1, 2, 3, 4, 5],\n",
       " 'KBA05_CCM4': [0, 1, 2, 3, 4],\n",
       " 'SEMIO_DOM': [1, 2, 3, 4, 5, 6, 7],\n",
       " 'KBA05_AUTOQUOT': [1, 2, 3, 4, 5],\n",
       " 'D19_GESAMT_ONLINE_DATUM': [1, 2, 3, 4, 5, 6, 7, 8, 9, 10],\n",
       " 'D19_BANKEN_ANZ_12': [0, 1, 2, 3, 4, 5, 6],\n",
       " 'KBA13_HERST_SONST': [0, 1, 2, 3, 4, 5],\n",
       " 'KBA05_SEG4': [1, 2, 3, 4, 5],\n",
       " 'KBA13_CCM_1800': [0, 1, 2, 3, 4, 5],\n",
       " 'KBA13_VW': [0, 1, 2, 3, 4, 5],\n",
       " 'SEMIO_KRIT': [1, 2, 3, 4, 5, 6, 7],\n",
       " 'PLZ8_ANTG4': [0, 1, 2],\n",
       " 'PLZ8_GBZ': [1, 2, 3, 4, 5],\n",
       " 'D19_VERSI_ANZ_12': [0, 1, 2, 3, 4, 5, 6],\n",
       " 'KBA13_KW_121': [0, 1, 2, 3, 4, 5],\n",
       " 'KBA13_KW_80': [0, 1, 2, 3, 4, 5],\n",
       " 'LP_LEBENSPHASE_GROB': [1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12],\n",
       " 'KBA13_HERST_ASIEN': [0, 1, 2, 3, 4, 5],\n",
       " 'D19_VERSI_ANZ_24': [0, 1, 2, 3, 4, 5, 6],\n",
       " 'KBA13_KRSSEG_KLEIN': [0, 1, 2, 3],\n",
       " 'D19_BANKEN_ONLINE_DATUM': [1, 2, 3, 4, 5, 6, 7, 8, 9, 10],\n",
       " 'KBA05_BAUMAX': [1, 2, 3, 4, 5],\n",
       " 'KBA05_ANTG4': [0, 1, 2],\n",
       " 'KBA13_ALTERHALTER_45': [0, 1, 2, 3, 4, 5],\n",
       " 'KBA05_SEG8': [0, 1, 2, 3],\n",
       " 'KBA05_VORB1': [1, 2, 3, 4, 5],\n",
       " 'KBA13_KW_50': [0, 1, 2, 3, 4, 5],\n",
       " 'GEBAEUDETYP': [1, 2, 3, 4, 5, 6, 7, 8],\n",
       " 'KBA13_KW_120': [0, 1, 2, 3, 4, 5],\n",
       " 'LP_FAMILIE_FEIN': [1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11],\n",
       " 'KBA05_GBZ': [1, 2, 3, 4, 5],\n",
       " 'KBA13_HALTER_55': [0, 1, 2, 3, 4, 5],\n",
       " 'KBA05_KRSVAN': [1, 2, 3],\n",
       " 'ALTERSKATEGORIE_GROB': [1, 2, 3, 4, 9],\n",
       " 'KBA13_KRSAQUOT': [0, 1, 2, 3, 4, 5],\n",
       " 'KBA05_DIESEL': [0, 1, 2, 3, 4],\n",
       " 'KBA13_KMH_180': [0, 1, 2, 3, 4, 5],\n",
       " 'D19_VERSAND_ANZ_12': [0, 1, 2, 3, 4, 5, 6],\n",
       " 'KBA05_ANTG1': [0, 1, 2, 3, 4],\n",
       " 'PLZ8_ANTG1': [0, 1, 2, 3, 4],\n",
       " 'KBA13_BJ_2006': [0, 1, 2, 3, 4, 5],\n",
       " 'CJT_GESAMTTYP': [1, 2, 3, 4, 5, 6],\n",
       " 'KBA13_FORD': [0, 1, 2, 3, 4, 5],\n",
       " 'KBA13_SEG_SPORTWAGEN': [0, 1, 2, 3, 4, 5],\n",
       " 'KBA13_CCM_3001': [0, 1, 2, 3, 4, 5],\n",
       " 'KBA13_FAB_SONSTIGE': [0, 1, 2, 3, 4, 5],\n",
       " 'PRAEGENDE_JUGENDJAHRE': [1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15],\n",
       " 'KBA05_MOD1': [0, 1, 2, 3, 4],\n",
       " 'KBA13_HALTER_65': [0, 1, 2, 3, 4, 5],\n",
       " 'KBA05_SEG9': [0, 1, 2, 3],\n",
       " 'ALTER_HH': [1,\n",
       "  2,\n",
       "  3,\n",
       "  4,\n",
       "  5,\n",
       "  6,\n",
       "  7,\n",
       "  8,\n",
       "  9,\n",
       "  10,\n",
       "  11,\n",
       "  12,\n",
       "  13,\n",
       "  14,\n",
       "  15,\n",
       "  16,\n",
       "  17,\n",
       "  18,\n",
       "  19,\n",
       "  20,\n",
       "  21],\n",
       " 'D19_TELKO_OFFLINE_DATUM': [1, 2, 3, 4, 5, 6, 7, 8, 9, 10],\n",
       " 'D19_BANKEN_DATUM': [1, 2, 3, 4, 5, 6, 7, 8, 9, 10],\n",
       " 'KBA13_KRSHERST_BMW_BENZ': [0, 1, 2, 3, 4, 5],\n",
       " 'D19_BANKEN_OFFLINE_DATUM': [1, 2, 3, 4, 5, 6, 7, 8, 9, 10],\n",
       " 'SEMIO_REL': [1, 2, 3, 4, 5, 6, 7],\n",
       " 'KBA13_KRSHERST_FORD_OPEL': [0, 1, 2, 3, 4, 5],\n",
       " 'KBA05_KW2': [1, 2, 3, 4, 5],\n",
       " 'RELAT_AB': [1, 2, 3, 4, 5],\n",
       " 'KBA13_BJ_2000': [0, 1, 2, 3, 4, 5],\n",
       " 'NATIONALITAET_KZ': [1, 2, 3],\n",
       " 'SEMIO_VERT': [1, 2, 3, 4, 5, 6, 7],\n",
       " 'KBA05_MOTOR': [1, 2, 3, 4],\n",
       " 'KBA05_KW1': [1, 2, 3, 4, 5],\n",
       " 'KBA05_HERST5': [0, 1, 2, 3, 4, 5],\n",
       " 'KBA05_MAXVORB': [1, 2, 3],\n",
       " 'D19_BANKEN_ANZ_24': [0, 1, 2, 3, 4, 5, 6],\n",
       " 'KBA13_PEUGEOT': [0, 1, 2, 3, 4, 5],\n",
       " 'D19_GESAMT_DATUM': [1, 2, 3, 4, 5, 6, 7, 8, 9, 10],\n",
       " 'D19_BANKEN_ONLINE_QUOTE_12': [0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10],\n",
       " 'KBA13_KW_90': [0, 1, 2, 3, 4, 5],\n",
       " 'KBA05_ALTER1': [0, 1, 2, 3, 4],\n",
       " 'KBA13_SEG_SONSTIGE': [0, 1, 2, 3, 4, 5],\n",
       " 'INNENSTADT': [1, 2, 3, 4, 5, 6, 7, 8],\n",
       " 'KBA13_VORB_1_2': [0, 1, 2, 3, 4, 5],\n",
       " 'KBA13_BJ_1999': [0, 1, 2, 3, 4, 5],\n",
       " 'SEMIO_KAEM': [1, 2, 3, 4, 5, 6, 7],\n",
       " 'KBA13_KW_61_120': [0, 1, 2, 3, 4, 5],\n",
       " 'KBA13_KW_40': [0, 1, 2, 3, 4, 5],\n",
       " 'KBA13_HALTER_60': [0, 1, 2, 3, 4, 5],\n",
       " 'KBA13_KMH_110': [0, 1, 2, 3, 4, 5],\n",
       " 'GREEN_AVANTGARDE': [0, 1],\n",
       " 'CAMEO_DEU_2015': ['1A',\n",
       "  '1B',\n",
       "  '1C',\n",
       "  '1D',\n",
       "  '1E',\n",
       "  '2A',\n",
       "  '2B',\n",
       "  '2C',\n",
       "  '2D',\n",
       "  '3A',\n",
       "  '3B',\n",
       "  '3C',\n",
       "  '3D',\n",
       "  '4A',\n",
       "  '4B',\n",
       "  '4C',\n",
       "  '4D',\n",
       "  '4E',\n",
       "  '5A',\n",
       "  '5B',\n",
       "  '5C',\n",
       "  '5D',\n",
       "  '5E',\n",
       "  '5F',\n",
       "  '6A',\n",
       "  '6B',\n",
       "  '6C',\n",
       "  '6D',\n",
       "  '6E',\n",
       "  '6F',\n",
       "  '7A',\n",
       "  '7B',\n",
       "  '7C',\n",
       "  '7D',\n",
       "  '7E',\n",
       "  '8A',\n",
       "  '8B',\n",
       "  '8C',\n",
       "  '8D',\n",
       "  '9A',\n",
       "  '9B',\n",
       "  '9C',\n",
       "  '9D',\n",
       "  '9E'],\n",
       " 'D19_VERSAND_ANZ_24': [0, 1, 2, 3, 4, 5, 6],\n",
       " 'D19_VERSAND_ONLINE_QUOTE_12': [0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10],\n",
       " 'KBA13_BMW': [0, 1, 2, 3, 4, 5],\n",
       " 'KBA13_HALTER_40': [0, 1, 2, 3, 4, 5],\n",
       " 'KBA13_HALTER_20': [0, 1, 2, 3, 4, 5],\n",
       " 'WOHNDAUER_2008': [1, 2, 3, 4, 5, 6, 7, 8, 9],\n",
       " 'KBA13_SEG_UTILITIES': [0, 1, 2, 3, 4, 5],\n",
       " 'EWDICHTE': [1, 2, 3, 4, 5, 6],\n",
       " 'D19_GESAMT_ONLINE_QUOTE_12': [0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10],\n",
       " 'KBA13_AUTOQUOTE': [0, 1, 2, 3, 4, 5],\n",
       " 'KBA13_SEG_OBERKLASSE': [0, 1, 2, 3, 4, 5],\n",
       " 'SEMIO_TRADV': [1, 2, 3, 4, 5, 6, 7],\n",
       " 'OST_WEST_KZ': ['O', 'W'],\n",
       " 'KBA05_HERST2': [0, 1, 2, 3, 4, 5],\n",
       " 'KBA05_ALTER3': [1, 2, 3, 4, 5],\n",
       " 'KBA05_VORB0': [1, 2, 3, 4, 5],\n",
       " 'KBA13_VORB_2': [0, 1, 2, 3, 4, 5],\n",
       " 'KBA05_ANTG2': [0, 1, 2, 3, 4],\n",
       " 'PLZ8_ANTG2': [0, 1, 2, 3, 4],\n",
       " 'KBA13_KW_30': [0, 1, 2, 3, 4, 5],\n",
       " 'GEBAEUDETYP_RASTER': [1, 2, 3, 4, 5],\n",
       " 'KBA13_KW_0_60': [0, 1, 2, 3, 4, 5],\n",
       " 'KBA13_HERST_EUROPA': [0, 1, 2, 3, 4, 5],\n",
       " 'KBA13_MAZDA': [0, 1, 2, 3, 4, 5],\n",
       " 'KBA13_HALTER_66': [0, 1, 2, 3, 4, 5],\n",
       " 'WOHNLAGE': [0, 1, 2, 3, 4, 5, 7, 8],\n",
       " 'RETOURTYP_BK_S': [1, 2, 3, 4, 5],\n",
       " 'LP_STATUS_GROB': [1, 2, 3, 4, 5, 6, 7, 8, 9, 10],\n",
       " 'KBA13_SEG_KLEINST': [0, 1, 2, 3, 4, 5],\n",
       " 'KBA05_MOD2': [1, 2, 3, 4, 5],\n",
       " 'D19_TELKO_DATUM': [1, 2, 3, 4, 5, 6, 7, 8, 9, 10],\n",
       " 'KBA05_KRSKLEIN': [1, 2, 3],\n",
       " 'D19_GESAMT_ANZ_12': [0, 1, 2, 3, 4, 5, 6],\n",
       " 'KBA13_AUDI': [0, 1, 2, 3, 4, 5],\n",
       " 'HH_EINKOMMEN_SCORE': [1, 2, 3, 4, 5, 6],\n",
       " 'SHOPPER_TYP': [0, 1, 2, 3],\n",
       " 'KBA13_KMH_140': [0, 1, 2, 3, 4, 5],\n",
       " 'KBA05_HERSTTEMP': [1, 2, 3, 4, 5],\n",
       " 'ANREDE_KZ': [1, 2],\n",
       " 'KBA13_CCM_1400': [0, 1, 2, 3, 4, 5],\n",
       " 'KBA13_KW_60': [0, 1, 2, 3, 4, 5],\n",
       " 'PLZ8_ANTG3': [0, 1, 2, 3],\n",
       " 'KBA05_SEG1': [0, 1, 2, 3],\n",
       " 'KBA13_ALTERHALTER_61': [0, 1, 2, 3, 4, 5],\n",
       " 'D19_KONSUMTYP': [1, 2, 3, 4, 5, 6, 9],\n",
       " 'AGER_TYP': [0, 1, 2, 3],\n",
       " 'KONSUMNAEHE': [1, 2, 3, 4, 5, 6, 7],\n",
       " 'SEMIO_FAM': [1, 2, 3, 4, 5, 6, 7],\n",
       " 'KBA13_MOTOR': [0, 1, 2, 3, 4],\n",
       " 'KBA13_CCM_0_1400': [0, 1, 2, 3, 4, 5],\n",
       " 'ORTSGR_KLS9': [1, 2, 3, 4, 5, 6, 7, 8, 9],\n",
       " 'KBA13_KMH_250': [0, 1, 2, 3, 4, 5],\n",
       " 'KBA05_ZUL3': [0, 1, 2, 3, 4, 5],\n",
       " 'D19_TELKO_ANZ_12': [0, 1, 2, 3, 4, 5, 6],\n",
       " 'HEALTH_TYP': [0, 1, 2, 3],\n",
       " 'SEMIO_SOZ': [1, 2, 3, 4, 5, 6, 7],\n",
       " 'KBA05_KRSHERST2': [1, 2, 3, 4, 5],\n",
       " 'SEMIO_RAT': [1, 2, 3, 4, 5, 6, 7],\n",
       " 'D19_GESAMT_OFFLINE_DATUM': [1, 2, 3, 4, 5, 6, 7, 8, 9, 10],\n",
       " 'KBA05_CCM2': [1, 2, 3, 4, 5],\n",
       " 'KBA05_HERST1': [0, 1, 2, 3, 4, 5],\n",
       " 'KBA05_CCM3': [1, 2, 3, 4, 5],\n",
       " 'KBA05_ALTER4': [0, 1, 2, 3, 4, 5],\n",
       " 'KBA13_KMH_211': [0, 1, 2, 3, 4, 5],\n",
       " 'KBA13_KRSSEG_VAN': [0, 1, 2, 3],\n",
       " 'KBA05_HERST4': [0, 1, 2, 3, 4, 5],\n",
       " 'BALLRAUM': [1, 2, 3, 4, 5, 6, 7],\n",
       " 'KBA13_ALTERHALTER_30': [0, 1, 2, 3, 4, 5],\n",
       " 'KBA05_MAXAH': [1, 2, 3, 4, 5],\n",
       " 'FINANZ_SPARER': [1, 2, 3, 4, 5],\n",
       " 'KBA05_VORB2': [0, 1, 2, 3, 4, 5],\n",
       " 'SEMIO_KULT': [1, 2, 3, 4, 5, 6, 7],\n",
       " 'KBA05_ZUL2': [1, 2, 3, 4, 5],\n",
       " 'D19_VERSAND_ONLINE_DATUM': [1, 2, 3, 4, 5, 6, 7, 8, 9, 10],\n",
       " 'KBA13_VORB_1': [0, 1, 2, 3, 4, 5],\n",
       " 'D19_TELKO_ANZ_24': [0, 1, 2, 3, 4, 5, 6],\n",
       " 'KBA13_FAB_ASIEN': [0, 1, 2, 3, 4, 5],\n",
       " 'KBA05_MAXSEG': [1, 2, 3, 4],\n",
       " 'KBA13_RENAULT': [0, 1, 2, 3, 4, 5],\n",
       " 'KBA13_CCM_1200': [0, 1, 2, 3, 4, 5],\n",
       " 'LP_FAMILIE_GROB': [1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11],\n",
       " 'REGIOTYP': [1, 2, 3, 4, 5, 6, 7],\n",
       " 'KBA13_KRSSEG_OBER': [0, 1, 2, 3],\n",
       " 'KBA05_KW3': [0, 1, 2, 3, 4],\n",
       " 'KBA05_SEG7': [0, 1, 2, 3],\n",
       " 'KBA13_KRSZUL_NEU': [0, 1, 2, 3],\n",
       " 'KBA13_SITZE_5': [0, 1, 2, 3, 4, 5],\n",
       " 'LP_LEBENSPHASE_FEIN': [1,\n",
       "  2,\n",
       "  3,\n",
       "  4,\n",
       "  5,\n",
       "  6,\n",
       "  7,\n",
       "  8,\n",
       "  9,\n",
       "  10,\n",
       "  11,\n",
       "  12,\n",
       "  13,\n",
       "  14,\n",
       "  15,\n",
       "  16,\n",
       "  17,\n",
       "  18,\n",
       "  19,\n",
       "  20,\n",
       "  21,\n",
       "  22,\n",
       "  23,\n",
       "  24,\n",
       "  25,\n",
       "  26,\n",
       "  27,\n",
       "  28,\n",
       "  29,\n",
       "  30,\n",
       "  31,\n",
       "  32,\n",
       "  33,\n",
       "  34,\n",
       "  35,\n",
       "  36,\n",
       "  37,\n",
       "  38,\n",
       "  39,\n",
       "  40],\n",
       " 'KBA13_HERST_BMW_BENZ': [0, 1, 2, 3, 4, 5],\n",
       " 'KBA13_CCM_3000': [0, 1, 2, 3, 4, 5],\n",
       " 'SEMIO_LUST': [1, 2, 3, 4, 5, 6, 7],\n",
       " 'KBA13_TOYOTA': [0, 1, 2, 3, 4, 5],\n",
       " 'KBA05_KRSHERST1': [1, 2, 3, 4, 5],\n",
       " 'KBA13_NISSAN': [0, 1, 2, 3, 4, 5],\n",
       " 'KBA05_KRSAQUOT': [1, 2, 3, 4, 5],\n",
       " 'KBA13_HALTER_35': [0, 1, 2, 3, 4, 5],\n",
       " 'KBA05_KRSZUL': [1, 2, 3],\n",
       " 'FINANZ_VORSORGER': [1, 2, 3, 4, 5],\n",
       " 'KBA05_SEG2': [1, 2, 3, 4, 5],\n",
       " 'MOBI_REGIO': [1, 2, 3, 4, 5, 6],\n",
       " 'KBA13_HERST_FORD_OPEL': [0, 1, 2, 3, 4, 5],\n",
       " 'KBA13_KW_70': [0, 1, 2, 3, 4, 5],\n",
       " 'KBA13_BJ_2004': [0, 1, 2, 3, 4, 5],\n",
       " 'KBA13_KMH_140_210': [0, 1, 2, 3, 4, 5],\n",
       " 'KBA13_HALTER_50': [0, 1, 2, 3, 4, 5],\n",
       " 'KBA13_VORB_3': [0, 1, 2, 3, 4, 5],\n",
       " 'PLZ8_HHZ': [1, 2, 3, 4, 5],\n",
       " 'KBA13_SITZE_4': [0, 1, 2, 3, 4, 5],\n",
       " 'KBA13_CCM_1500': [0, 1, 2, 3, 4, 5],\n",
       " 'KBA05_ANHANG': [0, 1, 2, 3],\n",
       " 'KBA13_KMH_0_140': [0, 1, 2, 3, 4, 5],\n",
       " 'SEMIO_ERL': [1, 2, 3, 4, 5, 6, 7],\n",
       " 'KBA05_ALTER2': [1, 2, 3, 4, 5],\n",
       " 'FINANZ_HAUSBAUER': [1, 2, 3, 4, 5]}"
      ]
     },
     "execution_count": 31,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "values_dict"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "metadata": {},
   "outputs": [],
   "source": [
    "pk.dump(values_dict,open(\"Values_dictionary.pkl\",\"wb\")) #for later use"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "{9.0}"
      ]
     },
     "execution_count": 33,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "set(azdias.KBA05_MOD3.value_counts().index).difference(set(values_dict['KBA05_MOD3']))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "34740782"
      ]
     },
     "execution_count": 34,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "before=azdias.isna().sum().sum()\n",
    "before"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "True"
      ]
     },
     "execution_count": 35,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "all(item in values_dict for item in inter_cols)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "metadata": {},
   "outputs": [],
   "source": [
    "for col in inter_cols:\n",
    "    \n",
    "    values=list(set(azdias[col].value_counts().index).difference(set(values_dict[col])))\n",
    "    \n",
    "    if len(values) >1 :\n",
    "        \n",
    "        for value in values:\n",
    "            azdias[col].replace(value,np.nan,inplace=True)\n",
    "    else:\n",
    "        \n",
    "        azdias[col].replace(values,np.nan,inplace=True)\n",
    "            \n",
    "                "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Added 3144445 more Nans to azdias\n"
     ]
    }
   ],
   "source": [
    "after=azdias.isna().sum().sum()\n",
    "print(\"Added {} more Nans to azdias\".format(after - before))\n",
    "before=after=None"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "metadata": {},
   "outputs": [],
   "source": [
    "for col in inter_cols:\n",
    "    \n",
    "    values=list(set(customers[col].value_counts().index).difference(set(values_dict[col])))\n",
    "    \n",
    "    if len(values) >1 :\n",
    "        \n",
    "        for value in values:\n",
    "            customers[col].replace(value,np.nan,inplace=True)\n",
    "    else:\n",
    "        \n",
    "        customers[col].replace(values,np.nan,inplace=True)           "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## **So let's take a look of the propotion of NANs in each column and how they're distributed accross both the DataSets**"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Let's first make a slice of the customers data frame without the columns that belongs to the company "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 39,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "366"
      ]
     },
     "execution_count": 39,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "cols_to_keep = list(set(customers.columns).intersection(set(azdias.columns))) #getting the intersection between the columns of customers and the population \n",
    "len(cols_to_keep)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 40,
   "metadata": {},
   "outputs": [],
   "source": [
    "cust_slic=customers[cols_to_keep]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 41,
   "metadata": {},
   "outputs": [],
   "source": [
    "#creating a series for nans and thier propotions in the azdias dataframe\n",
    "nans_azdias=azdias.isna().sum()/azdias.shape[0]\n",
    "#creating a series for nans and thier propotions in the customers dataframe\n",
    "nans_cust=cust_slic.isna().sum()/cust_slic.shape[0]\n",
    "#naming both serieses for merging\n",
    "nans_azdias.name=\"Population\"\n",
    "nans_cust.name=\"Customers\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 42,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(366, 366)"
      ]
     },
     "execution_count": 42,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "len(nans_azdias),len(nans_cust)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 43,
   "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>Feature</th>\n",
       "      <th>Class</th>\n",
       "      <th>Propotion</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>ALTER_KIND4</td>\n",
       "      <td>Population</td>\n",
       "      <td>0.998648</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>ALTER_KIND3</td>\n",
       "      <td>Population</td>\n",
       "      <td>0.993077</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>TITEL_KZ</td>\n",
       "      <td>Population</td>\n",
       "      <td>0.997576</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>ALTER_KIND2</td>\n",
       "      <td>Population</td>\n",
       "      <td>0.966900</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>ALTER_KIND1</td>\n",
       "      <td>Population</td>\n",
       "      <td>0.909048</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>727</th>\n",
       "      <td>SEMIO_TRADV</td>\n",
       "      <td>Customers</td>\n",
       "      <td>0.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>728</th>\n",
       "      <td>SEMIO_VERT</td>\n",
       "      <td>Customers</td>\n",
       "      <td>0.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>729</th>\n",
       "      <td>ZABEOTYP</td>\n",
       "      <td>Customers</td>\n",
       "      <td>0.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>730</th>\n",
       "      <td>ANREDE_KZ</td>\n",
       "      <td>Customers</td>\n",
       "      <td>0.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>731</th>\n",
       "      <td>ALTERSKATEGORIE_GROB</td>\n",
       "      <td>Customers</td>\n",
       "      <td>0.000000</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>732 rows × 3 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                  Feature       Class  Propotion\n",
       "0             ALTER_KIND4  Population   0.998648\n",
       "1             ALTER_KIND3  Population   0.993077\n",
       "2                TITEL_KZ  Population   0.997576\n",
       "3             ALTER_KIND2  Population   0.966900\n",
       "4             ALTER_KIND1  Population   0.909048\n",
       "..                    ...         ...        ...\n",
       "727           SEMIO_TRADV   Customers   0.000000\n",
       "728            SEMIO_VERT   Customers   0.000000\n",
       "729              ZABEOTYP   Customers   0.000000\n",
       "730             ANREDE_KZ   Customers   0.000000\n",
       "731  ALTERSKATEGORIE_GROB   Customers   0.000000\n",
       "\n",
       "[732 rows x 3 columns]"
      ]
     },
     "execution_count": 43,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#merging on the index (feature names)\n",
    "feature_nans=pd.merge(nans_azdias,nans_cust,left_index=True,right_index=True)\n",
    "#making the index as a column to ease plotting \n",
    "feature_nans=feature_nans.reset_index().rename(columns={\"index\":\"Feature\"})\n",
    "#sorting for the order of the plot \n",
    "feature_nans.sort_values(by=[\"Customers\",\"Population\"],ascending=False,inplace=True)\n",
    "feature_nans.reset_index(inplace=True,drop=True)\n",
    "feature_nans=feature_nans.melt(id_vars=['Feature'],var_name=\"Class\",value_name=\"Propotion\")\n",
    "feature_nans"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Let's select only columns with more than 50% missing values, as it seams not the important to be included since it's missing that much data "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 44,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "['TITEL_KZ',\n",
       " 'ALTER_KIND1',\n",
       " 'ALTER_KIND3',\n",
       " 'AGER_TYP',\n",
       " 'ALTER_KIND4',\n",
       " 'KBA05_BAUMAX',\n",
       " 'KK_KUNDENTYP',\n",
       " 'ALTER_KIND2',\n",
       " 'CAMEO_DEUG_2015',\n",
       " 'EXTSEL992']"
      ]
     },
     "execution_count": 44,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "threshold=0.5\n",
    "removed_cols=list(set(feature_nans[~(feature_nans.Propotion < threshold)].Feature.values))\n",
    "removed_cols"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 45,
   "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>Feature</th>\n",
       "      <th>Class</th>\n",
       "      <th>Propotion</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>374</th>\n",
       "      <td>AGER_TYP</td>\n",
       "      <td>Customers</td>\n",
       "      <td>0.480595</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>375</th>\n",
       "      <td>EXTSEL992</td>\n",
       "      <td>Customers</td>\n",
       "      <td>0.444989</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>376</th>\n",
       "      <td>ALTER_HH</td>\n",
       "      <td>Customers</td>\n",
       "      <td>0.358707</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>ALTER_HH</td>\n",
       "      <td>Population</td>\n",
       "      <td>0.348137</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>377</th>\n",
       "      <td>KKK</td>\n",
       "      <td>Customers</td>\n",
       "      <td>0.313401</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>339</th>\n",
       "      <td>FINANZ_ANLEGER</td>\n",
       "      <td>Population</td>\n",
       "      <td>0.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>340</th>\n",
       "      <td>FINANZ_HAUSBAUER</td>\n",
       "      <td>Population</td>\n",
       "      <td>0.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>341</th>\n",
       "      <td>FINANZ_MINIMALIST</td>\n",
       "      <td>Population</td>\n",
       "      <td>0.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>342</th>\n",
       "      <td>FINANZ_SPARER</td>\n",
       "      <td>Population</td>\n",
       "      <td>0.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>731</th>\n",
       "      <td>ALTERSKATEGORIE_GROB</td>\n",
       "      <td>Customers</td>\n",
       "      <td>0.000000</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>714 rows × 3 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                  Feature       Class  Propotion\n",
       "374              AGER_TYP   Customers   0.480595\n",
       "375             EXTSEL992   Customers   0.444989\n",
       "376              ALTER_HH   Customers   0.358707\n",
       "10               ALTER_HH  Population   0.348137\n",
       "377                   KKK   Customers   0.313401\n",
       "..                    ...         ...        ...\n",
       "339        FINANZ_ANLEGER  Population   0.000000\n",
       "340      FINANZ_HAUSBAUER  Population   0.000000\n",
       "341     FINANZ_MINIMALIST  Population   0.000000\n",
       "342         FINANZ_SPARER  Population   0.000000\n",
       "731  ALTERSKATEGORIE_GROB   Customers   0.000000\n",
       "\n",
       "[714 rows x 3 columns]"
      ]
     },
     "execution_count": 45,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "feature_nans=feature_nans[feature_nans.Propotion < threshold]\n",
    "feature_nans.sort_values(by=\"Propotion\",ascending=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 46,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "358"
      ]
     },
     "execution_count": 46,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "len(set(feature_nans.Feature))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 47,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "358"
      ]
     },
     "execution_count": 47,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "cols_to_keep=list(set(feature_nans.Feature.values))\n",
    "len(cols_to_keep)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 48,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "{'AGER_TYP', 'EXTSEL992'}"
      ]
     },
     "execution_count": 48,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "set(removed_cols).intersection(set(cols_to_keep))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "========================================================================================"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 49,
   "metadata": {},
   "outputs": [],
   "source": [
    "save_list(cols_to_keep,\"cols_to_keep\")\n",
    "save_list(removed_cols,\"cols_removed\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 50,
   "metadata": {},
   "outputs": [],
   "source": [
    "azdias.to_csv(\"azdias_ver1.csv\",index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 51,
   "metadata": {},
   "outputs": [],
   "source": [
    "customers.to_csv(\"customers_ver1.csv\",index=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Now we should restart kernel for memory shortage "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "# import libraries here; add more as necessary\n",
    "import numpy as np\n",
    "import pandas as pd\n",
    "import matplotlib.pyplot as plt\n",
    "import seaborn as sns\n",
    "from tools import*\n",
    "# magic word for producing visualizations in notebook\n",
    "%matplotlib inline"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/home/shapiro/anaconda3/lib/python3.7/site-packages/IPython/core/interactiveshell.py:3146: DtypeWarning: Columns (19) have mixed types.Specify dtype option on import or set low_memory=False.\n",
      "  interactivity=interactivity, compiler=compiler, result=result)\n"
     ]
    }
   ],
   "source": [
    "cols_to_keep=load_list(\"cols_to_keep\")\n",
    "removed_cols=load_list(\"cols_removed\")\n",
    "azdias=pd.read_csv(\"azdias_ver1.csv\")\n",
    "customers=pd.read_csv(\"customers_ver1.csv\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "======================================================================================"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "azdias=azdias[cols_to_keep]\n",
    "customers=customers[cols_to_keep]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We can see that we dropped 10 non important columns , so we still have 358 attributes with missing values"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "['TITEL_KZ',\n",
       " 'KBA05_BAUMAX',\n",
       " 'CAMEO_DEUG_2015',\n",
       " 'AGER_TYP',\n",
       " 'KK_KUNDENTYP',\n",
       " 'EXTSEL992',\n",
       " 'ALTER_KIND3',\n",
       " 'ALTER_KIND4',\n",
       " 'ALTER_KIND2',\n",
       " 'ALTER_KIND1']"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "removed_cols"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "For a categorical value we can replace with median , and for numeric variables we can replace with mean "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [],
   "source": [
    "#loading the description excel sheet\n",
    "# here we are making a multi index of of the value and the name of the column \n",
    "description_full = pd.read_excel(\"DIAS Attributes - Values 2017.xlsx\",sheet_name=\"Tabelle1\",header=1,index_col=[1],keep_default_na=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "{'ANZ_HAUSHALTE_AKTIV',\n",
       " 'ANZ_HH_TITEL',\n",
       " 'ANZ_PERSONEN',\n",
       " 'ANZ_TITEL',\n",
       " 'GEBURTSJAHR',\n",
       " 'KBA13_ANZAHL_PKW',\n",
       " 'MIN_GEBAEUDEJAHR'}"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#Knowing which has a numerical or catgorical variable from the description csv file\n",
    "numeric=list(description_full[description_full.Meaning.str.match('.*numeric.*')].index)\n",
    "numeric=set(numeric) - set(removed_cols)\n",
    "numeric"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [],
   "source": [
    "#categorical \n",
    "categorical=list(set(azdias.columns) - set(numeric))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "358"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "len(numeric) + len(categorical)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Let's do further exploration to see if we missed any string columns that cannot be passed to imputer instance"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Couldn't convert EINGEFUEGT_AM column in customers\n",
      "Couldn't convert CAMEO_INTL_2015 column in customers\n",
      "Couldn't convert CAMEO_DEU_2015 column in customers\n",
      "Couldn't convert D19_LETZTER_KAUF_BRANCHE column in customers\n",
      "Couldn't convert OST_WEST_KZ column in customers\n",
      "Couldn't convert EINGEFUEGT_AM column in azdias \n",
      "Couldn't convert CAMEO_INTL_2015 column in azdias \n",
      "Couldn't convert CAMEO_DEU_2015 column in azdias \n",
      "Couldn't convert D19_LETZTER_KAUF_BRANCHE column in azdias \n",
      "Couldn't convert OST_WEST_KZ column in azdias \n"
     ]
    }
   ],
   "source": [
    "# Before casting to float we should first fill NAs with any temporary value to make it possible to cast since we cannot do any operation with nans \n",
    "string_cols_cust=set()\n",
    "string_cols_azd=set()\n",
    "#iterating over the customers columns \n",
    "for col in customers.columns:\n",
    "    \n",
    "    try :\n",
    "        customers[col].fillna(0).astype(float)\n",
    "    except:\n",
    "        print(\"Couldn't convert {} column in customers\".format(col))\n",
    "        string_cols_cust.add(col)\n",
    "\n",
    "#another iteration in the azdias column since maybe there will be an entry error in azdias but not in customers        \n",
    "for col in azdias.columns:\n",
    "    try :\n",
    "        azdias[col].fillna(0,).astype(float)\n",
    "    except:\n",
    "        print(\"Couldn't convert {} column in azdias \".format(col))\n",
    "        string_cols_azd.add(col)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Lets compare both sets "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(5, 5)"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "len(string_cols_azd),len(string_cols_cust)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Let's Manually Check those columns "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Checking in Customers Dataframe**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Column OST_WEST_KZ\n",
      "Values that cannot be parsed to Float ['O', 'W']\n",
      "NaNs = 49927\n",
      "Sample of values = ['W', 'O']\n",
      "Propotion  of NaNs = 0.26050863022561727\n",
      "==========================================\n",
      "Column CAMEO_DEU_2015\n",
      "Values that cannot be parsed to Float ['3D', '4B', '5C', '4C', '7A', '8A', '5A', '2D', '9D', '8D']\n",
      "NaNs = 50554\n",
      "Sample of values = ['6D', '8C']\n",
      "Propotion  of NaNs = 0.2637801849184981\n",
      "==========================================\n",
      "Column D19_LETZTER_KAUF_BRANCHE\n",
      "Values that cannot be parsed to Float ['D19_HANDWERK', 'D19_SONSTIGE', 'D19_DROGERIEARTIKEL', 'D19_KINDERARTIKEL', 'D19_UNBEKANNT', 'D19_LEBENSMITTEL', 'D19_VOLLSORTIMENT', 'D19_DIGIT_SERV', 'D19_SAMMELARTIKEL', 'D19_TELKO_MOBILE']\n",
      "NaNs = 47697\n",
      "Sample of values = ['D19_BUCH_CD', 'D19_SONSTIGE']\n",
      "Propotion  of NaNs = 0.24887295723498842\n",
      "==========================================\n",
      "Column EINGEFUEGT_AM\n",
      "Values that cannot be parsed to Float ['1998-07-01 00:00:00', '2001-08-15 00:00:00', '2009-07-29 00:00:00', '2005-04-22 00:00:00', '2002-05-02 00:00:00', '2013-12-12 00:00:00', '2009-04-06 00:00:00', '2000-11-27 00:00:00', '2013-02-04 00:00:00', '2008-12-03 00:00:00']\n",
      "NaNs = 49927\n",
      "Sample of values = ['2002-04-02 00:00:00', '2014-10-12 00:00:00']\n",
      "Propotion  of NaNs = 0.26050863022561727\n",
      "==========================================\n",
      "Column CAMEO_INTL_2015\n",
      "Values that cannot be parsed to Float ['XX']\n",
      "NaNs = 50428\n",
      "Sample of values = ['55', '23']\n",
      "Propotion  of NaNs = 0.26312274330557467\n",
      "==========================================\n"
     ]
    }
   ],
   "source": [
    "summarize_columns(string_cols_cust,customers)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "- Tt's more appropriate  to drop `EINGEFUEGT_AM` since it have dates and it will be inconsistent to impute the median date, also it's missing more than 26% of it's values\n",
    "- Some NaNs in `CAMEO_INTL_2015` and `CAMEO_DEU_2015` are encoded with \"XX\" ( Should be Changed to np.nan )"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Checking in Customers Dataframe**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [],
   "source": [
    "string_cols_azd.remove(\"EINGEFUEGT_AM\")\n",
    "string_cols_cust.remove(\"EINGEFUEGT_AM\")\n",
    "categorical.remove(\"EINGEFUEGT_AM\")\n",
    "customers.drop(columns=\"EINGEFUEGT_AM\",inplace=True)\n",
    "azdias.drop(columns=\"EINGEFUEGT_AM\",inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [],
   "source": [
    "customers.CAMEO_INTL_2015.replace(\"XX\",np.nan,inplace=True)\n",
    "azdias.CAMEO_INTL_2015.replace(\"XX\",np.nan,inplace=True)\n",
    "customers.CAMEO_DEU_2015.replace(\"XX\",np.nan,inplace=True)\n",
    "azdias.CAMEO_DEU_2015.replace(\"XX\",np.nan,inplace=True)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Checking in Azdias Dataframe**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Column OST_WEST_KZ\n",
      "Values that cannot be parsed to Float ['O', 'W']\n",
      "NaNs = 93148\n",
      "Sample of values = ['W', 'O']\n",
      "Propotion  of NaNs = 0.10451728583594866\n",
      "==========================================\n",
      "Column CAMEO_DEU_2015\n",
      "Values that cannot be parsed to Float ['3D', '5C', '4B', '4C', '7A', '8A', '5A', '2D', '9D', '8D']\n",
      "NaNs = 99352\n",
      "Sample of values = ['6D', '5F']\n",
      "Propotion  of NaNs = 0.11147852216229195\n",
      "==========================================\n",
      "Column D19_LETZTER_KAUF_BRANCHE\n",
      "Values that cannot be parsed to Float ['D19_HANDWERK', 'D19_SONSTIGE', 'D19_DROGERIEARTIKEL', 'D19_KINDERARTIKEL', 'D19_UNBEKANNT', 'D19_VOLLSORTIMENT', 'D19_LEBENSMITTEL', 'D19_DIGIT_SERV', 'D19_SAMMELARTIKEL', 'D19_TELKO_MOBILE']\n",
      "NaNs = 257113\n",
      "Sample of values = ['D19_KINDERARTIKEL', 'D19_BIO_OEKO']\n",
      "Propotion  of NaNs = 0.2884952217239046\n",
      "==========================================\n",
      "Column CAMEO_INTL_2015\n",
      "Values that cannot be parsed to Float []\n",
      "NaNs = 99352\n",
      "Sample of values = [24.0, 55.0]\n",
      "Propotion  of NaNs = 0.11147852216229195\n",
      "==========================================\n"
     ]
    }
   ],
   "source": [
    "summarize_columns(string_cols_azd,azdias)\n",
    "    "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "357"
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "len(numeric)+ len(categorical)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(357, 357)"
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "azdias.shape[1],customers.shape[1]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Checking the categorical "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Column OST_WEST_KZ\n",
      "Values that cannot be parsed ['O', 'W']\n",
      "NaNs = 49927\n",
      "Sample of values = ['W' 'W' 'W' 'W' 'W' 'O' 'W' 'W' 'W' 'W' 'W' 'W' 'W' 'W' 'W' 'W' 'W' 'W'\n",
      " 'O' 'W']\n",
      "Propotion  of NaNs = 0.26050863022561727\n",
      "==========================================\n",
      "Column CAMEO_DEU_2015\n",
      "Values that cannot be parsed ['3D', '4B', '5C', '4C', '7A', '8A', '5A', '2D', '9D', '8D']\n",
      "NaNs = 50554\n",
      "Sample of values = ['5D' '8A' '1D' '2D' '9A' '2D' '4A' '3D' '3B' '4C' '2C' '4C' '4C' '2D'\n",
      " '9E' '4D' '5A' '3D' '3D' '4A']\n",
      "Propotion  of NaNs = 0.2637801849184981\n",
      "==========================================\n",
      "Column D19_LETZTER_KAUF_BRANCHE\n",
      "Values that cannot be parsed ['D19_HANDWERK', 'D19_SONSTIGE', 'D19_DROGERIEARTIKEL', 'D19_KINDERARTIKEL', 'D19_UNBEKANNT', 'D19_LEBENSMITTEL', 'D19_VOLLSORTIMENT', 'D19_DIGIT_SERV', 'D19_SAMMELARTIKEL', 'D19_TELKO_MOBILE']\n",
      "NaNs = 47697\n",
      "Sample of values = ['D19_SONSTIGE' 'D19_DROGERIEARTIKEL' 'D19_BEKLEIDUNG_GEH'\n",
      " 'D19_BEKLEIDUNG_REST' 'D19_BEKLEIDUNG_GEH' 'D19_BUCH_CD' 'D19_FREIZEIT'\n",
      " 'D19_BUCH_CD' 'D19_UNBEKANNT' 'D19_VERSICHERUNGEN' 'D19_BEKLEIDUNG_GEH'\n",
      " 'D19_SONSTIGE' 'D19_DROGERIEARTIKEL' 'D19_UNBEKANNT'\n",
      " 'D19_BEKLEIDUNG_REST' 'D19_SONSTIGE' 'D19_SONSTIGE' 'D19_SCHUHE'\n",
      " 'D19_BEKLEIDUNG_REST' 'D19_UNBEKANNT']\n",
      "Propotion  of NaNs = 0.24887295723498842\n",
      "==========================================\n"
     ]
    }
   ],
   "source": [
    "summarize_columns(categorical,customers,only_uncasted=True,samples=20)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [],
   "source": [
    "save_list(string_cols_azd,\"string_cols_azd\")\n",
    "save_list(string_cols_cust,\"string_cols_cust\")\n",
    "save_list(categorical,\"cat1\")\n",
    "save_list(numeric,\"num1\")\n",
    "#load numeric and categorical after\n",
    "azdias.to_csv(\"azdias_ver2.csv\",index=False)\n",
    "customers.to_csv(\"customers_ver2.csv\",index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "((891221, 357), (77283, 357))"
      ]
     },
     "execution_count": 20,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "azdias.shape,azdias.dropna().shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "((191652, 357), (60136, 357))"
      ]
     },
     "execution_count": 21,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "customers.shape,customers.dropna().shape"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Now Restarting Kernel "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "# import libraries here; add more as necessary\n",
    "import numpy as np\n",
    "import pandas as pd\n",
    "import matplotlib.pyplot as plt\n",
    "import seaborn as sns\n",
    "from tools import*\n",
    "# magic word for producing visualizations in notebook\n",
    "%matplotlib inline"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "string_cols_azd=load_list(\"string_cols_azd\")\n",
    "string_cols_cust=load_list(\"string_cols_cust\")\n",
    "categorical=load_list(\"cat1\")\n",
    "numeric=load_list(\"num1\")\n",
    "azdias=pd.read_csv(\"azdias_ver2.csv\")\n",
    "customers=pd.read_csv(\"customers_ver2.csv\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "=============================================="
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "from sklearn.linear_model import BayesianRidge\n",
    "num_estimator=BayesianRidge()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "{'CAMEO_DEU_2015', 'OST_WEST_KZ'}"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "string_cols_azd"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "{'CAMEO_DEU_2015', 'OST_WEST_KZ'}"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "string_cols_cust"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Those above columns needs to be encoded before imputing them , but let's first check thier meaning to select and apropiate encoder whether ordinal or label "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [],
   "source": [
    "#loading the description excel sheet\n",
    "# here we are making a multi index of of the value and the name of the column \n",
    "description_full = pd.read_excel(\"DIAS Attributes - Values 2017.xlsx\",sheet_name=\"Tabelle1\",header=1,index_col=[1],keep_default_na=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "{'CAMEO_DEU_2015', 'OST_WEST_KZ'}\n",
      "CAMEO classification 2015 - detailled classification\n",
      "flag indicating the former GDR/FRG\n"
     ]
    }
   ],
   "source": [
    "explainable=set(description_full.index).intersection(string_cols_azd)\n",
    "print(explainable)\n",
    "for i in explainable:\n",
    "    print(description_full.loc[i,\"Description\"].values[0])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Column CAMEO_INTL_2015\n",
      "Values that cannot be parsed to Float []\n",
      "NaNs = 99352\n",
      "Sample of values = [12.0, 35.0, 51.0, 22.0, 23.0]\n",
      "Propotion  of NaNs = 0.11147852216229195\n",
      "==========================================\n",
      "Column D19_LETZTER_KAUF_BRANCHE\n",
      "Values that cannot be parsed to Float ['D19_KINDERARTIKEL', 'D19_NAHRUNGSERGAENZUNG', 'D19_UNBEKANNT', 'D19_SCHUHE', 'D19_VERSAND_REST', 'D19_HAUS_DEKO', 'D19_BEKLEIDUNG_REST', 'D19_DROGERIEARTIKEL', 'D19_GARTEN', 'D19_REISEN']\n",
      "NaNs = 257113\n",
      "Sample of values = ['D19_NAHRUNGSERGAENZUNG', 'D19_FREIZEIT', 'D19_WEIN_FEINKOST', 'D19_BANKEN_DIREKT', 'D19_HAUS_DEKO']\n",
      "Propotion  of NaNs = 0.2884952217239046\n",
      "==========================================\n",
      "Column CAMEO_DEU_2015\n",
      "Values that cannot be parsed to Float ['4B', '1D', '7D', '1B', '7A', '8B', '6F', '2B', '5A', '4D']\n",
      "NaNs = 99352\n",
      "Sample of values = ['1D', '9C', '5F', '6B', '2B']\n",
      "Propotion  of NaNs = 0.11147852216229195\n",
      "==========================================\n",
      "Column OST_WEST_KZ\n",
      "Values that cannot be parsed to Float ['W', 'O']\n",
      "NaNs = 93148\n",
      "Sample of values = ['O', 'W']\n",
      "Propotion  of NaNs = 0.10451728583594866\n",
      "==========================================\n"
     ]
    }
   ],
   "source": [
    "summarize_columns(['CAMEO_INTL_2015','D19_LETZTER_KAUF_BRANCHE',\"CAMEO_DEU_2015\",\"OST_WEST_KZ\"],azdias)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We can see from above that CAMEO_INTL_2015 is now a numerical column after removing unknown encoding of 'XX' from it. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "numeric.add(\"CAMEO_INTL_2015\")\n",
    "categorical.remove(\"CAMEO_INTL_2015\")\n",
    "save_list(numeric,\"num1\")\n",
    "save_list(categorical,\"cat1\")\n",
    "string_cols_azd.remove(\"CAMEO_INTL_2015\")\n",
    "string_cols_cust.remove(\"CAMEO_INTL_2015\")\n",
    "save_list(string_cols_azd,\"string_cols_azd\")\n",
    "save_list(string_cols_azd,\"string_cols_cust\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Column CAMEO_DEU_2015\n",
      "Values that cannot be parsed to Float ['4B', '1D', '7D', '1B', '7A', '8B', '6F', '2B', '5A', '4D']\n",
      "NaNs = 99352\n",
      "Sample of values = ['5E', '5C', '9A', '9B', '2C']\n",
      "Propotion  of NaNs = 0.11147852216229195\n",
      "==========================================\n",
      "Column OST_WEST_KZ\n",
      "Values that cannot be parsed to Float ['W', 'O']\n",
      "NaNs = 93148\n",
      "Sample of values = ['O', 'W']\n",
      "Propotion  of NaNs = 0.10451728583594866\n",
      "==========================================\n"
     ]
    }
   ],
   "source": [
    "summarize_columns(string_cols_azd,azdias)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Column CAMEO_DEU_2015\n",
      "Values that cannot be parsed to Float ['4B', '1D', '7D', '1B', '7A', '8B', '6F', '2B', '5A', '4D']\n",
      "NaNs = 50554\n",
      "Sample of values = ['7B', '8A', '4A', '8D', '3D']\n",
      "Propotion  of NaNs = 0.2637801849184981\n",
      "==========================================\n",
      "Column OST_WEST_KZ\n",
      "Values that cannot be parsed to Float ['W', 'O']\n",
      "NaNs = 49927\n",
      "Sample of values = ['O', 'W']\n",
      "Propotion  of NaNs = 0.26050863022561727\n",
      "==========================================\n"
     ]
    }
   ],
   "source": [
    "summarize_columns(string_cols_cust,customers)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We can remove the D19_LETZTER_KAUF_BRANCHE column because it's just a comination of other columns name and also 25% of it's values are missing and it's not a small ratio."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [],
   "source": [
    "azdias.drop(columns=\"D19_LETZTER_KAUF_BRANCHE\",inplace=True)\n",
    "customers.drop(columns=\"D19_LETZTER_KAUF_BRANCHE\",inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "categorical.remove(\"D19_LETZTER_KAUF_BRANCHE\")\n",
    "save_list(numeric,\"num1\")\n",
    "save_list(categorical,\"cat1\")\n",
    "string_cols_azd.remove(\"D19_LETZTER_KAUF_BRANCHE\")\n",
    "string_cols_cust.remove(\"D19_LETZTER_KAUF_BRANCHE\")\n",
    "save_list(string_cols_azd,\"string_cols_azd\")\n",
    "save_list(string_cols_azd,\"string_cols_cust\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Now we can see we're only left with two string categorical columns , which are `OST_WEST_KZ` and `CAMEO_DEU_2015`"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "As we saw above , we can encode them ordinaly to impute thier missing values , and after then we will decode them and one hot encode them later on"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "But column `OST_WEST_KZ` encodes are easy and don't need a OrdinalEncoder for them since they've only two levels. \n",
    "'W' , 'O'. \n",
    "- 'W' will be 1\n",
    "- 'O' will be 0"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [],
   "source": [
    "azdias.OST_WEST_KZ.replace(\"W\",1,inplace=True)\n",
    "azdias.OST_WEST_KZ.replace(\"O\",0,inplace=True)\n",
    "customers.OST_WEST_KZ.replace(\"W\",1,inplace=True)\n",
    "customers.OST_WEST_KZ.replace(\"O\",0,inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [],
   "source": [
    "from sklearn.preprocessing import OrdinalEncoder"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [],
   "source": [
    "encoder=OrdinalEncoder()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We have replace NANS first for the ordinal to be fitted"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [],
   "source": [
    "azdias.CAMEO_DEU_2015.replace(np.nan,\"NULL\",inplace=True)\n",
    "customers.CAMEO_DEU_2015.replace(np.nan,\"NULL\",inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "OrdinalEncoder()"
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "encoder.fit(azdias.CAMEO_DEU_2015.values.reshape(-1,1))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array([[44.]])"
      ]
     },
     "execution_count": 19,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "encoder.transform(np.array(\"NULL\").reshape(1,-1))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "we can see that NULL has the value of 44 , let's keep that in mind to change it back again to nan "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array([['NULL'],\n",
       "       ['8A'],\n",
       "       ['4C'],\n",
       "       ...,\n",
       "       ['4C'],\n",
       "       ['9D'],\n",
       "       ['6B']], dtype=object)"
      ]
     },
     "execution_count": 20,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "azdias.CAMEO_DEU_2015.values.reshape(-1,1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array([44., 35., 15., ..., 15., 42., 25.])"
      ]
     },
     "execution_count": 21,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "new_CAMEO_DEU_2015_azd=encoder.transform(azdias.CAMEO_DEU_2015.values.reshape(-1,1)).squeeze()\n",
    "new_CAMEO_DEU_2015_azd\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array([['1A'],\n",
       "       ['NULL'],\n",
       "       ['5D'],\n",
       "       ...,\n",
       "       ['4D'],\n",
       "       ['4C'],\n",
       "       ['5C']], dtype=object)"
      ]
     },
     "execution_count": 22,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "customers.CAMEO_DEU_2015.values.reshape(-1,1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array([ 0., 44., 21., ..., 16., 15., 20.])"
      ]
     },
     "execution_count": 23,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "new_CAMEO_DEU_2015_cust=encoder.transform(customers.CAMEO_DEU_2015.values.reshape(-1,1)).squeeze()\n",
    "new_CAMEO_DEU_2015_cust"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {},
   "outputs": [],
   "source": [
    "azdias.loc[:,\"CAMEO_DEU_2015\"]=new_CAMEO_DEU_2015_azd\n",
    "customers.loc[:,\"CAMEO_DEU_2015\"]=new_CAMEO_DEU_2015_cust"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(0, 0)"
      ]
     },
     "execution_count": 25,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "azdias.CAMEO_DEU_2015.isna().sum(),customers.CAMEO_DEU_2015.isna().sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {},
   "outputs": [],
   "source": [
    "azdias.CAMEO_DEU_2015.replace(44,np.nan,inplace=True)\n",
    "customers.CAMEO_DEU_2015.replace(44,np.nan,inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(99352, 50554)"
      ]
     },
     "execution_count": 27,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "azdias.CAMEO_DEU_2015.isna().sum(),customers.CAMEO_DEU_2015.isna().sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pickle as pk "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "metadata": {},
   "outputs": [],
   "source": [
    "pk.dump(encoder,open(\"CAMEO_DEU_2015_ENC.pkl\",\"wb\"))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "metadata": {},
   "outputs": [],
   "source": [
    "from sklearn.experimental import enable_iterative_imputer\n",
    "from sklearn.impute import IterativeImputer\n",
    "from sklearn.linear_model import BayesianRidge\n",
    "estimator=BayesianRidge()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "metadata": {},
   "outputs": [],
   "source": [
    "imp_mean=IterativeImputer(estimator=estimator,initial_strategy=\"mean\",missing_values=np.nan)  #mean imputer for numeric values \n",
    "imp_mode=IterativeImputer(estimator=estimator,initial_strategy=\"most_frequent\",missing_values=np.nan) #mode imputer for categorical variable"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Before imputing NaNs lets change string categorical variables to strings"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "metadata": {},
   "outputs": [],
   "source": [
    "customers.loc[:,string_cols_cust]=customers[string_cols_cust].astype(str).replace(\"nan\",np.nan)\n",
    "azdias.loc[:,string_cols_azd]=azdias[string_cols_azd].astype(str).replace(\"nan\",np.nan)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "And now for the imputing step , since our dataset has a lot of observations and a lot of columns , we will first impute the NaNs in the numeric columns at once , after that we will impute those for categorical columns but taking 10% of columns at a time for 10 times for our memory to withstand this expensive calculations "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Customers NaNs , Before 13372697 , After Numeric NaNs imputing 13025706\n",
      "Population NaNs, Before 31579853 , After Numeric NaNs imputing 30944399\n"
     ]
    }
   ],
   "source": [
    "cust_nan_before=customers.isna().sum().sum() #calculating missing values in customers dataframe before imputing \n",
    "azd_nan_before=azdias.isna().sum().sum() #calculating missing values in Azdias dataframe before imputing \n",
    "\n",
    "customers.loc[:,numeric]=imp_mean.fit_transform(customers[numeric])\n",
    "\n",
    "azdias.loc[:,numeric]=imp_mean.fit_transform(azdias[numeric])\n",
    "\n",
    "cust_nan_after_numeric=customers.isna().sum().sum() #calculating missing values in customers dataframe After imputing \n",
    "azd_nan_after_numeric=azdias.isna().sum().sum() #calculating missing values in Azdias dataframe After imputing \n",
    "print(\"Customers NaNs , Before {} , After Numeric NaNs imputing {}\".format(cust_nan_before,cust_nan_after_numeric))\n",
    "print(\"Population NaNs, Before {} , After Numeric NaNs imputing {}\".format(azd_nan_before,azd_nan_after_numeric))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "metadata": {},
   "outputs": [],
   "source": [
    "batches=[]\n",
    "for x in range(0,len(categorical),20):\n",
    "    batches.append(categorical[x:x+20])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "20\n",
      "20\n",
      "20\n",
      "20\n",
      "20\n",
      "20\n",
      "20\n",
      "20\n",
      "20\n",
      "20\n",
      "20\n",
      "20\n",
      "20\n",
      "20\n",
      "20\n",
      "20\n",
      "20\n",
      "8\n"
     ]
    }
   ],
   "source": [
    "s=0\n",
    "for i in batches:\n",
    "    print(len(i))\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/home/shapiro/anaconda3/lib/python3.7/site-packages/sklearn/impute/_iterative.py:670: ConvergenceWarning: [IterativeImputer] Early stopping criterion not reached.\n",
      "  \" reached.\", ConvergenceWarning)\n",
      "/home/shapiro/anaconda3/lib/python3.7/site-packages/sklearn/impute/_iterative.py:670: ConvergenceWarning: [IterativeImputer] Early stopping criterion not reached.\n",
      "  \" reached.\", ConvergenceWarning)\n",
      "/home/shapiro/anaconda3/lib/python3.7/site-packages/sklearn/impute/_iterative.py:670: ConvergenceWarning: [IterativeImputer] Early stopping criterion not reached.\n",
      "  \" reached.\", ConvergenceWarning)\n",
      "/home/shapiro/anaconda3/lib/python3.7/site-packages/sklearn/impute/_iterative.py:670: ConvergenceWarning: [IterativeImputer] Early stopping criterion not reached.\n",
      "  \" reached.\", ConvergenceWarning)\n",
      "/home/shapiro/anaconda3/lib/python3.7/site-packages/sklearn/impute/_iterative.py:670: ConvergenceWarning: [IterativeImputer] Early stopping criterion not reached.\n",
      "  \" reached.\", ConvergenceWarning)\n",
      "/home/shapiro/anaconda3/lib/python3.7/site-packages/sklearn/impute/_iterative.py:670: ConvergenceWarning: [IterativeImputer] Early stopping criterion not reached.\n",
      "  \" reached.\", ConvergenceWarning)\n",
      "/home/shapiro/anaconda3/lib/python3.7/site-packages/sklearn/impute/_iterative.py:670: ConvergenceWarning: [IterativeImputer] Early stopping criterion not reached.\n",
      "  \" reached.\", ConvergenceWarning)\n",
      "/home/shapiro/anaconda3/lib/python3.7/site-packages/sklearn/impute/_iterative.py:670: ConvergenceWarning: [IterativeImputer] Early stopping criterion not reached.\n",
      "  \" reached.\", ConvergenceWarning)\n",
      "/home/shapiro/anaconda3/lib/python3.7/site-packages/sklearn/impute/_iterative.py:670: ConvergenceWarning: [IterativeImputer] Early stopping criterion not reached.\n",
      "  \" reached.\", ConvergenceWarning)\n",
      "/home/shapiro/anaconda3/lib/python3.7/site-packages/sklearn/impute/_iterative.py:670: ConvergenceWarning: [IterativeImputer] Early stopping criterion not reached.\n",
      "  \" reached.\", ConvergenceWarning)\n"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Customers NaNs , Before 13372697 , After Numeric NaNs imputing 0\n",
      "Population NaNs, Before 31579853 , After Numeric NaNs imputing 0\n",
      "CPU times: user 4h 30min 38s, sys: 1h 28min 36s, total: 5h 59min 14s\n",
      "Wall time: 41min 28s\n"
     ]
    }
   ],
   "source": [
    "%%time\n",
    "for batch in batches:\n",
    "    \n",
    "    customers.loc[:,batch]=imp_mode.fit_transform(customers[batch])\n",
    "    azdias.loc[:,batch]=imp_mode.fit_transform(azdias[batch])\n",
    "\n",
    "cust_nan_after_all=customers.isna().sum().sum() #calculating missing values in customers dataframe After imputing \n",
    "azd_nan_after_all=azdias.isna().sum().sum() #calculating missing values in Azdias dataframe After imputing \n",
    "print(\"Customers NaNs , Before {} , After Numeric NaNs imputing {}\".format(cust_nan_before,cust_nan_after_all))\n",
    "print(\"Population NaNs, Before {} , After Numeric NaNs imputing {}\".format(azd_nan_before,azd_nan_after_all))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 41,
   "metadata": {},
   "outputs": [],
   "source": [
    "azdias.to_csv(\"azdias_nona.csv\",index=False)\n",
    "customers.to_csv(\"customers_nona.csv\",index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0"
      ]
     },
     "execution_count": 37,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "customers.isna().sum().sum() "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0"
      ]
     },
     "execution_count": 38,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "azdias.isna().sum().sum() "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Now we can see that both of our datasets don't contain any NaNs "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [],
   "source": [
    "def string_columns(df):\n",
    "    cols=[]\n",
    "    for col in df.columns:\n",
    "        for value in df[col].values:\n",
    "            try:\n",
    "                float(value)\n",
    "            except:\n",
    "                cols.append(col)\n",
    "                break\n",
    "    return cols"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 40,
   "metadata": {},
   "outputs": [],
   "source": [
    "string_cols_customers=string_columns(customers)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 42,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[]"
      ]
     },
     "execution_count": 42,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "string_cols_customers"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 43,
   "metadata": {},
   "outputs": [],
   "source": [
    "string_cols_azdias=string_columns(azdias)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 44,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[]"
      ]
     },
     "execution_count": 44,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "string_cols_azdias"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Now let's decode the columns we already encoded before imputing which are `CAMEO_DEU_2015` and `OST_WEST_KZ`"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 45,
   "metadata": {},
   "outputs": [],
   "source": [
    "import seaborn as sns "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 46,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "<seaborn.axisgrid.FacetGrid at 0x7f80896d0b50>"
      ]
     },
     "execution_count": 46,
     "metadata": {},
     "output_type": "execute_result"
    },
    {
     "data": {
      "image/png": "iVBORw0KGgoAAAANSUhEUgAAAWAAAAFgCAYAAACFYaNMAAAAOXRFWHRTb2Z0d2FyZQBNYXRwbG90bGliIHZlcnNpb24zLjMuMSwgaHR0cHM6Ly9tYXRwbG90bGliLm9yZy/d3fzzAAAACXBIWXMAAAsTAAALEwEAmpwYAAAcYUlEQVR4nO3df7RdZX3n8fdHIkjVxAQDQwkusGRagdZfKaJ2XNbMQGo7xc6gZsaWtKXNqqWu2s501HbN0JEyI2usWDpKF6ORH/2BlOKAVmQyoda2IhBbKyJliFIhhUI0mIu2UIPf+WM/V06uNzcnl5w8l+T9Wuusc8537+c5z2PIJ9vn7L1PqgpJ0v73lN4DkKSDlQEsSZ0YwJLUiQEsSZ0YwJLUyaLeA1go1qxZUx/72Md6D0PSgSmzFT0Cbr785S/3HoKkg4wBLEmdGMCS1IkBLEmdGMCS1IkBLEmdGMCS1IkBLEmdGMCS1IkBLEmdGMCS1IkBLEmdGMCS1IkBLEljqCp27NjBvvwdTQNYksYwNTXF69/1YaampvZZnwawJI3pqYc/fZ/2ZwBLUicGsCR1YgBLUicGsCR1YgBLUicGsCR1YgBLUicGsCR1MtEATvKsJFcn+ZskdyR5aZJlSTYmuas9Lx3Z/21JtiS5M8npI/UXJ7mtbbsoSVr9sCQfbPWbkxw30mZd+4y7kqyb5DwlaT4mfQT8W8DHqup7gOcDdwBvBTZV1UpgU3tPkhOBtcBJwBrgvUkOaf1cDKwHVrbHmlY/G3ioqk4ALgQuaH0tA84FXgKcApw7GvSStBBMLICTLAZeAbwfoKr+qaq+CpwBXNZ2uwx4TXt9BnBlVT1aVXcDW4BTkhwNLK6qm2q4C8blM9pM93U1sLodHZ8ObKyq7VX1ELCRx0NbkhaESR4BPxfYBnwgyV8leV+SpwNHVdX9AO35yLb/McC9I+23ttox7fXM+i5tqmonsAM4Yo6+JGnBmGQALwJeBFxcVS8Evk5bbtiNzFKrOerzbfP4Bybrk2xOsnnbtm1zDE2S9r1JBvBWYGtV3dzeX80QyA+0ZQXa84Mj+x870n4FcF+rr5ilvkubJIuAJcD2OfraRVVdUlWrqmrV8uXL5zlNSZqfiQVwVf09cG+S726l1cDngeuA6bMS1gHXttfXAWvbmQ3HM3zZdktbpng4yaltffesGW2m+zoTuLGtE98AnJZkafvy7bRWk6QFY9GE+38T8HtJDgW+CPwUQ+hfleRs4B7gtQBVdXuSqxhCeidwTlU91vp5I3ApcDhwfXvA8AXfFUm2MBz5rm19bU9yHnBr2+/tVbV9khOVpL010QCuqs8Aq2bZtHo3+58PnD9LfTNw8iz1R2gBPsu2DcCGvRiuJO1XXgknSZ0YwJLUiQEsSZ0YwJLUiQEsSZ0YwJLUiQEsSZ0YwJLUiQEsSZ0YwJLUiQEsSZ0YwJLUiQEsSZ0YwJLUiQEsSZ0YwJLUiQEsSZ0YwJLUiQEsSZ0YwJLUiQEsSZ0YwJLUiQEsSZ0YwJLUiQEsSZ0YwJLUiQEsSZ0YwJLUiQEsSZ0YwJLUiQEsSZ0YwJLUiQEsSZ0YwJLUiQEsSZ0YwJLUiQEsSZ0YwJLUyUQDOMnfJrktyWeSbG61ZUk2JrmrPS8d2f9tSbYkuTPJ6SP1F7d+tiS5KEla/bAkH2z1m5McN9JmXfuMu5Ksm+Q8JWk+9scR8A9W1QuqalV7/1ZgU1WtBDa19yQ5EVgLnASsAd6b5JDW5mJgPbCyPda0+tnAQ1V1AnAhcEHraxlwLvAS4BTg3NGgl6SFoMcSxBnAZe31ZcBrRupXVtWjVXU3sAU4JcnRwOKquqmqCrh8Rpvpvq4GVrej49OBjVW1vaoeAjbyeGhL0oIw6QAu4P8k+XSS9a12VFXdD9Cej2z1Y4B7R9pubbVj2uuZ9V3aVNVOYAdwxBx97SLJ+iSbk2zetm3bvCcpSfOxaML9v7yq7ktyJLAxyd/MsW9mqdUc9fm2ebxQdQlwCcCqVau+bbskTdJEj4Cr6r72/CDwIYb12AfasgLt+cG2+1bg2JHmK4D7Wn3FLPVd2iRZBCwBts/RlyQtGBML4CRPT/LM6dfAacDngOuA6bMS1gHXttfXAWvbmQ3HM3zZdktbpng4yaltffesGW2m+zoTuLGtE98AnJZkafvy7bRWk6QFY5JLEEcBH2pnjC0Cfr+qPpbkVuCqJGcD9wCvBaiq25NcBXwe2AmcU1WPtb7eCFwKHA5c3x4A7weuSLKF4ch3betre5LzgFvbfm+vqu0TnKsk7bWJBXBVfRF4/iz1rwCrd9PmfOD8WeqbgZNnqT9CC/BZtm0ANuzdqCVp//FKOEnqxACWpE4MYEnqxACWpE4MYEnqxACWpE4MYEnqxACWpE4MYEnqxACWpE4MYEnqxACWpE4MYEnqxACWpE4MYEnqxACWpE4MYEnqxACWpE4MYEnqxACWpE4MYEnqxACWpE4MYEnqxACWpE4MYEnqxACWpE4MYEnqxACWpE4MYEnqxACWpE4MYEnqxACWpE4MYEnqxACWpE4MYEnqxACWpE4MYEnqZOIBnOSQJH+V5CPt/bIkG5Pc1Z6Xjuz7tiRbktyZ5PSR+ouT3Na2XZQkrX5Ykg+2+s1Jjhtps659xl1J1k16npK0t/bHEfAvAneMvH8rsKmqVgKb2nuSnAisBU4C1gDvTXJIa3MxsB5Y2R5rWv1s4KGqOgG4ELig9bUMOBd4CXAKcO5o0EvSQjDRAE6yAvhh4H0j5TOAy9rry4DXjNSvrKpHq+puYAtwSpKjgcVVdVNVFXD5jDbTfV0NrG5Hx6cDG6tqe1U9BGzk8dCWpAVh0kfA7wb+E/DNkdpRVXU/QHs+stWPAe4d2W9rqx3TXs+s79KmqnYCO4Aj5uhrF0nWJ9mcZPO2bdvmMT1Jmr+JBXCSHwEerKpPj9tkllrNUZ9vm8cLVZdU1aqqWrV8+fIxhylJ+8Ykj4BfDvxokr8FrgReleR3gQfasgLt+cG2/1bg2JH2K4D7Wn3FLPVd2iRZBCwBts/RlyQtGBML4Kp6W1WtqKrjGL5cu7Gqfhy4Dpg+K2EdcG17fR2wtp3ZcDzDl223tGWKh5Oc2tZ3z5rRZrqvM9tnFHADcFqSpe3Lt9NaTZIWjEUdPvMdwFVJzgbuAV4LUFW3J7kK+DywEzinqh5rbd4IXAocDlzfHgDvB65IsoXhyHdt62t7kvOAW9t+b6+q7ZOemCTtjf0SwFX1ceDj7fVXgNW72e984PxZ6puBk2epP0IL8Fm2bQA2zHfMkjRpXgknSZ0YwJLUiQEsSZ0YwJLUiQEsSZ0YwJLUiQEsSZ0YwJLUiQEsSZ0YwJLUiQEsSZ0YwJLUiQEsSZ0YwJLUyVgBnOTl49QkSeMb9wj4t8esSZLGNOcN2ZO8FHgZsDzJL49sWgwcMsmBSdKBbk+/iHEo8Iy23zNH6lMMv8EmSZqnOQO4qv4U+NMkl1bVl/bTmCTpoDDub8IdluQS4LjRNlX1qkkMSpIOBuMG8B8CvwO8D3hsD/tKksYwbgDvrKqLJzoSSTrIjHsa2oeT/HySo5Msm35MdGSSdIAb9wh4XXv+lZFaAc/dt8ORpIPHWAFcVcdPeiCSdLAZK4CTnDVbvaou37fDkaSDx7hLEN8/8vppwGrgLwEDWJLmadwliDeNvk+yBLhiIiOSpIPEfG9H+Q/Ayn05EEk62Iy7BvxhhrMeYLgJz/OAqyY1KEk6GIy7BvzOkdc7gS9V1dYJjEeSDhpjLUG0m/L8DcMd0ZYC/zTJQUnSwWDcX8R4HXAL8FrgdcDNSbwdpSQ9AeMuQfwa8P1V9SBAkuXA/wWuntTAJOlAN+5ZEE+ZDt/mK3vRVpI0i3GPgD+W5AbgD9r71wMfncyQJOngsKffhDsBOKqqfiXJvwF+AAhwE/B7+2F8knTA2tMywruBhwGq6pqq+uWq+iWGo993T3ZoknRg21MAH1dVn51ZrKrNDD9PtFtJnpbkliR/neT2JP+11Zcl2Zjkrva8dKTN25JsSXJnktNH6i9OclvbdlGStPphST7Y6jcnOW6kzbr2GXclWYckLTB7CuCnzbHt8D20fRR4VVU9H3gBsCbJqcBbgU1VtRLY1N6T5ERgLXASsAZ4b5JDWl8XA+sZLn9e2bYDnA08VFUnABcCF7S+lgHnAi8BTgHOHQ16SVoI9hTAtyb52ZnFJGcDn56rYQ2+1t4+tT0KOAO4rNUvA17TXp8BXFlVj1bV3cAW4JQkRwOLq+qmqiqGO7CNtpnu62pgdTs6Ph3YWFXbq+ohYCOPh7YkLQh7OgvizcCHkryBxwN3FXAo8GN76rwdwX4aOAF4T1XdnOSoqrofoKruT3Jk2/0Y4FMjzbe22jfa65n16Tb3tr52JtkBHDFan6XN6PjWMxxZ85znPGdP05GkfWrOAK6qB4CXJflB4ORW/uOqunGczqvqMeAFSZ7FEOQnz7F7Zutijvp824yO7xLgEoBVq1Z923ZJmqRx7wf8J8CfzPdDquqrST7OsAzwQJKj29Hv0cD0BR5bgWNHmq0A7mv1FbPUR9tsTbIIWAJsb/VXzmjz8fmOX5ImYWJXsyVZ3o58SXI48C8ZbuhzHY//yOc64Nr2+jpgbTuz4XiGL9tuacsVDyc5ta3vnjWjzXRfZwI3tnXiG4DTkixtX76d1mqStGCMeyXcfBwNXNbWgZ8CXFVVH0lyE3BV+yLvHoYb/FBVtye5Cvg8wy0vz2lLGABvBC5lOPPi+vYAeD9wRZItDEe+a1tf25OcB9za9nt7VW2f4Fwlaa9NLIDb+cMvnKX+FYbflJutzfnA+bPUN/P4GvRo/RFagM+ybQOwYe9GLUn7jzfUkaRODGBJ6sQAlqRODGBJ6sQAlqRODGBJ6sQAlqRODGBJ6sQAlqRODGBJ6sQAlqRODGBJ6sQAlqRODGBJ6sQAlqRODGBJ6sQAlqRODGBJ6sQAlqRODGBJ6sQAlqRODGBJ6sQAlqRODGBJ6sQAlqRODGBJ6sQAlqRODGBJ6sQAlqRODGBJ6sQAlqRODGBJ6sQAlqRODGBJ6sQAlqRODGBJ6sQAlqROJhbASY5N8idJ7khye5JfbPVlSTYmuas9Lx1p87YkW5LcmeT0kfqLk9zWtl2UJK1+WJIPtvrNSY4babOufcZdSdZNap6SNF+TPALeCfyHqnoecCpwTpITgbcCm6pqJbCpvadtWwucBKwB3pvkkNbXxcB6YGV7rGn1s4GHquoE4ELggtbXMuBc4CXAKcC5o0EvSQvBxAK4qu6vqr9srx8G7gCOAc4ALmu7XQa8pr0+A7iyqh6tqruBLcApSY4GFlfVTVVVwOUz2kz3dTWwuh0dnw5srKrtVfUQsJHHQ1uSFoT9sgbclgZeCNwMHFVV98MQ0sCRbbdjgHtHmm1ttWPa65n1XdpU1U5gB3DEHH3NHNf6JJuTbN62bdsTmKEk7b2JB3CSZwB/BLy5qqbm2nWWWs1Rn2+bxwtVl1TVqqpatXz58jmGJkn73kQDOMlTGcL396rqmlZ+oC0r0J4fbPWtwLEjzVcA97X6ilnqu7RJsghYAmyfoy9JWjAmeRZEgPcDd1TVu0Y2XQdMn5WwDrh2pL62ndlwPMOXbbe0ZYqHk5za+jxrRpvpvs4EbmzrxDcApyVZ2r58O63VJGnBWDTBvl8O/ARwW5LPtNqvAu8ArkpyNnAP8FqAqro9yVXA5xnOoDinqh5r7d4IXAocDlzfHjAE/BVJtjAc+a5tfW1Pch5wa9vv7VW1fULzlKR5mVgAV9WfM/taLMDq3bQ5Hzh/lvpm4ORZ6o/QAnyWbRuADeOOV5L2N6+Ek6RODGBJ6sQAlqRODGBJ6sQAlqRODGBJ6sQAlqRODGBJ6sQAnqeqYseOHQxXPkvS3jOA52lqaorXv+vDTE3NdYM3Sdo9A/gJeOrhT+89BElPYgawJHViAEtSJwawJHViAEtSJwawJHViAEtSJwawJHViAEtSJwawJHViAEtSJwawJHViAEtSJwawJHViAEtSJwawJHViAEtSJwawJHViAEtSJwawJHViAEtSJwawJHViAEtSJwawJHViAEtSJwawJHViAEtSJxML4CQbkjyY5HMjtWVJNia5qz0vHdn2tiRbktyZ5PSR+ouT3Na2XZQkrX5Ykg+2+s1Jjhtps659xl1J1k1qjpL0REzyCPhSYM2M2luBTVW1EtjU3pPkRGAtcFJr894kh7Q2FwPrgZXtMd3n2cBDVXUCcCFwQetrGXAu8BLgFODc0aCXpIViYgFcVZ8Ats8onwFc1l5fBrxmpH5lVT1aVXcDW4BTkhwNLK6qm6qqgMtntJnu62pgdTs6Ph3YWFXbq+ohYCPf/g+BJHW3v9eAj6qq+wHa85Gtfgxw78h+W1vtmPZ6Zn2XNlW1E9gBHDFHX5K0oCyUL+EyS63mqM+3za4fmqxPsjnJ5m3bto01UEnaV/Z3AD/QlhVozw+2+lbg2JH9VgD3tfqKWeq7tEmyCFjCsOSxu76+TVVdUlWrqmrV8uXLn8C0JGnv7e8Avg6YPithHXDtSH1tO7PheIYv225pyxQPJzm1re+eNaPNdF9nAje2deIbgNOSLG1fvp3WapK0oCyaVMdJ/gB4JfDsJFsZzkx4B3BVkrOBe4DXAlTV7UmuAj4P7ATOqarHWldvZDij4nDg+vYAeD9wRZItDEe+a1tf25OcB9za9nt7Vc38MlCSuptYAFfVv9vNptW72f984PxZ6puBk2epP0IL8Fm2bQA2jD1YSepgoXwJJ0kHHQNYkjoxgCWpEwNYkjoxgCWpEwNYkjoxgCWpEwNYkjoxgCWpEwNYkjoxgCWpEwNYkjoxgCWpEwNY0kRVFTt27GC4XbdGGcCSJmpqaorXv+vDTE1N9R7KgmMAS5q4px7+9N5DWJAMYEnqxACWpE4MYEnqxACWpE4MYEnqxACWpE4MYEnqxACWpE4MYEnqxACWpE4MYEnqxACWpE4MYEnqxACWpE4MYEnqxACWpE4MYEnqxACWpE4MYEnqxACWpE4MYEnqxACWtF9UFTt27KCqeg9lwTigAzjJmiR3JtmS5K29xyMd6OYK2ampKV73m9dxzz338NWvftUgBhb1HsCkJDkEeA/wr4CtwK1Jrquqz/cdmXRgmC1sp6amWP/+T3DJ2a9gyZIlLF68mB07dnxre54SfvK3r2fR076DP/yPP0oSFi9eTJIeU+jugA1g4BRgS1V9ESDJlcAZwD4L4G/849d3+Y9LOpjs2LGDs971IR577Ju71Bcddjg//s5rWHTY4fz2T/4Av/C+TTz1GUuZmpriG//4D9/ab+vWrbz5ik+y4Zw1LFmyZH8Pf6/t2LGDb/zj1/dpnzlQ/29AkjOBNVX1M+39TwAvqapfGNlnPbC+vf1u4M69/JhnA1/eB8NdCJzLwnOgzAOcy5eras3M4oF8BDzb/6fZ5V+bqroEuGTeH5BsrqpV822/kDiXhedAmQc4l905kL+E2wocO/J+BXBfp7FI0rc5kAP4VmBlkuOTHAqsBa7rPCZJ+pYDdgmiqnYm+QXgBuAQYENV3b6PP2beyxcLkHNZeA6UeYBzmdUB+yWcJC10B/IShCQtaAawJHViAI9hT5c0Z3BR2/7ZJC/qMc5xjDGXN7Q5fDbJJ5M8v8c492Tcy8yTfH+Sx9p54QvSOHNJ8sokn0lye5I/3d9jHNcY/30tSfLhJH/d5vJTPca5J0k2JHkwyed2s33f/J2vKh9zPBi+wPsC8FzgUOCvgRNn7PNq4HqGc49PBW7uPe4nMJeXAUvb6x9aiHMZZx4j+90IfBQ4s/e4n8CfybMYruB8Tnt/ZO9xP4G5/CpwQXu9HNgOHNp77LPM5RXAi4DP7Wb7Pvk77xHwnn3rkuaq+idg+pLmUWcAl9fgU8Czkhy9vwc6hj3Opao+WVUPtbefYjh/eqEZ588E4E3AHwEP7s/B7aVx5vLvgWuq6h6Aqlqo8xlnLgU8M8PNH57BEMA79+8w96yqPsEwtt3ZJ3/nDeA9Owa4d+T91lbb230Wgr0d59kM/8ovNHucR5JjgB8Dfmc/jms+xvkz+efA0iQfT/LpJGftt9HtnXHm8j+B5zFcFHUb8ItV9U2efPbJ3/kD9jzgfWiPlzSPuc9CMPY4k/wgQwD/wERHND/jzOPdwFuq6rEFfqetceayCHgxsBo4HLgpyaeq6v9NenB7aZy5nA58BngV8F3AxiR/VlVTEx7bvrZP/s4bwHs2ziXNT5bLnscaZ5LvA94H/FBVfWU/jW1vjDOPVcCVLXyfDbw6yc6q+t/7ZYTjG/e/ry9X1deBryf5BPB8YKEF8Dhz+SngHTUspG5JcjfwPcAt+2eI+8y++Tvfe7F7oT8Y/pH6InA8j3+xcNKMfX6YXRfkb+k97icwl+cAW4CX9R7vE5nHjP0vZeF+CTfOn8nzgE1t3+8APgec3Hvs85zLxcCvt9dHAX8HPLv32Hczn+PY/Zdw++TvvEfAe1C7uaQ5yc+17b/D8C37qxmC6x8Y/pVfcMacy38BjgDe244ed9YCu4vVmPN4UhhnLlV1R5KPAZ8Fvgm8r6pmPT2qpzH/XM4DLk1yG0N4vaWqFtxtKpP8AfBK4NlJtgLnAk+Ffft33kuRJakTz4KQpE4MYEnqxACWpE4MYEnqxACWpE4MYEnqxPOA9aSSZAXwHuBEhgOIjwC/wvDf8v8Cvo/h/NKvAm8Arm1N/xnwGLCtvT+lhhvGjPZ9IfClqnp3e38DcG9V/Ux7/5sMFw5cA9wB3DnS/F1VdXmSnwZ+ieGy1KcAvwacBryc4eKE40fa/UZVXT3LHC8FPlJVVydZxnARxkUMF2H87Miui4CTGO44dsfc/8tpITKA9aTR7qB1DXBxVZ2R5BCG3+c6n+HOVQ9U1fe2fb8b+PuqekF7/+vA16rqnXN8xCeB1wLvTvIUhkuYF49sfxnw5vb6C9N9j4xvBUPgvqiqdiR5BrC8qq5t249jCNZd2s0x3yUMFzVcUlUfaOX3jGz/b8BnDN8nL5cg9GTyKuCR6TCqqscYjjZ/muHI8u+md6yqO6vq0b3s/y8YQhaGI8vPAQ8nWZrkMIZLgv9qjvZHAg8DX2tj+FpV3b2XY5j2DIZLXX+/qi6euTHJK4DXAT8/z/61ABjAejI5Cfj0aKGGu2jdA/wu8JYkNyX5jSQr97bzqroP2JnkOQxBfBNwM/BShpv7fHZk2eK72i9UTD/+BcO9Dx4A7k7ygST/ep7zBHgX8OdVdeHMDUmeBXwAWFdPvruIaYQBrCeTMPst/wI8xPBLDP8DWAbcmuR58/iM6aPg6QC+aeT9J0f2+0JVvWDk8WftiHwNcCbDncoubEsf83EjcEaSI2fZdjHwu1X1F/PsWwuEAawnk9sZjkS/JclihtsCfqH9X/5rqurnGY6IXz2Pz/gkQ9h+L8MSxKcYjoBfxhDOc6rBLVX134G1wL+dxxhg+DWJi4GPJnnmdDHJOoa7dJ03z361gBjAejLZBHzH9C9CtC/hfpPhdpMvTLK01Q9lOEviS/P4jL8AfgTYXlWPVdV2ht9keynD0fBuJfnOGT/O+IJ5jgGAdjbGJuBDSQ5N8lyGLxzfUFUL7md8tPc8C0JPGlVVSX6M4VaZ/5nhAOKjDD/0+Hrg4namxFOAP2b4Pbi9dRvD2Q+/P6P2jBm3TfyuJJ8Zeb+B4ZS3dyb5TuARhlPefm4eY/iWqnpLkg8AVwBTwNOBa2b8ysebqurPnsjnqA9vRylJnbgEIUmduAShg06SIxjWVmdaXfvxN/CSvIfhCrlRvzVy0YUOcC5BSFInLkFIUicGsCR1YgBLUicGsCR18v8BzOrT+Vk2VyAAAAAASUVORK5CYII=\n",
      "text/plain": [
       "<Figure size 360x360 with 1 Axes>"
      ]
     },
     "metadata": {
      "needs_background": "light"
     },
     "output_type": "display_data"
    }
   ],
   "source": [
    "sns.displot(azdias.OST_WEST_KZ)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "As we can see we have alot of float values , but all of them are around 0.8 , so we should ceil them up "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 49,
   "metadata": {},
   "outputs": [],
   "source": [
    "for col in categorical:\n",
    "    \n",
    "    max_azd=azdias.loc[:,col].max() #maximum value in column \n",
    "    azdias.loc[:,col]=azdias.loc[:,col].apply(lambda x: np.round(x) if np.round(x) <= max_azd else max_azd )\n",
    "    max_cust=customers.loc[:,col].max()\n",
    "    customers.loc[:,col]=customers.loc[:,col].apply(lambda x: np.round(x) if np.round(x) <= max_cust else max_cust )\n",
    "    #rounding up if value is smaller than maximum ;else setting maximum if rounded value  is bigger than maximum \n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 50,
   "metadata": {},
   "outputs": [],
   "source": [
    "azdias.OST_WEST_KZ.replace(1,\"W\",inplace=True)\n",
    "azdias.OST_WEST_KZ.replace(0,\"O\",inplace=True)\n",
    "customers.OST_WEST_KZ.replace(1,\"W\",inplace=True)\n",
    "customers.OST_WEST_KZ.replace(0,\"O\",inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 51,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "<seaborn.axisgrid.FacetGrid at 0x7f80cd789690>"
      ]
     },
     "execution_count": 51,
     "metadata": {},
     "output_type": "execute_result"
    },
    {
     "data": {
      "image/png": "iVBORw0KGgoAAAANSUhEUgAAAWAAAAFgCAYAAACFYaNMAAAAOXRFWHRTb2Z0d2FyZQBNYXRwbG90bGliIHZlcnNpb24zLjMuMSwgaHR0cHM6Ly9tYXRwbG90bGliLm9yZy/d3fzzAAAACXBIWXMAAAsTAAALEwEAmpwYAAAeiUlEQVR4nO3df5BdZ33f8fcHCWRHIGMJWd1ITmXVW4LtKXa0qApkKKCAFYcgk9rNMiVSO6rVcRWK0+DUopkSOqMWTygGZ2ynAhPJDrWsKLhWiG2qSoakrSOz/gFC/hEtK2HvSEjyD4yA2qDl2z/Oc+2j67u7V6t79rk/Pq+ZnXvu955z7vPI3s89+5xznquIwMzMpt9rcjfAzKxXOYDNzDJxAJuZZeIANjPLxAFsZpbJzNwNmG4rV66M++67L3czzKy3qFGx546An3nmmdxNMDMDejCAzczaRWUBLOnNkh4t/fxA0jWS5kraKWl/ejy7tM0GScOSnpR0aam+VNLe9NqNkpTqsyTdmep7JC2uqj9mZq1WWQBHxJMRcXFEXAwsBX4M3AVcB+yKiH5gV3qOpAuAQeBCYCVws6QZaXe3AOuA/vSzMtXXAs9HxPnADcD1VfXHzKzVpmsIYgXwnYj4LrAK2JLqW4DL0/IqYGtEvBQRB4BhYJmkPmBORDwQxX3Tt9VtU9vXdmBF7ejYzKzdTVcADwJ3pOUFEXEYID2ek+oLgadL24ym2sK0XF8/aZuIOAG8AMyrf3NJ6yQNSRo6duxYSzpkZna6Kg9gSa8DPgD8+WSrNqjFBPWJtjm5ELEpIgYiYmD+/PmTNMPMbHpMxxHwrwEPR8SR9PxIGlYgPR5N9VHg3NJ2i4BDqb6oQf2kbSTNBM4CnqugD2ZmLTcdAfwhXhl+ANgBrEnLa4C7S/XBdGXDeRQn2x5MwxTHJS1P47ur67ap7esKYHd4fk0z6xCV3gkn6eeA9wL/ulT+FLBN0lrgKeBKgIjYJ2kb8BhwAlgfEWNpm6uBzcCZwL3pB+BW4HZJwxRHvoNV9sfMrJXUaweMAwMDMTQ0lLsZZtZbfCuymVk7cQCbmWXSc7OhmbWjsbExRkZGXn6+ZMkSZsyYMcEW1g0cwGZtYGRkhKtuuofZ8/r40bOH+fz6y+jv78/dLKuYA9isTcye18cbFpw7+YrWNTwGbGaWiQPYzCwTB7CZWSYOYDOzTBzAZmaZOIDNzDJxAJuZZeIANjPLxAFsZpaJA9jMLBMHsJlZJg5gM7NMHMBmZpk4gM3MMnEAm5ll4gA2M8vEAWxmlokD2MwsEwewmVkm/k44s2ngbz22RhzAZtPA33psjTiAzaaJv/XY6nkM2MwsEwewmVkmDmAzs0wcwGZmmTiAzcwycQCbmWXiADYzy6TSAJb0RknbJT0h6XFJvyxprqSdkvanx7NL62+QNCzpSUmXlupLJe1Nr90oSak+S9Kdqb5H0uIq+2Nm1kpVHwF/DrgvIn4ReCvwOHAdsCsi+oFd6TmSLgAGgQuBlcDNkmr3at4CrAP608/KVF8LPB8R5wM3ANdX3B8zs5apLIAlzQHeCdwKEBE/iYjvA6uALWm1LcDlaXkVsDUiXoqIA8AwsExSHzAnIh6IiABuq9umtq/twIra0bGZWbur8gh4CXAM+FNJj0j6gqTZwIKIOAyQHs9J6y8Eni5tP5pqC9Nyff2kbSLiBPACMK++IZLWSRqSNHTs2LFW9c/M7LRUGcAzgV8CbomIS4AfkYYbxtHoyDUmqE+0zcmFiE0RMRARA/Pnz5+41WZm06TKAB4FRiNiT3q+nSKQj6RhBdLj0dL65ZlKFgGHUn1Rg/pJ20iaCZwFPNfynpiZVaCyAI6I7wFPS3pzKq0AHgN2AGtSbQ1wd1reAQymKxvOozjZ9mAapjguaXka311dt01tX1cAu9M4sZlZ26t6OsqPAF+S9DpgBPiXFKG/TdJa4CngSoCI2CdpG0VInwDWR8RY2s/VwGbgTODe9APFCb7bJQ1THPkOVtwfM7OWqTSAI+JRYKDBSyvGWX8jsLFBfQi4qEH9RVKAm5l1Gt8JZ2aWiQPYzCwTB7CZWSYOYDOzTBzAZmaZOIDNzDJxAJuZZeIANjPLxAFsZpaJA9jMLBMHsJlZJg5gM7NMHMBmZpk4gM3MMnEAm5ll4gA2M8vEAWxmlokD2MwsEwewmVkmDmAzs0wcwGZmmTiAzcwycQCbmWXiADYzy8QBbGaWyczcDTCz7jM2NsbIyAgAS5YsYcaMGZlb1J58BGxmLTcyMsJVN93DVTfd83IQ26v5CNjMKjF7Xl/uJrQ9HwGbmWXiADYzy8QBbGaWiQPYzCwTB7CZWSYOYDOzTCoNYEkHJe2V9KikoVSbK2mnpP3p8ezS+hskDUt6UtKlpfrStJ9hSTdKUqrPknRnqu+RtLjK/piZtdJ0HAG/OyIujoiB9Pw6YFdE9AO70nMkXQAMAhcCK4GbJdVun7kFWAf0p5+Vqb4WeD4izgduAK6fhv6YmbVEjiGIVcCWtLwFuLxU3xoRL0XEAWAYWCapD5gTEQ9ERAC31W1T29d2YEXt6NjMrN1VHcAB/E9JD0lal2oLIuIwQHo8J9UXAk+Xth1NtYVpub5+0jYRcQJ4AZhX3whJ6yQNSRo6duxYSzpmZna6qr4V+R0RcUjSOcBOSU9MsG6jI9eYoD7RNicXIjYBmwAGBgZe9bqZWQ6VHgFHxKH0eBS4C1gGHEnDCqTHo2n1UeDc0uaLgEOpvqhB/aRtJM0EzgKeq6IvZmatVlkAS5ot6Q21ZeB9wLeBHcCatNoa4O60vAMYTFc2nEdxsu3BNExxXNLyNL67um6b2r6uAHancWIzs7ZX5RDEAuCudE5sJvDfI+I+Sd8AtklaCzwFXAkQEfskbQMeA04A6yNiLO3ramAzcCZwb/oBuBW4XdIwxZHvYIX9MTNrqcoCOCJGgLc2qD8LrBhnm43Axgb1IeCiBvUXSQFuZtZpfCecmVkmnpDdsvHX1liv8xGwZeOvrbFe5yNgy8pfW2O9zEfAZmaZOIDNzDJxAJuZZeIANjPLxAFsZpaJA9jMLBMHsJlZJg5gM7NMHMBmZpk4gM3MMnEAm5ll4gA2M8vEAWxmlokD2MwsEwewmVkmDmAzs0wcwGZmmTiAzcwycQCbmWXiADYzy8QBbGaWiQPYzCwTB7CZWSYOYDOzTBzAZmaZOIDNzDJxAJuZZeIANjPLxAFsZpaJA9jMLJPKA1jSDEmPSPpKej5X0k5J+9Pj2aV1N0galvSkpEtL9aWS9qbXbpSkVJ8l6c5U3yNpcdX9MTNrlek4Av4o8Hjp+XXArojoB3al50i6ABgELgRWAjdLmpG2uQVYB/Snn5WpvhZ4PiLOB24Arq+2K2ZmrVNpAEtaBPw68IVSeRWwJS1vAS4v1bdGxEsRcQAYBpZJ6gPmRMQDERHAbXXb1Pa1HVhROzo2M2t3VR8Bfxb4feBnpdqCiDgMkB7PSfWFwNOl9UZTbWFarq+ftE1EnABeAObVN0LSOklDkoaOHTt2ml0yM2uNygJY0vuBoxHxULObNKjFBPWJtjm5ELEpIgYiYmD+/PlNNsfMrFozK9z3O4APSLoMOAOYI+nPgCOS+iLicBpeOJrWHwXOLW2/CDiU6osa1MvbjEqaCZwFPFdVh8zMWqmyI+CI2BARiyJiMcXJtd0R8WFgB7AmrbYGuDst7wAG05UN51GcbHswDVMcl7Q8je+urtumtq8r0nu86gjYzKwdVXkEPJ5PAdskrQWeAq4EiIh9krYBjwEngPURMZa2uRrYDJwJ3Jt+AG4Fbpc0THHkOzhdnTAzO13TEsAR8TXga2n5WWDFOOttBDY2qA8BFzWov0gKcDOzTtPUEISkdzRTMzOz5jU7BvzHTdbMzKxJEw5BSPpl4O3AfEn/rvTSHGBG463MzKwZk40Bvw54fVrvDaX6DyiuOjAzsymaMIAj4uvA1yVtjojvTlObzMx6QrNXQcyStAlYXN4mIt5TRaPMzHpBswH858CfUEyqMzbJumZm1oRmA/hERNxSaUvMzHpMs5eh/aWkfyOpL02oPlfS3EpbZmbW5Zo9Aq7Nt3BtqRbAktY2x8ysdzQVwBFxXtUNMTPrNU0FsKTVjeoRcVtrm2Nm1juaHYJ4W2n5DIrJdB6m+HogMzObgmaHID5Sfi7pLOD2SlpkZtYjpjod5Y8pJky3NjU2NsbIyMjLz5csWcKMGZ6+w6ydNDsG/Je88l1rM4C3ANuqapSdvpGREa666R5mz+vjR88e5vPrL6O/f2qfmQ5zs2o0ewT86dLyCeC7ETE63srWHmbP6+MNC86dfMVJtDLMbXr5w7O9NTsG/HVJC3jlZNz+6ppk7ag+zP2L3Rn84dnemh2C+GfAH1F8rZCAP5Z0bURsr7Bt1sb8i905WvWXkLVes0MQ/wF4W0QcBZA0H/hfgAO4h/kX2+z0NDsXxGtq4Zs8ewrbmplZA80eAd8n6avAHen5bwH3VNMkM+t25XMIvXz+YLLvhDsfWBAR10r6TeBXKMaAHwC+NA3tsw7jXyxrRu0cAtDT5w8mOwL+LPBxgIj4MvBlAEkD6bXfqLBt1oH8i2XNmj2vL3cTspssgBdHxLfqixExJGlxNU2yTudfLLPmTHYi7YwJXjuzlQ0xM+s1kwXwNyRdVV+UtBZ4qJommZn1hsmGIK4B7pL0z3klcAeA1wEfrLBdZmZdb8IAjogjwNslvRu4KJX/KiJ2V94yM7Mu1+xcEPcD91fcFjOznuK72czMMpnqhOxmZi3VizPsOYDNrC304gx7DmAzaxu9NsNeZWPAks6Q9KCkb0raJ+mTqT5X0k5J+9Pj2aVtNkgalvSkpEtL9aWS9qbXbpSkVJ8l6c5U3+O788ysk1R5Eu4l4D0R8VbgYmClpOXAdcCuiOgHdqXnSLoAGAQuBFYCN0uqDQDdAqyj+CLQ/vQ6wFrg+Yg4H7gBuL7C/piZtVRlARyFH6anr00/AawCtqT6FuDytLwK2BoRL0XEAWAYWCapD5gTEQ9ERAC31W1T29d2YEXt6NjMrN1VehmapBmSHgWOAjsjYg/F9JaHAdLjOWn1hcDTpc1HU21hWq6vn7RNRJwAXgDmNWjHOklDkoaOHTvWot6ZmZ2eSgM4IsYi4mJgEcXR7EUTrN7oyDUmqE+0TX07NkXEQEQMzJ8/f5JWm5lNj2m5ESMivk/xhZ4rgSNpWIH0WPuqo1GgfPpzEXAo1Rc1qJ+0jaSZwFnAc1X0wcys1aq8CmK+pDem5TOBXwWeAHYAa9Jqa4C70/IOYDBd2XAexcm2B9MwxXFJy9P47uq6bWr7ugLYncaJzczaXpXXAfcBW9KVDK8BtkXEVyQ9AGxLU1o+BVwJEBH7JG0DHgNOAOsjYizt62pgM8UcxPemH4BbgdslDVMc+Q5W2B8zs5aqLIDTN2lc0qD+LLBinG02Ahsb1Id4ZTa2cv1FUoCbmU1FzlugfSecmfW0nLdAO4DNrOflugXa01GamWXiI2Az6wqdOJ2lA9jMukInTmfpADazrtFp01l6DNjMLBMHsJlZJg5gM7NMPAZs1mE68Wy/NeYANuswnXi23xpzAJt1oE4722+NeQzYzCwTB7CZWSYegjCzntCOJy8dwGbWE9rx5KUD2KyHteNRYZXa7eSlA9ish7XjUWEvcQCb9bh2OyrsJb4KwswsEwewmVkmDmAzs0wcwGZmmTiAzcwycQCbmWXiADYzy8QBbGaWiQPYzCwTB7CZWSYOYDOzTBzAZmaZOIDNzDJxAJuZZeLpKM2s4zSaSL4TVXYELOlcSfdLelzSPkkfTfW5knZK2p8ezy5ts0HSsKQnJV1aqi+VtDe9dqMkpfosSXem+h5Ji6vqj5m1j9pE8tdsfYSrbrrnpDDuJFUOQZwAfi8i3gIsB9ZLugC4DtgVEf3ArvSc9NogcCGwErhZUu27UW4B1gH96Wdlqq8Fno+I84EbgOsr7I+ZtZHaRPKz5/XlbsqUVRbAEXE4Ih5Oy8eBx4GFwCpgS1ptC3B5Wl4FbI2IlyLiADAMLJPUB8yJiAciIoDb6rap7Ws7sKJ2dGxm1u6m5SRcGhq4BNgDLIiIw1CENHBOWm0h8HRps9FUW5iW6+snbRMRJ4AXgHkN3n+dpCFJQ8eOHWtRr8zMTk/lASzp9cBfANdExA8mWrVBLSaoT7TNyYWITRExEBED8+fPn6zJHWdsbIz9+/ezf/9+xsbGcjfHzJpUaQBLei1F+H4pIr6cykfSsALp8WiqjwLlbwZcBBxK9UUN6idtI2kmcBbwXOt70t5qJyQ6+WSEWS+q8ioIAbcCj0fEZ0ov7QDWpOU1wN2l+mC6suE8ipNtD6ZhiuOSlqd9rq7bpravK4DdaZy458ye19fRJyPMelGV1wG/A/htYK+kR1Pt48CngG2S1gJPAVcCRMQ+SduAxyiuoFgfEbW/p68GNgNnAvemHygC/nZJwxRHvoMV9sfMrKUqC+CI+N80HqMFWDHONhuBjQ3qQ8BFDeovkgLczKzT+FZkM7NMHMBmZpk4gM3MMvFkPGbWthpNujNjxowJtugsDmAza1u1a9xnz+vjR88e5vPrL6O/vz93s1rGAWxmba026U438hiwmVkmPgI2K+n2MUdrLw5gs5JuH3O09uIANqvTzWOO1l48BmxmlokD2MwsEw9BdBCfIDLrLg7gDuITRGbdxQHcYXyCyKx7eAzYzCwTB7CZWSYOYDOzTBzAZmaZOIDNzDJxAJuZZeIANjPLxNcBtxHf6TZ15X87/7tZp3AAtxHf6TZ1tX87wP9u1jEcwG3Gd7pN3ex5fbmb0HI+su9uHgM2a2O1I/urbrrnpOEp6w4+AjZrc914ZG8FHwGbmWXiI+CK+coGMxuPA7hivrIhn1wffj5xZs1yAE8DX9mQR6MPvyVLllQeyr4kzprlALauVv/hN11/kfjEmTXDATwJj+F2H/9FYu3CATyJbhrD9YeJWXup7DI0SV+UdFTSt0u1uZJ2StqfHs8uvbZB0rCkJyVdWqovlbQ3vXajJKX6LEl3pvoeSYur6kvtiKnT/6ysfZhcs/URX9jfZcbGxti/f//LP2NjY7mbZE2o8jrgzcDKutp1wK6I6Ad2pedIugAYBC5M29wsqXZodguwDuhPP7V9rgWej4jzgRuA6yvrSRfplg8TO5k/XDtTZQEcEX8NPFdXXgVsSctbgMtL9a0R8VJEHACGgWWS+oA5EfFARARwW902tX1tB1bUjo7NepE/XDvPdN8JtyAiDgOkx3NSfSHwdGm90VRbmJbr6ydtExEngBeAeY3eVNI6SUOSho4dO9airpiZnZ52uRW50ZFrTFCfaJtXFyM2RcRARAzMnz9/ik00M2ut6b4K4oikvog4nIYXjqb6KFC+LmgRcCjVFzWol7cZlTQTOItXD3lYG/DVF53F/72mz3QfAe8A1qTlNcDdpfpgurLhPIqTbQ+mYYrjkpan8d3VddvU9nUFsDuNE1ubmeoJovKZfZ/Vnz4+oTd9KjsClnQH8C7gTZJGgU8AnwK2SVoLPAVcCRAR+yRtAx4DTgDrI6L2G3c1xRUVZwL3ph+AW4HbJQ1THPkOVtUXO31TufnBt/Tm45tVpkdlARwRHxrnpRXjrL8R2NigPgRc1KD+IinArXv5jL51s3Y5CWdm1nMcwGZmmTiAzcwycQCbmWXiADYzy8TTUZovvDfLxAFsXTXn8XTyB5edLgewAb7wfir8wWWnywFsdhr8wdWdpuuvGwewmVmd6frrxgFsHcXjrjZdpuOvGwewdZRc466Ngt/sdDmArePkGHdtFPy9pvwh5L88WsMBbNakXj/h5ulBW88BnImPJqwTeXrQ1vKtyJnUjib8jQNmvctHwBn5aMI6na9KOT0O4BbymXLrNb4b8PQ4gKdgvE99nym3XtTrJydPhwN4Cib61Pf/jGbWLAfwFDlozex0+SoIM7NMHMBmZpk4gM3MMnEAm5ll4gA2M8vEAWxmlokD2MwsEwewmVkmDmAzs0wcwGZmmTiAzcwycQCbmWXiADYzy6TjA1jSSklPShqWdF3u9piZNaujA1jSDOAm4NeAC4APSbogb6vMzJrT6fMBLwOGI2IEQNJWYBXwWCvf5EfPHn758eDBs8etHTx4cErrTrZ9zXivn8q+TqXdk73v6bal0brjve9U9nUq7T6Vf2P/t53e/7an+9/jVP6Nm9lXK79ySRHRsp1NN0lXACsj4l+l578N/OOI+J269dYB69LTNwNPnuJbvQl45jSb2yl6qa/QW/3tpb5Ce/X3mYhYWV/s9CNgNai96hMlIjYBm6b8JtJQRAxMdftO0kt9hd7qby/1FTqjvx09BgyMAuXvBVoEHMrUFjOzU9LpAfwNoF/SeZJeBwwCOzK3ycysKR09BBERJyT9DvBVYAbwxYjYV8FbTXn4ogP1Ul+ht/rbS32FDuhvR5+EMzPrZJ0+BGFm1rEcwGZmmTiAJ9DttzlL+qKko5K+XarNlbRT0v70+Oqr6DuQpHMl3S/pcUn7JH001bu1v2dIelDSN1N/P5nqXdlfKO6MlfSIpK+k523fVwfwOHrkNufNQP3F4dcBuyKiH9iVnneDE8DvRcRbgOXA+vTfs1v7+xLwnoh4K3AxsFLScrq3vwAfBR4vPW/7vjqAx/fybc4R8ROgdptz14iIvwaeqyuvArak5S3A5dPZpqpExOGIeDgtH6f4RV1I9/Y3IuKH6elr00/Qpf2VtAj4deALpXLb99UBPL6FwNOl56Op1u0WRMRhKEILOCdze1pO0mLgEmAPXdzf9Cf5o8BRYGdEdHN/Pwv8PvCzUq3t++oAHl9TtzlbZ5H0euAvgGsi4ge521OliBiLiIsp7hBdJumizE2qhKT3A0cj4qHcbTlVDuDx9eptzkck9QGkx6OZ29Mykl5LEb5fiogvp3LX9rcmIr4PfI1ivL8b+/sO4AOSDlIMFb5H0p/RAX11AI+vV29z3gGsSctrgLsztqVlJAm4FXg8Ij5Teqlb+ztf0hvT8pnArwJP0IX9jYgNEbEoIhZT/J7ujogP0wF99Z1wE5B0GcXYUu025415W9Raku4A3kUxbd8R4BPA/wC2Ab8APAVcGRH1J+o6jqRfAf4G2Msr44QfpxgH7sb+/iOKE08zKA60tkXEf5I0jy7sb42kdwEfi4j3d0JfHcBmZpl4CMLMLBMHsJlZJg5gM7NMHMBmZpk4gM3MMnEAm5ll4gC2LCT9PUlbJX1H0mOS7pH0D9NrvyvpRUlnldZ/l6SQtLZUuyTVPpaeb5Z0QNKj6ef/lta9XNK3JD0haa+kyydpX21f35T0d5Juk7Sw9PrBtJ/ae92Y6l+TNFBab3F5us8G7/NeSQ+lfT0k6T2l15am+rCkG9PNJEh6p6SHJZ2QdEXd/sZKbeqFG4c6Wkd/J5x1phQkdwFbImIw1S4GFgB/B3yI4k7ED1JMmVmzF/gtijvaoLjr6Zt1u782IrbXvd9bgU8D742IA5LOA3ZKGomIb03Q1GsjYntq7zXA/ZIuSrPjAbw7Ip5pvucNPQP8RkQcSnM1fJVXJn26BVgH/C1wD8WtxPdS3FTwL4CPNdjf/0vzP1gH8BGw5fBu4KcR8Se1QkQ8GhF/I+kfAK8H/oAiiMueAs6QtCCFYi2QJvMx4D9HxIH0XgeA/wJc20xj09SONwDfo5gfumUi4pGIqM0xso+if7PS3AVzIuKBKO6Wuo00nWJEHEwfHD9ruFPrGA5gy+EiYLyZqz4E3EFx2/CbJdVPIbgduBJ4O/AwxcTjZX9U+hP8S6l2YYP3G0r1U/Ew8Iul5/eX3ut3T3FfjfxT4JGIeIniKHi09Fqz06GeIWlI0t9ONsxi+XkIwtrNIPDBiPiZpC9ThO1Npde3AXdSBOEdFEFc9qohCIqpRevvuW9Um0z9FKWNhiAa7XPS95F0IXA98L5x3qup/QC/kIYzlgC7Je2NiO80sZ1l4CNgy2EfsLS+mCaQ6acYnz1IEcYnDUNExPeAnwLvpfiamWbfb6Cu9kvAY6fU6mIS98cnWedZoPzdY3MpxnnHlb7N4S5gdSksRymmQK1pajrU2nBGRIxQTEF5yWTbWD4OYMthNzBL0lW1gqS3AZ8D/jAiFqefnwcWSvr7ddv/R+DfR8RYk+/3aWCDim/CqH0jxseB/9rMxir8W6APuG+S1b8GfLh2xQLFNIj3T7DvNwJ/BWyIiP9Tq6dvcDguaXna12ommU5R0tmSZqXlN1HMk3uqHzI2jTwbmmUh6ecppvpcCrwIHAQuA94SEU+U1vsMxVSZe0jTDNbt5w+BH0bEpyVtBv4J8EJplWUR8RNJvwl8kuK70X4KfKI0KXuj9tX29QPg5yiuRNgQEaPp9YPAcaD2IfCtiFitYu7ozwDvpBgyGAI+EhE/Hud9/gDYAOwvld8XEUfT5WybgTMpTjZ+JCIifVjdRXGk/SLwvYi4UNLbgf9GcXLuNcBnI+JWrG05gM3MMvEQhJlZJr4KwnqapJsoxkrLPhcRf9ri97mU4iqHsgMR8cFWvo91Fg9BmJll4iEIM7NMHMBmZpk4gM3MMnEAm5ll8v8BQMtBSZfNj9gAAAAASUVORK5CYII=\n",
      "text/plain": [
       "<Figure size 360x360 with 1 Axes>"
      ]
     },
     "metadata": {
      "needs_background": "light"
     },
     "output_type": "display_data"
    }
   ],
   "source": [
    "sns.displot(azdias.CAMEO_DEU_2015)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 52,
   "metadata": {},
   "outputs": [],
   "source": [
    "azdias.loc[:,\"CAMEO_DEU_2015\"]=encoder.inverse_transform(azdias.CAMEO_DEU_2015.values.reshape(-1,1)).squeeze()\n",
    "customers.loc[:,\"CAMEO_DEU_2015\"]=encoder.inverse_transform(customers.CAMEO_DEU_2015.values.reshape(-1,1)).squeeze()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 53,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "6C    67872\n",
       "6B    62839\n",
       "8A    52501\n",
       "4C    48121\n",
       "2D    35090\n",
       "3C    34849\n",
       "7A    34608\n",
       "3D    34420\n",
       "8B    33469\n",
       "4A    33289\n",
       "8C    31019\n",
       "9D    28596\n",
       "9B    27681\n",
       "6E    26334\n",
       "9C    24991\n",
       "7B    24671\n",
       "9A    20557\n",
       "2C    19430\n",
       "8D    17590\n",
       "5D    16827\n",
       "2B    15494\n",
       "2A    13250\n",
       "5A    13196\n",
       "6D    12801\n",
       "1D    11910\n",
       "6A    11553\n",
       "5B    11550\n",
       "5C    11398\n",
       "1A    10850\n",
       "3A    10566\n",
       "6F    10342\n",
       "4B     9239\n",
       "7C     9209\n",
       "4D     9047\n",
       "3B     7205\n",
       "5F     7006\n",
       "9E     6381\n",
       "4E     5984\n",
       "5E     5842\n",
       "7D     5460\n",
       "1E     5065\n",
       "7E     4731\n",
       "1C     4317\n",
       "1B     4071\n",
       "Name: CAMEO_DEU_2015, dtype: int64"
      ]
     },
     "execution_count": 53,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "azdias.CAMEO_DEU_2015.value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 55,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "5E    46143\n",
       "2D    11209\n",
       "6B     9647\n",
       "4C     9393\n",
       "3D     8123\n",
       "4A     7601\n",
       "3C     6643\n",
       "5D     5943\n",
       "1D     5880\n",
       "2C     5076\n",
       "8A     4252\n",
       "1A     4198\n",
       "7A     4110\n",
       "8B     3790\n",
       "2A     3715\n",
       "2B     3485\n",
       "7B     3422\n",
       "1E     3337\n",
       "6E     3089\n",
       "6C     2846\n",
       "8C     2801\n",
       "5A     2594\n",
       "4D     2539\n",
       "4B     2280\n",
       "1C     2206\n",
       "8D     2206\n",
       "5B     2166\n",
       "9D     1957\n",
       "5C     1939\n",
       "3A     1912\n",
       "4E     1813\n",
       "3B     1781\n",
       "5F     1733\n",
       "9E     1508\n",
       "6D     1246\n",
       "7C     1194\n",
       "6F     1171\n",
       "1B     1157\n",
       "7E     1034\n",
       "9A     1023\n",
       "9C      987\n",
       "9B      917\n",
       "7D      801\n",
       "6A      785\n",
       "Name: CAMEO_DEU_2015, dtype: int64"
      ]
     },
     "execution_count": 55,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "customers.CAMEO_DEU_2015.value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 56,
   "metadata": {},
   "outputs": [],
   "source": [
    "azdias.to_csv(\"azdias_clean.csv\",index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 57,
   "metadata": {},
   "outputs": [],
   "source": [
    "customers.to_csv(\"customers_clean.csv\",index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 58,
   "metadata": {},
   "outputs": [],
   "source": [
    "save_list(azdias.columns,\"Wanted_Columns\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Cleaning the Training and Test Datasets"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Since we cleaned the main datasets , we should also clean the training and testing dataset to make sure they all come from the same distribution before any further machine learning approach"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "# import libraries here; add more as necessary\n",
    "import numpy as np\n",
    "import pandas as pd\n",
    "import matplotlib.pyplot as plt\n",
    "import seaborn as sns\n",
    "from tools import*\n",
    "# magic word for producing visualizations in notebook\n",
    "%matplotlib inline"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/home/shapiro/anaconda3/lib/python3.7/site-packages/IPython/core/interactiveshell.py:3146: DtypeWarning: Columns (18,19) have mixed types.Specify dtype option on import or set low_memory=False.\n",
      "  interactivity=interactivity, compiler=compiler, result=result)\n"
     ]
    }
   ],
   "source": [
    "train = pd.read_csv(\"Udacity_MAILOUT_052018_TRAIN.csv\",sep=\";\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "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": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "train.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(2217179, 'Percentage : 14.062100630847082 %')"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "train.isna().sum().sum(),\"Percentage : {} %\".format((train.isna().sum().sum()/(train.shape[0]*train.shape[1]))*100)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We can see the more than of 14% of values are NaNs. \n",
    "<br> Let's do the exact same step we did to our Azdias and Customers datasets"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "1. Dropping Columns Not in Azdias and Customers. \n",
    "2. Decoding Unkown Values to NaNs using the Description sheet\n",
    "3. Replacing outliers with NaNs using the value dict we computed earlier\n",
    "4. Replacing \"XX\" in both `CAMEO_INTL_2015` and `CAMEO_DEU_2015` with NaNs \n",
    "6. Imputing both the numerical NaNs and the Categorical NaNs using both the Categorical and Numerical Columns from above in `numerical` and `categorical` objects"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [],
   "source": [
    "wanted_columns=list(load_list(\"Wanted_Columns\"))\n",
    "wanted_columns.append(\"RESPONSE\") #adding the response variable to the wanted columns for training only "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(42962, 357)"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "train=train[wanted_columns]\n",
    "train.shape #should be (X,517)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "{'KBA05_DIESEL': [0, 1, 2, 3, 4],\n",
       " 'SEMIO_TRADV': [1, 2, 3, 4, 5, 6, 7],\n",
       " 'D19_GESAMT_ANZ_12': [0, 1, 2, 3, 4, 5, 6],\n",
       " 'KBA05_KRSVAN': [1, 2, 3],\n",
       " 'KBA05_SEG8': [0, 1, 2, 3],\n",
       " 'SEMIO_ERL': [1, 2, 3, 4, 5, 6, 7],\n",
       " 'KBA05_ANTG1': [0, 1, 2, 3, 4],\n",
       " 'ORTSGR_KLS9': [1, 2, 3, 4, 5, 6, 7, 8, 9],\n",
       " 'KBA13_SEG_KLEINWAGEN': [0, 1, 2, 3, 4, 5],\n",
       " 'HEALTH_TYP': [0, 1, 2, 3],\n",
       " 'SEMIO_VERT': [1, 2, 3, 4, 5, 6, 7],\n",
       " 'KBA13_SEG_KOMPAKTKLASSE': [0, 1, 2, 3, 4, 5],\n",
       " 'LP_FAMILIE_FEIN': [1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11],\n",
       " 'KBA13_VORB_1': [0, 1, 2, 3, 4, 5],\n",
       " 'KBA13_KRSSEG_OBER': [0, 1, 2, 3],\n",
       " 'VERS_TYP': [1, 2],\n",
       " 'PLZ8_ANTG4': [0, 1, 2],\n",
       " 'SEMIO_LUST': [1, 2, 3, 4, 5, 6, 7],\n",
       " 'KBA05_SEG2': [1, 2, 3, 4, 5],\n",
       " 'KBA13_HERST_AUDI_VW': [0, 1, 2, 3, 4, 5],\n",
       " 'KBA13_ALTERHALTER_60': [0, 1, 2, 3, 4, 5],\n",
       " 'KBA05_MAXSEG': [1, 2, 3, 4],\n",
       " 'KBA13_VORB_0': [0, 1, 2, 3, 4, 5],\n",
       " 'KBA05_CCM3': [1, 2, 3, 4, 5],\n",
       " 'KBA13_BJ_2006': [0, 1, 2, 3, 4, 5],\n",
       " 'KBA13_FAB_SONSTIGE': [0, 1, 2, 3, 4, 5],\n",
       " 'KBA13_SEG_GROSSRAUMVANS': [0, 1, 2, 3, 4, 5],\n",
       " 'KBA13_SEG_OBERKLASSE': [0, 1, 2, 3, 4, 5],\n",
       " 'D19_BANKEN_OFFLINE_DATUM': [1, 2, 3, 4, 5, 6, 7, 8, 9, 10],\n",
       " 'KBA13_ALTERHALTER_45': [0, 1, 2, 3, 4, 5],\n",
       " 'EWDICHTE': [1, 2, 3, 4, 5, 6],\n",
       " 'KBA05_ALTER3': [1, 2, 3, 4, 5],\n",
       " 'KBA13_CCM_3000': [0, 1, 2, 3, 4, 5],\n",
       " 'KBA05_HERSTTEMP': [1, 2, 3, 4, 5],\n",
       " 'D19_VERSAND_ONLINE_QUOTE_12': [0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10],\n",
       " 'KBA05_ZUL2': [1, 2, 3, 4, 5],\n",
       " 'KBA13_CCM_1400': [0, 1, 2, 3, 4, 5],\n",
       " 'KBA13_HALTER_50': [0, 1, 2, 3, 4, 5],\n",
       " 'KBA05_SEG6': [0, 1],\n",
       " 'KBA13_OPEL': [0, 1, 2, 3, 4, 5],\n",
       " 'KBA13_CCM_3001': [0, 1, 2, 3, 4, 5],\n",
       " 'REGIOTYP': [1, 2, 3, 4, 5, 6, 7],\n",
       " 'KBA05_ZUL3': [0, 1, 2, 3, 4, 5],\n",
       " 'GEBAEUDETYP': [1, 2, 3, 4, 5, 6, 7, 8],\n",
       " 'KBA05_GBZ': [1, 2, 3, 4, 5],\n",
       " 'KBA13_KMH_250': [0, 1, 2, 3, 4, 5],\n",
       " 'KBA05_KW1': [1, 2, 3, 4, 5],\n",
       " 'KBA13_TOYOTA': [0, 1, 2, 3, 4, 5],\n",
       " 'CJT_GESAMTTYP': [1, 2, 3, 4, 5, 6],\n",
       " 'KBA05_ZUL1': [1, 2, 3, 4, 5],\n",
       " 'KBA13_KW_0_60': [0, 1, 2, 3, 4, 5],\n",
       " 'KBA05_CCM1': [1, 2, 3, 4, 5],\n",
       " 'GFK_URLAUBERTYP': [1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12],\n",
       " 'KBA13_CCM_1500': [0, 1, 2, 3, 4, 5],\n",
       " 'KBA13_HALTER_65': [0, 1, 2, 3, 4, 5],\n",
       " 'RETOURTYP_BK_S': [1, 2, 3, 4, 5],\n",
       " 'KBA05_MOTRAD': [0, 1, 2, 3],\n",
       " 'ONLINE_AFFINITAET': [0, 1, 2, 3, 4, 5],\n",
       " 'LP_STATUS_FEIN': [1, 2, 3, 4, 5, 6, 7, 8, 9, 10],\n",
       " 'W_KEIT_KIND_HH': [1, 2, 3, 4, 5, 6],\n",
       " 'KBA05_SEG7': [0, 1, 2, 3],\n",
       " 'KBA13_HERST_BMW_BENZ': [0, 1, 2, 3, 4, 5],\n",
       " 'KBA05_MAXAH': [1, 2, 3, 4, 5],\n",
       " 'KBA05_MOD3': [1, 2, 3, 4, 5],\n",
       " 'KBA05_SEG4': [1, 2, 3, 4, 5],\n",
       " 'PLZ8_BAUMAX': [1, 2, 3, 4, 5],\n",
       " 'ANREDE_KZ': [1, 2],\n",
       " 'KBA13_SEG_KLEINST': [0, 1, 2, 3, 4, 5],\n",
       " 'CAMEO_DEUG_2015': [1, 2, 3, 4, 5, 6, 7, 8, 9],\n",
       " 'SEMIO_KAEM': [1, 2, 3, 4, 5, 6, 7],\n",
       " 'D19_GESAMT_ONLINE_DATUM': [1, 2, 3, 4, 5, 6, 7, 8, 9, 10],\n",
       " 'LP_LEBENSPHASE_GROB': [1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12],\n",
       " 'KBA05_HERST2': [0, 1, 2, 3, 4, 5],\n",
       " 'SEMIO_REL': [1, 2, 3, 4, 5, 6, 7],\n",
       " 'KBA13_FIAT': [0, 1, 2, 3, 4, 5],\n",
       " 'D19_GESAMT_OFFLINE_DATUM': [1, 2, 3, 4, 5, 6, 7, 8, 9, 10],\n",
       " 'KBA13_HALTER_40': [0, 1, 2, 3, 4, 5],\n",
       " 'D19_BANKEN_ANZ_12': [0, 1, 2, 3, 4, 5, 6],\n",
       " 'KBA05_KRSHERST1': [1, 2, 3, 4, 5],\n",
       " 'KBA13_KW_50': [0, 1, 2, 3, 4, 5],\n",
       " 'KBA13_VORB_2': [0, 1, 2, 3, 4, 5],\n",
       " 'KBA05_MOD2': [1, 2, 3, 4, 5],\n",
       " 'SEMIO_DOM': [1, 2, 3, 4, 5, 6, 7],\n",
       " 'KBA05_MAXVORB': [1, 2, 3],\n",
       " 'KBA13_BJ_1999': [0, 1, 2, 3, 4, 5],\n",
       " 'KBA13_RENAULT': [0, 1, 2, 3, 4, 5],\n",
       " 'D19_VERSAND_ANZ_24': [0, 1, 2, 3, 4, 5, 6],\n",
       " 'PLZ8_ANTG2': [0, 1, 2, 3, 4],\n",
       " 'D19_GESAMT_DATUM': [1, 2, 3, 4, 5, 6, 7, 8, 9, 10],\n",
       " 'PLZ8_HHZ': [1, 2, 3, 4, 5],\n",
       " 'GEBAEUDETYP_RASTER': [1, 2, 3, 4, 5],\n",
       " 'KBA13_KRSAQUOT': [0, 1, 2, 3, 4, 5],\n",
       " 'KBA05_MOD1': [0, 1, 2, 3, 4],\n",
       " 'KBA13_HALTER_30': [0, 1, 2, 3, 4, 5],\n",
       " 'KBA05_FRAU': [1, 2, 3, 4, 5],\n",
       " 'KBA05_KRSHERST2': [1, 2, 3, 4, 5],\n",
       " 'KBA13_HERST_SONST': [0, 1, 2, 3, 4, 5],\n",
       " 'KBA05_ANHANG': [0, 1, 2, 3],\n",
       " 'KBA13_KW_30': [0, 1, 2, 3, 4, 5],\n",
       " 'KBA13_HALTER_25': [0, 1, 2, 3, 4, 5],\n",
       " 'FINANZ_MINIMALIST': [1, 2, 3, 4, 5],\n",
       " 'NATIONALITAET_KZ': [1, 2, 3],\n",
       " 'D19_GESAMT_ONLINE_QUOTE_12': [0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10],\n",
       " 'KBA13_NISSAN': [0, 1, 2, 3, 4, 5],\n",
       " 'KBA13_ALTERHALTER_61': [0, 1, 2, 3, 4, 5],\n",
       " 'KBA05_SEG1': [0, 1, 2, 3],\n",
       " 'KBA13_HERST_ASIEN': [0, 1, 2, 3, 4, 5],\n",
       " 'FINANZ_VORSORGER': [1, 2, 3, 4, 5],\n",
       " 'PLZ8_ANTG1': [0, 1, 2, 3, 4],\n",
       " 'KBA13_BJ_2008': [0, 1, 2, 3, 4, 5],\n",
       " 'ZABEOTYP': [1, 2, 3, 4, 5, 6],\n",
       " 'KBA13_KW_70': [0, 1, 2, 3, 4, 5],\n",
       " 'KBA13_HERST_EUROPA': [0, 1, 2, 3, 4, 5],\n",
       " 'KONSUMNAEHE': [1, 2, 3, 4, 5, 6, 7],\n",
       " 'OST_WEST_KZ': ['O', 'W'],\n",
       " 'D19_KONSUMTYP': [1, 2, 3, 4, 5, 6, 9],\n",
       " 'KBA13_KW_60': [0, 1, 2, 3, 4, 5],\n",
       " 'KBA13_CCM_1200': [0, 1, 2, 3, 4, 5],\n",
       " 'KBA13_SITZE_5': [0, 1, 2, 3, 4, 5],\n",
       " 'KBA13_CCM_2000': [0, 1, 2, 3, 4, 5],\n",
       " 'KBA05_KW2': [1, 2, 3, 4, 5],\n",
       " 'INNENSTADT': [1, 2, 3, 4, 5, 6, 7, 8],\n",
       " 'SEMIO_RAT': [1, 2, 3, 4, 5, 6, 7],\n",
       " 'FINANZ_ANLEGER': [1, 2, 3, 4, 5],\n",
       " 'KBA05_KRSAQUOT': [1, 2, 3, 4, 5],\n",
       " 'KKK': [1, 2, 3, 4],\n",
       " 'KBA05_BAUMAX': [1, 2, 3, 4, 5],\n",
       " 'KBA05_AUTOQUOT': [1, 2, 3, 4, 5],\n",
       " 'KBA13_SEG_MINIWAGEN': [0, 1, 2, 3, 4, 5],\n",
       " 'D19_VERSI_ANZ_24': [0, 1, 2, 3, 4, 5, 6],\n",
       " 'KBA13_BMW': [0, 1, 2, 3, 4, 5],\n",
       " 'KBA13_KMH_180': [0, 1, 2, 3, 4, 5],\n",
       " 'KBA13_KRSHERST_FORD_OPEL': [0, 1, 2, 3, 4, 5],\n",
       " 'WOHNLAGE': [0, 1, 2, 3, 4, 5, 7, 8],\n",
       " 'KBA05_HERST3': [0, 1, 2, 3, 4, 5],\n",
       " 'KBA13_HALTER_20': [0, 1, 2, 3, 4, 5],\n",
       " 'KBA13_BJ_2009': [0, 1, 2, 3, 4, 5],\n",
       " 'KBA13_KMH_251': [0, 1, 2, 3, 4, 5],\n",
       " 'D19_TELKO_OFFLINE_DATUM': [1, 2, 3, 4, 5, 6, 7, 8, 9, 10],\n",
       " 'D19_VERSAND_ANZ_12': [0, 1, 2, 3, 4, 5, 6],\n",
       " 'D19_TELKO_DATUM': [1, 2, 3, 4, 5, 6, 7, 8, 9, 10],\n",
       " 'PLZ8_GBZ': [1, 2, 3, 4, 5],\n",
       " 'KBA05_ANTG2': [0, 1, 2, 3, 4],\n",
       " 'KBA13_SEG_SONSTIGE': [0, 1, 2, 3, 4, 5],\n",
       " 'SHOPPER_TYP': [0, 1, 2, 3],\n",
       " 'KBA13_PEUGEOT': [0, 1, 2, 3, 4, 5],\n",
       " 'KBA13_VORB_3': [0, 1, 2, 3, 4, 5],\n",
       " 'RELAT_AB': [1, 2, 3, 4, 5],\n",
       " 'KBA13_KW_110': [0, 1, 2, 3, 4, 5],\n",
       " 'KBA13_SITZE_4': [0, 1, 2, 3, 4, 5],\n",
       " 'GREEN_AVANTGARDE': [0, 1],\n",
       " 'LP_LEBENSPHASE_FEIN': [1,\n",
       "  2,\n",
       "  3,\n",
       "  4,\n",
       "  5,\n",
       "  6,\n",
       "  7,\n",
       "  8,\n",
       "  9,\n",
       "  10,\n",
       "  11,\n",
       "  12,\n",
       "  13,\n",
       "  14,\n",
       "  15,\n",
       "  16,\n",
       "  17,\n",
       "  18,\n",
       "  19,\n",
       "  20,\n",
       "  21,\n",
       "  22,\n",
       "  23,\n",
       "  24,\n",
       "  25,\n",
       "  26,\n",
       "  27,\n",
       "  28,\n",
       "  29,\n",
       "  30,\n",
       "  31,\n",
       "  32,\n",
       "  33,\n",
       "  34,\n",
       "  35,\n",
       "  36,\n",
       "  37,\n",
       "  38,\n",
       "  39,\n",
       "  40],\n",
       " 'KBA13_KMH_110': [0, 1, 2, 3, 4, 5],\n",
       " 'KBA13_HERST_FORD_OPEL': [0, 1, 2, 3, 4, 5],\n",
       " 'KBA05_VORB0': [1, 2, 3, 4, 5],\n",
       " 'PRAEGENDE_JUGENDJAHRE': [1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15],\n",
       " 'D19_BANKEN_ONLINE_DATUM': [1, 2, 3, 4, 5, 6, 7, 8, 9, 10],\n",
       " 'KBA05_ZUL4': [0, 1, 2, 3, 4, 5],\n",
       " 'KBA13_SEG_WOHNMOBILE': [0, 1, 2, 3, 4, 5],\n",
       " 'KBA13_KW_120': [0, 1, 2, 3, 4, 5],\n",
       " 'D19_GESAMT_ANZ_24': [0, 1, 2, 3, 4, 5, 6],\n",
       " 'KBA13_MAZDA': [0, 1, 2, 3, 4, 5],\n",
       " 'KBA05_ANTG4': [0, 1, 2],\n",
       " 'KBA13_KMH_140_210': [0, 1, 2, 3, 4, 5],\n",
       " 'KBA13_CCM_2500': [0, 1, 2, 3, 4, 5],\n",
       " 'KBA05_SEG5': [0, 1, 2, 3, 4],\n",
       " 'KBA13_HALTER_66': [0, 1, 2, 3, 4, 5],\n",
       " 'KBA13_SEG_VAN': [0, 1, 2, 3, 4, 5],\n",
       " 'FINANZ_HAUSBAUER': [1, 2, 3, 4, 5],\n",
       " 'KBA13_HALTER_35': [0, 1, 2, 3, 4, 5],\n",
       " 'D19_VERSAND_ONLINE_DATUM': [1, 2, 3, 4, 5, 6, 7, 8, 9, 10],\n",
       " 'WOHNDAUER_2008': [1, 2, 3, 4, 5, 6, 7, 8, 9],\n",
       " 'KBA05_MOD4': [0, 1, 2, 3, 4, 5],\n",
       " 'LP_STATUS_GROB': [1, 2, 3, 4, 5, 6, 7, 8, 9, 10],\n",
       " 'KBA05_MAXHERST': [1, 2, 3, 4, 5],\n",
       " 'KBA13_SEG_GELAENDEWAGEN': [0, 1, 2, 3, 4, 5],\n",
       " 'KBA05_MAXBJ': [1, 2, 3, 4],\n",
       " 'KBA13_VW': [0, 1, 2, 3, 4, 5],\n",
       " 'ALTERSKATEGORIE_GROB': [1, 2, 3, 4, 9],\n",
       " 'KBA05_SEG10': [0, 1, 2, 3, 4],\n",
       " 'KBA13_SITZE_6': [0, 1, 2, 3, 4, 5],\n",
       " 'KBA13_KW_80': [0, 1, 2, 3, 4, 5],\n",
       " 'SEMIO_MAT': [1, 2, 3, 4, 5, 6, 7],\n",
       " 'KBA13_KW_121': [0, 1, 2, 3, 4, 5],\n",
       " 'SEMIO_KRIT': [1, 2, 3, 4, 5, 6, 7],\n",
       " 'FINANZ_UNAUFFAELLIGER': [1, 2, 3, 4, 5],\n",
       " 'SEMIO_KULT': [1, 2, 3, 4, 5, 6, 7],\n",
       " 'KBA13_BJ_2000': [0, 1, 2, 3, 4, 5],\n",
       " 'KBA05_ALTER1': [0, 1, 2, 3, 4],\n",
       " 'KBA13_AUTOQUOTE': [0, 1, 2, 3, 4, 5],\n",
       " 'KBA13_CCM_1600': [0, 1, 2, 3, 4, 5],\n",
       " 'KBA05_VORB2': [0, 1, 2, 3, 4, 5],\n",
       " 'KBA13_KRSSEG_VAN': [0, 1, 2, 3],\n",
       " 'SEMIO_FAM': [1, 2, 3, 4, 5, 6, 7],\n",
       " 'KBA05_HERST5': [0, 1, 2, 3, 4, 5],\n",
       " 'KBA05_SEG9': [0, 1, 2, 3],\n",
       " 'KBA05_KRSKLEIN': [1, 2, 3],\n",
       " 'D19_TELKO_ONLINE_DATUM': [1, 2, 3, 4, 5, 6, 7, 8, 9, 10],\n",
       " 'KBA05_KRSOBER': [1, 2, 3],\n",
       " 'D19_VERSAND_OFFLINE_DATUM': [1, 2, 3, 4, 5, 6, 7, 8, 9, 10],\n",
       " 'ALTER_HH': [1,\n",
       "  2,\n",
       "  3,\n",
       "  4,\n",
       "  5,\n",
       "  6,\n",
       "  7,\n",
       "  8,\n",
       "  9,\n",
       "  10,\n",
       "  11,\n",
       "  12,\n",
       "  13,\n",
       "  14,\n",
       "  15,\n",
       "  16,\n",
       "  17,\n",
       "  18,\n",
       "  19,\n",
       "  20,\n",
       "  21],\n",
       " 'TITEL_KZ': [1, 2, 3, 4, 5],\n",
       " 'KBA13_CCM_1800': [0, 1, 2, 3, 4, 5],\n",
       " 'SEMIO_SOZ': [1, 2, 3, 4, 5, 6, 7],\n",
       " 'KBA13_BJ_2004': [0, 1, 2, 3, 4, 5],\n",
       " 'KBA13_KMH_211': [0, 1, 2, 3, 4, 5],\n",
       " 'D19_BANKEN_DATUM': [1, 2, 3, 4, 5, 6, 7, 8, 9, 10],\n",
       " 'KBA13_HALTER_60': [0, 1, 2, 3, 4, 5],\n",
       " 'KBA13_KW_90': [0, 1, 2, 3, 4, 5],\n",
       " 'KBA13_SEG_OBEREMITTELKLASSE': [0, 1, 2, 3, 4, 5],\n",
       " 'KBA05_HERST4': [0, 1, 2, 3, 4, 5],\n",
       " 'KBA13_FORD': [0, 1, 2, 3, 4, 5],\n",
       " 'KBA13_HALTER_45': [0, 1, 2, 3, 4, 5],\n",
       " 'KBA05_KRSHERST3': [1, 2, 3, 4, 5],\n",
       " 'KBA13_CCM_0_1400': [0, 1, 2, 3, 4, 5],\n",
       " 'PLZ8_ANTG3': [0, 1, 2, 3],\n",
       " 'KBA05_ANTG3': [0, 1, 2, 3],\n",
       " 'FINANZ_SPARER': [1, 2, 3, 4, 5],\n",
       " 'BALLRAUM': [1, 2, 3, 4, 5, 6, 7],\n",
       " 'CAMEO_DEU_2015': ['1A',\n",
       "  '1B',\n",
       "  '1C',\n",
       "  '1D',\n",
       "  '1E',\n",
       "  '2A',\n",
       "  '2B',\n",
       "  '2C',\n",
       "  '2D',\n",
       "  '3A',\n",
       "  '3B',\n",
       "  '3C',\n",
       "  '3D',\n",
       "  '4A',\n",
       "  '4B',\n",
       "  '4C',\n",
       "  '4D',\n",
       "  '4E',\n",
       "  '5A',\n",
       "  '5B',\n",
       "  '5C',\n",
       "  '5D',\n",
       "  '5E',\n",
       "  '5F',\n",
       "  '6A',\n",
       "  '6B',\n",
       "  '6C',\n",
       "  '6D',\n",
       "  '6E',\n",
       "  '6F',\n",
       "  '7A',\n",
       "  '7B',\n",
       "  '7C',\n",
       "  '7D',\n",
       "  '7E',\n",
       "  '8A',\n",
       "  '8B',\n",
       "  '8C',\n",
       "  '8D',\n",
       "  '9A',\n",
       "  '9B',\n",
       "  '9C',\n",
       "  '9D',\n",
       "  '9E'],\n",
       " 'KBA13_AUDI': [0, 1, 2, 3, 4, 5],\n",
       " 'KBA13_CCM_1000': [0, 1, 2, 3, 4, 5],\n",
       " 'KBA05_HERST1': [0, 1, 2, 3, 4, 5],\n",
       " 'KBA05_KW3': [0, 1, 2, 3, 4],\n",
       " 'MOBI_REGIO': [1, 2, 3, 4, 5, 6],\n",
       " 'KBA13_SEG_SPORTWAGEN': [0, 1, 2, 3, 4, 5],\n",
       " 'KBA13_KW_61_120': [0, 1, 2, 3, 4, 5],\n",
       " 'KBA13_HALTER_55': [0, 1, 2, 3, 4, 5],\n",
       " 'KBA13_MOTOR': [0, 1, 2, 3, 4],\n",
       " 'KBA13_KW_40': [0, 1, 2, 3, 4, 5],\n",
       " 'FINANZTYP': [1, 2, 3, 4, 5, 6],\n",
       " 'KBA05_ALTER2': [1, 2, 3, 4, 5],\n",
       " 'KBA13_KRSZUL_NEU': [0, 1, 2, 3],\n",
       " 'KBA13_VORB_1_2': [0, 1, 2, 3, 4, 5],\n",
       " 'D19_TELKO_ANZ_24': [0, 1, 2, 3, 4, 5, 6],\n",
       " 'KBA05_MOTOR': [1, 2, 3, 4],\n",
       " 'KBA05_KRSZUL': [1, 2, 3],\n",
       " 'KBA05_VORB1': [1, 2, 3, 4, 5],\n",
       " 'KBA13_KMH_0_140': [0, 1, 2, 3, 4, 5],\n",
       " 'D19_VERSI_ANZ_12': [0, 1, 2, 3, 4, 5, 6],\n",
       " 'KBA13_SEG_MINIVANS': [0, 1, 2, 3, 4, 5],\n",
       " 'KBA05_CCM2': [1, 2, 3, 4, 5],\n",
       " 'D19_TELKO_ANZ_12': [0, 1, 2, 3, 4, 5, 6],\n",
       " 'KBA13_KMH_140': [0, 1, 2, 3, 4, 5],\n",
       " 'KBA05_MODTEMP': [1, 2, 3, 4, 5],\n",
       " 'KBA13_SEG_MITTELKLASSE': [0, 1, 2, 3, 4, 5],\n",
       " 'SEMIO_PFLICHT': [1, 2, 3, 4, 5, 6, 7],\n",
       " 'KBA13_KRSSEG_KLEIN': [0, 1, 2, 3],\n",
       " 'KBA13_SEG_UTILITIES': [0, 1, 2, 3, 4, 5],\n",
       " 'LP_FAMILIE_GROB': [1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11],\n",
       " 'KBA05_ALTER4': [0, 1, 2, 3, 4, 5],\n",
       " 'KBA13_KRSHERST_AUDI_VW': [0, 1, 2, 3, 4, 5],\n",
       " 'KBA13_CCM_2501': [0, 1, 2, 3, 4, 5],\n",
       " 'KBA13_KRSHERST_BMW_BENZ': [0, 1, 2, 3, 4, 5],\n",
       " 'HH_EINKOMMEN_SCORE': [1, 2, 3, 4, 5, 6],\n",
       " 'KBA13_MERCEDES': [0, 1, 2, 3, 4, 5],\n",
       " 'AGER_TYP': [0, 1, 2, 3],\n",
       " 'KBA13_ALTERHALTER_30': [0, 1, 2, 3, 4, 5],\n",
       " 'D19_BANKEN_ANZ_24': [0, 1, 2, 3, 4, 5, 6],\n",
       " 'KBA05_SEG3': [1, 2, 3, 4, 5],\n",
       " 'KBA05_CCM4': [0, 1, 2, 3, 4],\n",
       " 'KBA13_FAB_ASIEN': [0, 1, 2, 3, 4, 5],\n",
       " 'D19_BANKEN_ONLINE_QUOTE_12': [0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10],\n",
       " 'KBA05_MOD8': [0, 1, 2, 3],\n",
       " 'D19_VERSAND_DATUM': [1, 2, 3, 4, 5, 6, 7, 8, 9, 10]}"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "import pickle as pk \n",
    "values_dict=pk.load(open(\"Values_dictionary.pkl\",\"rb\"))\n",
    "values_dict"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "262"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "inter_cols=list(set(train.columns).intersection(set(values_dict.keys())))\n",
    "len(inter_cols)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Train increased NaNS replacing outliers with NA = 120388\n"
     ]
    }
   ],
   "source": [
    "before=train.isna().sum().sum()\n",
    "for col in inter_cols:\n",
    "    \n",
    "    values=list(set(train[col].value_counts().index).difference(set(values_dict[col])))\n",
    "    \n",
    "    if len(values) >1 :\n",
    "        \n",
    "        for value in values:\n",
    "            train[col].replace(value,np.nan,inplace=True)\n",
    "    else:\n",
    "        \n",
    "        train[col].replace(values,np.nan,inplace=True)\n",
    "            \n",
    "print(\"Train increased NaNS replacing outliers with NA = {}\".format(train.isna().sum().sum()-before))            "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [],
   "source": [
    "train.CAMEO_INTL_2015.replace(\"XX\",np.nan,inplace=True)\n",
    "train.CAMEO_DEU_2015.replace(\"XX\",np.nan,inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "['CAMEO_DEU_2015', 'OST_WEST_KZ']"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "string_cols_train=string_columns(train)\n",
    "string_cols_train"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "{'CAMEO_DEU_2015', 'OST_WEST_KZ'}"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "string_cols_azd=load_list(\"string_cols_azd\")\n",
    "string_cols_azd"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [],
   "source": [
    "train.loc[:,string_cols_train]=train[string_cols_train].astype(str).replace(\"nan\",np.nan)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Now we can see we're only left with two string categorical columns , which are `OST_WEST_KZ` and `CAMEO_DEU_2015`\n",
    "\n",
    "</br>As we saw above , we can encode them ordinaly to impute thier missing values , and after then we will decode them and one hot encode them later on\n",
    "\n",
    "</br>But column `OST_WEST_KZ` encodes are easy and don't need a OrdinalEncoder for them since they've only two levels. \n",
    "'W' , 'O'. \n",
    "- 'W' will be 1\n",
    "- 'O' will be 0"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [],
   "source": [
    "train.OST_WEST_KZ.replace(\"W\",1,inplace=True)\n",
    "train.OST_WEST_KZ.replace(\"O\",0,inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [],
   "source": [
    "from sklearn.preprocessing import OrdinalEncoder\n",
    "encoder=pk.load(open(\"CAMEO_DEU_2015_ENC.pkl\",\"rb\"))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [],
   "source": [
    "train.CAMEO_DEU_2015.replace(np.nan,\"NULL\",inplace=True)\n",
    "train.loc[:,\"CAMEO_DEU_2015\"]=encoder.transform(train.CAMEO_DEU_2015.values.reshape(-1,1)).squeeze()\n",
    "train.CAMEO_DEU_2015.replace(44,np.nan,inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "25.0    2452\n",
       "15.0    2216\n",
       "12.0    2152\n",
       "8.0     1991\n",
       "13.0    1684\n",
       "35.0    1597\n",
       "11.0    1547\n",
       "37.0    1267\n",
       "30.0    1242\n",
       "7.0     1173\n",
       "38.0    1168\n",
       "28.0    1117\n",
       "36.0     978\n",
       "6.0      942\n",
       "31.0     844\n",
       "3.0      812\n",
       "21.0     787\n",
       "26.0     761\n",
       "0.0      691\n",
       "42.0     669\n",
       "5.0      597\n",
       "40.0     515\n",
       "14.0     481\n",
       "18.0     467\n",
       "39.0     465\n",
       "16.0     447\n",
       "19.0     438\n",
       "41.0     433\n",
       "20.0     422\n",
       "29.0     400\n",
       "34.0     389\n",
       "10.0     383\n",
       "43.0     378\n",
       "32.0     376\n",
       "27.0     375\n",
       "4.0      365\n",
       "9.0      341\n",
       "2.0      313\n",
       "17.0     303\n",
       "24.0     258\n",
       "23.0     239\n",
       "33.0     213\n",
       "1.0      197\n",
       "22.0     178\n",
       "Name: CAMEO_DEU_2015, dtype: int64"
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "train.CAMEO_DEU_2015.value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "7899"
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "train.CAMEO_DEU_2015.isna().sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "348"
      ]
     },
     "execution_count": 19,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "categorical=load_list(\"cat1\")\n",
    "len(categorical)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "8"
      ]
     },
     "execution_count": 20,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "numeric=load_list(\"num1\")\n",
    "len(numeric)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(356, 357)"
      ]
     },
     "execution_count": 21,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "len(numeric) + len(categorical) , len(train.columns)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "As we can see above the only difference is in the response variable in the training data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {},
   "outputs": [],
   "source": [
    "train.loc[:,string_cols_train]=train[string_cols_train].astype(str).replace(\"nan\",np.nan)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {},
   "outputs": [],
   "source": [
    "from sklearn.experimental import enable_iterative_imputer\n",
    "from sklearn.impute import IterativeImputer\n",
    "from sklearn.linear_model import BayesianRidge\n",
    "estimator=BayesianRidge()\n",
    "imp_mean=IterativeImputer(estimator=estimator,initial_strategy=\"mean\",missing_values=np.nan)  #mean imputer for numeric values \n",
    "imp_mode=IterativeImputer(estimator=estimator,initial_strategy=\"most_frequent\",missing_values=np.nan) #mode imputer for categorical variable"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Train NaNs , Before 2104507 , After Numeric NaNs imputing 2050908\n"
     ]
    }
   ],
   "source": [
    "train_nan_before=train.isna().sum().sum()\n",
    "train.loc[:,numeric]=imp_mean.fit_transform(train[numeric])\n",
    "train_nan_after_numeric=train.isna().sum().sum()\n",
    "print(\"Train NaNs , Before {} , After Numeric NaNs imputing {}\".format(train_nan_before,train_nan_after_numeric))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Train NaNs, Before 2104507 , After all NaNs imputing 0\n"
     ]
    }
   ],
   "source": [
    "\n",
    "    \n",
    "train.loc[:,categorical]=imp_mode.fit_transform(train[categorical])\n",
    "\n",
    "\n",
    "train_nan_after_all=train.isna().sum().sum() #calculating missing values in customers dataframe After imputing \n",
    "print(\"Train NaNs, Before {} , After all NaNs imputing {}\".format(train_nan_before,train_nan_after_all))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "CPU times: user 1min 44s, sys: 11.3 ms, total: 1min 44s\n",
      "Wall time: 1min 44s\n"
     ]
    }
   ],
   "source": [
    "%%time\n",
    "for col in categorical:\n",
    "    \n",
    "    max_=train.loc[:,col].max() #maximum value in column \n",
    "    train.loc[:,col]=train.loc[:,col].apply(lambda x: np.round(x) if np.round(x) <= max_ else max_ )\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {},
   "outputs": [],
   "source": [
    "train.OST_WEST_KZ.replace(1,\"W\",inplace=True)\n",
    "train.OST_WEST_KZ.replace(0,\"O\",inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "metadata": {},
   "outputs": [],
   "source": [
    "train.loc[:,\"CAMEO_DEU_2015\"]=encoder.inverse_transform(train.CAMEO_DEU_2015.values.reshape(-1,1)).squeeze()\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "((42962, 357), 0)"
      ]
     },
     "execution_count": 31,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "train.shape,train.isna().sum().sum() "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "metadata": {},
   "outputs": [],
   "source": [
    "train.to_csv(\"train_clean.csv\",index=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Cleaning The Test Dataset"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/home/shapiro/anaconda3/lib/python3.7/site-packages/IPython/core/interactiveshell.py:3146: DtypeWarning: Columns (18,19) have mixed types.Specify dtype option on import or set low_memory=False.\n",
      "  interactivity=interactivity, compiler=compiler, result=result)\n"
     ]
    }
   ],
   "source": [
    "test=pd.read_csv(\"Udacity_MAILOUT_052018_TEST.csv\",sep=\";\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(2186757, 'Percentage : 13.948931668665152 %')"
      ]
     },
     "execution_count": 34,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "test.isna().sum().sum(),\"Percentage : {} %\".format((test.isna().sum().sum()/(test.shape[0]*test.shape[1]))*100)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We can see that our test set has approximately 14% of it's values missing"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Let's check its shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(42833, 366)"
      ]
     },
     "execution_count": 35,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "test.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "metadata": {},
   "outputs": [],
   "source": [
    "wanted_columns=list(load_list(\"Wanted_Columns\"))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(42833, 356)"
      ]
     },
     "execution_count": 37,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "test=test[wanted_columns]\n",
    "test.shape #should be (X,516)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Let's do the exact same things we did with our training data set"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Test increased NaNS replacing outliers with NA = 118973\n"
     ]
    }
   ],
   "source": [
    "before=test.isna().sum().sum()\n",
    "for col in inter_cols:\n",
    "    \n",
    "    values=list(set(test[col].value_counts().index).difference(set(values_dict[col])))\n",
    "    \n",
    "    if len(values) >1 :\n",
    "        \n",
    "        for value in values:\n",
    "            test[col].replace(value,np.nan,inplace=True)\n",
    "    else:\n",
    "        \n",
    "        test[col].replace(values,np.nan,inplace=True)\n",
    "            \n",
    "print(\"Test increased NaNS replacing outliers with NA = {}\".format(test.isna().sum().sum()-before))            "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 39,
   "metadata": {},
   "outputs": [],
   "source": [
    "test.CAMEO_INTL_2015.replace(\"XX\",np.nan,inplace=True)\n",
    "test.CAMEO_DEU_2015.replace(\"XX\",np.nan,inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 40,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "['CAMEO_DEU_2015', 'OST_WEST_KZ']"
      ]
     },
     "execution_count": 40,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "string_cols_test=string_columns(test)\n",
    "string_cols_test"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 41,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "{'CAMEO_DEU_2015', 'OST_WEST_KZ'}"
      ]
     },
     "execution_count": 41,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "string_cols_azd=load_list(\"string_cols_azd\")\n",
    "string_cols_azd"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 42,
   "metadata": {},
   "outputs": [],
   "source": [
    "test.loc[:,string_cols_test]=test[string_cols_test].astype(str).replace(\"nan\",np.nan)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Now we can see we're only left with two string categorical columns , which are `OST_WEST_KZ` and `CAMEO_DEU_2015`\n",
    "\n",
    "</br>As we saw above , we can encode them ordinaly to impute thier missing values , and after then we will decode them and one hot encode them later on\n",
    "\n",
    "</br>But column `OST_WEST_KZ` encodes are easy and don't need a OrdinalEncoder for them since they've only two levels. \n",
    "'W' , 'O'. \n",
    "- 'W' will be 1\n",
    "- 'O' will be 0"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 43,
   "metadata": {},
   "outputs": [],
   "source": [
    "test.OST_WEST_KZ.replace(\"W\",1,inplace=True)\n",
    "test.OST_WEST_KZ.replace(\"O\",0,inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 44,
   "metadata": {},
   "outputs": [],
   "source": [
    "from sklearn.preprocessing import OrdinalEncoder\n",
    "encoder=pk.load(open(\"CAMEO_DEU_2015_ENC.pkl\",\"rb\"))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 45,
   "metadata": {},
   "outputs": [],
   "source": [
    "test.CAMEO_DEU_2015.replace(np.nan,\"NULL\",inplace=True)\n",
    "test.loc[:,\"CAMEO_DEU_2015\"]=encoder.transform(test.CAMEO_DEU_2015.values.reshape(-1,1)).squeeze()\n",
    "test.CAMEO_DEU_2015.replace(44,np.nan,inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 46,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "25.0    2497\n",
       "15.0    2257\n",
       "12.0    2150\n",
       "8.0     1994\n",
       "13.0    1769\n",
       "35.0    1629\n",
       "11.0    1501\n",
       "37.0    1323\n",
       "30.0    1243\n",
       "7.0     1199\n",
       "28.0    1141\n",
       "38.0    1133\n",
       "36.0    1033\n",
       "6.0      920\n",
       "31.0     829\n",
       "3.0      824\n",
       "21.0     813\n",
       "26.0     699\n",
       "0.0      679\n",
       "42.0     662\n",
       "5.0      591\n",
       "40.0     484\n",
       "19.0     459\n",
       "14.0     451\n",
       "20.0     451\n",
       "18.0     446\n",
       "16.0     436\n",
       "41.0     429\n",
       "39.0     429\n",
       "34.0     405\n",
       "29.0     404\n",
       "43.0     403\n",
       "27.0     399\n",
       "10.0     356\n",
       "4.0      331\n",
       "17.0     326\n",
       "32.0     325\n",
       "2.0      310\n",
       "9.0      310\n",
       "1.0      231\n",
       "24.0     229\n",
       "23.0     201\n",
       "33.0     195\n",
       "22.0     190\n",
       "Name: CAMEO_DEU_2015, dtype: int64"
      ]
     },
     "execution_count": 46,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "test.CAMEO_DEU_2015.value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 47,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "7747"
      ]
     },
     "execution_count": 47,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "test.CAMEO_DEU_2015.isna().sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 48,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "348"
      ]
     },
     "execution_count": 48,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "categorical=load_list(\"cat1\")\n",
    "len(categorical)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 49,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "8"
      ]
     },
     "execution_count": 49,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "numeric=load_list(\"num1\")\n",
    "len(numeric)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 50,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(356, 356)"
      ]
     },
     "execution_count": 50,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "len(numeric) + len(categorical) , len(test.columns)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "As we can see above the only difference is in the response variable in the training data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 51,
   "metadata": {},
   "outputs": [],
   "source": [
    "test.loc[:,string_cols_test]=test[string_cols_test].astype(str).replace(\"nan\",np.nan)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 52,
   "metadata": {},
   "outputs": [],
   "source": [
    "from sklearn.experimental import enable_iterative_imputer\n",
    "from sklearn.impute import IterativeImputer\n",
    "from sklearn.linear_model import BayesianRidge\n",
    "estimator=BayesianRidge()\n",
    "imp_mean=IterativeImputer(estimator=estimator,initial_strategy=\"mean\",missing_values=np.nan)  #mean imputer for numeric values \n",
    "imp_mode=IterativeImputer(estimator=estimator,initial_strategy=\"most_frequent\",missing_values=np.nan) #mode imputer for categorical variable"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 53,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Test NaNs , Before 2074066 , After Numeric NaNs imputing 2021300\n"
     ]
    }
   ],
   "source": [
    "test_nan_before=test.isna().sum().sum()\n",
    "test.loc[:,numeric]=imp_mean.fit_transform(test[numeric])\n",
    "test_nan_after_numeric=test.isna().sum().sum()\n",
    "print(\"Test NaNs , Before {} , After Numeric NaNs imputing {}\".format(test_nan_before,test_nan_after_numeric))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 54,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Train NaNs, Before 2074066 , After all NaNs imputing 0\n",
      "CPU times: user 50min 2s, sys: 20min 38s, total: 1h 10min 41s\n",
      "Wall time: 8min 27s\n"
     ]
    }
   ],
   "source": [
    "%%time\n",
    "    \n",
    "test.loc[:,categorical]=imp_mode.fit_transform(test[categorical])\n",
    "\n",
    "\n",
    "test_nan_after_all=test.isna().sum().sum() #calculating missing values in customers dataframe After imputing \n",
    "print(\"Train NaNs, Before {} , After all NaNs imputing {}\".format(test_nan_before,test_nan_after_all))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 55,
   "metadata": {},
   "outputs": [],
   "source": [
    "for col in categorical:\n",
    "    \n",
    "    max_=test.loc[:,col].max() #maximum value in column \n",
    "    test.loc[:,col]=test.loc[:,col].apply(lambda x: np.round(x) if np.round(x) <= max_ else max_ )\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 56,
   "metadata": {},
   "outputs": [],
   "source": [
    "test.OST_WEST_KZ.replace(1,\"W\",inplace=True)\n",
    "test.OST_WEST_KZ.replace(0,\"O\",inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 57,
   "metadata": {},
   "outputs": [],
   "source": [
    "test.loc[:,\"CAMEO_DEU_2015\"]=encoder.inverse_transform(test.CAMEO_DEU_2015.values.reshape(-1,1)).squeeze()\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 58,
   "metadata": {},
   "outputs": [],
   "source": [
    "test.to_csv(\"test_clean.csv\",index=False)"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.7.8"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}