FeatureEngineering_Activity_Solution.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {
    "colab": {},
    "colab_type": "code",
    "id": "HEiOAwQPW0qb"
   },
   "outputs": [],
   "source": [
    "import pandas as pd"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {
    "colab": {},
    "colab_type": "code",
    "id": "j7whidfaYjns"
   },
   "outputs": [],
   "source": [
    "disp_url = 'https://raw.githubusercontent.com/fenago/MLEssentials/main/datasets/disp.csv'\n",
    "account_url = 'https://raw.githubusercontent.com/fenago/MLEssentials/main/datasets/account.csv'\n",
    "client_url = 'https://raw.githubusercontent.com/fenago/MLEssentials/main/datasets/client.csv'\n",
    "trans_url = './datasets/trans.csv'\n",
    "# data can be found here: https://github.com/fenago/MLEssentials/tree/main/datasets"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/",
     "height": 71
    },
    "colab_type": "code",
    "executionInfo": {
     "elapsed": 8712,
     "status": "ok",
     "timestamp": 1574650430652,
     "user": {
      "displayName": "Anthony So",
      "photoUrl": "https://lh3.googleusercontent.com/a-/AAuE7mCYY-iGjUIqBSnlLoszfZTN7rU7FRNg05Rdt9Ii3A=s64",
      "userId": "11809607246124237079"
     },
     "user_tz": -660
    },
    "id": "nFMz2jNVt-xy",
    "outputId": "95f4874b-575f-4b8d-ede4-734116c4a502"
   },
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/opt/conda/lib/python3.8/site-packages/IPython/core/interactiveshell.py:3146: DtypeWarning: Columns (8) have mixed types.Specify dtype option on import or set low_memory=False.\n",
      "  has_raised = await self.run_ast_nodes(code_ast.body, cell_name,\n"
     ]
    }
   ],
   "source": [
    "df_disp = pd.read_csv(disp_url, sep=';')\n",
    "df_trans = pd.read_csv(trans_url, sep=';')\n",
    "df_account = pd.read_csv(account_url, sep=';')\n",
    "df_client = pd.read_csv(client_url, sep=';')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/",
     "height": 204
    },
    "colab_type": "code",
    "executionInfo": {
     "elapsed": 8706,
     "status": "ok",
     "timestamp": 1574650430653,
     "user": {
      "displayName": "Anthony So",
      "photoUrl": "https://lh3.googleusercontent.com/a-/AAuE7mCYY-iGjUIqBSnlLoszfZTN7rU7FRNg05Rdt9Ii3A=s64",
      "userId": "11809607246124237079"
     },
     "user_tz": -660
    },
    "id": "xAqQ7rpV0nkT",
    "outputId": "d059dc0b-ace2-467e-c3cd-8361faf44170"
   },
   "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>trans_id</th>\n",
       "      <th>account_id</th>\n",
       "      <th>date</th>\n",
       "      <th>type</th>\n",
       "      <th>operation</th>\n",
       "      <th>amount</th>\n",
       "      <th>balance</th>\n",
       "      <th>k_symbol</th>\n",
       "      <th>bank</th>\n",
       "      <th>account</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>695247</td>\n",
       "      <td>2378</td>\n",
       "      <td>930101</td>\n",
       "      <td>PRIJEM</td>\n",
       "      <td>VKLAD</td>\n",
       "      <td>700.0</td>\n",
       "      <td>700.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>171812</td>\n",
       "      <td>576</td>\n",
       "      <td>930101</td>\n",
       "      <td>PRIJEM</td>\n",
       "      <td>VKLAD</td>\n",
       "      <td>900.0</td>\n",
       "      <td>900.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>207264</td>\n",
       "      <td>704</td>\n",
       "      <td>930101</td>\n",
       "      <td>PRIJEM</td>\n",
       "      <td>VKLAD</td>\n",
       "      <td>1000.0</td>\n",
       "      <td>1000.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1117247</td>\n",
       "      <td>3818</td>\n",
       "      <td>930101</td>\n",
       "      <td>PRIJEM</td>\n",
       "      <td>VKLAD</td>\n",
       "      <td>600.0</td>\n",
       "      <td>600.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>579373</td>\n",
       "      <td>1972</td>\n",
       "      <td>930102</td>\n",
       "      <td>PRIJEM</td>\n",
       "      <td>VKLAD</td>\n",
       "      <td>400.0</td>\n",
       "      <td>400.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   trans_id  account_id    date    type operation  amount  balance k_symbol  \\\n",
       "0    695247        2378  930101  PRIJEM     VKLAD   700.0    700.0      NaN   \n",
       "1    171812         576  930101  PRIJEM     VKLAD   900.0    900.0      NaN   \n",
       "2    207264         704  930101  PRIJEM     VKLAD  1000.0   1000.0      NaN   \n",
       "3   1117247        3818  930101  PRIJEM     VKLAD   600.0    600.0      NaN   \n",
       "4    579373        1972  930102  PRIJEM     VKLAD   400.0    400.0      NaN   \n",
       "\n",
       "  bank  account  \n",
       "0  NaN      NaN  \n",
       "1  NaN      NaN  \n",
       "2  NaN      NaN  \n",
       "3  NaN      NaN  \n",
       "4  NaN      NaN  "
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_trans.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/",
     "height": 34
    },
    "colab_type": "code",
    "executionInfo": {
     "elapsed": 8701,
     "status": "ok",
     "timestamp": 1574650430654,
     "user": {
      "displayName": "Anthony So",
      "photoUrl": "https://lh3.googleusercontent.com/a-/AAuE7mCYY-iGjUIqBSnlLoszfZTN7rU7FRNg05Rdt9Ii3A=s64",
      "userId": "11809607246124237079"
     },
     "user_tz": -660
    },
    "id": "zybRLGrd2bPf",
    "outputId": "8e1da605-af26-46e1-9bf1-6e5b9a337857"
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(1056320, 10)"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_trans.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/",
     "height": 204
    },
    "colab_type": "code",
    "executionInfo": {
     "elapsed": 8696,
     "status": "ok",
     "timestamp": 1574650430655,
     "user": {
      "displayName": "Anthony So",
      "photoUrl": "https://lh3.googleusercontent.com/a-/AAuE7mCYY-iGjUIqBSnlLoszfZTN7rU7FRNg05Rdt9Ii3A=s64",
      "userId": "11809607246124237079"
     },
     "user_tz": -660
    },
    "id": "IvvSvYl42Koi",
    "outputId": "c90574b1-5620-4d6b-e13b-ce8ba5ae66f0"
   },
   "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>account_id</th>\n",
       "      <th>district_id</th>\n",
       "      <th>frequency</th>\n",
       "      <th>date</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>576</td>\n",
       "      <td>55</td>\n",
       "      <td>POPLATEK MESICNE</td>\n",
       "      <td>930101</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>3818</td>\n",
       "      <td>74</td>\n",
       "      <td>POPLATEK MESICNE</td>\n",
       "      <td>930101</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>704</td>\n",
       "      <td>55</td>\n",
       "      <td>POPLATEK MESICNE</td>\n",
       "      <td>930101</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2378</td>\n",
       "      <td>16</td>\n",
       "      <td>POPLATEK MESICNE</td>\n",
       "      <td>930101</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2632</td>\n",
       "      <td>24</td>\n",
       "      <td>POPLATEK MESICNE</td>\n",
       "      <td>930102</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   account_id  district_id         frequency    date\n",
       "0         576           55  POPLATEK MESICNE  930101\n",
       "1        3818           74  POPLATEK MESICNE  930101\n",
       "2         704           55  POPLATEK MESICNE  930101\n",
       "3        2378           16  POPLATEK MESICNE  930101\n",
       "4        2632           24  POPLATEK MESICNE  930102"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_account.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {
    "colab": {},
    "colab_type": "code",
    "id": "Fk2bTvEp17Bp"
   },
   "outputs": [],
   "source": [
    "df_trans_acc = pd.merge(df_trans, df_account, how='left', on='account_id')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/",
     "height": 34
    },
    "colab_type": "code",
    "executionInfo": {
     "elapsed": 9099,
     "status": "ok",
     "timestamp": 1574650431066,
     "user": {
      "displayName": "Anthony So",
      "photoUrl": "https://lh3.googleusercontent.com/a-/AAuE7mCYY-iGjUIqBSnlLoszfZTN7rU7FRNg05Rdt9Ii3A=s64",
      "userId": "11809607246124237079"
     },
     "user_tz": -660
    },
    "id": "9BN6zHeL2lOx",
    "outputId": "357d0b26-daed-429e-dd5f-7f9c0e5a4a27"
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(1056320, 13)"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_trans_acc.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/",
     "height": 204
    },
    "colab_type": "code",
    "executionInfo": {
     "elapsed": 9095,
     "status": "ok",
     "timestamp": 1574650431067,
     "user": {
      "displayName": "Anthony So",
      "photoUrl": "https://lh3.googleusercontent.com/a-/AAuE7mCYY-iGjUIqBSnlLoszfZTN7rU7FRNg05Rdt9Ii3A=s64",
      "userId": "11809607246124237079"
     },
     "user_tz": -660
    },
    "id": "98f8YCkO2q41",
    "outputId": "213b7737-8471-49f8-f25a-7aa78acac4f8"
   },
   "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>disp_id</th>\n",
       "      <th>client_id</th>\n",
       "      <th>account_id</th>\n",
       "      <th>type</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>OWNER</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>OWNER</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "      <td>2</td>\n",
       "      <td>DISPONENT</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>3</td>\n",
       "      <td>OWNER</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>5</td>\n",
       "      <td>5</td>\n",
       "      <td>3</td>\n",
       "      <td>DISPONENT</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   disp_id  client_id  account_id       type\n",
       "0        1          1           1      OWNER\n",
       "1        2          2           2      OWNER\n",
       "2        3          3           2  DISPONENT\n",
       "3        4          4           3      OWNER\n",
       "4        5          5           3  DISPONENT"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_disp.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {
    "colab": {},
    "colab_type": "code",
    "id": "ap--Zddi3KYG"
   },
   "outputs": [],
   "source": [
    "df_disp_owner = df_disp[df_disp['type'] == 'OWNER']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/",
     "height": 34
    },
    "colab_type": "code",
    "executionInfo": {
     "elapsed": 9088,
     "status": "ok",
     "timestamp": 1574650431068,
     "user": {
      "displayName": "Anthony So",
      "photoUrl": "https://lh3.googleusercontent.com/a-/AAuE7mCYY-iGjUIqBSnlLoszfZTN7rU7FRNg05Rdt9Ii3A=s64",
      "userId": "11809607246124237079"
     },
     "user_tz": -660
    },
    "id": "8rvJpD6V3VWU",
    "outputId": "e950a8f3-2d85-4c82-b9b6-d62b6df6cc2d"
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_disp_owner.duplicated(subset='account_id').sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/",
     "height": 34
    },
    "colab_type": "code",
    "executionInfo": {
     "elapsed": 9081,
     "status": "ok",
     "timestamp": 1574650431069,
     "user": {
      "displayName": "Anthony So",
      "photoUrl": "https://lh3.googleusercontent.com/a-/AAuE7mCYY-iGjUIqBSnlLoszfZTN7rU7FRNg05Rdt9Ii3A=s64",
      "userId": "11809607246124237079"
     },
     "user_tz": -660
    },
    "id": "aeshCYj63Ezw",
    "outputId": "27de9e85-0643-4997-e318-f3d6bd7b3dd6"
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(1056320, 16)"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_trans_acc_disp = pd.merge(df_trans_acc, df_disp_owner, how='left', on='account_id')\n",
    "df_trans_acc_disp.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/",
     "height": 204
    },
    "colab_type": "code",
    "executionInfo": {
     "elapsed": 9076,
     "status": "ok",
     "timestamp": 1574650431070,
     "user": {
      "displayName": "Anthony So",
      "photoUrl": "https://lh3.googleusercontent.com/a-/AAuE7mCYY-iGjUIqBSnlLoszfZTN7rU7FRNg05Rdt9Ii3A=s64",
      "userId": "11809607246124237079"
     },
     "user_tz": -660
    },
    "id": "J-_jUOwP3GGJ",
    "outputId": "3b19d650-c852-41d9-fa99-7123158a34ce"
   },
   "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>client_id</th>\n",
       "      <th>birth_number</th>\n",
       "      <th>district_id</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>706213</td>\n",
       "      <td>18</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>450204</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "      <td>406009</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4</td>\n",
       "      <td>561201</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>5</td>\n",
       "      <td>605703</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   client_id  birth_number  district_id\n",
       "0          1        706213           18\n",
       "1          2        450204            1\n",
       "2          3        406009            1\n",
       "3          4        561201            5\n",
       "4          5        605703            5"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_client.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/",
     "height": 34
    },
    "colab_type": "code",
    "executionInfo": {
     "elapsed": 9440,
     "status": "ok",
     "timestamp": 1574650431439,
     "user": {
      "displayName": "Anthony So",
      "photoUrl": "https://lh3.googleusercontent.com/a-/AAuE7mCYY-iGjUIqBSnlLoszfZTN7rU7FRNg05Rdt9Ii3A=s64",
      "userId": "11809607246124237079"
     },
     "user_tz": -660
    },
    "id": "35JidayO4C5X",
    "outputId": "da451ba8-56fd-4d66-f147-5d3c8f817991"
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(1056320, 17)"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_merged = pd.merge(df_trans_acc_disp, df_client, how='left', on=['client_id', 'district_id'])\n",
    "df_merged.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/",
     "height": 85
    },
    "colab_type": "code",
    "executionInfo": {
     "elapsed": 9436,
     "status": "ok",
     "timestamp": 1574650431440,
     "user": {
      "displayName": "Anthony So",
      "photoUrl": "https://lh3.googleusercontent.com/a-/AAuE7mCYY-iGjUIqBSnlLoszfZTN7rU7FRNg05Rdt9Ii3A=s64",
      "userId": "11809607246124237079"
     },
     "user_tz": -660
    },
    "id": "8wGMCNV547RR",
    "outputId": "1069eb5a-7f28-47b4-8cf5-aa03fee7eac9"
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index(['trans_id', 'account_id', 'date_x', 'type_x', 'operation', 'amount',\n",
       "       'balance', 'k_symbol', 'bank', 'account', 'district_id', 'frequency',\n",
       "       'date_y', 'disp_id', 'client_id', 'type_y', 'birth_number'],\n",
       "      dtype='object')"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_merged.columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {
    "colab": {},
    "colab_type": "code",
    "id": "2oyeYQnB5ODm"
   },
   "outputs": [],
   "source": [
    "df_merged.rename(columns={'date_x': 'trans_date', 'type_x': 'trans_type', 'date_y':'account_creation', 'type_y':'client_type'}, inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/",
     "height": 309
    },
    "colab_type": "code",
    "executionInfo": {
     "elapsed": 9997,
     "status": "ok",
     "timestamp": 1574650432011,
     "user": {
      "displayName": "Anthony So",
      "photoUrl": "https://lh3.googleusercontent.com/a-/AAuE7mCYY-iGjUIqBSnlLoszfZTN7rU7FRNg05Rdt9Ii3A=s64",
      "userId": "11809607246124237079"
     },
     "user_tz": -660
    },
    "id": "tiPA68we6G-v",
    "outputId": "be267f16-4953-459f-a9d9-2139b91f4f31"
   },
   "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>trans_id</th>\n",
       "      <th>account_id</th>\n",
       "      <th>trans_date</th>\n",
       "      <th>trans_type</th>\n",
       "      <th>operation</th>\n",
       "      <th>amount</th>\n",
       "      <th>balance</th>\n",
       "      <th>k_symbol</th>\n",
       "      <th>bank</th>\n",
       "      <th>account</th>\n",
       "      <th>district_id</th>\n",
       "      <th>frequency</th>\n",
       "      <th>account_creation</th>\n",
       "      <th>disp_id</th>\n",
       "      <th>client_id</th>\n",
       "      <th>client_type</th>\n",
       "      <th>birth_number</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>695247</td>\n",
       "      <td>2378</td>\n",
       "      <td>930101</td>\n",
       "      <td>PRIJEM</td>\n",
       "      <td>VKLAD</td>\n",
       "      <td>700.0</td>\n",
       "      <td>700.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>16</td>\n",
       "      <td>POPLATEK MESICNE</td>\n",
       "      <td>930101</td>\n",
       "      <td>2873</td>\n",
       "      <td>2873</td>\n",
       "      <td>OWNER</td>\n",
       "      <td>755324.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>171812</td>\n",
       "      <td>576</td>\n",
       "      <td>930101</td>\n",
       "      <td>PRIJEM</td>\n",
       "      <td>VKLAD</td>\n",
       "      <td>900.0</td>\n",
       "      <td>900.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>55</td>\n",
       "      <td>POPLATEK MESICNE</td>\n",
       "      <td>930101</td>\n",
       "      <td>692</td>\n",
       "      <td>692</td>\n",
       "      <td>OWNER</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>207264</td>\n",
       "      <td>704</td>\n",
       "      <td>930101</td>\n",
       "      <td>PRIJEM</td>\n",
       "      <td>VKLAD</td>\n",
       "      <td>1000.0</td>\n",
       "      <td>1000.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>55</td>\n",
       "      <td>POPLATEK MESICNE</td>\n",
       "      <td>930101</td>\n",
       "      <td>844</td>\n",
       "      <td>844</td>\n",
       "      <td>OWNER</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1117247</td>\n",
       "      <td>3818</td>\n",
       "      <td>930101</td>\n",
       "      <td>PRIJEM</td>\n",
       "      <td>VKLAD</td>\n",
       "      <td>600.0</td>\n",
       "      <td>600.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>74</td>\n",
       "      <td>POPLATEK MESICNE</td>\n",
       "      <td>930101</td>\n",
       "      <td>4601</td>\n",
       "      <td>4601</td>\n",
       "      <td>OWNER</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>579373</td>\n",
       "      <td>1972</td>\n",
       "      <td>930102</td>\n",
       "      <td>PRIJEM</td>\n",
       "      <td>VKLAD</td>\n",
       "      <td>400.0</td>\n",
       "      <td>400.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>77</td>\n",
       "      <td>POPLATEK MESICNE</td>\n",
       "      <td>930102</td>\n",
       "      <td>2397</td>\n",
       "      <td>2397</td>\n",
       "      <td>OWNER</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   trans_id  account_id  trans_date trans_type operation  amount  balance  \\\n",
       "0    695247        2378      930101     PRIJEM     VKLAD   700.0    700.0   \n",
       "1    171812         576      930101     PRIJEM     VKLAD   900.0    900.0   \n",
       "2    207264         704      930101     PRIJEM     VKLAD  1000.0   1000.0   \n",
       "3   1117247        3818      930101     PRIJEM     VKLAD   600.0    600.0   \n",
       "4    579373        1972      930102     PRIJEM     VKLAD   400.0    400.0   \n",
       "\n",
       "  k_symbol bank  account  district_id         frequency  account_creation  \\\n",
       "0      NaN  NaN      NaN           16  POPLATEK MESICNE            930101   \n",
       "1      NaN  NaN      NaN           55  POPLATEK MESICNE            930101   \n",
       "2      NaN  NaN      NaN           55  POPLATEK MESICNE            930101   \n",
       "3      NaN  NaN      NaN           74  POPLATEK MESICNE            930101   \n",
       "4      NaN  NaN      NaN           77  POPLATEK MESICNE            930102   \n",
       "\n",
       "   disp_id  client_id client_type  birth_number  \n",
       "0     2873       2873       OWNER      755324.0  \n",
       "1      692        692       OWNER           NaN  \n",
       "2      844        844       OWNER           NaN  \n",
       "3     4601       4601       OWNER           NaN  \n",
       "4     2397       2397       OWNER           NaN  "
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_merged.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/",
     "height": 323
    },
    "colab_type": "code",
    "executionInfo": {
     "elapsed": 9993,
     "status": "ok",
     "timestamp": 1574650432012,
     "user": {
      "displayName": "Anthony So",
      "photoUrl": "https://lh3.googleusercontent.com/a-/AAuE7mCYY-iGjUIqBSnlLoszfZTN7rU7FRNg05Rdt9Ii3A=s64",
      "userId": "11809607246124237079"
     },
     "user_tz": -660
    },
    "id": "GV_1Jh4H6Qy4",
    "outputId": "e0a7024c-8bc7-429c-a4c8-c012c21347fc"
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "trans_id              int64\n",
       "account_id            int64\n",
       "trans_date            int64\n",
       "trans_type           object\n",
       "operation            object\n",
       "amount              float64\n",
       "balance             float64\n",
       "k_symbol             object\n",
       "bank                 object\n",
       "account             float64\n",
       "district_id           int64\n",
       "frequency            object\n",
       "account_creation      int64\n",
       "disp_id               int64\n",
       "client_id             int64\n",
       "client_type          object\n",
       "birth_number        float64\n",
       "dtype: object"
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_merged.dtypes"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {
    "colab": {},
    "colab_type": "code",
    "id": "tLCnpFRU5lna"
   },
   "outputs": [],
   "source": [
    "df_merged['trans_date'] = pd.to_datetime(df_merged['trans_date'], format=\"%y%m%d\")\n",
    "df_merged['account_creation'] = pd.to_datetime(df_merged['account_creation'], format=\"%y%m%d\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/",
     "height": 323
    },
    "colab_type": "code",
    "executionInfo": {
     "elapsed": 9986,
     "status": "ok",
     "timestamp": 1574650432014,
     "user": {
      "displayName": "Anthony So",
      "photoUrl": "https://lh3.googleusercontent.com/a-/AAuE7mCYY-iGjUIqBSnlLoszfZTN7rU7FRNg05Rdt9Ii3A=s64",
      "userId": "11809607246124237079"
     },
     "user_tz": -660
    },
    "id": "6f791uGS6P25",
    "outputId": "37cec372-1702-40de-8981-c1320a696ff1"
   },
   "outputs": [],
   "source": [
    "df_merged.dtypes"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "colab": {},
    "colab_type": "code",
    "id": "p3Z3dB8h7lXd"
   },
   "outputs": [],
   "source": [
    "df_merged['is_female'] = (df_merged['birth_number'] % 10000) / 5000 > 1"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/",
     "height": 119
    },
    "colab_type": "code",
    "executionInfo": {
     "elapsed": 9980,
     "status": "ok",
     "timestamp": 1574650432015,
     "user": {
      "displayName": "Anthony So",
      "photoUrl": "https://lh3.googleusercontent.com/a-/AAuE7mCYY-iGjUIqBSnlLoszfZTN7rU7FRNg05Rdt9Ii3A=s64",
      "userId": "11809607246124237079"
     },
     "user_tz": -660
    },
    "id": "4_Ml3J8f7PI_",
    "outputId": "68f53d4a-618f-4c25-b9f7-9ded0650191a"
   },
   "outputs": [],
   "source": [
    "df_merged['birth_number'].head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "colab": {},
    "colab_type": "code",
    "id": "X4yKolNl8ys8"
   },
   "outputs": [],
   "source": [
    "df_merged.loc[df_merged['is_female'] == True, 'birth_number'] -= 5000"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/",
     "height": 119
    },
    "colab_type": "code",
    "executionInfo": {
     "elapsed": 9973,
     "status": "ok",
     "timestamp": 1574650432016,
     "user": {
      "displayName": "Anthony So",
      "photoUrl": "https://lh3.googleusercontent.com/a-/AAuE7mCYY-iGjUIqBSnlLoszfZTN7rU7FRNg05Rdt9Ii3A=s64",
      "userId": "11809607246124237079"
     },
     "user_tz": -660
    },
    "id": "HxWTe7zS91aB",
    "outputId": "4260e7bd-1b96-49d6-a392-a7efa8ac9e3d"
   },
   "outputs": [],
   "source": [
    "df_merged['birth_number'].head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/",
     "height": 221
    },
    "colab_type": "code",
    "executionInfo": {
     "elapsed": 9967,
     "status": "ok",
     "timestamp": 1574650432017,
     "user": {
      "displayName": "Anthony So",
      "photoUrl": "https://lh3.googleusercontent.com/a-/AAuE7mCYY-iGjUIqBSnlLoszfZTN7rU7FRNg05Rdt9Ii3A=s64",
      "userId": "11809607246124237079"
     },
     "user_tz": -660
    },
    "id": "A8cKsZf86v9u",
    "outputId": "75087216-14a2-40fa-966d-960ffeb45acb"
   },
   "outputs": [],
   "source": [
    "pd.to_datetime(df_merged['birth_number'], format=\"%y%m%d\", errors='coerce')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/",
     "height": 119
    },
    "colab_type": "code",
    "executionInfo": {
     "elapsed": 10897,
     "status": "ok",
     "timestamp": 1574650432952,
     "user": {
      "displayName": "Anthony So",
      "photoUrl": "https://lh3.googleusercontent.com/a-/AAuE7mCYY-iGjUIqBSnlLoszfZTN7rU7FRNg05Rdt9Ii3A=s64",
      "userId": "11809607246124237079"
     },
     "user_tz": -660
    },
    "id": "F6YkBC4eClRA",
    "outputId": "f676d8f3-f03a-4bbf-f93a-69e2b032c225"
   },
   "outputs": [],
   "source": [
    "df_merged['birth_number'] = df_merged['birth_number'].astype(str)\n",
    "df_merged['birth_number'].head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/",
     "height": 119
    },
    "colab_type": "code",
    "executionInfo": {
     "elapsed": 10893,
     "status": "ok",
     "timestamp": 1574650432953,
     "user": {
      "displayName": "Anthony So",
      "photoUrl": "https://lh3.googleusercontent.com/a-/AAuE7mCYY-iGjUIqBSnlLoszfZTN7rU7FRNg05Rdt9Ii3A=s64",
      "userId": "11809607246124237079"
     },
     "user_tz": -660
    },
    "id": "RkEYHyd4Dtz7",
    "outputId": "5cba124b-35fc-4a9c-f790-a30ff44a6cb0"
   },
   "outputs": [],
   "source": [
    "import numpy as np\n",
    "df_merged.loc[df_merged['birth_number'] == 'nan', 'birth_number'] = np.nan\n",
    "df_merged['birth_number'].head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/",
     "height": 119
    },
    "colab_type": "code",
    "executionInfo": {
     "elapsed": 11346,
     "status": "ok",
     "timestamp": 1574650433411,
     "user": {
      "displayName": "Anthony So",
      "photoUrl": "https://lh3.googleusercontent.com/a-/AAuE7mCYY-iGjUIqBSnlLoszfZTN7rU7FRNg05Rdt9Ii3A=s64",
      "userId": "11809607246124237079"
     },
     "user_tz": -660
    },
    "id": "y2LZf7Q3CIU7",
    "outputId": "1e4fa5c2-3a93-4015-a27f-067466bdc28e"
   },
   "outputs": [],
   "source": [
    "df_merged.loc[~df_merged['birth_number'].isna(), 'birth_number'] = '19' + df_merged.loc[~df_merged['birth_number'].isna(), 'birth_number']\n",
    "df_merged['birth_number'].head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/",
     "height": 374
    },
    "colab_type": "code",
    "executionInfo": {
     "elapsed": 11341,
     "status": "ok",
     "timestamp": 1574650433411,
     "user": {
      "displayName": "Anthony So",
      "photoUrl": "https://lh3.googleusercontent.com/a-/AAuE7mCYY-iGjUIqBSnlLoszfZTN7rU7FRNg05Rdt9Ii3A=s64",
      "userId": "11809607246124237079"
     },
     "user_tz": -660
    },
    "id": "14FMFck_D5pE",
    "outputId": "64ade2cc-2cea-43eb-c7eb-37931a93c001"
   },
   "outputs": [],
   "source": [
    "df_merged['birth_number'] = pd.to_datetime(df_merged['birth_number'], format=\"%Y%m%d\", errors='coerce')\n",
    "df_merged['birth_number'].head(20)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "colab": {},
    "colab_type": "code",
    "id": "UnOMNI5w5-jD"
   },
   "outputs": [],
   "source": [
    "df_merged['age_at_creation'] = df_merged['account_creation'] - df_merged['birth_number']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "colab": {},
    "colab_type": "code",
    "id": "DFt1MP-6E7cI"
   },
   "outputs": [],
   "source": [
    "df_merged['age_at_creation'] = df_merged['age_at_creation'] / np.timedelta64(1,'Y')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/",
     "height": 309
    },
    "colab_type": "code",
    "executionInfo": {
     "elapsed": 11751,
     "status": "ok",
     "timestamp": 1574650433833,
     "user": {
      "displayName": "Anthony So",
      "photoUrl": "https://lh3.googleusercontent.com/a-/AAuE7mCYY-iGjUIqBSnlLoszfZTN7rU7FRNg05Rdt9Ii3A=s64",
      "userId": "11809607246124237079"
     },
     "user_tz": -660
    },
    "id": "a_c3aRiiFzga",
    "outputId": "13446355-b3af-478f-a143-931f06f91617"
   },
   "outputs": [],
   "source": [
    "df_merged['age_at_creation'] = df_merged['age_at_creation'].round()\n",
    "df_merged.head()"
   ]
  }
 ],
 "metadata": {
  "colab": {
   "collapsed_sections": [],
   "name": "Activity12_1.ipynb",
   "provenance": []
  },
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.8.6"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}