01 - Data Wrangling.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# KIVA Data\n",
    "\n",
    "Name: Dylan Connor and Kaung Khin  \n",
    "Andrew ID: dmconnor | Kkhin"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Imports"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import numpy as np\n",
    "import matplotlib.pyplot as plt\n",
    "import seaborn as sns\n",
    "import math\n",
    "pd.options.display.max_columns = None"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 110,
   "metadata": {},
   "outputs": [],
   "source": [
    "import dill\n",
    "dill.dump_session('notebook_env_wrangle.db')\n",
    "#dill.load_session('notebook_env_wrangle.db')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Load and Wrangle Data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Source: https://www.kaggle.com/kiva/data-science-for-good-kiva-crowdfunding\n",
    "loans = pd.read_csv(\"input_data/kiva_loans.csv\")\n",
    "region_mpi = pd.read_csv(\"input_data/kiva_mpi_region_locations.csv\")\n",
    "theme = pd.read_csv(\"input_data/loan_theme_ids.csv\")\n",
    "theme_reg = pd.read_csv(\"input_data/loan_themes_by_region.csv\")\n",
    "mpi = pd.read_excel(\"input_data/MPI_countries.xlsx\")\n",
    "ISO_code = pd.read_csv(\"input_data/ISO_2.csv\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(15736, 20)"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "theme_reg.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 58,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "56800"
      ]
     },
     "execution_count": 58,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "loans['region'].isna().sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 59,
   "metadata": {},
   "outputs": [],
   "source": [
    "loans.loc[loans['region'].isna(),'region'] = loans.loc[loans['region'].isna(),'country_code']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 60,
   "metadata": {},
   "outputs": [],
   "source": [
    "loans['region'] = loans['region'].apply(lambda v : v.lower())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 61,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(671205, 20)"
      ]
     },
     "execution_count": 61,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "loans.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 62,
   "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>id</th>\n",
       "      <th>funded_amount</th>\n",
       "      <th>loan_amount</th>\n",
       "      <th>activity</th>\n",
       "      <th>sector</th>\n",
       "      <th>use</th>\n",
       "      <th>country_code</th>\n",
       "      <th>country</th>\n",
       "      <th>region</th>\n",
       "      <th>currency</th>\n",
       "      <th>partner_id</th>\n",
       "      <th>posted_time</th>\n",
       "      <th>disbursed_time</th>\n",
       "      <th>funded_time</th>\n",
       "      <th>term_in_months</th>\n",
       "      <th>lender_count</th>\n",
       "      <th>tags</th>\n",
       "      <th>borrower_genders</th>\n",
       "      <th>repayment_interval</th>\n",
       "      <th>date</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>653051</td>\n",
       "      <td>300.0</td>\n",
       "      <td>300.0</td>\n",
       "      <td>Fruits &amp; Vegetables</td>\n",
       "      <td>Food</td>\n",
       "      <td>To buy seasonal, fresh fruits to sell.</td>\n",
       "      <td>PK</td>\n",
       "      <td>Pakistan</td>\n",
       "      <td>lahore</td>\n",
       "      <td>PKR</td>\n",
       "      <td>247.0</td>\n",
       "      <td>2014-01-01 06:12:39+00:00</td>\n",
       "      <td>2013-12-17 08:00:00+00:00</td>\n",
       "      <td>2014-01-02 10:06:32+00:00</td>\n",
       "      <td>12.0</td>\n",
       "      <td>12</td>\n",
       "      <td>NaN</td>\n",
       "      <td>female</td>\n",
       "      <td>irregular</td>\n",
       "      <td>2014-01-01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>653053</td>\n",
       "      <td>575.0</td>\n",
       "      <td>575.0</td>\n",
       "      <td>Rickshaw</td>\n",
       "      <td>Transportation</td>\n",
       "      <td>to repair and maintain the auto rickshaw used ...</td>\n",
       "      <td>PK</td>\n",
       "      <td>Pakistan</td>\n",
       "      <td>lahore</td>\n",
       "      <td>PKR</td>\n",
       "      <td>247.0</td>\n",
       "      <td>2014-01-01 06:51:08+00:00</td>\n",
       "      <td>2013-12-17 08:00:00+00:00</td>\n",
       "      <td>2014-01-02 09:17:23+00:00</td>\n",
       "      <td>11.0</td>\n",
       "      <td>14</td>\n",
       "      <td>NaN</td>\n",
       "      <td>female, female</td>\n",
       "      <td>irregular</td>\n",
       "      <td>2014-01-01</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "       id  funded_amount  loan_amount             activity          sector  \\\n",
       "0  653051          300.0        300.0  Fruits & Vegetables            Food   \n",
       "1  653053          575.0        575.0             Rickshaw  Transportation   \n",
       "\n",
       "                                                 use country_code   country  \\\n",
       "0            To buy seasonal, fresh fruits to sell.            PK  Pakistan   \n",
       "1  to repair and maintain the auto rickshaw used ...           PK  Pakistan   \n",
       "\n",
       "   region currency  partner_id                posted_time  \\\n",
       "0  lahore      PKR       247.0  2014-01-01 06:12:39+00:00   \n",
       "1  lahore      PKR       247.0  2014-01-01 06:51:08+00:00   \n",
       "\n",
       "              disbursed_time                funded_time  term_in_months  \\\n",
       "0  2013-12-17 08:00:00+00:00  2014-01-02 10:06:32+00:00            12.0   \n",
       "1  2013-12-17 08:00:00+00:00  2014-01-02 09:17:23+00:00            11.0   \n",
       "\n",
       "   lender_count tags borrower_genders repayment_interval        date  \n",
       "0            12  NaN           female          irregular  2014-01-01  \n",
       "1            14  NaN   female, female          irregular  2014-01-01  "
      ]
     },
     "execution_count": 62,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "loans.head(2)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 63,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "145.0    107188\n",
       "199.0     19446\n",
       "177.0     16414\n",
       "311.0     15837\n",
       "156.0     15516\n",
       "Name: partner_id, dtype: int64"
      ]
     },
     "execution_count": 63,
     "metadata": {},
     "output_type": "execute_result"
    },
    {
     "data": {
      "text/plain": [
       "<matplotlib.figure.Figure at 0x26dc9d6e748>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "plt.figure(figsize=(8,6))\n",
    "#loans['partner_id'].value_counts().plot()\n",
    "loans['partner_id'].value_counts().head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 64,
   "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>Country</th>\n",
       "      <th>ISO_2</th>\n",
       "      <th>ISO_3</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>234</th>\n",
       "      <td>United States of America</td>\n",
       "      <td>US</td>\n",
       "      <td>USA</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                       Country ISO_2 ISO_3\n",
       "234  United States of America     US   USA"
      ]
     },
     "execution_count": 64,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "ISO_code.loc[ISO_code['ISO_2'] == 'US']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 65,
   "metadata": {},
   "outputs": [],
   "source": [
    "loans = pd.merge(loans, ISO_code, how = 'left', left_on= [\"country_code\"], right_on = [\"ISO_2\"])\n",
    "loans = pd.merge(loans, mpi, how = \"left\", left_on = \"ISO_3\", right_on = \"ISO\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 66,
   "metadata": {},
   "outputs": [],
   "source": [
    "loans = loans.drop(['ISO_2', 'ISO', 'Country'], axis = 1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 67,
   "metadata": {},
   "outputs": [],
   "source": [
    "loans['MPI'] = loans['MPI'].fillna(0)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 69,
   "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>id</th>\n",
       "      <th>funded_amount</th>\n",
       "      <th>loan_amount</th>\n",
       "      <th>activity</th>\n",
       "      <th>sector</th>\n",
       "      <th>use</th>\n",
       "      <th>country_code</th>\n",
       "      <th>country</th>\n",
       "      <th>region</th>\n",
       "      <th>currency</th>\n",
       "      <th>partner_id</th>\n",
       "      <th>posted_time</th>\n",
       "      <th>disbursed_time</th>\n",
       "      <th>funded_time</th>\n",
       "      <th>term_in_months</th>\n",
       "      <th>lender_count</th>\n",
       "      <th>tags</th>\n",
       "      <th>borrower_genders</th>\n",
       "      <th>repayment_interval</th>\n",
       "      <th>date</th>\n",
       "      <th>ISO_3</th>\n",
       "      <th>MPI</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>653051</td>\n",
       "      <td>300.0</td>\n",
       "      <td>300.0</td>\n",
       "      <td>Fruits &amp; Vegetables</td>\n",
       "      <td>Food</td>\n",
       "      <td>To buy seasonal, fresh fruits to sell.</td>\n",
       "      <td>PK</td>\n",
       "      <td>Pakistan</td>\n",
       "      <td>lahore</td>\n",
       "      <td>PKR</td>\n",
       "      <td>247.0</td>\n",
       "      <td>2014-01-01 06:12:39+00:00</td>\n",
       "      <td>2013-12-17 08:00:00+00:00</td>\n",
       "      <td>2014-01-02 10:06:32+00:00</td>\n",
       "      <td>12.0</td>\n",
       "      <td>12</td>\n",
       "      <td>NaN</td>\n",
       "      <td>female</td>\n",
       "      <td>irregular</td>\n",
       "      <td>2014-01-01</td>\n",
       "      <td>PAK</td>\n",
       "      <td>0.23032</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "       id  funded_amount  loan_amount             activity sector  \\\n",
       "0  653051          300.0        300.0  Fruits & Vegetables   Food   \n",
       "\n",
       "                                       use country_code   country  region  \\\n",
       "0  To buy seasonal, fresh fruits to sell.            PK  Pakistan  lahore   \n",
       "\n",
       "  currency  partner_id                posted_time             disbursed_time  \\\n",
       "0      PKR       247.0  2014-01-01 06:12:39+00:00  2013-12-17 08:00:00+00:00   \n",
       "\n",
       "                 funded_time  term_in_months  lender_count tags  \\\n",
       "0  2014-01-02 10:06:32+00:00            12.0            12  NaN   \n",
       "\n",
       "  borrower_genders repayment_interval        date ISO_3      MPI  \n",
       "0           female          irregular  2014-01-01   PAK  0.23032  "
      ]
     },
     "execution_count": 69,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "loans.head(1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 70,
   "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>id</th>\n",
       "      <th>funded_amount</th>\n",
       "      <th>loan_amount</th>\n",
       "      <th>activity</th>\n",
       "      <th>sector</th>\n",
       "      <th>use</th>\n",
       "      <th>country_code</th>\n",
       "      <th>country</th>\n",
       "      <th>region</th>\n",
       "      <th>currency</th>\n",
       "      <th>partner_id</th>\n",
       "      <th>posted_time</th>\n",
       "      <th>disbursed_time</th>\n",
       "      <th>funded_time</th>\n",
       "      <th>term_in_months</th>\n",
       "      <th>lender_count</th>\n",
       "      <th>tags</th>\n",
       "      <th>borrower_genders</th>\n",
       "      <th>repayment_interval</th>\n",
       "      <th>date</th>\n",
       "      <th>ISO_3</th>\n",
       "      <th>MPI</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>count</th>\n",
       "      <td>6.712050e+05</td>\n",
       "      <td>671205.000000</td>\n",
       "      <td>671205.000000</td>\n",
       "      <td>671205</td>\n",
       "      <td>671205</td>\n",
       "      <td>666973</td>\n",
       "      <td>671197</td>\n",
       "      <td>671205</td>\n",
       "      <td>671205</td>\n",
       "      <td>671205</td>\n",
       "      <td>657698.000000</td>\n",
       "      <td>671205</td>\n",
       "      <td>668809</td>\n",
       "      <td>622874</td>\n",
       "      <td>671205.000000</td>\n",
       "      <td>671205.000000</td>\n",
       "      <td>499789</td>\n",
       "      <td>666984</td>\n",
       "      <td>671205</td>\n",
       "      <td>671205</td>\n",
       "      <td>669786</td>\n",
       "      <td>671205.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>unique</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>163</td>\n",
       "      <td>15</td>\n",
       "      <td>424912</td>\n",
       "      <td>86</td>\n",
       "      <td>87</td>\n",
       "      <td>12738</td>\n",
       "      <td>67</td>\n",
       "      <td>NaN</td>\n",
       "      <td>667399</td>\n",
       "      <td>5719</td>\n",
       "      <td>498007</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>86719</td>\n",
       "      <td>11298</td>\n",
       "      <td>4</td>\n",
       "      <td>1298</td>\n",
       "      <td>86</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>top</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Farming</td>\n",
       "      <td>Agriculture</td>\n",
       "      <td>to buy a water filter to provide safe drinking...</td>\n",
       "      <td>PH</td>\n",
       "      <td>Philippines</td>\n",
       "      <td>sv</td>\n",
       "      <td>PHP</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2017-05-15 00:00:00+00:00</td>\n",
       "      <td>2017-02-01 08:00:00+00:00</td>\n",
       "      <td>2016-09-21 13:03:24+00:00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>user_favorite</td>\n",
       "      <td>female</td>\n",
       "      <td>monthly</td>\n",
       "      <td>2017-03-20</td>\n",
       "      <td>PHL</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>freq</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>72955</td>\n",
       "      <td>180302</td>\n",
       "      <td>5217</td>\n",
       "      <td>160441</td>\n",
       "      <td>160441</td>\n",
       "      <td>20251</td>\n",
       "      <td>160440</td>\n",
       "      <td>NaN</td>\n",
       "      <td>25</td>\n",
       "      <td>2800</td>\n",
       "      <td>33</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>27088</td>\n",
       "      <td>426502</td>\n",
       "      <td>342717</td>\n",
       "      <td>1308</td>\n",
       "      <td>160441</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>mean</th>\n",
       "      <td>9.932486e+05</td>\n",
       "      <td>785.995061</td>\n",
       "      <td>842.397107</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>178.199616</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>13.739022</td>\n",
       "      <td>20.590922</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.116783</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>std</th>\n",
       "      <td>1.966113e+05</td>\n",
       "      <td>1130.398941</td>\n",
       "      <td>1198.660073</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>94.247581</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>8.598919</td>\n",
       "      <td>28.459551</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.116243</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>min</th>\n",
       "      <td>6.530470e+05</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>25.000000</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>9.000000</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25%</th>\n",
       "      <td>8.230720e+05</td>\n",
       "      <td>250.000000</td>\n",
       "      <td>275.000000</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>126.000000</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>8.000000</td>\n",
       "      <td>7.000000</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.029003</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>50%</th>\n",
       "      <td>9.927800e+05</td>\n",
       "      <td>450.000000</td>\n",
       "      <td>500.000000</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>145.000000</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>13.000000</td>\n",
       "      <td>13.000000</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.052128</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>75%</th>\n",
       "      <td>1.163653e+06</td>\n",
       "      <td>900.000000</td>\n",
       "      <td>1000.000000</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>204.000000</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>14.000000</td>\n",
       "      <td>24.000000</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.187418</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>max</th>\n",
       "      <td>1.340339e+06</td>\n",
       "      <td>100000.000000</td>\n",
       "      <td>100000.000000</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>536.000000</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>158.000000</td>\n",
       "      <td>2986.000000</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.557405</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                  id  funded_amount    loan_amount activity       sector  \\\n",
       "count   6.712050e+05  671205.000000  671205.000000   671205       671205   \n",
       "unique           NaN            NaN            NaN      163           15   \n",
       "top              NaN            NaN            NaN  Farming  Agriculture   \n",
       "freq             NaN            NaN            NaN    72955       180302   \n",
       "mean    9.932486e+05     785.995061     842.397107      NaN          NaN   \n",
       "std     1.966113e+05    1130.398941    1198.660073      NaN          NaN   \n",
       "min     6.530470e+05       0.000000      25.000000      NaN          NaN   \n",
       "25%     8.230720e+05     250.000000     275.000000      NaN          NaN   \n",
       "50%     9.927800e+05     450.000000     500.000000      NaN          NaN   \n",
       "75%     1.163653e+06     900.000000    1000.000000      NaN          NaN   \n",
       "max     1.340339e+06  100000.000000  100000.000000      NaN          NaN   \n",
       "\n",
       "                                                      use country_code  \\\n",
       "count                                              666973       671197   \n",
       "unique                                             424912           86   \n",
       "top     to buy a water filter to provide safe drinking...           PH   \n",
       "freq                                                 5217       160441   \n",
       "mean                                                  NaN          NaN   \n",
       "std                                                   NaN          NaN   \n",
       "min                                                   NaN          NaN   \n",
       "25%                                                   NaN          NaN   \n",
       "50%                                                   NaN          NaN   \n",
       "75%                                                   NaN          NaN   \n",
       "max                                                   NaN          NaN   \n",
       "\n",
       "            country  region currency     partner_id  \\\n",
       "count        671205  671205   671205  657698.000000   \n",
       "unique           87   12738       67            NaN   \n",
       "top     Philippines      sv      PHP            NaN   \n",
       "freq         160441   20251   160440            NaN   \n",
       "mean            NaN     NaN      NaN     178.199616   \n",
       "std             NaN     NaN      NaN      94.247581   \n",
       "min             NaN     NaN      NaN       9.000000   \n",
       "25%             NaN     NaN      NaN     126.000000   \n",
       "50%             NaN     NaN      NaN     145.000000   \n",
       "75%             NaN     NaN      NaN     204.000000   \n",
       "max             NaN     NaN      NaN     536.000000   \n",
       "\n",
       "                      posted_time             disbursed_time  \\\n",
       "count                      671205                     668809   \n",
       "unique                     667399                       5719   \n",
       "top     2017-05-15 00:00:00+00:00  2017-02-01 08:00:00+00:00   \n",
       "freq                           25                       2800   \n",
       "mean                          NaN                        NaN   \n",
       "std                           NaN                        NaN   \n",
       "min                           NaN                        NaN   \n",
       "25%                           NaN                        NaN   \n",
       "50%                           NaN                        NaN   \n",
       "75%                           NaN                        NaN   \n",
       "max                           NaN                        NaN   \n",
       "\n",
       "                      funded_time  term_in_months   lender_count  \\\n",
       "count                      622874   671205.000000  671205.000000   \n",
       "unique                     498007             NaN            NaN   \n",
       "top     2016-09-21 13:03:24+00:00             NaN            NaN   \n",
       "freq                           33             NaN            NaN   \n",
       "mean                          NaN       13.739022      20.590922   \n",
       "std                           NaN        8.598919      28.459551   \n",
       "min                           NaN        1.000000       0.000000   \n",
       "25%                           NaN        8.000000       7.000000   \n",
       "50%                           NaN       13.000000      13.000000   \n",
       "75%                           NaN       14.000000      24.000000   \n",
       "max                           NaN      158.000000    2986.000000   \n",
       "\n",
       "                 tags borrower_genders repayment_interval        date   ISO_3  \\\n",
       "count          499789           666984             671205      671205  669786   \n",
       "unique          86719            11298                  4        1298      86   \n",
       "top     user_favorite           female            monthly  2017-03-20     PHL   \n",
       "freq            27088           426502             342717        1308  160441   \n",
       "mean              NaN              NaN                NaN         NaN     NaN   \n",
       "std               NaN              NaN                NaN         NaN     NaN   \n",
       "min               NaN              NaN                NaN         NaN     NaN   \n",
       "25%               NaN              NaN                NaN         NaN     NaN   \n",
       "50%               NaN              NaN                NaN         NaN     NaN   \n",
       "75%               NaN              NaN                NaN         NaN     NaN   \n",
       "max               NaN              NaN                NaN         NaN     NaN   \n",
       "\n",
       "                  MPI  \n",
       "count   671205.000000  \n",
       "unique            NaN  \n",
       "top               NaN  \n",
       "freq              NaN  \n",
       "mean         0.116783  \n",
       "std          0.116243  \n",
       "min          0.000000  \n",
       "25%          0.029003  \n",
       "50%          0.052128  \n",
       "75%          0.187418  \n",
       "max          0.557405  "
      ]
     },
     "execution_count": 70,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "loans.describe(include = 'all')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 71,
   "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>LocationName</th>\n",
       "      <th>ISO</th>\n",
       "      <th>country</th>\n",
       "      <th>region</th>\n",
       "      <th>world_region</th>\n",
       "      <th>MPI</th>\n",
       "      <th>geo</th>\n",
       "      <th>lat</th>\n",
       "      <th>lon</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Badakhshan, Afghanistan</td>\n",
       "      <td>AFG</td>\n",
       "      <td>Afghanistan</td>\n",
       "      <td>Badakhshan</td>\n",
       "      <td>South Asia</td>\n",
       "      <td>0.387</td>\n",
       "      <td>(36.7347725, 70.81199529999999)</td>\n",
       "      <td>36.734772</td>\n",
       "      <td>70.811995</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Badghis, Afghanistan</td>\n",
       "      <td>AFG</td>\n",
       "      <td>Afghanistan</td>\n",
       "      <td>Badghis</td>\n",
       "      <td>South Asia</td>\n",
       "      <td>0.466</td>\n",
       "      <td>(35.1671339, 63.7695384)</td>\n",
       "      <td>35.167134</td>\n",
       "      <td>63.769538</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "              LocationName  ISO      country      region world_region    MPI  \\\n",
       "0  Badakhshan, Afghanistan  AFG  Afghanistan  Badakhshan   South Asia  0.387   \n",
       "1     Badghis, Afghanistan  AFG  Afghanistan     Badghis   South Asia  0.466   \n",
       "\n",
       "                               geo        lat        lon  \n",
       "0  (36.7347725, 70.81199529999999)  36.734772  70.811995  \n",
       "1         (35.1671339, 63.7695384)  35.167134  63.769538  "
      ]
     },
     "execution_count": 71,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "region_mpi.head(2)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 72,
   "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>LocationName</th>\n",
       "      <th>ISO</th>\n",
       "      <th>country</th>\n",
       "      <th>region</th>\n",
       "      <th>world_region</th>\n",
       "      <th>MPI</th>\n",
       "      <th>geo</th>\n",
       "      <th>lat</th>\n",
       "      <th>lon</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>count</th>\n",
       "      <td>984</td>\n",
       "      <td>1008</td>\n",
       "      <td>1008</td>\n",
       "      <td>984</td>\n",
       "      <td>1008</td>\n",
       "      <td>984.000000</td>\n",
       "      <td>2772</td>\n",
       "      <td>892.000000</td>\n",
       "      <td>892.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>unique</th>\n",
       "      <td>984</td>\n",
       "      <td>102</td>\n",
       "      <td>102</td>\n",
       "      <td>928</td>\n",
       "      <td>6</td>\n",
       "      <td>NaN</td>\n",
       "      <td>881</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>top</th>\n",
       "      <td>East, Rwanda</td>\n",
       "      <td>NGA</td>\n",
       "      <td>Nigeria</td>\n",
       "      <td>Central</td>\n",
       "      <td>Sub-Saharan Africa</td>\n",
       "      <td>NaN</td>\n",
       "      <td>(1000.0, 1000.0)</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>freq</th>\n",
       "      <td>1</td>\n",
       "      <td>37</td>\n",
       "      <td>37</td>\n",
       "      <td>8</td>\n",
       "      <td>432</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1880</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>mean</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.211330</td>\n",
       "      <td>NaN</td>\n",
       "      <td>9.169710</td>\n",
       "      <td>16.635888</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>std</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.183621</td>\n",
       "      <td>NaN</td>\n",
       "      <td>16.484531</td>\n",
       "      <td>61.234566</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>min</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>NaN</td>\n",
       "      <td>-34.947896</td>\n",
       "      <td>-122.747131</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25%</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.053000</td>\n",
       "      <td>NaN</td>\n",
       "      <td>-1.027901</td>\n",
       "      <td>-12.819854</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>50%</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.155000</td>\n",
       "      <td>NaN</td>\n",
       "      <td>11.211379</td>\n",
       "      <td>26.419389</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>75%</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.341500</td>\n",
       "      <td>NaN</td>\n",
       "      <td>18.084292</td>\n",
       "      <td>47.019436</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>max</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.744000</td>\n",
       "      <td>NaN</td>\n",
       "      <td>49.264748</td>\n",
       "      <td>138.581284</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "        LocationName   ISO  country   region        world_region         MPI  \\\n",
       "count            984  1008     1008      984                1008  984.000000   \n",
       "unique           984   102      102      928                   6         NaN   \n",
       "top     East, Rwanda   NGA  Nigeria  Central  Sub-Saharan Africa         NaN   \n",
       "freq               1    37       37        8                 432         NaN   \n",
       "mean             NaN   NaN      NaN      NaN                 NaN    0.211330   \n",
       "std              NaN   NaN      NaN      NaN                 NaN    0.183621   \n",
       "min              NaN   NaN      NaN      NaN                 NaN    0.000000   \n",
       "25%              NaN   NaN      NaN      NaN                 NaN    0.053000   \n",
       "50%              NaN   NaN      NaN      NaN                 NaN    0.155000   \n",
       "75%              NaN   NaN      NaN      NaN                 NaN    0.341500   \n",
       "max              NaN   NaN      NaN      NaN                 NaN    0.744000   \n",
       "\n",
       "                     geo         lat         lon  \n",
       "count               2772  892.000000  892.000000  \n",
       "unique               881         NaN         NaN  \n",
       "top     (1000.0, 1000.0)         NaN         NaN  \n",
       "freq                1880         NaN         NaN  \n",
       "mean                 NaN    9.169710   16.635888  \n",
       "std                  NaN   16.484531   61.234566  \n",
       "min                  NaN  -34.947896 -122.747131  \n",
       "25%                  NaN   -1.027901  -12.819854  \n",
       "50%                  NaN   11.211379   26.419389  \n",
       "75%                  NaN   18.084292   47.019436  \n",
       "max                  NaN   49.264748  138.581284  "
      ]
     },
     "execution_count": 72,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "region_mpi.describe(include = 'all')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 73,
   "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>id</th>\n",
       "      <th>Loan Theme ID</th>\n",
       "      <th>Loan Theme Type</th>\n",
       "      <th>Partner ID</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>638631</td>\n",
       "      <td>a1050000000skGl</td>\n",
       "      <td>General</td>\n",
       "      <td>151.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>640322</td>\n",
       "      <td>a1050000000skGl</td>\n",
       "      <td>General</td>\n",
       "      <td>151.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "       id    Loan Theme ID Loan Theme Type  Partner ID\n",
       "0  638631  a1050000000skGl         General       151.0\n",
       "1  640322  a1050000000skGl         General       151.0"
      ]
     },
     "execution_count": 73,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "theme.head(2)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 74,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "<matplotlib.axes._subplots.AxesSubplot at 0x26dc9d53be0>"
      ]
     },
     "execution_count": 74,
     "metadata": {},
     "output_type": "execute_result"
    },
    {
     "data": {
      "image/png": "iVBORw0KGgoAAAANSUhEUgAAAY0AAADvCAYAAAAO7Ig0AAAABHNCSVQICAgIfAhkiAAAAAlwSFlzAAALEgAACxIB0t1+/AAAHPVJREFUeJzt3X+MXeWd3/H3594x/onN2Ey9ztgJv5ymQLtOPTK0rFJ2rdhuWtVEAdaRNvgPC6eCptlqqxXkH2dBVkHaLC1/gOosFoZmAxbZCG8ESx2TajfaYntICMZmiScx1J4Ye/AYm1/+MTPf/nGea5853Hvmzi/PmHxe0tWc+73nee5zieRPznOeex9FBGZmZs2oTPQAzMzs4uHQMDOzpjk0zMysaQ4NMzNrmkPDzMya5tAwM7OmNR0akqqSfi7pR+n5XEnbJe1Pf1tz594rqUvSG5JW5upLJe1Jrz0sSak+VdLTqb5T0hW5NmvTe+yXtHYsPrSZmY3McK40vgm8nnt+D7AjIhYDO9JzJF0LrAGuA1YBj0iqpjaPAncCi9NjVaqvA45HxDXAQ8CDqa+5wAbgBmAZsCEfTmZmdmE1FRqSFgL/DvjLXHk1sCUdbwFuydWfiojTEXEA6AKWSVoAzI6IlyL7RuEThTa1vp4BlqerkJXA9ojojYjjwHbOB42ZmV1gzV5p/HfgT4GBXG1+RBxOx28D89NxO3Awd96hVGtPx8X6oDYR0QecAOaV9GVmZhOgZagTJP174GhEvCzp5nrnRERImrDfI5G0HlgPMHPmzKWf+9znJmooZmYXpZdffvmdiGgb6rwhQwO4CfgPkr4ETANmS/pfwBFJCyLicJp6OprO7wYW5dovTLXudFys59scktQCzAGOpfrNhTb/pzjAiNgEbALo6OiIzs7OJj6WmZnVSHqrmfOGnJ6KiHsjYmFEXEF2g/vFiPgjYBtQW820Fng2HW8D1qQVUVeS3fDelaayTkq6Md2vuKPQptbXrek9AngBWCGpNd0AX5FqZmY2AZq50mjkAWCrpHXAW8DtABGxV9JWYB/QB9wdEf2pzV3A48B04Pn0AHgMeFJSF9BLFk5ERK+k+4Hd6bz7IqJ3FGM2M7NR0Cftp9E9PWVmNnySXo6IjqHO8zfCzcysaQ4NMzNrmkPDzMyaNpob4Z8oEUHt9k6lookdjJnZJOUrjeTYB2e46lvP8b2dTS1VNjP7reTQSCrZD+4y8MlaTGZmNqYcGkltRqrfqWFm1pBDI6ndxxj4hH1vxcxsLDk0ktr0lDPDzKwxh0ZybnrKqWFm1pBDIzl/I9yhYWbWiEMj8fSUmdnQHBqJV0+ZmQ3NoZFUvXrKzGxIDo1E/nKfmdmQHBo5FcGAU8PMrCGHRk61Ik9PmZmVGDI0JE2TtEvSLyTtlfRnqf5tSd2SXkmPL+Xa3CupS9Ibklbm6ksl7UmvPZz2CiftJ/50qu+UdEWuzVpJ+9NjLeNIkqenzMxKNPPT6KeBP4iI9yVNAX4qqba390MR8ef5kyVdS7bH93XAp4AfS/ps2if8UeBOYCfwHLCKbJ/wdcDxiLhG0hrgQeAPJc0FNgAdQAAvS9oWEcdH97Hrq8g3ws3Mygx5pRGZ99PTKelR9i/rauCpiDgdEQeALmCZpAXA7Ih4KbKNyZ8Absm12ZKOnwGWp6uQlcD2iOhNQbGdLGjGRVXyPQ0zsxJN3dOQVJX0CnCU7B/xnemlb0h6VdJmSa2p1g4czDU/lGrt6bhYH9QmIvqAE8C8kr7GRcXTU2ZmpZoKjYjoj4glwEKyq4bryaaargKWAIeB74zbKIcgab2kTkmdPT09o+jH01NmZmWGtXoqIt4FfgKsiogjKUwGgO8Cy9Jp3cCiXLOFqdadjov1QW0ktQBzgGMlfRXHtSkiOiKio62tbTgfaRCvnjIzK9fM6qk2SZel4+nAF4F/TPcoar4MvJaOtwFr0oqoK4HFwK6IOAyclHRjul9xB/Bsrk1tZdStwIvpvscLwApJrWn6a0WqjYtsesqhYWbWSDOrpxYAWyRVyUJma0T8SNKTkpaQ3RR/E/g6QETslbQV2Af0AXenlVMAdwGPA9PJVk3VVmE9BjwpqQvoJVt9RUT0Srof2J3Ouy8iekfxeUtJon9gvHo3M7v4DRkaEfEq8Pk69a+VtNkIbKxT7wSur1M/BdzWoK/NwOahxjkWqhUIX2mYmTXkb4TneHrKzKycQyOn4ukpM7NSDo2ciqenzMxKOTRyPD1lZlbOoZFTkeh3ZpiZNeTQyPEPFpqZlXNo5FQk39MwMyvh0MjJVk85NMzMGnFo5FQq/pVbM7MyDo2cirzk1sysjEMjx9NTZmblHBo5np4yMyvn0Mjxklszs3IOjRx/I9zMrJxDI6cqMeAfLDQza8ihkeM9ws3Myjk0cjw9ZWZWrpk9wqdJ2iXpF5L2SvqzVJ8rabuk/elva67NvZK6JL0haWWuvlTSnvTaw2mvcNJ+4k+n+k5JV+TarE3vsV/SWsZR1aunzMxKNXOlcRr4g4j4XWAJsErSjcA9wI6IWAzsSM+RdC3ZHt/XAauAR9L+4gCPAncCi9NjVaqvA45HxDXAQ8CDqa+5wAbgBmAZsCEfTmPN01NmZuWGDI3IvJ+eTkmPAFYDW1J9C3BLOl4NPBURpyPiANAFLJO0AJgdES9F9rXrJwptan09AyxPVyErge0R0RsRx4HtnA+aMVeRGPClhplZQ03d05BUlfQKcJTsH/GdwPyIOJxOeRuYn47bgYO55odSrT0dF+uD2kREH3ACmFfS17jw9JSZWbmmQiMi+iNiCbCQ7Krh+sLrQXb1MSEkrZfUKamzp6dnxP34y31mZuWGtXoqIt4FfkI2RXQkTTmR/h5Np3UDi3LNFqZadzou1ge1kdQCzAGOlfRVHNemiOiIiI62trbhfKRB5N+eMjMr1czqqTZJl6Xj6cAXgX8EtgG11UxrgWfT8TZgTVoRdSXZDe9daSrrpKQb0/2KOwptan3dCryYrl5eAFZIak03wFek2rioSvhCw8yssZYmzlkAbEkroCrA1oj4kaT/C2yVtA54C7gdICL2StoK7AP6gLsjoj/1dRfwODAdeD49AB4DnpTUBfSSrb4iInol3Q/sTufdFxG9o/nAZSoVT0+ZmZUZMjQi4lXg83Xqx4DlDdpsBDbWqXcC19epnwJua9DXZmDzUOMcC5Lod2iYmTXkb4TneHrKzKycQyPHq6fMzMo5NHK8c5+ZWTmHRk6l4ukpM7MyDo0cT0+ZmZVzaOR4esrMrJxDI6fi354yMyvl0Mjx9JSZWTmHRo537jMzK+fQyPF+GmZm5RwaOdmVxkSPwsxs8nJo5PiehplZOYdGTrZzn0PDzKwRh0aOJAYGJnoUZmaTl0Mjx9NTZmblHBo5np4yMyvn0MhRWj0VDg4zs7qa2SN8kaSfSNonaa+kb6b6tyV1S3olPb6Ua3OvpC5Jb0hamasvlbQnvfZw2iuctJ/406m+U9IVuTZrJe1Pj7WMo4qyv84MM7P6mtkjvA/4k4j4maRLgZclbU+vPRQRf54/WdK1ZHt8Xwd8CvixpM+mfcIfBe4EdgLPAavI9glfBxyPiGskrQEeBP5Q0lxgA9ABRHrvbRFxfHQfu75qlmEMRFBB4/EWZmYXtSGvNCLicET8LB2/B7wOtJc0WQ08FRGnI+IA0AUsk7QAmB0RL0U2//MEcEuuzZZ0/AywPF2FrAS2R0RvCortZEEzLirpUsP7hJuZ1Tesexpp2ujzZFcKAN+Q9KqkzZJaU60dOJhrdijV2tNxsT6oTUT0ASeAeSV9jYtKutJwZpiZ1dd0aEiaBfwA+OOIOEk21XQVsAQ4DHxnXEbY3NjWS+qU1NnT0zPifmr3NLyCysysvqZCQ9IUssD4XkT8NUBEHImI/ogYAL4LLEundwOLcs0Xplp3Oi7WB7WR1ALMAY6V9DVIRGyKiI6I6Ghra2vmI9VVu9LwRkxmZvU1s3pKwGPA6xHxF7n6gtxpXwZeS8fbgDVpRdSVwGJgV0QcBk5KujH1eQfwbK5NbWXUrcCL6b7HC8AKSa1p+mtFqo2L2j0NZ4aZWX3NrJ66CfgasEfSK6n2LeCrkpaQrWp6E/g6QETslbQV2Ee28urutHIK4C7gcWA62aqp51P9MeBJSV1AL9nqKyKiV9L9wO503n0R0Tuyjzq080tunRpmZvUMGRoR8VOou/70uZI2G4GNdeqdwPV16qeA2xr0tRnYPNQ4x4Knp8zMyvkb4TmenjIzK+fQyPH0lJlZOYdGzrnpKYeGmVldDo2c8z8jMsEDMTObpBwaOap9uc+pYWZWl0Mjp5L7wUIzM/s4h0ZO1aunzMxKOTRy5N+eMjMr5dDIOTc95UsNM7O6HBo5np4yMyvn0MjxT6ObmZVzaOTIvz1lZlbKoZFT9c59ZmalHBo5lfRfw9NTZmb1OTRy5N+eMjMr5dDIOT895dAwM6vHoZFT8Q8WmpmVamaP8EWSfiJpn6S9kr6Z6nMlbZe0P/1tzbW5V1KXpDckrczVl0rak157OO0VTtpP/OlU3ynpilybtek99ktayziqLbn16ikzs/qaudLoA/4kIq4FbgTulnQtcA+wIyIWAzvSc9Jra4DrgFXAI5Kqqa9HgTuBxemxKtXXAccj4hrgIeDB1NdcYANwA7AM2JAPp7F2fuc+h4aZWT1DhkZEHI6In6Xj94DXgXZgNbAlnbYFuCUdrwaeiojTEXEA6AKWSVoAzI6IlyK7afBEoU2tr2eA5ekqZCWwPSJ6I+I4sJ3zQTPmKl5ya2ZWalj3NNK00eeBncD8iDicXnobmJ+O24GDuWaHUq09HRfrg9pERB9wAphX0te48PSUmVm5pkND0izgB8AfR8TJ/GvpymHC/qWVtF5Sp6TOnp6eEffj6Skzs3JNhYakKWSB8b2I+OtUPpKmnEh/j6Z6N7Ao13xhqnWn42J9UBtJLcAc4FhJX4NExKaI6IiIjra2tmY+Ul2enjIzK9fM6ikBjwGvR8Rf5F7aBtRWM60Fns3V16QVUVeS3fDelaayTkq6MfV5R6FNra9bgRfT1csLwApJrekG+IpUGxeenjIzK9fSxDk3AV8D9kh6JdW+BTwAbJW0DngLuB0gIvZK2grsI1t5dXdE9Kd2dwGPA9OB59MDslB6UlIX0Eu2+oqI6JV0P7A7nXdfRPSO8LMOydu9mpmVGzI0IuKngBq8vLxBm43Axjr1TuD6OvVTwG0N+toMbB5qnGPBX+4zMyvnb4Tn+AcLzczKOTRyqp6eMjMr5dDIkaenzMxKOTRyzm336tQwM6vLoZFT9Zf7zMxKOTRyvHrKzKycQyNHnp4yMyvl0Mjx9JSZWTmHRo6np8zMyjk0cmrTU/2+0jAzq8uhkVM99yu3Dg0zs3ocGjnnpqc8P2VmVpdDI6cWGv3ODDOzuhwaObUfLPT0lJlZfQ6NnHNXGp6eMjOry6GR4yW3ZmblHBo53k/DzKxcM3uEb5Z0VNJrudq3JXVLeiU9vpR77V5JXZLekLQyV18qaU967eG0TzhpL/GnU32npCtybdZK2p8etT3Ex41XT5mZlWvmSuNxYFWd+kMRsSQ9ngOQdC3Z/t7XpTaPSKqm8x8F7gQWp0etz3XA8Yi4BngIeDD1NRfYANwALAM2SGod9iccBk9PmZmVGzI0IuLvgN4m+1sNPBURpyPiANAFLJO0AJgdES9FtjTpCeCWXJst6fgZYHm6ClkJbI+I3og4DmynfniNmXP7aXh6ysysrtHc0/iGpFfT9FXtCqAdOJg751CqtafjYn1Qm4joA04A80r6GjeSkBwaZmaNjDQ0HgWuApYAh4HvjNmIRkDSekmdkjp7enpG1VdFcmiYmTUwotCIiCMR0R8RA8B3ye45AHQDi3KnLky17nRcrA9qI6kFmAMcK+mr3ng2RURHRHS0tbWN5COdU5V8T8PMrIERhUa6R1HzZaC2smobsCatiLqS7Ib3rog4DJyUdGO6X3EH8GyuTW1l1K3Ai+m+xwvACkmtafprRaqNK8mrp8zMGmkZ6gRJ3wduBi6XdIhsRdPNkpYAAbwJfB0gIvZK2grsA/qAuyOiP3V1F9lKrOnA8+kB8BjwpKQushvua1JfvZLuB3an8+6LiGZvyI+Yp6fMzBobMjQi4qt1yo+VnL8R2Fin3glcX6d+CritQV+bgc1DjXEsVSuenjIza8TfCC+Q/NtTZmaNODQKKpJ/5dbMrAGHRoGnp8zMGnNoFFTkPcLNzBpxaBTI01NmZg05NAqqEgMDEz0KM7PJyaFR4OkpM7PGHBoF8pf7zMwacmgUVCvCmWFmVp9Do6DiL/eZmTXk0CioVDw9ZWbWiEOjIPtG+ESPwsxscnJoFHh6ysysMYdGgX8a3cysMYdGQcU795mZNeTQKKhU8JWGmVkDDo2CqqenzMwaGjI0JG2WdFTSa7naXEnbJe1Pf1tzr90rqUvSG5JW5upLJe1Jrz2c9gon7Sf+dKrvlHRFrs3a9B77JdX2ER9X8vSUmVlDzVxpPA6sKtTuAXZExGJgR3qOpGvJ9vi+LrV5RFI1tXkUuBNYnB61PtcBxyPiGuAh4MHU11yy/chvAJYBG/LhNF4qggGnhplZXUOGRkT8HdBbKK8GtqTjLcAtufpTEXE6Ig4AXcAySQuA2RHxUmS/O/5EoU2tr2eA5ekqZCWwPSJ6I+I4sJ2Ph9eYq/rLfWZmDY30nsb8iDicjt8G5qfjduBg7rxDqdaejov1QW0iog84Acwr6etjJK2X1Cmps6enZ4Qf6VxfDg0zswZGfSM8XTlM6L+yEbEpIjoioqOtrW1UfWXTU2M0MDOzT5iRhsaRNOVE+ns01buBRbnzFqZadzou1ge1kdQCzAGOlfQ1rjw9ZWbW2EhDYxtQW820Fng2V1+TVkRdSXbDe1eayjop6cZ0v+KOQptaX7cCL6arlxeAFZJa0w3wFak2rvyNcDOzxlqGOkHS94GbgcslHSJb0fQAsFXSOuAt4HaAiNgraSuwD+gD7o6I/tTVXWQrsaYDz6cHwGPAk5K6yG64r0l99Uq6H9idzrsvIoo35MecJPqdGWZmdQ0ZGhHx1QYvLW9w/kZgY516J3B9nfop4LYGfW0GNg81xrFUFYSvNMzM6vI3wgs8PWVm1phDo0AS/V49ZWZWl0OjoFrx9JSZWSMOjQJPT5mZNebQKKhI3rnPzKwBh0ZBpeI9ws3MGnFoFFTkTZjMzBpxaBRUJPodGmZmdTk0CiqSf7DQzKwBh0ZBxd8INzNryKFR4OkpM7PGHBoFlYr3CDcza8ShUeDpKTOzxhwaBf5yn5lZYw6NgstmTOHER2c5618tNDP7GIdGwaLWGQwE/ObdjyZ6KGZmk45Do2Dh3OkAHOx1aJiZFY0qNCS9KWmPpFckdabaXEnbJe1Pf1tz598rqUvSG5JW5upLUz9dkh5O+4iT9hp/OtV3SrpiNONtxqLWGQAcPP7heL+VmdlFZyyuNH4/IpZEREd6fg+wIyIWAzvScyRdS7b/93XAKuARSdXU5lHgTmBxeqxK9XXA8Yi4BngIeHAMxltqwZxpVCviYK9Dw8ysaDymp1YDW9LxFuCWXP2piDgdEQeALmCZpAXA7Ih4KbK1rk8U2tT6egZYXrsKGS8t1QqfumwaB497esrMrGi0oRHAjyW9LGl9qs2PiMPp+G1gfjpuBw7m2h5KtfZ0XKwPahMRfcAJYF5xEJLWS+qU1NnT0zPKj5RNUflKw8zs40YbGr8XEUuAfwvcLekL+RfTlcO4f+khIjZFREdEdLS1tY26v0WtMzjkexpmZh8zqtCIiO709yjwQ2AZcCRNOZH+Hk2ndwOLcs0Xplp3Oi7WB7WR1ALMAY6NZszNWDR3Ou+8f4YPz/SN91uZmV1URhwakmZKurR2DKwAXgO2AWvTaWuBZ9PxNmBNWhF1JdkN711pKuukpBvT/Yo7Cm1qfd0KvBgX4Dc+Fs3NVlAd8n0NM7NBWkbRdj7ww3RfugX4q4j4W0m7ga2S1gFvAbcDRMReSVuBfUAfcHdE9Ke+7gIeB6YDz6cHwGPAk5K6gF6y1VfjrhYaB3s/5LPzL70Qb2lmdlEYcWhExK+B361TPwYsb9BmI7CxTr0TuL5O/RRw20jHOFK172q8dcz3NczM8vyN8Doun3UJl05t4cA7H0z0UMzMJhWHRh2SuKptJr9+5/2JHoqZ2aTi0GjgqrZZ/LrHVxpmZnkOjQaubpvJ4ROn+OC0l92amdU4NBq4qm0WgO9rmJnlODQauDqFxq96fF/DzKzGodHAZ+bNQIJf+b6Gmdk5Do0Gpk2psrB1Or/2lYaZ2TkOjRJXt83il0feo8/7hZuZAQ6NUh2faeWXR97nXz/wItt+8ZuJHo6Z2YRzaJT4j//mav7n15bS3jqd//z9n3P/j/Zx9OSpiR6WmdmE0QX40dgLqqOjIzo7O8e0zzN9A3z7b/byVzv/H9WKmH/pVP7Zgtn8t6/8c/7JpdPG9L3MzCaCpJdz23Y3Ps+h0bwD73zAD3/ezaHjH/L8nrdpnTGFR/5oKUsWXTYu72dmdqE4NMbZa90nWP9EJ2+fPMW637uS/7rynzK1pTru72tmNh6aDQ3f0xih69vn8Lf/5QusWfZpvvv3B/jKo//Aq4fenehhmZmNK19pjIH/vfdt/vQHr/Luh2f53O9cytyZlzDjkhZmTq0yc2oLX/mX7Sz9zNwLOiYzs+Fo9kpjNDv3WbLiut/hX109j6d3H+Tv97/Dh2f6+M27H/HhmT6OvX+Gp3cf5OtfuIovfLaNha3TuXzWVKZN8VSWmV18LoorDUmrgP8BVIG/jIgHGp07EVcaZd47dZZv/fA1/qbwPY9ZU1v4FwvncNM1lzNv5iXMnj6F2dOmMHt6S/o7hUuntTCl6hlEMxt/n5gb4ZKqwC+BLwKHgN3AVyNiX73zJ1to1Bw5eYrXD5/kyMlTvPP+GY6ePMU//OoY+4+W/0zJjEuqg8JkxtQWqoKKhJRtGHX5rKl8eu4MprZUmFIVLdUK1Yqy40pWq1YqtFTFlPS3pSKmVFOtWsmOK7XjrI8p6bWWikh7wZvZJ9QnaXpqGdCV9iRH0lPAaqBuaExW82dPY/7sj3+n4/3TfZz86CwnT53l5Ef547OcPDX4tfdOn+XEh2cYCAiCgQEYiGD3m728++HZcR1/S0V1A6YYQFVlAVNJwVapnD+uVgqvqfgaVCvZcfZ+KbBgUGjVDkUKzlxd0rnn5M4b3O48NThn0HmpqEKbsr4HnVMI3OJ7lrWvd06x3/qfp3BOE5+r3vs289+MuufU/29W77/dcE3E/38Z6f9pGs1QR/KWrTMu4aZrLh/Fuw7tYgiNduBg7vkh4Ib8CZLWA+sBPv3pT1+4kY2BWVNbmDW1hU8xfVT9fHC6j7P9A5ztD/oGBujrD/oGgr5U6x8Iztbq/QOczb3WNzBwvm16fqZv4Fz7M6lN30Ck+gBn+1L/qc+z6fWBiCzUInvP2vO+/oFzx9nf86GXr/cPZGPoHzjff+1iOCI4d10cEKmWnhIpTEnHtXr+INfDx87J91U8x+xisGTRZQ6NZkTEJmATZNNTEzycCTFz6ifif8pJLz+dO5zQKYYZTZwzuJ/BfZ/rJ3dSo/aDxtzgPfMvlp3T8DMP43MVhj0so5lOH/l7Xuh3HPl7Xojvil0M/9J0A4tyzxemmtkFV2+aLPfqBR2L2US4GJbm7AYWS7pS0iXAGmDbBI/JzOy30qS/0oiIPkn/CXiBbMnt5ojYO8HDMjP7rTTpQwMgIp4DnpvocZiZ/ba7GKanzMxsknBomJlZ0xwaZmbWNIeGmZk1bdL/9tRwSeoB3hpFF5cD74zRcMbKZBwTeFzDMRnHBB7XcEzGMcHYjeszEdE21EmfuNAYLUmdzfxo14U0GccEHtdwTMYxgcc1HJNxTHDhx+XpKTMza5pDw8zMmubQ+LhNEz2AOibjmMDjGo7JOCbwuIZjMo4JLvC4fE/DzMya5isNMzNrmkPDzMya5tAwM7OmOTTMzKxpDg0zM2va/wdy8P6MZfkfjgAAAABJRU5ErkJggg==\n",
      "text/plain": [
       "<matplotlib.figure.Figure at 0x26dc8521710>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "theme[\"Loan Theme Type\"].value_counts().plot()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 75,
   "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>id</th>\n",
       "      <th>Loan Theme ID</th>\n",
       "      <th>Loan Theme Type</th>\n",
       "      <th>Partner ID</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>count</th>\n",
       "      <td>7.790920e+05</td>\n",
       "      <td>764279</td>\n",
       "      <td>764279</td>\n",
       "      <td>764279.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>unique</th>\n",
       "      <td>NaN</td>\n",
       "      <td>956</td>\n",
       "      <td>203</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>top</th>\n",
       "      <td>NaN</td>\n",
       "      <td>a1050000000wf0q</td>\n",
       "      <td>General</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>freq</th>\n",
       "      <td>NaN</td>\n",
       "      <td>110264</td>\n",
       "      <td>380693</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>mean</th>\n",
       "      <td>1.047475e+06</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>180.825840</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>std</th>\n",
       "      <td>2.282538e+05</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>97.914029</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>min</th>\n",
       "      <td>6.386310e+05</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>9.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25%</th>\n",
       "      <td>8.499768e+05</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>126.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>50%</th>\n",
       "      <td>1.046528e+06</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>145.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>75%</th>\n",
       "      <td>1.244768e+06</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>204.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>max</th>\n",
       "      <td>1.444243e+06</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>557.000000</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                  id    Loan Theme ID Loan Theme Type     Partner ID\n",
       "count   7.790920e+05           764279          764279  764279.000000\n",
       "unique           NaN              956             203            NaN\n",
       "top              NaN  a1050000000wf0q         General            NaN\n",
       "freq             NaN           110264          380693            NaN\n",
       "mean    1.047475e+06              NaN             NaN     180.825840\n",
       "std     2.282538e+05              NaN             NaN      97.914029\n",
       "min     6.386310e+05              NaN             NaN       9.000000\n",
       "25%     8.499768e+05              NaN             NaN     126.000000\n",
       "50%     1.046528e+06              NaN             NaN     145.000000\n",
       "75%     1.244768e+06              NaN             NaN     204.000000\n",
       "max     1.444243e+06              NaN             NaN     557.000000"
      ]
     },
     "execution_count": 75,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "theme.describe(include = 'all')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 76,
   "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>Partner ID</th>\n",
       "      <th>Field Partner Name</th>\n",
       "      <th>sector</th>\n",
       "      <th>Loan Theme ID</th>\n",
       "      <th>Loan Theme Type</th>\n",
       "      <th>country</th>\n",
       "      <th>forkiva</th>\n",
       "      <th>region</th>\n",
       "      <th>geocode_old</th>\n",
       "      <th>ISO</th>\n",
       "      <th>number</th>\n",
       "      <th>amount</th>\n",
       "      <th>LocationName</th>\n",
       "      <th>geocode</th>\n",
       "      <th>names</th>\n",
       "      <th>geo</th>\n",
       "      <th>lat</th>\n",
       "      <th>lon</th>\n",
       "      <th>mpi_region</th>\n",
       "      <th>mpi_geo</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>171</td>\n",
       "      <td>Bai Tushum Bank</td>\n",
       "      <td>General Financial Inclusion</td>\n",
       "      <td>a1050000002YqaF</td>\n",
       "      <td>Green</td>\n",
       "      <td>Kyrgyzstan</td>\n",
       "      <td>No</td>\n",
       "      <td>\"The first May\" village</td>\n",
       "      <td>NaN</td>\n",
       "      <td>KGZ</td>\n",
       "      <td>2</td>\n",
       "      <td>1700</td>\n",
       "      <td>\"The first May\" village, Kyrgyzstan</td>\n",
       "      <td>[(42.7338599, 78.3689411)]</td>\n",
       "      <td>1 May; Tyup; Tyup; Issyk-Kul Region; Kyrgyzstan</td>\n",
       "      <td>(42.7338599, 78.3689411)</td>\n",
       "      <td>42.73386</td>\n",
       "      <td>78.368941</td>\n",
       "      <td>KGZ</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>171</td>\n",
       "      <td>Bai Tushum Bank</td>\n",
       "      <td>General Financial Inclusion</td>\n",
       "      <td>a1050000007QIg8</td>\n",
       "      <td>Agriculture</td>\n",
       "      <td>Kyrgyzstan</td>\n",
       "      <td>No</td>\n",
       "      <td>\"The first May\" village</td>\n",
       "      <td>NaN</td>\n",
       "      <td>KGZ</td>\n",
       "      <td>1</td>\n",
       "      <td>300</td>\n",
       "      <td>\"The first May\" village, Kyrgyzstan</td>\n",
       "      <td>[(42.7338599, 78.3689411)]</td>\n",
       "      <td>1 May; Tyup; Tyup; Issyk-Kul Region; Kyrgyzstan</td>\n",
       "      <td>(42.7338599, 78.3689411)</td>\n",
       "      <td>42.73386</td>\n",
       "      <td>78.368941</td>\n",
       "      <td>KGZ</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   Partner ID Field Partner Name                       sector  \\\n",
       "0         171    Bai Tushum Bank  General Financial Inclusion   \n",
       "1         171    Bai Tushum Bank  General Financial Inclusion   \n",
       "\n",
       "     Loan Theme ID Loan Theme Type     country forkiva  \\\n",
       "0  a1050000002YqaF           Green  Kyrgyzstan      No   \n",
       "1  a1050000007QIg8     Agriculture  Kyrgyzstan      No   \n",
       "\n",
       "                    region geocode_old  ISO  number  amount  \\\n",
       "0  \"The first May\" village         NaN  KGZ       2    1700   \n",
       "1  \"The first May\" village         NaN  KGZ       1     300   \n",
       "\n",
       "                          LocationName                     geocode  \\\n",
       "0  \"The first May\" village, Kyrgyzstan  [(42.7338599, 78.3689411)]   \n",
       "1  \"The first May\" village, Kyrgyzstan  [(42.7338599, 78.3689411)]   \n",
       "\n",
       "                                             names                       geo  \\\n",
       "0  1 May; Tyup; Tyup; Issyk-Kul Region; Kyrgyzstan  (42.7338599, 78.3689411)   \n",
       "1  1 May; Tyup; Tyup; Issyk-Kul Region; Kyrgyzstan  (42.7338599, 78.3689411)   \n",
       "\n",
       "        lat        lon mpi_region mpi_geo  \n",
       "0  42.73386  78.368941        KGZ     NaN  \n",
       "1  42.73386  78.368941        KGZ     NaN  "
      ]
     },
     "execution_count": 76,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "theme_reg.head(2)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 77,
   "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>Partner ID</th>\n",
       "      <th>Field Partner Name</th>\n",
       "      <th>sector</th>\n",
       "      <th>Loan Theme ID</th>\n",
       "      <th>Loan Theme Type</th>\n",
       "      <th>country</th>\n",
       "      <th>forkiva</th>\n",
       "      <th>region</th>\n",
       "      <th>geocode_old</th>\n",
       "      <th>ISO</th>\n",
       "      <th>number</th>\n",
       "      <th>amount</th>\n",
       "      <th>LocationName</th>\n",
       "      <th>geocode</th>\n",
       "      <th>names</th>\n",
       "      <th>geo</th>\n",
       "      <th>lat</th>\n",
       "      <th>lon</th>\n",
       "      <th>mpi_region</th>\n",
       "      <th>mpi_geo</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>count</th>\n",
       "      <td>15736.000000</td>\n",
       "      <td>15736</td>\n",
       "      <td>15736</td>\n",
       "      <td>15736</td>\n",
       "      <td>15736</td>\n",
       "      <td>15736</td>\n",
       "      <td>15736</td>\n",
       "      <td>15736</td>\n",
       "      <td>1200</td>\n",
       "      <td>15722</td>\n",
       "      <td>15736.000000</td>\n",
       "      <td>1.573600e+04</td>\n",
       "      <td>15736</td>\n",
       "      <td>13662</td>\n",
       "      <td>13661</td>\n",
       "      <td>15736</td>\n",
       "      <td>13662.000000</td>\n",
       "      <td>13662.000000</td>\n",
       "      <td>15722</td>\n",
       "      <td>9671</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>unique</th>\n",
       "      <td>NaN</td>\n",
       "      <td>302</td>\n",
       "      <td>11</td>\n",
       "      <td>718</td>\n",
       "      <td>170</td>\n",
       "      <td>79</td>\n",
       "      <td>2</td>\n",
       "      <td>9526</td>\n",
       "      <td>340</td>\n",
       "      <td>77</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>9561</td>\n",
       "      <td>6557</td>\n",
       "      <td>6275</td>\n",
       "      <td>6558</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>392</td>\n",
       "      <td>335</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>top</th>\n",
       "      <td>NaN</td>\n",
       "      <td>Alalay sa Kaunlaran (ASKI)</td>\n",
       "      <td>General Financial Inclusion</td>\n",
       "      <td>a1050000000wf0V</td>\n",
       "      <td>General</td>\n",
       "      <td>Philippines</td>\n",
       "      <td>No</td>\n",
       "      <td>Chouf</td>\n",
       "      <td>(-1.2833333, 36.8166667)</td>\n",
       "      <td>PHL</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Chouf, Lebanon</td>\n",
       "      <td>[(19.7126764, 105.8393447)]</td>\n",
       "      <td>Philippines</td>\n",
       "      <td>(1000.0, 1000.0)</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Northern Mindanao, Philippines</td>\n",
       "      <td>(8.020163499999999, 124.6856509)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>freq</th>\n",
       "      <td>NaN</td>\n",
       "      <td>1207</td>\n",
       "      <td>13679</td>\n",
       "      <td>1152</td>\n",
       "      <td>5661</td>\n",
       "      <td>3467</td>\n",
       "      <td>13211</td>\n",
       "      <td>25</td>\n",
       "      <td>20</td>\n",
       "      <td>3467</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>25</td>\n",
       "      <td>43</td>\n",
       "      <td>137</td>\n",
       "      <td>2074</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1174</td>\n",
       "      <td>1174</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>mean</th>\n",
       "      <td>191.376144</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>NaN</td>\n",
       "      <td>53.628432</td>\n",
       "      <td>2.003991e+04</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>14.328878</td>\n",
       "      <td>29.433569</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>std</th>\n",
       "      <td>118.705003</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>NaN</td>\n",
       "      <td>403.079799</td>\n",
       "      <td>9.636941e+04</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>16.757689</td>\n",
       "      <td>83.255739</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>min</th>\n",
       "      <td>9.000000</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>NaN</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>2.500000e+01</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>-34.610548</td>\n",
       "      <td>-172.790661</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25%</th>\n",
       "      <td>123.000000</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>NaN</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>9.500000e+02</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>6.266728</td>\n",
       "      <td>-71.967463</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>50%</th>\n",
       "      <td>154.000000</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>NaN</td>\n",
       "      <td>4.000000</td>\n",
       "      <td>2.600000e+03</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>13.484101</td>\n",
       "      <td>41.899993</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>75%</th>\n",
       "      <td>217.000000</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>NaN</td>\n",
       "      <td>15.000000</td>\n",
       "      <td>8.750000e+03</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>22.189940</td>\n",
       "      <td>106.677321</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>max</th>\n",
       "      <td>545.000000</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>NaN</td>\n",
       "      <td>22538.000000</td>\n",
       "      <td>4.929900e+06</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>51.314017</td>\n",
       "      <td>159.972900</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "          Partner ID          Field Partner Name                       sector  \\\n",
       "count   15736.000000                       15736                        15736   \n",
       "unique           NaN                         302                           11   \n",
       "top              NaN  Alalay sa Kaunlaran (ASKI)  General Financial Inclusion   \n",
       "freq             NaN                        1207                        13679   \n",
       "mean      191.376144                         NaN                          NaN   \n",
       "std       118.705003                         NaN                          NaN   \n",
       "min         9.000000                         NaN                          NaN   \n",
       "25%       123.000000                         NaN                          NaN   \n",
       "50%       154.000000                         NaN                          NaN   \n",
       "75%       217.000000                         NaN                          NaN   \n",
       "max       545.000000                         NaN                          NaN   \n",
       "\n",
       "          Loan Theme ID Loan Theme Type      country forkiva region  \\\n",
       "count             15736           15736        15736   15736  15736   \n",
       "unique              718             170           79       2   9526   \n",
       "top     a1050000000wf0V         General  Philippines      No  Chouf   \n",
       "freq               1152            5661         3467   13211     25   \n",
       "mean                NaN             NaN          NaN     NaN    NaN   \n",
       "std                 NaN             NaN          NaN     NaN    NaN   \n",
       "min                 NaN             NaN          NaN     NaN    NaN   \n",
       "25%                 NaN             NaN          NaN     NaN    NaN   \n",
       "50%                 NaN             NaN          NaN     NaN    NaN   \n",
       "75%                 NaN             NaN          NaN     NaN    NaN   \n",
       "max                 NaN             NaN          NaN     NaN    NaN   \n",
       "\n",
       "                     geocode_old    ISO        number        amount  \\\n",
       "count                       1200  15722  15736.000000  1.573600e+04   \n",
       "unique                       340     77           NaN           NaN   \n",
       "top     (-1.2833333, 36.8166667)    PHL           NaN           NaN   \n",
       "freq                          20   3467           NaN           NaN   \n",
       "mean                         NaN    NaN     53.628432  2.003991e+04   \n",
       "std                          NaN    NaN    403.079799  9.636941e+04   \n",
       "min                          NaN    NaN      1.000000  2.500000e+01   \n",
       "25%                          NaN    NaN      1.000000  9.500000e+02   \n",
       "50%                          NaN    NaN      4.000000  2.600000e+03   \n",
       "75%                          NaN    NaN     15.000000  8.750000e+03   \n",
       "max                          NaN    NaN  22538.000000  4.929900e+06   \n",
       "\n",
       "          LocationName                      geocode        names  \\\n",
       "count            15736                        13662        13661   \n",
       "unique            9561                         6557         6275   \n",
       "top     Chouf, Lebanon  [(19.7126764, 105.8393447)]  Philippines   \n",
       "freq                25                           43          137   \n",
       "mean               NaN                          NaN          NaN   \n",
       "std                NaN                          NaN          NaN   \n",
       "min                NaN                          NaN          NaN   \n",
       "25%                NaN                          NaN          NaN   \n",
       "50%                NaN                          NaN          NaN   \n",
       "75%                NaN                          NaN          NaN   \n",
       "max                NaN                          NaN          NaN   \n",
       "\n",
       "                     geo           lat           lon  \\\n",
       "count              15736  13662.000000  13662.000000   \n",
       "unique              6558           NaN           NaN   \n",
       "top     (1000.0, 1000.0)           NaN           NaN   \n",
       "freq                2074           NaN           NaN   \n",
       "mean                 NaN     14.328878     29.433569   \n",
       "std                  NaN     16.757689     83.255739   \n",
       "min                  NaN    -34.610548   -172.790661   \n",
       "25%                  NaN      6.266728    -71.967463   \n",
       "50%                  NaN     13.484101     41.899993   \n",
       "75%                  NaN     22.189940    106.677321   \n",
       "max                  NaN     51.314017    159.972900   \n",
       "\n",
       "                            mpi_region                           mpi_geo  \n",
       "count                            15722                              9671  \n",
       "unique                             392                               335  \n",
       "top     Northern Mindanao, Philippines  (8.020163499999999, 124.6856509)  \n",
       "freq                              1174                              1174  \n",
       "mean                               NaN                               NaN  \n",
       "std                                NaN                               NaN  \n",
       "min                                NaN                               NaN  \n",
       "25%                                NaN                               NaN  \n",
       "50%                                NaN                               NaN  \n",
       "75%                                NaN                               NaN  \n",
       "max                                NaN                               NaN  "
      ]
     },
     "execution_count": 77,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "theme_reg.describe(include = 'all')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 78,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Source: https://build.kiva.org/\n",
    "loans_2 = pd.read_csv(\"input_data/loans.csv\", usecols= ['id', 'original_language', 'status', 'planned_expiration_time']) #, nrows = 1000000)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 79,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index(['id', 'original_language', 'status', 'planned_expiration_time'], dtype='object')"
      ]
     },
     "execution_count": 79,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "loans_2.columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 80,
   "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>id</th>\n",
       "      <th>original_language</th>\n",
       "      <th>status</th>\n",
       "      <th>planned_expiration_time</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>466993</td>\n",
       "      <td>Spanish</td>\n",
       "      <td>funded</td>\n",
       "      <td>2012-10-10 17:20:08+00:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>468275</td>\n",
       "      <td>Spanish</td>\n",
       "      <td>funded</td>\n",
       "      <td>2012-10-13 01:10:02+00:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>468837</td>\n",
       "      <td>English</td>\n",
       "      <td>funded</td>\n",
       "      <td>2012-10-09 17:10:15+00:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>468882</td>\n",
       "      <td>Spanish</td>\n",
       "      <td>expired</td>\n",
       "      <td>2012-10-14 10:50:02+00:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>469762</td>\n",
       "      <td>English</td>\n",
       "      <td>funded</td>\n",
       "      <td>2012-10-11 02:10:10+00:00</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "       id original_language   status    planned_expiration_time\n",
       "0  466993           Spanish   funded  2012-10-10 17:20:08+00:00\n",
       "1  468275           Spanish   funded  2012-10-13 01:10:02+00:00\n",
       "2  468837           English   funded  2012-10-09 17:10:15+00:00\n",
       "3  468882           Spanish  expired  2012-10-14 10:50:02+00:00\n",
       "4  469762           English   funded  2012-10-11 02:10:10+00:00"
      ]
     },
     "execution_count": 80,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "loans_2.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 81,
   "metadata": {},
   "outputs": [],
   "source": [
    "merged = pd.merge(loans, loans_2, how = 'inner')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 82,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(671205, 25)"
      ]
     },
     "execution_count": 82,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "merged.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 83,
   "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>id</th>\n",
       "      <th>funded_amount</th>\n",
       "      <th>loan_amount</th>\n",
       "      <th>activity</th>\n",
       "      <th>sector</th>\n",
       "      <th>use</th>\n",
       "      <th>country_code</th>\n",
       "      <th>country</th>\n",
       "      <th>region</th>\n",
       "      <th>currency</th>\n",
       "      <th>partner_id</th>\n",
       "      <th>posted_time</th>\n",
       "      <th>disbursed_time</th>\n",
       "      <th>funded_time</th>\n",
       "      <th>term_in_months</th>\n",
       "      <th>lender_count</th>\n",
       "      <th>tags</th>\n",
       "      <th>borrower_genders</th>\n",
       "      <th>repayment_interval</th>\n",
       "      <th>date</th>\n",
       "      <th>ISO_3</th>\n",
       "      <th>MPI</th>\n",
       "      <th>original_language</th>\n",
       "      <th>status</th>\n",
       "      <th>planned_expiration_time</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>653051</td>\n",
       "      <td>300.0</td>\n",
       "      <td>300.0</td>\n",
       "      <td>Fruits &amp; Vegetables</td>\n",
       "      <td>Food</td>\n",
       "      <td>To buy seasonal, fresh fruits to sell.</td>\n",
       "      <td>PK</td>\n",
       "      <td>Pakistan</td>\n",
       "      <td>lahore</td>\n",
       "      <td>PKR</td>\n",
       "      <td>247.0</td>\n",
       "      <td>2014-01-01 06:12:39+00:00</td>\n",
       "      <td>2013-12-17 08:00:00+00:00</td>\n",
       "      <td>2014-01-02 10:06:32+00:00</td>\n",
       "      <td>12.0</td>\n",
       "      <td>12</td>\n",
       "      <td>NaN</td>\n",
       "      <td>female</td>\n",
       "      <td>irregular</td>\n",
       "      <td>2014-01-01</td>\n",
       "      <td>PAK</td>\n",
       "      <td>0.23032</td>\n",
       "      <td>English</td>\n",
       "      <td>funded</td>\n",
       "      <td>2014-03-02 07:10:02+00:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>653053</td>\n",
       "      <td>575.0</td>\n",
       "      <td>575.0</td>\n",
       "      <td>Rickshaw</td>\n",
       "      <td>Transportation</td>\n",
       "      <td>to repair and maintain the auto rickshaw used ...</td>\n",
       "      <td>PK</td>\n",
       "      <td>Pakistan</td>\n",
       "      <td>lahore</td>\n",
       "      <td>PKR</td>\n",
       "      <td>247.0</td>\n",
       "      <td>2014-01-01 06:51:08+00:00</td>\n",
       "      <td>2013-12-17 08:00:00+00:00</td>\n",
       "      <td>2014-01-02 09:17:23+00:00</td>\n",
       "      <td>11.0</td>\n",
       "      <td>14</td>\n",
       "      <td>NaN</td>\n",
       "      <td>female, female</td>\n",
       "      <td>irregular</td>\n",
       "      <td>2014-01-01</td>\n",
       "      <td>PAK</td>\n",
       "      <td>0.23032</td>\n",
       "      <td>English</td>\n",
       "      <td>funded</td>\n",
       "      <td>2014-01-31 10:10:02+00:00</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "       id  funded_amount  loan_amount             activity          sector  \\\n",
       "0  653051          300.0        300.0  Fruits & Vegetables            Food   \n",
       "1  653053          575.0        575.0             Rickshaw  Transportation   \n",
       "\n",
       "                                                 use country_code   country  \\\n",
       "0            To buy seasonal, fresh fruits to sell.            PK  Pakistan   \n",
       "1  to repair and maintain the auto rickshaw used ...           PK  Pakistan   \n",
       "\n",
       "   region currency  partner_id                posted_time  \\\n",
       "0  lahore      PKR       247.0  2014-01-01 06:12:39+00:00   \n",
       "1  lahore      PKR       247.0  2014-01-01 06:51:08+00:00   \n",
       "\n",
       "              disbursed_time                funded_time  term_in_months  \\\n",
       "0  2013-12-17 08:00:00+00:00  2014-01-02 10:06:32+00:00            12.0   \n",
       "1  2013-12-17 08:00:00+00:00  2014-01-02 09:17:23+00:00            11.0   \n",
       "\n",
       "   lender_count tags borrower_genders repayment_interval        date ISO_3  \\\n",
       "0            12  NaN           female          irregular  2014-01-01   PAK   \n",
       "1            14  NaN   female, female          irregular  2014-01-01   PAK   \n",
       "\n",
       "       MPI original_language  status    planned_expiration_time  \n",
       "0  0.23032           English  funded  2014-03-02 07:10:02+00:00  \n",
       "1  0.23032           English  funded  2014-01-31 10:10:02+00:00  "
      ]
     },
     "execution_count": 83,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "merged.head(2)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 84,
   "metadata": {},
   "outputs": [],
   "source": [
    "merged3 = pd.merge(merged, theme, how = 'inner', left_on=['id'], right_on=['id'] )"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 85,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(671199, 28)"
      ]
     },
     "execution_count": 85,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "merged3.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 86,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Partner ID  Loan Theme ID    region                  \n",
       "9           a1050000000slfi  Banteay Meanchey            1\n",
       "                             Battambang Province         1\n",
       "                             Kampong Cham Province       1\n",
       "                             Kampong Chhnang Province    1\n",
       "                             Kampong Speu Province       1\n",
       "dtype: int64"
      ]
     },
     "execution_count": 86,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#Without being able to match region appropriately in the main data set, this can only be collapsed\n",
    "theme_reg.groupby(['Partner ID','Loan Theme ID','region']).apply(lambda s : len(s)).head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 87,
   "metadata": {},
   "outputs": [],
   "source": [
    "grps = theme_reg[['Partner ID', 'Loan Theme ID', 'number', 'amount', 'lat', 'lon']].groupby(['Partner ID','Loan Theme ID'])\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 88,
   "metadata": {},
   "outputs": [],
   "source": [
    "theme_reg_agg = grps.agg({'number': 'sum','amount':'sum','lat':'mean','lon':'mean'}).reset_index()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 89,
   "metadata": {},
   "outputs": [],
   "source": [
    "merged4 = pd.merge(merged3, theme_reg_agg, how = 'left', left_on=['partner_id','Loan Theme ID'], right_on=['Partner ID','Loan Theme ID'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 90,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(671199, 33)"
      ]
     },
     "execution_count": 90,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "merged4.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 91,
   "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>id</th>\n",
       "      <th>funded_amount</th>\n",
       "      <th>loan_amount</th>\n",
       "      <th>activity</th>\n",
       "      <th>sector</th>\n",
       "      <th>use</th>\n",
       "      <th>country_code</th>\n",
       "      <th>country</th>\n",
       "      <th>region</th>\n",
       "      <th>currency</th>\n",
       "      <th>partner_id</th>\n",
       "      <th>posted_time</th>\n",
       "      <th>disbursed_time</th>\n",
       "      <th>funded_time</th>\n",
       "      <th>term_in_months</th>\n",
       "      <th>lender_count</th>\n",
       "      <th>tags</th>\n",
       "      <th>borrower_genders</th>\n",
       "      <th>repayment_interval</th>\n",
       "      <th>date</th>\n",
       "      <th>ISO_3</th>\n",
       "      <th>MPI</th>\n",
       "      <th>original_language</th>\n",
       "      <th>status</th>\n",
       "      <th>planned_expiration_time</th>\n",
       "      <th>Loan Theme ID</th>\n",
       "      <th>Loan Theme Type</th>\n",
       "      <th>Partner ID_x</th>\n",
       "      <th>Partner ID_y</th>\n",
       "      <th>number</th>\n",
       "      <th>amount</th>\n",
       "      <th>lat</th>\n",
       "      <th>lon</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>653053</td>\n",
       "      <td>575.0</td>\n",
       "      <td>575.0</td>\n",
       "      <td>Rickshaw</td>\n",
       "      <td>Transportation</td>\n",
       "      <td>to repair and maintain the auto rickshaw used ...</td>\n",
       "      <td>PK</td>\n",
       "      <td>Pakistan</td>\n",
       "      <td>lahore</td>\n",
       "      <td>PKR</td>\n",
       "      <td>247</td>\n",
       "      <td>2014-01-01 06:51:08+00:00</td>\n",
       "      <td>2013-12-17 08:00:00+00:00</td>\n",
       "      <td>2014-01-02 09:17:23+00:00</td>\n",
       "      <td>11.0</td>\n",
       "      <td>14</td>\n",
       "      <td>NaN</td>\n",
       "      <td>female, female</td>\n",
       "      <td>irregular</td>\n",
       "      <td>2014-01-01</td>\n",
       "      <td>PAK</td>\n",
       "      <td>0.23032</td>\n",
       "      <td>English</td>\n",
       "      <td>funded</td>\n",
       "      <td>2014-01-31 10:10:02+00:00</td>\n",
       "      <td>a1050000000sjEC</td>\n",
       "      <td>Underserved</td>\n",
       "      <td>247.0</td>\n",
       "      <td>247.0</td>\n",
       "      <td>9569.0</td>\n",
       "      <td>4171675.0</td>\n",
       "      <td>30.0854</td>\n",
       "      <td>71.581756</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "       id  funded_amount  loan_amount  activity          sector  \\\n",
       "0  653053          575.0        575.0  Rickshaw  Transportation   \n",
       "\n",
       "                                                 use country_code   country  \\\n",
       "0  to repair and maintain the auto rickshaw used ...           PK  Pakistan   \n",
       "\n",
       "   region currency partner_id                posted_time  \\\n",
       "0  lahore      PKR        247  2014-01-01 06:51:08+00:00   \n",
       "\n",
       "              disbursed_time                funded_time  term_in_months  \\\n",
       "0  2013-12-17 08:00:00+00:00  2014-01-02 09:17:23+00:00            11.0   \n",
       "\n",
       "   lender_count tags borrower_genders repayment_interval        date ISO_3  \\\n",
       "0            14  NaN   female, female          irregular  2014-01-01   PAK   \n",
       "\n",
       "       MPI original_language  status    planned_expiration_time  \\\n",
       "0  0.23032           English  funded  2014-01-31 10:10:02+00:00   \n",
       "\n",
       "     Loan Theme ID Loan Theme Type  Partner ID_x  Partner ID_y  number  \\\n",
       "0  a1050000000sjEC     Underserved         247.0         247.0  9569.0   \n",
       "\n",
       "      amount      lat        lon  \n",
       "0  4171675.0  30.0854  71.581756  "
      ]
     },
     "execution_count": 91,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "merged4.head(1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 92,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index(['id', 'funded_amount', 'loan_amount', 'activity', 'sector', 'use',\n",
       "       'country_code', 'country', 'region', 'currency', 'partner_id',\n",
       "       'posted_time', 'disbursed_time', 'funded_time', 'term_in_months',\n",
       "       'lender_count', 'tags', 'borrower_genders', 'repayment_interval',\n",
       "       'date', 'ISO_3', 'MPI', 'original_language', 'status',\n",
       "       'planned_expiration_time', 'Loan Theme ID', 'Loan Theme Type',\n",
       "       'Partner ID_x', 'Partner ID_y', 'number', 'amount', 'lat', 'lon'],\n",
       "      dtype='object')"
      ]
     },
     "execution_count": 92,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "merged4.columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 93,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "funded         621964\n",
       "expired         42310\n",
       "fundRaising      5343\n",
       "refunded         1582\n",
       "Name: status, dtype: int64"
      ]
     },
     "execution_count": 93,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "merged4['status'].value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 342,
   "metadata": {},
   "outputs": [],
   "source": [
    "#merged_trim = merged4[['loan_amount', 'activity', 'sector', 'country', 'currency', 'status', 'Partner ID', 'lat', 'lon']]\n",
    "#merged_trim.apply(lambda s : s.isnull().sum())\n",
    "#merged_trim.to_csv(\"output_data/merged_data_trim.csv\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 94,
   "metadata": {},
   "outputs": [],
   "source": [
    "merged4 = merged4.drop(['funded_time','tags'], axis =1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 95,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "id                             0\n",
       "funded_amount                  0\n",
       "loan_amount                    0\n",
       "activity                       0\n",
       "sector                         0\n",
       "use                         4231\n",
       "country_code                   8\n",
       "country                        0\n",
       "region                         0\n",
       "currency                       0\n",
       "partner_id                 13507\n",
       "posted_time                    0\n",
       "disbursed_time              2396\n",
       "term_in_months                 0\n",
       "lender_count                   0\n",
       "borrower_genders            4220\n",
       "repayment_interval             0\n",
       "date                           0\n",
       "ISO_3                       1419\n",
       "MPI                            0\n",
       "original_language           4220\n",
       "status                         0\n",
       "planned_expiration_time        0\n",
       "Loan Theme ID              13507\n",
       "Loan Theme Type            13507\n",
       "Partner ID_x               13507\n",
       "Partner ID_y               42232\n",
       "number                     42232\n",
       "amount                     42232\n",
       "lat                        42533\n",
       "lon                        42533\n",
       "dtype: int64"
      ]
     },
     "execution_count": 95,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "merged4.apply(lambda s : s.isna().sum())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 96,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "id                            0\n",
       "funded_amount                 0\n",
       "loan_amount                   0\n",
       "activity                      0\n",
       "sector                        0\n",
       "use                          10\n",
       "country_code                  0\n",
       "country                       0\n",
       "region                        0\n",
       "currency                      0\n",
       "partner_id                 1879\n",
       "posted_time                   0\n",
       "disbursed_time             1879\n",
       "term_in_months                0\n",
       "lender_count                  0\n",
       "borrower_genders              4\n",
       "repayment_interval            0\n",
       "date                          0\n",
       "ISO_3                       197\n",
       "MPI                           0\n",
       "original_language             4\n",
       "status                        0\n",
       "planned_expiration_time       0\n",
       "Loan Theme ID              1879\n",
       "Loan Theme Type            1879\n",
       "Partner ID_x               1879\n",
       "Partner ID_y               3231\n",
       "number                     3231\n",
       "amount                     3231\n",
       "lat                        3231\n",
       "lon                        3231\n",
       "dtype: int64"
      ]
     },
     "execution_count": 96,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "merged4.loc[merged4['status']=='expired'].apply(lambda s : s.isna().sum())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 348,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(671199, 31)"
      ]
     },
     "execution_count": 348,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "merged4.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 97,
   "metadata": {},
   "outputs": [],
   "source": [
    "merged4 = merged4.dropna()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 351,
   "metadata": {},
   "outputs": [],
   "source": [
    "#column_nulls = merged4.apply(lambda s : s.isnull().sum())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 352,
   "metadata": {},
   "outputs": [],
   "source": [
    "#a = list(column_nulls.loc[column_nulls< 117628].index.values)\n",
    "#a.remove('funded_time')\n",
    "#a\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 353,
   "metadata": {},
   "outputs": [],
   "source": [
    "\n",
    "#ml_data = merged4['id', 'funded_amount', 'loan_amount', 'activity', 'sector', 'use', \n",
    "#                  'country_code', 'country','region', 'currency', 'partner_id', '\n",
    "                  \n",
    "#ml_data = merged4[a]\n",
    "#ml_data.head(1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 98,
   "metadata": {},
   "outputs": [],
   "source": [
    "ml_data = merged4[merged4['status']!='fundRaising']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 99,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(618813, 31)"
      ]
     },
     "execution_count": 99,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "ml_data.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 358,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index(['id', 'funded_amount', 'loan_amount', 'activity', 'sector', 'use',\n",
       "       'country_code', 'country', 'region', 'currency', 'partner_id',\n",
       "       'posted_time', 'disbursed_time', 'term_in_months', 'lender_count',\n",
       "       'borrower_genders', 'repayment_interval', 'date', 'ISO', 'MPI',\n",
       "       'original_language', 'status', 'planned_expiration_time',\n",
       "       'Loan Theme ID', 'Loan Theme Type', 'Partner ID_x', 'Partner ID_y',\n",
       "       'number', 'amount', 'lat', 'lon'],\n",
       "      dtype='object')"
      ]
     },
     "execution_count": 358,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "ml_data.columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 100,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "2014-03-27    2436\n",
       "2014-03-26    2306\n",
       "2017-07-20    1430\n",
       "2015-02-05    1148\n",
       "2014-12-03    1129\n",
       "Name: planned_expiration_time, dtype: int64"
      ]
     },
     "execution_count": 100,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "import datetime\n",
    "ml_data['planned_expiration_time'].apply(lambda v : v.split(\" \")[0]).value_counts().head()\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 101,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "22"
      ]
     },
     "execution_count": 101,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "t = \"2014-03-27\"\n",
    "t2 = \"2014-03-05\"\n",
    "d = datetime.date(*(int(s) for s in t.split('-')))\n",
    "d2 = datetime.date(*(int(s) for s in t2.split('-')))\n",
    "(d - d2).days"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 102,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "C:\\Users\\dmcph\\Anaconda3\\lib\\site-packages\\ipykernel_launcher.py:2: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame.\n",
      "Try using .loc[row_indexer,col_indexer] = value instead\n",
      "\n",
      "See the caveats in the documentation: http://pandas.pydata.org/pandas-docs/stable/indexing.html#indexing-view-versus-copy\n",
      "  \n"
     ]
    }
   ],
   "source": [
    "ml_data['days_to_expiration'] = (ml_data['planned_expiration_time'].apply(lambda v : datetime.date(*(int(s) for s in (v.split(\" \")[0]).split('-')))) - \n",
    " ml_data['date'].apply(lambda v : datetime.date(*(int(s) for s in v.split(\"-\")))))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 103,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "C:\\Users\\dmcph\\Anaconda3\\lib\\site-packages\\ipykernel_launcher.py:1: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame.\n",
      "Try using .loc[row_indexer,col_indexer] = value instead\n",
      "\n",
      "See the caveats in the documentation: http://pandas.pydata.org/pandas-docs/stable/indexing.html#indexing-view-versus-copy\n",
      "  \"\"\"Entry point for launching an IPython kernel.\n"
     ]
    }
   ],
   "source": [
    "ml_data['days_to_expiration'] = ml_data['days_to_expiration'].apply(lambda v : v.days)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 104,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "C:\\Users\\dmcph\\Anaconda3\\lib\\site-packages\\ipykernel_launcher.py:1: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame.\n",
      "Try using .loc[row_indexer,col_indexer] = value instead\n",
      "\n",
      "See the caveats in the documentation: http://pandas.pydata.org/pandas-docs/stable/indexing.html#indexing-view-versus-copy\n",
      "  \"\"\"Entry point for launching an IPython kernel.\n",
      "C:\\Users\\dmcph\\Anaconda3\\lib\\site-packages\\ipykernel_launcher.py:2: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame.\n",
      "Try using .loc[row_indexer,col_indexer] = value instead\n",
      "\n",
      "See the caveats in the documentation: http://pandas.pydata.org/pandas-docs/stable/indexing.html#indexing-view-versus-copy\n",
      "  \n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "0    2014\n",
       "1    2014\n",
       "2    2014\n",
       "3    2014\n",
       "5    2014\n",
       "Name: year, dtype: object"
      ]
     },
     "execution_count": 104,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "ml_data['year'] = ml_data['date'].apply(lambda v : v.split(\"-\")[0])\n",
    "ml_data['month'] = ml_data['date'].apply(lambda v : v.split(\"-\")[1])\n",
    "ml_data['year'].head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 105,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index(['id', 'funded_amount', 'loan_amount', 'activity', 'sector', 'use',\n",
       "       'country_code', 'country', 'region', 'currency', 'partner_id',\n",
       "       'posted_time', 'disbursed_time', 'term_in_months', 'lender_count',\n",
       "       'borrower_genders', 'repayment_interval', 'date', 'ISO_3', 'MPI',\n",
       "       'original_language', 'status', 'planned_expiration_time',\n",
       "       'Loan Theme ID', 'Loan Theme Type', 'Partner ID_x', 'Partner ID_y',\n",
       "       'number', 'amount', 'lat', 'lon', 'days_to_expiration', 'year',\n",
       "       'month'],\n",
       "      dtype='object')"
      ]
     },
     "execution_count": 105,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "ml_data.columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 106,
   "metadata": {},
   "outputs": [],
   "source": [
    "ml_data_trim = ml_data[['status', 'loan_amount', 'activity', 'sector','country', 'currency', 'partner_id', 'number', 'amount','date',\n",
    "                  'term_in_months', 'borrower_genders', 'repayment_interval', 'original_language', 'days_to_expiration',\n",
    "                  'year', 'month', 'lat', 'lon','MPI']]\n",
    "                  "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 107,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "funded     579934\n",
       "expired     38879\n",
       "Name: status, dtype: int64"
      ]
     },
     "execution_count": 107,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "ml_data_trim['status'].value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 108,
   "metadata": {},
   "outputs": [],
   "source": [
    "ml_data_trim.to_csv(\"output_data/ML_Final_Data_v2.csv\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 109,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "funded     579934\n",
       "expired     38879\n",
       "Name: status, dtype: int64"
      ]
     },
     "execution_count": 109,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "status = ml_data_trim['status']\n",
    "ml_data_trim = ml_data_trim.drop('status', axis = 1)\n",
    "status.value_counts()\n"
   ]
  }
 ],
 "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.3"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}