DataCleaning.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Merging Demographic and Spending Data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "Exception ignored in: <function ZipFile.__del__ at 0x000002A0A94039C0>\n",
      "Traceback (most recent call last):\n",
      "  File \"c:\\ProgramData\\anaconda3\\Lib\\zipfile.py\", line 1872, in __del__\n",
      "    self.close()\n",
      "  File \"c:\\ProgramData\\anaconda3\\Lib\\zipfile.py\", line 1874, in close\n",
      "    def close(self):\n",
      "\n",
      "KeyboardInterrupt: \n"
     ]
    },
    {
     "ename": "KeyboardInterrupt",
     "evalue": "",
     "output_type": "error",
     "traceback": [
      "\u001b[1;31m---------------------------------------------------------------------------\u001b[0m",
      "\u001b[1;31mKeyboardInterrupt\u001b[0m                         Traceback (most recent call last)",
      "Cell \u001b[1;32mIn[21], line 5\u001b[0m\n\u001b[0;32m      2\u001b[0m \u001b[38;5;28;01mfrom\u001b[39;00m \u001b[38;5;21;01msklearn\u001b[39;00m\u001b[38;5;21;01m.\u001b[39;00m\u001b[38;5;21;01mpreprocessing\u001b[39;00m \u001b[38;5;28;01mimport\u001b[39;00m LabelEncoder\n\u001b[0;32m      4\u001b[0m \u001b[38;5;66;03m# load and merge files\u001b[39;00m\n\u001b[1;32m----> 5\u001b[0m transaction_data \u001b[38;5;241m=\u001b[39m pd\u001b[38;5;241m.\u001b[39mread_excel(\u001b[38;5;124m\"\u001b[39m\u001b[38;5;124mkartdata1.xlsx\u001b[39m\u001b[38;5;124m\"\u001b[39m)\n\u001b[0;32m      6\u001b[0m demographical_data \u001b[38;5;241m=\u001b[39m pd\u001b[38;5;241m.\u001b[39mread_excel(\u001b[38;5;124m\"\u001b[39m\u001b[38;5;124mdemografik.xlsx\u001b[39m\u001b[38;5;124m\"\u001b[39m)\n\u001b[0;32m      8\u001b[0m df_fs \u001b[38;5;241m=\u001b[39m pd\u001b[38;5;241m.\u001b[39mmerge(transaction_data, demographical_data, on\u001b[38;5;241m=\u001b[39m\u001b[38;5;124m\"\u001b[39m\u001b[38;5;124mCustomer_id\u001b[39m\u001b[38;5;124m\"\u001b[39m, how\u001b[38;5;241m=\u001b[39m\u001b[38;5;124m\"\u001b[39m\u001b[38;5;124mleft\u001b[39m\u001b[38;5;124m\"\u001b[39m)\n",
      "File \u001b[1;32mc:\\ProgramData\\anaconda3\\Lib\\site-packages\\pandas\\util\\_decorators.py:211\u001b[0m, in \u001b[0;36mdeprecate_kwarg.<locals>._deprecate_kwarg.<locals>.wrapper\u001b[1;34m(*args, **kwargs)\u001b[0m\n\u001b[0;32m    209\u001b[0m     \u001b[38;5;28;01melse\u001b[39;00m:\n\u001b[0;32m    210\u001b[0m         kwargs[new_arg_name] \u001b[38;5;241m=\u001b[39m new_arg_value\n\u001b[1;32m--> 211\u001b[0m \u001b[38;5;28;01mreturn\u001b[39;00m func(\u001b[38;5;241m*\u001b[39margs, \u001b[38;5;241m*\u001b[39m\u001b[38;5;241m*\u001b[39mkwargs)\n",
      "File \u001b[1;32mc:\\ProgramData\\anaconda3\\Lib\\site-packages\\pandas\\util\\_decorators.py:331\u001b[0m, in \u001b[0;36mdeprecate_nonkeyword_arguments.<locals>.decorate.<locals>.wrapper\u001b[1;34m(*args, **kwargs)\u001b[0m\n\u001b[0;32m    325\u001b[0m \u001b[38;5;28;01mif\u001b[39;00m \u001b[38;5;28mlen\u001b[39m(args) \u001b[38;5;241m>\u001b[39m num_allow_args:\n\u001b[0;32m    326\u001b[0m     warnings\u001b[38;5;241m.\u001b[39mwarn(\n\u001b[0;32m    327\u001b[0m         msg\u001b[38;5;241m.\u001b[39mformat(arguments\u001b[38;5;241m=\u001b[39m_format_argument_list(allow_args)),\n\u001b[0;32m    328\u001b[0m         \u001b[38;5;167;01mFutureWarning\u001b[39;00m,\n\u001b[0;32m    329\u001b[0m         stacklevel\u001b[38;5;241m=\u001b[39mfind_stack_level(),\n\u001b[0;32m    330\u001b[0m     )\n\u001b[1;32m--> 331\u001b[0m \u001b[38;5;28;01mreturn\u001b[39;00m func(\u001b[38;5;241m*\u001b[39margs, \u001b[38;5;241m*\u001b[39m\u001b[38;5;241m*\u001b[39mkwargs)\n",
      "File \u001b[1;32mc:\\ProgramData\\anaconda3\\Lib\\site-packages\\pandas\\io\\excel\\_base.py:490\u001b[0m, in \u001b[0;36mread_excel\u001b[1;34m(io, sheet_name, header, names, index_col, usecols, squeeze, dtype, engine, converters, true_values, false_values, skiprows, nrows, na_values, keep_default_na, na_filter, verbose, parse_dates, date_parser, thousands, decimal, comment, skipfooter, convert_float, mangle_dupe_cols, storage_options)\u001b[0m\n\u001b[0;32m    484\u001b[0m     \u001b[38;5;28;01mraise\u001b[39;00m \u001b[38;5;167;01mValueError\u001b[39;00m(\n\u001b[0;32m    485\u001b[0m         \u001b[38;5;124m\"\u001b[39m\u001b[38;5;124mEngine should not be specified when passing \u001b[39m\u001b[38;5;124m\"\u001b[39m\n\u001b[0;32m    486\u001b[0m         \u001b[38;5;124m\"\u001b[39m\u001b[38;5;124man ExcelFile - ExcelFile already has the engine set\u001b[39m\u001b[38;5;124m\"\u001b[39m\n\u001b[0;32m    487\u001b[0m     )\n\u001b[0;32m    489\u001b[0m \u001b[38;5;28;01mtry\u001b[39;00m:\n\u001b[1;32m--> 490\u001b[0m     data \u001b[38;5;241m=\u001b[39m io\u001b[38;5;241m.\u001b[39mparse(\n\u001b[0;32m    491\u001b[0m         sheet_name\u001b[38;5;241m=\u001b[39msheet_name,\n\u001b[0;32m    492\u001b[0m         header\u001b[38;5;241m=\u001b[39mheader,\n\u001b[0;32m    493\u001b[0m         names\u001b[38;5;241m=\u001b[39mnames,\n\u001b[0;32m    494\u001b[0m         index_col\u001b[38;5;241m=\u001b[39mindex_col,\n\u001b[0;32m    495\u001b[0m         usecols\u001b[38;5;241m=\u001b[39musecols,\n\u001b[0;32m    496\u001b[0m         squeeze\u001b[38;5;241m=\u001b[39msqueeze,\n\u001b[0;32m    497\u001b[0m         dtype\u001b[38;5;241m=\u001b[39mdtype,\n\u001b[0;32m    498\u001b[0m         converters\u001b[38;5;241m=\u001b[39mconverters,\n\u001b[0;32m    499\u001b[0m         true_values\u001b[38;5;241m=\u001b[39mtrue_values,\n\u001b[0;32m    500\u001b[0m         false_values\u001b[38;5;241m=\u001b[39mfalse_values,\n\u001b[0;32m    501\u001b[0m         skiprows\u001b[38;5;241m=\u001b[39mskiprows,\n\u001b[0;32m    502\u001b[0m         nrows\u001b[38;5;241m=\u001b[39mnrows,\n\u001b[0;32m    503\u001b[0m         na_values\u001b[38;5;241m=\u001b[39mna_values,\n\u001b[0;32m    504\u001b[0m         keep_default_na\u001b[38;5;241m=\u001b[39mkeep_default_na,\n\u001b[0;32m    505\u001b[0m         na_filter\u001b[38;5;241m=\u001b[39mna_filter,\n\u001b[0;32m    506\u001b[0m         verbose\u001b[38;5;241m=\u001b[39mverbose,\n\u001b[0;32m    507\u001b[0m         parse_dates\u001b[38;5;241m=\u001b[39mparse_dates,\n\u001b[0;32m    508\u001b[0m         date_parser\u001b[38;5;241m=\u001b[39mdate_parser,\n\u001b[0;32m    509\u001b[0m         thousands\u001b[38;5;241m=\u001b[39mthousands,\n\u001b[0;32m    510\u001b[0m         decimal\u001b[38;5;241m=\u001b[39mdecimal,\n\u001b[0;32m    511\u001b[0m         comment\u001b[38;5;241m=\u001b[39mcomment,\n\u001b[0;32m    512\u001b[0m         skipfooter\u001b[38;5;241m=\u001b[39mskipfooter,\n\u001b[0;32m    513\u001b[0m         convert_float\u001b[38;5;241m=\u001b[39mconvert_float,\n\u001b[0;32m    514\u001b[0m         mangle_dupe_cols\u001b[38;5;241m=\u001b[39mmangle_dupe_cols,\n\u001b[0;32m    515\u001b[0m     )\n\u001b[0;32m    516\u001b[0m \u001b[38;5;28;01mfinally\u001b[39;00m:\n\u001b[0;32m    517\u001b[0m     \u001b[38;5;66;03m# make sure to close opened file handles\u001b[39;00m\n\u001b[0;32m    518\u001b[0m     \u001b[38;5;28;01mif\u001b[39;00m should_close:\n",
      "File \u001b[1;32mc:\\ProgramData\\anaconda3\\Lib\\site-packages\\pandas\\io\\excel\\_base.py:1734\u001b[0m, in \u001b[0;36mExcelFile.parse\u001b[1;34m(self, sheet_name, header, names, index_col, usecols, squeeze, converters, true_values, false_values, skiprows, nrows, na_values, parse_dates, date_parser, thousands, comment, skipfooter, convert_float, mangle_dupe_cols, **kwds)\u001b[0m\n\u001b[0;32m   1700\u001b[0m \u001b[38;5;28;01mdef\u001b[39;00m \u001b[38;5;21mparse\u001b[39m(\n\u001b[0;32m   1701\u001b[0m     \u001b[38;5;28mself\u001b[39m,\n\u001b[0;32m   1702\u001b[0m     sheet_name: \u001b[38;5;28mstr\u001b[39m \u001b[38;5;241m|\u001b[39m \u001b[38;5;28mint\u001b[39m \u001b[38;5;241m|\u001b[39m \u001b[38;5;28mlist\u001b[39m[\u001b[38;5;28mint\u001b[39m] \u001b[38;5;241m|\u001b[39m \u001b[38;5;28mlist\u001b[39m[\u001b[38;5;28mstr\u001b[39m] \u001b[38;5;241m|\u001b[39m \u001b[38;5;28;01mNone\u001b[39;00m \u001b[38;5;241m=\u001b[39m \u001b[38;5;241m0\u001b[39m,\n\u001b[1;32m   (...)\u001b[0m\n\u001b[0;32m   1721\u001b[0m     \u001b[38;5;241m*\u001b[39m\u001b[38;5;241m*\u001b[39mkwds,\n\u001b[0;32m   1722\u001b[0m ) \u001b[38;5;241m-\u001b[39m\u001b[38;5;241m>\u001b[39m DataFrame \u001b[38;5;241m|\u001b[39m \u001b[38;5;28mdict\u001b[39m[\u001b[38;5;28mstr\u001b[39m, DataFrame] \u001b[38;5;241m|\u001b[39m \u001b[38;5;28mdict\u001b[39m[\u001b[38;5;28mint\u001b[39m, DataFrame]:\n\u001b[0;32m   1723\u001b[0m \u001b[38;5;250m    \u001b[39m\u001b[38;5;124;03m\"\"\"\u001b[39;00m\n\u001b[0;32m   1724\u001b[0m \u001b[38;5;124;03m    Parse specified sheet(s) into a DataFrame.\u001b[39;00m\n\u001b[0;32m   1725\u001b[0m \n\u001b[1;32m   (...)\u001b[0m\n\u001b[0;32m   1732\u001b[0m \u001b[38;5;124;03m        DataFrame from the passed in Excel file.\u001b[39;00m\n\u001b[0;32m   1733\u001b[0m \u001b[38;5;124;03m    \"\"\"\u001b[39;00m\n\u001b[1;32m-> 1734\u001b[0m     \u001b[38;5;28;01mreturn\u001b[39;00m \u001b[38;5;28mself\u001b[39m\u001b[38;5;241m.\u001b[39m_reader\u001b[38;5;241m.\u001b[39mparse(\n\u001b[0;32m   1735\u001b[0m         sheet_name\u001b[38;5;241m=\u001b[39msheet_name,\n\u001b[0;32m   1736\u001b[0m         header\u001b[38;5;241m=\u001b[39mheader,\n\u001b[0;32m   1737\u001b[0m         names\u001b[38;5;241m=\u001b[39mnames,\n\u001b[0;32m   1738\u001b[0m         index_col\u001b[38;5;241m=\u001b[39mindex_col,\n\u001b[0;32m   1739\u001b[0m         usecols\u001b[38;5;241m=\u001b[39musecols,\n\u001b[0;32m   1740\u001b[0m         squeeze\u001b[38;5;241m=\u001b[39msqueeze,\n\u001b[0;32m   1741\u001b[0m         converters\u001b[38;5;241m=\u001b[39mconverters,\n\u001b[0;32m   1742\u001b[0m         true_values\u001b[38;5;241m=\u001b[39mtrue_values,\n\u001b[0;32m   1743\u001b[0m         false_values\u001b[38;5;241m=\u001b[39mfalse_values,\n\u001b[0;32m   1744\u001b[0m         skiprows\u001b[38;5;241m=\u001b[39mskiprows,\n\u001b[0;32m   1745\u001b[0m         nrows\u001b[38;5;241m=\u001b[39mnrows,\n\u001b[0;32m   1746\u001b[0m         na_values\u001b[38;5;241m=\u001b[39mna_values,\n\u001b[0;32m   1747\u001b[0m         parse_dates\u001b[38;5;241m=\u001b[39mparse_dates,\n\u001b[0;32m   1748\u001b[0m         date_parser\u001b[38;5;241m=\u001b[39mdate_parser,\n\u001b[0;32m   1749\u001b[0m         thousands\u001b[38;5;241m=\u001b[39mthousands,\n\u001b[0;32m   1750\u001b[0m         comment\u001b[38;5;241m=\u001b[39mcomment,\n\u001b[0;32m   1751\u001b[0m         skipfooter\u001b[38;5;241m=\u001b[39mskipfooter,\n\u001b[0;32m   1752\u001b[0m         convert_float\u001b[38;5;241m=\u001b[39mconvert_float,\n\u001b[0;32m   1753\u001b[0m         mangle_dupe_cols\u001b[38;5;241m=\u001b[39mmangle_dupe_cols,\n\u001b[0;32m   1754\u001b[0m         \u001b[38;5;241m*\u001b[39m\u001b[38;5;241m*\u001b[39mkwds,\n\u001b[0;32m   1755\u001b[0m     )\n",
      "File \u001b[1;32mc:\\ProgramData\\anaconda3\\Lib\\site-packages\\pandas\\io\\excel\\_base.py:765\u001b[0m, in \u001b[0;36mBaseExcelReader.parse\u001b[1;34m(self, sheet_name, header, names, index_col, usecols, squeeze, dtype, true_values, false_values, skiprows, nrows, na_values, verbose, parse_dates, date_parser, thousands, decimal, comment, skipfooter, convert_float, mangle_dupe_cols, **kwds)\u001b[0m\n\u001b[0;32m    762\u001b[0m     sheet \u001b[38;5;241m=\u001b[39m \u001b[38;5;28mself\u001b[39m\u001b[38;5;241m.\u001b[39mget_sheet_by_index(asheetname)\n\u001b[0;32m    764\u001b[0m file_rows_needed \u001b[38;5;241m=\u001b[39m \u001b[38;5;28mself\u001b[39m\u001b[38;5;241m.\u001b[39m_calc_rows(header, index_col, skiprows, nrows)\n\u001b[1;32m--> 765\u001b[0m data \u001b[38;5;241m=\u001b[39m \u001b[38;5;28mself\u001b[39m\u001b[38;5;241m.\u001b[39mget_sheet_data(sheet, convert_float, file_rows_needed)\n\u001b[0;32m    766\u001b[0m \u001b[38;5;28;01mif\u001b[39;00m \u001b[38;5;28mhasattr\u001b[39m(sheet, \u001b[38;5;124m\"\u001b[39m\u001b[38;5;124mclose\u001b[39m\u001b[38;5;124m\"\u001b[39m):\n\u001b[0;32m    767\u001b[0m     \u001b[38;5;66;03m# pyxlsb opens two TemporaryFiles\u001b[39;00m\n\u001b[0;32m    768\u001b[0m     sheet\u001b[38;5;241m.\u001b[39mclose()\n",
      "File \u001b[1;32mc:\\ProgramData\\anaconda3\\Lib\\site-packages\\pandas\\io\\excel\\_openpyxl.py:615\u001b[0m, in \u001b[0;36mOpenpyxlReader.get_sheet_data\u001b[1;34m(self, sheet, convert_float, file_rows_needed)\u001b[0m\n\u001b[0;32m    613\u001b[0m data: \u001b[38;5;28mlist\u001b[39m[\u001b[38;5;28mlist\u001b[39m[Scalar]] \u001b[38;5;241m=\u001b[39m []\n\u001b[0;32m    614\u001b[0m last_row_with_data \u001b[38;5;241m=\u001b[39m \u001b[38;5;241m-\u001b[39m\u001b[38;5;241m1\u001b[39m\n\u001b[1;32m--> 615\u001b[0m \u001b[38;5;28;01mfor\u001b[39;00m row_number, row \u001b[38;5;129;01min\u001b[39;00m \u001b[38;5;28menumerate\u001b[39m(sheet\u001b[38;5;241m.\u001b[39mrows):\n\u001b[0;32m    616\u001b[0m     converted_row \u001b[38;5;241m=\u001b[39m [\u001b[38;5;28mself\u001b[39m\u001b[38;5;241m.\u001b[39m_convert_cell(cell, convert_float) \u001b[38;5;28;01mfor\u001b[39;00m cell \u001b[38;5;129;01min\u001b[39;00m row]\n\u001b[0;32m    617\u001b[0m     \u001b[38;5;28;01mwhile\u001b[39;00m converted_row \u001b[38;5;129;01mand\u001b[39;00m converted_row[\u001b[38;5;241m-\u001b[39m\u001b[38;5;241m1\u001b[39m] \u001b[38;5;241m==\u001b[39m \u001b[38;5;124m\"\u001b[39m\u001b[38;5;124m\"\u001b[39m:\n\u001b[0;32m    618\u001b[0m         \u001b[38;5;66;03m# trim trailing empty elements\u001b[39;00m\n",
      "File \u001b[1;32mc:\\ProgramData\\anaconda3\\Lib\\site-packages\\openpyxl\\worksheet\\_read_only.py:79\u001b[0m, in \u001b[0;36mReadOnlyWorksheet._cells_by_row\u001b[1;34m(self, min_col, min_row, max_col, max_row, values_only)\u001b[0m\n\u001b[0;32m     75\u001b[0m src \u001b[38;5;241m=\u001b[39m \u001b[38;5;28mself\u001b[39m\u001b[38;5;241m.\u001b[39m_get_source()\n\u001b[0;32m     76\u001b[0m parser \u001b[38;5;241m=\u001b[39m WorkSheetParser(src, \u001b[38;5;28mself\u001b[39m\u001b[38;5;241m.\u001b[39m_shared_strings,\n\u001b[0;32m     77\u001b[0m                          data_only\u001b[38;5;241m=\u001b[39m\u001b[38;5;28mself\u001b[39m\u001b[38;5;241m.\u001b[39mparent\u001b[38;5;241m.\u001b[39mdata_only, epoch\u001b[38;5;241m=\u001b[39m\u001b[38;5;28mself\u001b[39m\u001b[38;5;241m.\u001b[39mparent\u001b[38;5;241m.\u001b[39mepoch,\n\u001b[0;32m     78\u001b[0m                          date_formats\u001b[38;5;241m=\u001b[39m\u001b[38;5;28mself\u001b[39m\u001b[38;5;241m.\u001b[39mparent\u001b[38;5;241m.\u001b[39m_date_formats)\n\u001b[1;32m---> 79\u001b[0m \u001b[38;5;28;01mfor\u001b[39;00m idx, row \u001b[38;5;129;01min\u001b[39;00m parser\u001b[38;5;241m.\u001b[39mparse():\n\u001b[0;32m     80\u001b[0m     \u001b[38;5;28;01mif\u001b[39;00m max_row \u001b[38;5;129;01mis\u001b[39;00m \u001b[38;5;129;01mnot\u001b[39;00m \u001b[38;5;28;01mNone\u001b[39;00m \u001b[38;5;129;01mand\u001b[39;00m idx \u001b[38;5;241m>\u001b[39m max_row:\n\u001b[0;32m     81\u001b[0m         \u001b[38;5;28;01mbreak\u001b[39;00m\n",
      "File \u001b[1;32mc:\\ProgramData\\anaconda3\\Lib\\site-packages\\openpyxl\\worksheet\\_reader.py:144\u001b[0m, in \u001b[0;36mparse\u001b[1;34m()\u001b[0m\n",
      "File \u001b[1;32mc:\\ProgramData\\anaconda3\\Lib\\xml\\etree\\ElementTree.py:1251\u001b[0m, in \u001b[0;36miterparse.<locals>.iterator\u001b[1;34m(source)\u001b[0m\n\u001b[0;32m   1249\u001b[0m \u001b[38;5;28;01myield from\u001b[39;00m pullparser\u001b[38;5;241m.\u001b[39mread_events()\n\u001b[0;32m   1250\u001b[0m \u001b[38;5;66;03m# load event buffer\u001b[39;00m\n\u001b[1;32m-> 1251\u001b[0m data \u001b[38;5;241m=\u001b[39m source\u001b[38;5;241m.\u001b[39mread(\u001b[38;5;241m16\u001b[39m \u001b[38;5;241m*\u001b[39m \u001b[38;5;241m1024\u001b[39m)\n\u001b[0;32m   1252\u001b[0m \u001b[38;5;28;01mif\u001b[39;00m \u001b[38;5;129;01mnot\u001b[39;00m data:\n\u001b[0;32m   1253\u001b[0m     \u001b[38;5;28;01mbreak\u001b[39;00m\n",
      "File \u001b[1;32mc:\\ProgramData\\anaconda3\\Lib\\zipfile.py:953\u001b[0m, in \u001b[0;36mZipExtFile.read\u001b[1;34m(self, n)\u001b[0m\n\u001b[0;32m    951\u001b[0m \u001b[38;5;28mself\u001b[39m\u001b[38;5;241m.\u001b[39m_offset \u001b[38;5;241m=\u001b[39m \u001b[38;5;241m0\u001b[39m\n\u001b[0;32m    952\u001b[0m \u001b[38;5;28;01mwhile\u001b[39;00m n \u001b[38;5;241m>\u001b[39m \u001b[38;5;241m0\u001b[39m \u001b[38;5;129;01mand\u001b[39;00m \u001b[38;5;129;01mnot\u001b[39;00m \u001b[38;5;28mself\u001b[39m\u001b[38;5;241m.\u001b[39m_eof:\n\u001b[1;32m--> 953\u001b[0m     data \u001b[38;5;241m=\u001b[39m \u001b[38;5;28mself\u001b[39m\u001b[38;5;241m.\u001b[39m_read1(n)\n\u001b[0;32m    954\u001b[0m     \u001b[38;5;28;01mif\u001b[39;00m n \u001b[38;5;241m<\u001b[39m \u001b[38;5;28mlen\u001b[39m(data):\n\u001b[0;32m    955\u001b[0m         \u001b[38;5;28mself\u001b[39m\u001b[38;5;241m.\u001b[39m_readbuffer \u001b[38;5;241m=\u001b[39m data\n",
      "File \u001b[1;32mc:\\ProgramData\\anaconda3\\Lib\\zipfile.py:1021\u001b[0m, in \u001b[0;36mZipExtFile._read1\u001b[1;34m(self, n)\u001b[0m\n\u001b[0;32m   1019\u001b[0m     data \u001b[38;5;241m=\u001b[39m \u001b[38;5;28mself\u001b[39m\u001b[38;5;241m.\u001b[39m_decompressor\u001b[38;5;241m.\u001b[39munconsumed_tail\n\u001b[0;32m   1020\u001b[0m     \u001b[38;5;28;01mif\u001b[39;00m n \u001b[38;5;241m>\u001b[39m \u001b[38;5;28mlen\u001b[39m(data):\n\u001b[1;32m-> 1021\u001b[0m         data \u001b[38;5;241m+\u001b[39m\u001b[38;5;241m=\u001b[39m \u001b[38;5;28mself\u001b[39m\u001b[38;5;241m.\u001b[39m_read2(n \u001b[38;5;241m-\u001b[39m \u001b[38;5;28mlen\u001b[39m(data))\n\u001b[0;32m   1022\u001b[0m \u001b[38;5;28;01melse\u001b[39;00m:\n\u001b[0;32m   1023\u001b[0m     data \u001b[38;5;241m=\u001b[39m \u001b[38;5;28mself\u001b[39m\u001b[38;5;241m.\u001b[39m_read2(n)\n",
      "File \u001b[1;32mc:\\ProgramData\\anaconda3\\Lib\\zipfile.py:1053\u001b[0m, in \u001b[0;36mZipExtFile._read2\u001b[1;34m(self, n)\u001b[0m\n\u001b[0;32m   1050\u001b[0m n \u001b[38;5;241m=\u001b[39m \u001b[38;5;28mmax\u001b[39m(n, \u001b[38;5;28mself\u001b[39m\u001b[38;5;241m.\u001b[39mMIN_READ_SIZE)\n\u001b[0;32m   1051\u001b[0m n \u001b[38;5;241m=\u001b[39m \u001b[38;5;28mmin\u001b[39m(n, \u001b[38;5;28mself\u001b[39m\u001b[38;5;241m.\u001b[39m_compress_left)\n\u001b[1;32m-> 1053\u001b[0m data \u001b[38;5;241m=\u001b[39m \u001b[38;5;28mself\u001b[39m\u001b[38;5;241m.\u001b[39m_fileobj\u001b[38;5;241m.\u001b[39mread(n)\n\u001b[0;32m   1054\u001b[0m \u001b[38;5;28mself\u001b[39m\u001b[38;5;241m.\u001b[39m_compress_left \u001b[38;5;241m-\u001b[39m\u001b[38;5;241m=\u001b[39m \u001b[38;5;28mlen\u001b[39m(data)\n\u001b[0;32m   1055\u001b[0m \u001b[38;5;28;01mif\u001b[39;00m \u001b[38;5;129;01mnot\u001b[39;00m data:\n",
      "File \u001b[1;32mc:\\ProgramData\\anaconda3\\Lib\\zipfile.py:773\u001b[0m, in \u001b[0;36m_SharedFile.read\u001b[1;34m(self, n)\u001b[0m\n\u001b[0;32m    769\u001b[0m     \u001b[38;5;28;01mraise\u001b[39;00m \u001b[38;5;167;01mValueError\u001b[39;00m(\u001b[38;5;124m\"\u001b[39m\u001b[38;5;124mCan\u001b[39m\u001b[38;5;124m'\u001b[39m\u001b[38;5;124mt read from the ZIP file while there \u001b[39m\u001b[38;5;124m\"\u001b[39m\n\u001b[0;32m    770\u001b[0m             \u001b[38;5;124m\"\u001b[39m\u001b[38;5;124mis an open writing handle on it. \u001b[39m\u001b[38;5;124m\"\u001b[39m\n\u001b[0;32m    771\u001b[0m             \u001b[38;5;124m\"\u001b[39m\u001b[38;5;124mClose the writing handle before trying to read.\u001b[39m\u001b[38;5;124m\"\u001b[39m)\n\u001b[0;32m    772\u001b[0m \u001b[38;5;28mself\u001b[39m\u001b[38;5;241m.\u001b[39m_file\u001b[38;5;241m.\u001b[39mseek(\u001b[38;5;28mself\u001b[39m\u001b[38;5;241m.\u001b[39m_pos)\n\u001b[1;32m--> 773\u001b[0m data \u001b[38;5;241m=\u001b[39m \u001b[38;5;28mself\u001b[39m\u001b[38;5;241m.\u001b[39m_file\u001b[38;5;241m.\u001b[39mread(n)\n\u001b[0;32m    774\u001b[0m \u001b[38;5;28mself\u001b[39m\u001b[38;5;241m.\u001b[39m_pos \u001b[38;5;241m=\u001b[39m \u001b[38;5;28mself\u001b[39m\u001b[38;5;241m.\u001b[39m_file\u001b[38;5;241m.\u001b[39mtell()\n\u001b[0;32m    775\u001b[0m \u001b[38;5;28;01mreturn\u001b[39;00m data\n",
      "\u001b[1;31mKeyboardInterrupt\u001b[0m: "
     ]
    }
   ],
   "source": [
    "import pandas as pd\n",
    "from sklearn.preprocessing import LabelEncoder\n",
    "\n",
    "# load and merge files\n",
    "transaction_data = pd.read_excel(\"kartdata1.xlsx\")\n",
    "demographical_data = pd.read_excel(\"demografik.xlsx\")\n",
    "\n",
    "df_fs = pd.merge(transaction_data, demographical_data, on=\"Customer_id\", how=\"left\")\n",
    "df_fs.to_excel(\"df_fs.xlsx\", index=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Adding Slope, MA and Churn Columns to the Data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "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>Customer_id</th>\n",
       "      <th>trxdate</th>\n",
       "      <th>max_taksit</th>\n",
       "      <th>qty</th>\n",
       "      <th>TotalAmountLC</th>\n",
       "      <th>total_amount_market</th>\n",
       "      <th>total_qty_count_market</th>\n",
       "      <th>total_amount_diger</th>\n",
       "      <th>total_qty_count_diger</th>\n",
       "      <th>total_amount_giyim</th>\n",
       "      <th>...</th>\n",
       "      <th>Age</th>\n",
       "      <th>EmploymentType</th>\n",
       "      <th>EmployerSector</th>\n",
       "      <th>JobTitle</th>\n",
       "      <th>City</th>\n",
       "      <th>County</th>\n",
       "      <th>Churn</th>\n",
       "      <th>Slope</th>\n",
       "      <th>Unnamed: 0</th>\n",
       "      <th>Moving_Average</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>97425780</td>\n",
       "      <td>2024-03</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>340.00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>340.00</td>\n",
       "      <td>1.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>30</td>\n",
       "      <td>Serbest Meslek Sahibi</td>\n",
       "      <td>Özel Kurum</td>\n",
       "      <td>Unvan Yok</td>\n",
       "      <td>Şanlıurfa</td>\n",
       "      <td>Siverek</td>\n",
       "      <td>0</td>\n",
       "      <td>-2.900325</td>\n",
       "      <td>579355.0</td>\n",
       "      <td>321.427143</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>97425780</td>\n",
       "      <td>2023-09</td>\n",
       "      <td>5</td>\n",
       "      <td>1</td>\n",
       "      <td>710.00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>710.00</td>\n",
       "      <td>1.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>30</td>\n",
       "      <td>Serbest Meslek Sahibi</td>\n",
       "      <td>Özel Kurum</td>\n",
       "      <td>Unvan Yok</td>\n",
       "      <td>Şanlıurfa</td>\n",
       "      <td>Siverek</td>\n",
       "      <td>0</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>579349.0</td>\n",
       "      <td>0.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>97425780</td>\n",
       "      <td>2023-12</td>\n",
       "      <td>5</td>\n",
       "      <td>1</td>\n",
       "      <td>710.00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>710.00</td>\n",
       "      <td>1.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>30</td>\n",
       "      <td>Serbest Meslek Sahibi</td>\n",
       "      <td>Özel Kurum</td>\n",
       "      <td>Unvan Yok</td>\n",
       "      <td>Şanlıurfa</td>\n",
       "      <td>Siverek</td>\n",
       "      <td>1</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>579352.0</td>\n",
       "      <td>355.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>97425780</td>\n",
       "      <td>2023-08</td>\n",
       "      <td>5</td>\n",
       "      <td>1</td>\n",
       "      <td>710.00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>710.00</td>\n",
       "      <td>1.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>30</td>\n",
       "      <td>Serbest Meslek Sahibi</td>\n",
       "      <td>Özel Kurum</td>\n",
       "      <td>Unvan Yok</td>\n",
       "      <td>Şanlıurfa</td>\n",
       "      <td>Siverek</td>\n",
       "      <td>1</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>579348.0</td>\n",
       "      <td>0.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>97425780</td>\n",
       "      <td>2024-01</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>119.99</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>119.99</td>\n",
       "      <td>1.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>30</td>\n",
       "      <td>Serbest Meslek Sahibi</td>\n",
       "      <td>Özel Kurum</td>\n",
       "      <td>Unvan Yok</td>\n",
       "      <td>Şanlıurfa</td>\n",
       "      <td>Siverek</td>\n",
       "      <td>0</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>579353.0</td>\n",
       "      <td>284.000000</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 42 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "   Customer_id  trxdate  max_taksit  qty  TotalAmountLC  total_amount_market  \\\n",
       "0     97425780  2024-03           0    1         340.00                  NaN   \n",
       "1     97425780  2023-09           5    1         710.00                  NaN   \n",
       "2     97425780  2023-12           5    1         710.00                  NaN   \n",
       "3     97425780  2023-08           5    1         710.00                  NaN   \n",
       "4     97425780  2024-01           0    1         119.99                  NaN   \n",
       "\n",
       "   total_qty_count_market  total_amount_diger  total_qty_count_diger  \\\n",
       "0                     NaN              340.00                    1.0   \n",
       "1                     NaN              710.00                    1.0   \n",
       "2                     NaN              710.00                    1.0   \n",
       "3                     NaN              710.00                    1.0   \n",
       "4                     NaN              119.99                    1.0   \n",
       "\n",
       "   total_amount_giyim  ...  Age         EmploymentType  EmployerSector  \\\n",
       "0                 NaN  ...   30  Serbest Meslek Sahibi      Özel Kurum   \n",
       "1                 NaN  ...   30  Serbest Meslek Sahibi      Özel Kurum   \n",
       "2                 NaN  ...   30  Serbest Meslek Sahibi      Özel Kurum   \n",
       "3                 NaN  ...   30  Serbest Meslek Sahibi      Özel Kurum   \n",
       "4                 NaN  ...   30  Serbest Meslek Sahibi      Özel Kurum   \n",
       "\n",
       "    JobTitle       City   County  Churn     Slope  Unnamed: 0  Moving_Average  \n",
       "0  Unvan Yok  Şanlıurfa  Siverek      0 -2.900325    579355.0      321.427143  \n",
       "1  Unvan Yok  Şanlıurfa  Siverek      0  0.000000    579349.0        0.000000  \n",
       "2  Unvan Yok  Şanlıurfa  Siverek      1  0.000000    579352.0      355.000000  \n",
       "3  Unvan Yok  Şanlıurfa  Siverek      1  0.000000    579348.0        0.000000  \n",
       "4  Unvan Yok  Şanlıurfa  Siverek      0  0.000000    579353.0      284.000000  \n",
       "\n",
       "[5 rows x 42 columns]"
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "import pandas as pd\n",
    "from sklearn.preprocessing import LabelEncoder\n",
    "df_fs = pd.read_excel(\"df_fs.xlsx\")\n",
    "slope_df = pd.read_csv(\"slope.csv\")\n",
    "MA_df = pd.read_csv(\"MA.csv\")\n",
    "df_fs[\"trxdate\"] = pd.to_datetime(df_fs[\"trxdate\"]).dt.strftime('%Y-%m')\n",
    "churn_df = pd.read_csv(\"churn_analysis.csv\")\n",
    "merged_df = pd.merge(df_fs, churn_df, how=\"left\", on=[\"Customer_id\", \"trxdate\"])\n",
    "merged_df = pd.merge(merged_df, slope_df, how=\"left\", on=[\"Customer_id\", \"trxdate\"])\n",
    "merged_df = pd.merge(merged_df, MA_df, how=\"left\", on=[\"Customer_id\", \"trxdate\"])\n",
    "merged_df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "merged_df.to_excel(\"merged_df.xlsx\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Saving prepared file as an Excel file"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "merged_df= pd.read_excel(\"merged_df.xlsx\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Checking Null Values"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Unnamed: 0.1                            0\n",
       "Customer_id                             0\n",
       "trxdate                                 0\n",
       "max_taksit                              0\n",
       "qty                                     0\n",
       "TotalAmountLC                           0\n",
       "total_amount_market                123443\n",
       "total_qty_count_market             123443\n",
       "total_amount_diger                 109923\n",
       "total_qty_count_diger              109923\n",
       "total_amount_giyim                 286029\n",
       "total_qty_count_giyim              286029\n",
       "total_amount_saglik_kozmetik       316018\n",
       "total_qty_count_saglik_kozmetik    316018\n",
       "total_amount_sigorta               407822\n",
       "total_qty_count_sigorta            407822\n",
       "total_amount_teknoloji             338836\n",
       "total_qty_count_teknoloji          338836\n",
       "total_amount_ulasim                252845\n",
       "total_qty_count_ulasim             252845\n",
       "total_amount_yemek                 271016\n",
       "total_qty_count_yemek              271016\n",
       "Accommodation                           0\n",
       "BranchName                              0\n",
       "Unnamed: 3                         465999\n",
       "Citizenship                             0\n",
       "Gender                                  0\n",
       "MaritalStatus                          38\n",
       "ProfessionCode                          0\n",
       "ValueSegment                            0\n",
       "RecordingTime                           0\n",
       "sesLevel                                2\n",
       "EducationLevelPI                        0\n",
       "Age                                     0\n",
       "EmploymentType                        805\n",
       "EmployerSector                       3765\n",
       "JobTitle                             2869\n",
       "City                                38129\n",
       "County                              38148\n",
       "Churn                                   0\n",
       "Slope                               42121\n",
       "Unnamed: 0                          42121\n",
       "Moving_Average                      42121\n",
       "dtype: int64"
      ]
     },
     "execution_count": 24,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "merged_df.isnull().sum()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Changing Null Values of Spending Categories with 0"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Unnamed: 0.1                            0\n",
      "Customer_id                             0\n",
      "trxdate                                 0\n",
      "max_taksit                              0\n",
      "qty                                     0\n",
      "TotalAmountLC                           0\n",
      "total_amount_market                     0\n",
      "total_qty_count_market                  0\n",
      "total_amount_diger                      0\n",
      "total_qty_count_diger                   0\n",
      "total_amount_giyim                      0\n",
      "total_qty_count_giyim                   0\n",
      "total_amount_saglik_kozmetik            0\n",
      "total_qty_count_saglik_kozmetik         0\n",
      "total_amount_sigorta                    0\n",
      "total_qty_count_sigorta                 0\n",
      "total_amount_teknoloji                  0\n",
      "total_qty_count_teknoloji               0\n",
      "total_amount_ulasim                     0\n",
      "total_qty_count_ulasim                  0\n",
      "total_amount_yemek                      0\n",
      "total_qty_count_yemek                   0\n",
      "Accommodation                           0\n",
      "BranchName                              0\n",
      "Unnamed: 3                         465999\n",
      "Citizenship                             0\n",
      "Gender                                  0\n",
      "MaritalStatus                          38\n",
      "ProfessionCode                          0\n",
      "ValueSegment                            0\n",
      "RecordingTime                           0\n",
      "sesLevel                                2\n",
      "EducationLevelPI                        0\n",
      "Age                                     0\n",
      "EmploymentType                        805\n",
      "EmployerSector                       3765\n",
      "JobTitle                             2869\n",
      "City                                38129\n",
      "County                              38148\n",
      "Churn                                   0\n",
      "Slope                               42121\n",
      "Unnamed: 0                          42121\n",
      "Moving_Average                      42121\n",
      "dtype: int64\n"
     ]
    }
   ],
   "source": [
    "MCCPI = [\n",
    "    \"total_amount_market\",\n",
    "    \"total_qty_count_market\",\n",
    "    \"total_amount_diger\",\n",
    "    \"total_qty_count_diger\",\n",
    "    \"total_amount_giyim\",\n",
    "    \"total_qty_count_giyim\",\n",
    "    \"total_amount_saglik_kozmetik\",\n",
    "    \"total_qty_count_saglik_kozmetik\",\n",
    "    \"total_amount_sigorta\",\n",
    "    \"total_qty_count_sigorta\",\n",
    "    \"total_amount_teknoloji\",\n",
    "    \"total_qty_count_teknoloji\",\n",
    "    \"total_amount_ulasim\",\n",
    "    \"total_qty_count_ulasim\",\n",
    "    \"total_amount_yemek\",\n",
    "    \"total_qty_count_yemek\"\n",
    "]\n",
    "\n",
    "merged_df[MCCPI] = merged_df[MCCPI].fillna(0)\n",
    "\n",
    "\n",
    "print(merged_df.isnull().sum())\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Deleting Unnamed Column"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "   Customer_id  trxdate  max_taksit  qty  TotalAmountLC  total_amount_market  \\\n",
      "0     97425780  2024-03           0    1         340.00                  0.0   \n",
      "1     97425780  2023-09           5    1         710.00                  0.0   \n",
      "2     97425780  2023-12           5    1         710.00                  0.0   \n",
      "3     97425780  2023-08           5    1         710.00                  0.0   \n",
      "4     97425780  2024-01           0    1         119.99                  0.0   \n",
      "\n",
      "   total_qty_count_market  total_amount_diger  total_qty_count_diger  \\\n",
      "0                     0.0              340.00                    1.0   \n",
      "1                     0.0              710.00                    1.0   \n",
      "2                     0.0              710.00                    1.0   \n",
      "3                     0.0              710.00                    1.0   \n",
      "4                     0.0              119.99                    1.0   \n",
      "\n",
      "   total_amount_giyim  ...  EducationLevelPI  Age         EmploymentType  \\\n",
      "0                 0.0  ...                 6   30  Serbest Meslek Sahibi   \n",
      "1                 0.0  ...                 6   30  Serbest Meslek Sahibi   \n",
      "2                 0.0  ...                 6   30  Serbest Meslek Sahibi   \n",
      "3                 0.0  ...                 6   30  Serbest Meslek Sahibi   \n",
      "4                 0.0  ...                 6   30  Serbest Meslek Sahibi   \n",
      "\n",
      "   EmployerSector   JobTitle       City   County  Churn     Slope  \\\n",
      "0      Özel Kurum  Unvan Yok  Şanlıurfa  Siverek      0 -2.900325   \n",
      "1      Özel Kurum  Unvan Yok  Şanlıurfa  Siverek      0  0.000000   \n",
      "2      Özel Kurum  Unvan Yok  Şanlıurfa  Siverek      1  0.000000   \n",
      "3      Özel Kurum  Unvan Yok  Şanlıurfa  Siverek      1  0.000000   \n",
      "4      Özel Kurum  Unvan Yok  Şanlıurfa  Siverek      0  0.000000   \n",
      "\n",
      "   Moving_Average  \n",
      "0      321.427143  \n",
      "1        0.000000  \n",
      "2      355.000000  \n",
      "3        0.000000  \n",
      "4      284.000000  \n",
      "\n",
      "[5 rows x 40 columns]\n"
     ]
    }
   ],
   "source": [
    "merged_df = merged_df.drop(columns=merged_df.columns[merged_df.columns.str.contains('Unnamed')])\n",
    "print(merged_df.head())"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Handling the Null Values"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Silinen satır sayısı: 38\n"
     ]
    }
   ],
   "source": [
    "import pandas as pd\n",
    "\n",
    "null_marital_row = merged_df[merged_df['MaritalStatus'].isnull()]\n",
    "\n",
    "boş_customer_ids = null_marital_row['Customer_id']\n",
    "\n",
    "merged_df = merged_df[~merged_df['Customer_id'].isin(boş_customer_ids)]\n",
    "\n",
    "silinen_satır_sayısı = len(null_marital_row)\n",
    "print(\"Number of Deleted Rows:\", silinen_satır_sayısı)\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Handling Null Values of EmployerSector"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Customer_id                            0\n",
      "trxdate                                0\n",
      "max_taksit                             0\n",
      "qty                                    0\n",
      "TotalAmountLC                          0\n",
      "total_amount_market                    0\n",
      "total_qty_count_market                 0\n",
      "total_amount_diger                     0\n",
      "total_qty_count_diger                  0\n",
      "total_amount_giyim                     0\n",
      "total_qty_count_giyim                  0\n",
      "total_amount_saglik_kozmetik           0\n",
      "total_qty_count_saglik_kozmetik        0\n",
      "total_amount_sigorta                   0\n",
      "total_qty_count_sigorta                0\n",
      "total_amount_teknoloji                 0\n",
      "total_qty_count_teknoloji              0\n",
      "total_amount_ulasim                    0\n",
      "total_qty_count_ulasim                 0\n",
      "total_amount_yemek                     0\n",
      "total_qty_count_yemek                  0\n",
      "Accommodation                          0\n",
      "BranchName                             0\n",
      "Citizenship                            0\n",
      "Gender                                 0\n",
      "MaritalStatus                          0\n",
      "ProfessionCode                         0\n",
      "ValueSegment                           0\n",
      "RecordingTime                          0\n",
      "sesLevel                               2\n",
      "EducationLevelPI                       0\n",
      "Age                                    0\n",
      "EmploymentType                       805\n",
      "EmployerSector                         0\n",
      "JobTitle                            2754\n",
      "City                               38077\n",
      "County                             38096\n",
      "Churn                                  0\n",
      "Slope                              42030\n",
      "Moving_Average                     42030\n",
      "dtype: int64\n"
     ]
    }
   ],
   "source": [
    "import pandas as pd\n",
    "\n",
    "#Replacing some EmployerSector values with \"Sektör Yok\"\n",
    "condition = merged_df['EmploymentType'].isin(['Çalışmıyor', 'Emekli Çalışan', 'Serbest Meslek Sahibi'])\n",
    "merged_df.loc[condition, 'EmployerSector'] = 'Sektör Yok'\n",
    " \n",
    "# Delete Null values whose Employment type is Ücretli\n",
    "isveren_df = merged_df[(merged_df['EmploymentType'] == 'Ücretli') & (merged_df['EmployerSector'].isnull())]\n",
    "for customer_id in isveren_df['Customer_id'].unique():\n",
    "    rows_to_drop = merged_df[merged_df['Customer_id'] == customer_id]\n",
    "    merged_df.drop(rows_to_drop.index, inplace=True)\n",
    "\n",
    "#For \"İşveren\" EmploymentType, set EmployerSector as \"Özel Kurum\" \n",
    "isveren_indexes = merged_df.index[merged_df['EmploymentType'] == 'İşveren']\n",
    "merged_df.loc[isveren_indexes, 'EmployerSector'] = 'Özel Kurum'\n",
    "\n",
    "#For 'Tacir/Esnaf' EmploymentType, set EmployerSector as \"Özel Kurum\" \n",
    "esnaf_indexes = merged_df.index[merged_df['EmploymentType'] == 'Tacir/Esnaf']\n",
    "merged_df.loc[esnaf_indexes, 'EmployerSector'] = 'Özel Kurum'\n",
    " \n",
    "# Filling the rest of null values as \"Sektör Yok\"\n",
    "merged_df['EmployerSector'].fillna('Sektör Yok', inplace=True)\n",
    " \n",
    "print(merged_df.isnull().sum())\n",
    " \n",
    "unique_customers = merged_df[merged_df['EmployerSector'].isnull()]['Customer_id'].unique()\n",
    "for customer_id in unique_customers:\n",
    "    Employment_type = merged_df.loc[merged_df['Customer_id'] == customer_id, 'EmploymentType'].iloc[0]\n",
    "    print(f\"Customer ID: {customer_id}, EmploymentType: {Employment_type}\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Filling Null Values of City with the City information of BranchName Column"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "   Customer_id  trxdate  max_taksit  qty  TotalAmountLC  total_amount_market  \\\n",
      "0     97425780  2024-03           0    1         340.00                 0.00   \n",
      "1     97425780  2023-09           5    1         710.00                 0.00   \n",
      "2     97425780  2023-12           5    1         710.00                 0.00   \n",
      "3     97425780  2023-08           5    1         710.00                 0.00   \n",
      "4     97425780  2024-01           0    1         119.99                 0.00   \n",
      "5     97425780  2024-02           0    1         468.00                 0.00   \n",
      "6     97425734  2023-09           4   30       28885.30              1034.64   \n",
      "7     97425734  2023-08           0   26       18669.50              6997.63   \n",
      "8     97425734  2023-07           3   35       16782.61              6682.06   \n",
      "9     97425734  2023-11           4   41       19916.13              4741.78   \n",
      "\n",
      "   total_qty_count_market  total_amount_diger  total_qty_count_diger  \\\n",
      "0                     0.0              340.00                    1.0   \n",
      "1                     0.0              710.00                    1.0   \n",
      "2                     0.0              710.00                    1.0   \n",
      "3                     0.0              710.00                    1.0   \n",
      "4                     0.0              119.99                    1.0   \n",
      "5                     0.0              468.00                    1.0   \n",
      "6                     2.0            13722.28                    8.0   \n",
      "7                     2.0             5657.00                    2.0   \n",
      "8                     5.0             2501.56                    4.0   \n",
      "9                     4.0             7779.91                    8.0   \n",
      "\n",
      "   total_amount_giyim  ...  EducationLevelPI  Age         EmploymentType  \\\n",
      "0                0.00  ...                 6   30  Serbest Meslek Sahibi   \n",
      "1                0.00  ...                 6   30  Serbest Meslek Sahibi   \n",
      "2                0.00  ...                 6   30  Serbest Meslek Sahibi   \n",
      "3                0.00  ...                 6   30  Serbest Meslek Sahibi   \n",
      "4                0.00  ...                 6   30  Serbest Meslek Sahibi   \n",
      "5                0.00  ...                 6   30  Serbest Meslek Sahibi   \n",
      "6             2600.00  ...                 8   62                 Emekli   \n",
      "7                0.00  ...                 8   62                 Emekli   \n",
      "8             4245.07  ...                 8   62                 Emekli   \n",
      "9              525.00  ...                 8   62                 Emekli   \n",
      "\n",
      "   EmployerSector            JobTitle       City   County  Churn     Slope  \\\n",
      "0      Sektör Yok           Unvan Yok  Şanlıurfa  Siverek      0 -2.900325   \n",
      "1      Sektör Yok           Unvan Yok  Şanlıurfa  Siverek      0  0.000000   \n",
      "2      Sektör Yok           Unvan Yok  Şanlıurfa  Siverek      1  0.000000   \n",
      "3      Sektör Yok           Unvan Yok  Şanlıurfa  Siverek      1  0.000000   \n",
      "4      Sektör Yok           Unvan Yok  Şanlıurfa  Siverek      0  0.000000   \n",
      "5      Sektör Yok           Unvan Yok  Şanlıurfa  Siverek      0  0.831000   \n",
      "6      Özel Kurum  Üst Düzey Yönetici   İstanbul    Fatih      0 -0.112431   \n",
      "7      Özel Kurum  Üst Düzey Yönetici   İstanbul    Fatih      0  0.044392   \n",
      "8      Özel Kurum  Üst Düzey Yönetici   İstanbul    Fatih      0       NaN   \n",
      "9      Özel Kurum  Üst Düzey Yönetici   İstanbul    Fatih      0  0.527189   \n",
      "\n",
      "   Moving_Average  \n",
      "0      321.427143  \n",
      "1        0.000000  \n",
      "2      355.000000  \n",
      "3        0.000000  \n",
      "4      284.000000  \n",
      "5      355.000000  \n",
      "6    16782.610000  \n",
      "7        0.000000  \n",
      "8             NaN  \n",
      "9    21445.803333  \n",
      "\n",
      "[10 rows x 40 columns]\n"
     ]
    }
   ],
   "source": [
    "import pandas as pd\n",
    "\n",
    "cities_of_branch = pd.read_excel(\"CitiesofBranch.xlsx\")\n",
    "\n",
    "null_city_rows = merged_df[merged_df['City'].isnull()]\n",
    "\n",
    "for index, row in null_city_rows.iterrows():\n",
    "    branch_name = row['BranchName']\n",
    "    \n",
    "    city_name = cities_of_branch.loc[cities_of_branch['branchname'] == branch_name, 'CityName'].values\n",
    "    \n",
    "    if len(city_name) > 0:\n",
    "        merged_df.at[index, 'City'] = city_name[0]\n",
    "\n",
    "print(merged_df.head(10))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Deleting Unnecessary Columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "metadata": {},
   "outputs": [],
   "source": [
    "columns_to_drop = [ \"BranchName\", \"County\"] \n",
    "merged_df.drop(columns=columns_to_drop, inplace=True)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Filling Null JobTitle Values"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Find what is the ProfessionCodes of the Job Titles that have Null Values"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Values that JobTitle can take:  ['Unvan Yok' 'Üst Düzey Yönetici' 'Pratisyen' 'Memur'\n",
      " 'Orta Düzey Yönetici' 'Müdür' 'Yetkili' 'Uzman Doktor' 'Müdür Yrd.'\n",
      " 'Uzman' 'Çalışan' 'İşyeri Sahibi' 'Asteğmen' 'İmam' 'Okutman' 'Astsubay'\n",
      " 'Araştırma Görevlisi' 'Başhekim' 'Uzman Asker' 'Profesör' 'Doktor' 'Vaiz'\n",
      " 'Müezzin' nan 'Doçent' 'Teğmen' 'Binbaşı' 'Üsteğmen' 'Subay' 'General'\n",
      " 'Yüzbaşı' 'Albay' 'Yönetici']\n",
      "Profession Codes that has null Job Titles:  [ 17  25  39  24  38  60  14  83  62  87  13   4  40 101  32  34  50  36\n",
      "   7  33  30  35  44  15  43   0  10   8  42  27  98   2  28  58   9  79\n",
      "  29  57  48  81  73  64   6  11  55  91  67   5]\n"
     ]
    }
   ],
   "source": [
    "\n",
    "pd.set_option('display.max_rows', None)\n",
    "unique_jobtitles = merged_df['JobTitle'].unique()\n",
    "print('Values that JobTitle can take: ', unique_jobtitles)\n",
    "\n",
    "null_jobtitle_rows = merged_df[merged_df['JobTitle'].isnull()]\n",
    "ProfessionCode_JobTitle_null = null_jobtitle_rows['ProfessionCode'].unique()\n",
    "print('Profession Codes that has null Job Titles: ', ProfessionCode_JobTitle_null )"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "As the ProfessionCodes has no Titles, replace Jobtitles with \"Unvan Yok\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "metadata": {},
   "outputs": [],
   "source": [
    "merged_df['JobTitle'].fillna('Unvan Yok', inplace=True)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Deleting the Customer Data Which has Null SesLevels"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "null_sesLevel_customer_ids = merged_df[merged_df['sesLevel'].isnull()]['Customer_id'].unique()\n",
    "\n",
    "merged_df = merged_df[~merged_df['Customer_id'].isin(null_sesLevel_customer_ids)]\n",
    "\n",
    "print(\"Churn değeri Null olan Customer_id'ler:\", len(null_sesLevel_customer_ids))\n",
    "print(\"\\nTemizlenmiş DataFrame:\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Filling Null Values of MA and Slope with 0"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "merged_df['Moving_Average'].fillna(0, inplace=True)\n",
    "merged_df['Slope'].fillna(0, inplace=True)\n",
    "merged_df.isnull().sum()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Handling Null Values of EmploymentType"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Understand Which Profession Codes has Null EmploymentTypes"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "['Serbest Meslek Sahibi' 'Emekli' 'Emekli Çalışan' 'Öğrenci' 'Ücretli'\n",
      " 'Çalışmıyor' 'İşveren' 'Tacir/Esnaf' nan]\n",
      "[17 83 38 32 39 25 24 30 60 55 50 34]\n"
     ]
    }
   ],
   "source": [
    "import pandas as pd\n",
    " \n",
    "unique_Employment_types = merged_df[\"EmploymentType\"].unique()\n",
    " \n",
    "null_employment_df = merged_df[merged_df['EmploymentType'].isnull()][\"ProfessionCode\"]\n",
    "unique_professionCode = null_employment_df.unique()\n",
    "\n",
    "print(unique_Employment_types)\n",
    "print(unique_professionCode)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Assigning Appropriate EmploymentTypes for Different ProfessionCodes"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "metadata": {},
   "outputs": [],
   "source": [
    "\n",
    "null_employment_df = merged_df[(merged_df['EmploymentType'].isnull()) & \n",
    "                               (merged_df['ProfessionCode'].isin([17, 83, 32, 30, 39, 38, 25, 24, 60, 55, 34, 50]))]\n",
    " \n",
    "for index, row in null_employment_df.iterrows():\n",
    "    profession_code = row['ProfessionCode']\n",
    "    if profession_code in [17, 38]:\n",
    "        merged_df.at[index, 'EmploymentType'] = 'Çalışmıyor'\n",
    "    elif profession_code in [25, 83]:\n",
    "        merged_df.at[index, 'EmploymentType'] = 'Tacir/Esnaf'\n",
    "    else:\n",
    "        merged_df.at[index, 'EmploymentType'] = 'Ücretli'"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Handling Data Types"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Customer_id                                 int64\n",
       "trxdate                                    object\n",
       "max_taksit                                  int64\n",
       "qty                                         int64\n",
       "TotalAmountLC                             float64\n",
       "total_amount_market                       float64\n",
       "total_qty_count_market                    float64\n",
       "total_amount_diger                        float64\n",
       "total_qty_count_diger                     float64\n",
       "total_amount_giyim                        float64\n",
       "total_qty_count_giyim                     float64\n",
       "total_amount_saglik_kozmetik              float64\n",
       "total_qty_count_saglik_kozmetik           float64\n",
       "total_amount_sigorta                      float64\n",
       "total_qty_count_sigorta                   float64\n",
       "total_amount_teknoloji                    float64\n",
       "total_qty_count_teknoloji                 float64\n",
       "total_amount_ulasim                       float64\n",
       "total_qty_count_ulasim                    float64\n",
       "total_amount_yemek                        float64\n",
       "total_qty_count_yemek                     float64\n",
       "Accommodation                               int64\n",
       "Citizenship                                 int64\n",
       "Gender                                      int64\n",
       "MaritalStatus                             float64\n",
       "ProfessionCode                              int64\n",
       "ValueSegment                               object\n",
       "RecordingTime                      datetime64[ns]\n",
       "sesLevel                                  float64\n",
       "EducationLevelPI                            int64\n",
       "Age                                         int64\n",
       "EmploymentType                             object\n",
       "EmployerSector                             object\n",
       "JobTitle                                   object\n",
       "City                                       object\n",
       "Churn                                       int64\n",
       "Slope                                     float64\n",
       "Moving_Average                            float64\n",
       "dtype: object"
      ]
     },
     "execution_count": 37,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "merged_df.dtypes"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Changing Recording Time to Tenure"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "\n",
    "today_date = pd.to_datetime('today').strftime('%Y-%m-%d')\n",
    "\n",
    "merged_df['RecordingTime'] = pd.to_datetime(merged_df['RecordingTime']).dt.strftime('%Y-%m-%d')\n",
    "\n",
    "merged_df['Tenure'] = (pd.to_datetime(today_date) - pd.to_datetime(merged_df['RecordingTime'])).dt.days / 365\n",
    "merged_df.drop(columns=['RecordingTime'], inplace=True)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Encoding the ValueSegment"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 39,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "   Customer_id  trxdate  max_taksit  qty  TotalAmountLC  total_amount_market  \\\n",
      "0     97425780  2024-03           0    1         340.00                  0.0   \n",
      "1     97425780  2023-09           5    1         710.00                  0.0   \n",
      "2     97425780  2023-12           5    1         710.00                  0.0   \n",
      "3     97425780  2023-08           5    1         710.00                  0.0   \n",
      "4     97425780  2024-01           0    1         119.99                  0.0   \n",
      "\n",
      "   total_qty_count_market  total_amount_diger  total_qty_count_diger  \\\n",
      "0                     0.0              340.00                    1.0   \n",
      "1                     0.0              710.00                    1.0   \n",
      "2                     0.0              710.00                    1.0   \n",
      "3                     0.0              710.00                    1.0   \n",
      "4                     0.0              119.99                    1.0   \n",
      "\n",
      "   total_amount_giyim  ...       City  Churn     Slope  Moving_Average  \\\n",
      "0                 0.0  ...  Şanlıurfa      0 -2.900325      321.427143   \n",
      "1                 0.0  ...  Şanlıurfa      0  0.000000        0.000000   \n",
      "2                 0.0  ...  Şanlıurfa      1  0.000000      355.000000   \n",
      "3                 0.0  ...  Şanlıurfa      1  0.000000        0.000000   \n",
      "4                 0.0  ...  Şanlıurfa      0  0.000000      284.000000   \n",
      "\n",
      "     Tenure  ValueSegment_A1  ValueSegment_A2  ValueSegment_B  ValueSegment_C  \\\n",
      "0  2.635616                0                0               0               1   \n",
      "1  2.635616                0                0               0               1   \n",
      "2  2.635616                0                0               0               1   \n",
      "3  2.635616                0                0               0               1   \n",
      "4  2.635616                0                0               0               1   \n",
      "\n",
      "   ValueSegment_D  \n",
      "0               0  \n",
      "1               0  \n",
      "2               0  \n",
      "3               0  \n",
      "4               0  \n",
      "\n",
      "[5 rows x 42 columns]\n"
     ]
    }
   ],
   "source": [
    "ValueSegment_encoded = pd.get_dummies(merged_df['ValueSegment'], prefix='ValueSegment')\n",
    "\n",
    "ValueSegment_encoded = ValueSegment_encoded.astype(int)\n",
    "\n",
    "merged_df = pd.concat([merged_df, ValueSegment_encoded], axis=1)\n",
    "\n",
    "merged_df.drop('ValueSegment', axis=1, inplace=True)\n",
    "\n",
    "print(merged_df.head())"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Encoding the JobTitle"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 40,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "   Customer_id  trxdate  max_taksit  qty  TotalAmountLC  total_amount_market  \\\n",
      "0     97425780  2024-03           0    1         340.00                  0.0   \n",
      "1     97425780  2023-09           5    1         710.00                  0.0   \n",
      "2     97425780  2023-12           5    1         710.00                  0.0   \n",
      "3     97425780  2023-08           5    1         710.00                  0.0   \n",
      "4     97425780  2024-01           0    1         119.99                  0.0   \n",
      "\n",
      "   total_qty_count_market  total_amount_diger  total_qty_count_diger  \\\n",
      "0                     0.0              340.00                    1.0   \n",
      "1                     0.0              710.00                    1.0   \n",
      "2                     0.0              710.00                    1.0   \n",
      "3                     0.0              710.00                    1.0   \n",
      "4                     0.0              119.99                    1.0   \n",
      "\n",
      "   total_amount_giyim  ...  JobTitle_Uzman Doktor  JobTitle_Vaiz  \\\n",
      "0                 0.0  ...                      0              0   \n",
      "1                 0.0  ...                      0              0   \n",
      "2                 0.0  ...                      0              0   \n",
      "3                 0.0  ...                      0              0   \n",
      "4                 0.0  ...                      0              0   \n",
      "\n",
      "   JobTitle_Yetkili  JobTitle_Yönetici  JobTitle_Yüzbaşı  JobTitle_Çalışan  \\\n",
      "0                 0                  0                 0                 0   \n",
      "1                 0                  0                 0                 0   \n",
      "2                 0                  0                 0                 0   \n",
      "3                 0                  0                 0                 0   \n",
      "4                 0                  0                 0                 0   \n",
      "\n",
      "   JobTitle_Üst Düzey Yönetici  JobTitle_Üsteğmen  JobTitle_İmam  \\\n",
      "0                            0                  0              0   \n",
      "1                            0                  0              0   \n",
      "2                            0                  0              0   \n",
      "3                            0                  0              0   \n",
      "4                            0                  0              0   \n",
      "\n",
      "   JobTitle_İşyeri Sahibi  \n",
      "0                       0  \n",
      "1                       0  \n",
      "2                       0  \n",
      "3                       0  \n",
      "4                       0  \n",
      "\n",
      "[5 rows x 73 columns]\n"
     ]
    }
   ],
   "source": [
    "JobTitle_encoded = pd.get_dummies(merged_df['JobTitle'], prefix='JobTitle')\n",
    "\n",
    "JobTitle_encoded = JobTitle_encoded.astype(int)\n",
    "\n",
    "merged_df = pd.concat([merged_df, JobTitle_encoded], axis=1)\n",
    "\n",
    "merged_df.drop('JobTitle', axis=1, inplace=True)\n",
    "\n",
    "print(merged_df.head())"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Encoding the EmploymentType"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 41,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "   Customer_id  trxdate  max_taksit  qty  TotalAmountLC  total_amount_market  \\\n",
      "0     97425780  2024-03           0    1         340.00                  0.0   \n",
      "1     97425780  2023-09           5    1         710.00                  0.0   \n",
      "2     97425780  2023-12           5    1         710.00                  0.0   \n",
      "3     97425780  2023-08           5    1         710.00                  0.0   \n",
      "4     97425780  2024-01           0    1         119.99                  0.0   \n",
      "\n",
      "   total_qty_count_market  total_amount_diger  total_qty_count_diger  \\\n",
      "0                     0.0              340.00                    1.0   \n",
      "1                     0.0              710.00                    1.0   \n",
      "2                     0.0              710.00                    1.0   \n",
      "3                     0.0              710.00                    1.0   \n",
      "4                     0.0              119.99                    1.0   \n",
      "\n",
      "   total_amount_giyim  ...  JobTitle_İmam  JobTitle_İşyeri Sahibi  \\\n",
      "0                 0.0  ...              0                       0   \n",
      "1                 0.0  ...              0                       0   \n",
      "2                 0.0  ...              0                       0   \n",
      "3                 0.0  ...              0                       0   \n",
      "4                 0.0  ...              0                       0   \n",
      "\n",
      "   EmploymentType_Emekli  EmploymentType_Emekli Çalışan  \\\n",
      "0                      0                              0   \n",
      "1                      0                              0   \n",
      "2                      0                              0   \n",
      "3                      0                              0   \n",
      "4                      0                              0   \n",
      "\n",
      "   EmploymentType_Serbest Meslek Sahibi  EmploymentType_Tacir/Esnaf  \\\n",
      "0                                     1                           0   \n",
      "1                                     1                           0   \n",
      "2                                     1                           0   \n",
      "3                                     1                           0   \n",
      "4                                     1                           0   \n",
      "\n",
      "   EmploymentType_Çalışmıyor  EmploymentType_Öğrenci  EmploymentType_Ücretli  \\\n",
      "0                          0                       0                       0   \n",
      "1                          0                       0                       0   \n",
      "2                          0                       0                       0   \n",
      "3                          0                       0                       0   \n",
      "4                          0                       0                       0   \n",
      "\n",
      "   EmploymentType_İşveren  \n",
      "0                       0  \n",
      "1                       0  \n",
      "2                       0  \n",
      "3                       0  \n",
      "4                       0  \n",
      "\n",
      "[5 rows x 80 columns]\n"
     ]
    }
   ],
   "source": [
    "EmploymentType_encoded = pd.get_dummies(merged_df['EmploymentType'], prefix='EmploymentType')\n",
    "\n",
    "EmploymentType_encoded = EmploymentType_encoded.astype(int)\n",
    "\n",
    "merged_df = pd.concat([merged_df, EmploymentType_encoded], axis=1)\n",
    "\n",
    "merged_df.drop('EmploymentType', axis=1, inplace=True)\n",
    "\n",
    "print(merged_df.head())"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Encoding the EmployerSector"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 42,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "   Customer_id  trxdate  max_taksit  qty  TotalAmountLC  total_amount_market  \\\n",
      "0     97425780  2024-03           0    1          340.0                  0.0   \n",
      "1     97425780  2023-09           5    1          710.0                  0.0   \n",
      "\n",
      "   total_qty_count_market  total_amount_diger  total_qty_count_diger  \\\n",
      "0                     0.0               340.0                    1.0   \n",
      "1                     0.0               710.0                    1.0   \n",
      "\n",
      "   total_amount_giyim  ...  EmploymentType_Emekli Çalışan  \\\n",
      "0                 0.0  ...                              0   \n",
      "1                 0.0  ...                              0   \n",
      "\n",
      "   EmploymentType_Serbest Meslek Sahibi  EmploymentType_Tacir/Esnaf  \\\n",
      "0                                     1                           0   \n",
      "1                                     1                           0   \n",
      "\n",
      "   EmploymentType_Çalışmıyor  EmploymentType_Öğrenci  EmploymentType_Ücretli  \\\n",
      "0                          0                       0                       0   \n",
      "1                          0                       0                       0   \n",
      "\n",
      "   EmploymentType_İşveren  EmployerSector_Kamu Kurumu  \\\n",
      "0                       0                           0   \n",
      "1                       0                           0   \n",
      "\n",
      "   EmployerSector_Sektör Yok  EmployerSector_Özel Kurum  \n",
      "0                          1                          0  \n",
      "1                          1                          0  \n",
      "\n",
      "[2 rows x 82 columns]\n",
      "Customer_id                               int64\n",
      "trxdate                                  object\n",
      "max_taksit                                int64\n",
      "qty                                       int64\n",
      "TotalAmountLC                           float64\n",
      "total_amount_market                     float64\n",
      "total_qty_count_market                  float64\n",
      "total_amount_diger                      float64\n",
      "total_qty_count_diger                   float64\n",
      "total_amount_giyim                      float64\n",
      "total_qty_count_giyim                   float64\n",
      "total_amount_saglik_kozmetik            float64\n",
      "total_qty_count_saglik_kozmetik         float64\n",
      "total_amount_sigorta                    float64\n",
      "total_qty_count_sigorta                 float64\n",
      "total_amount_teknoloji                  float64\n",
      "total_qty_count_teknoloji               float64\n",
      "total_amount_ulasim                     float64\n",
      "total_qty_count_ulasim                  float64\n",
      "total_amount_yemek                      float64\n",
      "total_qty_count_yemek                   float64\n",
      "Accommodation                             int64\n",
      "Citizenship                               int64\n",
      "Gender                                    int64\n",
      "MaritalStatus                           float64\n",
      "ProfessionCode                            int64\n",
      "sesLevel                                float64\n",
      "EducationLevelPI                          int64\n",
      "Age                                       int64\n",
      "City                                     object\n",
      "Churn                                     int64\n",
      "Slope                                   float64\n",
      "Moving_Average                          float64\n",
      "Tenure                                  float64\n",
      "ValueSegment_A1                           int32\n",
      "ValueSegment_A2                           int32\n",
      "ValueSegment_B                            int32\n",
      "ValueSegment_C                            int32\n",
      "ValueSegment_D                            int32\n",
      "JobTitle_Albay                            int32\n",
      "JobTitle_Araştırma Görevlisi              int32\n",
      "JobTitle_Asteğmen                         int32\n",
      "JobTitle_Astsubay                         int32\n",
      "JobTitle_Başhekim                         int32\n",
      "JobTitle_Binbaşı                          int32\n",
      "JobTitle_Doktor                           int32\n",
      "JobTitle_Doçent                           int32\n",
      "JobTitle_General                          int32\n",
      "JobTitle_Memur                            int32\n",
      "JobTitle_Müdür                            int32\n",
      "JobTitle_Müdür Yrd.                       int32\n",
      "JobTitle_Müezzin                          int32\n",
      "JobTitle_Okutman                          int32\n",
      "JobTitle_Orta Düzey Yönetici              int32\n",
      "JobTitle_Pratisyen                        int32\n",
      "JobTitle_Profesör                         int32\n",
      "JobTitle_Subay                            int32\n",
      "JobTitle_Teğmen                           int32\n",
      "JobTitle_Unvan Yok                        int32\n",
      "JobTitle_Uzman                            int32\n",
      "JobTitle_Uzman Asker                      int32\n",
      "JobTitle_Uzman Doktor                     int32\n",
      "JobTitle_Vaiz                             int32\n",
      "JobTitle_Yetkili                          int32\n",
      "JobTitle_Yönetici                         int32\n",
      "JobTitle_Yüzbaşı                          int32\n",
      "JobTitle_Çalışan                          int32\n",
      "JobTitle_Üst Düzey Yönetici               int32\n",
      "JobTitle_Üsteğmen                         int32\n",
      "JobTitle_İmam                             int32\n",
      "JobTitle_İşyeri Sahibi                    int32\n",
      "EmploymentType_Emekli                     int32\n",
      "EmploymentType_Emekli Çalışan             int32\n",
      "EmploymentType_Serbest Meslek Sahibi      int32\n",
      "EmploymentType_Tacir/Esnaf                int32\n",
      "EmploymentType_Çalışmıyor                 int32\n",
      "EmploymentType_Öğrenci                    int32\n",
      "EmploymentType_Ücretli                    int32\n",
      "EmploymentType_İşveren                    int32\n",
      "EmployerSector_Kamu Kurumu                int32\n",
      "EmployerSector_Sektör Yok                 int32\n",
      "EmployerSector_Özel Kurum                 int32\n",
      "dtype: object\n"
     ]
    }
   ],
   "source": [
    "EmployerSector_encoded = pd.get_dummies(merged_df['EmployerSector'], prefix='EmployerSector')\n",
    "\n",
    "EmployerSector_encoded = EmployerSector_encoded.astype(int)\n",
    "\n",
    "merged_df = pd.concat([merged_df, EmployerSector_encoded], axis=1)\n",
    "\n",
    "merged_df.drop('EmployerSector', axis=1, inplace=True)\n",
    "\n",
    "print(merged_df.head(2))\n",
    "print(merged_df.dtypes)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Changing City with Geographical Region"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 43,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "           City GeographicalRegion\n",
      "465987   Manisa                Ege\n",
      "465988   Manisa                Ege\n",
      "465989   Manisa                Ege\n",
      "465990   Manisa                Ege\n",
      "465991   Manisa                Ege\n",
      "465992   Manisa                Ege\n",
      "465993   Manisa                Ege\n",
      "465994   Manisa                Ege\n",
      "465995   Manisa                Ege\n",
      "465996  Malatya       Doğu Anadolu\n",
      "465997  Malatya       Doğu Anadolu\n",
      "465998  Malatya       Doğu Anadolu\n",
      "Customer_id                               int64\n",
      "trxdate                                  object\n",
      "max_taksit                                int64\n",
      "qty                                       int64\n",
      "TotalAmountLC                           float64\n",
      "total_amount_market                     float64\n",
      "total_qty_count_market                  float64\n",
      "total_amount_diger                      float64\n",
      "total_qty_count_diger                   float64\n",
      "total_amount_giyim                      float64\n",
      "total_qty_count_giyim                   float64\n",
      "total_amount_saglik_kozmetik            float64\n",
      "total_qty_count_saglik_kozmetik         float64\n",
      "total_amount_sigorta                    float64\n",
      "total_qty_count_sigorta                 float64\n",
      "total_amount_teknoloji                  float64\n",
      "total_qty_count_teknoloji               float64\n",
      "total_amount_ulasim                     float64\n",
      "total_qty_count_ulasim                  float64\n",
      "total_amount_yemek                      float64\n",
      "total_qty_count_yemek                   float64\n",
      "Accommodation                             int64\n",
      "Citizenship                               int64\n",
      "Gender                                    int64\n",
      "MaritalStatus                           float64\n",
      "ProfessionCode                            int64\n",
      "sesLevel                                float64\n",
      "EducationLevelPI                          int64\n",
      "Age                                       int64\n",
      "City                                     object\n",
      "Churn                                     int64\n",
      "Slope                                   float64\n",
      "Moving_Average                          float64\n",
      "Tenure                                  float64\n",
      "ValueSegment_A1                           int32\n",
      "ValueSegment_A2                           int32\n",
      "ValueSegment_B                            int32\n",
      "ValueSegment_C                            int32\n",
      "ValueSegment_D                            int32\n",
      "JobTitle_Albay                            int32\n",
      "JobTitle_Araştırma Görevlisi              int32\n",
      "JobTitle_Asteğmen                         int32\n",
      "JobTitle_Astsubay                         int32\n",
      "JobTitle_Başhekim                         int32\n",
      "JobTitle_Binbaşı                          int32\n",
      "JobTitle_Doktor                           int32\n",
      "JobTitle_Doçent                           int32\n",
      "JobTitle_General                          int32\n",
      "JobTitle_Memur                            int32\n",
      "JobTitle_Müdür                            int32\n",
      "JobTitle_Müdür Yrd.                       int32\n",
      "JobTitle_Müezzin                          int32\n",
      "JobTitle_Okutman                          int32\n",
      "JobTitle_Orta Düzey Yönetici              int32\n",
      "JobTitle_Pratisyen                        int32\n",
      "JobTitle_Profesör                         int32\n",
      "JobTitle_Subay                            int32\n",
      "JobTitle_Teğmen                           int32\n",
      "JobTitle_Unvan Yok                        int32\n",
      "JobTitle_Uzman                            int32\n",
      "JobTitle_Uzman Asker                      int32\n",
      "JobTitle_Uzman Doktor                     int32\n",
      "JobTitle_Vaiz                             int32\n",
      "JobTitle_Yetkili                          int32\n",
      "JobTitle_Yönetici                         int32\n",
      "JobTitle_Yüzbaşı                          int32\n",
      "JobTitle_Çalışan                          int32\n",
      "JobTitle_Üst Düzey Yönetici               int32\n",
      "JobTitle_Üsteğmen                         int32\n",
      "JobTitle_İmam                             int32\n",
      "JobTitle_İşyeri Sahibi                    int32\n",
      "EmploymentType_Emekli                     int32\n",
      "EmploymentType_Emekli Çalışan             int32\n",
      "EmploymentType_Serbest Meslek Sahibi      int32\n",
      "EmploymentType_Tacir/Esnaf                int32\n",
      "EmploymentType_Çalışmıyor                 int32\n",
      "EmploymentType_Öğrenci                    int32\n",
      "EmploymentType_Ücretli                    int32\n",
      "EmploymentType_İşveren                    int32\n",
      "EmployerSector_Kamu Kurumu                int32\n",
      "EmployerSector_Sektör Yok                 int32\n",
      "EmployerSector_Özel Kurum                 int32\n",
      "GeographicalRegion                       object\n",
      "dtype: object\n"
     ]
    }
   ],
   "source": [
    "geographical_regions = {\n",
    "    'Marmara': ['Edirne', 'Kırklareli', 'Tekirdağ', 'İstanbul', 'Kocaeli', 'Yalova', 'Sakarya', 'Bilecik', 'Bursa', 'Balıkesir', 'Çanakkale'],\n",
    "    'İç Anadolu': ['Aksaray', 'Ankara', 'Çankırı', 'Eskişehir', 'Karaman', 'Kırıkkale', 'Kırşehir', 'Konya', 'Nevşehir', 'Niğde', 'Sivas', 'Yozgat', 'Kayseri'],\n",
    "    'Ege': ['İzmir', 'Manisa', 'Aydın', 'Denizli', 'Kütahya', 'Afyonkarahisar', 'Uşak', 'Muğla'],\n",
    "    'Akdeniz': ['Adana', 'Osmaniye', 'Antalya', 'Burdur', 'Hatay', 'Isparta', 'Mersin', 'Kahramanmaraş'],\n",
    "    'Karadeniz': ['Rize', 'Trabzon', 'Artvin', 'Sinop', 'Tokat', 'Çorum', 'Amasya', 'Samsun', 'Zonguldak', 'Bolu', 'Düzce', 'Karabük', 'Bartın', 'Kastamonu', 'Bayburt', 'Giresun', 'Gümüşhane', 'Ordu'],\n",
    "    'Doğu Anadolu': ['Ağrı', 'Ardahan', 'Bingöl', 'Bitlis', 'Elazığ', 'Erzincan', 'Erzurum', 'Hakkari', 'Iğdır', 'Kars', 'Malatya', 'Muş', 'Tunceli', 'Van', 'Şırnak'],\n",
    "    'Güneydoğu Anadolu': ['Adıyaman', 'Batman', 'Diyarbakır', 'Gaziantep', 'Kilis', 'Mardin', 'Siirt', 'Şanlıurfa']\n",
    "}\n",
    "\n",
    "def get_region(city):\n",
    "    for region, cities in geographical_regions.items():\n",
    "        if city in cities:\n",
    "            return region\n",
    "    return None\n",
    "\n",
    "merged_df['GeographicalRegion'] = merged_df['City'].apply(get_region)\n",
    "\n",
    "print(merged_df[['City', 'GeographicalRegion']].tail(12))\n",
    "print(merged_df.dtypes)\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Encoding the Geographical  Region "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 44,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "        Customer_id  trxdate  max_taksit  qty  TotalAmountLC  \\\n",
      "465997     16082019  2023-07           0    2         119.99   \n",
      "465998     16082019  2024-03           0    5        1465.26   \n",
      "\n",
      "        total_amount_market  total_qty_count_market  total_amount_diger  \\\n",
      "465997                  0.0                     0.0              119.99   \n",
      "465998                472.1                     1.0              993.16   \n",
      "\n",
      "        total_qty_count_diger  total_amount_giyim  ...  \\\n",
      "465997                    2.0                 0.0  ...   \n",
      "465998                    2.0                 0.0  ...   \n",
      "\n",
      "        EmployerSector_Kamu Kurumu  EmployerSector_Sektör Yok  \\\n",
      "465997                           0                          0   \n",
      "465998                           0                          0   \n",
      "\n",
      "        EmployerSector_Özel Kurum  GeographicalRegion_Akdeniz  \\\n",
      "465997                          1                           0   \n",
      "465998                          1                           0   \n",
      "\n",
      "        GeographicalRegion_Doğu Anadolu  GeographicalRegion_Ege  \\\n",
      "465997                                1                       0   \n",
      "465998                                1                       0   \n",
      "\n",
      "        GeographicalRegion_Güneydoğu Anadolu  GeographicalRegion_Karadeniz  \\\n",
      "465997                                     0                             0   \n",
      "465998                                     0                             0   \n",
      "\n",
      "        GeographicalRegion_Marmara  GeographicalRegion_İç Anadolu  \n",
      "465997                           0                              0  \n",
      "465998                           0                              0  \n",
      "\n",
      "[2 rows x 88 columns]\n",
      "Customer_id                               int64\n",
      "trxdate                                  object\n",
      "max_taksit                                int64\n",
      "qty                                       int64\n",
      "TotalAmountLC                           float64\n",
      "total_amount_market                     float64\n",
      "total_qty_count_market                  float64\n",
      "total_amount_diger                      float64\n",
      "total_qty_count_diger                   float64\n",
      "total_amount_giyim                      float64\n",
      "total_qty_count_giyim                   float64\n",
      "total_amount_saglik_kozmetik            float64\n",
      "total_qty_count_saglik_kozmetik         float64\n",
      "total_amount_sigorta                    float64\n",
      "total_qty_count_sigorta                 float64\n",
      "total_amount_teknoloji                  float64\n",
      "total_qty_count_teknoloji               float64\n",
      "total_amount_ulasim                     float64\n",
      "total_qty_count_ulasim                  float64\n",
      "total_amount_yemek                      float64\n",
      "total_qty_count_yemek                   float64\n",
      "Accommodation                             int64\n",
      "Citizenship                               int64\n",
      "Gender                                    int64\n",
      "MaritalStatus                           float64\n",
      "ProfessionCode                            int64\n",
      "sesLevel                                float64\n",
      "EducationLevelPI                          int64\n",
      "Age                                       int64\n",
      "Churn                                     int64\n",
      "Slope                                   float64\n",
      "Moving_Average                          float64\n",
      "Tenure                                  float64\n",
      "ValueSegment_A1                           int32\n",
      "ValueSegment_A2                           int32\n",
      "ValueSegment_B                            int32\n",
      "ValueSegment_C                            int32\n",
      "ValueSegment_D                            int32\n",
      "JobTitle_Albay                            int32\n",
      "JobTitle_Araştırma Görevlisi              int32\n",
      "JobTitle_Asteğmen                         int32\n",
      "JobTitle_Astsubay                         int32\n",
      "JobTitle_Başhekim                         int32\n",
      "JobTitle_Binbaşı                          int32\n",
      "JobTitle_Doktor                           int32\n",
      "JobTitle_Doçent                           int32\n",
      "JobTitle_General                          int32\n",
      "JobTitle_Memur                            int32\n",
      "JobTitle_Müdür                            int32\n",
      "JobTitle_Müdür Yrd.                       int32\n",
      "JobTitle_Müezzin                          int32\n",
      "JobTitle_Okutman                          int32\n",
      "JobTitle_Orta Düzey Yönetici              int32\n",
      "JobTitle_Pratisyen                        int32\n",
      "JobTitle_Profesör                         int32\n",
      "JobTitle_Subay                            int32\n",
      "JobTitle_Teğmen                           int32\n",
      "JobTitle_Unvan Yok                        int32\n",
      "JobTitle_Uzman                            int32\n",
      "JobTitle_Uzman Asker                      int32\n",
      "JobTitle_Uzman Doktor                     int32\n",
      "JobTitle_Vaiz                             int32\n",
      "JobTitle_Yetkili                          int32\n",
      "JobTitle_Yönetici                         int32\n",
      "JobTitle_Yüzbaşı                          int32\n",
      "JobTitle_Çalışan                          int32\n",
      "JobTitle_Üst Düzey Yönetici               int32\n",
      "JobTitle_Üsteğmen                         int32\n",
      "JobTitle_İmam                             int32\n",
      "JobTitle_İşyeri Sahibi                    int32\n",
      "EmploymentType_Emekli                     int32\n",
      "EmploymentType_Emekli Çalışan             int32\n",
      "EmploymentType_Serbest Meslek Sahibi      int32\n",
      "EmploymentType_Tacir/Esnaf                int32\n",
      "EmploymentType_Çalışmıyor                 int32\n",
      "EmploymentType_Öğrenci                    int32\n",
      "EmploymentType_Ücretli                    int32\n",
      "EmploymentType_İşveren                    int32\n",
      "EmployerSector_Kamu Kurumu                int32\n",
      "EmployerSector_Sektör Yok                 int32\n",
      "EmployerSector_Özel Kurum                 int32\n",
      "GeographicalRegion_Akdeniz                int32\n",
      "GeographicalRegion_Doğu Anadolu           int32\n",
      "GeographicalRegion_Ege                    int32\n",
      "GeographicalRegion_Güneydoğu Anadolu      int32\n",
      "GeographicalRegion_Karadeniz              int32\n",
      "GeographicalRegion_Marmara                int32\n",
      "GeographicalRegion_İç Anadolu             int32\n",
      "dtype: object\n"
     ]
    }
   ],
   "source": [
    "GeographicalRegion_encoded = pd.get_dummies(merged_df['GeographicalRegion'], prefix='GeographicalRegion')\n",
    "\n",
    "GeographicalRegion_encoded = GeographicalRegion_encoded.astype(int)\n",
    "\n",
    "merged_df = pd.concat([merged_df, GeographicalRegion_encoded], axis=1)\n",
    "\n",
    "merged_df.drop('City', axis=1, inplace=True)\n",
    "merged_df.drop('GeographicalRegion', axis=1, inplace=True)\n",
    "\n",
    "print(merged_df.tail(2))\n",
    "print(merged_df.dtypes)\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Save the Cleaned Data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 50,
   "metadata": {},
   "outputs": [],
   "source": [
    "merged_df.to_csv(\"cleanedData.csv\")"
   ]
  }
 ],
 "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.11.3"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}