Seminar_Pandas3_several_df_20230323.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "65eeda8d-31a9-4b49-8cbb-6040b9b02dd9",
   "metadata": {},
   "source": [
    "# Pandas. Работа с несколькими датафреймами."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "d97352d8-9af7-488d-bd1d-047d2089df8f",
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import numpy as np\n",
    "\n",
    "pd.set_option(\"display.max_columns\", None, \"display.max_rows\", None)\n",
    "\n",
    "from IPython.display import Image"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "ca2a098c-0ee7-4e73-b4b3-0509ac7df943",
   "metadata": {},
   "source": [
    "### Const"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "9ab15f8f-0542-4426-bb77-dd2998bd5f15",
   "metadata": {},
   "outputs": [],
   "source": [
    "application_train_data_path =  'data/home-credit-default-risk/application_train_small.csv'\n",
    "bureau_data_path = 'data/home-credit-default-risk/bureau_small.csv'"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "a912a02d-0bd9-4d4a-82b3-42d1429b1f5e",
   "metadata": {},
   "source": [
    "### Application data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "15d7cc8e-7cd6-439c-a476-fc2d182b03b9",
   "metadata": {},
   "outputs": [],
   "source": [
    "df = pd.read_csv(application_train_data_path)\n",
    "df = df.rename(columns = lambda x: x.lower())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "c1dc1200-96e1-424c-bdbe-e43ce2f9ecdd",
   "metadata": {},
   "outputs": [],
   "source": [
    "need_cols = [\n",
    "    'sk_id_curr',\n",
    "    'target',\n",
    "    'name_contract_type',\n",
    "    'code_gender',\n",
    "    'flag_own_car',\n",
    "    'flag_own_realty',\n",
    "    'cnt_children',\n",
    "    'amt_income_total',\n",
    "    'amt_credit',\n",
    "    'amt_annuity',\n",
    "    'amt_goods_price',\n",
    "    'name_type_suite',\n",
    "    'name_income_type',\n",
    "    'name_education_type',\n",
    "    'name_family_status',\n",
    "    'name_housing_type',\n",
    "    'region_population_relative',\n",
    "    'days_birth',\n",
    "    'days_employed',\n",
    "    'days_registration',\n",
    "    'days_id_publish',\n",
    "    'own_car_age',\n",
    "    'flag_mobil',\n",
    "    'flag_emp_phone',\n",
    "    'flag_work_phone',\n",
    "    'flag_cont_mobile',\n",
    "    'flag_phone',\n",
    "    'flag_email',\n",
    "    'occupation_type',\n",
    "    'cnt_fam_members',\n",
    "    'amt_req_credit_bureau_hour',\n",
    "    'amt_req_credit_bureau_day',\n",
    "    'amt_req_credit_bureau_week',\n",
    "    'amt_req_credit_bureau_mon',\n",
    "    'amt_req_credit_bureau_qrt',\n",
    "    'amt_req_credit_bureau_year'\n",
    "]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "e0c63c00-7c63-40e0-afd7-13d2f236a5bd",
   "metadata": {},
   "outputs": [],
   "source": [
    "#отберем часть столбцов для дальнейшей работы\n",
    "df = df[need_cols]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "634b9b91-9f1b-4c9a-b57d-e976df90f4f2",
   "metadata": {},
   "outputs": [],
   "source": [
    "#небольшой препроцессинг с прошлого занятия\n",
    "cols_to_preprocess = ['days_birth', 'days_employed','days_registration']\n",
    "\n",
    "for x in cols_to_preprocess:\n",
    "    df[f'{x}_years'] = np.abs(df[x])/365.25\n",
    "    \n",
    "df['age_bins'] = pd.cut(\n",
    "    df['days_birth_years'], \n",
    "    bins = [-np.inf, 23, 25, 30, 35, 40, 45, 50, np.inf],\n",
    "    labels = ['0.0-23', '1.23-25', '2.25-30', '3.30-35', '4.35-40', '5.40-45', '6.45-50', '7.50+']\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "934dadf3-cc21-41ec-b24a-74db22179946",
   "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>sk_id_curr</th>\n",
       "      <th>target</th>\n",
       "      <th>name_contract_type</th>\n",
       "      <th>code_gender</th>\n",
       "      <th>flag_own_car</th>\n",
       "      <th>flag_own_realty</th>\n",
       "      <th>cnt_children</th>\n",
       "      <th>amt_income_total</th>\n",
       "      <th>amt_credit</th>\n",
       "      <th>amt_annuity</th>\n",
       "      <th>amt_goods_price</th>\n",
       "      <th>name_type_suite</th>\n",
       "      <th>name_income_type</th>\n",
       "      <th>name_education_type</th>\n",
       "      <th>name_family_status</th>\n",
       "      <th>name_housing_type</th>\n",
       "      <th>region_population_relative</th>\n",
       "      <th>days_birth</th>\n",
       "      <th>days_employed</th>\n",
       "      <th>days_registration</th>\n",
       "      <th>days_id_publish</th>\n",
       "      <th>own_car_age</th>\n",
       "      <th>flag_mobil</th>\n",
       "      <th>flag_emp_phone</th>\n",
       "      <th>flag_work_phone</th>\n",
       "      <th>flag_cont_mobile</th>\n",
       "      <th>flag_phone</th>\n",
       "      <th>flag_email</th>\n",
       "      <th>occupation_type</th>\n",
       "      <th>cnt_fam_members</th>\n",
       "      <th>amt_req_credit_bureau_hour</th>\n",
       "      <th>amt_req_credit_bureau_day</th>\n",
       "      <th>amt_req_credit_bureau_week</th>\n",
       "      <th>amt_req_credit_bureau_mon</th>\n",
       "      <th>amt_req_credit_bureau_qrt</th>\n",
       "      <th>amt_req_credit_bureau_year</th>\n",
       "      <th>days_birth_years</th>\n",
       "      <th>days_employed_years</th>\n",
       "      <th>days_registration_years</th>\n",
       "      <th>age_bins</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>163465</td>\n",
       "      <td>0</td>\n",
       "      <td>Cash loans</td>\n",
       "      <td>F</td>\n",
       "      <td>N</td>\n",
       "      <td>Y</td>\n",
       "      <td>0</td>\n",
       "      <td>54000.0</td>\n",
       "      <td>50940.0</td>\n",
       "      <td>5166.0</td>\n",
       "      <td>45000.0</td>\n",
       "      <td>Unaccompanied</td>\n",
       "      <td>State servant</td>\n",
       "      <td>Secondary / secondary special</td>\n",
       "      <td>Married</td>\n",
       "      <td>House / apartment</td>\n",
       "      <td>0.031329</td>\n",
       "      <td>-18735</td>\n",
       "      <td>-5347</td>\n",
       "      <td>-6359.0</td>\n",
       "      <td>-2115</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>Cooking staff</td>\n",
       "      <td>2.0</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.0</td>\n",
       "      <td>51.293634</td>\n",
       "      <td>14.639288</td>\n",
       "      <td>17.409993</td>\n",
       "      <td>7.50+</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>256730</td>\n",
       "      <td>0</td>\n",
       "      <td>Cash loans</td>\n",
       "      <td>M</td>\n",
       "      <td>N</td>\n",
       "      <td>N</td>\n",
       "      <td>0</td>\n",
       "      <td>58500.0</td>\n",
       "      <td>269550.0</td>\n",
       "      <td>10116.0</td>\n",
       "      <td>225000.0</td>\n",
       "      <td>Unaccompanied</td>\n",
       "      <td>Pensioner</td>\n",
       "      <td>Secondary / secondary special</td>\n",
       "      <td>Separated</td>\n",
       "      <td>House / apartment</td>\n",
       "      <td>0.020246</td>\n",
       "      <td>-23152</td>\n",
       "      <td>365243</td>\n",
       "      <td>-15551.0</td>\n",
       "      <td>-4795</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1.0</td>\n",
       "      <td>2.0</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>63.386721</td>\n",
       "      <td>999.980835</td>\n",
       "      <td>42.576318</td>\n",
       "      <td>7.50+</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>208600</td>\n",
       "      <td>0</td>\n",
       "      <td>Revolving loans</td>\n",
       "      <td>M</td>\n",
       "      <td>N</td>\n",
       "      <td>Y</td>\n",
       "      <td>0</td>\n",
       "      <td>126000.0</td>\n",
       "      <td>180000.0</td>\n",
       "      <td>9000.0</td>\n",
       "      <td>180000.0</td>\n",
       "      <td>Unaccompanied</td>\n",
       "      <td>Commercial associate</td>\n",
       "      <td>Higher education</td>\n",
       "      <td>Single / not married</td>\n",
       "      <td>House / apartment</td>\n",
       "      <td>0.019689</td>\n",
       "      <td>-9172</td>\n",
       "      <td>-2229</td>\n",
       "      <td>-1060.0</td>\n",
       "      <td>-1850</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>Managers</td>\n",
       "      <td>1.0</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.0</td>\n",
       "      <td>25.111567</td>\n",
       "      <td>6.102669</td>\n",
       "      <td>2.902122</td>\n",
       "      <td>2.25-30</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>444689</td>\n",
       "      <td>0</td>\n",
       "      <td>Cash loans</td>\n",
       "      <td>M</td>\n",
       "      <td>Y</td>\n",
       "      <td>Y</td>\n",
       "      <td>0</td>\n",
       "      <td>126000.0</td>\n",
       "      <td>139230.0</td>\n",
       "      <td>10903.5</td>\n",
       "      <td>112500.0</td>\n",
       "      <td>Other_B</td>\n",
       "      <td>Pensioner</td>\n",
       "      <td>Secondary / secondary special</td>\n",
       "      <td>Married</td>\n",
       "      <td>House / apartment</td>\n",
       "      <td>0.007020</td>\n",
       "      <td>-23445</td>\n",
       "      <td>365243</td>\n",
       "      <td>-4303.0</td>\n",
       "      <td>-4765</td>\n",
       "      <td>3.0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>6.0</td>\n",
       "      <td>64.188912</td>\n",
       "      <td>999.980835</td>\n",
       "      <td>11.780972</td>\n",
       "      <td>7.50+</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>140507</td>\n",
       "      <td>0</td>\n",
       "      <td>Cash loans</td>\n",
       "      <td>F</td>\n",
       "      <td>N</td>\n",
       "      <td>N</td>\n",
       "      <td>0</td>\n",
       "      <td>225000.0</td>\n",
       "      <td>1800000.0</td>\n",
       "      <td>49500.0</td>\n",
       "      <td>1800000.0</td>\n",
       "      <td>Unaccompanied</td>\n",
       "      <td>State servant</td>\n",
       "      <td>Higher education</td>\n",
       "      <td>Married</td>\n",
       "      <td>House / apartment</td>\n",
       "      <td>0.035792</td>\n",
       "      <td>-19622</td>\n",
       "      <td>-11059</td>\n",
       "      <td>-3538.0</td>\n",
       "      <td>-3083</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>Core staff</td>\n",
       "      <td>2.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.0</td>\n",
       "      <td>8.0</td>\n",
       "      <td>53.722108</td>\n",
       "      <td>30.277892</td>\n",
       "      <td>9.686516</td>\n",
       "      <td>7.50+</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   sk_id_curr  target name_contract_type code_gender flag_own_car  \\\n",
       "0      163465       0         Cash loans           F            N   \n",
       "1      256730       0         Cash loans           M            N   \n",
       "2      208600       0    Revolving loans           M            N   \n",
       "3      444689       0         Cash loans           M            Y   \n",
       "4      140507       0         Cash loans           F            N   \n",
       "\n",
       "  flag_own_realty  cnt_children  amt_income_total  amt_credit  amt_annuity  \\\n",
       "0               Y             0           54000.0     50940.0       5166.0   \n",
       "1               N             0           58500.0    269550.0      10116.0   \n",
       "2               Y             0          126000.0    180000.0       9000.0   \n",
       "3               Y             0          126000.0    139230.0      10903.5   \n",
       "4               N             0          225000.0   1800000.0      49500.0   \n",
       "\n",
       "   amt_goods_price name_type_suite      name_income_type  \\\n",
       "0          45000.0   Unaccompanied         State servant   \n",
       "1         225000.0   Unaccompanied             Pensioner   \n",
       "2         180000.0   Unaccompanied  Commercial associate   \n",
       "3         112500.0         Other_B             Pensioner   \n",
       "4        1800000.0   Unaccompanied         State servant   \n",
       "\n",
       "             name_education_type    name_family_status  name_housing_type  \\\n",
       "0  Secondary / secondary special               Married  House / apartment   \n",
       "1  Secondary / secondary special             Separated  House / apartment   \n",
       "2               Higher education  Single / not married  House / apartment   \n",
       "3  Secondary / secondary special               Married  House / apartment   \n",
       "4               Higher education               Married  House / apartment   \n",
       "\n",
       "   region_population_relative  days_birth  days_employed  days_registration  \\\n",
       "0                    0.031329      -18735          -5347            -6359.0   \n",
       "1                    0.020246      -23152         365243           -15551.0   \n",
       "2                    0.019689       -9172          -2229            -1060.0   \n",
       "3                    0.007020      -23445         365243            -4303.0   \n",
       "4                    0.035792      -19622         -11059            -3538.0   \n",
       "\n",
       "   days_id_publish  own_car_age  flag_mobil  flag_emp_phone  flag_work_phone  \\\n",
       "0            -2115          NaN           1               1                0   \n",
       "1            -4795          NaN           1               0                0   \n",
       "2            -1850          NaN           1               1                1   \n",
       "3            -4765          3.0           1               0                0   \n",
       "4            -3083          NaN           1               1                0   \n",
       "\n",
       "   flag_cont_mobile  flag_phone  flag_email occupation_type  cnt_fam_members  \\\n",
       "0                 1           0           0   Cooking staff              2.0   \n",
       "1                 1           0           0             NaN              1.0   \n",
       "2                 1           1           0        Managers              1.0   \n",
       "3                 1           0           0             NaN              2.0   \n",
       "4                 1           0           0      Core staff              2.0   \n",
       "\n",
       "   amt_req_credit_bureau_hour  amt_req_credit_bureau_day  \\\n",
       "0                         0.0                        0.0   \n",
       "1                         2.0                        0.0   \n",
       "2                         0.0                        0.0   \n",
       "3                         0.0                        0.0   \n",
       "4                         0.0                        0.0   \n",
       "\n",
       "   amt_req_credit_bureau_week  amt_req_credit_bureau_mon  \\\n",
       "0                         0.0                        0.0   \n",
       "1                         0.0                        0.0   \n",
       "2                         0.0                        0.0   \n",
       "3                         0.0                        1.0   \n",
       "4                         0.0                        0.0   \n",
       "\n",
       "   amt_req_credit_bureau_qrt  amt_req_credit_bureau_year  days_birth_years  \\\n",
       "0                        0.0                         2.0         51.293634   \n",
       "1                        0.0                         0.0         63.386721   \n",
       "2                        0.0                         2.0         25.111567   \n",
       "3                        0.0                         6.0         64.188912   \n",
       "4                        2.0                         8.0         53.722108   \n",
       "\n",
       "   days_employed_years  days_registration_years age_bins  \n",
       "0            14.639288                17.409993    7.50+  \n",
       "1           999.980835                42.576318    7.50+  \n",
       "2             6.102669                 2.902122  2.25-30  \n",
       "3           999.980835                11.780972    7.50+  \n",
       "4            30.277892                 9.686516    7.50+  "
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "49359a75-91ba-4157-b1f1-63a52c2e017c",
   "metadata": {},
   "source": [
    "### Bureau data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "1673b3b6-3a2c-46da-9fb6-951e8993c84c",
   "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>SK_ID_CURR</th>\n",
       "      <th>SK_ID_BUREAU</th>\n",
       "      <th>CREDIT_ACTIVE</th>\n",
       "      <th>CREDIT_CURRENCY</th>\n",
       "      <th>DAYS_CREDIT</th>\n",
       "      <th>CREDIT_DAY_OVERDUE</th>\n",
       "      <th>DAYS_CREDIT_ENDDATE</th>\n",
       "      <th>DAYS_ENDDATE_FACT</th>\n",
       "      <th>AMT_CREDIT_MAX_OVERDUE</th>\n",
       "      <th>CNT_CREDIT_PROLONG</th>\n",
       "      <th>AMT_CREDIT_SUM</th>\n",
       "      <th>AMT_CREDIT_SUM_DEBT</th>\n",
       "      <th>AMT_CREDIT_SUM_LIMIT</th>\n",
       "      <th>AMT_CREDIT_SUM_OVERDUE</th>\n",
       "      <th>CREDIT_TYPE</th>\n",
       "      <th>DAYS_CREDIT_UPDATE</th>\n",
       "      <th>AMT_ANNUITY</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>426155</td>\n",
       "      <td>5714498</td>\n",
       "      <td>Closed</td>\n",
       "      <td>currency 1</td>\n",
       "      <td>-1331</td>\n",
       "      <td>0</td>\n",
       "      <td>-994.0</td>\n",
       "      <td>-1023.0</td>\n",
       "      <td>1350.0</td>\n",
       "      <td>0</td>\n",
       "      <td>39433.5</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>Consumer credit</td>\n",
       "      <td>-1023</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>426155</td>\n",
       "      <td>5714499</td>\n",
       "      <td>Closed</td>\n",
       "      <td>currency 1</td>\n",
       "      <td>-2534</td>\n",
       "      <td>0</td>\n",
       "      <td>-2352.0</td>\n",
       "      <td>-2347.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "      <td>38830.5</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>Consumer credit</td>\n",
       "      <td>-2345</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>426155</td>\n",
       "      <td>5714500</td>\n",
       "      <td>Closed</td>\n",
       "      <td>currency 1</td>\n",
       "      <td>-845</td>\n",
       "      <td>0</td>\n",
       "      <td>-480.0</td>\n",
       "      <td>-480.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0</td>\n",
       "      <td>67500.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>Consumer credit</td>\n",
       "      <td>-480</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   SK_ID_CURR  SK_ID_BUREAU CREDIT_ACTIVE CREDIT_CURRENCY  DAYS_CREDIT  \\\n",
       "0      426155       5714498        Closed      currency 1        -1331   \n",
       "1      426155       5714499        Closed      currency 1        -2534   \n",
       "2      426155       5714500        Closed      currency 1         -845   \n",
       "\n",
       "   CREDIT_DAY_OVERDUE  DAYS_CREDIT_ENDDATE  DAYS_ENDDATE_FACT  \\\n",
       "0                   0               -994.0            -1023.0   \n",
       "1                   0              -2352.0            -2347.0   \n",
       "2                   0               -480.0             -480.0   \n",
       "\n",
       "   AMT_CREDIT_MAX_OVERDUE  CNT_CREDIT_PROLONG  AMT_CREDIT_SUM  \\\n",
       "0                  1350.0                   0         39433.5   \n",
       "1                     NaN                   0         38830.5   \n",
       "2                     0.0                   0         67500.0   \n",
       "\n",
       "   AMT_CREDIT_SUM_DEBT  AMT_CREDIT_SUM_LIMIT  AMT_CREDIT_SUM_OVERDUE  \\\n",
       "0                  0.0                   0.0                     0.0   \n",
       "1                  0.0                   0.0                     0.0   \n",
       "2                  0.0                   0.0                     0.0   \n",
       "\n",
       "       CREDIT_TYPE  DAYS_CREDIT_UPDATE  AMT_ANNUITY  \n",
       "0  Consumer credit               -1023          NaN  \n",
       "1  Consumer credit               -2345          NaN  \n",
       "2  Consumer credit                -480          NaN  "
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "db = pd.read_csv(bureau_data_path)\n",
    "db.head(3)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "id": "db0171df-56d1-483b-94de-2b526a324825",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(147256, 17)"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "db.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "id": "9b1a2c48-86e7-48cb-b4a0-7d6f0db49b6f",
   "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>sk_id_curr</th>\n",
       "      <th>sk_id_bureau</th>\n",
       "      <th>credit_active</th>\n",
       "      <th>credit_currency</th>\n",
       "      <th>days_credit</th>\n",
       "      <th>credit_day_overdue</th>\n",
       "      <th>days_credit_enddate</th>\n",
       "      <th>days_enddate_fact</th>\n",
       "      <th>amt_credit_max_overdue</th>\n",
       "      <th>cnt_credit_prolong</th>\n",
       "      <th>amt_credit_sum</th>\n",
       "      <th>amt_credit_sum_debt</th>\n",
       "      <th>amt_credit_sum_limit</th>\n",
       "      <th>amt_credit_sum_overdue</th>\n",
       "      <th>credit_type</th>\n",
       "      <th>days_credit_update</th>\n",
       "      <th>amt_annuity</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>426155</td>\n",
       "      <td>5714498</td>\n",
       "      <td>Closed</td>\n",
       "      <td>currency 1</td>\n",
       "      <td>-1331</td>\n",
       "      <td>0</td>\n",
       "      <td>-994.0</td>\n",
       "      <td>-1023.0</td>\n",
       "      <td>1350.0</td>\n",
       "      <td>0</td>\n",
       "      <td>39433.5</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>Consumer credit</td>\n",
       "      <td>-1023</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>426155</td>\n",
       "      <td>5714499</td>\n",
       "      <td>Closed</td>\n",
       "      <td>currency 1</td>\n",
       "      <td>-2534</td>\n",
       "      <td>0</td>\n",
       "      <td>-2352.0</td>\n",
       "      <td>-2347.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "      <td>38830.5</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>Consumer credit</td>\n",
       "      <td>-2345</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   sk_id_curr  sk_id_bureau credit_active credit_currency  days_credit  \\\n",
       "0      426155       5714498        Closed      currency 1        -1331   \n",
       "1      426155       5714499        Closed      currency 1        -2534   \n",
       "\n",
       "   credit_day_overdue  days_credit_enddate  days_enddate_fact  \\\n",
       "0                   0               -994.0            -1023.0   \n",
       "1                   0              -2352.0            -2347.0   \n",
       "\n",
       "   amt_credit_max_overdue  cnt_credit_prolong  amt_credit_sum  \\\n",
       "0                  1350.0                   0         39433.5   \n",
       "1                     NaN                   0         38830.5   \n",
       "\n",
       "   amt_credit_sum_debt  amt_credit_sum_limit  amt_credit_sum_overdue  \\\n",
       "0                  0.0                   0.0                     0.0   \n",
       "1                  0.0                   0.0                     0.0   \n",
       "\n",
       "       credit_type  days_credit_update  amt_annuity  \n",
       "0  Consumer credit               -1023          NaN  \n",
       "1  Consumer credit               -2345          NaN  "
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "db = db.rename(columns = lambda x: x.lower())\n",
    "db.head(2)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "id": "016bf863-8c49-4538-bb4b-a8da721f89d3",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(30751, 40)"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "id": "9d1c2a86-daf6-4fdf-a82f-e3eb19221b51",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(26344, 30751)"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#посмотрим, сколько различных заявок (sk_id_curr) и сколько различных идентификаторов записи бюро sk_id_bureau\n",
    "db.sk_id_curr.nunique(), df.sk_id_curr.nunique()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "id": "f28cad0c-91ab-4d3c-97cb-29e8f8890e70",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "147256"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "db.sk_id_bureau.nunique()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "id": "eb5d41ce-0ca8-4154-96ab-8a354fc6c00c",
   "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>sk_id_curr</th>\n",
       "      <th>sk_id_bureau</th>\n",
       "      <th>days_credit</th>\n",
       "      <th>credit_day_overdue</th>\n",
       "      <th>days_credit_enddate</th>\n",
       "      <th>days_enddate_fact</th>\n",
       "      <th>amt_credit_max_overdue</th>\n",
       "      <th>cnt_credit_prolong</th>\n",
       "      <th>amt_credit_sum</th>\n",
       "      <th>amt_credit_sum_debt</th>\n",
       "      <th>amt_credit_sum_limit</th>\n",
       "      <th>amt_credit_sum_overdue</th>\n",
       "      <th>days_credit_update</th>\n",
       "      <th>amt_annuity</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>count</th>\n",
       "      <td>147256.000000</td>\n",
       "      <td>1.472560e+05</td>\n",
       "      <td>147256.000000</td>\n",
       "      <td>147256.000000</td>\n",
       "      <td>138380.000000</td>\n",
       "      <td>92782.000000</td>\n",
       "      <td>5.161700e+04</td>\n",
       "      <td>147256.000000</td>\n",
       "      <td>1.472560e+05</td>\n",
       "      <td>1.248210e+05</td>\n",
       "      <td>9.786800e+04</td>\n",
       "      <td>1.472560e+05</td>\n",
       "      <td>147256.000000</td>\n",
       "      <td>3.341800e+04</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>mean</th>\n",
       "      <td>279260.739569</td>\n",
       "      <td>5.891754e+06</td>\n",
       "      <td>-1142.309305</td>\n",
       "      <td>0.871998</td>\n",
       "      <td>478.211938</td>\n",
       "      <td>-1021.860867</td>\n",
       "      <td>3.250696e+03</td>\n",
       "      <td>0.007001</td>\n",
       "      <td>3.481388e+05</td>\n",
       "      <td>1.367120e+05</td>\n",
       "      <td>6.387555e+03</td>\n",
       "      <td>6.265304e+01</td>\n",
       "      <td>-602.829895</td>\n",
       "      <td>1.309053e+04</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>std</th>\n",
       "      <td>103147.581606</td>\n",
       "      <td>5.396515e+05</td>\n",
       "      <td>798.009197</td>\n",
       "      <td>37.730225</td>\n",
       "      <td>4953.708842</td>\n",
       "      <td>715.115883</td>\n",
       "      <td>5.988816e+04</td>\n",
       "      <td>0.100583</td>\n",
       "      <td>8.916177e+05</td>\n",
       "      <td>6.431245e+05</td>\n",
       "      <td>4.858185e+04</td>\n",
       "      <td>1.036017e+04</td>\n",
       "      <td>814.276324</td>\n",
       "      <td>5.815073e+04</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>min</th>\n",
       "      <td>100003.000000</td>\n",
       "      <td>5.000088e+06</td>\n",
       "      <td>-2922.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>-42042.000000</td>\n",
       "      <td>-2903.000000</td>\n",
       "      <td>0.000000e+00</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000e+00</td>\n",
       "      <td>-2.796724e+06</td>\n",
       "      <td>-4.013469e+05</td>\n",
       "      <td>0.000000e+00</td>\n",
       "      <td>-41943.000000</td>\n",
       "      <td>0.000000e+00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25%</th>\n",
       "      <td>189202.500000</td>\n",
       "      <td>5.412590e+06</td>\n",
       "      <td>-1667.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>-1142.000000</td>\n",
       "      <td>-1498.000000</td>\n",
       "      <td>0.000000e+00</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>5.080500e+04</td>\n",
       "      <td>0.000000e+00</td>\n",
       "      <td>0.000000e+00</td>\n",
       "      <td>0.000000e+00</td>\n",
       "      <td>-903.000000</td>\n",
       "      <td>0.000000e+00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>50%</th>\n",
       "      <td>279673.000000</td>\n",
       "      <td>5.860460e+06</td>\n",
       "      <td>-986.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>-337.000000</td>\n",
       "      <td>-900.000000</td>\n",
       "      <td>0.000000e+00</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>1.231200e+05</td>\n",
       "      <td>0.000000e+00</td>\n",
       "      <td>0.000000e+00</td>\n",
       "      <td>0.000000e+00</td>\n",
       "      <td>-408.000000</td>\n",
       "      <td>0.000000e+00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>75%</th>\n",
       "      <td>369288.000000</td>\n",
       "      <td>6.369210e+06</td>\n",
       "      <td>-471.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>458.000000</td>\n",
       "      <td>-430.000000</td>\n",
       "      <td>0.000000e+00</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>3.060000e+05</td>\n",
       "      <td>3.947850e+04</td>\n",
       "      <td>0.000000e+00</td>\n",
       "      <td>0.000000e+00</td>\n",
       "      <td>-33.000000</td>\n",
       "      <td>1.350450e+04</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>max</th>\n",
       "      <td>456239.000000</td>\n",
       "      <td>6.843330e+06</td>\n",
       "      <td>-1.000000</td>\n",
       "      <td>2754.000000</td>\n",
       "      <td>31198.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>1.086181e+07</td>\n",
       "      <td>5.000000</td>\n",
       "      <td>5.400000e+07</td>\n",
       "      <td>5.136516e+07</td>\n",
       "      <td>4.500000e+06</td>\n",
       "      <td>3.756681e+06</td>\n",
       "      <td>19.000000</td>\n",
       "      <td>4.174677e+06</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "          sk_id_curr  sk_id_bureau    days_credit  credit_day_overdue  \\\n",
       "count  147256.000000  1.472560e+05  147256.000000       147256.000000   \n",
       "mean   279260.739569  5.891754e+06   -1142.309305            0.871998   \n",
       "std    103147.581606  5.396515e+05     798.009197           37.730225   \n",
       "min    100003.000000  5.000088e+06   -2922.000000            0.000000   \n",
       "25%    189202.500000  5.412590e+06   -1667.000000            0.000000   \n",
       "50%    279673.000000  5.860460e+06    -986.000000            0.000000   \n",
       "75%    369288.000000  6.369210e+06    -471.000000            0.000000   \n",
       "max    456239.000000  6.843330e+06      -1.000000         2754.000000   \n",
       "\n",
       "       days_credit_enddate  days_enddate_fact  amt_credit_max_overdue  \\\n",
       "count        138380.000000       92782.000000            5.161700e+04   \n",
       "mean            478.211938       -1021.860867            3.250696e+03   \n",
       "std            4953.708842         715.115883            5.988816e+04   \n",
       "min          -42042.000000       -2903.000000            0.000000e+00   \n",
       "25%           -1142.000000       -1498.000000            0.000000e+00   \n",
       "50%            -337.000000        -900.000000            0.000000e+00   \n",
       "75%             458.000000        -430.000000            0.000000e+00   \n",
       "max           31198.000000           0.000000            1.086181e+07   \n",
       "\n",
       "       cnt_credit_prolong  amt_credit_sum  amt_credit_sum_debt  \\\n",
       "count       147256.000000    1.472560e+05         1.248210e+05   \n",
       "mean             0.007001    3.481388e+05         1.367120e+05   \n",
       "std              0.100583    8.916177e+05         6.431245e+05   \n",
       "min              0.000000    0.000000e+00        -2.796724e+06   \n",
       "25%              0.000000    5.080500e+04         0.000000e+00   \n",
       "50%              0.000000    1.231200e+05         0.000000e+00   \n",
       "75%              0.000000    3.060000e+05         3.947850e+04   \n",
       "max              5.000000    5.400000e+07         5.136516e+07   \n",
       "\n",
       "       amt_credit_sum_limit  amt_credit_sum_overdue  days_credit_update  \\\n",
       "count          9.786800e+04            1.472560e+05       147256.000000   \n",
       "mean           6.387555e+03            6.265304e+01         -602.829895   \n",
       "std            4.858185e+04            1.036017e+04          814.276324   \n",
       "min           -4.013469e+05            0.000000e+00       -41943.000000   \n",
       "25%            0.000000e+00            0.000000e+00         -903.000000   \n",
       "50%            0.000000e+00            0.000000e+00         -408.000000   \n",
       "75%            0.000000e+00            0.000000e+00          -33.000000   \n",
       "max            4.500000e+06            3.756681e+06           19.000000   \n",
       "\n",
       "        amt_annuity  \n",
       "count  3.341800e+04  \n",
       "mean   1.309053e+04  \n",
       "std    5.815073e+04  \n",
       "min    0.000000e+00  \n",
       "25%    0.000000e+00  \n",
       "50%    0.000000e+00  \n",
       "75%    1.350450e+04  \n",
       "max    4.174677e+06  "
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#посмотрим основные статистики по датасету\n",
    "db.describe()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "id": "7b9a24e2-2286-4311-b027-c433a0735b3f",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "sk_id_curr                  int64\n",
       "sk_id_bureau                int64\n",
       "credit_active              object\n",
       "credit_currency            object\n",
       "days_credit                 int64\n",
       "credit_day_overdue          int64\n",
       "days_credit_enddate       float64\n",
       "days_enddate_fact         float64\n",
       "amt_credit_max_overdue    float64\n",
       "cnt_credit_prolong          int64\n",
       "amt_credit_sum            float64\n",
       "amt_credit_sum_debt       float64\n",
       "amt_credit_sum_limit      float64\n",
       "amt_credit_sum_overdue    float64\n",
       "credit_type                object\n",
       "days_credit_update          int64\n",
       "amt_annuity               float64\n",
       "dtype: object"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "db.dtypes"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "id": "08620c9a-8732-4ae2-8f15-8a33ba9539d3",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "['credit_active', 'credit_currency', 'credit_type']"
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "cat_cols = [x for x in db.columns if db[x].dtype=='object']\n",
    "cat_cols"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "id": "71c5e343-ca70-406f-a9bc-faeeb6d3731c",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Closed      92515\n",
      "Active      54197\n",
      "Sold          543\n",
      "Bad debt        1\n",
      "Name: credit_active, dtype: int64\n",
      "currency 1    147141\n",
      "currency 2        98\n",
      "currency 3        15\n",
      "currency 4         2\n",
      "Name: credit_currency, dtype: int64\n",
      "Consumer credit                           107826\n",
      "Credit card                                34278\n",
      "Car loan                                    2344\n",
      "Mortgage                                    1533\n",
      "Microloan                                    948\n",
      "Loan for business development                155\n",
      "Another type of loan                          85\n",
      "Unknown type of loan                          50\n",
      "Loan for working capital replenishment        33\n",
      "Real estate loan                               3\n",
      "Cash loan (non-earmarked)                      1\n",
      "Name: credit_type, dtype: int64\n"
     ]
    }
   ],
   "source": [
    "for x in cat_cols:\n",
    "    print(db[x].value_counts(dropna=False))"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "f4c644f9-ea40-439a-9fc5-83fe5f48f3a5",
   "metadata": {},
   "source": [
    "### Как объединить два датафрейма?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "id": "5e17940d-fa9a-48b7-b8db-8688a285fac6",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "image/png": "iVBORw0KGgoAAAANSUhEUgAAAbgAAAFACAMAAAD51Zn1AAAAllBMVEX/////kh4AAAAzMzNmZmbMzMyZmZlAQEC/v7/v7+8QEBDf39+fn59gYGAQCQIwMDBAJQggICDviRyAgICASQ9mSy7MeiO/bheZYymfWxNgNwvPdxivr6+Pj49wcHBQUFAgEgTfgBowGwZwQA1QLgmPUhHl5eWvZBXZgCKzbiaAVyuMXSpAOTJNPzBZRS+/dCVzUSymaCftun1mAAAgAElEQVR4nO1dh3ajOhC16dUN91CM4zi9/f/PvRmBbYwpAo2dZB/3nN04CRFirqZJQtPrdejQoUOHDh06dOjQoUOH/wn22h7+2fhR9Xs9zUh+2Ov5GqLXsx1NU4+Xs497RwkdEz748Be+k/xif/u+/6+hyir8c/GjpvR6sr5nP+z1lCghDr9GkZleLiOluqcaig5kK/AXisy4ltWyO/wz2Ktm/UU3Q0JcBHwkxEVKLyVOSS7Ar2YUpJcDcXud6VgIZDLiIh2f528SJyMUJoRe8qg4Mnunpz99o7qRoiv2QWLsMk25aPFmSLrhy3ZKnC87l8SdngM67EXs0172E+K0KOz9WeISmjiIU1EupgcGKZEYCuwXENdT3JQ4VWN9y2mc7qeXw3NFXvIRvibEqWgs/3Xi0qeGQZpIzEPv8guIs6HDCXFmpCTEJVYk8XEHS4nPlT4aPk5CXC8EY/mPE5de0dPkVGJ7XfsVxPVA0RLi4DvjTOMiLTgqXDFxph78VeI8VVX3HMQBYQxwZSoxDYzlbyCu5yopcb1AN3Km0tEzUaUSnj4mxPUMWf2jxEWKovitiAOB/Q7ibNlLiTMjL+/j5KOPc3q+zFI2/HIgrhcq0d8kjtdU+nIydOGpDxKzZedXEAeKlhLXM/R8VOmmAYmDpLku/KeCeTwRt9f1v0zcPslF8SlLiNunQxdC6qPEND34FcRhVJK6qvAQnMC37BECTAFsV8GYGIJiWXFZMnckDhj9y8T1WEZjIzlFxGFs5rE5CLzkJLFI+UHimsA+Th2o9o92hAwH4gyIUnwdzYqssAmjZN4IfYKhemhmTDfyIVUKskNdlf8Icf8ejrOwdqB4zBhqyRRtMlOLxHmKl4xS31MCNlPLZneTH/kFbXbo0KFDhw4dOnTo0KFDhw4d/gUYRuAd4BitphpNwzg24fnGPzIP9nthO6FuSXmMdc/n38akQhsXTUjDyDOu2HFi2AZAcOeWim3QdKcapu+OUcSW7oKeHRB4bkLlMOSYfjOdyEqpBj07wPOihErd++UrBUYQ6tlRZ+mR5zfrs2l4oT48G/au51/P5DhRItnCcWb7Hj6O5VZyZzrYXysKCseZ6rBxMQ5/KXem46bi3o5GuylgNBptpHTA8amOnTwjAP52DU3cw9dBMgSi4AoPbrsWirSaFp9d5JWNHcNFtaxWKZsNj6Hzm7aPMtgBI217/7CY9M8QL6a7Tf2gBagha2M0XS3Om+jfPU4ZfePaNprB0LFfPGPKR3LcIupQ2cYhh0FgammV0v8TSCzF9j4v7wx7q/WgWkZmgKo2ergrbePuYSdxyogPSNuYWwdMb1xAnQM/1LlHE9Nv97donR1Cb7YPcanEUyyQu6FT2IaKA3q3mtQ0MVkhdzpJxGIDbXpxb8qALJ2J3cAfNDLgpmeB1v0G6mwQ+WBdridnWI3QV1x0G4f+ZlrLfMLdwwb1RLTbZgitNLe7QJ113Ahrg9vSG+s/UtfizsRA2jbTOkXJIF5fiF0F2rYr/ib6C6RfTOuMLAFNgGIfJjoWWC17gUKLflbp4CkGTUR+pO70wCY8xbbcOZZT13ysn+4ZCkgOTayHrw6wL+2A4+YHlQ5uP2iibUfqRidX4VjSpin1jLpNe7mpQzGxgarpzlHxWgGHTijQBRHgvXdxC5knYmeiw2HbhnrEw6Cl6EDmgobKxiC6tbolMID5H8kMYNgOHtuJHDC5R6XzLWnLGdYUABW3hZ8KW/3VGUxMpkXjIxi01g9MpTigbi1VJQEoHcTSU5EmUOka644rWaLZhDmE7EecfuyKcHTc/J7Sg5DMQWEkUd4gJ982NJcwzMeiwxx4G036dwPJFWwIR/9tmYPHH7Q3cUeZQ3zRJi7JYrJrZG9A5kPROBx8xJo9wN9jDk2FkJlMHnvdXw2Eda5/3+DZKXgzU97+IHNkvB2/CGHF/+wuJW+s98Ih/Q2Zy3a9LeCRV4cPt2OOgLfe2cND74Wl7twqtiTljX28F22Ok7lAsoQTJ1fa5h5EeMLbIegWByh4iweZkDRDYmsAc/Wj1iAY2kHeSaxIBoO4IeC5i7B/m2zPuAfm2ifyKVb1pJiWuFkzLoPpqTQUbbWniwc5tfDEeevvcjr7KJ5bQGxZN2p18UDCHBcMsRFBuwRjqgY+gYynF9w/kIyGqLLnAYFmREXueELg5oxruznbErdqC2kQX0pd2G+C/a2agrIJHJwvbYrG10IaC7soT9JFm6iELh4BToo82mQjPiDAVVZQo4tO5jODVrxseE+wQDO8qrEMzmPhVtgVcn9XoIZN8VBhDB0CQxmWjdpJ5ZDhAxjL60WWYCiFHdxjsbkBx7cTbRrChDKtAmUR9kO2NChzxCsCQ+eKm4RS6OITi2ATS3YpbMWNJaSHJS7eI4i39Yp0cyQJ70OAsXWt+AR8s6hoQbHKopBFiSo2a7145FMIxah6eohPRNsnGVzFGJcpCz/iQam56a/F9bm/KbaIHoEZ0istwkg8triayjnSSFiwVeRUkcqLx0KVMy1xx1+pcKhy4sHPtVRuLMWico0rzeGUQOUKR35AIBG3ZkJ1JB79gMpdI7B0xHNkULiqp58QqNyiSOXG4jbIrvPvjzUTNzygsOiXIFG4QeXvaVTuYuT7JDKt69qGYnSIhzgX8Ak8XB0xMUHYurpkyRWP1WHY1hmDB/FNX72IoKeXbQqnWTAoa55+TXKT3Mg3JUv46dX66YGYIDxx6MOTWhvPgcdaL7kgmD6Z5h2FQzCTGHKs9Y4IonlLfIzlEBC4n119Hlirk/WI844iEo/3OCwlja2ksOrnGIqHJhMOpZ0K77LF8XE+40swijksJZWtJH4RxCZYFlhxLAnFBCHQ6vzhjXq/kR5JizAdrSCXCrjG07YmDVPrtckkIP8MfD2vxppnxozAVk7ObaVXPxmlysePSqgVXBBx2Zv7IjvnnPJKnvP4h8Q5eCS+ntMfVCdxh4cXjyu3Z1GCXh8znIjbYwWEyyoPFk/XIfgqyJ/Nk4M9EOcVjY0UIbGT43BPs+Xh02Qmv17+/o4rYlwU2tPPz+PH5ay2jemZjnG4OEacqoHIPFlTNTkvWZsv2J3kZ238vaoyM+xrqqoCcXu8hx8p5YUcfdrJE4NjuutE3DfINn7O//6Bz9oWOtPX00A4EPcclzaxyHo1m2ONE4kLXM0NSojzOUPqTW6MKKGiQtumG2qKq/W0KNR0rZo4nu42QMCRxpyIw0+XirHjs7ajGid3aFiel1+T9fAGxxAG4e51E+0k072L8hwe53pWPpNLSuP1HJzLiZA4LMh5qAJSAtpMjsfFAV2TF/ntub+UZZn9O//9RuJ6+Pu8kGbPsyWy9fkuz16XQNyL/P7Ux0IpFQLMCr3eaaBwdUVRsCZTr4A4vtgEbfR5xogMYflALfmsKWnVxyridKnil40x5BA6EPfy0n+S4xKN4wz0V3mLOnufY2tz+Qn+AXHw9WtWrXH3GQGGHOk31j1zwQupJRqn84056Pt5Cp4SFwY9fuJ4+ssPnixutpzIn/P5x3cxccVRxyUurpu9stZgULDBgQ3P5WrisnMYPCMYTaVsp58KiOOd7lvkzHJKnA9m2NY5ifMoieOaOJgt5/IM8FxM3CPvnFleM9PWmAvlJC4rQE7ier6sKGFCXBDl5q54ZwXucrl+SlzPAzvsnogz9IqEzqEMKw0eoc+WMZjJjKjPMeXdsDLIDe+0tVds8IOPuDiztGNxLHGZKvtPTT/lq1KZ3NM5uZDQ3qct7u2e4uP/ybd2xS5MnmCKGw5PJA8Cfv34nD8non5+n59Hh9zEjXIOJSUuflvOX95OxL0vn7gESBBec43b/H2zUHvMXHLejJA4rnD49RkzOPRI+Ak05FywdWF+5sLLdp+h1aeXr08gkX2c4b+CJP8owJOa/QLiTEWWCw+6LL7ZrYmrQZ6PUqzLU4/Je4WWnd/s2PdfQFwjmJQZ+E2JK7GpE8jmPl64b3bs+18jjqLDR7gEU8yixPXny+Vn4S8Kb0Yph79LHG8CWi1Lzgu5o5jKm7WQg5nGevu8OyIg7tCmXTpJeboZ4VLq/4O4w+LOxbIZAXGHNitz7/RmhMEJ71xdtSw5L6QmrmZJ+XQmeSlxnKs6/Txx+5Pu/BBxDYKTNAl4upiqJCDu+fXsHhXIEFczc6LKDljIPVaEBuJYZsyEvFczeRdvAh6f7ek0HRkz+UObLKtnxFXl37QLcnXETeLjx3RCY34xdc9NXH554MQSz4rO4WbHvtcQp8iu0gsURTeAOE9xoz0jLoiUzMHBvBtuzucqbVdWgp4CbUObrqJESV13U3HdivPreVYzuFFH3GkpjoC43IWZuTN+4ganBNytecsXZ5RNXJZG5ev1wpBpR2T2tJOh41kcQeT2LjDbC217Xk+T7Z4ZOWyNANpVyrfBkE4y12wVen17nz3Fr7MXUA7582W2nDDiJsvZy2m25GKdrQznxD2/v82eJ9+zr08k7nP2NU+Ie5598U151QmCLQX4niunGxh0JM5TNM077SDSOWd9cutxCXFOCKrGzC9QBsRFnqYp5bPMOuVuoZqw6unjZT6Zf86X7yDU98/5xxcjbvYyfzmpC/8k89nGnPjlYx5Pvuefctxfvr3Mv+UnJG75Mf9+K5VmnCGuzmcgcW5oOAfiZBSyorBC8wfw+vjcjk5scK8HqncgTkHiZCBOKzeHY8qF1LplncRUTuYgUfkZ5CbDx/4T0DhJ1wv6uMjYblkntY9zXBiAFvsvr0jcG7T7cbGt5YCsr1FrXtUB4thSCxK3Z1qhKfnAkmuSvY/by87FJieRTqCwXQtoIIG4msCSds9JTViFxD3N3mdIHPofeQ7E4e4F+eSOeBdS73L7kpC4ycvbLF0YwP+weflyb8QJZ5vBa15dUkLf1ENNAeJcVwsgJAFZm66S1QqevVKIXOphgrapchC4OBIULYSQBFizdU8LSqecSfNv1N/KDiNx79/M+SBToGdA3PfH2TUTzpg6v+CKbL18HddQ+68vcI8nudLpZLcu1DkNEwjaa76p9fa+6eD2K9xwbPqak5nk4FsCX+SFbmugzpqx93u4TQ+LL2Ehvb2jldeA5Ni+2wQ1m4W+IMF6++4/I3EQj7zgDpH+BH7Sj08Xbbg2lV74QuT/66v/hBr3NoevbHBg2BMXN9DH+CYjGIotpnzRyZRA6JH4UTdZeNXb857e5P7z29vrDISKm7GSqPLpQ85ujN3xTb/k1+0mH6Bg7/IXbvB6ZbvIkLjJlyx/lLRwthyH0Ym48al5/mPXf917VrxGvgpTvuNECd7DuzufwCB4P5fr+SckNyJ+s5FAnAsu8vmuqkbuRTWOlwdqwfPywCPJC5TER7HpBNPM+V1AheDUy0qMzv1EQPLCYf27KGuSV82JD8zwSF445FiO3VK8QHlusijOMvDrDcFkQPECpfj5EPkWxV/O5nuxkeIFypzJGhJEavW28uK2LeDSH1rJ8xJ0DXheJeZ8pacSu7zJojjLoO5goQsD3QYUR1flEd7GVpJYyrzJopCHWjd/8EtPyyB6CbzOUdzRvwKOcAnCE71monlNYOXGtNl3grqOc6D+rK7qs774UHA0E0V4UnOUWUxwCo5/lQO1Kc5gu6/hZcL1mng1Ck9ho3D61WeZrUlOxKR8w+oIi+TUw8rfX+3cQxqVqwisY4KjsK+jcJjKXf/YQ/GxUXLGLsVJ1VUH5FIo3Pg6CofHYBKItUrlys9r5kfJEbs2iUaURk53BCUjgquVjKBQuV2Fyl1R4aiOQS9LiX7zicysaeFXCOLys/uv5+FY5wkMUWnhgQcCZYmuWHbguqdpV3HKi4rCDT5JAZzC+KS83AE/KLpXjqH49AnYwxK1HZE0Xq5WEYGxLBbAlqRsz3UikwQqgVI8lEzBPBCo865qnheMpbAjUouKsJVVqWgCgtp2lfAIpDsqNJZxqSby47Ha3Fyl0ibeVbz0m0d92uEFCIxlMUVb8cgkrivA5xGUIo3ybu5uIL5+eoPqtqokrhirgqoR9wSqvK01N674wDaH56uKEwIHp1pXOPw8D4egdNH6gqUVgfNc17MCUhcOUGwrm4oCb8ItqtcvkIpwxdd3ckWJmbkRXnrgKusMAQqFnE/MjQh0mKBPXCAoAz45r/wdExQCf+QpBM6kLiwlQzp2dy3uNSmsAPedhJk7q9l+oYDtGuQzNxTMOSlzk5E4b+qt9K3Hnl1Y0IvTqL0pbwlzotJ2LAyuoePivBGMo0Z3Exb16sAcEW/8oZ06JJH3+o6AN/+mvJEyFxPwthg0mncAWz8WzcTBwkniMvckgs0wjQDM7UTj99UAcm7QFYoh0DCcdsUFFgJxgrOLZiRZ18/fcrCH0jYWFDiQNiLg7b4xbzhzJUUiZg4e3wL2hVJvw5KGV9jUVQdTlwaipR2AOeGJLojsrBYjHxydwGgPLEk30UG1l7wJKhtecSGnAnBnvpeDywDuTZIGYhkcuLdhq2k+FJzeboLQANKZpYXB21b4EJfe3kweACNuKzBx+TCAYQs2S8BZTu4Fhi3Iv80f226GcRCB1cJbGu0ZJ4EN95+2FHs8SgIEsFmtlW6xERu2YPEsr5n8kLZx5p6ouOOGLhZp06+5bMoBePRNG7Gjquipf4CYeNRmphKpF4owQOweUMd96C6MMqDNykUkjMqAvx++3pzqK8CEbm+bin0yHUjWqe8qPMo6bkrbGp5ffNgidVLEJUfTQZEXaChSZ7lcYYrtjX8FbQhU/FETrUPapPPn9+Fx1k3cJdLWxrkUwAzGKPcak2s6wI40LBG5jfSPwxru7AC8qhT9sJHMAKnbPHD6urs10HZpnZxxA/ofd1Jj51T5AK6FEg3KRGp48IQ1xDiRhPw7JdeYfoisjctPpvkRGNjrXX1aFz9sUeSFnXfgwQYcand3v2nmVLjguziHJQ1dzzBOTZuG77ko8Hp1QqWMkH9LDwMjMwRUw/H0pHHvB/LtOtgh9m23iiskPsW8TS838CoO/M36sUJ3H5E1KbpKBqQGCUcMw0TWDHro8OqJGkSZv9OtTBvlhwn9NHxmcDa76eJC8neP09EAxVFnKZJBu71fXcY7i9U9Eg+RxDUlACrm6TqKfIxfPM9oriWgYiH+scTaiKCN32UfC6AyZwAYjdbTFLsRk7c0dvnGbeIMwGyORocmpiNGOxD/i8ftn4cKJv1kchI7EXl+o0FnBGHGVEnMc4D36Ui7AYwD2psJ89hGx1iHDh06dOjQoUOHDh06dOjQoUOHDh06dOjQoUOHDh06dOjQoUOHDh06dOjQoUOHDh06dOjQoUOHDh06dOhADfXXvyn8P4IqJ+9+KzICvkVompxASy7Cr2agK66OP1AU+KezdzDlX3i8wv8ER+KUs2+znxLiFHePZyMFKXEyO6q2I+7HwEucL+/xky/bCXGejEecdMT9GHiJS39/+KhoARrLjrgfw5G4SNM0H751VFU1exfEHdwdsJYQZ+phR9wP4oI4V1EUu1dPXM+AC/48cbbKnmCfPIfta3jciZ1Ez+p+rybY99IfsIu05Bwb/JvD3+1v3O0ev6mM0iP8Iu9AXC+MzL9OnKIriuz1epoM39hK5GmBax5kIWu+org4ktOTuXDw2m4EXkIxk7/RZHbwl6Ldvuu8xHkR+7SX/SNxpq79eeIUPP7XYSTsdS/z017CU14MZoQXma6SEqfrqGy/lbg9RJD7ZGyFUe9IXM/X+U4d/b1gTw3/IQmee/bTYuK0JH9VQSKMOCVp4UeI85hzYz5O218SZxqqi9rmy55qKJGdIQ6yuX+BuEhjJMjO2U+LiXPTSiuQzybE2fhnP0HcHvkC4nz2db/X9sdfJJ/2oZKcmbnXlJAd6OeDyffV7DV/FoqrqiF4NU1OGWKRCHo+QBFxmRgtIa4XgLH8CeL+31BwGs/unYhjdCGdAD7izCjsiLs50Chi2AUkmKmpPE02FJrKNIA5mkrm7zribg2kaJ8GGie6eIIT9UhcL4jCjrgbIwlOEu1RZaZN5cSpsg0p0Hk6gFeaetQRd2MwiiAPYAm4r0eB5pUQtw80nOPr2REm4KGZIa5nyB1xNwab3Nqrh6krVTuf8oL/TDU5/NV0tPTEdzU75ZW5skOHDh06dOhAApWgyEN3evvNYBpYoyNbMUH3SksllcA2PDdbeMHSI8/5PRWs/j3Yx7I42xEWKlnD/9u00I7LWRZGDSLrwLiuh16kH+vrdEVKrgKV1XSSRverXGGgyWJ1P5J4ygKltYWsyPNzizqog0wFo19WhOyvg5VRlEYP5SXQFqwQ17Ci9hmrxGVF5eWDTJ+NDb7ikB04kNS+W9XVLYxXyF1JPVUswllb9DHV65LyeR2aIak2GdewlnJ3PygsJ4zVJnVOL2i45fT/BtgGFmIDOEaLAmy5NnyRykqVaFzfdbW5KPuOtDXZY8LK6bq/L1CxfS9XQSypPtmEPtXJVyHDGnpeWf3R1l2NWhSyRuoytdexCnxTBWJFrL36624H00kqjWL643msgFjgQVaT/pCLPDuNqAej3XT6sECw6oGs8p9FGZeB+Db1ZV0LqBscy8Zj+Xa9xXAyPQh1fktulxRLlvTCwpJGwCitKxGbVFrd7B4uq3VCYD7dcZXr5IM6lAbTFrT1k+rzrHK8MZbGLcu12mCkQ4rnEIUfsSSzahTZrOJyeT1hE+sSS7uKqJyjQC4vAksaxe14w25sJcvohWdGsyl8Sxr+9C4+VgN9zBHmmsidVegTWA30bW1UjiWpN8JVwE3oxkNr2hCgdGMkT6QTumT9aFbHwiTuevKMoAvqbBeLgMe8A34tRp09lDb1Bcer8QCaL2qzgx81l0EREVUwWQCdFTtSP5jWK9sJ8XTQnjrVknZNblaEFbhiaSjKnGElzvIH0CYchtwH4uEg+10z2hCTqdQyMoDbrQVpQ95Wk7VkiTop0P3hTzDHwuE2w47Fw+lLVBBf3bdRgHgntRmvDshclLe1NLhjX4SZM3+EOQiHrZbhMOMLk1CwtNu2/maxaX5/Qt76/SkBc9HtmQvFpm6AMh0nL1pmUwjMqJp1wSfg7eHAG5pMYeZ67o2Zw2i2rbolQKVrr24JQOmaZEMQlwjztjrxhmmBJSx1XRqKNtEA6lg8qAKzJZhOgdKNGqRTNgFviyxvaDaF9QX8nFt/FREg7xe+GfC2HohHeGAuOfNYkJDw3e4GOe53UiQqCHMs3WrOGWQe1F9VDVcaLFAOFLE5H3ORNBK912Sb98nwE2GpgwW/zZSzwz3Iy+EmJifeijO3GHB1J5A2onk3WMZd/kcw9oSl7hO4Sg4Q8sZGLIX9qu8QDGvRea7+YxH3j9JYWOqhpIs2UQ8K3ryTi78Vc0PhQKg/GUhFC69rguBiKO576gAjV5g3JxuaAXP3oiKtZ84Td3AQiBQmnZONuLFUJevKO1FsAt6M85D6IlJrgbpU2LYGseg9FtKm7BfixtITj04rQZF0XKRTd5K4+1lV+/dIZIYmxabQUCJ2BPH8+LqRpSvuRYH7vBBXJAFfRc+MMmVpgOllRHlAPBA3dIY0Fm2iAg5JBHUpgIIwuzGqEipdarMv6AwQmcSlv5wSxCe6uA8qBRg54UlVXxoUaNdWPOaLyxMqgyAymVYZW2BVWOXUK6qcLm7LTavQU9xJ4sHDqtQc6KXeiRuTQdF4O4JC5dyrqVxAMI/tlsT+U5JwvXhcUSjcQ3XOAion7EOupnImgaE0SsOQrbgbiksMViSebkBIGVf+fkoQWOpXCixdgk1Jw1J6FgSR5UNhZGkThJSPddM7EwJ18a+Ty9kEM6FOhdFak6RaBWPWI2h4V+sl15LYujJiLB7iFIAiXB1XZNpxtfvnwqJI5cY1Vo4Dk3qlfSQIT7xrzFhSuE6n0uBUBtycGF2Oe5UgNFlxTKduxMMT+xq7GCiC1eqxXxNxc6FA5UKC0GTHMSd3TyCgIb2ttK+ucPjowlk4qFzeyw0l4eHAYSlxzIjbyoDeVoYk4W61h48Jwr9VXny2tBVu9JFr4WlgCUtIpY8rLQoLXifCnXguB64m965vveecy8ePz29ygX7ec/VrV5jmZk5i4DgdfSyJSjl/ewI7UO9sHgnmmqc5a+PWT3dliHubA3lx/oKtxHPnh0I7555M9+Ho+wpf6FLn4BFBg+N6Z7MR90dxLjIb1gv9RBz7JM9zv5/wWdu7usGdEqfK5ZdQOzmTIDRROfaW3BNEgNtzi8WRDCBdTzP5/akvy/ISazKe/37BubciLyRVcXR2iLumy4Gi9mQtkgNW/lEpFxLt5liHYLYr5HAUdwS28txiGRzDBYl7f+5/v5do3APncBrlHJSqeyYSp0VYfAGIC3s2fKnSuB5xJkdhejksJdpKLhFV4dxWOhwpBtD1+T6fz+WnYuLuOVeF1jkpqbLJCpVEOCmQlieDbyuJG9JGJ1ZtpLvPxHKFdXptLmVa18hokhdqAbbZANjjEDrQtXybAUqIG3GOpmlu2oYxBEwx38ZJXESagnOkF9rJbLtuUcrHM/QxriwI3uNZnJFxLe6z8os4Jiqh0ef3U/MXxG0G9TdFLHK57oE4F6uTcRLnkYaVHLHOiTgjwppBFxdwhOV9DOAKYonJy9HIHoibv5a38Zh1yDqHtmCjH1/z+WfS/Pvy6fz3vJOdZcQZuq8qJ+L2slF+sLtHugzu1q+gMuL2qtkzPVfdO/KFuRzz2Zu8k4vj/lN/PkEjGU+eULJPINb45WMelzUxyc5X8tz2aQZ/9DqbPbNP8F9uVPASN8lNlNqsVAmovxF6GJVgsU781lfKV4AM0jd3ODwmEhdEim7bka74rqzkdZTz6fPTucvX91c0Xs/vs/eXGRD3Mnt7he/eQMxl2EiNb1uFO+498hU7BG3ObY20xHHEqECcilFvmNYyuuwQXy40zSUNS/kJvU6Mjk4mAt8AAApoSURBVGeGxM3hX9xfzira2GUcPAFxC+7lphLiDE/TdM5lVlLieLJCoMtTNM2TS4jji00upcQYkuffH8nnQ/RQSdw04+B/A3FYP5A3ViTNwHlGgYYFlbG8YQlxPGE5Ip+Cp8SxL7zErTL9/RXENQHlC1cex3YKoCsNLIuJi3hfD8hJOiXuCcxj/4uTuEVHXAKe3ALLK7oK1hVF4syL0yl5wnKGwfmaXEpc/+V9+fVxIm7+toxLm4gzD//XiLMpV+R4JrxUH4/61pw9+9SztbbE5aYp5hg8Ikmfy6flVz9eHr+NuQTIT9xTqsTP+RSRgjgl8XF26ezyAaTBiU4wf8a9uF0yv4SZ3Ed5BlAqwDri4ovc/sIK8y4OXBBnnvJsjhWdBL+PON6RX0LcbAZ5HGcT/Uz6Mq6ZrjrNb5USx52A54nLVHf9/xLXn5dPlVxge+pwjYmeyeg4X2avEyDu6QuzeiQufpktTxfxT3mdzfQFeqTY+0DBI1hlA5IlkxFnakrFiY2kK6m/grgmGGWJi6uunMvf8/7zfP71Ah9n88+3byRu8vY9/zgxN2g3yWy7nmqqhqpBuCFHhuqGjDjFU71yV0c6yfyXiavLHxNTGX/LbEKm//yBxH2DUf58b9ylh9wEcWIqTawEK/tYoRk+9uwIlwpKZ5k5poX5wU/cYVXOvuiYOHGHpbj4qeSCEzan5cOgZvUaift8//iQEx+HS3MzMKBn+xfWnDnofU5bkDhbiQ4LA/AfEKextkvZoVCSFo0dFncui66LE1cePFwgEyTUzZECcbHMVh1Y+6Bn0P7XeUaQnz8twyi3hRGlgGWQUuJAz4A4p3pKi/QdOb16T2Vm8a2cuLroLvP0Z9++HPWrHXFmzYh5++w/AXEvMub0kHEs2cQa7tKLj9fwJnKDnNBDiDN0p+cjcRCPgGcD4kw8fb/UUtLuiK12mGay+Gaj9uMaAVpLRtzZkhx3An6+h5EFD/0YF+RwKQ6tJSPuqXILQ4MFuWd51n+V35+BuNnyTX5N2n9+l99OSWPh6u4l4rzQbV3u+brONngZkawkUaXtynJp5OiQrupUE2ezxTdFUfR9T3MVsOmMODtSokwnuIk7FxIL119ns7dPthT38c5WdCazj4+P8q1HZ7v4uSdJK8C3hWlFIHTaDbE1uQXLKsGYeh54XrtnglkH4hQHrMJJ50Le9+dzoxuDhwmqACpfv//1hcQtv4DS79ImzsLygOBNkrotTIerKLbCUR6jV5PNJ8Q5YZQuEGghELeXNU2LTq7O43Tw+fklFq4/vxyjvjck7v1luZyVu7qzsFwl2Kq54nJyG/GA0KY9Ra+mOSRurweYVibEKUCcisRlZpp9TgeflxES9/H1+X0WrstA3LJ84vI8LOfaz1mNmGcP+h1BXEHr4upiVCSObTkB4rDvCppKM1efi3fgT3NWiW1VgDA9TZCXzFRm56MKcB6WuwSvAG05NvlNCfZnRZTpd68uH8DFN1UOAhc1TtFCl+279vVAyw4fzuWBUU4/Zl/Pk7ev5QyI+/hYvr49IXFPby/L1/I8/Hyc+eLnYYLxrXeU+fe7WoDiFY0zhNVL4Lj4pmrG3u+p6l7DcqFYUtnWtKzF0vksVm4dtT8BkxgvnyfLfvw8+cZFOFyhi7/hR2VN5G2WdRNbSWEpA+oqSf6N3vm4xlsfNC+Bb2uTCppXwIlLypkE75D4XMuRPDapDvk3Q1WCl4lXtQfUDMRPgVHpD10YiqcXJpeT24mfSXL5Lvb1z2ArePe8Ba5wCltI0GTNylgiH4LX9xcXzsYhsL/3NekMQWhiS+Jv/+dBMffJM4XBcxBMvYgvBhnB2UJx4TGbR6wIBOReo+AHwVQMz7kVPAfB1KHghJ+6A1Z4UH3UGMFB6BRnpV2CwlYOa8c9haV8LBr7Y3EvV6lyK4KpqqsoHEnA49TawZrTPLlQeIpd1al9vKjwcpONeByvXqlMC0GKYVp1Z3XVnebJgbh48mEonstVnKZNc5b2dWp9UJxQ49ZIb0FyPk2hwTGqYwsuPJSpbUxykue1yv9ZFPFutQsbUeRbJd0MCaxw2VHtI3FlMa9XicwjULnqw5EXBDMcpUYLJCO8SFByVHvxgcJNJXO1Wu4mgcpVF9wYkfihsk5SGMvCo9rvCOoZBQSFOEpxbZW7qsL10FiKR5ajy8gSIkrhTEklOF++HCZB2R67YtiX1xziRnWNm6G4m4sHFwZ3Jz6eQbJXrR5HEfiUxwilMVsDVFeVukJRYszuxMt4X70sMUGqYZYVvIwH4jVa6opeGpK4Uj+c24wVgYO7fgl3irKCfoknK/AeTTGpLTPr5PWlBdbS9sRcXb1BHrg3qGzrkQS+RcbygSAy2dWH1AEtc9yVrCvgEnBfj+GVylndDShqNnKE1C4lc3+HN/TvBEWvLyLLCUH5OOCeRwIEzE1S5v4Qb+glxN1cmPUSDDvx9TJIp/giJ08Sjy3XyP6DOG/mzXhDqYuHQHqOp/sLJptjyx1SO5J4HATM7cRlbg6l4a14w+BCOJuDDmeZWxFUa1w3GE++Je1EB8q9JAlPdhjW1fOALCgKSqvZVHhFEi80metTh9JGLKF7HABxupjTAJt9tYnlQhAzBy5eeNp+3dBsgW+R7tsr3WQnSZ49lCyB+QgYPJZ4wblmIGVuRRIrNHY3YC5bKx2o2xhja09A6UT+tj3QqYoaZ2Bu/XO8wTNEkrSLW9ztbgQmLnl60Bqp4qyZcvhjIW1tD2ROdLwgc5ARDURnDycjadwqTADpNbeX8RpU5XS7wJKsxjmBoUtSdHt1YzB18WAYxutAPC6527bXfs+SBvdxQ9rGZ0ShtxwHTTqAtOnX2qbAAeiwqLIbkridXA0kt73VNoE6ac2r84+jPG0IGyRhhZwKZDrjojZuCseSdCFHF0gS+AihtU0wtYKTp6YHktxM49pb3d1vykRuh0D/0KkXhu9aP6ttCcQCWltH7wDB3ba9sVxskvBODA6EKdL2vkrvHpE1yS29l+kMMa/Ln4p79rwOssatmleFGbaPaQ/hMAZ3LTOqGLKpiGTmwQ5Q7tJo+ng5iO5W9yP8ZVSjUXaIbViRZ1xKxAjcMf7SvXXiVgqjZTzsZMJhULpBC083mUI2RScI02GyBdUbracH7EYb9rNh6PM8pB1EFrte10PvAF1Pf+b9uIk8A3h3qyl1GFdlIgpU3E1T6iAoaZdCVcD2vUiXzmDprmc0uQ20oQ/zbYTB7yKNAcVueQ0MJrqDnIPGwGyz4jeYqG2Sey1voRoHtL+DQdDG1YFilzjNt41BXEFcZUAbvCnV3fqatP2vwGLqcViXkScxgFt8mY1B1/ahjjsWlf+O2OzfgB+xsMkplajhIWvDikmGJKbe3j+W2czJ43rD4rurPMH/FibLh6Rx5Pm5SqGG5zJ/PQzqFMUOWHCwXU8X54H54nG6YxFeVNtGhxYwvDT4lcZ6ijS04oyosVpIePib7SjBJv1e935NHvQvwja88JC8IGN65DlNo2HV91z9FJnrEJT7t9uc0aFDhw4dOnTo0KHDCf8BYrvlNb0OaDEAAAAASUVORK5CYII=\n",
      "text/plain": [
       "<IPython.core.display.Image object>"
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "Image(filename='images/joins.png')"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "3e5f72d4-294a-44e3-a614-057619e54203",
   "metadata": {},
   "source": [
    "#### Merge"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "127cb32c-3001-4ea0-90da-3331cb9d4ae9",
   "metadata": {},
   "source": [
    "Удобно использовать для джойна датафреймов по столбцам. По дефолту 'inner join'."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "id": "49eb88d6-cf26-44e8-bdcd-f350b85fc52d",
   "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>sk_id_curr</th>\n",
       "      <th>target</th>\n",
       "      <th>name_contract_type</th>\n",
       "      <th>code_gender</th>\n",
       "      <th>flag_own_car</th>\n",
       "      <th>flag_own_realty</th>\n",
       "      <th>cnt_children</th>\n",
       "      <th>amt_income_total</th>\n",
       "      <th>amt_credit</th>\n",
       "      <th>amt_annuity_app</th>\n",
       "      <th>amt_goods_price</th>\n",
       "      <th>name_type_suite</th>\n",
       "      <th>name_income_type</th>\n",
       "      <th>name_education_type</th>\n",
       "      <th>name_family_status</th>\n",
       "      <th>name_housing_type</th>\n",
       "      <th>region_population_relative</th>\n",
       "      <th>days_birth</th>\n",
       "      <th>days_employed</th>\n",
       "      <th>days_registration</th>\n",
       "      <th>days_id_publish</th>\n",
       "      <th>own_car_age</th>\n",
       "      <th>flag_mobil</th>\n",
       "      <th>flag_emp_phone</th>\n",
       "      <th>flag_work_phone</th>\n",
       "      <th>flag_cont_mobile</th>\n",
       "      <th>flag_phone</th>\n",
       "      <th>flag_email</th>\n",
       "      <th>occupation_type</th>\n",
       "      <th>cnt_fam_members</th>\n",
       "      <th>amt_req_credit_bureau_hour</th>\n",
       "      <th>amt_req_credit_bureau_day</th>\n",
       "      <th>amt_req_credit_bureau_week</th>\n",
       "      <th>amt_req_credit_bureau_mon</th>\n",
       "      <th>amt_req_credit_bureau_qrt</th>\n",
       "      <th>amt_req_credit_bureau_year</th>\n",
       "      <th>days_birth_years</th>\n",
       "      <th>days_employed_years</th>\n",
       "      <th>days_registration_years</th>\n",
       "      <th>age_bins</th>\n",
       "      <th>sk_id_bureau</th>\n",
       "      <th>credit_active</th>\n",
       "      <th>credit_currency</th>\n",
       "      <th>days_credit</th>\n",
       "      <th>credit_day_overdue</th>\n",
       "      <th>days_credit_enddate</th>\n",
       "      <th>days_enddate_fact</th>\n",
       "      <th>amt_credit_max_overdue</th>\n",
       "      <th>cnt_credit_prolong</th>\n",
       "      <th>amt_credit_sum</th>\n",
       "      <th>amt_credit_sum_debt</th>\n",
       "      <th>amt_credit_sum_limit</th>\n",
       "      <th>amt_credit_sum_overdue</th>\n",
       "      <th>credit_type</th>\n",
       "      <th>days_credit_update</th>\n",
       "      <th>amt_annuity_bureau</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>163465</td>\n",
       "      <td>0</td>\n",
       "      <td>Cash loans</td>\n",
       "      <td>F</td>\n",
       "      <td>N</td>\n",
       "      <td>Y</td>\n",
       "      <td>0</td>\n",
       "      <td>54000.0</td>\n",
       "      <td>50940.0</td>\n",
       "      <td>5166.0</td>\n",
       "      <td>45000.0</td>\n",
       "      <td>Unaccompanied</td>\n",
       "      <td>State servant</td>\n",
       "      <td>Secondary / secondary special</td>\n",
       "      <td>Married</td>\n",
       "      <td>House / apartment</td>\n",
       "      <td>0.031329</td>\n",
       "      <td>-18735</td>\n",
       "      <td>-5347</td>\n",
       "      <td>-6359.0</td>\n",
       "      <td>-2115</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>Cooking staff</td>\n",
       "      <td>2.0</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.0</td>\n",
       "      <td>51.293634</td>\n",
       "      <td>14.639288</td>\n",
       "      <td>17.409993</td>\n",
       "      <td>7.50+</td>\n",
       "      <td>5540129</td>\n",
       "      <td>Active</td>\n",
       "      <td>currency 1</td>\n",
       "      <td>-63</td>\n",
       "      <td>0</td>\n",
       "      <td>302.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "      <td>90000.0</td>\n",
       "      <td>90000.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>Consumer credit</td>\n",
       "      <td>-60</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>163465</td>\n",
       "      <td>0</td>\n",
       "      <td>Cash loans</td>\n",
       "      <td>F</td>\n",
       "      <td>N</td>\n",
       "      <td>Y</td>\n",
       "      <td>0</td>\n",
       "      <td>54000.0</td>\n",
       "      <td>50940.0</td>\n",
       "      <td>5166.0</td>\n",
       "      <td>45000.0</td>\n",
       "      <td>Unaccompanied</td>\n",
       "      <td>State servant</td>\n",
       "      <td>Secondary / secondary special</td>\n",
       "      <td>Married</td>\n",
       "      <td>House / apartment</td>\n",
       "      <td>0.031329</td>\n",
       "      <td>-18735</td>\n",
       "      <td>-5347</td>\n",
       "      <td>-6359.0</td>\n",
       "      <td>-2115</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>Cooking staff</td>\n",
       "      <td>2.0</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.0</td>\n",
       "      <td>51.293634</td>\n",
       "      <td>14.639288</td>\n",
       "      <td>17.409993</td>\n",
       "      <td>7.50+</td>\n",
       "      <td>5540130</td>\n",
       "      <td>Closed</td>\n",
       "      <td>currency 1</td>\n",
       "      <td>-571</td>\n",
       "      <td>0</td>\n",
       "      <td>-389.0</td>\n",
       "      <td>-389.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "      <td>110358.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.0</td>\n",
       "      <td>Consumer credit</td>\n",
       "      <td>-376</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   sk_id_curr  target name_contract_type code_gender flag_own_car  \\\n",
       "0      163465       0         Cash loans           F            N   \n",
       "1      163465       0         Cash loans           F            N   \n",
       "\n",
       "  flag_own_realty  cnt_children  amt_income_total  amt_credit  \\\n",
       "0               Y             0           54000.0     50940.0   \n",
       "1               Y             0           54000.0     50940.0   \n",
       "\n",
       "   amt_annuity_app  amt_goods_price name_type_suite name_income_type  \\\n",
       "0           5166.0          45000.0   Unaccompanied    State servant   \n",
       "1           5166.0          45000.0   Unaccompanied    State servant   \n",
       "\n",
       "             name_education_type name_family_status  name_housing_type  \\\n",
       "0  Secondary / secondary special            Married  House / apartment   \n",
       "1  Secondary / secondary special            Married  House / apartment   \n",
       "\n",
       "   region_population_relative  days_birth  days_employed  days_registration  \\\n",
       "0                    0.031329      -18735          -5347            -6359.0   \n",
       "1                    0.031329      -18735          -5347            -6359.0   \n",
       "\n",
       "   days_id_publish  own_car_age  flag_mobil  flag_emp_phone  flag_work_phone  \\\n",
       "0            -2115          NaN           1               1                0   \n",
       "1            -2115          NaN           1               1                0   \n",
       "\n",
       "   flag_cont_mobile  flag_phone  flag_email occupation_type  cnt_fam_members  \\\n",
       "0                 1           0           0   Cooking staff              2.0   \n",
       "1                 1           0           0   Cooking staff              2.0   \n",
       "\n",
       "   amt_req_credit_bureau_hour  amt_req_credit_bureau_day  \\\n",
       "0                         0.0                        0.0   \n",
       "1                         0.0                        0.0   \n",
       "\n",
       "   amt_req_credit_bureau_week  amt_req_credit_bureau_mon  \\\n",
       "0                         0.0                        0.0   \n",
       "1                         0.0                        0.0   \n",
       "\n",
       "   amt_req_credit_bureau_qrt  amt_req_credit_bureau_year  days_birth_years  \\\n",
       "0                        0.0                         2.0         51.293634   \n",
       "1                        0.0                         2.0         51.293634   \n",
       "\n",
       "   days_employed_years  days_registration_years age_bins  sk_id_bureau  \\\n",
       "0            14.639288                17.409993    7.50+       5540129   \n",
       "1            14.639288                17.409993    7.50+       5540130   \n",
       "\n",
       "  credit_active credit_currency  days_credit  credit_day_overdue  \\\n",
       "0        Active      currency 1          -63                   0   \n",
       "1        Closed      currency 1         -571                   0   \n",
       "\n",
       "   days_credit_enddate  days_enddate_fact  amt_credit_max_overdue  \\\n",
       "0                302.0                NaN                     NaN   \n",
       "1               -389.0             -389.0                     NaN   \n",
       "\n",
       "   cnt_credit_prolong  amt_credit_sum  amt_credit_sum_debt  \\\n",
       "0                   0         90000.0              90000.0   \n",
       "1                   0        110358.0                  0.0   \n",
       "\n",
       "   amt_credit_sum_limit  amt_credit_sum_overdue      credit_type  \\\n",
       "0                   0.0                     0.0  Consumer credit   \n",
       "1                   NaN                     0.0  Consumer credit   \n",
       "\n",
       "   days_credit_update  amt_annuity_bureau  \n",
       "0                 -60                 NaN  \n",
       "1                -376                 NaN  "
      ]
     },
     "execution_count": 19,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#появились столбцы с суффиксами. Почему? Потому что есть дублирующие колонки в таблицах. Сделаем понятные суффиксы\n",
    "d_inner = df.merge(db, on = 'sk_id_curr', suffixes = ['_app', '_bureau'])\n",
    "d_inner.head(2)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "id": "07d296c5-dfd0-4945-9d74-9fd9ffd677c8",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(147256, 56)"
      ]
     },
     "execution_count": 20,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "d_inner.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "id": "7cf3bc5a-c33a-41d5-afff-04f6901d4dcc",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "26344"
      ]
     },
     "execution_count": 21,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "d_inner.sk_id_curr.nunique()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "id": "5bc31666-4803-472f-834d-68cee963599f",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(30751, 40)"
      ]
     },
     "execution_count": 22,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "id": "0effd4c7-6fc5-4efa-94e1-cc1a07c3a13f",
   "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>sk_id_curr</th>\n",
       "      <th>target</th>\n",
       "      <th>name_contract_type</th>\n",
       "      <th>code_gender</th>\n",
       "      <th>flag_own_car</th>\n",
       "      <th>flag_own_realty</th>\n",
       "      <th>cnt_children</th>\n",
       "      <th>amt_income_total</th>\n",
       "      <th>amt_credit</th>\n",
       "      <th>amt_annuity_app</th>\n",
       "      <th>amt_goods_price</th>\n",
       "      <th>name_type_suite</th>\n",
       "      <th>name_income_type</th>\n",
       "      <th>name_education_type</th>\n",
       "      <th>name_family_status</th>\n",
       "      <th>name_housing_type</th>\n",
       "      <th>region_population_relative</th>\n",
       "      <th>days_birth</th>\n",
       "      <th>days_employed</th>\n",
       "      <th>days_registration</th>\n",
       "      <th>days_id_publish</th>\n",
       "      <th>own_car_age</th>\n",
       "      <th>flag_mobil</th>\n",
       "      <th>flag_emp_phone</th>\n",
       "      <th>flag_work_phone</th>\n",
       "      <th>flag_cont_mobile</th>\n",
       "      <th>flag_phone</th>\n",
       "      <th>flag_email</th>\n",
       "      <th>occupation_type</th>\n",
       "      <th>cnt_fam_members</th>\n",
       "      <th>amt_req_credit_bureau_hour</th>\n",
       "      <th>amt_req_credit_bureau_day</th>\n",
       "      <th>amt_req_credit_bureau_week</th>\n",
       "      <th>amt_req_credit_bureau_mon</th>\n",
       "      <th>amt_req_credit_bureau_qrt</th>\n",
       "      <th>amt_req_credit_bureau_year</th>\n",
       "      <th>days_birth_years</th>\n",
       "      <th>days_employed_years</th>\n",
       "      <th>days_registration_years</th>\n",
       "      <th>age_bins</th>\n",
       "      <th>sk_id_bureau</th>\n",
       "      <th>credit_active</th>\n",
       "      <th>credit_currency</th>\n",
       "      <th>days_credit</th>\n",
       "      <th>credit_day_overdue</th>\n",
       "      <th>days_credit_enddate</th>\n",
       "      <th>days_enddate_fact</th>\n",
       "      <th>amt_credit_max_overdue</th>\n",
       "      <th>cnt_credit_prolong</th>\n",
       "      <th>amt_credit_sum</th>\n",
       "      <th>amt_credit_sum_debt</th>\n",
       "      <th>amt_credit_sum_limit</th>\n",
       "      <th>amt_credit_sum_overdue</th>\n",
       "      <th>credit_type</th>\n",
       "      <th>days_credit_update</th>\n",
       "      <th>amt_annuity_bureau</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>163465</td>\n",
       "      <td>0</td>\n",
       "      <td>Cash loans</td>\n",
       "      <td>F</td>\n",
       "      <td>N</td>\n",
       "      <td>Y</td>\n",
       "      <td>0</td>\n",
       "      <td>54000.0</td>\n",
       "      <td>50940.0</td>\n",
       "      <td>5166.0</td>\n",
       "      <td>45000.0</td>\n",
       "      <td>Unaccompanied</td>\n",
       "      <td>State servant</td>\n",
       "      <td>Secondary / secondary special</td>\n",
       "      <td>Married</td>\n",
       "      <td>House / apartment</td>\n",
       "      <td>0.031329</td>\n",
       "      <td>-18735</td>\n",
       "      <td>-5347</td>\n",
       "      <td>-6359.0</td>\n",
       "      <td>-2115</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>Cooking staff</td>\n",
       "      <td>2.0</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.0</td>\n",
       "      <td>51.293634</td>\n",
       "      <td>14.639288</td>\n",
       "      <td>17.409993</td>\n",
       "      <td>7.50+</td>\n",
       "      <td>5540129</td>\n",
       "      <td>Active</td>\n",
       "      <td>currency 1</td>\n",
       "      <td>-63</td>\n",
       "      <td>0</td>\n",
       "      <td>302.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "      <td>90000.0</td>\n",
       "      <td>90000.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>Consumer credit</td>\n",
       "      <td>-60</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>163465</td>\n",
       "      <td>0</td>\n",
       "      <td>Cash loans</td>\n",
       "      <td>F</td>\n",
       "      <td>N</td>\n",
       "      <td>Y</td>\n",
       "      <td>0</td>\n",
       "      <td>54000.0</td>\n",
       "      <td>50940.0</td>\n",
       "      <td>5166.0</td>\n",
       "      <td>45000.0</td>\n",
       "      <td>Unaccompanied</td>\n",
       "      <td>State servant</td>\n",
       "      <td>Secondary / secondary special</td>\n",
       "      <td>Married</td>\n",
       "      <td>House / apartment</td>\n",
       "      <td>0.031329</td>\n",
       "      <td>-18735</td>\n",
       "      <td>-5347</td>\n",
       "      <td>-6359.0</td>\n",
       "      <td>-2115</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>Cooking staff</td>\n",
       "      <td>2.0</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.0</td>\n",
       "      <td>51.293634</td>\n",
       "      <td>14.639288</td>\n",
       "      <td>17.409993</td>\n",
       "      <td>7.50+</td>\n",
       "      <td>5540130</td>\n",
       "      <td>Closed</td>\n",
       "      <td>currency 1</td>\n",
       "      <td>-571</td>\n",
       "      <td>0</td>\n",
       "      <td>-389.0</td>\n",
       "      <td>-389.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "      <td>110358.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.0</td>\n",
       "      <td>Consumer credit</td>\n",
       "      <td>-376</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   sk_id_curr  target name_contract_type code_gender flag_own_car  \\\n",
       "0      163465       0         Cash loans           F            N   \n",
       "1      163465       0         Cash loans           F            N   \n",
       "\n",
       "  flag_own_realty  cnt_children  amt_income_total  amt_credit  \\\n",
       "0               Y             0           54000.0     50940.0   \n",
       "1               Y             0           54000.0     50940.0   \n",
       "\n",
       "   amt_annuity_app  amt_goods_price name_type_suite name_income_type  \\\n",
       "0           5166.0          45000.0   Unaccompanied    State servant   \n",
       "1           5166.0          45000.0   Unaccompanied    State servant   \n",
       "\n",
       "             name_education_type name_family_status  name_housing_type  \\\n",
       "0  Secondary / secondary special            Married  House / apartment   \n",
       "1  Secondary / secondary special            Married  House / apartment   \n",
       "\n",
       "   region_population_relative  days_birth  days_employed  days_registration  \\\n",
       "0                    0.031329      -18735          -5347            -6359.0   \n",
       "1                    0.031329      -18735          -5347            -6359.0   \n",
       "\n",
       "   days_id_publish  own_car_age  flag_mobil  flag_emp_phone  flag_work_phone  \\\n",
       "0            -2115          NaN           1               1                0   \n",
       "1            -2115          NaN           1               1                0   \n",
       "\n",
       "   flag_cont_mobile  flag_phone  flag_email occupation_type  cnt_fam_members  \\\n",
       "0                 1           0           0   Cooking staff              2.0   \n",
       "1                 1           0           0   Cooking staff              2.0   \n",
       "\n",
       "   amt_req_credit_bureau_hour  amt_req_credit_bureau_day  \\\n",
       "0                         0.0                        0.0   \n",
       "1                         0.0                        0.0   \n",
       "\n",
       "   amt_req_credit_bureau_week  amt_req_credit_bureau_mon  \\\n",
       "0                         0.0                        0.0   \n",
       "1                         0.0                        0.0   \n",
       "\n",
       "   amt_req_credit_bureau_qrt  amt_req_credit_bureau_year  days_birth_years  \\\n",
       "0                        0.0                         2.0         51.293634   \n",
       "1                        0.0                         2.0         51.293634   \n",
       "\n",
       "   days_employed_years  days_registration_years age_bins  sk_id_bureau  \\\n",
       "0            14.639288                17.409993    7.50+       5540129   \n",
       "1            14.639288                17.409993    7.50+       5540130   \n",
       "\n",
       "  credit_active credit_currency  days_credit  credit_day_overdue  \\\n",
       "0        Active      currency 1          -63                   0   \n",
       "1        Closed      currency 1         -571                   0   \n",
       "\n",
       "   days_credit_enddate  days_enddate_fact  amt_credit_max_overdue  \\\n",
       "0                302.0                NaN                     NaN   \n",
       "1               -389.0             -389.0                     NaN   \n",
       "\n",
       "   cnt_credit_prolong  amt_credit_sum  amt_credit_sum_debt  \\\n",
       "0                   0         90000.0              90000.0   \n",
       "1                   0        110358.0                  0.0   \n",
       "\n",
       "   amt_credit_sum_limit  amt_credit_sum_overdue      credit_type  \\\n",
       "0                   0.0                     0.0  Consumer credit   \n",
       "1                   NaN                     0.0  Consumer credit   \n",
       "\n",
       "   days_credit_update  amt_annuity_bureau  \n",
       "0                 -60                 NaN  \n",
       "1                -376                 NaN  "
      ]
     },
     "execution_count": 23,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#left\n",
    "d_left = df.merge(db, on = 'sk_id_curr', how='left', suffixes = ['_app', '_bureau'])\n",
    "d_inner.head(2)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "id": "53f387c7-3d3a-4882-a010-f744d32853a8",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(151663, 56)"
      ]
     },
     "execution_count": 24,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "d_left.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "id": "ced4aa70-6094-471e-a563-031414fd4b31",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "30751"
      ]
     },
     "execution_count": 25,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "d_left.sk_id_curr.nunique()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "id": "d9109fc7-8dc3-4f6c-aa00-71bd8bb09503",
   "metadata": {},
   "outputs": [],
   "source": [
    "#почему в d_left больше записей, чем в d_inner и db?"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "8a693061-dfbc-4982-b439-aadd6e48de66",
   "metadata": {},
   "source": [
    "#### Join"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "722346f2-a07d-4b34-b127-2ad1b11e8a41",
   "metadata": {},
   "source": [
    "Удобно использовать для джойна датафреймов с одинаковым индексом. По дефолту 'left join'. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "id": "b5f1e779-5dae-465d-a4a9-a2a40f45271d",
   "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>sk_id_curr</th>\n",
       "      <th>target</th>\n",
       "      <th>name_contract_type</th>\n",
       "      <th>code_gender</th>\n",
       "      <th>flag_own_car</th>\n",
       "      <th>flag_own_realty</th>\n",
       "      <th>cnt_children</th>\n",
       "      <th>amt_income_total</th>\n",
       "      <th>amt_credit</th>\n",
       "      <th>amt_annuity</th>\n",
       "      <th>amt_goods_price</th>\n",
       "      <th>name_type_suite</th>\n",
       "      <th>name_income_type</th>\n",
       "      <th>name_education_type</th>\n",
       "      <th>name_family_status</th>\n",
       "      <th>name_housing_type</th>\n",
       "      <th>region_population_relative</th>\n",
       "      <th>days_birth</th>\n",
       "      <th>days_employed</th>\n",
       "      <th>days_registration</th>\n",
       "      <th>days_id_publish</th>\n",
       "      <th>own_car_age</th>\n",
       "      <th>flag_mobil</th>\n",
       "      <th>flag_emp_phone</th>\n",
       "      <th>flag_work_phone</th>\n",
       "      <th>flag_cont_mobile</th>\n",
       "      <th>flag_phone</th>\n",
       "      <th>flag_email</th>\n",
       "      <th>occupation_type</th>\n",
       "      <th>cnt_fam_members</th>\n",
       "      <th>amt_req_credit_bureau_hour</th>\n",
       "      <th>amt_req_credit_bureau_day</th>\n",
       "      <th>amt_req_credit_bureau_week</th>\n",
       "      <th>amt_req_credit_bureau_mon</th>\n",
       "      <th>amt_req_credit_bureau_qrt</th>\n",
       "      <th>amt_req_credit_bureau_year</th>\n",
       "      <th>days_birth_years</th>\n",
       "      <th>days_employed_years</th>\n",
       "      <th>days_registration_years</th>\n",
       "      <th>age_bins</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>163465</td>\n",
       "      <td>0</td>\n",
       "      <td>Cash loans</td>\n",
       "      <td>F</td>\n",
       "      <td>N</td>\n",
       "      <td>Y</td>\n",
       "      <td>0</td>\n",
       "      <td>54000.0</td>\n",
       "      <td>50940.0</td>\n",
       "      <td>5166.0</td>\n",
       "      <td>45000.0</td>\n",
       "      <td>Unaccompanied</td>\n",
       "      <td>State servant</td>\n",
       "      <td>Secondary / secondary special</td>\n",
       "      <td>Married</td>\n",
       "      <td>House / apartment</td>\n",
       "      <td>0.031329</td>\n",
       "      <td>-18735</td>\n",
       "      <td>-5347</td>\n",
       "      <td>-6359.0</td>\n",
       "      <td>-2115</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>Cooking staff</td>\n",
       "      <td>2.0</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.0</td>\n",
       "      <td>51.293634</td>\n",
       "      <td>14.639288</td>\n",
       "      <td>17.409993</td>\n",
       "      <td>7.50+</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>256730</td>\n",
       "      <td>0</td>\n",
       "      <td>Cash loans</td>\n",
       "      <td>M</td>\n",
       "      <td>N</td>\n",
       "      <td>N</td>\n",
       "      <td>0</td>\n",
       "      <td>58500.0</td>\n",
       "      <td>269550.0</td>\n",
       "      <td>10116.0</td>\n",
       "      <td>225000.0</td>\n",
       "      <td>Unaccompanied</td>\n",
       "      <td>Pensioner</td>\n",
       "      <td>Secondary / secondary special</td>\n",
       "      <td>Separated</td>\n",
       "      <td>House / apartment</td>\n",
       "      <td>0.020246</td>\n",
       "      <td>-23152</td>\n",
       "      <td>365243</td>\n",
       "      <td>-15551.0</td>\n",
       "      <td>-4795</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1.0</td>\n",
       "      <td>2.0</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>63.386721</td>\n",
       "      <td>999.980835</td>\n",
       "      <td>42.576318</td>\n",
       "      <td>7.50+</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   sk_id_curr  target name_contract_type code_gender flag_own_car  \\\n",
       "0      163465       0         Cash loans           F            N   \n",
       "1      256730       0         Cash loans           M            N   \n",
       "\n",
       "  flag_own_realty  cnt_children  amt_income_total  amt_credit  amt_annuity  \\\n",
       "0               Y             0           54000.0     50940.0       5166.0   \n",
       "1               N             0           58500.0    269550.0      10116.0   \n",
       "\n",
       "   amt_goods_price name_type_suite name_income_type  \\\n",
       "0          45000.0   Unaccompanied    State servant   \n",
       "1         225000.0   Unaccompanied        Pensioner   \n",
       "\n",
       "             name_education_type name_family_status  name_housing_type  \\\n",
       "0  Secondary / secondary special            Married  House / apartment   \n",
       "1  Secondary / secondary special          Separated  House / apartment   \n",
       "\n",
       "   region_population_relative  days_birth  days_employed  days_registration  \\\n",
       "0                    0.031329      -18735          -5347            -6359.0   \n",
       "1                    0.020246      -23152         365243           -15551.0   \n",
       "\n",
       "   days_id_publish  own_car_age  flag_mobil  flag_emp_phone  flag_work_phone  \\\n",
       "0            -2115          NaN           1               1                0   \n",
       "1            -4795          NaN           1               0                0   \n",
       "\n",
       "   flag_cont_mobile  flag_phone  flag_email occupation_type  cnt_fam_members  \\\n",
       "0                 1           0           0   Cooking staff              2.0   \n",
       "1                 1           0           0             NaN              1.0   \n",
       "\n",
       "   amt_req_credit_bureau_hour  amt_req_credit_bureau_day  \\\n",
       "0                         0.0                        0.0   \n",
       "1                         2.0                        0.0   \n",
       "\n",
       "   amt_req_credit_bureau_week  amt_req_credit_bureau_mon  \\\n",
       "0                         0.0                        0.0   \n",
       "1                         0.0                        0.0   \n",
       "\n",
       "   amt_req_credit_bureau_qrt  amt_req_credit_bureau_year  days_birth_years  \\\n",
       "0                        0.0                         2.0         51.293634   \n",
       "1                        0.0                         0.0         63.386721   \n",
       "\n",
       "   days_employed_years  days_registration_years age_bins  \n",
       "0            14.639288                17.409993    7.50+  \n",
       "1           999.980835                42.576318    7.50+  "
      ]
     },
     "execution_count": 27,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.head(2)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "id": "298b9822-57d7-4377-b99c-a5bc176015ee",
   "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>target</th>\n",
       "      <th>name_contract_type</th>\n",
       "      <th>code_gender</th>\n",
       "      <th>flag_own_car</th>\n",
       "      <th>flag_own_realty</th>\n",
       "      <th>cnt_children</th>\n",
       "      <th>amt_income_total</th>\n",
       "      <th>amt_credit</th>\n",
       "      <th>amt_annuity</th>\n",
       "      <th>amt_goods_price</th>\n",
       "      <th>name_type_suite</th>\n",
       "      <th>name_income_type</th>\n",
       "      <th>name_education_type</th>\n",
       "      <th>name_family_status</th>\n",
       "      <th>name_housing_type</th>\n",
       "      <th>region_population_relative</th>\n",
       "      <th>days_birth</th>\n",
       "      <th>days_employed</th>\n",
       "      <th>days_registration</th>\n",
       "      <th>days_id_publish</th>\n",
       "      <th>own_car_age</th>\n",
       "      <th>flag_mobil</th>\n",
       "      <th>flag_emp_phone</th>\n",
       "      <th>flag_work_phone</th>\n",
       "      <th>flag_cont_mobile</th>\n",
       "      <th>flag_phone</th>\n",
       "      <th>flag_email</th>\n",
       "      <th>occupation_type</th>\n",
       "      <th>cnt_fam_members</th>\n",
       "      <th>amt_req_credit_bureau_hour</th>\n",
       "      <th>amt_req_credit_bureau_day</th>\n",
       "      <th>amt_req_credit_bureau_week</th>\n",
       "      <th>amt_req_credit_bureau_mon</th>\n",
       "      <th>amt_req_credit_bureau_qrt</th>\n",
       "      <th>amt_req_credit_bureau_year</th>\n",
       "      <th>days_birth_years</th>\n",
       "      <th>days_employed_years</th>\n",
       "      <th>days_registration_years</th>\n",
       "      <th>age_bins</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>sk_id_curr</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",
       "      <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",
       "      <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",
       "      <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",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>163465</th>\n",
       "      <td>0</td>\n",
       "      <td>Cash loans</td>\n",
       "      <td>F</td>\n",
       "      <td>N</td>\n",
       "      <td>Y</td>\n",
       "      <td>0</td>\n",
       "      <td>54000.0</td>\n",
       "      <td>50940.0</td>\n",
       "      <td>5166.0</td>\n",
       "      <td>45000.0</td>\n",
       "      <td>Unaccompanied</td>\n",
       "      <td>State servant</td>\n",
       "      <td>Secondary / secondary special</td>\n",
       "      <td>Married</td>\n",
       "      <td>House / apartment</td>\n",
       "      <td>0.031329</td>\n",
       "      <td>-18735</td>\n",
       "      <td>-5347</td>\n",
       "      <td>-6359.0</td>\n",
       "      <td>-2115</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>Cooking staff</td>\n",
       "      <td>2.0</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.0</td>\n",
       "      <td>51.293634</td>\n",
       "      <td>14.639288</td>\n",
       "      <td>17.409993</td>\n",
       "      <td>7.50+</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>256730</th>\n",
       "      <td>0</td>\n",
       "      <td>Cash loans</td>\n",
       "      <td>M</td>\n",
       "      <td>N</td>\n",
       "      <td>N</td>\n",
       "      <td>0</td>\n",
       "      <td>58500.0</td>\n",
       "      <td>269550.0</td>\n",
       "      <td>10116.0</td>\n",
       "      <td>225000.0</td>\n",
       "      <td>Unaccompanied</td>\n",
       "      <td>Pensioner</td>\n",
       "      <td>Secondary / secondary special</td>\n",
       "      <td>Separated</td>\n",
       "      <td>House / apartment</td>\n",
       "      <td>0.020246</td>\n",
       "      <td>-23152</td>\n",
       "      <td>365243</td>\n",
       "      <td>-15551.0</td>\n",
       "      <td>-4795</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1.0</td>\n",
       "      <td>2.0</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>63.386721</td>\n",
       "      <td>999.980835</td>\n",
       "      <td>42.576318</td>\n",
       "      <td>7.50+</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "            target name_contract_type code_gender flag_own_car  \\\n",
       "sk_id_curr                                                       \n",
       "163465           0         Cash loans           F            N   \n",
       "256730           0         Cash loans           M            N   \n",
       "\n",
       "           flag_own_realty  cnt_children  amt_income_total  amt_credit  \\\n",
       "sk_id_curr                                                               \n",
       "163465                   Y             0           54000.0     50940.0   \n",
       "256730                   N             0           58500.0    269550.0   \n",
       "\n",
       "            amt_annuity  amt_goods_price name_type_suite name_income_type  \\\n",
       "sk_id_curr                                                                  \n",
       "163465           5166.0          45000.0   Unaccompanied    State servant   \n",
       "256730          10116.0         225000.0   Unaccompanied        Pensioner   \n",
       "\n",
       "                      name_education_type name_family_status  \\\n",
       "sk_id_curr                                                     \n",
       "163465      Secondary / secondary special            Married   \n",
       "256730      Secondary / secondary special          Separated   \n",
       "\n",
       "            name_housing_type  region_population_relative  days_birth  \\\n",
       "sk_id_curr                                                              \n",
       "163465      House / apartment                    0.031329      -18735   \n",
       "256730      House / apartment                    0.020246      -23152   \n",
       "\n",
       "            days_employed  days_registration  days_id_publish  own_car_age  \\\n",
       "sk_id_curr                                                                   \n",
       "163465              -5347            -6359.0            -2115          NaN   \n",
       "256730             365243           -15551.0            -4795          NaN   \n",
       "\n",
       "            flag_mobil  flag_emp_phone  flag_work_phone  flag_cont_mobile  \\\n",
       "sk_id_curr                                                                  \n",
       "163465               1               1                0                 1   \n",
       "256730               1               0                0                 1   \n",
       "\n",
       "            flag_phone  flag_email occupation_type  cnt_fam_members  \\\n",
       "sk_id_curr                                                            \n",
       "163465               0           0   Cooking staff              2.0   \n",
       "256730               0           0             NaN              1.0   \n",
       "\n",
       "            amt_req_credit_bureau_hour  amt_req_credit_bureau_day  \\\n",
       "sk_id_curr                                                          \n",
       "163465                             0.0                        0.0   \n",
       "256730                             2.0                        0.0   \n",
       "\n",
       "            amt_req_credit_bureau_week  amt_req_credit_bureau_mon  \\\n",
       "sk_id_curr                                                          \n",
       "163465                             0.0                        0.0   \n",
       "256730                             0.0                        0.0   \n",
       "\n",
       "            amt_req_credit_bureau_qrt  amt_req_credit_bureau_year  \\\n",
       "sk_id_curr                                                          \n",
       "163465                            0.0                         2.0   \n",
       "256730                            0.0                         0.0   \n",
       "\n",
       "            days_birth_years  days_employed_years  days_registration_years  \\\n",
       "sk_id_curr                                                                   \n",
       "163465             51.293634            14.639288                17.409993   \n",
       "256730             63.386721           999.980835                42.576318   \n",
       "\n",
       "           age_bins  \n",
       "sk_id_curr           \n",
       "163465        7.50+  \n",
       "256730        7.50+  "
      ]
     },
     "execution_count": 28,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df = df.set_index('sk_id_curr')\n",
    "df.head(2)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "id": "715514b2-e25c-4c05-bade-52111b299af2",
   "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>sk_id_bureau</th>\n",
       "      <th>credit_active</th>\n",
       "      <th>credit_currency</th>\n",
       "      <th>days_credit</th>\n",
       "      <th>credit_day_overdue</th>\n",
       "      <th>days_credit_enddate</th>\n",
       "      <th>days_enddate_fact</th>\n",
       "      <th>amt_credit_max_overdue</th>\n",
       "      <th>cnt_credit_prolong</th>\n",
       "      <th>amt_credit_sum</th>\n",
       "      <th>amt_credit_sum_debt</th>\n",
       "      <th>amt_credit_sum_limit</th>\n",
       "      <th>amt_credit_sum_overdue</th>\n",
       "      <th>credit_type</th>\n",
       "      <th>days_credit_update</th>\n",
       "      <th>amt_annuity</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>sk_id_curr</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",
       "      <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>426155</th>\n",
       "      <td>5714498</td>\n",
       "      <td>Closed</td>\n",
       "      <td>currency 1</td>\n",
       "      <td>-1331</td>\n",
       "      <td>0</td>\n",
       "      <td>-994.0</td>\n",
       "      <td>-1023.0</td>\n",
       "      <td>1350.0</td>\n",
       "      <td>0</td>\n",
       "      <td>39433.5</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>Consumer credit</td>\n",
       "      <td>-1023</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>426155</th>\n",
       "      <td>5714499</td>\n",
       "      <td>Closed</td>\n",
       "      <td>currency 1</td>\n",
       "      <td>-2534</td>\n",
       "      <td>0</td>\n",
       "      <td>-2352.0</td>\n",
       "      <td>-2347.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "      <td>38830.5</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>Consumer credit</td>\n",
       "      <td>-2345</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "            sk_id_bureau credit_active credit_currency  days_credit  \\\n",
       "sk_id_curr                                                            \n",
       "426155           5714498        Closed      currency 1        -1331   \n",
       "426155           5714499        Closed      currency 1        -2534   \n",
       "\n",
       "            credit_day_overdue  days_credit_enddate  days_enddate_fact  \\\n",
       "sk_id_curr                                                               \n",
       "426155                       0               -994.0            -1023.0   \n",
       "426155                       0              -2352.0            -2347.0   \n",
       "\n",
       "            amt_credit_max_overdue  cnt_credit_prolong  amt_credit_sum  \\\n",
       "sk_id_curr                                                               \n",
       "426155                      1350.0                   0         39433.5   \n",
       "426155                         NaN                   0         38830.5   \n",
       "\n",
       "            amt_credit_sum_debt  amt_credit_sum_limit  amt_credit_sum_overdue  \\\n",
       "sk_id_curr                                                                      \n",
       "426155                      0.0                   0.0                     0.0   \n",
       "426155                      0.0                   0.0                     0.0   \n",
       "\n",
       "                credit_type  days_credit_update  amt_annuity  \n",
       "sk_id_curr                                                    \n",
       "426155      Consumer credit               -1023          NaN  \n",
       "426155      Consumer credit               -2345          NaN  "
      ]
     },
     "execution_count": 29,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "db = db.set_index('sk_id_curr')\n",
    "db.head(2)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "id": "719bdf8c-f1b5-4c2f-8224-6d5960f8ea16",
   "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>target</th>\n",
       "      <th>name_contract_type</th>\n",
       "      <th>code_gender</th>\n",
       "      <th>flag_own_car</th>\n",
       "      <th>flag_own_realty</th>\n",
       "      <th>cnt_children</th>\n",
       "      <th>amt_income_total</th>\n",
       "      <th>amt_credit</th>\n",
       "      <th>amt_annuity_app</th>\n",
       "      <th>amt_goods_price</th>\n",
       "      <th>name_type_suite</th>\n",
       "      <th>name_income_type</th>\n",
       "      <th>name_education_type</th>\n",
       "      <th>name_family_status</th>\n",
       "      <th>name_housing_type</th>\n",
       "      <th>region_population_relative</th>\n",
       "      <th>days_birth</th>\n",
       "      <th>days_employed</th>\n",
       "      <th>days_registration</th>\n",
       "      <th>days_id_publish</th>\n",
       "      <th>own_car_age</th>\n",
       "      <th>flag_mobil</th>\n",
       "      <th>flag_emp_phone</th>\n",
       "      <th>flag_work_phone</th>\n",
       "      <th>flag_cont_mobile</th>\n",
       "      <th>flag_phone</th>\n",
       "      <th>flag_email</th>\n",
       "      <th>occupation_type</th>\n",
       "      <th>cnt_fam_members</th>\n",
       "      <th>amt_req_credit_bureau_hour</th>\n",
       "      <th>amt_req_credit_bureau_day</th>\n",
       "      <th>amt_req_credit_bureau_week</th>\n",
       "      <th>amt_req_credit_bureau_mon</th>\n",
       "      <th>amt_req_credit_bureau_qrt</th>\n",
       "      <th>amt_req_credit_bureau_year</th>\n",
       "      <th>days_birth_years</th>\n",
       "      <th>days_employed_years</th>\n",
       "      <th>days_registration_years</th>\n",
       "      <th>age_bins</th>\n",
       "      <th>sk_id_bureau</th>\n",
       "      <th>credit_active</th>\n",
       "      <th>credit_currency</th>\n",
       "      <th>days_credit</th>\n",
       "      <th>credit_day_overdue</th>\n",
       "      <th>days_credit_enddate</th>\n",
       "      <th>days_enddate_fact</th>\n",
       "      <th>amt_credit_max_overdue</th>\n",
       "      <th>cnt_credit_prolong</th>\n",
       "      <th>amt_credit_sum</th>\n",
       "      <th>amt_credit_sum_debt</th>\n",
       "      <th>amt_credit_sum_limit</th>\n",
       "      <th>amt_credit_sum_overdue</th>\n",
       "      <th>credit_type</th>\n",
       "      <th>days_credit_update</th>\n",
       "      <th>amt_annuity_bureau</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>sk_id_curr</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",
       "      <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",
       "      <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",
       "      <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",
       "      <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",
       "      <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",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>100003</th>\n",
       "      <td>0</td>\n",
       "      <td>Cash loans</td>\n",
       "      <td>F</td>\n",
       "      <td>N</td>\n",
       "      <td>N</td>\n",
       "      <td>0</td>\n",
       "      <td>270000.0</td>\n",
       "      <td>1293502.5</td>\n",
       "      <td>35698.5</td>\n",
       "      <td>1129500.0</td>\n",
       "      <td>Family</td>\n",
       "      <td>State servant</td>\n",
       "      <td>Higher education</td>\n",
       "      <td>Married</td>\n",
       "      <td>House / apartment</td>\n",
       "      <td>0.003541</td>\n",
       "      <td>-16765</td>\n",
       "      <td>-1188</td>\n",
       "      <td>-1186.0</td>\n",
       "      <td>-291</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>Core staff</td>\n",
       "      <td>2.0</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>0.0</td>\n",
       "      <td>45.900068</td>\n",
       "      <td>3.252567</td>\n",
       "      <td>3.247091</td>\n",
       "      <td>6.45-50</td>\n",
       "      <td>5885877.0</td>\n",
       "      <td>Closed</td>\n",
       "      <td>currency 1</td>\n",
       "      <td>-2586.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>-2434.0</td>\n",
       "      <td>-2131.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>22248.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>Consumer credit</td>\n",
       "      <td>-2131.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>100003</th>\n",
       "      <td>0</td>\n",
       "      <td>Cash loans</td>\n",
       "      <td>F</td>\n",
       "      <td>N</td>\n",
       "      <td>N</td>\n",
       "      <td>0</td>\n",
       "      <td>270000.0</td>\n",
       "      <td>1293502.5</td>\n",
       "      <td>35698.5</td>\n",
       "      <td>1129500.0</td>\n",
       "      <td>Family</td>\n",
       "      <td>State servant</td>\n",
       "      <td>Higher education</td>\n",
       "      <td>Married</td>\n",
       "      <td>House / apartment</td>\n",
       "      <td>0.003541</td>\n",
       "      <td>-16765</td>\n",
       "      <td>-1188</td>\n",
       "      <td>-1186.0</td>\n",
       "      <td>-291</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>Core staff</td>\n",
       "      <td>2.0</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>0.0</td>\n",
       "      <td>45.900068</td>\n",
       "      <td>3.252567</td>\n",
       "      <td>3.247091</td>\n",
       "      <td>6.45-50</td>\n",
       "      <td>5885878.0</td>\n",
       "      <td>Closed</td>\n",
       "      <td>currency 1</td>\n",
       "      <td>-1636.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>-540.0</td>\n",
       "      <td>-540.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>112500.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>Credit card</td>\n",
       "      <td>-540.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "            target name_contract_type code_gender flag_own_car  \\\n",
       "sk_id_curr                                                       \n",
       "100003           0         Cash loans           F            N   \n",
       "100003           0         Cash loans           F            N   \n",
       "\n",
       "           flag_own_realty  cnt_children  amt_income_total  amt_credit  \\\n",
       "sk_id_curr                                                               \n",
       "100003                   N             0          270000.0   1293502.5   \n",
       "100003                   N             0          270000.0   1293502.5   \n",
       "\n",
       "            amt_annuity_app  amt_goods_price name_type_suite name_income_type  \\\n",
       "sk_id_curr                                                                      \n",
       "100003              35698.5        1129500.0          Family    State servant   \n",
       "100003              35698.5        1129500.0          Family    State servant   \n",
       "\n",
       "           name_education_type name_family_status  name_housing_type  \\\n",
       "sk_id_curr                                                             \n",
       "100003        Higher education            Married  House / apartment   \n",
       "100003        Higher education            Married  House / apartment   \n",
       "\n",
       "            region_population_relative  days_birth  days_employed  \\\n",
       "sk_id_curr                                                          \n",
       "100003                        0.003541      -16765          -1188   \n",
       "100003                        0.003541      -16765          -1188   \n",
       "\n",
       "            days_registration  days_id_publish  own_car_age  flag_mobil  \\\n",
       "sk_id_curr                                                                \n",
       "100003                -1186.0             -291          NaN           1   \n",
       "100003                -1186.0             -291          NaN           1   \n",
       "\n",
       "            flag_emp_phone  flag_work_phone  flag_cont_mobile  flag_phone  \\\n",
       "sk_id_curr                                                                  \n",
       "100003                   1                0                 1           1   \n",
       "100003                   1                0                 1           1   \n",
       "\n",
       "            flag_email occupation_type  cnt_fam_members  \\\n",
       "sk_id_curr                                                \n",
       "100003               0      Core staff              2.0   \n",
       "100003               0      Core staff              2.0   \n",
       "\n",
       "            amt_req_credit_bureau_hour  amt_req_credit_bureau_day  \\\n",
       "sk_id_curr                                                          \n",
       "100003                             0.0                        0.0   \n",
       "100003                             0.0                        0.0   \n",
       "\n",
       "            amt_req_credit_bureau_week  amt_req_credit_bureau_mon  \\\n",
       "sk_id_curr                                                          \n",
       "100003                             0.0                        0.0   \n",
       "100003                             0.0                        0.0   \n",
       "\n",
       "            amt_req_credit_bureau_qrt  amt_req_credit_bureau_year  \\\n",
       "sk_id_curr                                                          \n",
       "100003                            0.0                         0.0   \n",
       "100003                            0.0                         0.0   \n",
       "\n",
       "            days_birth_years  days_employed_years  days_registration_years  \\\n",
       "sk_id_curr                                                                   \n",
       "100003             45.900068             3.252567                 3.247091   \n",
       "100003             45.900068             3.252567                 3.247091   \n",
       "\n",
       "           age_bins  sk_id_bureau credit_active credit_currency  days_credit  \\\n",
       "sk_id_curr                                                                     \n",
       "100003      6.45-50     5885877.0        Closed      currency 1      -2586.0   \n",
       "100003      6.45-50     5885878.0        Closed      currency 1      -1636.0   \n",
       "\n",
       "            credit_day_overdue  days_credit_enddate  days_enddate_fact  \\\n",
       "sk_id_curr                                                               \n",
       "100003                     0.0              -2434.0            -2131.0   \n",
       "100003                     0.0               -540.0             -540.0   \n",
       "\n",
       "            amt_credit_max_overdue  cnt_credit_prolong  amt_credit_sum  \\\n",
       "sk_id_curr                                                               \n",
       "100003                         0.0                 0.0         22248.0   \n",
       "100003                         0.0                 0.0        112500.0   \n",
       "\n",
       "            amt_credit_sum_debt  amt_credit_sum_limit  amt_credit_sum_overdue  \\\n",
       "sk_id_curr                                                                      \n",
       "100003                      0.0                   0.0                     0.0   \n",
       "100003                      0.0                   0.0                     0.0   \n",
       "\n",
       "                credit_type  days_credit_update  amt_annuity_bureau  \n",
       "sk_id_curr                                                           \n",
       "100003      Consumer credit             -2131.0                 NaN  \n",
       "100003          Credit card              -540.0                 NaN  "
      ]
     },
     "execution_count": 30,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "d_join = df.join(db, lsuffix = '_app', rsuffix = '_bureau')\n",
    "d_join.head(2)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "id": "98e96638-8419-4b56-ad87-c42eae22e608",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(151663, 55)"
      ]
     },
     "execution_count": 31,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "d_join.shape"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "d06a59ac-7c37-4213-8952-1b37d83d5614",
   "metadata": {},
   "source": [
    "### Pandasql"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "a8322bcb-e495-4155-b0dd-140fc1a176e0",
   "metadata": {},
   "source": [
    "Установка пакета"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "id": "f43a1132-ffe6-41d5-ad90-1a7e7bd964d4",
   "metadata": {},
   "outputs": [],
   "source": [
    "import sys"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "id": "a410c7af-9830-4efb-bcff-09a3970d2bb0",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Collecting pandasql\n",
      "  Using cached pandasql-0.7.3.tar.gz (26 kB)\n",
      "  Preparing metadata (setup.py) ... \u001b[?25ldone\n",
      "\u001b[?25hRequirement already satisfied: numpy in /Users/annarusinova/opt/anaconda3/lib/python3.9/site-packages (from pandasql) (1.21.5)\n",
      "Requirement already satisfied: pandas in /Users/annarusinova/opt/anaconda3/lib/python3.9/site-packages (from pandasql) (1.4.4)\n",
      "Requirement already satisfied: sqlalchemy in /Users/annarusinova/opt/anaconda3/lib/python3.9/site-packages (from pandasql) (1.4.39)\n",
      "Requirement already satisfied: python-dateutil>=2.8.1 in /Users/annarusinova/opt/anaconda3/lib/python3.9/site-packages (from pandas->pandasql) (2.8.2)\n",
      "Requirement already satisfied: pytz>=2020.1 in /Users/annarusinova/opt/anaconda3/lib/python3.9/site-packages (from pandas->pandasql) (2022.1)\n",
      "Requirement already satisfied: greenlet!=0.4.17 in /Users/annarusinova/opt/anaconda3/lib/python3.9/site-packages (from sqlalchemy->pandasql) (1.1.1)\n",
      "Requirement already satisfied: six>=1.5 in /Users/annarusinova/opt/anaconda3/lib/python3.9/site-packages (from python-dateutil>=2.8.1->pandas->pandasql) (1.16.0)\n",
      "Building wheels for collected packages: pandasql\n",
      "  Building wheel for pandasql (setup.py) ... \u001b[?25ldone\n",
      "\u001b[?25h  Created wheel for pandasql: filename=pandasql-0.7.3-py3-none-any.whl size=26768 sha256=11e6fc6dbff8c0942da371d2a9fb50484ca1a801d777ea444c235488d932036f\n",
      "  Stored in directory: /Users/annarusinova/Library/Caches/pip/wheels/63/e8/ec/75b1df467ecf57b6ececb32cb16f4e86697cbfe55cb0c51f07\n",
      "Successfully built pandasql\n",
      "Installing collected packages: pandasql\n",
      "Successfully installed pandasql-0.7.3\n"
     ]
    }
   ],
   "source": [
    "!pip install pandasql"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "id": "3eb39e0a-6c59-444a-9d37-c6e2bd138c7d",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Collecting sqlalchemy==1.4.46\n",
      "  Downloading SQLAlchemy-1.4.46.tar.gz (8.5 MB)\n",
      "\u001b[2K     \u001b[90m━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━\u001b[0m \u001b[32m8.5/8.5 MB\u001b[0m \u001b[31m7.9 MB/s\u001b[0m eta \u001b[36m0:00:00\u001b[0m00:01\u001b[0m00:01\u001b[0m\n",
      "\u001b[?25h  Preparing metadata (setup.py) ... \u001b[?25ldone\n",
      "\u001b[?25hRequirement already satisfied: greenlet!=0.4.17 in /Users/annarusinova/opt/anaconda3/lib/python3.9/site-packages (from sqlalchemy==1.4.46) (1.1.1)\n",
      "Building wheels for collected packages: sqlalchemy\n",
      "  Building wheel for sqlalchemy (setup.py) ... \u001b[?25ldone\n",
      "\u001b[?25h  Created wheel for sqlalchemy: filename=SQLAlchemy-1.4.46-cp39-cp39-macosx_10_9_x86_64.whl size=1559992 sha256=250d02e4d9195fdc627ef37add6a0dcf9e41ea749cc1dcfa06fefc6e46006a4b\n",
      "  Stored in directory: /Users/annarusinova/Library/Caches/pip/wheels/3c/99/65/57cf5a0ec6e7f3b803a68d31694501e168997e03e80adc903d\n",
      "Successfully built sqlalchemy\n",
      "Installing collected packages: sqlalchemy\n",
      "  Attempting uninstall: sqlalchemy\n",
      "    Found existing installation: SQLAlchemy 1.4.39\n",
      "    Uninstalling SQLAlchemy-1.4.39:\n",
      "      Successfully uninstalled SQLAlchemy-1.4.39\n",
      "Successfully installed sqlalchemy-1.4.46\n"
     ]
    }
   ],
   "source": [
    "#возможно потребуется переустановка библиотеки sqlalchemy на более старую версию для корректной работы\n",
    "!pip install sqlalchemy==1.4.46"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "f4aef43f-2edd-4c7a-913a-e754d4f44aac",
   "metadata": {},
   "source": [
    "Работа с pandasql"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "5cf60f8a-84c2-46fd-ae3a-c037aa371021",
   "metadata": {},
   "source": [
    "https://www.sql-ex.ru/?Lang=0"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "6e5ea5cb-e3ca-4f34-ab2f-1d70b7257507",
   "metadata": {},
   "source": [
    "#### Отобрать столбцы"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 40,
   "id": "b883a53d-97b6-4969-a49a-912481b3923f",
   "metadata": {},
   "outputs": [],
   "source": [
    "from pandasql import sqldf"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 47,
   "id": "7ffd5454-552f-46ef-a1fe-fe0b34f5c921",
   "metadata": {},
   "outputs": [],
   "source": [
    "#отобрать столбцы\n",
    "query = \"\"\"\n",
    "select target, name_contract_type\n",
    "from df\n",
    "\"\"\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 48,
   "id": "81c149fa-a33e-45cd-8a0b-cecdcf70d547",
   "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>target</th>\n",
       "      <th>name_contract_type</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0</td>\n",
       "      <td>Cash loans</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>0</td>\n",
       "      <td>Cash loans</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   target name_contract_type\n",
       "0       0         Cash loans\n",
       "1       0         Cash loans"
      ]
     },
     "execution_count": 48,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "dtest = sqldf(query)\n",
    "dtest.head(2)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 49,
   "id": "536dfcc3-902a-47b8-90f6-21f38447fa47",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "pandas.core.frame.DataFrame"
      ]
     },
     "execution_count": 49,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "type(dtest)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 50,
   "id": "5de89a73-d079-45e7-b097-d7f3501ffb17",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(30751, 2)"
      ]
     },
     "execution_count": 50,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "dtest.shape"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "99927f15-c969-4c59-a278-26b58416d23b",
   "metadata": {},
   "source": [
    "#### Условия "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 44,
   "id": "19e9c3e6-a2f0-41b1-8a22-6adcd9f7aa5d",
   "metadata": {},
   "outputs": [],
   "source": [
    "query = \"\"\"\n",
    "select target, name_contract_type\n",
    "from df\n",
    "where amt_credit > 200000\n",
    "    and days_birth_years > 30\n",
    "\"\"\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 45,
   "id": "2310fe28-e9c5-4654-821b-586fd44b2e0b",
   "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>target</th>\n",
       "      <th>name_contract_type</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0</td>\n",
       "      <td>Cash loans</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>0</td>\n",
       "      <td>Cash loans</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>0</td>\n",
       "      <td>Cash loans</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>0</td>\n",
       "      <td>Cash loans</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>0</td>\n",
       "      <td>Cash loans</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   target name_contract_type\n",
       "0       0         Cash loans\n",
       "1       0         Cash loans\n",
       "2       0         Cash loans\n",
       "3       0         Cash loans\n",
       "4       0         Cash loans"
      ]
     },
     "execution_count": 45,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "dtest = sqldf(query)\n",
    "dtest.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 46,
   "id": "e9a4b1b3-dbb8-4640-b631-1ad42d903444",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(23484, 2)"
      ]
     },
     "execution_count": 46,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "dtest.shape"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "e5aef2f1-a80b-4475-ae5c-2d5c1e4eeba6",
   "metadata": {},
   "source": [
    "#### Group by и агрегаты"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "id": "2430fc80-3f7b-44bb-b008-79fc59d41144",
   "metadata": {},
   "outputs": [],
   "source": [
    "#group by"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 53,
   "id": "caed9db7-35aa-49df-a9d3-f0beb5f626b2",
   "metadata": {},
   "outputs": [],
   "source": [
    "query = \"\"\"\n",
    "select name_contract_type, avg(days_birth_years) age_mean, max(amt_income_total) income_max\n",
    "from df\n",
    "group by name_contract_type\n",
    "\"\"\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 54,
   "id": "5bed57cb-f3d7-4146-8e0a-27f142211f02",
   "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>name_contract_type</th>\n",
       "      <th>age_mean</th>\n",
       "      <th>income_max</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Cash loans</td>\n",
       "      <td>44.186638</td>\n",
       "      <td>18000090.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Revolving loans</td>\n",
       "      <td>40.676315</td>\n",
       "      <td>2250000.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  name_contract_type   age_mean  income_max\n",
       "0         Cash loans  44.186638  18000090.0\n",
       "1    Revolving loans  40.676315   2250000.0"
      ]
     },
     "execution_count": 54,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "dtest = sqldf(query)\n",
    "dtest"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "a3d0beb2-53f1-4752-bb4c-c7000818acbc",
   "metadata": {},
   "source": [
    "#### Join"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 55,
   "id": "3ef6b017-21d4-48a6-a3dc-e7c63283f1e8",
   "metadata": {},
   "outputs": [],
   "source": [
    "query = \"\"\"\n",
    "select df.target,\n",
    "    df.amt_..\n",
    "    df.amt_annuity as amt_annuity_app\n",
    "    ...\n",
    "    db.amt_annuity as amt_annuity_bureau\n",
    "from df\n",
    "left join db\n",
    "on df.sk_id_curr = db.sk_id_curr\n",
    "\"\"\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 56,
   "id": "36967347-a093-49f1-a0ba-90810c20f8ed",
   "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>sk_id_curr</th>\n",
       "      <th>target</th>\n",
       "      <th>name_contract_type</th>\n",
       "      <th>code_gender</th>\n",
       "      <th>flag_own_car</th>\n",
       "      <th>flag_own_realty</th>\n",
       "      <th>cnt_children</th>\n",
       "      <th>amt_income_total</th>\n",
       "      <th>amt_credit</th>\n",
       "      <th>amt_annuity</th>\n",
       "      <th>amt_goods_price</th>\n",
       "      <th>name_type_suite</th>\n",
       "      <th>name_income_type</th>\n",
       "      <th>name_education_type</th>\n",
       "      <th>name_family_status</th>\n",
       "      <th>name_housing_type</th>\n",
       "      <th>region_population_relative</th>\n",
       "      <th>days_birth</th>\n",
       "      <th>days_employed</th>\n",
       "      <th>days_registration</th>\n",
       "      <th>days_id_publish</th>\n",
       "      <th>own_car_age</th>\n",
       "      <th>flag_mobil</th>\n",
       "      <th>flag_emp_phone</th>\n",
       "      <th>flag_work_phone</th>\n",
       "      <th>flag_cont_mobile</th>\n",
       "      <th>flag_phone</th>\n",
       "      <th>flag_email</th>\n",
       "      <th>occupation_type</th>\n",
       "      <th>cnt_fam_members</th>\n",
       "      <th>amt_req_credit_bureau_hour</th>\n",
       "      <th>amt_req_credit_bureau_day</th>\n",
       "      <th>amt_req_credit_bureau_week</th>\n",
       "      <th>amt_req_credit_bureau_mon</th>\n",
       "      <th>amt_req_credit_bureau_qrt</th>\n",
       "      <th>amt_req_credit_bureau_year</th>\n",
       "      <th>days_birth_years</th>\n",
       "      <th>days_employed_years</th>\n",
       "      <th>days_registration_years</th>\n",
       "      <th>age_bins</th>\n",
       "      <th>sk_id_curr</th>\n",
       "      <th>sk_id_bureau</th>\n",
       "      <th>credit_active</th>\n",
       "      <th>credit_currency</th>\n",
       "      <th>days_credit</th>\n",
       "      <th>credit_day_overdue</th>\n",
       "      <th>days_credit_enddate</th>\n",
       "      <th>days_enddate_fact</th>\n",
       "      <th>amt_credit_max_overdue</th>\n",
       "      <th>cnt_credit_prolong</th>\n",
       "      <th>amt_credit_sum</th>\n",
       "      <th>amt_credit_sum_debt</th>\n",
       "      <th>amt_credit_sum_limit</th>\n",
       "      <th>amt_credit_sum_overdue</th>\n",
       "      <th>credit_type</th>\n",
       "      <th>days_credit_update</th>\n",
       "      <th>amt_annuity</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>163465</td>\n",
       "      <td>0</td>\n",
       "      <td>Cash loans</td>\n",
       "      <td>F</td>\n",
       "      <td>N</td>\n",
       "      <td>Y</td>\n",
       "      <td>0</td>\n",
       "      <td>54000.0</td>\n",
       "      <td>50940.0</td>\n",
       "      <td>5166.0</td>\n",
       "      <td>45000.0</td>\n",
       "      <td>Unaccompanied</td>\n",
       "      <td>State servant</td>\n",
       "      <td>Secondary / secondary special</td>\n",
       "      <td>Married</td>\n",
       "      <td>House / apartment</td>\n",
       "      <td>0.031329</td>\n",
       "      <td>-18735</td>\n",
       "      <td>-5347</td>\n",
       "      <td>-6359.0</td>\n",
       "      <td>-2115</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>Cooking staff</td>\n",
       "      <td>2.0</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.0</td>\n",
       "      <td>51.293634</td>\n",
       "      <td>14.639288</td>\n",
       "      <td>17.409993</td>\n",
       "      <td>7.50+</td>\n",
       "      <td>163465.0</td>\n",
       "      <td>5540129.0</td>\n",
       "      <td>Active</td>\n",
       "      <td>currency 1</td>\n",
       "      <td>-63.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>302.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.0</td>\n",
       "      <td>90000.0</td>\n",
       "      <td>90000.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>Consumer credit</td>\n",
       "      <td>-60.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>163465</td>\n",
       "      <td>0</td>\n",
       "      <td>Cash loans</td>\n",
       "      <td>F</td>\n",
       "      <td>N</td>\n",
       "      <td>Y</td>\n",
       "      <td>0</td>\n",
       "      <td>54000.0</td>\n",
       "      <td>50940.0</td>\n",
       "      <td>5166.0</td>\n",
       "      <td>45000.0</td>\n",
       "      <td>Unaccompanied</td>\n",
       "      <td>State servant</td>\n",
       "      <td>Secondary / secondary special</td>\n",
       "      <td>Married</td>\n",
       "      <td>House / apartment</td>\n",
       "      <td>0.031329</td>\n",
       "      <td>-18735</td>\n",
       "      <td>-5347</td>\n",
       "      <td>-6359.0</td>\n",
       "      <td>-2115</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>Cooking staff</td>\n",
       "      <td>2.0</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.0</td>\n",
       "      <td>51.293634</td>\n",
       "      <td>14.639288</td>\n",
       "      <td>17.409993</td>\n",
       "      <td>7.50+</td>\n",
       "      <td>163465.0</td>\n",
       "      <td>5540130.0</td>\n",
       "      <td>Closed</td>\n",
       "      <td>currency 1</td>\n",
       "      <td>-571.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>-389.0</td>\n",
       "      <td>-389.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.0</td>\n",
       "      <td>110358.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.0</td>\n",
       "      <td>Consumer credit</td>\n",
       "      <td>-376.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   sk_id_curr  target name_contract_type code_gender flag_own_car  \\\n",
       "0      163465       0         Cash loans           F            N   \n",
       "1      163465       0         Cash loans           F            N   \n",
       "\n",
       "  flag_own_realty  cnt_children  amt_income_total  amt_credit  amt_annuity  \\\n",
       "0               Y             0           54000.0     50940.0       5166.0   \n",
       "1               Y             0           54000.0     50940.0       5166.0   \n",
       "\n",
       "   amt_goods_price name_type_suite name_income_type  \\\n",
       "0          45000.0   Unaccompanied    State servant   \n",
       "1          45000.0   Unaccompanied    State servant   \n",
       "\n",
       "             name_education_type name_family_status  name_housing_type  \\\n",
       "0  Secondary / secondary special            Married  House / apartment   \n",
       "1  Secondary / secondary special            Married  House / apartment   \n",
       "\n",
       "   region_population_relative  days_birth  days_employed  days_registration  \\\n",
       "0                    0.031329      -18735          -5347            -6359.0   \n",
       "1                    0.031329      -18735          -5347            -6359.0   \n",
       "\n",
       "   days_id_publish  own_car_age  flag_mobil  flag_emp_phone  flag_work_phone  \\\n",
       "0            -2115          NaN           1               1                0   \n",
       "1            -2115          NaN           1               1                0   \n",
       "\n",
       "   flag_cont_mobile  flag_phone  flag_email occupation_type  cnt_fam_members  \\\n",
       "0                 1           0           0   Cooking staff              2.0   \n",
       "1                 1           0           0   Cooking staff              2.0   \n",
       "\n",
       "   amt_req_credit_bureau_hour  amt_req_credit_bureau_day  \\\n",
       "0                         0.0                        0.0   \n",
       "1                         0.0                        0.0   \n",
       "\n",
       "   amt_req_credit_bureau_week  amt_req_credit_bureau_mon  \\\n",
       "0                         0.0                        0.0   \n",
       "1                         0.0                        0.0   \n",
       "\n",
       "   amt_req_credit_bureau_qrt  amt_req_credit_bureau_year  days_birth_years  \\\n",
       "0                        0.0                         2.0         51.293634   \n",
       "1                        0.0                         2.0         51.293634   \n",
       "\n",
       "   days_employed_years  days_registration_years age_bins  sk_id_curr  \\\n",
       "0            14.639288                17.409993    7.50+    163465.0   \n",
       "1            14.639288                17.409993    7.50+    163465.0   \n",
       "\n",
       "   sk_id_bureau credit_active credit_currency  days_credit  \\\n",
       "0     5540129.0        Active      currency 1        -63.0   \n",
       "1     5540130.0        Closed      currency 1       -571.0   \n",
       "\n",
       "   credit_day_overdue  days_credit_enddate  days_enddate_fact  \\\n",
       "0                 0.0                302.0                NaN   \n",
       "1                 0.0               -389.0             -389.0   \n",
       "\n",
       "   amt_credit_max_overdue  cnt_credit_prolong  amt_credit_sum  \\\n",
       "0                     NaN                 0.0         90000.0   \n",
       "1                     NaN                 0.0        110358.0   \n",
       "\n",
       "   amt_credit_sum_debt  amt_credit_sum_limit  amt_credit_sum_overdue  \\\n",
       "0              90000.0                   0.0                     0.0   \n",
       "1                  0.0                   NaN                     0.0   \n",
       "\n",
       "       credit_type  days_credit_update  amt_annuity  \n",
       "0  Consumer credit               -60.0          NaN  \n",
       "1  Consumer credit              -376.0          NaN  "
      ]
     },
     "execution_count": 56,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "dtest = sqldf(query)\n",
    "dtest.head(2)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 57,
   "id": "9eca9422-b949-486f-940a-e64d89fc35e7",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(151663, 57)"
      ]
     },
     "execution_count": 57,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "dtest.shape"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "7e308b5f-ed1c-485e-bc54-78057d4f5959",
   "metadata": {},
   "source": [
    "#### Время работы"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 58,
   "id": "ab839705-a5d2-4a38-ad65-1520de4fa743",
   "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>target</th>\n",
       "      <th>name_contract_type</th>\n",
       "      <th>code_gender</th>\n",
       "      <th>flag_own_car</th>\n",
       "      <th>flag_own_realty</th>\n",
       "      <th>cnt_children</th>\n",
       "      <th>amt_income_total</th>\n",
       "      <th>amt_credit</th>\n",
       "      <th>amt_annuity</th>\n",
       "      <th>amt_goods_price</th>\n",
       "      <th>name_type_suite</th>\n",
       "      <th>name_income_type</th>\n",
       "      <th>name_education_type</th>\n",
       "      <th>name_family_status</th>\n",
       "      <th>name_housing_type</th>\n",
       "      <th>region_population_relative</th>\n",
       "      <th>days_birth</th>\n",
       "      <th>days_employed</th>\n",
       "      <th>days_registration</th>\n",
       "      <th>days_id_publish</th>\n",
       "      <th>own_car_age</th>\n",
       "      <th>flag_mobil</th>\n",
       "      <th>flag_emp_phone</th>\n",
       "      <th>flag_work_phone</th>\n",
       "      <th>flag_cont_mobile</th>\n",
       "      <th>flag_phone</th>\n",
       "      <th>flag_email</th>\n",
       "      <th>occupation_type</th>\n",
       "      <th>cnt_fam_members</th>\n",
       "      <th>amt_req_credit_bureau_hour</th>\n",
       "      <th>amt_req_credit_bureau_day</th>\n",
       "      <th>amt_req_credit_bureau_week</th>\n",
       "      <th>amt_req_credit_bureau_mon</th>\n",
       "      <th>amt_req_credit_bureau_qrt</th>\n",
       "      <th>amt_req_credit_bureau_year</th>\n",
       "      <th>days_birth_years</th>\n",
       "      <th>days_employed_years</th>\n",
       "      <th>days_registration_years</th>\n",
       "      <th>age_bins</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>sk_id_curr</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",
       "      <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",
       "      <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",
       "      <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",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>163465</th>\n",
       "      <td>0</td>\n",
       "      <td>Cash loans</td>\n",
       "      <td>F</td>\n",
       "      <td>N</td>\n",
       "      <td>Y</td>\n",
       "      <td>0</td>\n",
       "      <td>54000.0</td>\n",
       "      <td>50940.0</td>\n",
       "      <td>5166.0</td>\n",
       "      <td>45000.0</td>\n",
       "      <td>Unaccompanied</td>\n",
       "      <td>State servant</td>\n",
       "      <td>Secondary / secondary special</td>\n",
       "      <td>Married</td>\n",
       "      <td>House / apartment</td>\n",
       "      <td>0.031329</td>\n",
       "      <td>-18735</td>\n",
       "      <td>-5347</td>\n",
       "      <td>-6359.0</td>\n",
       "      <td>-2115</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>Cooking staff</td>\n",
       "      <td>2.0</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.0</td>\n",
       "      <td>51.293634</td>\n",
       "      <td>14.639288</td>\n",
       "      <td>17.409993</td>\n",
       "      <td>7.50+</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>256730</th>\n",
       "      <td>0</td>\n",
       "      <td>Cash loans</td>\n",
       "      <td>M</td>\n",
       "      <td>N</td>\n",
       "      <td>N</td>\n",
       "      <td>0</td>\n",
       "      <td>58500.0</td>\n",
       "      <td>269550.0</td>\n",
       "      <td>10116.0</td>\n",
       "      <td>225000.0</td>\n",
       "      <td>Unaccompanied</td>\n",
       "      <td>Pensioner</td>\n",
       "      <td>Secondary / secondary special</td>\n",
       "      <td>Separated</td>\n",
       "      <td>House / apartment</td>\n",
       "      <td>0.020246</td>\n",
       "      <td>-23152</td>\n",
       "      <td>365243</td>\n",
       "      <td>-15551.0</td>\n",
       "      <td>-4795</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1.0</td>\n",
       "      <td>2.0</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>63.386721</td>\n",
       "      <td>999.980835</td>\n",
       "      <td>42.576318</td>\n",
       "      <td>7.50+</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "            target name_contract_type code_gender flag_own_car  \\\n",
       "sk_id_curr                                                       \n",
       "163465           0         Cash loans           F            N   \n",
       "256730           0         Cash loans           M            N   \n",
       "\n",
       "           flag_own_realty  cnt_children  amt_income_total  amt_credit  \\\n",
       "sk_id_curr                                                               \n",
       "163465                   Y             0           54000.0     50940.0   \n",
       "256730                   N             0           58500.0    269550.0   \n",
       "\n",
       "            amt_annuity  amt_goods_price name_type_suite name_income_type  \\\n",
       "sk_id_curr                                                                  \n",
       "163465           5166.0          45000.0   Unaccompanied    State servant   \n",
       "256730          10116.0         225000.0   Unaccompanied        Pensioner   \n",
       "\n",
       "                      name_education_type name_family_status  \\\n",
       "sk_id_curr                                                     \n",
       "163465      Secondary / secondary special            Married   \n",
       "256730      Secondary / secondary special          Separated   \n",
       "\n",
       "            name_housing_type  region_population_relative  days_birth  \\\n",
       "sk_id_curr                                                              \n",
       "163465      House / apartment                    0.031329      -18735   \n",
       "256730      House / apartment                    0.020246      -23152   \n",
       "\n",
       "            days_employed  days_registration  days_id_publish  own_car_age  \\\n",
       "sk_id_curr                                                                   \n",
       "163465              -5347            -6359.0            -2115          NaN   \n",
       "256730             365243           -15551.0            -4795          NaN   \n",
       "\n",
       "            flag_mobil  flag_emp_phone  flag_work_phone  flag_cont_mobile  \\\n",
       "sk_id_curr                                                                  \n",
       "163465               1               1                0                 1   \n",
       "256730               1               0                0                 1   \n",
       "\n",
       "            flag_phone  flag_email occupation_type  cnt_fam_members  \\\n",
       "sk_id_curr                                                            \n",
       "163465               0           0   Cooking staff              2.0   \n",
       "256730               0           0             NaN              1.0   \n",
       "\n",
       "            amt_req_credit_bureau_hour  amt_req_credit_bureau_day  \\\n",
       "sk_id_curr                                                          \n",
       "163465                             0.0                        0.0   \n",
       "256730                             2.0                        0.0   \n",
       "\n",
       "            amt_req_credit_bureau_week  amt_req_credit_bureau_mon  \\\n",
       "sk_id_curr                                                          \n",
       "163465                             0.0                        0.0   \n",
       "256730                             0.0                        0.0   \n",
       "\n",
       "            amt_req_credit_bureau_qrt  amt_req_credit_bureau_year  \\\n",
       "sk_id_curr                                                          \n",
       "163465                            0.0                         2.0   \n",
       "256730                            0.0                         0.0   \n",
       "\n",
       "            days_birth_years  days_employed_years  days_registration_years  \\\n",
       "sk_id_curr                                                                   \n",
       "163465             51.293634            14.639288                17.409993   \n",
       "256730             63.386721           999.980835                42.576318   \n",
       "\n",
       "           age_bins  \n",
       "sk_id_curr           \n",
       "163465        7.50+  \n",
       "256730        7.50+  "
      ]
     },
     "execution_count": 58,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.head(2)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 59,
   "id": "b08928e3-a36f-4042-8816-819054d6ed37",
   "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>sk_id_bureau</th>\n",
       "      <th>credit_active</th>\n",
       "      <th>credit_currency</th>\n",
       "      <th>days_credit</th>\n",
       "      <th>credit_day_overdue</th>\n",
       "      <th>days_credit_enddate</th>\n",
       "      <th>days_enddate_fact</th>\n",
       "      <th>amt_credit_max_overdue</th>\n",
       "      <th>cnt_credit_prolong</th>\n",
       "      <th>amt_credit_sum</th>\n",
       "      <th>amt_credit_sum_debt</th>\n",
       "      <th>amt_credit_sum_limit</th>\n",
       "      <th>amt_credit_sum_overdue</th>\n",
       "      <th>credit_type</th>\n",
       "      <th>days_credit_update</th>\n",
       "      <th>amt_annuity</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>sk_id_curr</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",
       "      <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>426155</th>\n",
       "      <td>5714498</td>\n",
       "      <td>Closed</td>\n",
       "      <td>currency 1</td>\n",
       "      <td>-1331</td>\n",
       "      <td>0</td>\n",
       "      <td>-994.0</td>\n",
       "      <td>-1023.0</td>\n",
       "      <td>1350.0</td>\n",
       "      <td>0</td>\n",
       "      <td>39433.5</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>Consumer credit</td>\n",
       "      <td>-1023</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>426155</th>\n",
       "      <td>5714499</td>\n",
       "      <td>Closed</td>\n",
       "      <td>currency 1</td>\n",
       "      <td>-2534</td>\n",
       "      <td>0</td>\n",
       "      <td>-2352.0</td>\n",
       "      <td>-2347.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "      <td>38830.5</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>Consumer credit</td>\n",
       "      <td>-2345</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "            sk_id_bureau credit_active credit_currency  days_credit  \\\n",
       "sk_id_curr                                                            \n",
       "426155           5714498        Closed      currency 1        -1331   \n",
       "426155           5714499        Closed      currency 1        -2534   \n",
       "\n",
       "            credit_day_overdue  days_credit_enddate  days_enddate_fact  \\\n",
       "sk_id_curr                                                               \n",
       "426155                       0               -994.0            -1023.0   \n",
       "426155                       0              -2352.0            -2347.0   \n",
       "\n",
       "            amt_credit_max_overdue  cnt_credit_prolong  amt_credit_sum  \\\n",
       "sk_id_curr                                                               \n",
       "426155                      1350.0                   0         39433.5   \n",
       "426155                         NaN                   0         38830.5   \n",
       "\n",
       "            amt_credit_sum_debt  amt_credit_sum_limit  amt_credit_sum_overdue  \\\n",
       "sk_id_curr                                                                      \n",
       "426155                      0.0                   0.0                     0.0   \n",
       "426155                      0.0                   0.0                     0.0   \n",
       "\n",
       "                credit_type  days_credit_update  amt_annuity  \n",
       "sk_id_curr                                                    \n",
       "426155      Consumer credit               -1023          NaN  \n",
       "426155      Consumer credit               -2345          NaN  "
      ]
     },
     "execution_count": 59,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "db.head(2)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 60,
   "id": "b8bc6d85-7f20-4cc9-a7a1-95970dddf004",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "CPU times: user 7.67 s, sys: 357 ms, total: 8.03 s\n",
      "Wall time: 8.07 s\n"
     ]
    }
   ],
   "source": [
    "%%time\n",
    "query = \"\"\"\n",
    "select *\n",
    "from df\n",
    "left join db\n",
    "on df.sk_id_curr = db.sk_id_curr\n",
    "\"\"\"\n",
    "\n",
    "dtest = sqldf(query)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 61,
   "id": "1a9cab70-3550-4255-b1f6-01378eeba34f",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "CPU times: user 73.5 ms, sys: 20.6 ms, total: 94.1 ms\n",
      "Wall time: 92.3 ms\n"
     ]
    }
   ],
   "source": [
    "%%time\n",
    "d_join = df.join(db, lsuffix = '_app', rsuffix = '_bureau')"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "61948a77-2851-406d-8e5c-cc2cb25b6e32",
   "metadata": {},
   "source": [
    "### Concat"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "0ee43c9f-45a3-4f6b-9e89-06320e3abd2c",
   "metadata": {},
   "source": [
    "https://pandas.pydata.org/pandas-docs/version/0.17/merging.html"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "id": "a6a4ddc6-d2da-48cf-94f0-0ed296a2aa28",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "((27815, 39), (2936, 39))"
      ]
     },
     "execution_count": 33,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# axis = 0 (вертикально)\n",
    "df_cash = df[df.name_contract_type == 'Cash loans']\n",
    "df_card = df[df.name_contract_type == 'Revolving loans']\n",
    "df_cash.shape, df_card.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "id": "aff501f4-9c77-4cbd-81d8-490ed98406f0",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(30751, 39)"
      ]
     },
     "execution_count": 34,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_concat = pd.concat([df_cash, df_card])\n",
    "df_concat.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "id": "3f1311c8-d746-4c52-8671-be7029377cf5",
   "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>target</th>\n",
       "      <th>name_contract_type</th>\n",
       "      <th>code_gender</th>\n",
       "      <th>flag_own_car</th>\n",
       "      <th>flag_own_realty</th>\n",
       "      <th>cnt_children</th>\n",
       "      <th>amt_income_total</th>\n",
       "      <th>amt_credit</th>\n",
       "      <th>amt_annuity</th>\n",
       "      <th>amt_goods_price</th>\n",
       "      <th>name_type_suite</th>\n",
       "      <th>name_income_type</th>\n",
       "      <th>name_education_type</th>\n",
       "      <th>name_family_status</th>\n",
       "      <th>name_housing_type</th>\n",
       "      <th>region_population_relative</th>\n",
       "      <th>days_birth</th>\n",
       "      <th>days_employed</th>\n",
       "      <th>days_registration</th>\n",
       "      <th>days_id_publish</th>\n",
       "      <th>own_car_age</th>\n",
       "      <th>flag_mobil</th>\n",
       "      <th>flag_emp_phone</th>\n",
       "      <th>flag_work_phone</th>\n",
       "      <th>flag_cont_mobile</th>\n",
       "      <th>flag_phone</th>\n",
       "      <th>flag_email</th>\n",
       "      <th>occupation_type</th>\n",
       "      <th>cnt_fam_members</th>\n",
       "      <th>amt_req_credit_bureau_hour</th>\n",
       "      <th>amt_req_credit_bureau_day</th>\n",
       "      <th>amt_req_credit_bureau_week</th>\n",
       "      <th>amt_req_credit_bureau_mon</th>\n",
       "      <th>amt_req_credit_bureau_qrt</th>\n",
       "      <th>amt_req_credit_bureau_year</th>\n",
       "      <th>days_birth_years</th>\n",
       "      <th>days_employed_years</th>\n",
       "      <th>days_registration_years</th>\n",
       "      <th>age_bins</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>sk_id_curr</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",
       "      <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",
       "      <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",
       "      <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",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>163465</th>\n",
       "      <td>0</td>\n",
       "      <td>Cash loans</td>\n",
       "      <td>F</td>\n",
       "      <td>N</td>\n",
       "      <td>Y</td>\n",
       "      <td>0</td>\n",
       "      <td>54000.0</td>\n",
       "      <td>50940.0</td>\n",
       "      <td>5166.0</td>\n",
       "      <td>45000.0</td>\n",
       "      <td>Unaccompanied</td>\n",
       "      <td>State servant</td>\n",
       "      <td>Secondary / secondary special</td>\n",
       "      <td>Married</td>\n",
       "      <td>House / apartment</td>\n",
       "      <td>0.031329</td>\n",
       "      <td>-18735</td>\n",
       "      <td>-5347</td>\n",
       "      <td>-6359.0</td>\n",
       "      <td>-2115</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>Cooking staff</td>\n",
       "      <td>2.0</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.0</td>\n",
       "      <td>51.293634</td>\n",
       "      <td>14.639288</td>\n",
       "      <td>17.409993</td>\n",
       "      <td>7.50+</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>256730</th>\n",
       "      <td>0</td>\n",
       "      <td>Cash loans</td>\n",
       "      <td>M</td>\n",
       "      <td>N</td>\n",
       "      <td>N</td>\n",
       "      <td>0</td>\n",
       "      <td>58500.0</td>\n",
       "      <td>269550.0</td>\n",
       "      <td>10116.0</td>\n",
       "      <td>225000.0</td>\n",
       "      <td>Unaccompanied</td>\n",
       "      <td>Pensioner</td>\n",
       "      <td>Secondary / secondary special</td>\n",
       "      <td>Separated</td>\n",
       "      <td>House / apartment</td>\n",
       "      <td>0.020246</td>\n",
       "      <td>-23152</td>\n",
       "      <td>365243</td>\n",
       "      <td>-15551.0</td>\n",
       "      <td>-4795</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1.0</td>\n",
       "      <td>2.0</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>63.386721</td>\n",
       "      <td>999.980835</td>\n",
       "      <td>42.576318</td>\n",
       "      <td>7.50+</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "            target name_contract_type code_gender flag_own_car  \\\n",
       "sk_id_curr                                                       \n",
       "163465           0         Cash loans           F            N   \n",
       "256730           0         Cash loans           M            N   \n",
       "\n",
       "           flag_own_realty  cnt_children  amt_income_total  amt_credit  \\\n",
       "sk_id_curr                                                               \n",
       "163465                   Y             0           54000.0     50940.0   \n",
       "256730                   N             0           58500.0    269550.0   \n",
       "\n",
       "            amt_annuity  amt_goods_price name_type_suite name_income_type  \\\n",
       "sk_id_curr                                                                  \n",
       "163465           5166.0          45000.0   Unaccompanied    State servant   \n",
       "256730          10116.0         225000.0   Unaccompanied        Pensioner   \n",
       "\n",
       "                      name_education_type name_family_status  \\\n",
       "sk_id_curr                                                     \n",
       "163465      Secondary / secondary special            Married   \n",
       "256730      Secondary / secondary special          Separated   \n",
       "\n",
       "            name_housing_type  region_population_relative  days_birth  \\\n",
       "sk_id_curr                                                              \n",
       "163465      House / apartment                    0.031329      -18735   \n",
       "256730      House / apartment                    0.020246      -23152   \n",
       "\n",
       "            days_employed  days_registration  days_id_publish  own_car_age  \\\n",
       "sk_id_curr                                                                   \n",
       "163465              -5347            -6359.0            -2115          NaN   \n",
       "256730             365243           -15551.0            -4795          NaN   \n",
       "\n",
       "            flag_mobil  flag_emp_phone  flag_work_phone  flag_cont_mobile  \\\n",
       "sk_id_curr                                                                  \n",
       "163465               1               1                0                 1   \n",
       "256730               1               0                0                 1   \n",
       "\n",
       "            flag_phone  flag_email occupation_type  cnt_fam_members  \\\n",
       "sk_id_curr                                                            \n",
       "163465               0           0   Cooking staff              2.0   \n",
       "256730               0           0             NaN              1.0   \n",
       "\n",
       "            amt_req_credit_bureau_hour  amt_req_credit_bureau_day  \\\n",
       "sk_id_curr                                                          \n",
       "163465                             0.0                        0.0   \n",
       "256730                             2.0                        0.0   \n",
       "\n",
       "            amt_req_credit_bureau_week  amt_req_credit_bureau_mon  \\\n",
       "sk_id_curr                                                          \n",
       "163465                             0.0                        0.0   \n",
       "256730                             0.0                        0.0   \n",
       "\n",
       "            amt_req_credit_bureau_qrt  amt_req_credit_bureau_year  \\\n",
       "sk_id_curr                                                          \n",
       "163465                            0.0                         2.0   \n",
       "256730                            0.0                         0.0   \n",
       "\n",
       "            days_birth_years  days_employed_years  days_registration_years  \\\n",
       "sk_id_curr                                                                   \n",
       "163465             51.293634            14.639288                17.409993   \n",
       "256730             63.386721           999.980835                42.576318   \n",
       "\n",
       "           age_bins  \n",
       "sk_id_curr           \n",
       "163465        7.50+  \n",
       "256730        7.50+  "
      ]
     },
     "execution_count": 35,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# axis = 1 (горизонтально)\n",
    "# удобно конкатенировать при наличии одинаковых индексов\n",
    "df.head(2)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "id": "5c1be23b-91ed-4fe8-9263-9e766da634a6",
   "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>sk_id_bureau</th>\n",
       "      <th>credit_active</th>\n",
       "      <th>credit_currency</th>\n",
       "      <th>days_credit</th>\n",
       "      <th>credit_day_overdue</th>\n",
       "      <th>days_credit_enddate</th>\n",
       "      <th>days_enddate_fact</th>\n",
       "      <th>amt_credit_max_overdue</th>\n",
       "      <th>cnt_credit_prolong</th>\n",
       "      <th>amt_credit_sum</th>\n",
       "      <th>amt_credit_sum_debt</th>\n",
       "      <th>amt_credit_sum_limit</th>\n",
       "      <th>amt_credit_sum_overdue</th>\n",
       "      <th>credit_type</th>\n",
       "      <th>days_credit_update</th>\n",
       "      <th>amt_annuity</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>sk_id_curr</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",
       "      <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>426155</th>\n",
       "      <td>5714498</td>\n",
       "      <td>Closed</td>\n",
       "      <td>currency 1</td>\n",
       "      <td>-1331</td>\n",
       "      <td>0</td>\n",
       "      <td>-994.0</td>\n",
       "      <td>-1023.0</td>\n",
       "      <td>1350.0</td>\n",
       "      <td>0</td>\n",
       "      <td>39433.5</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>Consumer credit</td>\n",
       "      <td>-1023</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>426155</th>\n",
       "      <td>5714499</td>\n",
       "      <td>Closed</td>\n",
       "      <td>currency 1</td>\n",
       "      <td>-2534</td>\n",
       "      <td>0</td>\n",
       "      <td>-2352.0</td>\n",
       "      <td>-2347.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "      <td>38830.5</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>Consumer credit</td>\n",
       "      <td>-2345</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "            sk_id_bureau credit_active credit_currency  days_credit  \\\n",
       "sk_id_curr                                                            \n",
       "426155           5714498        Closed      currency 1        -1331   \n",
       "426155           5714499        Closed      currency 1        -2534   \n",
       "\n",
       "            credit_day_overdue  days_credit_enddate  days_enddate_fact  \\\n",
       "sk_id_curr                                                               \n",
       "426155                       0               -994.0            -1023.0   \n",
       "426155                       0              -2352.0            -2347.0   \n",
       "\n",
       "            amt_credit_max_overdue  cnt_credit_prolong  amt_credit_sum  \\\n",
       "sk_id_curr                                                               \n",
       "426155                      1350.0                   0         39433.5   \n",
       "426155                         NaN                   0         38830.5   \n",
       "\n",
       "            amt_credit_sum_debt  amt_credit_sum_limit  amt_credit_sum_overdue  \\\n",
       "sk_id_curr                                                                      \n",
       "426155                      0.0                   0.0                     0.0   \n",
       "426155                      0.0                   0.0                     0.0   \n",
       "\n",
       "                credit_type  days_credit_update  amt_annuity  \n",
       "sk_id_curr                                                    \n",
       "426155      Consumer credit               -1023          NaN  \n",
       "426155      Consumer credit               -2345          NaN  "
      ]
     },
     "execution_count": 36,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "db.head(2)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "id": "c86d33e8-4957-4249-bb68-0087481a2009",
   "metadata": {
    "collapsed": true,
    "jupyter": {
     "outputs_hidden": true
    },
    "tags": []
   },
   "outputs": [
    {
     "ename": "InvalidIndexError",
     "evalue": "Reindexing only valid with uniquely valued Index objects",
     "output_type": "error",
     "traceback": [
      "\u001b[0;31m---------------------------------------------------------------------------\u001b[0m",
      "\u001b[0;31mInvalidIndexError\u001b[0m                         Traceback (most recent call last)",
      "\u001b[0;32m/var/folders/b6/jwrkyjdn0vj5dzwm6918d7g00000gn/T/ipykernel_32247/2444971314.py\u001b[0m in \u001b[0;36m<module>\u001b[0;34m\u001b[0m\n\u001b[0;32m----> 1\u001b[0;31m \u001b[0md_concat\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mpd\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mconcat\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m[\u001b[0m\u001b[0mdf\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mdb\u001b[0m\u001b[0;34m]\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0maxis\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0;36m1\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m      2\u001b[0m \u001b[0md_concat\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mshape\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;32m~/opt/anaconda3/lib/python3.9/site-packages/pandas/util/_decorators.py\u001b[0m in \u001b[0;36mwrapper\u001b[0;34m(*args, **kwargs)\u001b[0m\n\u001b[1;32m    309\u001b[0m                     \u001b[0mstacklevel\u001b[0m\u001b[0;34m=\u001b[0m\u001b[0mstacklevel\u001b[0m\u001b[0;34m,\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    310\u001b[0m                 )\n\u001b[0;32m--> 311\u001b[0;31m             \u001b[0;32mreturn\u001b[0m \u001b[0mfunc\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m*\u001b[0m\u001b[0margs\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0;34m**\u001b[0m\u001b[0mkwargs\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m    312\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    313\u001b[0m         \u001b[0;32mreturn\u001b[0m \u001b[0mwrapper\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;32m~/opt/anaconda3/lib/python3.9/site-packages/pandas/core/reshape/concat.py\u001b[0m in \u001b[0;36mconcat\u001b[0;34m(objs, axis, join, ignore_index, keys, levels, names, verify_integrity, sort, copy)\u001b[0m\n\u001b[1;32m    358\u001b[0m     )\n\u001b[1;32m    359\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m--> 360\u001b[0;31m     \u001b[0;32mreturn\u001b[0m \u001b[0mop\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mget_result\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m    361\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    362\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;32m~/opt/anaconda3/lib/python3.9/site-packages/pandas/core/reshape/concat.py\u001b[0m in \u001b[0;36mget_result\u001b[0;34m(self)\u001b[0m\n\u001b[1;32m    589\u001b[0m                     \u001b[0mobj_labels\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mobj\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0maxes\u001b[0m\u001b[0;34m[\u001b[0m\u001b[0;36m1\u001b[0m \u001b[0;34m-\u001b[0m \u001b[0max\u001b[0m\u001b[0;34m]\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    590\u001b[0m                     \u001b[0;32mif\u001b[0m \u001b[0;32mnot\u001b[0m \u001b[0mnew_labels\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mequals\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mobj_labels\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m--> 591\u001b[0;31m                         \u001b[0mindexers\u001b[0m\u001b[0;34m[\u001b[0m\u001b[0max\u001b[0m\u001b[0;34m]\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mobj_labels\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mget_indexer\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mnew_labels\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m    592\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    593\u001b[0m                 \u001b[0mmgrs_indexers\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mappend\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mobj\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0m_mgr\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mindexers\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;32m~/opt/anaconda3/lib/python3.9/site-packages/pandas/core/indexes/base.py\u001b[0m in \u001b[0;36mget_indexer\u001b[0;34m(self, target, method, limit, tolerance)\u001b[0m\n\u001b[1;32m   3727\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m   3728\u001b[0m         \u001b[0;32mif\u001b[0m \u001b[0;32mnot\u001b[0m \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0m_index_as_unique\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m-> 3729\u001b[0;31m             \u001b[0;32mraise\u001b[0m \u001b[0mInvalidIndexError\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0m_requires_unique_msg\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m   3730\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m   3731\u001b[0m         \u001b[0;32mif\u001b[0m \u001b[0mlen\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mtarget\u001b[0m\u001b[0;34m)\u001b[0m \u001b[0;34m==\u001b[0m \u001b[0;36m0\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;31mInvalidIndexError\u001b[0m: Reindexing only valid with uniquely valued Index objects"
     ]
    }
   ],
   "source": [
    "d_concat = pd.concat([df, db], axis = 1)\n",
    "d_concat.shape"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "ee1869e0-ca7a-4a79-9271-465a189ec261",
   "metadata": {
    "tags": []
   },
   "source": [
    "### Задачa\n",
    "\n",
    "На основе данных bureau сформируйте осмысленные агрегаты в разрезе sk_id_curr и добавьте эти данные к заявочным данным (application data)."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "ee1d159a-7d66-495b-98fc-58bef6b14cf6",
   "metadata": {},
   "source": [
    "#### Способ 1"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "id": "e1e1bfe3-eca7-4ffd-93d4-680ad5cf1fd1",
   "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>sk_id_curr</th>\n",
       "      <th>sk_id_bureau</th>\n",
       "      <th>credit_active</th>\n",
       "      <th>credit_currency</th>\n",
       "      <th>days_credit</th>\n",
       "      <th>credit_day_overdue</th>\n",
       "      <th>days_credit_enddate</th>\n",
       "      <th>days_enddate_fact</th>\n",
       "      <th>amt_credit_max_overdue</th>\n",
       "      <th>cnt_credit_prolong</th>\n",
       "      <th>amt_credit_sum</th>\n",
       "      <th>amt_credit_sum_debt</th>\n",
       "      <th>amt_credit_sum_limit</th>\n",
       "      <th>amt_credit_sum_overdue</th>\n",
       "      <th>credit_type</th>\n",
       "      <th>days_credit_update</th>\n",
       "      <th>amt_annuity</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>426155</td>\n",
       "      <td>5714498</td>\n",
       "      <td>Closed</td>\n",
       "      <td>currency 1</td>\n",
       "      <td>-1331</td>\n",
       "      <td>0</td>\n",
       "      <td>-994.0</td>\n",
       "      <td>-1023.0</td>\n",
       "      <td>1350.0</td>\n",
       "      <td>0</td>\n",
       "      <td>39433.5</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>Consumer credit</td>\n",
       "      <td>-1023</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>426155</td>\n",
       "      <td>5714499</td>\n",
       "      <td>Closed</td>\n",
       "      <td>currency 1</td>\n",
       "      <td>-2534</td>\n",
       "      <td>0</td>\n",
       "      <td>-2352.0</td>\n",
       "      <td>-2347.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "      <td>38830.5</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>Consumer credit</td>\n",
       "      <td>-2345</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   sk_id_curr  sk_id_bureau credit_active credit_currency  days_credit  \\\n",
       "0      426155       5714498        Closed      currency 1        -1331   \n",
       "1      426155       5714499        Closed      currency 1        -2534   \n",
       "\n",
       "   credit_day_overdue  days_credit_enddate  days_enddate_fact  \\\n",
       "0                   0               -994.0            -1023.0   \n",
       "1                   0              -2352.0            -2347.0   \n",
       "\n",
       "   amt_credit_max_overdue  cnt_credit_prolong  amt_credit_sum  \\\n",
       "0                  1350.0                   0         39433.5   \n",
       "1                     NaN                   0         38830.5   \n",
       "\n",
       "   amt_credit_sum_debt  amt_credit_sum_limit  amt_credit_sum_overdue  \\\n",
       "0                  0.0                   0.0                     0.0   \n",
       "1                  0.0                   0.0                     0.0   \n",
       "\n",
       "       credit_type  days_credit_update  amt_annuity  \n",
       "0  Consumer credit               -1023          NaN  \n",
       "1  Consumer credit               -2345          NaN  "
      ]
     },
     "execution_count": 38,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "db = db.reset_index()\n",
    "db.head(2)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 39,
   "id": "d2a10352-8c3e-4215-a987-c56032b108bc",
   "metadata": {},
   "outputs": [],
   "source": [
    "db_days_credit = db.groupby('sk_id_curr')['days_credit'].max().rename('days_credit_max')\n",
    "db_credit_day_overdue = db.groupby('sk_id_curr')['credit_day_overdue'].max().rename('day_overdue_max')\n",
    "db_days_credit_enddate = db.groupby('sk_id_curr')['days_credit_enddate'].max().rename('days_credit_enddate_max')\n",
    "db_days_enddate_fact = db.groupby('sk_id_curr')['days_enddate_fact'].max().rename('days_enddate_fact_max')\n",
    "db_amt_credit_max_overdue = db.groupby('sk_id_curr')['amt_credit_max_overdue'].max().rename('amt_credit_max_overdue_max')\n",
    "\n",
    "db_amt_credit = db.groupby('sk_id_curr').amt_credit_sum.agg(['mean', 'max', 'sum', 'count'])\\\n",
    ".rename(columns = {'mean':'amt_credit_mean', 'max':'amt_credit_max', 'sum':'amt_credit_sum', 'count':'amt_credit_cnt'})\n",
    "db_amt_credit_debt = db.groupby('sk_id_curr').amt_credit_sum_debt.agg(['max', 'sum'])\\\n",
    ".rename(columns = {'max':'amt_credit_debt_max', 'sum':'amt_credit_debt_sum'})\n",
    "db_amt_credit_limit = db.groupby('sk_id_curr').amt_credit_sum_limit.agg(['max', 'sum'])\\\n",
    ".rename(columns = {'max':'amt_credit_limit_max', 'sum':'amt_credit_limit_sum'})\n",
    "db_amt_credit_overdue = db.groupby('sk_id_curr').amt_credit_sum_overdue.agg(['max', 'sum'])\\\n",
    ".rename(columns = {'max':'amt_credit_overdue_max', 'sum':'amt_credit_overdue_sum'})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 40,
   "id": "5e628995-e70a-4c69-96d8-1be32d4114fc",
   "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>credit_type</th>\n",
       "      <th>Another type of loan</th>\n",
       "      <th>Car loan</th>\n",
       "      <th>Cash loan (non-earmarked)</th>\n",
       "      <th>Consumer credit</th>\n",
       "      <th>Credit card</th>\n",
       "      <th>Loan for business development</th>\n",
       "      <th>Loan for working capital replenishment</th>\n",
       "      <th>Microloan</th>\n",
       "      <th>Mortgage</th>\n",
       "      <th>Real estate loan</th>\n",
       "      <th>Unknown type of loan</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>sk_id_curr</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",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>100003</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2.0</td>\n",
       "      <td>2.0</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>100007</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1.0</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",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "credit_type  Another type of loan  Car loan  Cash loan (non-earmarked)  \\\n",
       "sk_id_curr                                                               \n",
       "100003                        NaN       NaN                        NaN   \n",
       "100007                        NaN       NaN                        NaN   \n",
       "\n",
       "credit_type  Consumer credit  Credit card  Loan for business development  \\\n",
       "sk_id_curr                                                                 \n",
       "100003                   2.0          2.0                            NaN   \n",
       "100007                   1.0          NaN                            NaN   \n",
       "\n",
       "credit_type  Loan for working capital replenishment  Microloan  Mortgage  \\\n",
       "sk_id_curr                                                                 \n",
       "100003                                          NaN        NaN       NaN   \n",
       "100007                                          NaN        NaN       NaN   \n",
       "\n",
       "credit_type  Real estate loan  Unknown type of loan  \n",
       "sk_id_curr                                           \n",
       "100003                    NaN                   NaN  \n",
       "100007                    NaN                   NaN  "
      ]
     },
     "execution_count": 40,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "db_credit_type = db.groupby(['sk_id_curr', 'credit_type']).size().unstack()\n",
    "db_credit_type.head(2)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 41,
   "id": "2ec75558-d9a2-46a6-b3de-9f38d20fe0f1",
   "metadata": {},
   "outputs": [],
   "source": [
    "db_amt_annuity = db.groupby('sk_id_curr').amt_annuity.agg(['max', 'sum'])\\\n",
    ".rename(columns = {'max':'amt_annuity_max', 'sum':'amt_annuity_sum'})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 42,
   "id": "0c27bab0-37cc-4fcd-b6d3-764012fdddd5",
   "metadata": {},
   "outputs": [],
   "source": [
    "db_agg_list = [\n",
    "    db_days_credit,\n",
    "    db_credit_day_overdue,\n",
    "    db_days_credit_enddate,\n",
    "    db_days_enddate_fact,\n",
    "    db_amt_credit_max_overdue,\n",
    "    db_amt_credit,\n",
    "    db_amt_credit_debt,\n",
    "    db_amt_credit_limit,\n",
    "    db_amt_credit_overdue,\n",
    "    db_credit_type,\n",
    "    db_amt_annuity\n",
    "]\n",
    "               \n",
    "db_agg = pd.concat(db_agg_list, axis=1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 43,
   "id": "dc7304a7-6fd3-4885-81f7-fe038837e3e1",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(26344, 28)"
      ]
     },
     "execution_count": 43,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "db_agg.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 44,
   "id": "d386d576-0ea2-4848-acfd-035a38794e1a",
   "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>days_credit_max</th>\n",
       "      <th>day_overdue_max</th>\n",
       "      <th>days_credit_enddate_max</th>\n",
       "      <th>days_enddate_fact_max</th>\n",
       "      <th>amt_credit_max_overdue_max</th>\n",
       "      <th>amt_credit_mean</th>\n",
       "      <th>amt_credit_max</th>\n",
       "      <th>amt_credit_sum</th>\n",
       "      <th>amt_credit_cnt</th>\n",
       "      <th>amt_credit_debt_max</th>\n",
       "      <th>amt_credit_debt_sum</th>\n",
       "      <th>amt_credit_limit_max</th>\n",
       "      <th>amt_credit_limit_sum</th>\n",
       "      <th>amt_credit_overdue_max</th>\n",
       "      <th>amt_credit_overdue_sum</th>\n",
       "      <th>Another type of loan</th>\n",
       "      <th>Car loan</th>\n",
       "      <th>Cash loan (non-earmarked)</th>\n",
       "      <th>Consumer credit</th>\n",
       "      <th>Credit card</th>\n",
       "      <th>Loan for business development</th>\n",
       "      <th>Loan for working capital replenishment</th>\n",
       "      <th>Microloan</th>\n",
       "      <th>Mortgage</th>\n",
       "      <th>Real estate loan</th>\n",
       "      <th>Unknown type of loan</th>\n",
       "      <th>amt_annuity_max</th>\n",
       "      <th>amt_annuity_sum</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>sk_id_curr</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",
       "      <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",
       "      <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",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>100003</th>\n",
       "      <td>-606</td>\n",
       "      <td>0</td>\n",
       "      <td>1216.0</td>\n",
       "      <td>-540.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>254350.125</td>\n",
       "      <td>810000.0</td>\n",
       "      <td>1017400.5</td>\n",
       "      <td>4</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>810000.0</td>\n",
       "      <td>810000.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2.0</td>\n",
       "      <td>2.0</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>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>100007</th>\n",
       "      <td>-1149</td>\n",
       "      <td>0</td>\n",
       "      <td>-783.0</td>\n",
       "      <td>-783.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>146250.000</td>\n",
       "      <td>146250.0</td>\n",
       "      <td>146250.0</td>\n",
       "      <td>1</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>0.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "            days_credit_max  day_overdue_max  days_credit_enddate_max  \\\n",
       "sk_id_curr                                                              \n",
       "100003                 -606                0                   1216.0   \n",
       "100007                -1149                0                   -783.0   \n",
       "\n",
       "            days_enddate_fact_max  amt_credit_max_overdue_max  \\\n",
       "sk_id_curr                                                      \n",
       "100003                     -540.0                         0.0   \n",
       "100007                     -783.0                         0.0   \n",
       "\n",
       "            amt_credit_mean  amt_credit_max  amt_credit_sum  amt_credit_cnt  \\\n",
       "sk_id_curr                                                                    \n",
       "100003           254350.125        810000.0       1017400.5               4   \n",
       "100007           146250.000        146250.0        146250.0               1   \n",
       "\n",
       "            amt_credit_debt_max  amt_credit_debt_sum  amt_credit_limit_max  \\\n",
       "sk_id_curr                                                                   \n",
       "100003                      0.0                  0.0              810000.0   \n",
       "100007                      0.0                  0.0                   0.0   \n",
       "\n",
       "            amt_credit_limit_sum  amt_credit_overdue_max  \\\n",
       "sk_id_curr                                                 \n",
       "100003                  810000.0                     0.0   \n",
       "100007                       0.0                     0.0   \n",
       "\n",
       "            amt_credit_overdue_sum  Another type of loan  Car loan  \\\n",
       "sk_id_curr                                                           \n",
       "100003                         0.0                   NaN       NaN   \n",
       "100007                         0.0                   NaN       NaN   \n",
       "\n",
       "            Cash loan (non-earmarked)  Consumer credit  Credit card  \\\n",
       "sk_id_curr                                                            \n",
       "100003                            NaN              2.0          2.0   \n",
       "100007                            NaN              1.0          NaN   \n",
       "\n",
       "            Loan for business development  \\\n",
       "sk_id_curr                                  \n",
       "100003                                NaN   \n",
       "100007                                NaN   \n",
       "\n",
       "            Loan for working capital replenishment  Microloan  Mortgage  \\\n",
       "sk_id_curr                                                                \n",
       "100003                                         NaN        NaN       NaN   \n",
       "100007                                         NaN        NaN       NaN   \n",
       "\n",
       "            Real estate loan  Unknown type of loan  amt_annuity_max  \\\n",
       "sk_id_curr                                                            \n",
       "100003                   NaN                   NaN              NaN   \n",
       "100007                   NaN                   NaN              NaN   \n",
       "\n",
       "            amt_annuity_sum  \n",
       "sk_id_curr                   \n",
       "100003                  0.0  \n",
       "100007                  0.0  "
      ]
     },
     "execution_count": 44,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "db_agg.head(2)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 46,
   "id": "b081c59f-72f1-46a5-a557-fb84beccb229",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_with_bureau = df.join(db_agg, how = 'left')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 47,
   "id": "6006045b-ac1b-4987-82da-e514e8282dd4",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(30751, 67)"
      ]
     },
     "execution_count": 47,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_with_bureau.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "31c682d6-8e52-4d4c-90cf-f6cc5148b862",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_with_bureau.reset_index().to_csv('data/home-credit-default-risk/app_with_bureau_agg_small.csv', index=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "c404a493-5a1e-4f81-988d-5f5fbca0d245",
   "metadata": {},
   "source": [
    "#### Способ 2 \n",
    "Реализовать с помощью pandasql"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "73f305d3-5511-4ac9-a638-3d557ead62a4",
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "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.9.13"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}