manual_feature_engineering_part_2.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Introduction: Manual Feature Engineering (part two)\n",
    "\n",
    "In this notebook we will expand on the Introduction to Manual Feature Engineering notebook. We will use the aggregation and value counting functions developed in that notebook in order to incorporate information from the `previous_application`, `POS_CASH_balance`, `installments_payments`, and `credit_card_balance` data files. We already used the information from the `bureau` and `bureau_balance` in the previous notebook and were able to improve our competition score compared to using only the `application` data. \n",
    "\n",
    "The definitions of these data files are:\n",
    "\n",
    "* previous_application: previous applications for loans at Home Credit of clients who have loans in the application data. Each current loan in the application data can have multiple previous loans. Each previous application has one row and is identified by the feature SK_ID_PREV.\n",
    "* POS_CASH_BALANCE: monthly data about previous point of sale or cash loans clients have had with Home Credit. Each row is one month of a previous point of sale or cash loan, and a single previous loan can have many rows.\n",
    "* credit_card_balance: monthly data about previous credit cards clients have had with Home Credit. Each row is one month of a credit card balance, and a single credit card can have many rows.\n",
    "* installments_payment: payment history for previous loans at Home Credit. There is one row for every made payment and one row for every missed payment."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Functions \n",
    "\n",
    "We spent quite a bit of time developing two functions in the previous notebook:\n",
    "\n",
    "* `agg_numeric`: calculate aggregation statistics for numeric variables.\n",
    "* `count_cats`: compute counts of each category in a categorical variable and calculate the normalized counts. \n",
    "\n",
    "Together, these two functions can extract information about both the numeric and categorical data in a dataframe. Our general approach will be to apply both of these functions to the dataframes, grouping by the client id, `SK_ID_CURR`. For the `POS_CASH_balance`, `credit_card_balance`, and `installment_payments`, we can first group by the `SK_ID_PREV`, the unique id for the previous loan. Then we will group the resulting dataframe by the `SK_ID_CURR` to calculate the aggregation statistics for each client acorss all of their previous loans. If that's a little confusing, we'll walk through what we are doing!"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Function to Aggregate Numeric Data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [],
   "source": [
    "# pandas and numpy for data manipulation\n",
    "import pandas as pd\n",
    "import numpy as np\n",
    "\n",
    "# matplotlib and seaborn for plotting\n",
    "import matplotlib.pyplot as plt\n",
    "import seaborn as sns\n",
    "\n",
    "# Suppress warnings from pandas\n",
    "import warnings\n",
    "warnings.filterwarnings('ignore')\n",
    "\n",
    "plt.style.use('fivethirtyeight')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [],
   "source": [
    "def agg_numeric(df, group_var, df_name):\n",
    "    \"\"\"Aggregates the numeric values in a dataframe. This can\n",
    "    be used to create features for each instance of the grouping variable.\n",
    "    \n",
    "    Args:\n",
    "        df (dataframe): the dataframe to calculate the statistics on\n",
    "        group_var (string): the variable by which to group df\n",
    "        df_name (string): the variable used to rename the columns\n",
    "        \n",
    "    Returns:\n",
    "        agg (dataframe): a dataframe with the statistics aggregated for \n",
    "        all numeric columns. Each instance of the grouping variable will have \n",
    "        the statistics (mean, min, max, sum; currently supported) calculated. \n",
    "        The columns are also renamed to keep track of features created.\n",
    "    \n",
    "    \"\"\"\n",
    "    \n",
    "    # First calculate counts\n",
    "    counts = pd.DataFrame(df.groupby(group_var, as_index = False)[df.columns[2]].count()).rename(columns = {df.columns[2]: '%s_counts' % df_name})\n",
    "    \n",
    "    # Group by the specified variable and calculate the statistics\n",
    "    agg = df.groupby(group_var).agg(['mean', 'max', 'min', 'sum']).reset_index()\n",
    "    \n",
    "    # Need to create new column names\n",
    "    columns = [group_var]\n",
    "    \n",
    "    # Iterate through the variables names\n",
    "    for var in agg.columns.levels[0]:\n",
    "        # Skip the grouping variable\n",
    "        if var != group_var:\n",
    "            # Iterate through the stat names\n",
    "            for stat in agg.columns.levels[1][:-1]:\n",
    "                # Make a new column name for the variable and stat\n",
    "                columns.append('%s_%s_%s' % (df_name, var, stat))\n",
    "              \n",
    "    #  Rename the columns\n",
    "    agg.columns = columns\n",
    "    \n",
    "    # Merge with the counts\n",
    "    agg = agg.merge(counts, on = group_var, how = 'left')\n",
    "    \n",
    "    return agg"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Function to Calculate Categorical Counts"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [],
   "source": [
    "def count_cats(df, group_var, df_name):\n",
    "    \"\"\"Computes value counts of categories in all categorical variables for a dataframe.\n",
    "    The value counts are calculated for every instance of the group_var. The dataframe that is\n",
    "    returned includes the overall counts of each occurence of the grouping variable \n",
    "    as well as the value counts of categories for each categorical variable for each \n",
    "    occurence of the grouping variable\n",
    "    \n",
    "    Args:\n",
    "        df (dataframe): The dataframe to calculate the value counts for.\n",
    "        group_var (string): the variable by which to group df\n",
    "        df_name (string): the variable used to rename the columns\n",
    "    \n",
    "    Returns:\n",
    "        tg (dataframe): a dataframe with value counts of each category in every categorical variable\n",
    "        of the original df. The overall counts are also merged into the dataframe.\n",
    "    \"\"\"\n",
    "    \n",
    "    # Select the categorical columns\n",
    "    cats = df.select_dtypes('object')\n",
    "\n",
    "    # Make sure to put the identifying id on the column\n",
    "    cats[group_var] = df[group_var]\n",
    "\n",
    "    cats_cols = list(cats.columns)\n",
    "\n",
    "    # Iterate through each categorical column\n",
    "    for col in cats_cols:\n",
    "        if col == group_var:\n",
    "            next\n",
    "        else:\n",
    "            # Group by the grouping variable, select the column, calculate value counts\n",
    "            tg = cats.groupby(group_var)[col].value_counts()\n",
    "\n",
    "            # Rename the column\n",
    "            tg = pd.DataFrame(tg).rename(columns = {col: '%s_counts' % col}).reset_index()\n",
    "\n",
    "            # Pivot table with counts the value to aggregate, index is the grouping variable, \n",
    "            # columns are the categorical name, the aggfunc is the mean and fill\n",
    "            # in missing values with np.nan\n",
    "            table = pd.pivot_table(tg, values = '%s_counts' % col, index = group_var, \n",
    "                                   columns = col, aggfunc = 'mean', fill_value = None)\n",
    "\n",
    "            # Remove the table name\n",
    "            table.columns.name = None\n",
    "\n",
    "            # New names of columns\n",
    "            table_columns = ['%s_%s_counts' % (col, x) for x in table.columns]\n",
    "            table.columns = table_columns\n",
    "\n",
    "            # Merge the created table with the categorical dataframe\n",
    "            cats = cats.merge(table, left_on = group_var, right_index = True, how = 'left')\n",
    "            cats = cats.drop_duplicates()\n",
    "            cats = cats.drop(columns = [col])\n",
    "\n",
    "    # Get the counts for each client\n",
    "    counts = pd.DataFrame(df.groupby(group_var)[group_var].count()).rename(columns = {group_var: '%s_counts' % df_name})\n",
    "\n",
    "    # Merge the counts into the categorical data\n",
    "    cats = cats.merge(counts, left_on = group_var, right_index = True, how = 'left')\n",
    "    cats = cats.set_index(group_var)\n",
    "\n",
    "    # Normalize all the columns by the total_counts\n",
    "    for col in list(cats.columns):\n",
    "        cats['%s_normalized' % col] = cats[col] / cats['%s_counts' % df_name]\n",
    "    \n",
    "    # Reset the index to make group var a column\n",
    "    cats = cats.reset_index()\n",
    "    \n",
    "    # Drop the duplicate rows\n",
    "    cats = cats.drop_duplicates()\n",
    "    \n",
    "    return cats"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Function for KDE Plots of Variable\n",
    "\n",
    "We also made a function that plots the distribution of variable colored by the value of `TARGET` (either 1 for did not repay the loan or 0 for did repay the loan). We can use this function to visually examine any new variables we create. This also calculates the correlation cofficient of the variable with the target which can be used as an approximation of whether or not the created variable will be useful. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Plots the disribution of a variable colored by value of the target\n",
    "def kde_target(var_name, df):\n",
    "    \n",
    "    # Calculate the correlation coefficient between the new variable and the target\n",
    "    corr = df['TARGET'].corr(df[var_name])\n",
    "    \n",
    "    # Calculate medians for repaid vs not repaid\n",
    "    avg_repaid = df.ix[df['TARGET'] == 0, var_name].median()\n",
    "    avg_not_repaid = df.ix[df['TARGET'] == 1, var_name].median()\n",
    "    \n",
    "    plt.figure(figsize = (12, 6))\n",
    "    \n",
    "    # Plot the distribution for target == 0 and target == 1\n",
    "    sns.kdeplot(df.ix[df['TARGET'] == 0, var_name], label = 'TARGET == 0')\n",
    "    sns.kdeplot(df.ix[df['TARGET'] == 1, var_name], label = 'TARGET == 1')\n",
    "    \n",
    "    # label the plot\n",
    "    plt.xlabel(var_name); plt.ylabel('Density'); plt.title('%s Distribution' % var_name)\n",
    "    plt.legend();\n",
    "    \n",
    "    # print out the correlation\n",
    "    print('The correlation between %s and the TARGET is %0.4f' % (var_name, corr))\n",
    "    # Print out average values\n",
    "    print('Median value for loan that was not repaid = %0.4f' % avg_not_repaid)\n",
    "    print('Median value for loan that was repaid =     %0.4f' % avg_repaid)\n",
    "    "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Let's deal with one dataframe at a time. First up is the `previous_applications`. This has one row for every previous loan a client had at Home Credit. A client can have multiple previous loans which is why we need to aggregate statistics for each client."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>SK_ID_PREV</th>\n",
       "      <th>SK_ID_CURR</th>\n",
       "      <th>NAME_CONTRACT_TYPE</th>\n",
       "      <th>AMT_ANNUITY</th>\n",
       "      <th>AMT_APPLICATION</th>\n",
       "      <th>AMT_CREDIT</th>\n",
       "      <th>AMT_DOWN_PAYMENT</th>\n",
       "      <th>AMT_GOODS_PRICE</th>\n",
       "      <th>WEEKDAY_APPR_PROCESS_START</th>\n",
       "      <th>HOUR_APPR_PROCESS_START</th>\n",
       "      <th>...</th>\n",
       "      <th>NAME_SELLER_INDUSTRY</th>\n",
       "      <th>CNT_PAYMENT</th>\n",
       "      <th>NAME_YIELD_GROUP</th>\n",
       "      <th>PRODUCT_COMBINATION</th>\n",
       "      <th>DAYS_FIRST_DRAWING</th>\n",
       "      <th>DAYS_FIRST_DUE</th>\n",
       "      <th>DAYS_LAST_DUE_1ST_VERSION</th>\n",
       "      <th>DAYS_LAST_DUE</th>\n",
       "      <th>DAYS_TERMINATION</th>\n",
       "      <th>NFLAG_INSURED_ON_APPROVAL</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2030495</td>\n",
       "      <td>271877</td>\n",
       "      <td>Consumer loans</td>\n",
       "      <td>1730.430</td>\n",
       "      <td>17145.0</td>\n",
       "      <td>17145.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>17145.0</td>\n",
       "      <td>SATURDAY</td>\n",
       "      <td>15</td>\n",
       "      <td>...</td>\n",
       "      <td>Connectivity</td>\n",
       "      <td>12.0</td>\n",
       "      <td>middle</td>\n",
       "      <td>POS mobile with interest</td>\n",
       "      <td>365243.0</td>\n",
       "      <td>-42.0</td>\n",
       "      <td>300.0</td>\n",
       "      <td>-42.0</td>\n",
       "      <td>-37.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2802425</td>\n",
       "      <td>108129</td>\n",
       "      <td>Cash loans</td>\n",
       "      <td>25188.615</td>\n",
       "      <td>607500.0</td>\n",
       "      <td>679671.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>607500.0</td>\n",
       "      <td>THURSDAY</td>\n",
       "      <td>11</td>\n",
       "      <td>...</td>\n",
       "      <td>XNA</td>\n",
       "      <td>36.0</td>\n",
       "      <td>low_action</td>\n",
       "      <td>Cash X-Sell: low</td>\n",
       "      <td>365243.0</td>\n",
       "      <td>-134.0</td>\n",
       "      <td>916.0</td>\n",
       "      <td>365243.0</td>\n",
       "      <td>365243.0</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2523466</td>\n",
       "      <td>122040</td>\n",
       "      <td>Cash loans</td>\n",
       "      <td>15060.735</td>\n",
       "      <td>112500.0</td>\n",
       "      <td>136444.5</td>\n",
       "      <td>NaN</td>\n",
       "      <td>112500.0</td>\n",
       "      <td>TUESDAY</td>\n",
       "      <td>11</td>\n",
       "      <td>...</td>\n",
       "      <td>XNA</td>\n",
       "      <td>12.0</td>\n",
       "      <td>high</td>\n",
       "      <td>Cash X-Sell: high</td>\n",
       "      <td>365243.0</td>\n",
       "      <td>-271.0</td>\n",
       "      <td>59.0</td>\n",
       "      <td>365243.0</td>\n",
       "      <td>365243.0</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2819243</td>\n",
       "      <td>176158</td>\n",
       "      <td>Cash loans</td>\n",
       "      <td>47041.335</td>\n",
       "      <td>450000.0</td>\n",
       "      <td>470790.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>450000.0</td>\n",
       "      <td>MONDAY</td>\n",
       "      <td>7</td>\n",
       "      <td>...</td>\n",
       "      <td>XNA</td>\n",
       "      <td>12.0</td>\n",
       "      <td>middle</td>\n",
       "      <td>Cash X-Sell: middle</td>\n",
       "      <td>365243.0</td>\n",
       "      <td>-482.0</td>\n",
       "      <td>-152.0</td>\n",
       "      <td>-182.0</td>\n",
       "      <td>-177.0</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1784265</td>\n",
       "      <td>202054</td>\n",
       "      <td>Cash loans</td>\n",
       "      <td>31924.395</td>\n",
       "      <td>337500.0</td>\n",
       "      <td>404055.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>337500.0</td>\n",
       "      <td>THURSDAY</td>\n",
       "      <td>9</td>\n",
       "      <td>...</td>\n",
       "      <td>XNA</td>\n",
       "      <td>24.0</td>\n",
       "      <td>high</td>\n",
       "      <td>Cash Street: high</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",
       "<p>5 rows × 37 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "   SK_ID_PREV  SK_ID_CURR NAME_CONTRACT_TYPE  AMT_ANNUITY  AMT_APPLICATION  \\\n",
       "0     2030495      271877     Consumer loans     1730.430          17145.0   \n",
       "1     2802425      108129         Cash loans    25188.615         607500.0   \n",
       "2     2523466      122040         Cash loans    15060.735         112500.0   \n",
       "3     2819243      176158         Cash loans    47041.335         450000.0   \n",
       "4     1784265      202054         Cash loans    31924.395         337500.0   \n",
       "\n",
       "   AMT_CREDIT  AMT_DOWN_PAYMENT  AMT_GOODS_PRICE WEEKDAY_APPR_PROCESS_START  \\\n",
       "0     17145.0               0.0          17145.0                   SATURDAY   \n",
       "1    679671.0               NaN         607500.0                   THURSDAY   \n",
       "2    136444.5               NaN         112500.0                    TUESDAY   \n",
       "3    470790.0               NaN         450000.0                     MONDAY   \n",
       "4    404055.0               NaN         337500.0                   THURSDAY   \n",
       "\n",
       "   HOUR_APPR_PROCESS_START            ...            NAME_SELLER_INDUSTRY  \\\n",
       "0                       15            ...                    Connectivity   \n",
       "1                       11            ...                             XNA   \n",
       "2                       11            ...                             XNA   \n",
       "3                        7            ...                             XNA   \n",
       "4                        9            ...                             XNA   \n",
       "\n",
       "   CNT_PAYMENT  NAME_YIELD_GROUP       PRODUCT_COMBINATION  \\\n",
       "0         12.0            middle  POS mobile with interest   \n",
       "1         36.0        low_action          Cash X-Sell: low   \n",
       "2         12.0              high         Cash X-Sell: high   \n",
       "3         12.0            middle       Cash X-Sell: middle   \n",
       "4         24.0              high         Cash Street: high   \n",
       "\n",
       "   DAYS_FIRST_DRAWING DAYS_FIRST_DUE DAYS_LAST_DUE_1ST_VERSION  DAYS_LAST_DUE  \\\n",
       "0            365243.0          -42.0                     300.0          -42.0   \n",
       "1            365243.0         -134.0                     916.0       365243.0   \n",
       "2            365243.0         -271.0                      59.0       365243.0   \n",
       "3            365243.0         -482.0                    -152.0         -182.0   \n",
       "4                 NaN            NaN                       NaN            NaN   \n",
       "\n",
       "  DAYS_TERMINATION NFLAG_INSURED_ON_APPROVAL  \n",
       "0            -37.0                       0.0  \n",
       "1         365243.0                       1.0  \n",
       "2         365243.0                       1.0  \n",
       "3           -177.0                       1.0  \n",
       "4              NaN                       NaN  \n",
       "\n",
       "[5 rows x 37 columns]"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "previous = pd.read_csv('../input/previous_application.csv')\n",
    "previous.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "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>previous_loans_AMT_ANNUITY_mean</th>\n",
       "      <th>previous_loans_AMT_ANNUITY_max</th>\n",
       "      <th>previous_loans_AMT_ANNUITY_min</th>\n",
       "      <th>previous_loans_AMT_ANNUITY_sum</th>\n",
       "      <th>previous_loans_AMT_APPLICATION_mean</th>\n",
       "      <th>previous_loans_AMT_APPLICATION_max</th>\n",
       "      <th>previous_loans_AMT_APPLICATION_min</th>\n",
       "      <th>previous_loans_AMT_APPLICATION_sum</th>\n",
       "      <th>previous_loans_AMT_CREDIT_mean</th>\n",
       "      <th>...</th>\n",
       "      <th>previous_loans_DAYS_LAST_DUE_sum</th>\n",
       "      <th>previous_loans_DAYS_TERMINATION_mean</th>\n",
       "      <th>previous_loans_DAYS_TERMINATION_max</th>\n",
       "      <th>previous_loans_DAYS_TERMINATION_min</th>\n",
       "      <th>previous_loans_DAYS_TERMINATION_sum</th>\n",
       "      <th>previous_loans_NFLAG_INSURED_ON_APPROVAL_mean</th>\n",
       "      <th>previous_loans_NFLAG_INSURED_ON_APPROVAL_max</th>\n",
       "      <th>previous_loans_NFLAG_INSURED_ON_APPROVAL_min</th>\n",
       "      <th>previous_loans_NFLAG_INSURED_ON_APPROVAL_sum</th>\n",
       "      <th>previous_loans_counts</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>100001</td>\n",
       "      <td>3951.000</td>\n",
       "      <td>3951.000</td>\n",
       "      <td>3951.000</td>\n",
       "      <td>3951.000</td>\n",
       "      <td>24835.50</td>\n",
       "      <td>24835.5</td>\n",
       "      <td>24835.5</td>\n",
       "      <td>24835.5</td>\n",
       "      <td>23787.00</td>\n",
       "      <td>...</td>\n",
       "      <td>-1619.0</td>\n",
       "      <td>-1612.000000</td>\n",
       "      <td>-1612.0</td>\n",
       "      <td>-1612.0</td>\n",
       "      <td>-1612.0</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>100002</td>\n",
       "      <td>9251.775</td>\n",
       "      <td>9251.775</td>\n",
       "      <td>9251.775</td>\n",
       "      <td>9251.775</td>\n",
       "      <td>179055.00</td>\n",
       "      <td>179055.0</td>\n",
       "      <td>179055.0</td>\n",
       "      <td>179055.0</td>\n",
       "      <td>179055.00</td>\n",
       "      <td>...</td>\n",
       "      <td>-25.0</td>\n",
       "      <td>-17.000000</td>\n",
       "      <td>-17.0</td>\n",
       "      <td>-17.0</td>\n",
       "      <td>-17.0</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>100003</td>\n",
       "      <td>56553.990</td>\n",
       "      <td>98356.995</td>\n",
       "      <td>6737.310</td>\n",
       "      <td>169661.970</td>\n",
       "      <td>435436.50</td>\n",
       "      <td>900000.0</td>\n",
       "      <td>68809.5</td>\n",
       "      <td>1306309.5</td>\n",
       "      <td>484191.00</td>\n",
       "      <td>...</td>\n",
       "      <td>-3163.0</td>\n",
       "      <td>-1047.333333</td>\n",
       "      <td>-527.0</td>\n",
       "      <td>-1976.0</td>\n",
       "      <td>-3142.0</td>\n",
       "      <td>0.666667</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>100004</td>\n",
       "      <td>5357.250</td>\n",
       "      <td>5357.250</td>\n",
       "      <td>5357.250</td>\n",
       "      <td>5357.250</td>\n",
       "      <td>24282.00</td>\n",
       "      <td>24282.0</td>\n",
       "      <td>24282.0</td>\n",
       "      <td>24282.0</td>\n",
       "      <td>20106.00</td>\n",
       "      <td>...</td>\n",
       "      <td>-724.0</td>\n",
       "      <td>-714.000000</td>\n",
       "      <td>-714.0</td>\n",
       "      <td>-714.0</td>\n",
       "      <td>-714.0</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>100005</td>\n",
       "      <td>4813.200</td>\n",
       "      <td>4813.200</td>\n",
       "      <td>4813.200</td>\n",
       "      <td>4813.200</td>\n",
       "      <td>22308.75</td>\n",
       "      <td>44617.5</td>\n",
       "      <td>0.0</td>\n",
       "      <td>44617.5</td>\n",
       "      <td>20076.75</td>\n",
       "      <td>...</td>\n",
       "      <td>-466.0</td>\n",
       "      <td>-460.000000</td>\n",
       "      <td>-460.0</td>\n",
       "      <td>-460.0</td>\n",
       "      <td>-460.0</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 78 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "   SK_ID_CURR  previous_loans_AMT_ANNUITY_mean  \\\n",
       "0      100001                         3951.000   \n",
       "1      100002                         9251.775   \n",
       "2      100003                        56553.990   \n",
       "3      100004                         5357.250   \n",
       "4      100005                         4813.200   \n",
       "\n",
       "   previous_loans_AMT_ANNUITY_max  previous_loans_AMT_ANNUITY_min  \\\n",
       "0                        3951.000                        3951.000   \n",
       "1                        9251.775                        9251.775   \n",
       "2                       98356.995                        6737.310   \n",
       "3                        5357.250                        5357.250   \n",
       "4                        4813.200                        4813.200   \n",
       "\n",
       "   previous_loans_AMT_ANNUITY_sum  previous_loans_AMT_APPLICATION_mean  \\\n",
       "0                        3951.000                             24835.50   \n",
       "1                        9251.775                            179055.00   \n",
       "2                      169661.970                            435436.50   \n",
       "3                        5357.250                             24282.00   \n",
       "4                        4813.200                             22308.75   \n",
       "\n",
       "   previous_loans_AMT_APPLICATION_max  previous_loans_AMT_APPLICATION_min  \\\n",
       "0                             24835.5                             24835.5   \n",
       "1                            179055.0                            179055.0   \n",
       "2                            900000.0                             68809.5   \n",
       "3                             24282.0                             24282.0   \n",
       "4                             44617.5                                 0.0   \n",
       "\n",
       "   previous_loans_AMT_APPLICATION_sum  previous_loans_AMT_CREDIT_mean  \\\n",
       "0                             24835.5                        23787.00   \n",
       "1                            179055.0                       179055.00   \n",
       "2                           1306309.5                       484191.00   \n",
       "3                             24282.0                        20106.00   \n",
       "4                             44617.5                        20076.75   \n",
       "\n",
       "           ...            previous_loans_DAYS_LAST_DUE_sum  \\\n",
       "0          ...                                     -1619.0   \n",
       "1          ...                                       -25.0   \n",
       "2          ...                                     -3163.0   \n",
       "3          ...                                      -724.0   \n",
       "4          ...                                      -466.0   \n",
       "\n",
       "   previous_loans_DAYS_TERMINATION_mean  previous_loans_DAYS_TERMINATION_max  \\\n",
       "0                          -1612.000000                              -1612.0   \n",
       "1                            -17.000000                                -17.0   \n",
       "2                          -1047.333333                               -527.0   \n",
       "3                           -714.000000                               -714.0   \n",
       "4                           -460.000000                               -460.0   \n",
       "\n",
       "   previous_loans_DAYS_TERMINATION_min  previous_loans_DAYS_TERMINATION_sum  \\\n",
       "0                              -1612.0                              -1612.0   \n",
       "1                                -17.0                                -17.0   \n",
       "2                              -1976.0                              -3142.0   \n",
       "3                               -714.0                               -714.0   \n",
       "4                               -460.0                               -460.0   \n",
       "\n",
       "   previous_loans_NFLAG_INSURED_ON_APPROVAL_mean  \\\n",
       "0                                       0.000000   \n",
       "1                                       0.000000   \n",
       "2                                       0.666667   \n",
       "3                                       0.000000   \n",
       "4                                       0.000000   \n",
       "\n",
       "   previous_loans_NFLAG_INSURED_ON_APPROVAL_max  \\\n",
       "0                                           0.0   \n",
       "1                                           0.0   \n",
       "2                                           1.0   \n",
       "3                                           0.0   \n",
       "4                                           0.0   \n",
       "\n",
       "   previous_loans_NFLAG_INSURED_ON_APPROVAL_min  \\\n",
       "0                                           0.0   \n",
       "1                                           0.0   \n",
       "2                                           0.0   \n",
       "3                                           0.0   \n",
       "4                                           0.0   \n",
       "\n",
       "   previous_loans_NFLAG_INSURED_ON_APPROVAL_sum  previous_loans_counts  \n",
       "0                                           0.0                      1  \n",
       "1                                           0.0                      1  \n",
       "2                                           2.0                      3  \n",
       "3                                           0.0                      1  \n",
       "4                                           0.0                      1  \n",
       "\n",
       "[5 rows x 78 columns]"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "previous_agg = agg_numeric(previous.drop(columns = ['SK_ID_PREV']), group_var = 'SK_ID_CURR', df_name = 'previous_loans')\n",
    "previous_agg.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "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>NAME_CONTRACT_TYPE_Cash loans_counts</th>\n",
       "      <th>NAME_CONTRACT_TYPE_Consumer loans_counts</th>\n",
       "      <th>NAME_CONTRACT_TYPE_Revolving loans_counts</th>\n",
       "      <th>NAME_CONTRACT_TYPE_XNA_counts</th>\n",
       "      <th>WEEKDAY_APPR_PROCESS_START_FRIDAY_counts</th>\n",
       "      <th>WEEKDAY_APPR_PROCESS_START_MONDAY_counts</th>\n",
       "      <th>WEEKDAY_APPR_PROCESS_START_SATURDAY_counts</th>\n",
       "      <th>WEEKDAY_APPR_PROCESS_START_SUNDAY_counts</th>\n",
       "      <th>WEEKDAY_APPR_PROCESS_START_THURSDAY_counts</th>\n",
       "      <th>...</th>\n",
       "      <th>PRODUCT_COMBINATION_Cash X-Sell: middle_counts_normalized</th>\n",
       "      <th>PRODUCT_COMBINATION_POS household with interest_counts_normalized</th>\n",
       "      <th>PRODUCT_COMBINATION_POS household without interest_counts_normalized</th>\n",
       "      <th>PRODUCT_COMBINATION_POS industry with interest_counts_normalized</th>\n",
       "      <th>PRODUCT_COMBINATION_POS industry without interest_counts_normalized</th>\n",
       "      <th>PRODUCT_COMBINATION_POS mobile with interest_counts_normalized</th>\n",
       "      <th>PRODUCT_COMBINATION_POS mobile without interest_counts_normalized</th>\n",
       "      <th>PRODUCT_COMBINATION_POS other with interest_counts_normalized</th>\n",
       "      <th>PRODUCT_COMBINATION_POS others without interest_counts_normalized</th>\n",
       "      <th>previous_loans_counts_normalized</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>271877</td>\n",
       "      <td>0</td>\n",
       "      <td>3</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.666667</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.333333</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>108129</td>\n",
       "      <td>2</td>\n",
       "      <td>3</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>...</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.166667</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.333333</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>122040</td>\n",
       "      <td>2</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>0</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.250000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>176158</td>\n",
       "      <td>22</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>3</td>\n",
       "      <td>8</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>...</td>\n",
       "      <td>0.043478</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.043478</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>202054</td>\n",
       "      <td>18</td>\n",
       "      <td>6</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>3</td>\n",
       "      <td>5</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>6</td>\n",
       "      <td>...</td>\n",
       "      <td>0.040000</td>\n",
       "      <td>0.080000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.040000</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 289 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "   SK_ID_CURR  NAME_CONTRACT_TYPE_Cash loans_counts  \\\n",
       "0      271877                                     0   \n",
       "1      108129                                     2   \n",
       "2      122040                                     2   \n",
       "3      176158                                    22   \n",
       "4      202054                                    18   \n",
       "\n",
       "   NAME_CONTRACT_TYPE_Consumer loans_counts  \\\n",
       "0                                         3   \n",
       "1                                         3   \n",
       "2                                         1   \n",
       "3                                         1   \n",
       "4                                         6   \n",
       "\n",
       "   NAME_CONTRACT_TYPE_Revolving loans_counts  NAME_CONTRACT_TYPE_XNA_counts  \\\n",
       "0                                          0                              0   \n",
       "1                                          1                              0   \n",
       "2                                          1                              0   \n",
       "3                                          0                              0   \n",
       "4                                          1                              0   \n",
       "\n",
       "   WEEKDAY_APPR_PROCESS_START_FRIDAY_counts  \\\n",
       "0                                         0   \n",
       "1                                         0   \n",
       "2                                         1   \n",
       "3                                         3   \n",
       "4                                         3   \n",
       "\n",
       "   WEEKDAY_APPR_PROCESS_START_MONDAY_counts  \\\n",
       "0                                         0   \n",
       "1                                         0   \n",
       "2                                         0   \n",
       "3                                         8   \n",
       "4                                         5   \n",
       "\n",
       "   WEEKDAY_APPR_PROCESS_START_SATURDAY_counts  \\\n",
       "0                                           2   \n",
       "1                                           0   \n",
       "2                                           0   \n",
       "3                                           1   \n",
       "4                                           2   \n",
       "\n",
       "   WEEKDAY_APPR_PROCESS_START_SUNDAY_counts  \\\n",
       "0                                         1   \n",
       "1                                         1   \n",
       "2                                         0   \n",
       "3                                         0   \n",
       "4                                         1   \n",
       "\n",
       "   WEEKDAY_APPR_PROCESS_START_THURSDAY_counts  \\\n",
       "0                                           0   \n",
       "1                                           2   \n",
       "2                                           0   \n",
       "3                                           1   \n",
       "4                                           6   \n",
       "\n",
       "                 ...                 \\\n",
       "0                ...                  \n",
       "1                ...                  \n",
       "2                ...                  \n",
       "3                ...                  \n",
       "4                ...                  \n",
       "\n",
       "   PRODUCT_COMBINATION_Cash X-Sell: middle_counts_normalized  \\\n",
       "0                                           0.000000           \n",
       "1                                           0.000000           \n",
       "2                                           0.000000           \n",
       "3                                           0.043478           \n",
       "4                                           0.040000           \n",
       "\n",
       "   PRODUCT_COMBINATION_POS household with interest_counts_normalized  \\\n",
       "0                                           0.000000                   \n",
       "1                                           0.166667                   \n",
       "2                                           0.250000                   \n",
       "3                                           0.000000                   \n",
       "4                                           0.080000                   \n",
       "\n",
       "   PRODUCT_COMBINATION_POS household without interest_counts_normalized  \\\n",
       "0                                           0.000000                      \n",
       "1                                           0.000000                      \n",
       "2                                           0.000000                      \n",
       "3                                           0.043478                      \n",
       "4                                           0.000000                      \n",
       "\n",
       "   PRODUCT_COMBINATION_POS industry with interest_counts_normalized  \\\n",
       "0                                           0.666667                  \n",
       "1                                           0.333333                  \n",
       "2                                           0.000000                  \n",
       "3                                           0.000000                  \n",
       "4                                           0.000000                  \n",
       "\n",
       "   PRODUCT_COMBINATION_POS industry without interest_counts_normalized  \\\n",
       "0                                                0.0                     \n",
       "1                                                0.0                     \n",
       "2                                                0.0                     \n",
       "3                                                0.0                     \n",
       "4                                                0.0                     \n",
       "\n",
       "   PRODUCT_COMBINATION_POS mobile with interest_counts_normalized  \\\n",
       "0                                           0.333333                \n",
       "1                                           0.000000                \n",
       "2                                           0.000000                \n",
       "3                                           0.000000                \n",
       "4                                           0.040000                \n",
       "\n",
       "   PRODUCT_COMBINATION_POS mobile without interest_counts_normalized  \\\n",
       "0                                                0.0                   \n",
       "1                                                0.0                   \n",
       "2                                                0.0                   \n",
       "3                                                0.0                   \n",
       "4                                                0.0                   \n",
       "\n",
       "   PRODUCT_COMBINATION_POS other with interest_counts_normalized  \\\n",
       "0                                                0.0               \n",
       "1                                                0.0               \n",
       "2                                                0.0               \n",
       "3                                                0.0               \n",
       "4                                                0.0               \n",
       "\n",
       "   PRODUCT_COMBINATION_POS others without interest_counts_normalized  \\\n",
       "0                                                0.0                   \n",
       "1                                                0.0                   \n",
       "2                                                0.0                   \n",
       "3                                                0.0                   \n",
       "4                                                0.0                   \n",
       "\n",
       "   previous_loans_counts_normalized  \n",
       "0                               1.0  \n",
       "1                               1.0  \n",
       "2                               1.0  \n",
       "3                               1.0  \n",
       "4                               1.0  \n",
       "\n",
       "[5 rows x 289 columns]"
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "previous_cats = count_cats(previous, group_var = 'SK_ID_CURR', df_name = 'previous_loans')\n",
    "previous_cats.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.6.5"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}