feature_selection.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import pickle\n",
    "import my_func\n",
    "import lightgbm as lgb\n",
    "import warnings\n",
    "warnings.filterwarnings('ignore')\n",
    "import numpy as np\n",
    "from sklearn.model_selection import KFold \n",
    "from sklearn.model_selection import train_test_split\n",
    "from sklearn.metrics import classification_report, roc_auc_score, accuracy_score, precision_score, recall_score, f1_score\n",
    "from datetime import datetime, timedelta\n",
    "from sklearn.metrics import roc_auc_score\n",
    "from sklearn.metrics import roc_curve\n",
    "import seaborn as sns\n",
    "import matplotlib.pyplot as plt\n",
    "import time\n",
    "from sklearn.metrics import confusion_matrix\n",
    "import shap\n",
    "from hyperopt import hp, fmin, tpe\n",
    "\n",
    "sns.set()\n",
    "\n",
    "fe_name_tr = 'churn_train_model_fe.pickle'\n",
    "b_num_name_tr ='churn_train_model_b_num.pickle'\n",
    "dpi_name_tr ='churn_train_model_dpi.pickle'\n",
    "\n",
    "fe_name_te = 'churn_test_model_fe.pickle'\n",
    "b_num_name_te ='churn_test_model_b_num.pickle'\n",
    "dpi_name_te ='churn_test_model_dpi.pickle'\n",
    "\n",
    "file_path = r'D:\\BDL-Final\\data-from-BDL' + '\\\\'\n",
    "\n",
    "# settings for dataframe\n",
    "pd.set_option('display.max_columns', None)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Load dataframes\n",
    "df_train_fe = pd.read_pickle(file_path+fe_name_tr)\n",
    "df_train_dpi = pd.read_pickle(file_path+dpi_name_tr)\n",
    "df_train_b_num = pd.read_pickle(file_path+b_num_name_tr)\n",
    "\n",
    "df_test_fe = pd.read_pickle(file_path+fe_name_te)\n",
    "df_test_dpi = pd.read_pickle(file_path+dpi_name_te)\n",
    "df_test_b_num = pd.read_pickle(file_path+b_num_name_te)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "150000"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "len(df_test_fe['abon_id'].unique())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [],
   "source": [
    "# міняємо на тест\n",
    "df_train_dpi = pd.read_pickle(file_path+dpi_name_te)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Progress: 0.30%\n",
      "Progress: 0.61%\n",
      "Progress: 0.91%\n",
      "Progress: 1.21%\n",
      "Progress: 1.52%\n",
      "Progress: 1.82%\n",
      "Progress: 2.13%\n",
      "Progress: 2.43%\n",
      "Progress: 2.74%\n",
      "Progress: 3.04%\n",
      "Progress: 3.35%\n",
      "Progress: 3.65%\n",
      "Progress: 3.96%\n",
      "Progress: 4.26%\n",
      "Progress: 4.57%\n",
      "Progress: 4.87%\n",
      "Progress: 5.18%\n",
      "Progress: 5.48%\n",
      "Progress: 5.78%\n",
      "Progress: 6.09%\n",
      "Progress: 6.39%\n",
      "Progress: 6.70%\n",
      "Progress: 7.00%\n",
      "Progress: 7.31%\n",
      "Progress: 7.61%\n",
      "Progress: 7.92%\n",
      "Progress: 8.22%\n",
      "Progress: 8.52%\n",
      "Progress: 8.83%\n",
      "Progress: 9.13%\n",
      "Progress: 9.44%\n",
      "Progress: 9.74%\n",
      "Progress: 10.05%\n",
      "Progress: 10.35%\n",
      "Progress: 10.66%\n",
      "Progress: 10.96%\n",
      "Progress: 11.27%\n",
      "Progress: 11.57%\n",
      "Progress: 11.88%\n",
      "Progress: 12.18%\n",
      "Progress: 12.49%\n",
      "Progress: 12.80%\n",
      "Progress: 13.10%\n",
      "Progress: 13.41%\n",
      "Progress: 13.71%\n",
      "Progress: 14.02%\n",
      "Progress: 14.32%\n",
      "Progress: 14.62%\n",
      "Progress: 14.92%\n",
      "Progress: 15.22%\n",
      "Progress: 15.52%\n",
      "Progress: 15.83%\n",
      "Progress: 16.13%\n",
      "Progress: 16.43%\n",
      "Progress: 16.73%\n",
      "Progress: 17.03%\n",
      "Progress: 17.33%\n",
      "Progress: 17.63%\n",
      "Progress: 17.94%\n",
      "Progress: 18.24%\n",
      "Progress: 18.54%\n",
      "Progress: 18.84%\n",
      "Progress: 19.14%\n",
      "Progress: 19.44%\n",
      "Progress: 19.74%\n",
      "Progress: 20.05%\n",
      "Progress: 20.35%\n",
      "Progress: 20.65%\n",
      "Progress: 20.95%\n",
      "Progress: 21.25%\n",
      "Progress: 21.56%\n",
      "Progress: 21.86%\n",
      "Progress: 22.16%\n",
      "Progress: 22.46%\n",
      "Progress: 22.76%\n",
      "Progress: 23.06%\n",
      "Progress: 23.36%\n",
      "Progress: 23.66%\n",
      "Progress: 23.96%\n",
      "Progress: 24.26%\n",
      "Progress: 24.57%\n",
      "Progress: 24.87%\n",
      "Progress: 25.17%\n",
      "Progress: 25.47%\n",
      "Progress: 25.77%\n",
      "Progress: 26.07%\n",
      "Progress: 26.37%\n",
      "Progress: 26.67%\n",
      "Progress: 26.97%\n",
      "Progress: 27.28%\n",
      "Progress: 27.58%\n",
      "Progress: 27.88%\n",
      "Progress: 28.18%\n",
      "Progress: 28.48%\n",
      "Progress: 28.78%\n",
      "Progress: 29.09%\n",
      "Progress: 29.39%\n",
      "Progress: 29.69%\n",
      "Progress: 29.99%\n",
      "Progress: 30.29%\n",
      "Progress: 30.59%\n",
      "Progress: 30.89%\n",
      "Progress: 31.19%\n",
      "Progress: 31.50%\n",
      "Progress: 31.80%\n",
      "Progress: 32.10%\n",
      "Progress: 32.40%\n",
      "Progress: 32.70%\n",
      "Progress: 33.00%\n",
      "Progress: 33.30%\n",
      "Progress: 33.60%\n",
      "Progress: 33.90%\n",
      "Progress: 34.20%\n",
      "Progress: 34.50%\n",
      "Progress: 34.80%\n",
      "Progress: 35.10%\n",
      "Progress: 35.40%\n",
      "Progress: 35.70%\n",
      "Progress: 35.99%\n",
      "Progress: 36.29%\n",
      "Progress: 36.59%\n",
      "Progress: 36.89%\n",
      "Progress: 37.19%\n",
      "Progress: 37.49%\n",
      "Progress: 37.79%\n",
      "Progress: 38.09%\n",
      "Progress: 38.39%\n",
      "Progress: 38.69%\n",
      "Progress: 38.99%\n",
      "Progress: 39.30%\n",
      "Progress: 39.60%\n",
      "Progress: 39.90%\n",
      "Progress: 40.21%\n",
      "Progress: 40.51%\n",
      "Progress: 40.81%\n",
      "Progress: 41.12%\n",
      "Progress: 41.42%\n",
      "Progress: 41.72%\n",
      "Progress: 42.02%\n",
      "Progress: 42.33%\n",
      "Progress: 42.63%\n",
      "Progress: 42.94%\n",
      "Progress: 43.24%\n",
      "Progress: 43.54%\n",
      "Progress: 43.85%\n",
      "Progress: 44.15%\n",
      "Progress: 44.45%\n",
      "Progress: 44.76%\n",
      "Progress: 45.06%\n",
      "Progress: 45.36%\n",
      "Progress: 45.67%\n",
      "Progress: 45.97%\n",
      "Progress: 46.27%\n",
      "Progress: 46.58%\n",
      "Progress: 46.88%\n",
      "Progress: 47.19%\n",
      "Progress: 47.49%\n",
      "Progress: 47.79%\n",
      "Progress: 48.10%\n",
      "Progress: 48.40%\n",
      "Progress: 48.70%\n",
      "Progress: 49.01%\n",
      "Progress: 49.31%\n",
      "Progress: 49.62%\n",
      "Progress: 49.92%\n",
      "Progress: 50.23%\n",
      "Progress: 50.53%\n",
      "Progress: 50.83%\n",
      "Progress: 51.14%\n",
      "Progress: 51.44%\n",
      "Progress: 51.75%\n",
      "Progress: 52.05%\n",
      "Progress: 52.35%\n",
      "Progress: 52.65%\n",
      "Progress: 52.94%\n",
      "Progress: 53.24%\n",
      "Progress: 53.54%\n",
      "Progress: 53.81%\n",
      "Progress: 54.09%\n",
      "Progress: 54.37%\n",
      "Progress: 54.65%\n",
      "Progress: 54.93%\n",
      "Progress: 55.21%\n",
      "Progress: 55.49%\n",
      "Progress: 55.75%\n",
      "Progress: 56.03%\n",
      "Progress: 56.32%\n",
      "Progress: 56.61%\n",
      "Progress: 56.89%\n",
      "Progress: 57.17%\n",
      "Progress: 57.46%\n",
      "Progress: 57.72%\n",
      "Progress: 58.01%\n",
      "Progress: 58.29%\n",
      "Progress: 58.58%\n",
      "Progress: 58.86%\n",
      "Progress: 59.15%\n",
      "Progress: 59.43%\n",
      "Progress: 59.72%\n",
      "Progress: 60.01%\n",
      "Progress: 60.29%\n",
      "Progress: 60.57%\n",
      "Progress: 60.85%\n",
      "Progress: 61.15%\n",
      "Progress: 61.44%\n",
      "Progress: 61.73%\n",
      "Progress: 62.03%\n",
      "Progress: 62.30%\n",
      "Progress: 62.59%\n",
      "Progress: 62.89%\n",
      "Progress: 63.19%\n",
      "Progress: 63.49%\n",
      "Progress: 63.79%\n",
      "Progress: 64.10%\n",
      "Progress: 64.40%\n",
      "Progress: 64.71%\n",
      "Progress: 65.01%\n",
      "Progress: 65.31%\n",
      "Progress: 65.62%\n",
      "Progress: 65.92%\n",
      "Progress: 66.23%\n",
      "Progress: 66.54%\n",
      "Progress: 66.84%\n",
      "Progress: 67.14%\n",
      "Progress: 67.45%\n",
      "Progress: 67.75%\n",
      "Progress: 68.06%\n",
      "Progress: 68.36%\n",
      "Progress: 68.67%\n",
      "Progress: 68.97%\n",
      "Progress: 69.28%\n",
      "Progress: 69.58%\n",
      "Progress: 69.88%\n",
      "Progress: 70.19%\n",
      "Progress: 70.49%\n",
      "Progress: 70.80%\n",
      "Progress: 71.10%\n",
      "Progress: 71.41%\n",
      "Progress: 71.71%\n",
      "Progress: 72.02%\n",
      "Progress: 72.32%\n",
      "Progress: 72.63%\n",
      "Progress: 72.93%\n",
      "Progress: 73.23%\n",
      "Progress: 73.54%\n",
      "Progress: 73.84%\n",
      "Progress: 74.11%\n",
      "Progress: 74.41%\n",
      "Progress: 74.71%\n",
      "Progress: 74.99%\n",
      "Progress: 75.24%\n",
      "Progress: 75.53%\n",
      "Progress: 75.81%\n",
      "Progress: 76.10%\n",
      "Progress: 76.39%\n",
      "Progress: 76.67%\n",
      "Progress: 76.96%\n",
      "Progress: 77.24%\n",
      "Progress: 77.51%\n",
      "Progress: 77.79%\n",
      "Progress: 78.09%\n",
      "Progress: 78.36%\n",
      "Progress: 78.65%\n",
      "Progress: 78.94%\n",
      "Progress: 79.24%\n",
      "Progress: 79.54%\n",
      "Progress: 79.84%\n",
      "Progress: 80.14%\n",
      "Progress: 80.44%\n",
      "Progress: 80.73%\n",
      "Progress: 81.01%\n",
      "Progress: 81.31%\n",
      "Progress: 81.61%\n",
      "Progress: 81.91%\n",
      "Progress: 82.21%\n",
      "Progress: 82.51%\n",
      "Progress: 82.81%\n",
      "Progress: 83.11%\n",
      "Progress: 83.41%\n",
      "Progress: 83.71%\n",
      "Progress: 84.01%\n",
      "Progress: 84.31%\n",
      "Progress: 84.61%\n",
      "Progress: 84.90%\n",
      "Progress: 85.19%\n",
      "Progress: 85.49%\n",
      "Progress: 85.79%\n",
      "Progress: 86.09%\n",
      "Progress: 86.38%\n",
      "Progress: 86.68%\n",
      "Progress: 86.98%\n",
      "Progress: 87.28%\n",
      "Progress: 87.57%\n",
      "Progress: 87.87%\n",
      "Progress: 88.17%\n",
      "Progress: 88.46%\n",
      "Progress: 88.73%\n",
      "Progress: 89.01%\n",
      "Progress: 89.30%\n",
      "Progress: 89.59%\n",
      "Progress: 89.89%\n",
      "Progress: 90.18%\n",
      "Progress: 90.48%\n",
      "Progress: 90.77%\n",
      "Progress: 91.07%\n",
      "Progress: 91.37%\n",
      "Progress: 91.67%\n",
      "Progress: 91.96%\n",
      "Progress: 92.26%\n",
      "Progress: 92.54%\n",
      "Progress: 92.83%\n",
      "Progress: 93.12%\n",
      "Progress: 93.41%\n",
      "Progress: 93.70%\n",
      "Progress: 94.00%\n",
      "Progress: 94.29%\n",
      "Progress: 94.59%\n",
      "Progress: 94.89%\n",
      "Progress: 95.17%\n",
      "Progress: 95.47%\n",
      "Progress: 95.75%\n",
      "Progress: 96.03%\n",
      "Progress: 96.33%\n",
      "Progress: 96.61%\n",
      "Progress: 96.90%\n",
      "Progress: 97.18%\n",
      "Progress: 97.48%\n",
      "Progress: 97.77%\n",
      "Progress: 98.07%\n",
      "Progress: 98.36%\n",
      "Progress: 98.66%\n",
      "Progress: 98.96%\n",
      "Progress: 99.26%\n",
      "Progress: 99.55%\n",
      "Progress: 99.85%\n"
     ]
    }
   ],
   "source": [
    "\n",
    "\n",
    "\n",
    "# Unique values of 'Application'\n",
    "unique_abon_ids = df_train_dpi['abon_id'].unique()\n",
    "unique_values_train = df_train_dpi['Application'].unique()\n",
    "unique_values_test = df_test_dpi['Application'].unique()\n",
    "unique_applications = np.unique(np.concatenate((unique_values_train, unique_values_test)))\n",
    "#unique_applications = np.array([14, 23])\n",
    "\n",
    "# Create the df_t dataframe with 'abon_id' column\n",
    "df_t = pd.DataFrame({'abon_id': unique_abon_ids})\n",
    "\n",
    "# Pre-filter df_train_dpi for each application\n",
    "filtered_dfs = {}\n",
    "for app in unique_applications:\n",
    "    filtered_dfs[app] = df_train_dpi[df_train_dpi['Application'] == app]\n",
    "\n",
    "# Initialize dictionaries to store column values\n",
    "column_values = {}\n",
    "for app in unique_applications:\n",
    "    col_names = [f\"{app}_SUM_of_Duration_sec\", f\"{app}_SUM_of_Volume_kb\", f\"{app}_SUM_of_Count_events\", f\"{app}_MAX_of_day_cnt\"]\n",
    "    column_values[col_names[0]] = []\n",
    "    column_values[col_names[1]] = []\n",
    "    column_values[col_names[2]] = []\n",
    "    column_values[col_names[3]] = []\n",
    "\n",
    "# Progress update interval in seconds\n",
    "progress_interval = 3 * 60  # 3 minutes\n",
    "\n",
    "# Iterate over each row in df_t\n",
    "total_rows = len(df_t)\n",
    "start_time = time.time()\n",
    "last_progress_time = start_time\n",
    "for row_index, row in df_t.iterrows():\n",
    "    abon_id = row['abon_id']\n",
    "    \n",
    "    # Iterate over each application\n",
    "    for app_index, app in enumerate(unique_applications):\n",
    "        col_names = [f\"{app}_SUM_of_Duration_sec\", f\"{app}_SUM_of_Volume_kb\", f\"{app}_SUM_of_Count_events\", f\"{app}_MAX_of_day_cnt\"]\n",
    "        filtered_df = filtered_dfs[app]\n",
    "        \n",
    "        # Find the values for the current abon_id and application\n",
    "        values = filtered_df.loc[filtered_df['abon_id'] == abon_id, ['SUM_of_Duration_sec', 'SUM_of_Volume_kb', 'SUM_of_Count_events', 'MAX_of_day_cnt']].values\n",
    "        \n",
    "        if len(values) > 0:\n",
    "            column_values[col_names[0]].append(values[0][0])\n",
    "            column_values[col_names[1]].append(values[0][1])\n",
    "            column_values[col_names[2]].append(values[0][2])\n",
    "            column_values[col_names[3]].append(values[0][3])\n",
    "        else:\n",
    "            column_values[col_names[0]].append(None)\n",
    "            column_values[col_names[1]].append(None)\n",
    "            column_values[col_names[2]].append(None)\n",
    "            column_values[col_names[3]].append(None)\n",
    "    \n",
    "    # Print progress every 3 minutes\n",
    "    current_time = time.time()\n",
    "    elapsed_time = current_time - last_progress_time\n",
    "    if elapsed_time >= progress_interval:\n",
    "        progress = (row_index + 1) / total_rows * 100\n",
    "        print(f\"Progress: {progress:.2f}%\")\n",
    "        last_progress_time = current_time\n",
    "\n",
    "# Add the values as new columns to df_t\n",
    "for col_name, values in column_values.items():\n",
    "    df_t[col_name] = values\n",
    "\n",
    "# Print the resulting df_t dataframe\n",
    "#print(df_t)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [],
   "source": [
    "# зберігти 7.07.23\n",
    "#df_t = pd.read_pickle('df_test_dpi_t_v2.pickle')\n",
    "df_t['abon_id'] = df_t['abon_id'].astype(int)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Mem. usage decreased to 678.41 Mb (75.0% reduction)\n"
     ]
    }
   ],
   "source": [
    "df_t = my_func.reduce_mem_usage(df_t)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#df_test_fe['abon_id'] = df_test_fe['abon_id'].astype(int)\n",
    "#df_test_fe = my_func.reduce_mem_usage(df_test_fe)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "150000"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Merge the two datasets based on 'abon_id' column\n",
    "df_combined = pd.merge(df_test_fe, df_t, on='abon_id', how='left')\n",
    "\n",
    "# Check the resulting combined dataframe\n",
    "len(df_combined['abon_id'])\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_combined.to_pickle('df_test+fe_dpi.pickle')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "\n",
    "df_new = pd.DataFrame(df_train_b_num['abon_id'].unique(), columns=['abon_id'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "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>bnum</th>\n",
       "      <th>call_cnt_out</th>\n",
       "      <th>call_cnt_in</th>\n",
       "      <th>call_dur_out</th>\n",
       "      <th>call_dur_in</th>\n",
       "      <th>cnt_sms_out</th>\n",
       "      <th>cnt_sms_in</th>\n",
       "      <th>abon_id</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>b'dsns ukr'</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>3.590290</td>\n",
       "      <td>1545052.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>b'polgazzbut'</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>4.786566</td>\n",
       "      <td>1545052.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>b'cyberpolic'</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>2.921812</td>\n",
       "      <td>1545235.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>b'dsns ukr'</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>3.590290</td>\n",
       "      <td>1545235.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>b'e-health'</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>2.206949</td>\n",
       "      <td>1545235.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>671243</th>\n",
       "      <td>b'whatsapp'</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.480453</td>\n",
       "      <td>130754629.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>671244</th>\n",
       "      <td>b'apple'</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.480453</td>\n",
       "      <td>130754935.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>671245</th>\n",
       "      <td>b'icu'</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>5.324077</td>\n",
       "      <td>130754935.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>671246</th>\n",
       "      <td>b'novaposhta'</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>3.590290</td>\n",
       "      <td>130754935.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>671247</th>\n",
       "      <td>b'sweet.tv'</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.480453</td>\n",
       "      <td>130754935.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>671248 rows × 8 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                 bnum  call_cnt_out  call_cnt_in  call_dur_out  call_dur_in  \\\n",
       "0         b'dsns ukr'           0.0          0.0           0.0          0.0   \n",
       "1       b'polgazzbut'           0.0          0.0           0.0          0.0   \n",
       "2       b'cyberpolic'           0.0          0.0           0.0          0.0   \n",
       "3         b'dsns ukr'           0.0          0.0           0.0          0.0   \n",
       "4         b'e-health'           0.0          0.0           0.0          0.0   \n",
       "...               ...           ...          ...           ...          ...   \n",
       "671243    b'whatsapp'           0.0          0.0           0.0          0.0   \n",
       "671244       b'apple'           0.0          0.0           0.0          0.0   \n",
       "671245         b'icu'           0.0          0.0           0.0          0.0   \n",
       "671246  b'novaposhta'           0.0          0.0           0.0          0.0   \n",
       "671247    b'sweet.tv'           0.0          0.0           0.0          0.0   \n",
       "\n",
       "        cnt_sms_out  cnt_sms_in      abon_id  \n",
       "0               0.0    3.590290    1545052.0  \n",
       "1               0.0    4.786566    1545052.0  \n",
       "2               0.0    2.921812    1545235.0  \n",
       "3               0.0    3.590290    1545235.0  \n",
       "4               0.0    2.206949    1545235.0  \n",
       "...             ...         ...          ...  \n",
       "671243          0.0    1.480453  130754629.0  \n",
       "671244          0.0    1.480453  130754935.0  \n",
       "671245          0.0    5.324077  130754935.0  \n",
       "671246          0.0    3.590290  130754935.0  \n",
       "671247          0.0    1.480453  130754935.0  \n",
       "\n",
       "[671248 rows x 8 columns]"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_train_b_num"
   ]
  },
  {
   "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>abon_id</th>\n",
       "      <th>111</th>\n",
       "      <th>3700</th>\n",
       "      <th>380800210800</th>\n",
       "      <th>1545</th>\n",
       "      <th>112</th>\n",
       "      <th>102</th>\n",
       "      <th>103</th>\n",
       "      <th>380505022250</th>\n",
       "      <th>380502907290</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1545052.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1545235.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1549591.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1558772.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1558921.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>138697</th>\n",
       "      <td>130753784.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>138698</th>\n",
       "      <td>130753819.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>138699</th>\n",
       "      <td>130754333.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>138700</th>\n",
       "      <td>130754629.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>138701</th>\n",
       "      <td>130754935.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>138702 rows × 10 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "            abon_id  111  3700  380800210800  1545  112  102  103  \\\n",
       "0         1545052.0    0     0             0     0    0    0    0   \n",
       "1         1545235.0    0     0             0     0    0    0    0   \n",
       "2         1549591.0    0     0             0     0    0    0    0   \n",
       "3         1558772.0    0     0             0     0    0    0    0   \n",
       "4         1558921.0    0     0             0     0    0    0    0   \n",
       "...             ...  ...   ...           ...   ...  ...  ...  ...   \n",
       "138697  130753784.0    0     0             0     0    0    0    0   \n",
       "138698  130753819.0    0     0             0     0    0    0    0   \n",
       "138699  130754333.0    0     0             0     0    0    0    0   \n",
       "138700  130754629.0    0     0             0     0    0    0    0   \n",
       "138701  130754935.0    0     0             0     0    0    0    0   \n",
       "\n",
       "        380505022250  380502907290  \n",
       "0                  0             0  \n",
       "1                  0             0  \n",
       "2                  0             0  \n",
       "3                  0             0  \n",
       "4                  0             0  \n",
       "...              ...           ...  \n",
       "138697             0             0  \n",
       "138698             0             0  \n",
       "138699             0             0  \n",
       "138700             0             0  \n",
       "138701             0             0  \n",
       "\n",
       "[138702 rows x 10 columns]"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "\n",
    "# List of bnum values to include in the transformed DataFrame\n",
    "bnum_list = ['111', '3700', '380800210800', '1545', '112', '102', '103', '380505022250', '380502907290']\n",
    "\n",
    "# Remove the 'b' prefix and single quotes from the 'bnum' column\n",
    "df_train_b_num['bnum'] = df_train_b_num['bnum'].str[2:-1]\n",
    "\n",
    "# Create a new DataFrame with unique abon_id\n",
    "df_transformed = pd.DataFrame(df_train_b_num['abon_id'].unique(), columns=['abon_id'])\n",
    "\n",
    "# Create a new column for each value in bnum_list\n",
    "for bnum_value in bnum_list:\n",
    "    df_transformed[bnum_value] = 0\n",
    "\n",
    "# Iterate over the rows in the original DataFrame\n",
    "for i in df_train_b_num.index:\n",
    "    # Get the current row's 'abon_id', 'bnum', and 'call_cnt_out' values\n",
    "    abon_id = df_train_b_num.loc[i, 'abon_id']\n",
    "    bnum = df_train_b_num.loc[i, 'bnum']\n",
    "    call_cnt_out = df_train_b_num.loc[i, 'call_cnt_out']\n",
    "    \n",
    "    # If 'call_cnt_out' is not null and 'bnum' is in the transformed DataFrame's columns\n",
    "    if pd.notnull(call_cnt_out) and bnum in df_transformed.columns:\n",
    "        # Find the row in the transformed DataFrame with the same 'abon_id'\n",
    "        row_index = df_transformed[df_transformed['abon_id'] == abon_id].index[0]\n",
    "        \n",
    "        # Update the 'bnum' column in that row with the 'call_cnt_out' value\n",
    "        df_transformed.loc[row_index, bnum] = call_cnt_out\n",
    "\n",
    "df_transformed\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_transformed.to_excel('num_t.xlsx')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_transformed['111'].sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th>bnum</th>\n",
       "      <th>abon_id</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "Empty DataFrame\n",
       "Columns: [abon_id]\n",
       "Index: []"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# List of bnum values to include in the transformed DataFrame\n",
    "bnum_list = ['111', '3700', '380800210800', '1545', '112', '102', '103', '380505022250', '380502907290']\n",
    "\n",
    "# Filter the DataFrame to include only the rows with bnum in bnum_list\n",
    "df_filtered = df_train_b_num[df_train_b_num['bnum'].isin(bnum_list)]\n",
    "\n",
    "# Create a pivot table with 'abon_id' as index, 'bnum' as columns, and 'call_cnt_out' as values\n",
    "# Fill missing values with 0\n",
    "df_transformed = df_filtered.pivot_table(index='abon_id', columns='bnum', values='call_cnt_out', fill_value=0)\n",
    "\n",
    "# Reset the index to make 'abon_id' a column again\n",
    "df_transformed.reset_index(inplace=True)\n",
    "\n",
    "df_transformed\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.series.Series'>\n",
      "RangeIndex: 671248 entries, 0 to 671247\n",
      "Series name: bnum\n",
      "Non-Null Count   Dtype \n",
      "--------------   ----- \n",
      "671248 non-null  object\n",
      "dtypes: object(1)\n",
      "memory usage: 5.1+ MB\n"
     ]
    }
   ],
   "source": [
    "#df_train_b_num['bnum'] = df_train_b_num['bnum'].str[2:-1]\n",
    "\n",
    "\n",
    "#df_train_b_num.pivot_table(values='call_cnt_out',index='abon_id',aggfunc=np.sum)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "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>bnum</th>\n",
       "      <th>b'102'</th>\n",
       "      <th>b'103'</th>\n",
       "      <th>b'111'</th>\n",
       "      <th>b'112'</th>\n",
       "      <th>b'1545'</th>\n",
       "      <th>b'3700'</th>\n",
       "      <th>b'380502907290'</th>\n",
       "      <th>b'380505022250'</th>\n",
       "      <th>b'380800210800'</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>abon_id</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1560464.0</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2.921812</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1610598.0</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2.206949</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1610707.0</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2.921812</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1612053.0</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2.206949</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1637193.0</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1.480453</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>130748999.0</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1.480453</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>130749792.0</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>3.590290</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>130753073.0</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2.206949</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>130753819.0</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2.206949</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>130754629.0</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1.480453</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>21452 rows × 9 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "bnum         b'102'  b'103'    b'111'  b'112'  b'1545'   b'3700'  \\\n",
       "abon_id                                                            \n",
       "1560464.0       NaN     NaN       NaN     NaN      NaN  2.921812   \n",
       "1610598.0       NaN     NaN       NaN     NaN      NaN       NaN   \n",
       "1610707.0       NaN     NaN       NaN     NaN      NaN       NaN   \n",
       "1612053.0       NaN     NaN       NaN     NaN      NaN  2.206949   \n",
       "1637193.0       NaN     NaN       NaN     NaN      NaN       NaN   \n",
       "...             ...     ...       ...     ...      ...       ...   \n",
       "130748999.0     NaN     NaN  1.480453     NaN      NaN       NaN   \n",
       "130749792.0     NaN     NaN  3.590290     NaN      NaN       NaN   \n",
       "130753073.0     NaN     NaN  2.206949     NaN      NaN       NaN   \n",
       "130753819.0     NaN     NaN  2.206949     NaN      NaN       NaN   \n",
       "130754629.0     NaN     NaN       NaN     NaN      NaN  1.480453   \n",
       "\n",
       "bnum         b'380502907290'  b'380505022250'  b'380800210800'  \n",
       "abon_id                                                         \n",
       "1560464.0                NaN              NaN              NaN  \n",
       "1610598.0           2.206949              NaN              NaN  \n",
       "1610707.0                NaN              NaN         2.921812  \n",
       "1612053.0                NaN              NaN              NaN  \n",
       "1637193.0                NaN              NaN         1.480453  \n",
       "...                      ...              ...              ...  \n",
       "130748999.0              NaN              NaN              NaN  \n",
       "130749792.0              NaN              NaN              NaN  \n",
       "130753073.0              NaN              NaN              NaN  \n",
       "130753819.0              NaN              NaN              NaN  \n",
       "130754629.0              NaN              NaN              NaN  \n",
       "\n",
       "[21452 rows x 9 columns]"
      ]
     },
     "execution_count": 30,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "bnum_list = [\"b'111'\", \"b'3700'\", \"b'380800210800'\", \"b'1545'\", \"b'112'\", \"b'102'\", \"b'103'\", \"b'380505022250'\", \"b'380502907290'\"]\n",
    "df_train_b_num['bnum'] = df_train_b_num['bnum'].astype(str)\n",
    "\n",
    "# Filter the DataFrame based on the 'bnum' column\n",
    "df_filtered = df_train_b_num[df_train_b_num['bnum'].str.contains('|'.join(bnum_list))]\n",
    "\n",
    "\n",
    "\n",
    "# # Pivot the filtered DataFrame\n",
    "pivot_table = df_filtered.pivot_table(values='call_cnt_out', index='abon_id', columns='bnum', aggfunc=np.sum)\n",
    "\n",
    "# # Fill NaN values with 0\n",
    "# pivot_table = pivot_table.fillna(0)\n",
    "\n",
    "pivot_table['b\\'103\\'']\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "1101.872683656741"
      ]
     },
     "execution_count": 31,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pivot_table['b\\'103\\''].sum()\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Define the old and new column names\n",
    "old_column_names = [\"b'111'\", \"b'3700'\", \"b'380800210800'\", \"b'1545'\", \"b'112'\", \"b'102'\", \"b'103'\", \"b'380505022250'\", \"b'380502907290'\"]\n",
    "new_column_names = [\"vodafone\", \"PryvatBank\", \"OshchadBank\", \"GovUa\", \"ExtDopomoga\", \"Police\", \"Emergancy\", \"VoliaCable\", \"PUMB\"]\n",
    "\n",
    "# Create a dictionary to map the old column names to the new column names\n",
    "column_name_mapping = dict(zip(old_column_names, new_column_names))\n",
    "\n",
    "# Rename the columns in the DataFrame\n",
    "pivot_table = pivot_table.rename(columns=column_name_mapping)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "metadata": {},
   "outputs": [],
   "source": [
    "pivot_table.to_pickle('B_num_transformed.pickle')"
   ]
  }
 ],
 "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.10.4"
  },
  "orig_nbformat": 4
 },
 "nbformat": 4,
 "nbformat_minor": 2
}