FoCS_Notebook.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "from datetime import datetime\n",
    "import time\n",
    "\n",
    "starttime = time.time()\n",
    "\n",
    "#remove columns view max limit\n",
    "pd.set_option('display.max_columns', None)\n",
    "\n",
    "loan_lenders = pd.read_csv(\"loans_lenders.csv\")\n",
    "#parsing to datetime\n",
    "loan = pd.read_csv(\"loans.csv\",parse_dates=['planned_expiration_time','disburse_time'])\n",
    "lenders=pd.read_csv('lenders.csv')\n",
    "country_stats=pd.read_csv('country_stats.csv')\n",
    "\n",
    "importtime = time.time()\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<h3>Exercise 1<h3>\n",
    "\n",
    "Normalize the loan_lenders table. In the normalized table, each row must have one loan_id and one lender.\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "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>loan_id</th>\n",
       "      <th>lenders</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>483693</td>\n",
       "      <td>muc888, sam4326, camaran3922, lachheb1865, reb...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>483738</td>\n",
       "      <td>muc888, nora3555, williammanashi, barbara5610,...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>485000</td>\n",
       "      <td>muc888, terrystl, richardandsusan8352, sherri4...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>486087</td>\n",
       "      <td>muc888, james5068, rudi5955, daniel9859, don92...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>534428</td>\n",
       "      <td>muc888, niki3008, teresa9174, mike4896, david7...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1387427</th>\n",
       "      <td>678999</td>\n",
       "      <td>michael43411218, carol5987, gooddogg1, chris41...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1387428</th>\n",
       "      <td>1207353</td>\n",
       "      <td>rjhoward1986, jeffrey6870, trolltech4460, elys...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1387429</th>\n",
       "      <td>1206220</td>\n",
       "      <td>vicky7746, gooddogg1, fairspirit, craig9729960...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1387430</th>\n",
       "      <td>1206425</td>\n",
       "      <td>rich6705, sergiiy9766, angela7509, barbara5610...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1387431</th>\n",
       "      <td>1206486</td>\n",
       "      <td>alan5175, amy38101311</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>1387432 rows × 2 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "         loan_id                                            lenders\n",
       "0         483693  muc888, sam4326, camaran3922, lachheb1865, reb...\n",
       "1         483738  muc888, nora3555, williammanashi, barbara5610,...\n",
       "2         485000  muc888, terrystl, richardandsusan8352, sherri4...\n",
       "3         486087  muc888, james5068, rudi5955, daniel9859, don92...\n",
       "4         534428  muc888, niki3008, teresa9174, mike4896, david7...\n",
       "...          ...                                                ...\n",
       "1387427   678999  michael43411218, carol5987, gooddogg1, chris41...\n",
       "1387428  1207353  rjhoward1986, jeffrey6870, trolltech4460, elys...\n",
       "1387429  1206220  vicky7746, gooddogg1, fairspirit, craig9729960...\n",
       "1387430  1206425  rich6705, sergiiy9766, angela7509, barbara5610...\n",
       "1387431  1206486                              alan5175, amy38101311\n",
       "\n",
       "[1387432 rows x 2 columns]"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "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>loan_id</th>\n",
       "      <th>lender</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>483693</td>\n",
       "      <td>muc888</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>483693</td>\n",
       "      <td>sam4326</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>483693</td>\n",
       "      <td>camaran3922</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>483693</td>\n",
       "      <td>lachheb1865</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>483693</td>\n",
       "      <td>rebecca3499</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>28293926</th>\n",
       "      <td>1206425</td>\n",
       "      <td>trogdorfamily7622</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>28293927</th>\n",
       "      <td>1206425</td>\n",
       "      <td>danny6470</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>28293928</th>\n",
       "      <td>1206425</td>\n",
       "      <td>don6118</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>28293929</th>\n",
       "      <td>1206486</td>\n",
       "      <td>alan5175</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>28293930</th>\n",
       "      <td>1206486</td>\n",
       "      <td>amy38101311</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>28293931 rows × 2 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "          loan_id              lender\n",
       "0          483693              muc888\n",
       "1          483693             sam4326\n",
       "2          483693         camaran3922\n",
       "3          483693         lachheb1865\n",
       "4          483693         rebecca3499\n",
       "...           ...                 ...\n",
       "28293926  1206425   trogdorfamily7622\n",
       "28293927  1206425           danny6470\n",
       "28293928  1206425             don6118\n",
       "28293929  1206486            alan5175\n",
       "28293930  1206486         amy38101311\n",
       "\n",
       "[28293931 rows x 2 columns]"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "display(loan_lenders)\n",
    "\n",
    "loan_lenders_list = loan_lenders.values.tolist()\n",
    "loan_id=[]\n",
    "lenders_list=[]\n",
    "\n",
    "for row in loan_lenders_list:\n",
    "    names_splitted=row[1].split(',')\n",
    "    id_=row[0]\n",
    "    for name in names_splitted:\n",
    "        lenders_list.append(name)\n",
    "        loan_id.append(id_)\n",
    "    \n",
    "loan_lenders_normalized = pd.DataFrame({\"loan_id\": loan_id, \"lender\": lenders_list}, columns=[\"loan_id\", \"lender\"])\n",
    "display(loan_lenders_normalized)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<h3>Exercise 2<h3>\n",
    "For each loan, add a column duration corresponding to the number of days between the disburse time and the planned expiration time. \n",
    "If any of those two dates is missing, also the duration must be missing."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "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>loan_id</th>\n",
       "      <th>disburse_time</th>\n",
       "      <th>planned_expiration_time</th>\n",
       "      <th>duration</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>657307</td>\n",
       "      <td>2013-12-22 08:00:00+00:00</td>\n",
       "      <td>2014-02-14 03:30:06+00:00</td>\n",
       "      <td>53</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>657259</td>\n",
       "      <td>2013-12-20 08:00:00+00:00</td>\n",
       "      <td>2014-03-26 22:25:07+00:00</td>\n",
       "      <td>96</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>658010</td>\n",
       "      <td>2014-01-09 08:00:00+00:00</td>\n",
       "      <td>2014-02-15 21:10:05+00:00</td>\n",
       "      <td>37</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>659347</td>\n",
       "      <td>2014-01-17 08:00:00+00:00</td>\n",
       "      <td>2014-02-21 03:10:02+00:00</td>\n",
       "      <td>34</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>656933</td>\n",
       "      <td>2013-12-17 08:00:00+00:00</td>\n",
       "      <td>2014-02-13 06:10:02+00:00</td>\n",
       "      <td>57</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1419602</th>\n",
       "      <td>988180</td>\n",
       "      <td>2015-11-23 08:00:00+00:00</td>\n",
       "      <td>2016-01-02 01:00:03+00:00</td>\n",
       "      <td>39</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1419603</th>\n",
       "      <td>988213</td>\n",
       "      <td>2015-11-24 08:00:00+00:00</td>\n",
       "      <td>2016-01-02 16:40:07+00:00</td>\n",
       "      <td>39</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1419604</th>\n",
       "      <td>989109</td>\n",
       "      <td>2015-11-13 08:00:00+00:00</td>\n",
       "      <td>2016-01-03 22:20:04+00:00</td>\n",
       "      <td>51</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1419605</th>\n",
       "      <td>989143</td>\n",
       "      <td>2015-11-03 08:00:00+00:00</td>\n",
       "      <td>2016-01-05 08:50:02+00:00</td>\n",
       "      <td>63</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1419606</th>\n",
       "      <td>989240</td>\n",
       "      <td>2015-11-03 08:00:00+00:00</td>\n",
       "      <td>2016-01-03 20:50:06+00:00</td>\n",
       "      <td>61</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>1044962 rows × 4 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "         loan_id             disburse_time   planned_expiration_time  duration\n",
       "0         657307 2013-12-22 08:00:00+00:00 2014-02-14 03:30:06+00:00        53\n",
       "1         657259 2013-12-20 08:00:00+00:00 2014-03-26 22:25:07+00:00        96\n",
       "2         658010 2014-01-09 08:00:00+00:00 2014-02-15 21:10:05+00:00        37\n",
       "3         659347 2014-01-17 08:00:00+00:00 2014-02-21 03:10:02+00:00        34\n",
       "4         656933 2013-12-17 08:00:00+00:00 2014-02-13 06:10:02+00:00        57\n",
       "...          ...                       ...                       ...       ...\n",
       "1419602   988180 2015-11-23 08:00:00+00:00 2016-01-02 01:00:03+00:00        39\n",
       "1419603   988213 2015-11-24 08:00:00+00:00 2016-01-02 16:40:07+00:00        39\n",
       "1419604   989109 2015-11-13 08:00:00+00:00 2016-01-03 22:20:04+00:00        51\n",
       "1419605   989143 2015-11-03 08:00:00+00:00 2016-01-05 08:50:02+00:00        63\n",
       "1419606   989240 2015-11-03 08:00:00+00:00 2016-01-03 20:50:06+00:00        61\n",
       "\n",
       "[1044962 rows x 4 columns]"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "#dropping rows where any of the two dates is missing\n",
    "loan_duration=loan.dropna(subset=['planned_expiration_time'])\n",
    "loan_duration=loan_duration.dropna(subset=['disburse_time'])\n",
    "\n",
    "loan_duration['duration']=loan['planned_expiration_time']-loan['disburse_time']\n",
    "loan_duration['duration']=loan_duration['duration'].dt.days\n",
    "display(loan_duration[['loan_id','disburse_time','planned_expiration_time','duration']])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<h3>Exercise 3<h3>\n",
    "Find the lenders that have funded at least twice."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "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>nr_funds</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>lender</th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>000</th>\n",
       "      <td>39</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>00000</th>\n",
       "      <td>39</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0002</th>\n",
       "      <td>70</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0101craign0101</th>\n",
       "      <td>71</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0132575</th>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>zyrorl</th>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>zzaman</th>\n",
       "      <td>11</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>zzanita</th>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>zzmcfate</th>\n",
       "      <td>56</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>zzrvmf8538</th>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>1005111 rows × 1 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                 nr_funds\n",
       "lender                   \n",
       " 000                   39\n",
       " 00000                 39\n",
       " 0002                  70\n",
       " 0101craign0101        71\n",
       " 0132575                4\n",
       "...                   ...\n",
       "zyrorl                  3\n",
       "zzaman                 11\n",
       "zzanita                 2\n",
       "zzmcfate               56\n",
       "zzrvmf8538              2\n",
       "\n",
       "[1005111 rows x 1 columns]"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#grouping by lender name\n",
    "loan_lenders_grouped = loan_lenders_normalized.groupby('lender').count()\n",
    "loan_lenders_grouped=loan_lenders_grouped.rename(columns={\"loan_id\":\"nr_funds\"})\n",
    "\n",
    "\n",
    "loan_lenders_grouped.loc[loan_lenders_grouped['nr_funds'] >= 2]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<h3>Exercise 4<h3>\n",
    "For each country, compute how many loans have involved that country as borrowers."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "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>index</th>\n",
       "      <th>loans</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Philippines</td>\n",
       "      <td>285336</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Kenya</td>\n",
       "      <td>143699</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Peru</td>\n",
       "      <td>86000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Cambodia</td>\n",
       "      <td>79701</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>El Salvador</td>\n",
       "      <td>64037</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>91</th>\n",
       "      <td>Papua New Guinea</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>92</th>\n",
       "      <td>Mauritania</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>93</th>\n",
       "      <td>Botswana</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>94</th>\n",
       "      <td>Uruguay</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>95</th>\n",
       "      <td>Canada</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>96 rows × 2 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "               index   loans\n",
       "0        Philippines  285336\n",
       "1              Kenya  143699\n",
       "2               Peru   86000\n",
       "3           Cambodia   79701\n",
       "4        El Salvador   64037\n",
       "..               ...     ...\n",
       "91  Papua New Guinea       1\n",
       "92        Mauritania       1\n",
       "93          Botswana       1\n",
       "94           Uruguay       1\n",
       "95            Canada       1\n",
       "\n",
       "[96 rows x 2 columns]"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#counting unique values\n",
    "nr_loans = loan['country_name'].value_counts().reset_index(name='loans')\n",
    "nr_loans"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<h3>Exercise 5<h3>\n",
    "For each country, compute the overall amount of money borrowed."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>country_name</th>\n",
       "      <th>overall_money_borrowed</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Afghanistan</td>\n",
       "      <td>1967950.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Albania</td>\n",
       "      <td>4307350.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Armenia</td>\n",
       "      <td>22950475.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Azerbaijan</td>\n",
       "      <td>14784625.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Belize</td>\n",
       "      <td>150175.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>91</th>\n",
       "      <td>Vietnam</td>\n",
       "      <td>24681100.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>92</th>\n",
       "      <td>Virgin Islands</td>\n",
       "      <td>10000.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>93</th>\n",
       "      <td>Yemen</td>\n",
       "      <td>3444000.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>94</th>\n",
       "      <td>Zambia</td>\n",
       "      <td>1978975.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>95</th>\n",
       "      <td>Zimbabwe</td>\n",
       "      <td>5851875.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>96 rows × 2 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "      country_name  overall_money_borrowed\n",
       "0      Afghanistan               1967950.0\n",
       "1          Albania               4307350.0\n",
       "2          Armenia              22950475.0\n",
       "3       Azerbaijan              14784625.0\n",
       "4           Belize                150175.0\n",
       "..             ...                     ...\n",
       "91         Vietnam              24681100.0\n",
       "92  Virgin Islands                 10000.0\n",
       "93           Yemen               3444000.0\n",
       "94          Zambia               1978975.0\n",
       "95        Zimbabwe               5851875.0\n",
       "\n",
       "[96 rows x 2 columns]"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "country_loans = loan.groupby('country_name')['loan_amount'].sum().reset_index(name='overall_money_borrowed')\n",
    "country_loans"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<h3>Exercise 6<h3>\n",
    "Like the previous point, but expressed as a percentage of the overall amount lent."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>country_name</th>\n",
       "      <th>overall_money_borrowed</th>\n",
       "      <th>overall_money_perc</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Afghanistan</td>\n",
       "      <td>1967950.0</td>\n",
       "      <td>0.166573</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Albania</td>\n",
       "      <td>4307350.0</td>\n",
       "      <td>0.364586</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Armenia</td>\n",
       "      <td>22950475.0</td>\n",
       "      <td>1.942589</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Azerbaijan</td>\n",
       "      <td>14784625.0</td>\n",
       "      <td>1.251410</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Belize</td>\n",
       "      <td>150175.0</td>\n",
       "      <td>0.012711</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>91</th>\n",
       "      <td>Vietnam</td>\n",
       "      <td>24681100.0</td>\n",
       "      <td>2.089074</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>92</th>\n",
       "      <td>Virgin Islands</td>\n",
       "      <td>10000.0</td>\n",
       "      <td>0.000846</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>93</th>\n",
       "      <td>Yemen</td>\n",
       "      <td>3444000.0</td>\n",
       "      <td>0.291509</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>94</th>\n",
       "      <td>Zambia</td>\n",
       "      <td>1978975.0</td>\n",
       "      <td>0.167506</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>95</th>\n",
       "      <td>Zimbabwe</td>\n",
       "      <td>5851875.0</td>\n",
       "      <td>0.495318</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>96 rows × 3 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "      country_name  overall_money_borrowed  overall_money_perc\n",
       "0      Afghanistan               1967950.0            0.166573\n",
       "1          Albania               4307350.0            0.364586\n",
       "2          Armenia              22950475.0            1.942589\n",
       "3       Azerbaijan              14784625.0            1.251410\n",
       "4           Belize                150175.0            0.012711\n",
       "..             ...                     ...                 ...\n",
       "91         Vietnam              24681100.0            2.089074\n",
       "92  Virgin Islands                 10000.0            0.000846\n",
       "93           Yemen               3444000.0            0.291509\n",
       "94          Zambia               1978975.0            0.167506\n",
       "95        Zimbabwe               5851875.0            0.495318\n",
       "\n",
       "[96 rows x 3 columns]"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "total_money=loan['loan_amount'].sum()\n",
    "country_loans['overall_money_perc'] = country_loans['overall_money_borrowed']/total_money*100\n",
    "country_loans"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<h3>Exercise 6<h3>\n",
    "Like the three previous points, but split for each year (with respect to disburse time).\n",
    "\n",
    "Exercise 4v2 ---> how many loans have involved that country as borrowers."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "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>year</th>\n",
       "      <th>country_name</th>\n",
       "      <th>loans</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2005.0</td>\n",
       "      <td>Bulgaria</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2005.0</td>\n",
       "      <td>Cambodia</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2005.0</td>\n",
       "      <td>Ecuador</td>\n",
       "      <td>10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2005.0</td>\n",
       "      <td>Gaza</td>\n",
       "      <td>8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2005.0</td>\n",
       "      <td>Honduras</td>\n",
       "      <td>72</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>743</th>\n",
       "      <td>2018.0</td>\n",
       "      <td>Tanzania</td>\n",
       "      <td>45</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>744</th>\n",
       "      <td>2018.0</td>\n",
       "      <td>Thailand</td>\n",
       "      <td>13</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>745</th>\n",
       "      <td>2018.0</td>\n",
       "      <td>Uganda</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>746</th>\n",
       "      <td>2018.0</td>\n",
       "      <td>Uruguay</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>747</th>\n",
       "      <td>2018.0</td>\n",
       "      <td>Zambia</td>\n",
       "      <td>14</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>748 rows × 3 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "       year country_name  loans\n",
       "0    2005.0     Bulgaria      3\n",
       "1    2005.0     Cambodia      2\n",
       "2    2005.0      Ecuador     10\n",
       "3    2005.0         Gaza      8\n",
       "4    2005.0     Honduras     72\n",
       "..      ...          ...    ...\n",
       "743  2018.0     Tanzania     45\n",
       "744  2018.0     Thailand     13\n",
       "745  2018.0       Uganda      2\n",
       "746  2018.0      Uruguay      1\n",
       "747  2018.0       Zambia     14\n",
       "\n",
       "[748 rows x 3 columns]"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "loan['year']=loan['disburse_time'].dt.year\n",
    "nr_loans_annual=loan.groupby(['year','country_name']).size().reset_index(name='loans')\n",
    "nr_loans_annual"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<h3>Exercise 5v2 ---> overall amount of money borrowed.<h3>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>country_name</th>\n",
       "      <th>year</th>\n",
       "      <th>loan_amount</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Afghanistan</td>\n",
       "      <td>2007.0</td>\n",
       "      <td>194975.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Afghanistan</td>\n",
       "      <td>2008.0</td>\n",
       "      <td>365375.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Afghanistan</td>\n",
       "      <td>2009.0</td>\n",
       "      <td>585125.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Afghanistan</td>\n",
       "      <td>2010.0</td>\n",
       "      <td>563350.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Afghanistan</td>\n",
       "      <td>2011.0</td>\n",
       "      <td>245125.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>743</th>\n",
       "      <td>Zimbabwe</td>\n",
       "      <td>2013.0</td>\n",
       "      <td>678525.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>744</th>\n",
       "      <td>Zimbabwe</td>\n",
       "      <td>2014.0</td>\n",
       "      <td>1311575.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>745</th>\n",
       "      <td>Zimbabwe</td>\n",
       "      <td>2015.0</td>\n",
       "      <td>723625.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>746</th>\n",
       "      <td>Zimbabwe</td>\n",
       "      <td>2016.0</td>\n",
       "      <td>788600.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>747</th>\n",
       "      <td>Zimbabwe</td>\n",
       "      <td>2017.0</td>\n",
       "      <td>1237600.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>748 rows × 3 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "    country_name    year  loan_amount\n",
       "0    Afghanistan  2007.0     194975.0\n",
       "1    Afghanistan  2008.0     365375.0\n",
       "2    Afghanistan  2009.0     585125.0\n",
       "3    Afghanistan  2010.0     563350.0\n",
       "4    Afghanistan  2011.0     245125.0\n",
       "..           ...     ...          ...\n",
       "743     Zimbabwe  2013.0     678525.0\n",
       "744     Zimbabwe  2014.0    1311575.0\n",
       "745     Zimbabwe  2015.0     723625.0\n",
       "746     Zimbabwe  2016.0     788600.0\n",
       "747     Zimbabwe  2017.0    1237600.0\n",
       "\n",
       "[748 rows x 3 columns]"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "country_loans_annual=loan.groupby(['country_name','year'])['loan_amount'].sum().reset_index()\n",
    "country_loans_annual"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<h3>Exercise 6v2 --->  overall amount of money borrowed expressed as percentage"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "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_name</th>\n",
       "      <th>year</th>\n",
       "      <th>loan_amount</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Afghanistan</td>\n",
       "      <td>2007.0</td>\n",
       "      <td>0.012623</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Afghanistan</td>\n",
       "      <td>2008.0</td>\n",
       "      <td>0.009268</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Afghanistan</td>\n",
       "      <td>2009.0</td>\n",
       "      <td>0.009803</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Afghanistan</td>\n",
       "      <td>2010.0</td>\n",
       "      <td>0.007759</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Afghanistan</td>\n",
       "      <td>2011.0</td>\n",
       "      <td>0.002616</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>743</th>\n",
       "      <td>Zimbabwe</td>\n",
       "      <td>2013.0</td>\n",
       "      <td>0.005139</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>744</th>\n",
       "      <td>Zimbabwe</td>\n",
       "      <td>2014.0</td>\n",
       "      <td>0.008613</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>745</th>\n",
       "      <td>Zimbabwe</td>\n",
       "      <td>2015.0</td>\n",
       "      <td>0.004644</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>746</th>\n",
       "      <td>Zimbabwe</td>\n",
       "      <td>2016.0</td>\n",
       "      <td>0.004899</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>747</th>\n",
       "      <td>Zimbabwe</td>\n",
       "      <td>2017.0</td>\n",
       "      <td>0.007453</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>748 rows × 3 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "    country_name    year  loan_amount\n",
       "0    Afghanistan  2007.0     0.012623\n",
       "1    Afghanistan  2008.0     0.009268\n",
       "2    Afghanistan  2009.0     0.009803\n",
       "3    Afghanistan  2010.0     0.007759\n",
       "4    Afghanistan  2011.0     0.002616\n",
       "..           ...     ...          ...\n",
       "743     Zimbabwe  2013.0     0.005139\n",
       "744     Zimbabwe  2014.0     0.008613\n",
       "745     Zimbabwe  2015.0     0.004644\n",
       "746     Zimbabwe  2016.0     0.004899\n",
       "747     Zimbabwe  2017.0     0.007453\n",
       "\n",
       "[748 rows x 3 columns]"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "percentage_annual=loan.groupby(['country_name','year'])['loan_amount'].sum()/loan.groupby('year')['loan_amount'].sum()\n",
    "percentage_annual.reset_index()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<h3>Exercise 8<h3>\n",
    "For each lender, compute the overall amount of money lent. \n",
    "For each loan that has more than one lender, you must assume that all lenders contributed the same amount."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "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>loan_id</th>\n",
       "      <th>counts</th>\n",
       "      <th>loan_amount</th>\n",
       "      <th>lender</th>\n",
       "      <th>single_share</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>84</td>\n",
       "      <td>3</td>\n",
       "      <td>500.0</td>\n",
       "      <td>ward</td>\n",
       "      <td>166.666667</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>84</td>\n",
       "      <td>3</td>\n",
       "      <td>500.0</td>\n",
       "      <td>michael</td>\n",
       "      <td>166.666667</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>84</td>\n",
       "      <td>3</td>\n",
       "      <td>500.0</td>\n",
       "      <td>brooke</td>\n",
       "      <td>166.666667</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>85</td>\n",
       "      <td>2</td>\n",
       "      <td>500.0</td>\n",
       "      <td>michael</td>\n",
       "      <td>250.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>85</td>\n",
       "      <td>2</td>\n",
       "      <td>500.0</td>\n",
       "      <td>patrick</td>\n",
       "      <td>250.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>28293907</th>\n",
       "      <td>1444051</td>\n",
       "      <td>1</td>\n",
       "      <td>225.0</td>\n",
       "      <td>lauren1424</td>\n",
       "      <td>225.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>28293908</th>\n",
       "      <td>1444053</td>\n",
       "      <td>1</td>\n",
       "      <td>725.0</td>\n",
       "      <td>dario6858</td>\n",
       "      <td>725.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>28293909</th>\n",
       "      <td>1444058</td>\n",
       "      <td>1</td>\n",
       "      <td>25.0</td>\n",
       "      <td>el5018</td>\n",
       "      <td>25.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>28293910</th>\n",
       "      <td>1444063</td>\n",
       "      <td>1</td>\n",
       "      <td>50.0</td>\n",
       "      <td>pgs67</td>\n",
       "      <td>50.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>28293911</th>\n",
       "      <td>1444065</td>\n",
       "      <td>1</td>\n",
       "      <td>25.0</td>\n",
       "      <td>el5018</td>\n",
       "      <td>25.000000</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>28293912 rows × 5 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "          loan_id  counts  loan_amount      lender  single_share\n",
       "0              84       3        500.0        ward    166.666667\n",
       "1              84       3        500.0     michael    166.666667\n",
       "2              84       3        500.0      brooke    166.666667\n",
       "3              85       2        500.0     michael    250.000000\n",
       "4              85       2        500.0     patrick    250.000000\n",
       "...           ...     ...          ...         ...           ...\n",
       "28293907  1444051       1        225.0  lauren1424    225.000000\n",
       "28293908  1444053       1        725.0   dario6858    725.000000\n",
       "28293909  1444058       1         25.0      el5018     25.000000\n",
       "28293910  1444063       1         50.0       pgs67     50.000000\n",
       "28293911  1444065       1         25.0      el5018     25.000000\n",
       "\n",
       "[28293912 rows x 5 columns]"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "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>lender</th>\n",
       "      <th>overall_amount</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>000</td>\n",
       "      <td>1672.618411</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>00000</td>\n",
       "      <td>1380.693644</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>0002</td>\n",
       "      <td>2472.563566</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>00mike00</td>\n",
       "      <td>52.631579</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>0101craign0101</td>\n",
       "      <td>2623.565117</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1639021</th>\n",
       "      <td>zzanita</td>\n",
       "      <td>87.500000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1639022</th>\n",
       "      <td>zzcyna7269</td>\n",
       "      <td>55.357143</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1639023</th>\n",
       "      <td>zzinnia</td>\n",
       "      <td>38.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1639024</th>\n",
       "      <td>zzmcfate</td>\n",
       "      <td>2287.291955</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1639025</th>\n",
       "      <td>zzrvmf8538</td>\n",
       "      <td>106.417625</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>1639026 rows × 2 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                  lender  overall_amount\n",
       "0                    000     1672.618411\n",
       "1                  00000     1380.693644\n",
       "2                   0002     2472.563566\n",
       "3               00mike00       52.631579\n",
       "4         0101craign0101     2623.565117\n",
       "...                  ...             ...\n",
       "1639021          zzanita       87.500000\n",
       "1639022       zzcyna7269       55.357143\n",
       "1639023          zzinnia       38.000000\n",
       "1639024         zzmcfate     2287.291955\n",
       "1639025       zzrvmf8538      106.417625\n",
       "\n",
       "[1639026 rows x 2 columns]"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "#counting how many lenders partacipate to total amount\n",
    "loan_merged=pd.merge(loan[['loan_id','loan_amount']],loan_lenders_normalized,on='loan_id')\n",
    "count_shares=loan_merged.groupby('loan_id').size().reset_index(name='counts')\n",
    "lenders_shares=pd.merge(count_shares,loan_merged,on='loan_id')\n",
    "#dividing for every lender that contributed\n",
    "lenders_shares['single_share']=lenders_shares['loan_amount']/lenders_shares['counts']\n",
    "display(lenders_shares)\n",
    "lenders_overall_money=lenders_shares.groupby('lender').sum()['single_share'].reset_index(name='overall_amount')\n",
    "display(lenders_overall_money)\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<h3>Exercise 9<h3>\n",
    "For each country, compute the difference between the overall amount of money lent and the overall amount of money borrowed. \n",
    "Since the country of the lender is often unknown, \n",
    "you can assume that the true distribution among the countries is the same as the one computed from the rows where the country is known.\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {
    "scrolled": true
   },
   "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_code</th>\n",
       "      <th>overall_lent</th>\n",
       "      <th>overall_borrowed</th>\n",
       "      <th>difference_amount</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>AD</td>\n",
       "      <td>115.15</td>\n",
       "      <td>0.00</td>\n",
       "      <td>115.15</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>AE</td>\n",
       "      <td>116011.09</td>\n",
       "      <td>0.00</td>\n",
       "      <td>116011.09</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>AF</td>\n",
       "      <td>6260.80</td>\n",
       "      <td>1967950.00</td>\n",
       "      <td>-1961689.20</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>AL</td>\n",
       "      <td>732.29</td>\n",
       "      <td>4307350.00</td>\n",
       "      <td>-4306617.71</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>AM</td>\n",
       "      <td>1042.01</td>\n",
       "      <td>22950475.00</td>\n",
       "      <td>-22949432.99</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>202</th>\n",
       "      <td>SB</td>\n",
       "      <td>0.00</td>\n",
       "      <td>815575.00</td>\n",
       "      <td>-815575.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>203</th>\n",
       "      <td>SO</td>\n",
       "      <td>0.00</td>\n",
       "      <td>308725.00</td>\n",
       "      <td>-308725.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>204</th>\n",
       "      <td>TD</td>\n",
       "      <td>0.00</td>\n",
       "      <td>20075.00</td>\n",
       "      <td>-20075.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>205</th>\n",
       "      <td>WS</td>\n",
       "      <td>0.00</td>\n",
       "      <td>12212175.00</td>\n",
       "      <td>-12212175.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>206</th>\n",
       "      <td>ZW</td>\n",
       "      <td>0.00</td>\n",
       "      <td>5851875.00</td>\n",
       "      <td>-5851875.00</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>207 rows × 4 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "    country_code  overall_lent  overall_borrowed  difference_amount\n",
       "0             AD        115.15              0.00             115.15\n",
       "1             AE     116011.09              0.00          116011.09\n",
       "2             AF       6260.80        1967950.00        -1961689.20\n",
       "3             AL        732.29        4307350.00        -4306617.71\n",
       "4             AM       1042.01       22950475.00       -22949432.99\n",
       "..           ...           ...               ...                ...\n",
       "202           SB          0.00         815575.00         -815575.00\n",
       "203           SO          0.00         308725.00         -308725.00\n",
       "204           TD          0.00          20075.00          -20075.00\n",
       "205           WS          0.00       12212175.00       -12212175.00\n",
       "206           ZW          0.00        5851875.00        -5851875.00\n",
       "\n",
       "[207 rows x 4 columns]"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "#suppressing scientific notation\n",
    "pd.options.display.float_format = '{:.2f}'.format\n",
    "\n",
    "#dropping rows with missing values\n",
    "lenders_country=lenders.dropna(subset=['country_code'])\n",
    "\n",
    "#adding country code to previous df with overall money\n",
    "lenders_country_money=pd.merge(lenders_country[['permanent_name','country_code']],lenders_overall_money,left_on='permanent_name',right_on='lender')\n",
    "lenders_country_money=lenders_country_money.drop('permanent_name',axis=1)\n",
    "loans_per_country=lenders_country_money.groupby('country_code').sum()['overall_amount'].reset_index()\n",
    "borrows_per_country=loan.groupby('country_code').sum()['loan_amount'].reset_index()\n",
    "overall_country=pd.merge(loans_per_country,borrows_per_country,how='outer')\n",
    "\n",
    "#considering NA as zeros (cases where a country has only loans or only borrows)\n",
    "overall_country=overall_country.fillna(0)\n",
    "overall_country['difference_amount']=overall_country['overall_amount']-overall_country['loan_amount']\n",
    "overall_country.columns = ['country_code','overall_lent','overall_borrowed','difference_amount']\n",
    "display(overall_country)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<h3>Exercise 10<h3>\n",
    "Which country has the highest ratio between the difference computed at the previous point and the population?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "country_code                 LU\n",
       "difference_amount     135863.29\n",
       "country_name         Luxembourg\n",
       "population               583455\n",
       "ratio                      0.23\n",
       "Name: 82, dtype: object"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#merging output from ex9 with country_stats\n",
    "ratio_country=pd.merge(overall_country[['country_code','difference_amount']],country_stats[['country_code','country_name','population']],how='inner')\n",
    "ratio_country['ratio']=ratio_country['difference_amount']/ratio_country['population']\n",
    "#searching for highest ratio\n",
    "ratio_country.loc[ratio_country['ratio'].idxmax()]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<h3>Exercise 11<h3>\n",
    "Which country has the highest ratio between the difference computed at point 9 \n",
    "and the population that is not below the poverty line?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "country_code                             CA\n",
       "difference_amount                4480813.77\n",
       "country_name                         Canada\n",
       "population                         36624199\n",
       "population_below_poverty_line          9.40\n",
       "pop_over_povl                   33181524.00\n",
       "ratio                                  0.14\n",
       "Name: 21, dtype: object"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "poverty_per_country=pd.merge(overall_country[['country_code','difference_amount']],country_stats[['country_code','country_name','population','population_below_poverty_line']],how='inner')\n",
    "# calculating population over poverty line:  tot_pop*perc_over_povl/100\n",
    "poverty_per_country['pop_over_povl']=(poverty_per_country['population']*(100-poverty_per_country['population_below_poverty_line']))//100\n",
    "poverty_per_country['ratio']=poverty_per_country['difference_amount']/poverty_per_country['pop_over_povl']\n",
    "#searching for highest ratio\n",
    "poverty_per_country.loc[poverty_per_country['ratio'].idxmax()]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<h3>Exercise 12<h3>\n",
    "For each year, compute the total amount of loans. Each loan that has planned expiration time and disburse time in different years must have its amount distributed proportionally to the number of days in each year. \n",
    "For example, a loan with disburse time December 1st, 2016, planned expiration time January 30th 2018, \n",
    "and amount 5000USD has an amount of 5000USD * 31 / (31+365+30) = 363.85 for 2016, \n",
    "5000USD * 365 / (31+365+30) = 4284.04 for 2017, and 5000USD * 30 / (31+365+30) = 352.11 for 2018."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [],
   "source": [
    "#returns a list with every amount per year\n",
    "def divide_money_by_year(row):\n",
    "    #localize required for subtraction of two timestamps\n",
    "    start= row['disburse_time'].tz_localize(None)\n",
    "    end= row['planned_expiration_time'].tz_localize(None)\n",
    "    money= row['loan_amount']\n",
    "    years_difference = end.year - start.year \n",
    "    days=[]\n",
    "\n",
    "    #variable keeping track of latest year added to the list\n",
    "    index = start\n",
    "    while(years_difference >= 0):\n",
    "        #case where disburse and expiration is in same year\n",
    "        if years_difference == 0:\n",
    "            days.append((end - index).days)\n",
    "            #last year, exit from while loop\n",
    "            years_difference = -1\n",
    "        #case of multiple years    \n",
    "        else:\n",
    "            last_of_the_year = datetime(index.year+1,1,1)\n",
    "            days.append((last_of_the_year - index).days)\n",
    "            index = last_of_the_year\n",
    "            years_difference = years_difference - 1\n",
    "   \n",
    "    if sum(days)==0:\n",
    "        #creating a list for returning same type\n",
    "        moneylist=[]\n",
    "        moneylist.append(money)\n",
    "        return moneylist\n",
    "    else:\n",
    "        for i in range(len(days)):\n",
    "            #calculating amount per year\n",
    "            days[i] = days[i]*money/sum(days)\n",
    "    return(days)        \n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>loan_name</th>\n",
       "      <th>disburse_time</th>\n",
       "      <th>planned_expiration_time</th>\n",
       "      <th>money_per_year</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Aivy</td>\n",
       "      <td>2013-12-22 08:00:00+00:00</td>\n",
       "      <td>2014-02-14 03:30:06+00:00</td>\n",
       "      <td>[21.22641509433962, 84.3216661845531]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Idalia Marizza</td>\n",
       "      <td>2013-12-20 08:00:00+00:00</td>\n",
       "      <td>2014-03-26 22:25:07+00:00</td>\n",
       "      <td>[46.31578947368421, 257.8352180936995]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Aasia</td>\n",
       "      <td>2014-01-09 08:00:00+00:00</td>\n",
       "      <td>2014-02-15 21:10:05+00:00</td>\n",
       "      <td>[400.0]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Gulmira</td>\n",
       "      <td>2014-01-17 08:00:00+00:00</td>\n",
       "      <td>2014-02-21 03:10:02+00:00</td>\n",
       "      <td>[625.0]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Ricky\\t</td>\n",
       "      <td>2013-12-17 08:00:00+00:00</td>\n",
       "      <td>2014-02-13 06:10:02+00:00</td>\n",
       "      <td>[104.3859649122807, 123.99416736102845]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1419602</th>\n",
       "      <td>NaN</td>\n",
       "      <td>2015-11-23 08:00:00+00:00</td>\n",
       "      <td>2016-01-02 01:00:03+00:00</td>\n",
       "      <td>[389.7435897435897, 1.0236892184526545]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1419603</th>\n",
       "      <td>Perlita</td>\n",
       "      <td>2015-11-24 08:00:00+00:00</td>\n",
       "      <td>2016-01-02 16:40:07+00:00</td>\n",
       "      <td>[292.10526315789474, 1.0235230741605315]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1419604</th>\n",
       "      <td>Okyeso Nyame Group</td>\n",
       "      <td>2015-11-13 08:00:00+00:00</td>\n",
       "      <td>2016-01-03 22:20:04+00:00</td>\n",
       "      <td>[2328.0, 2.0815450643776825]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1419605</th>\n",
       "      <td>Exequila</td>\n",
       "      <td>2015-11-03 08:00:00+00:00</td>\n",
       "      <td>2016-01-05 08:50:02+00:00</td>\n",
       "      <td>[93.54838709677419, 4.1005291005291005]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1419606</th>\n",
       "      <td>Lydia</td>\n",
       "      <td>2015-11-03 08:00:00+00:00</td>\n",
       "      <td>2016-01-03 20:50:06+00:00</td>\n",
       "      <td>[169.16666666666666, 2.044790652385589]</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>1044962 rows × 4 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                  loan_name             disburse_time  \\\n",
       "0                      Aivy 2013-12-22 08:00:00+00:00   \n",
       "1            Idalia Marizza 2013-12-20 08:00:00+00:00   \n",
       "2                     Aasia 2014-01-09 08:00:00+00:00   \n",
       "3                   Gulmira 2014-01-17 08:00:00+00:00   \n",
       "4                   Ricky\\t 2013-12-17 08:00:00+00:00   \n",
       "...                     ...                       ...   \n",
       "1419602                 NaN 2015-11-23 08:00:00+00:00   \n",
       "1419603             Perlita 2015-11-24 08:00:00+00:00   \n",
       "1419604  Okyeso Nyame Group 2015-11-13 08:00:00+00:00   \n",
       "1419605            Exequila 2015-11-03 08:00:00+00:00   \n",
       "1419606               Lydia 2015-11-03 08:00:00+00:00   \n",
       "\n",
       "          planned_expiration_time                            money_per_year  \n",
       "0       2014-02-14 03:30:06+00:00     [21.22641509433962, 84.3216661845531]  \n",
       "1       2014-03-26 22:25:07+00:00    [46.31578947368421, 257.8352180936995]  \n",
       "2       2014-02-15 21:10:05+00:00                                   [400.0]  \n",
       "3       2014-02-21 03:10:02+00:00                                   [625.0]  \n",
       "4       2014-02-13 06:10:02+00:00   [104.3859649122807, 123.99416736102845]  \n",
       "...                           ...                                       ...  \n",
       "1419602 2016-01-02 01:00:03+00:00   [389.7435897435897, 1.0236892184526545]  \n",
       "1419603 2016-01-02 16:40:07+00:00  [292.10526315789474, 1.0235230741605315]  \n",
       "1419604 2016-01-03 22:20:04+00:00              [2328.0, 2.0815450643776825]  \n",
       "1419605 2016-01-05 08:50:02+00:00   [93.54838709677419, 4.1005291005291005]  \n",
       "1419606 2016-01-03 20:50:06+00:00   [169.16666666666666, 2.044790652385589]  \n",
       "\n",
       "[1044962 rows x 4 columns]"
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "lender_per_year = loan\n",
    "lender_per_year=lender_per_year.dropna(subset=['planned_expiration_time'])\n",
    "lender_per_year=lender_per_year.dropna(subset=['disburse_time'])\n",
    "#applying function to loan df\n",
    "lender_per_year['money_per_year'] = lender_per_year.apply(divide_money_by_year, axis=1)\n",
    "lender_per_year[['loan_name','disburse_time','planned_expiration_time','money_per_year']]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "loan_id                                                                      1077942\n",
       "loan_name                                                                    Patrick\n",
       "original_language                                                            English\n",
       "description                        My name is Patrick Marubu Njoroge. I was born ...\n",
       "description_translated             My name is Patrick Marubu Njoroge. I was born ...\n",
       "funded_amount                                                                 650.00\n",
       "loan_amount                                                                   650.00\n",
       "status                                                                        funded\n",
       "activity_name                                                               Services\n",
       "sector_name                                                                 Services\n",
       "loan_use                           buy raw materials and a kiln for my cook stove...\n",
       "country_code                                                                      KE\n",
       "country_name                                                                   Kenya\n",
       "town_name                                                                        NaN\n",
       "currency_policy                                                           not shared\n",
       "currency_exchange_coverage_rate                                                  NaN\n",
       "currency                                                                         KES\n",
       "partner_id                                                                       NaN\n",
       "posted_time                                            2011-11-20 20:56:53.000 +0000\n",
       "planned_expiration_time                                    2016-06-29 01:46:30+00:00\n",
       "disburse_time                                              2011-12-22 16:49:11+00:00\n",
       "raised_time                                            2011-12-22 01:55:20.000 +0000\n",
       "lender_term                                                                    12.00\n",
       "num_lenders_total                                                                  9\n",
       "num_journal_entries                                                                1\n",
       "num_bulk_entries                                                                   1\n",
       "tags                                                                             NaN\n",
       "borrower_genders                                                                male\n",
       "borrower_pictured                                                               true\n",
       "repayment_interval                                                           monthly\n",
       "distribution_model                                                            direct\n",
       "year                                                                         2011.00\n",
       "money_per_year                     [3.5454545454545454, 144.66003316749587, 166.7...\n",
       "diff                                                                               5\n",
       "Name: 33780, dtype: object"
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "lender_per_year['diff']=lender_per_year['planned_expiration_time'].dt.year-lender_per_year['disburse_time'].dt.year\n",
    "lender_per_year.loc[lender_per_year['diff'].idxmax()]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "{2013: 2031229.2159526998,\n",
       " 2014: 3343962.692399011,\n",
       " 2015: 3600229.0464434223,\n",
       " 2012: 1010384.8951696253,\n",
       " 2016: 3600231.091234075,\n",
       " 2017: 3217805.9695254937,\n",
       " 2018: 2104434.187236917,\n",
       " 2011: 639263.9662771509}"
      ]
     },
     "execution_count": 19,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#summing total amount per year\n",
    "\n",
    "#storing in a dictionary\n",
    "years = dict()\n",
    "    \n",
    "for i,row in lender_per_year.iterrows():\n",
    "        year_start= row['disburse_time'].year\n",
    "        year_finish=row['planned_expiration_time'].year\n",
    "        if len(row['money_per_year']) == 1:    \n",
    "            years[year_start] = years.get(year_start,0)+row['money_per_year'][0]\n",
    "        if len(row['money_per_year']) == 2:\n",
    "            years[year_start] = years.get(year_start,0)+row['money_per_year'][0]\n",
    "            years[year_finish] = years.get(year_start,0)+row['money_per_year'][1]\n",
    "        if len(row['money_per_year']) == 3:\n",
    "            years[year_start] = years.get(year_start,0)+row['money_per_year'][0]\n",
    "            years[year_finish] = years.get(year_start,0)+row['money_per_year'][1]\n",
    "            years[year_finish] = years.get(year_start,0)+row['money_per_year'][2]\n",
    "        if len(row['money_per_year']) == 4:\n",
    "            years[year_start] = years.get(year_start,0)+row['money_per_year'][0]\n",
    "            years[year_finish] = years.get(year_start,0)+row['money_per_year'][1]\n",
    "            years[year_finish] = years.get(year_start,0)+row['money_per_year'][2]\n",
    "            years[year_finish] = years.get(year_start,0)+row['money_per_year'][3]\n",
    "        if len(row['money_per_year']) == 5:\n",
    "            years[year_start] = years.get(year_start,0)+row['money_per_year'][0]\n",
    "            years[year_finish] = years.get(year_start,0)+row['money_per_year'][1]\n",
    "            years[year_finish] = years.get(year_start,0)+row['money_per_year'][2]\n",
    "            years[year_finish] = years.get(year_start,0)+row['money_per_year'][3]\n",
    "            years[year_finish] = years.get(year_start,0)+row['money_per_year'][4]\n",
    "\n",
    "            \n",
    "years                    "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Executing time: 5 minutes\n",
      "of which 41 seconds were for importing \n"
     ]
    }
   ],
   "source": [
    "endtime = time.time()\n",
    "print(\"Executing time: {} minutes\".format(int((endtime-starttime)/60)))\n",
    "print(\"of which {} seconds were for importing \".format(int((importtime-starttime))))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.8.5"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}