Dario Della Mura - 793751.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "##  FOUNDATIONS OF COMPUTER SCIENCE PROJECT"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Dario Della Mura - 793751"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Carico le Librerie necessarie per il progetto."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import numpy as np\n",
    "import datetime\n",
    "from IPython.display import display"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### 1) Normalize the loan_lenders table. In the normalized table, each row must have one loan_id and one lender."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Carico il dataset loan_lenders:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "loans_lenders= pd.read_csv('D:/UNIVERSITÀ/PROGETTI/FOCS/DATASET/loans_lenders.csv')\n",
    "#loans_lenders.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Il mio obbiettivo è quello di normalizzare il dataset \"lenders\" ovvero renderlo atomico: ogni cella deve contenere un solo elemento. Per poter normalizzare il datatset, utilizzo la funzione \"explode\" che consente di dividere gli elementi all'interno di una cella in più celle. Attraverso str.split identifico il delimitatore, nel mio caso la \" , \", ovvero qul carattere dopo il quale applicare la \"explode\" :"
   ]
  },
  {
   "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>lenders</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>0</th>\n",
       "      <td>483693</td>\n",
       "      <td>sam4326</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>483693</td>\n",
       "      <td>camaran3922</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>483693</td>\n",
       "      <td>lachheb1865</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>483693</td>\n",
       "      <td>rebecca3499</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   loan_id       lenders\n",
       "0   483693        muc888\n",
       "0   483693       sam4326\n",
       "0   483693   camaran3922\n",
       "0   483693   lachheb1865\n",
       "0   483693   rebecca3499"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "loans_lenders_nor = loans_lenders.assign(lenders=loans_lenders['lenders'].str.split(',')).explode('lenders')\n",
    "loans_lenders_nor.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Non potendo controllare tutto il dataset, non posso essere sicuro che ogni singola osservazione contenuta nella  colonna \"lenders\" sia scritta allo stesso modo (in termini di spazio prima e dopo al singolo lender). Per tanto applico la funzione str.split per eliminare eventuali spazi prima e dopo al nome del singolo lender :"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [],
   "source": [
    "loans_lenders_nor[\"lenders\"] = loans_lenders_nor[\"lenders\"].str.strip()\n",
    "#loans_lenders_nor.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### 2) For each loan, add a column duration corresponding to the number of days between the disburse time and the planned expiration time. If any of those two dates is missing, also the duration must be missing."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Carico il datatset \"loans\". Per un problema di memoria non vengono visualizzate tutte le colonne quindi, per una corretta esplorazione del dataset, utilizzo la funzione \"pd.set_option('display.max_columns', None)\" in modo da forzare la visualizzazione di tutte le colonne all'interno detaset. Dopo di che imposto le colonne \"disburse_time\" e \"planned_expiration_time\" come colonne contenenti delle date :"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [],
   "source": [
    "pd.set_option('display.max_columns', None)\n",
    "loans = pd.read_csv('D:/UNIVERSITÀ/PROGETTI/FOCS/DATASET/loans.csv')\n",
    "\n",
    "loans.disburse_time = pd.to_datetime(loans.disburse_time)\n",
    "loans.planned_expiration_time = pd.to_datetime(loans.planned_expiration_time)\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Calcolo ora la duration di ogni prestito come differenza tra \"planned_expiration_time\" e \"disburse_time\" (scadenza prevista meno giorno di erogazione del prestito). Decido di tener conto solo del giorno e non di ore e fuso orario:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "   loan_id  loan_amount disburse_time planned_expiration_time duration\n",
      "0   657307        125.0    2013-12-22              2014-02-14  54 days\n",
      "1   657259        400.0    2013-12-20              2014-03-26  96 days\n",
      "2   658010        400.0    2014-01-09              2014-02-15  37 days\n",
      "3   659347        625.0    2014-01-17              2014-02-21  35 days\n",
      "4   656933        425.0    2013-12-17              2014-02-13  58 days\n"
     ]
    }
   ],
   "source": [
    "loans['planned_expiration_time'] = loans['planned_expiration_time'].dt.tz_localize(None)\n",
    "\n",
    "loans['disburse_time'] = loans['disburse_time'].dt.tz_localize(None)\n",
    "\n",
    "loans['planned_expiration_time'] = loans['planned_expiration_time'].dt.normalize()\n",
    "\n",
    "loans['disburse_time'] = loans['disburse_time'].dt.normalize()\n",
    "\n",
    "loans['duration']=  (loans['planned_expiration_time'] - loans['disburse_time'])\n",
    "print(loans[['loan_id', 'loan_amount', 'disburse_time', 'planned_expiration_time', 'duration']].head())"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### 3) Find the lenders that have funded at least twice :"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Utilizzo il dataset loans_lenders normalizzato e applico una groupby in modo da ragruppare per singoli lenders e utilizzare il count per contare quanti hanno effettuato almeno 2 prestiti: "
   ]
  },
  {
   "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>lenders</th>\n",
       "      <th>count_loans</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>000</td>\n",
       "      <td>40</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>00000</td>\n",
       "      <td>39</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>0002</td>\n",
       "      <td>70</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>0101craign0101</td>\n",
       "      <td>71</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>0132575</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1383792</th>\n",
       "      <td>zzinnia</td>\n",
       "      <td>28</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1383793</th>\n",
       "      <td>zzivnk7094</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1383794</th>\n",
       "      <td>zzmcfate</td>\n",
       "      <td>1730</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1383796</th>\n",
       "      <td>zzrvmf8538</td>\n",
       "      <td>15</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1383797</th>\n",
       "      <td>zzzsai</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>901811 rows × 2 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                lenders  count_loans\n",
       "0                   000           40\n",
       "1                 00000           39\n",
       "2                  0002           70\n",
       "4        0101craign0101           71\n",
       "5               0132575            4\n",
       "...                 ...          ...\n",
       "1383792         zzinnia           28\n",
       "1383793      zzivnk7094            4\n",
       "1383794        zzmcfate         1730\n",
       "1383796      zzrvmf8538           15\n",
       "1383797          zzzsai            4\n",
       "\n",
       "[901811 rows x 2 columns]"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "lenders_least_2_loans = pd.DataFrame(loans_lenders_nor.groupby('lenders').count()['loan_id']).reset_index()[['lenders','loan_id']].query('loan_id > 1').rename(columns={\"loan_id\" : \"count_loans\"})\n",
    "lenders_least_2_loans"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### 4) For each country, compute how many loans have involved that country as borrowers."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Decido di pulire il dataset loans in modo tale da utilizzare solo le osservazioni non nulle in \"duration\" e che abbiamo una duration positiva (in quanto una duration negativa avrebbe poco senso) : "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [],
   "source": [
    "loans_no_null =  loans[loans[\"duration\"].notnull()==True]\n",
    "loans_cleaned = loans_no_null[loans_no_null[\"planned_expiration_time\"] > loans_no_null[\"disburse_time\"]]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Ora applico una groupby su \"country_name\" in modo tale da raggruppare i singoli paesi per poi effettuare una count che mi dica quanti prestiti sono stati erogati al singolo paese :"
   ]
  },
  {
   "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>count_loans</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Afghanistan</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Albania</td>\n",
       "      <td>3075</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Armenia</td>\n",
       "      <td>12809</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Azerbaijan</td>\n",
       "      <td>4026</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Belize</td>\n",
       "      <td>97</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>79</th>\n",
       "      <td>Vanuatu</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>80</th>\n",
       "      <td>Vietnam</td>\n",
       "      <td>16203</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>81</th>\n",
       "      <td>Yemen</td>\n",
       "      <td>4184</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>82</th>\n",
       "      <td>Zambia</td>\n",
       "      <td>1017</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>83</th>\n",
       "      <td>Zimbabwe</td>\n",
       "      <td>5057</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>84 rows × 2 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "   country_name  count_loans\n",
       "0   Afghanistan            1\n",
       "1       Albania         3075\n",
       "2       Armenia        12809\n",
       "3    Azerbaijan         4026\n",
       "4        Belize           97\n",
       "..          ...          ...\n",
       "79      Vanuatu            4\n",
       "80      Vietnam        16203\n",
       "81        Yemen         4184\n",
       "82       Zambia         1017\n",
       "83     Zimbabwe         5057\n",
       "\n",
       "[84 rows x 2 columns]"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "loans_for_country = loans_cleaned.groupby(\"country_name\").count().reset_index()[[\"country_name\", \"loan_id\"]].rename(columns={\"loan_id\" : \"count_loans\"})\n",
    "loans_for_country"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### 5) For each country, compute the overall amount of money borrowed."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Applico una groupby su \"country_name\" per poter raggruppare i singoli paesi e poi applicare una sum sull'attributo \"loan_amount\" (importo del prestito) in modo da poter capire quanto denaro è stato prestato ad ogni singolo paese :"
   ]
  },
  {
   "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>tot_borrowed</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Afghanistan</td>\n",
       "      <td>6000.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>20579275.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Azerbaijan</td>\n",
       "      <td>6839400.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Belize</td>\n",
       "      <td>63950.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>79</th>\n",
       "      <td>Vanuatu</td>\n",
       "      <td>9250.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>80</th>\n",
       "      <td>Vietnam</td>\n",
       "      <td>19907200.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>81</th>\n",
       "      <td>Yemen</td>\n",
       "      <td>3430100.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>82</th>\n",
       "      <td>Zambia</td>\n",
       "      <td>1620650.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>83</th>\n",
       "      <td>Zimbabwe</td>\n",
       "      <td>4838325.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>84 rows × 2 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "   country_name  tot_borrowed\n",
       "0   Afghanistan        6000.0\n",
       "1       Albania     4307350.0\n",
       "2       Armenia    20579275.0\n",
       "3    Azerbaijan     6839400.0\n",
       "4        Belize       63950.0\n",
       "..          ...           ...\n",
       "79      Vanuatu        9250.0\n",
       "80      Vietnam    19907200.0\n",
       "81        Yemen     3430100.0\n",
       "82       Zambia     1620650.0\n",
       "83     Zimbabwe     4838325.0\n",
       "\n",
       "[84 rows x 2 columns]"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "tot_borrowed_from_each_country = loans_cleaned.groupby('country_name')['loan_amount'].sum().reset_index().rename(columns={\"loan_amount\" : \"tot_borrowed\"})\n",
    "tot_borrowed_from_each_country"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### 6) Like the previous point, but expressed as a percentage of the overall amount lent."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Calcolo l'importo totale dei prestiti erogati :"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [],
   "source": [
    "tot_loans = loans_cleaned['loan_amount'].sum()\n",
    "#tot_loans"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Ora divido l'ammontare prestato al singolo paese per l'importo totale dei prestiti erogati \"tot_loans\":"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "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>tot_borrowed</th>\n",
       "      <th>tot_borrowed_%</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Afghanistan</td>\n",
       "      <td>6000.0</td>\n",
       "      <td>0.000692</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Albania</td>\n",
       "      <td>4307350.0</td>\n",
       "      <td>0.497080</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Armenia</td>\n",
       "      <td>20579275.0</td>\n",
       "      <td>2.374905</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Azerbaijan</td>\n",
       "      <td>6839400.0</td>\n",
       "      <td>0.789286</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Belize</td>\n",
       "      <td>63950.0</td>\n",
       "      <td>0.007380</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>79</th>\n",
       "      <td>Vanuatu</td>\n",
       "      <td>9250.0</td>\n",
       "      <td>0.001067</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>80</th>\n",
       "      <td>Vietnam</td>\n",
       "      <td>19907200.0</td>\n",
       "      <td>2.297345</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>81</th>\n",
       "      <td>Yemen</td>\n",
       "      <td>3430100.0</td>\n",
       "      <td>0.395843</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>82</th>\n",
       "      <td>Zambia</td>\n",
       "      <td>1620650.0</td>\n",
       "      <td>0.187027</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>83</th>\n",
       "      <td>Zimbabwe</td>\n",
       "      <td>4838325.0</td>\n",
       "      <td>0.558356</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>84 rows × 3 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "   country_name  tot_borrowed  tot_borrowed_%\n",
       "0   Afghanistan        6000.0        0.000692\n",
       "1       Albania     4307350.0        0.497080\n",
       "2       Armenia    20579275.0        2.374905\n",
       "3    Azerbaijan     6839400.0        0.789286\n",
       "4        Belize       63950.0        0.007380\n",
       "..          ...           ...             ...\n",
       "79      Vanuatu        9250.0        0.001067\n",
       "80      Vietnam    19907200.0        2.297345\n",
       "81        Yemen     3430100.0        0.395843\n",
       "82       Zambia     1620650.0        0.187027\n",
       "83     Zimbabwe     4838325.0        0.558356\n",
       "\n",
       "[84 rows x 3 columns]"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "tot_borrowed_from_each_country_perc = loans_cleaned.groupby('country_name')['loan_amount'].sum().reset_index().rename(columns={\"loan_amount\" : \"tot_borrowed\"})\n",
    "tot_borrowed_from_each_country_perc['tot_borrowed_%'] = (tot_borrowed_from_each_country_perc['tot_borrowed'] /  tot_loans)*100\n",
    "tot_borrowed_from_each_country_perc"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Creo un dataset unico in modo che contenga tutte le informazioni sui presti erogati ad ogni singolo paese (Numero di prestiti erogati, importo totale e percentuale sul totale) :"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "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>count_loans</th>\n",
       "      <th>tot_borrowed</th>\n",
       "      <th>tot_borrowed_%</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Afghanistan</td>\n",
       "      <td>1</td>\n",
       "      <td>6000.0</td>\n",
       "      <td>0.000692</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Albania</td>\n",
       "      <td>3075</td>\n",
       "      <td>4307350.0</td>\n",
       "      <td>0.497080</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Armenia</td>\n",
       "      <td>12809</td>\n",
       "      <td>20579275.0</td>\n",
       "      <td>2.374905</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Azerbaijan</td>\n",
       "      <td>4026</td>\n",
       "      <td>6839400.0</td>\n",
       "      <td>0.789286</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Belize</td>\n",
       "      <td>97</td>\n",
       "      <td>63950.0</td>\n",
       "      <td>0.007380</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  country_name  count_loans  tot_borrowed  tot_borrowed_%\n",
       "0  Afghanistan            1        6000.0        0.000692\n",
       "1      Albania         3075     4307350.0        0.497080\n",
       "2      Armenia        12809    20579275.0        2.374905\n",
       "3   Azerbaijan         4026     6839400.0        0.789286\n",
       "4       Belize           97       63950.0        0.007380"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "country_summary = pd.concat([loans_for_country, tot_borrowed_from_each_country, tot_borrowed_from_each_country_perc], axis=1)\n",
    "country_summary = country_summary.loc[:,~country_summary.columns.duplicated()]\n",
    "country_summary.head(5)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### 7) Like the three previous points, but split for each year (with respect to disburse time)."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Imposto come indice \"disburse_time\" :"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [],
   "source": [
    "loans_cleaned.index = pd.DatetimeIndex(loans_cleaned.disburse_time)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Effettuo un raggruppamento sia per \"country_name\" sia per anno, attraverso il comando pd.Grouper (indicando come parametri l'attributo su cui effettuare il raggruppamnto, \"disburse_time\", e il parametro \"Y\", ovvero anni) :"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>country_name</th>\n",
       "      <th>year</th>\n",
       "      <th>count_loans_for_year</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Afghanistan</td>\n",
       "      <td>2015</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Albania</td>\n",
       "      <td>2012</td>\n",
       "      <td>332</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Albania</td>\n",
       "      <td>2013</td>\n",
       "      <td>507</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Albania</td>\n",
       "      <td>2014</td>\n",
       "      <td>603</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Albania</td>\n",
       "      <td>2015</td>\n",
       "      <td>638</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>472</th>\n",
       "      <td>Zimbabwe</td>\n",
       "      <td>2013</td>\n",
       "      <td>415</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>473</th>\n",
       "      <td>Zimbabwe</td>\n",
       "      <td>2014</td>\n",
       "      <td>2072</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>474</th>\n",
       "      <td>Zimbabwe</td>\n",
       "      <td>2015</td>\n",
       "      <td>600</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>475</th>\n",
       "      <td>Zimbabwe</td>\n",
       "      <td>2016</td>\n",
       "      <td>808</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>476</th>\n",
       "      <td>Zimbabwe</td>\n",
       "      <td>2017</td>\n",
       "      <td>1078</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>477 rows × 3 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "    country_name  year  count_loans_for_year\n",
       "0    Afghanistan  2015                     1\n",
       "1        Albania  2012                   332\n",
       "2        Albania  2013                   507\n",
       "3        Albania  2014                   603\n",
       "4        Albania  2015                   638\n",
       "..           ...   ...                   ...\n",
       "472     Zimbabwe  2013                   415\n",
       "473     Zimbabwe  2014                  2072\n",
       "474     Zimbabwe  2015                   600\n",
       "475     Zimbabwe  2016                   808\n",
       "476     Zimbabwe  2017                  1078\n",
       "\n",
       "[477 rows x 3 columns]"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "loans_for_year_for_each_country = loans_cleaned.groupby([\"country_name\", pd.Grouper(key= 'disburse_time', freq=\"Y\")])['loan_id'].count().reset_index()[[\"country_name\", \"disburse_time\",\"loan_id\"]].rename(columns={\"loan_id\" : \"count_loans_for_year\", \"disburse_time\": \"year\"})\n",
    "loans_for_year_for_each_country['year'] = loans_for_year_for_each_country['year'].dt.year\n",
    "\n",
    "loans_for_year_for_each_country"
   ]
  },
  {
   "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>country_name</th>\n",
       "      <th>year</th>\n",
       "      <th>tot_borrowed_for_year</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Afghanistan</td>\n",
       "      <td>2015</td>\n",
       "      <td>6000.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Albania</td>\n",
       "      <td>2012</td>\n",
       "      <td>413000.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Albania</td>\n",
       "      <td>2013</td>\n",
       "      <td>786950.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Albania</td>\n",
       "      <td>2014</td>\n",
       "      <td>902550.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Albania</td>\n",
       "      <td>2015</td>\n",
       "      <td>844375.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>472</th>\n",
       "      <td>Zimbabwe</td>\n",
       "      <td>2013</td>\n",
       "      <td>654025.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>473</th>\n",
       "      <td>Zimbabwe</td>\n",
       "      <td>2014</td>\n",
       "      <td>1302775.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>474</th>\n",
       "      <td>Zimbabwe</td>\n",
       "      <td>2015</td>\n",
       "      <td>723625.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>475</th>\n",
       "      <td>Zimbabwe</td>\n",
       "      <td>2016</td>\n",
       "      <td>788600.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>476</th>\n",
       "      <td>Zimbabwe</td>\n",
       "      <td>2017</td>\n",
       "      <td>1187600.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>477 rows × 3 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "    country_name  year  tot_borrowed_for_year\n",
       "0    Afghanistan  2015                 6000.0\n",
       "1        Albania  2012               413000.0\n",
       "2        Albania  2013               786950.0\n",
       "3        Albania  2014               902550.0\n",
       "4        Albania  2015               844375.0\n",
       "..           ...   ...                    ...\n",
       "472     Zimbabwe  2013               654025.0\n",
       "473     Zimbabwe  2014              1302775.0\n",
       "474     Zimbabwe  2015               723625.0\n",
       "475     Zimbabwe  2016               788600.0\n",
       "476     Zimbabwe  2017              1187600.0\n",
       "\n",
       "[477 rows x 3 columns]"
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "overall_borrowed_for_year = loans_cleaned.groupby([\"country_name\", pd.Grouper(key= 'disburse_time', freq=\"Y\")])['loan_amount'].sum().reset_index()[[\"country_name\", \"disburse_time\",\"loan_amount\"]].rename(columns={\"loan_amount\" : \"tot_borrowed_for_year\", \"disburse_time\": \"year\"})\n",
    "overall_borrowed_for_year.year = overall_borrowed_for_year.year.dt.year\n",
    "overall_borrowed_for_year"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "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>2015</td>\n",
       "      <td>0.000692</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Albania</td>\n",
       "      <td>2012</td>\n",
       "      <td>0.047661</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Albania</td>\n",
       "      <td>2013</td>\n",
       "      <td>0.090816</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Albania</td>\n",
       "      <td>2014</td>\n",
       "      <td>0.104157</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Albania</td>\n",
       "      <td>2015</td>\n",
       "      <td>0.097443</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>472</th>\n",
       "      <td>Zimbabwe</td>\n",
       "      <td>2013</td>\n",
       "      <td>0.075476</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>473</th>\n",
       "      <td>Zimbabwe</td>\n",
       "      <td>2014</td>\n",
       "      <td>0.150344</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>474</th>\n",
       "      <td>Zimbabwe</td>\n",
       "      <td>2015</td>\n",
       "      <td>0.083508</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>475</th>\n",
       "      <td>Zimbabwe</td>\n",
       "      <td>2016</td>\n",
       "      <td>0.091007</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>476</th>\n",
       "      <td>Zimbabwe</td>\n",
       "      <td>2017</td>\n",
       "      <td>0.137052</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>477 rows × 3 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "    country_name  year  loan_amount\n",
       "0    Afghanistan  2015     0.000692\n",
       "1        Albania  2012     0.047661\n",
       "2        Albania  2013     0.090816\n",
       "3        Albania  2014     0.104157\n",
       "4        Albania  2015     0.097443\n",
       "..           ...   ...          ...\n",
       "472     Zimbabwe  2013     0.075476\n",
       "473     Zimbabwe  2014     0.150344\n",
       "474     Zimbabwe  2015     0.083508\n",
       "475     Zimbabwe  2016     0.091007\n",
       "476     Zimbabwe  2017     0.137052\n",
       "\n",
       "[477 rows x 3 columns]"
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "overall_loan_borrowed_for_year_perc =loans_cleaned.groupby([\"country_name\", pd.Grouper(key= 'disburse_time', freq=\"Y\")])['loan_amount'].sum().reset_index().rename(columns={\"disburse_time\" : \"year\"})\n",
    "overall_loan_borrowed_for_year_perc['loan_amount'] = (overall_loan_borrowed_for_year_perc['loan_amount']/tot_loans)*100\n",
    "overall_loan_borrowed_for_year_perc.rename(columns={\"loan_amount\" : \"tot_loan_borrowed_for_year_perc\"})\n",
    "overall_loan_borrowed_for_year_perc.year = overall_loan_borrowed_for_year_perc.year.dt.year\n",
    "overall_loan_borrowed_for_year_perc"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Creo un unico dataset in modo tale che contenga tutte le informazioni sui prestiti erogati ai singoli paesi suddivise per anni :"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "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>count_loans_for_year</th>\n",
       "      <th>tot_borrowed_for_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>2015</td>\n",
       "      <td>1</td>\n",
       "      <td>6000.0</td>\n",
       "      <td>0.000692</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Albania</td>\n",
       "      <td>2012</td>\n",
       "      <td>332</td>\n",
       "      <td>413000.0</td>\n",
       "      <td>0.047661</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Albania</td>\n",
       "      <td>2013</td>\n",
       "      <td>507</td>\n",
       "      <td>786950.0</td>\n",
       "      <td>0.090816</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Albania</td>\n",
       "      <td>2014</td>\n",
       "      <td>603</td>\n",
       "      <td>902550.0</td>\n",
       "      <td>0.104157</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Albania</td>\n",
       "      <td>2015</td>\n",
       "      <td>638</td>\n",
       "      <td>844375.0</td>\n",
       "      <td>0.097443</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  country_name  year  count_loans_for_year  tot_borrowed_for_year  loan_amount\n",
       "0  Afghanistan  2015                     1                 6000.0     0.000692\n",
       "1      Albania  2012                   332               413000.0     0.047661\n",
       "2      Albania  2013                   507               786950.0     0.090816\n",
       "3      Albania  2014                   603               902550.0     0.104157\n",
       "4      Albania  2015                   638               844375.0     0.097443"
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "country_summary_year = pd.concat([loans_for_year_for_each_country, overall_borrowed_for_year, overall_loan_borrowed_for_year_perc], axis=1)\n",
    "country_summary_year = country_summary_year.loc[:,~country_summary_year.columns.duplicated()]\n",
    "country_summary_year.head(5)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### 8) For each lender, compute the overall amount of money lent. For each loan that has more than one lender, you must assume that all lenders contributed the same amount.\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Creo un dataset che contenga per ogni prestito, \"loan_id\", il numero di finanziatori, \"lenders\" :"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "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_count</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>84</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>85</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>86</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>88</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>89</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   loan_id  lenders_count\n",
       "0       84              3\n",
       "1       85              2\n",
       "2       86              3\n",
       "3       88              3\n",
       "4       89              4"
      ]
     },
     "execution_count": 19,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "lenders_count= loans_lenders_nor.groupby(\"loan_id\").count().reset_index().rename(columns={\"lenders\" : \"lenders_count\"})\n",
    "lenders_count.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Unisco il daset appena creato, \"lenders_count\", con il dataset \"loans_cleaned\" in modo da visualizzare, per ogni prestito, il numero di finanziatori e il suo importo :"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "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_count</th>\n",
       "      <th>loan_amount</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>358219</td>\n",
       "      <td>74</td>\n",
       "      <td>2300.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>358926</td>\n",
       "      <td>60</td>\n",
       "      <td>1925.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>359566</td>\n",
       "      <td>42</td>\n",
       "      <td>1400.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>360000</td>\n",
       "      <td>90</td>\n",
       "      <td>2875.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>360232</td>\n",
       "      <td>58</td>\n",
       "      <td>1800.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   loan_id  lenders_count  loan_amount\n",
       "0   358219             74       2300.0\n",
       "1   358926             60       1925.0\n",
       "2   359566             42       1400.0\n",
       "3   360000             90       2875.0\n",
       "4   360232             58       1800.0"
      ]
     },
     "execution_count": 20,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "lenders_count_summary = pd.merge(lenders_count, loans_cleaned, on=\"loan_id\")[[\"loan_id\", \"lenders_count\", \"loan_amount\"]]\n",
    "lenders_count_summary.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Per ogni prestito calcolo l'importo prestato da un singolo finanziatore (si suppono che, in caso di pià finanziatori per un singolo prestito, ogni finanziatore abbia contribuito allo stesso modo) :"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "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_count</th>\n",
       "      <th>loan_amount</th>\n",
       "      <th>amount_for_lender</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>358219</td>\n",
       "      <td>74</td>\n",
       "      <td>2300.0</td>\n",
       "      <td>31.081081</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>358926</td>\n",
       "      <td>60</td>\n",
       "      <td>1925.0</td>\n",
       "      <td>32.083333</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>359566</td>\n",
       "      <td>42</td>\n",
       "      <td>1400.0</td>\n",
       "      <td>33.333333</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>360000</td>\n",
       "      <td>90</td>\n",
       "      <td>2875.0</td>\n",
       "      <td>31.944444</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>360232</td>\n",
       "      <td>58</td>\n",
       "      <td>1800.0</td>\n",
       "      <td>31.034483</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   loan_id  lenders_count  loan_amount  amount_for_lender\n",
       "0   358219             74       2300.0          31.081081\n",
       "1   358926             60       1925.0          32.083333\n",
       "2   359566             42       1400.0          33.333333\n",
       "3   360000             90       2875.0          31.944444\n",
       "4   360232             58       1800.0          31.034483"
      ]
     },
     "execution_count": 21,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "lenders_count_summary['amount_for_lender'] = lenders_count_summary['loan_amount'] / lenders_count_summary['lenders_count']\n",
    "lenders_count_summary.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Effettuo una merge tra \"loans_lenders_nor\", che contiene i nomi dei finanziatori per ogni prestito da loro effettuato, e \"lenders_count_summary\" che contiene le informazioni sui singoli prestiti. In questo modo calcolo, per ogni prestito, l'ammontare prestato dal singolo finanziatore :"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "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",
       "      <th>lenders_count</th>\n",
       "      <th>loan_amount</th>\n",
       "      <th>amount_for_lender</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>483693</td>\n",
       "      <td>muc888</td>\n",
       "      <td>40.0</td>\n",
       "      <td>1225.0</td>\n",
       "      <td>30.625</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>483693</td>\n",
       "      <td>sam4326</td>\n",
       "      <td>40.0</td>\n",
       "      <td>1225.0</td>\n",
       "      <td>30.625</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>483693</td>\n",
       "      <td>camaran3922</td>\n",
       "      <td>40.0</td>\n",
       "      <td>1225.0</td>\n",
       "      <td>30.625</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>483693</td>\n",
       "      <td>lachheb1865</td>\n",
       "      <td>40.0</td>\n",
       "      <td>1225.0</td>\n",
       "      <td>30.625</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>483693</td>\n",
       "      <td>rebecca3499</td>\n",
       "      <td>40.0</td>\n",
       "      <td>1225.0</td>\n",
       "      <td>30.625</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   loan_id      lenders  lenders_count  loan_amount  amount_for_lender\n",
       "0   483693       muc888           40.0       1225.0             30.625\n",
       "1   483693      sam4326           40.0       1225.0             30.625\n",
       "2   483693  camaran3922           40.0       1225.0             30.625\n",
       "3   483693  lachheb1865           40.0       1225.0             30.625\n",
       "4   483693  rebecca3499           40.0       1225.0             30.625"
      ]
     },
     "execution_count": 22,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "loans_for_lender = pd.merge(loans_lenders_nor, lenders_count_summary, on=\"loan_id\", how=\"left\")\n",
    "loans_for_lender.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Ora posso calcolare l'importo totale prestato da ogni finanziatore. Effettuo una groupby per finanziatore, \"lenders\", e applico la funzione sum così da poter individuare l'importo totale prestato da ogni singolo finanziatore :"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "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>lenders</th>\n",
       "      <th>amount_for_lender</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>000</td>\n",
       "      <td>1375.029391</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>00000</td>\n",
       "      <td>0.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>0002</td>\n",
       "      <td>677.081981</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>580.113482</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1383794</th>\n",
       "      <td>zzmcfate</td>\n",
       "      <td>48634.955869</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1383795</th>\n",
       "      <td>zzpaghetti9994</td>\n",
       "      <td>51.020408</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1383796</th>\n",
       "      <td>zzrvmf8538</td>\n",
       "      <td>502.129187</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1383797</th>\n",
       "      <td>zzzsai</td>\n",
       "      <td>267.667370</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1383798</th>\n",
       "      <td>zzzworld</td>\n",
       "      <td>27.522936</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>1383799 rows × 2 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                lenders  amount_for_lender\n",
       "0                   000        1375.029391\n",
       "1                 00000           0.000000\n",
       "2                  0002         677.081981\n",
       "3              00mike00          52.631579\n",
       "4        0101craign0101         580.113482\n",
       "...                 ...                ...\n",
       "1383794        zzmcfate       48634.955869\n",
       "1383795  zzpaghetti9994          51.020408\n",
       "1383796      zzrvmf8538         502.129187\n",
       "1383797          zzzsai         267.667370\n",
       "1383798        zzzworld          27.522936\n",
       "\n",
       "[1383799 rows x 2 columns]"
      ]
     },
     "execution_count": 23,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "tot_lent_by_lender = loans_for_lender.groupby(\"lenders\")[\"amount_for_lender\"].sum().to_frame().reset_index()\n",
    "tot_lent_by_lender"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### 9) For each country, compute the difference between the overall amount of money lent and the overall amount of money borrowed. Since the country of the lender is often unknown, 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."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Carico il dataset \"lenders\" : "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "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>permanent_name</th>\n",
       "      <th>display_name</th>\n",
       "      <th>city</th>\n",
       "      <th>state</th>\n",
       "      <th>country_code</th>\n",
       "      <th>member_since</th>\n",
       "      <th>occupation</th>\n",
       "      <th>loan_because</th>\n",
       "      <th>loan_purchase_num</th>\n",
       "      <th>invited_by</th>\n",
       "      <th>num_invited</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>qian3013</td>\n",
       "      <td>Qian</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1461300457</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>reena6733</td>\n",
       "      <td>Reena</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1461300634</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>9.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>mai5982</td>\n",
       "      <td>Mai</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1461300853</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>andrew86079135</td>\n",
       "      <td>Andrew</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1461301091</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>5.0</td>\n",
       "      <td>Peter Tan</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>nguyen6962</td>\n",
       "      <td>Nguyen</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1461301154</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2349169</th>\n",
       "      <td>janet7309</td>\n",
       "      <td>Janet</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1342097163</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2349170</th>\n",
       "      <td>pj4198</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1342097515</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2349171</th>\n",
       "      <td>maria2141</td>\n",
       "      <td>Maria</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>US</td>\n",
       "      <td>1342099723</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2349172</th>\n",
       "      <td>simone9846</td>\n",
       "      <td>Simone</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1342100213</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2349173</th>\n",
       "      <td>laurie1160</td>\n",
       "      <td>Laurie</td>\n",
       "      <td>Timonium</td>\n",
       "      <td>Maryland</td>\n",
       "      <td>US</td>\n",
       "      <td>1342101767</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1.0</td>\n",
       "      <td>Verian</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>2349174 rows × 11 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "         permanent_name display_name      city     state country_code  \\\n",
       "0              qian3013         Qian       NaN       NaN          NaN   \n",
       "1             reena6733        Reena       NaN       NaN          NaN   \n",
       "2               mai5982          Mai       NaN       NaN          NaN   \n",
       "3        andrew86079135       Andrew       NaN       NaN          NaN   \n",
       "4            nguyen6962       Nguyen       NaN       NaN          NaN   \n",
       "...                 ...          ...       ...       ...          ...   \n",
       "2349169       janet7309        Janet       NaN       NaN          NaN   \n",
       "2349170          pj4198          NaN       NaN       NaN          NaN   \n",
       "2349171       maria2141        Maria       NaN       NaN           US   \n",
       "2349172      simone9846       Simone       NaN       NaN          NaN   \n",
       "2349173      laurie1160       Laurie  Timonium  Maryland           US   \n",
       "\n",
       "         member_since occupation loan_because  loan_purchase_num invited_by  \\\n",
       "0          1461300457        NaN          NaN                1.0        NaN   \n",
       "1          1461300634        NaN          NaN                9.0        NaN   \n",
       "2          1461300853        NaN          NaN                NaN        NaN   \n",
       "3          1461301091        NaN          NaN                5.0  Peter Tan   \n",
       "4          1461301154        NaN          NaN                NaN        NaN   \n",
       "...               ...        ...          ...                ...        ...   \n",
       "2349169    1342097163        NaN          NaN                NaN        NaN   \n",
       "2349170    1342097515        NaN          NaN                NaN        NaN   \n",
       "2349171    1342099723        NaN          NaN                2.0        NaN   \n",
       "2349172    1342100213        NaN          NaN                NaN        NaN   \n",
       "2349173    1342101767        NaN          NaN                1.0     Verian   \n",
       "\n",
       "         num_invited  \n",
       "0                  0  \n",
       "1                  0  \n",
       "2                  0  \n",
       "3                  0  \n",
       "4                  0  \n",
       "...              ...  \n",
       "2349169            0  \n",
       "2349170            0  \n",
       "2349171            0  \n",
       "2349172            0  \n",
       "2349173            0  \n",
       "\n",
       "[2349174 rows x 11 columns]"
      ]
     },
     "execution_count": 24,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "lenders= pd.read_csv('D:/UNIVERSITÀ/PROGETTI/FOCS/DATASET/lenders.csv')\n",
    "lenders"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Il dataset \"lenders\" contiene molti valori mancanti nell'attributo \"country_code\", il quale ci servirà per poter calcolare la differenza tra ammontare prestato e preso in prestito per ogni paese. Effettuerò quindi una divisione in due dataset, uno in cui non vi siano valori mancanti in \"country_code\" e uno in cui vi siano solo valori mancanti in \"country_code\". Estrapolata la distribuzione di \"country_code\" dal dataset non contenenete valori mancanti, si andrà a sostituire i valori mancanti in \"country_code\" nel secondo df attraverso la distribuzione trovata. In questo modo si potrà tornare al df di partenza con zero valori mancanti in \"country_name\" . "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Estrapolo il df contenente solo valori mancanti (in \"country_code\") attraverso la funzione isnull(). Utilezzerò df.loc per poter estrarre solo le osservazioni il cui attributo \"country_name\" presenta valori mancanti : "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "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>permanent_name</th>\n",
       "      <th>display_name</th>\n",
       "      <th>city</th>\n",
       "      <th>state</th>\n",
       "      <th>country_code</th>\n",
       "      <th>member_since</th>\n",
       "      <th>occupation</th>\n",
       "      <th>loan_because</th>\n",
       "      <th>loan_purchase_num</th>\n",
       "      <th>invited_by</th>\n",
       "      <th>num_invited</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0</td>\n",
       "      <td>qian3013</td>\n",
       "      <td>Qian</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1461300457</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "      <td>reena6733</td>\n",
       "      <td>Reena</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1461300634</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>9.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2</td>\n",
       "      <td>mai5982</td>\n",
       "      <td>Mai</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1461300853</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   index permanent_name display_name city state country_code  member_since  \\\n",
       "0      0       qian3013         Qian  NaN   NaN          NaN    1461300457   \n",
       "1      1      reena6733        Reena  NaN   NaN          NaN    1461300634   \n",
       "2      2        mai5982          Mai  NaN   NaN          NaN    1461300853   \n",
       "\n",
       "  occupation loan_because  loan_purchase_num invited_by  num_invited  \n",
       "0        NaN          NaN                1.0        NaN            0  \n",
       "1        NaN          NaN                9.0        NaN            0  \n",
       "2        NaN          NaN                NaN        NaN            0  "
      ]
     },
     "execution_count": 25,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "country_code_unknow = lenders.loc[lenders[\"country_code\"].isnull()].reset_index()\n",
    "country_code_unknow.head(3)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Allo stesso modo, estrapolo il df non contenente valori mancanti (in \"country_code\") attraverso la funzione notnull() :"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "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>permanent_name</th>\n",
       "      <th>display_name</th>\n",
       "      <th>city</th>\n",
       "      <th>state</th>\n",
       "      <th>country_code</th>\n",
       "      <th>member_since</th>\n",
       "      <th>occupation</th>\n",
       "      <th>loan_because</th>\n",
       "      <th>loan_purchase_num</th>\n",
       "      <th>invited_by</th>\n",
       "      <th>num_invited</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>16</td>\n",
       "      <td>naresh2074</td>\n",
       "      <td>Naresh</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>US</td>\n",
       "      <td>1461303119</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>3.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>31</td>\n",
       "      <td>christina27976796</td>\n",
       "      <td>Christina</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>US</td>\n",
       "      <td>1461303341</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1.0</td>\n",
       "      <td>Peter Tan</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>37</td>\n",
       "      <td>vikas1098</td>\n",
       "      <td>Vikas</td>\n",
       "      <td>Bengaluru</td>\n",
       "      <td>NaN</td>\n",
       "      <td>IN</td>\n",
       "      <td>1461301846</td>\n",
       "      <td>Software Engineer</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   index     permanent_name display_name       city state country_code  \\\n",
       "0     16         naresh2074       Naresh        NaN   NaN           US   \n",
       "1     31  christina27976796    Christina        NaN   NaN           US   \n",
       "2     37          vikas1098        Vikas  Bengaluru   NaN           IN   \n",
       "\n",
       "   member_since         occupation loan_because  loan_purchase_num invited_by  \\\n",
       "0    1461303119                NaN          NaN                3.0        NaN   \n",
       "1    1461303341                NaN          NaN                1.0  Peter Tan   \n",
       "2    1461301846  Software Engineer          NaN                2.0        NaN   \n",
       "\n",
       "   num_invited  \n",
       "0            0  \n",
       "1            0  \n",
       "2            0  "
      ]
     },
     "execution_count": 26,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "country_code_know = lenders.loc[lenders[\"country_code\"].notnull()].reset_index()\n",
    "country_code_know.head(3)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "0\n"
     ]
    }
   ],
   "source": [
    "# controllo che l'operazione di splittagio del dataset originale sia andata a buon fine'\n",
    "\n",
    "print(len(lenders) - len(country_code_know) - len(country_code_unknow))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Calcolo la lunghezza, ovvero il numero di osservazioni presenti nel df \"country_code_know\", in modo tale da poterla utilizzare nel calcolo della distribuzione dello stesso dataset :"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "890539"
      ]
     },
     "execution_count": 28,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "tot_users_know = len(country_code_know.index)\n",
    "tot_users_know"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Trovo la distribuzione del df \"country_code_know\", calcolando le frequenze relative per ogni paese : "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "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_code</th>\n",
       "      <th>tot_users</th>\n",
       "      <th>%</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>AD</td>\n",
       "      <td>15</td>\n",
       "      <td>0.001684</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>AE</td>\n",
       "      <td>1043</td>\n",
       "      <td>0.117120</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>AF</td>\n",
       "      <td>228</td>\n",
       "      <td>0.025602</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>AG</td>\n",
       "      <td>8</td>\n",
       "      <td>0.000898</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>AI</td>\n",
       "      <td>4</td>\n",
       "      <td>0.000449</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>229</th>\n",
       "      <td>YE</td>\n",
       "      <td>195</td>\n",
       "      <td>0.021897</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>230</th>\n",
       "      <td>YT</td>\n",
       "      <td>2</td>\n",
       "      <td>0.000225</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>231</th>\n",
       "      <td>ZA</td>\n",
       "      <td>1051</td>\n",
       "      <td>0.118018</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>232</th>\n",
       "      <td>ZM</td>\n",
       "      <td>65</td>\n",
       "      <td>0.007299</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>233</th>\n",
       "      <td>ZW</td>\n",
       "      <td>54</td>\n",
       "      <td>0.006064</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>234 rows × 3 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "    country_code  tot_users         %\n",
       "0             AD         15  0.001684\n",
       "1             AE       1043  0.117120\n",
       "2             AF        228  0.025602\n",
       "3             AG          8  0.000898\n",
       "4             AI          4  0.000449\n",
       "..           ...        ...       ...\n",
       "229           YE        195  0.021897\n",
       "230           YT          2  0.000225\n",
       "231           ZA       1051  0.118018\n",
       "232           ZM         65  0.007299\n",
       "233           ZW         54  0.006064\n",
       "\n",
       "[234 rows x 3 columns]"
      ]
     },
     "execution_count": 29,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "country_distr_know = country_code_know[[\"index\", \"country_code\"]].groupby(\"country_code\").count().reset_index().rename(columns = {\"index\":\"tot_users\"})\n",
    "country_distr_know[\"%\"] = country_distr_know[\"tot_users\"]/tot_users_know*100\n",
    "country_distr_know"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Si ricorda che la somma di tutte le probabilità / freq_relative deve essere uguale 1. Per sicurezza, imposterò questa condizione :"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "metadata": {},
   "outputs": [],
   "source": [
    "country_distr_know[\"%\"] /= country_distr_know[\"%\"].sum()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Attraverso la funzione np.random.choice si va a sostituire in modo \"randomico\" (tenendo conto della distribuzione di di \"country_code\" del df \"country_code_know\") i valori mancanti di \"country_code\" nel df \"country_code_unknow\" :"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "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>permanent_name</th>\n",
       "      <th>display_name</th>\n",
       "      <th>city</th>\n",
       "      <th>state</th>\n",
       "      <th>country_code</th>\n",
       "      <th>member_since</th>\n",
       "      <th>occupation</th>\n",
       "      <th>loan_because</th>\n",
       "      <th>loan_purchase_num</th>\n",
       "      <th>invited_by</th>\n",
       "      <th>num_invited</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0</td>\n",
       "      <td>qian3013</td>\n",
       "      <td>Qian</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>GB</td>\n",
       "      <td>1461300457</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "      <td>reena6733</td>\n",
       "      <td>Reena</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>US</td>\n",
       "      <td>1461300634</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>9.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2</td>\n",
       "      <td>mai5982</td>\n",
       "      <td>Mai</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>US</td>\n",
       "      <td>1461300853</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>3</td>\n",
       "      <td>andrew86079135</td>\n",
       "      <td>Andrew</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>US</td>\n",
       "      <td>1461301091</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>5.0</td>\n",
       "      <td>Peter Tan</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>4</td>\n",
       "      <td>nguyen6962</td>\n",
       "      <td>Nguyen</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>US</td>\n",
       "      <td>1461301154</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>5</td>\n",
       "      <td>sirinapa6764</td>\n",
       "      <td>Sirinapa</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NL</td>\n",
       "      <td>1461301496</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>6</td>\n",
       "      <td>rene7585</td>\n",
       "      <td>Rene</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NL</td>\n",
       "      <td>1461301636</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>7</td>\n",
       "      <td>harald2826</td>\n",
       "      <td>Harald</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>US</td>\n",
       "      <td>1461301670</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>8</td>\n",
       "      <td>mehdi2903</td>\n",
       "      <td>Mehdi</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>US</td>\n",
       "      <td>1461301756</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>9</td>\n",
       "      <td>youchan8125</td>\n",
       "      <td>Youchan</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>US</td>\n",
       "      <td>1461301941</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   index  permanent_name display_name city state country_code  member_since  \\\n",
       "0      0        qian3013         Qian  NaN   NaN           GB    1461300457   \n",
       "1      1       reena6733        Reena  NaN   NaN           US    1461300634   \n",
       "2      2         mai5982          Mai  NaN   NaN           US    1461300853   \n",
       "3      3  andrew86079135       Andrew  NaN   NaN           US    1461301091   \n",
       "4      4      nguyen6962       Nguyen  NaN   NaN           US    1461301154   \n",
       "5      5    sirinapa6764     Sirinapa  NaN   NaN           NL    1461301496   \n",
       "6      6        rene7585         Rene  NaN   NaN           NL    1461301636   \n",
       "7      7      harald2826       Harald  NaN   NaN           US    1461301670   \n",
       "8      8       mehdi2903        Mehdi  NaN   NaN           US    1461301756   \n",
       "9      9     youchan8125      Youchan  NaN   NaN           US    1461301941   \n",
       "\n",
       "  occupation loan_because  loan_purchase_num invited_by  num_invited  \n",
       "0        NaN          NaN                1.0        NaN            0  \n",
       "1        NaN          NaN                9.0        NaN            0  \n",
       "2        NaN          NaN                NaN        NaN            0  \n",
       "3        NaN          NaN                5.0  Peter Tan            0  \n",
       "4        NaN          NaN                NaN        NaN            0  \n",
       "5        NaN          NaN                1.0        NaN            0  \n",
       "6        NaN          NaN                2.0        NaN            0  \n",
       "7        NaN          NaN                2.0        NaN            0  \n",
       "8        NaN          NaN                NaN        NaN            0  \n",
       "9        NaN          NaN                1.0        NaN            0  "
      ]
     },
     "execution_count": 31,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "np.random.seed(1234)\n",
    "country_code_unknow[\"country_code\"] = np.random.choice(country_distr_know[\"country_code\"], size=len(country_code_unknow.index), p = country_distr_know[\"%\"])\n",
    "country_code_unknow.head(10)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Ora posso effettuare una merge per ritornare al dataset originale :"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "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>permanent_name</th>\n",
       "      <th>display_name</th>\n",
       "      <th>city</th>\n",
       "      <th>state</th>\n",
       "      <th>country_code</th>\n",
       "      <th>member_since</th>\n",
       "      <th>occupation</th>\n",
       "      <th>loan_because</th>\n",
       "      <th>loan_purchase_num</th>\n",
       "      <th>invited_by</th>\n",
       "      <th>num_invited</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>naresh2074</td>\n",
       "      <td>Naresh</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>US</td>\n",
       "      <td>1461303119</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>3.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>christina27976796</td>\n",
       "      <td>Christina</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>US</td>\n",
       "      <td>1461303341</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1.0</td>\n",
       "      <td>Peter Tan</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>vikas1098</td>\n",
       "      <td>Vikas</td>\n",
       "      <td>Bengaluru</td>\n",
       "      <td>NaN</td>\n",
       "      <td>IN</td>\n",
       "      <td>1461301846</td>\n",
       "      <td>Software Engineer</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>qian1385</td>\n",
       "      <td>Qian</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>US</td>\n",
       "      <td>1461302036</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>xigg8769</td>\n",
       "      <td>Darla</td>\n",
       "      <td>Madison</td>\n",
       "      <td>NaN</td>\n",
       "      <td>US</td>\n",
       "      <td>1461302503</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1458630</th>\n",
       "      <td>todd5695</td>\n",
       "      <td>Todd</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>US</td>\n",
       "      <td>1342101461</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1458631</th>\n",
       "      <td>kate40761039</td>\n",
       "      <td>Kate</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NO</td>\n",
       "      <td>1342096938</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1.0</td>\n",
       "      <td>Irina</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1458632</th>\n",
       "      <td>janet7309</td>\n",
       "      <td>Janet</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>US</td>\n",
       "      <td>1342097163</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1458633</th>\n",
       "      <td>pj4198</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>IT</td>\n",
       "      <td>1342097515</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1458634</th>\n",
       "      <td>simone9846</td>\n",
       "      <td>Simone</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NZ</td>\n",
       "      <td>1342100213</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>2349174 rows × 11 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "            permanent_name display_name       city state country_code  \\\n",
       "0               naresh2074       Naresh        NaN   NaN           US   \n",
       "1        christina27976796    Christina        NaN   NaN           US   \n",
       "2                vikas1098        Vikas  Bengaluru   NaN           IN   \n",
       "3                 qian1385         Qian        NaN   NaN           US   \n",
       "4                 xigg8769        Darla    Madison   NaN           US   \n",
       "...                    ...          ...        ...   ...          ...   \n",
       "1458630           todd5695         Todd        NaN   NaN           US   \n",
       "1458631       kate40761039         Kate        NaN   NaN           NO   \n",
       "1458632          janet7309        Janet        NaN   NaN           US   \n",
       "1458633             pj4198          NaN        NaN   NaN           IT   \n",
       "1458634         simone9846       Simone        NaN   NaN           NZ   \n",
       "\n",
       "         member_since         occupation loan_because  loan_purchase_num  \\\n",
       "0          1461303119                NaN          NaN                3.0   \n",
       "1          1461303341                NaN          NaN                1.0   \n",
       "2          1461301846  Software Engineer          NaN                2.0   \n",
       "3          1461302036                NaN          NaN                2.0   \n",
       "4          1461302503                NaN          NaN                NaN   \n",
       "...               ...                ...          ...                ...   \n",
       "1458630    1342101461                NaN          NaN                NaN   \n",
       "1458631    1342096938                NaN          NaN                1.0   \n",
       "1458632    1342097163                NaN          NaN                NaN   \n",
       "1458633    1342097515                NaN          NaN                NaN   \n",
       "1458634    1342100213                NaN          NaN                NaN   \n",
       "\n",
       "        invited_by  num_invited  \n",
       "0              NaN            0  \n",
       "1        Peter Tan            0  \n",
       "2              NaN            0  \n",
       "3              NaN            0  \n",
       "4              NaN            0  \n",
       "...            ...          ...  \n",
       "1458630        NaN            0  \n",
       "1458631      Irina            0  \n",
       "1458632        NaN            0  \n",
       "1458633        NaN            0  \n",
       "1458634        NaN            0  \n",
       "\n",
       "[2349174 rows x 11 columns]"
      ]
     },
     "execution_count": 32,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "lenders = pd.concat([country_code_know, country_code_unknow]).drop(columns=\"index\")\n",
    "lenders"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Calcolo il totale prestato da ogni singolo paese effettuando una merge tra il dataset \"tot_lent_by_lender\" (contiene le informazioni di quanto prestato da ogni singolo finanziatore, \"lenders\") e il dataset \"lenders\" (contiene le informazioni di ogni finanziatore) : "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "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_code</th>\n",
       "      <th>total_lent_from_the_country</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>AD</td>\n",
       "      <td>6.412811e+03</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>AE</td>\n",
       "      <td>1.569657e+06</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>AF</td>\n",
       "      <td>1.589440e+05</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>AG</td>\n",
       "      <td>1.025686e+03</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>AI</td>\n",
       "      <td>2.647852e+02</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>225</th>\n",
       "      <td>YE</td>\n",
       "      <td>7.541314e+04</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>226</th>\n",
       "      <td>YT</td>\n",
       "      <td>2.970588e+01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>227</th>\n",
       "      <td>ZA</td>\n",
       "      <td>5.668958e+05</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>228</th>\n",
       "      <td>ZM</td>\n",
       "      <td>3.820296e+04</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>229</th>\n",
       "      <td>ZW</td>\n",
       "      <td>1.999256e+04</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>230 rows × 2 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "    country_code  total_lent_from_the_country\n",
       "0             AD                 6.412811e+03\n",
       "1             AE                 1.569657e+06\n",
       "2             AF                 1.589440e+05\n",
       "3             AG                 1.025686e+03\n",
       "4             AI                 2.647852e+02\n",
       "..           ...                          ...\n",
       "225           YE                 7.541314e+04\n",
       "226           YT                 2.970588e+01\n",
       "227           ZA                 5.668958e+05\n",
       "228           ZM                 3.820296e+04\n",
       "229           ZW                 1.999256e+04\n",
       "\n",
       "[230 rows x 2 columns]"
      ]
     },
     "execution_count": 33,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "total_lent_from_the_country = pd.merge(tot_lent_by_lender, lenders[[\"permanent_name\", \"country_code\"]], left_on=\"lenders\", right_on=\"permanent_name\").drop(columns=\"permanent_name\").groupby(\"country_code\")[\"amount_for_lender\"].sum().to_frame().rename(columns={'amount_for_lender' : 'total_lent_from_the_country'}).reset_index()\n",
    "total_lent_from_the_country"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Calcolo l'ammontare preso in prestito da ogni paese effettuando una groupby su \"country_code\" del dataset \"loans_cleaned\" (contiene le informazioni sui prestiti concessi ad ogni paese) e applicando una sum() all'attributo \"loan_amount\" :"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "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_code</th>\n",
       "      <th>total_borrowed_from_the_country</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>AF</td>\n",
       "      <td>6000.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>AL</td>\n",
       "      <td>4307350.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>AM</td>\n",
       "      <td>20579275.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>AZ</td>\n",
       "      <td>6839400.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>BF</td>\n",
       "      <td>3989650.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>78</th>\n",
       "      <td>XK</td>\n",
       "      <td>3083025.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>79</th>\n",
       "      <td>YE</td>\n",
       "      <td>3430100.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>80</th>\n",
       "      <td>ZA</td>\n",
       "      <td>250250.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>81</th>\n",
       "      <td>ZM</td>\n",
       "      <td>1620650.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>82</th>\n",
       "      <td>ZW</td>\n",
       "      <td>4838325.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>83 rows × 2 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "   country_code  total_borrowed_from_the_country\n",
       "0            AF                           6000.0\n",
       "1            AL                        4307350.0\n",
       "2            AM                       20579275.0\n",
       "3            AZ                        6839400.0\n",
       "4            BF                        3989650.0\n",
       "..          ...                              ...\n",
       "78           XK                        3083025.0\n",
       "79           YE                        3430100.0\n",
       "80           ZA                         250250.0\n",
       "81           ZM                        1620650.0\n",
       "82           ZW                        4838325.0\n",
       "\n",
       "[83 rows x 2 columns]"
      ]
     },
     "execution_count": 34,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "total_borrowed_from_the_country =  loans_cleaned.groupby('country_code', as_index=False)[\"loan_amount\"].sum().rename(columns={\"loan_amount\" : \"total_borrowed_from_the_country\"})\n",
    "total_borrowed_from_the_country"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Inserisco le informazioni ottenuto in un unico dataset: "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "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_code</th>\n",
       "      <th>total_lent_from_the_country</th>\n",
       "      <th>total_borrowed_from_the_country</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>AF</td>\n",
       "      <td>158943.967927</td>\n",
       "      <td>6000.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>AL</td>\n",
       "      <td>20913.605196</td>\n",
       "      <td>4307350.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>AM</td>\n",
       "      <td>33428.316161</td>\n",
       "      <td>20579275.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>AZ</td>\n",
       "      <td>11207.320499</td>\n",
       "      <td>6839400.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>BF</td>\n",
       "      <td>7985.769926</td>\n",
       "      <td>3989650.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>78</th>\n",
       "      <td>XK</td>\n",
       "      <td>3639.266764</td>\n",
       "      <td>3083025.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>79</th>\n",
       "      <td>YE</td>\n",
       "      <td>75413.144611</td>\n",
       "      <td>3430100.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>80</th>\n",
       "      <td>ZA</td>\n",
       "      <td>566895.780178</td>\n",
       "      <td>250250.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>81</th>\n",
       "      <td>ZM</td>\n",
       "      <td>38202.958960</td>\n",
       "      <td>1620650.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>82</th>\n",
       "      <td>ZW</td>\n",
       "      <td>19992.562333</td>\n",
       "      <td>4838325.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>83 rows × 3 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "   country_code  total_lent_from_the_country  total_borrowed_from_the_country\n",
       "0            AF                158943.967927                           6000.0\n",
       "1            AL                 20913.605196                        4307350.0\n",
       "2            AM                 33428.316161                       20579275.0\n",
       "3            AZ                 11207.320499                        6839400.0\n",
       "4            BF                  7985.769926                        3989650.0\n",
       "..          ...                          ...                              ...\n",
       "78           XK                  3639.266764                        3083025.0\n",
       "79           YE                 75413.144611                        3430100.0\n",
       "80           ZA                566895.780178                         250250.0\n",
       "81           ZM                 38202.958960                        1620650.0\n",
       "82           ZW                 19992.562333                        4838325.0\n",
       "\n",
       "[83 rows x 3 columns]"
      ]
     },
     "execution_count": 35,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "summary_of_loans_by_country = pd.merge(total_lent_from_the_country, total_borrowed_from_the_country, on = \"country_code\")\n",
    "summary_of_loans_by_country"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Calcolo la differenza tra ammontare prestato e preso a prestito per ogni paese (\"net_loans\") :"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "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_code</th>\n",
       "      <th>total_lent_from_the_country</th>\n",
       "      <th>total_borrowed_from_the_country</th>\n",
       "      <th>net_loans</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>AF</td>\n",
       "      <td>158943.967927</td>\n",
       "      <td>6000.0</td>\n",
       "      <td>1.529440e+05</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>AL</td>\n",
       "      <td>20913.605196</td>\n",
       "      <td>4307350.0</td>\n",
       "      <td>-4.286436e+06</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>AM</td>\n",
       "      <td>33428.316161</td>\n",
       "      <td>20579275.0</td>\n",
       "      <td>-2.054585e+07</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>AZ</td>\n",
       "      <td>11207.320499</td>\n",
       "      <td>6839400.0</td>\n",
       "      <td>-6.828193e+06</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>BF</td>\n",
       "      <td>7985.769926</td>\n",
       "      <td>3989650.0</td>\n",
       "      <td>-3.981664e+06</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>78</th>\n",
       "      <td>XK</td>\n",
       "      <td>3639.266764</td>\n",
       "      <td>3083025.0</td>\n",
       "      <td>-3.079386e+06</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>79</th>\n",
       "      <td>YE</td>\n",
       "      <td>75413.144611</td>\n",
       "      <td>3430100.0</td>\n",
       "      <td>-3.354687e+06</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>80</th>\n",
       "      <td>ZA</td>\n",
       "      <td>566895.780178</td>\n",
       "      <td>250250.0</td>\n",
       "      <td>3.166458e+05</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>81</th>\n",
       "      <td>ZM</td>\n",
       "      <td>38202.958960</td>\n",
       "      <td>1620650.0</td>\n",
       "      <td>-1.582447e+06</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>82</th>\n",
       "      <td>ZW</td>\n",
       "      <td>19992.562333</td>\n",
       "      <td>4838325.0</td>\n",
       "      <td>-4.818332e+06</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>83 rows × 4 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "   country_code  total_lent_from_the_country  total_borrowed_from_the_country  \\\n",
       "0            AF                158943.967927                           6000.0   \n",
       "1            AL                 20913.605196                        4307350.0   \n",
       "2            AM                 33428.316161                       20579275.0   \n",
       "3            AZ                 11207.320499                        6839400.0   \n",
       "4            BF                  7985.769926                        3989650.0   \n",
       "..          ...                          ...                              ...   \n",
       "78           XK                  3639.266764                        3083025.0   \n",
       "79           YE                 75413.144611                        3430100.0   \n",
       "80           ZA                566895.780178                         250250.0   \n",
       "81           ZM                 38202.958960                        1620650.0   \n",
       "82           ZW                 19992.562333                        4838325.0   \n",
       "\n",
       "       net_loans  \n",
       "0   1.529440e+05  \n",
       "1  -4.286436e+06  \n",
       "2  -2.054585e+07  \n",
       "3  -6.828193e+06  \n",
       "4  -3.981664e+06  \n",
       "..           ...  \n",
       "78 -3.079386e+06  \n",
       "79 -3.354687e+06  \n",
       "80  3.166458e+05  \n",
       "81 -1.582447e+06  \n",
       "82 -4.818332e+06  \n",
       "\n",
       "[83 rows x 4 columns]"
      ]
     },
     "execution_count": 36,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "summary_of_loans_by_country[\"net_loans\"] = summary_of_loans_by_country[\"total_lent_from_the_country\"] - summary_of_loans_by_country[\"total_borrowed_from_the_country\"]\n",
    "summary_of_loans_by_country"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### 10) Which country has the highest ratio between the difference computed at the previous point and the population?"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Carico il dataset \"country_stats\" contentente le infromazioni sulla popolazione di ogni paese :"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "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>country_code</th>\n",
       "      <th>country_code3</th>\n",
       "      <th>continent</th>\n",
       "      <th>region</th>\n",
       "      <th>population</th>\n",
       "      <th>population_below_poverty_line</th>\n",
       "      <th>hdi</th>\n",
       "      <th>life_expectancy</th>\n",
       "      <th>expected_years_of_schooling</th>\n",
       "      <th>mean_years_of_schooling</th>\n",
       "      <th>gni</th>\n",
       "      <th>kiva_country_name</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>India</td>\n",
       "      <td>IN</td>\n",
       "      <td>IND</td>\n",
       "      <td>Asia</td>\n",
       "      <td>Southern Asia</td>\n",
       "      <td>1339180127</td>\n",
       "      <td>21.9</td>\n",
       "      <td>0.623559</td>\n",
       "      <td>68.322</td>\n",
       "      <td>11.696590</td>\n",
       "      <td>6.298834</td>\n",
       "      <td>5663.474799</td>\n",
       "      <td>India</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Nigeria</td>\n",
       "      <td>NG</td>\n",
       "      <td>NGA</td>\n",
       "      <td>Africa</td>\n",
       "      <td>Western Africa</td>\n",
       "      <td>190886311</td>\n",
       "      <td>70.0</td>\n",
       "      <td>0.527105</td>\n",
       "      <td>53.057</td>\n",
       "      <td>9.970482</td>\n",
       "      <td>6.000000</td>\n",
       "      <td>5442.901264</td>\n",
       "      <td>Nigeria</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Mexico</td>\n",
       "      <td>MX</td>\n",
       "      <td>MEX</td>\n",
       "      <td>Americas</td>\n",
       "      <td>Central America</td>\n",
       "      <td>129163276</td>\n",
       "      <td>46.2</td>\n",
       "      <td>0.761683</td>\n",
       "      <td>76.972</td>\n",
       "      <td>13.299090</td>\n",
       "      <td>8.554985</td>\n",
       "      <td>16383.106680</td>\n",
       "      <td>Mexico</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  country_name country_code country_code3 continent           region  \\\n",
       "0        India           IN           IND      Asia    Southern Asia   \n",
       "1      Nigeria           NG           NGA    Africa   Western Africa   \n",
       "2       Mexico           MX           MEX  Americas  Central America   \n",
       "\n",
       "   population  population_below_poverty_line       hdi  life_expectancy  \\\n",
       "0  1339180127                           21.9  0.623559           68.322   \n",
       "1   190886311                           70.0  0.527105           53.057   \n",
       "2   129163276                           46.2  0.761683           76.972   \n",
       "\n",
       "   expected_years_of_schooling  mean_years_of_schooling           gni  \\\n",
       "0                    11.696590                 6.298834   5663.474799   \n",
       "1                     9.970482                 6.000000   5442.901264   \n",
       "2                    13.299090                 8.554985  16383.106680   \n",
       "\n",
       "  kiva_country_name  \n",
       "0             India  \n",
       "1           Nigeria  \n",
       "2            Mexico  "
      ]
     },
     "execution_count": 37,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "country_stat = pd.read_csv('D:/UNIVERSITÀ/PROGETTI/FOCS/DATASET/country_stats.csv')\n",
    "country_stat.head(3)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Unisco le informazioni sulla popolazione del singolo paese del dataset \"country_stat\" con le informazioni sui presti (erogati e ottenuti) per ogni paese del dataset \"summary_of_loans_by_country\" : "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "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_code</th>\n",
       "      <th>population</th>\n",
       "      <th>total_lent_from_the_country</th>\n",
       "      <th>total_borrowed_from_the_country</th>\n",
       "      <th>net_loans</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>IN</td>\n",
       "      <td>1339180127</td>\n",
       "      <td>2.012132e+06</td>\n",
       "      <td>8676050.0</td>\n",
       "      <td>-6.663918e+06</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>NG</td>\n",
       "      <td>190886311</td>\n",
       "      <td>2.013368e+05</td>\n",
       "      <td>1542950.0</td>\n",
       "      <td>-1.341613e+06</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>MX</td>\n",
       "      <td>129163276</td>\n",
       "      <td>1.340695e+06</td>\n",
       "      <td>15656225.0</td>\n",
       "      <td>-1.431553e+07</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>PK</td>\n",
       "      <td>197015955</td>\n",
       "      <td>2.441383e+05</td>\n",
       "      <td>19972925.0</td>\n",
       "      <td>-1.972879e+07</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>CD</td>\n",
       "      <td>81339988</td>\n",
       "      <td>7.547873e+03</td>\n",
       "      <td>16363725.0</td>\n",
       "      <td>-1.635618e+07</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>75</th>\n",
       "      <td>SB</td>\n",
       "      <td>611343</td>\n",
       "      <td>5.175210e+03</td>\n",
       "      <td>815575.0</td>\n",
       "      <td>-8.103998e+05</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>76</th>\n",
       "      <td>SO</td>\n",
       "      <td>14742523</td>\n",
       "      <td>9.486359e+02</td>\n",
       "      <td>49950.0</td>\n",
       "      <td>-4.900136e+04</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>77</th>\n",
       "      <td>WS</td>\n",
       "      <td>196440</td>\n",
       "      <td>1.948042e+03</td>\n",
       "      <td>9281475.0</td>\n",
       "      <td>-9.279527e+06</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>78</th>\n",
       "      <td>PS</td>\n",
       "      <td>4920724</td>\n",
       "      <td>6.724412e+04</td>\n",
       "      <td>19285750.0</td>\n",
       "      <td>-1.921851e+07</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>79</th>\n",
       "      <td>XK</td>\n",
       "      <td>1895250</td>\n",
       "      <td>3.639267e+03</td>\n",
       "      <td>3083025.0</td>\n",
       "      <td>-3.079386e+06</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>80 rows × 5 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "   country_code  population  total_lent_from_the_country  \\\n",
       "0            IN  1339180127                 2.012132e+06   \n",
       "1            NG   190886311                 2.013368e+05   \n",
       "2            MX   129163276                 1.340695e+06   \n",
       "3            PK   197015955                 2.441383e+05   \n",
       "4            CD    81339988                 7.547873e+03   \n",
       "..          ...         ...                          ...   \n",
       "75           SB      611343                 5.175210e+03   \n",
       "76           SO    14742523                 9.486359e+02   \n",
       "77           WS      196440                 1.948042e+03   \n",
       "78           PS     4920724                 6.724412e+04   \n",
       "79           XK     1895250                 3.639267e+03   \n",
       "\n",
       "    total_borrowed_from_the_country     net_loans  \n",
       "0                         8676050.0 -6.663918e+06  \n",
       "1                         1542950.0 -1.341613e+06  \n",
       "2                        15656225.0 -1.431553e+07  \n",
       "3                        19972925.0 -1.972879e+07  \n",
       "4                        16363725.0 -1.635618e+07  \n",
       "..                              ...           ...  \n",
       "75                         815575.0 -8.103998e+05  \n",
       "76                          49950.0 -4.900136e+04  \n",
       "77                        9281475.0 -9.279527e+06  \n",
       "78                       19285750.0 -1.921851e+07  \n",
       "79                        3083025.0 -3.079386e+06  \n",
       "\n",
       "[80 rows x 5 columns]"
      ]
     },
     "execution_count": 38,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "country_loan_stat = pd.merge(country_stat [['country_code', 'population']], summary_of_loans_by_country, on ='country_code')\n",
    "country_loan_stat"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Calcolo il rapporto tra i prestiti netti (\"net_loans\") e popolazione (\"population) per pgni singolo paese :"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 39,
   "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_code</th>\n",
       "      <th>population</th>\n",
       "      <th>total_lent_from_the_country</th>\n",
       "      <th>total_borrowed_from_the_country</th>\n",
       "      <th>net_loans</th>\n",
       "      <th>net_loans/pop</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>IN</td>\n",
       "      <td>1339180127</td>\n",
       "      <td>2.012132e+06</td>\n",
       "      <td>8676050.0</td>\n",
       "      <td>-6.663918e+06</td>\n",
       "      <td>-0.004976</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>NG</td>\n",
       "      <td>190886311</td>\n",
       "      <td>2.013368e+05</td>\n",
       "      <td>1542950.0</td>\n",
       "      <td>-1.341613e+06</td>\n",
       "      <td>-0.007028</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>MX</td>\n",
       "      <td>129163276</td>\n",
       "      <td>1.340695e+06</td>\n",
       "      <td>15656225.0</td>\n",
       "      <td>-1.431553e+07</td>\n",
       "      <td>-0.110833</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>PK</td>\n",
       "      <td>197015955</td>\n",
       "      <td>2.441383e+05</td>\n",
       "      <td>19972925.0</td>\n",
       "      <td>-1.972879e+07</td>\n",
       "      <td>-0.100138</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>CD</td>\n",
       "      <td>81339988</td>\n",
       "      <td>7.547873e+03</td>\n",
       "      <td>16363725.0</td>\n",
       "      <td>-1.635618e+07</td>\n",
       "      <td>-0.201084</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>75</th>\n",
       "      <td>SB</td>\n",
       "      <td>611343</td>\n",
       "      <td>5.175210e+03</td>\n",
       "      <td>815575.0</td>\n",
       "      <td>-8.103998e+05</td>\n",
       "      <td>-1.325606</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>76</th>\n",
       "      <td>SO</td>\n",
       "      <td>14742523</td>\n",
       "      <td>9.486359e+02</td>\n",
       "      <td>49950.0</td>\n",
       "      <td>-4.900136e+04</td>\n",
       "      <td>-0.003324</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>77</th>\n",
       "      <td>WS</td>\n",
       "      <td>196440</td>\n",
       "      <td>1.948042e+03</td>\n",
       "      <td>9281475.0</td>\n",
       "      <td>-9.279527e+06</td>\n",
       "      <td>-47.238480</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>78</th>\n",
       "      <td>PS</td>\n",
       "      <td>4920724</td>\n",
       "      <td>6.724412e+04</td>\n",
       "      <td>19285750.0</td>\n",
       "      <td>-1.921851e+07</td>\n",
       "      <td>-3.905626</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>79</th>\n",
       "      <td>XK</td>\n",
       "      <td>1895250</td>\n",
       "      <td>3.639267e+03</td>\n",
       "      <td>3083025.0</td>\n",
       "      <td>-3.079386e+06</td>\n",
       "      <td>-1.624791</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>80 rows × 6 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "   country_code  population  total_lent_from_the_country  \\\n",
       "0            IN  1339180127                 2.012132e+06   \n",
       "1            NG   190886311                 2.013368e+05   \n",
       "2            MX   129163276                 1.340695e+06   \n",
       "3            PK   197015955                 2.441383e+05   \n",
       "4            CD    81339988                 7.547873e+03   \n",
       "..          ...         ...                          ...   \n",
       "75           SB      611343                 5.175210e+03   \n",
       "76           SO    14742523                 9.486359e+02   \n",
       "77           WS      196440                 1.948042e+03   \n",
       "78           PS     4920724                 6.724412e+04   \n",
       "79           XK     1895250                 3.639267e+03   \n",
       "\n",
       "    total_borrowed_from_the_country     net_loans  net_loans/pop  \n",
       "0                         8676050.0 -6.663918e+06      -0.004976  \n",
       "1                         1542950.0 -1.341613e+06      -0.007028  \n",
       "2                        15656225.0 -1.431553e+07      -0.110833  \n",
       "3                        19972925.0 -1.972879e+07      -0.100138  \n",
       "4                        16363725.0 -1.635618e+07      -0.201084  \n",
       "..                              ...           ...            ...  \n",
       "75                         815575.0 -8.103998e+05      -1.325606  \n",
       "76                          49950.0 -4.900136e+04      -0.003324  \n",
       "77                        9281475.0 -9.279527e+06     -47.238480  \n",
       "78                       19285750.0 -1.921851e+07      -3.905626  \n",
       "79                        3083025.0 -3.079386e+06      -1.624791  \n",
       "\n",
       "[80 rows x 6 columns]"
      ]
     },
     "execution_count": 39,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "country_loan_stat[\"net_loans/pop\"] = country_loan_stat[\"net_loans\"]/country_loan_stat[\"population\"]\n",
    "country_loan_stat"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Estraggo il paese con il rapporto \"net_loans/pop\" più alto :"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 40,
   "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_code</th>\n",
       "      <th>population</th>\n",
       "      <th>total_lent_from_the_country</th>\n",
       "      <th>total_borrowed_from_the_country</th>\n",
       "      <th>net_loans</th>\n",
       "      <th>net_loans/pop</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>US</td>\n",
       "      <td>324459463</td>\n",
       "      <td>5.188974e+08</td>\n",
       "      <td>27809375.0</td>\n",
       "      <td>4.910881e+08</td>\n",
       "      <td>1.513557</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  country_code  population  total_lent_from_the_country  \\\n",
       "5           US   324459463                 5.188974e+08   \n",
       "\n",
       "   total_borrowed_from_the_country     net_loans  net_loans/pop  \n",
       "5                       27809375.0  4.910881e+08       1.513557  "
      ]
     },
     "execution_count": 40,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "country_loan_stat[country_loan_stat['net_loans/pop'] == country_loan_stat['net_loans/pop'].max()]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### 11)  Which country has the highest ratio between the difference computed at point 9 and the population that is not below the poverty line?"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Prefersico tener conto solo delle osservazioni con un effettivo valore nell'attributo \"population_below_poverty_line\" :"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 41,
   "metadata": {},
   "outputs": [],
   "source": [
    "country_stat_know = country_stat[country_stat[\"population_below_poverty_line\"].notnull() == True]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Calcolo la popolazione al di sopra della soglia di povertà"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 42,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "<ipython-input-42-c965275f7f00>: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: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  country_stat_know[\"population_above_poverty_line\"] = (country_stat_know[\"population\"] -(country_stat_know[\"population\"]*country_stat_know[\"population_below_poverty_line\"]/100))\n"
     ]
    },
    {
     "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>country_code</th>\n",
       "      <th>country_code3</th>\n",
       "      <th>continent</th>\n",
       "      <th>region</th>\n",
       "      <th>population</th>\n",
       "      <th>population_below_poverty_line</th>\n",
       "      <th>hdi</th>\n",
       "      <th>life_expectancy</th>\n",
       "      <th>expected_years_of_schooling</th>\n",
       "      <th>mean_years_of_schooling</th>\n",
       "      <th>gni</th>\n",
       "      <th>kiva_country_name</th>\n",
       "      <th>population_above_poverty_line</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>India</td>\n",
       "      <td>IN</td>\n",
       "      <td>IND</td>\n",
       "      <td>Asia</td>\n",
       "      <td>Southern Asia</td>\n",
       "      <td>1339180127</td>\n",
       "      <td>21.9</td>\n",
       "      <td>0.623559</td>\n",
       "      <td>68.322</td>\n",
       "      <td>11.696590</td>\n",
       "      <td>6.298834</td>\n",
       "      <td>5663.474799</td>\n",
       "      <td>India</td>\n",
       "      <td>1.045900e+09</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Nigeria</td>\n",
       "      <td>NG</td>\n",
       "      <td>NGA</td>\n",
       "      <td>Africa</td>\n",
       "      <td>Western Africa</td>\n",
       "      <td>190886311</td>\n",
       "      <td>70.0</td>\n",
       "      <td>0.527105</td>\n",
       "      <td>53.057</td>\n",
       "      <td>9.970482</td>\n",
       "      <td>6.000000</td>\n",
       "      <td>5442.901264</td>\n",
       "      <td>Nigeria</td>\n",
       "      <td>5.726589e+07</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Mexico</td>\n",
       "      <td>MX</td>\n",
       "      <td>MEX</td>\n",
       "      <td>Americas</td>\n",
       "      <td>Central America</td>\n",
       "      <td>129163276</td>\n",
       "      <td>46.2</td>\n",
       "      <td>0.761683</td>\n",
       "      <td>76.972</td>\n",
       "      <td>13.299090</td>\n",
       "      <td>8.554985</td>\n",
       "      <td>16383.106680</td>\n",
       "      <td>Mexico</td>\n",
       "      <td>6.948984e+07</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  country_name country_code country_code3 continent           region  \\\n",
       "0        India           IN           IND      Asia    Southern Asia   \n",
       "1      Nigeria           NG           NGA    Africa   Western Africa   \n",
       "2       Mexico           MX           MEX  Americas  Central America   \n",
       "\n",
       "   population  population_below_poverty_line       hdi  life_expectancy  \\\n",
       "0  1339180127                           21.9  0.623559           68.322   \n",
       "1   190886311                           70.0  0.527105           53.057   \n",
       "2   129163276                           46.2  0.761683           76.972   \n",
       "\n",
       "   expected_years_of_schooling  mean_years_of_schooling           gni  \\\n",
       "0                    11.696590                 6.298834   5663.474799   \n",
       "1                     9.970482                 6.000000   5442.901264   \n",
       "2                    13.299090                 8.554985  16383.106680   \n",
       "\n",
       "  kiva_country_name  population_above_poverty_line  \n",
       "0             India                   1.045900e+09  \n",
       "1           Nigeria                   5.726589e+07  \n",
       "2            Mexico                   6.948984e+07  "
      ]
     },
     "execution_count": 42,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "country_stat_know[\"population_above_poverty_line\"] = (country_stat_know[\"population\"] -(country_stat_know[\"population\"]*country_stat_know[\"population_below_poverty_line\"]/100))\n",
    "country_stat_know.head(3)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Aggiungo la nuova informazione ottenuta, ovvero l'attributo \"population_above_poverty_line\", al dataset \"country_loan_stat\" :"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 43,
   "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_code</th>\n",
       "      <th>population</th>\n",
       "      <th>total_lent_from_the_country</th>\n",
       "      <th>total_borrowed_from_the_country</th>\n",
       "      <th>net_loans</th>\n",
       "      <th>net_loans/pop</th>\n",
       "      <th>population_above_poverty_line</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>IN</td>\n",
       "      <td>1339180127</td>\n",
       "      <td>2.012132e+06</td>\n",
       "      <td>8676050.0</td>\n",
       "      <td>-6.663918e+06</td>\n",
       "      <td>-0.004976</td>\n",
       "      <td>1.045900e+09</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>NG</td>\n",
       "      <td>190886311</td>\n",
       "      <td>2.013368e+05</td>\n",
       "      <td>1542950.0</td>\n",
       "      <td>-1.341613e+06</td>\n",
       "      <td>-0.007028</td>\n",
       "      <td>5.726589e+07</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>MX</td>\n",
       "      <td>129163276</td>\n",
       "      <td>1.340695e+06</td>\n",
       "      <td>15656225.0</td>\n",
       "      <td>-1.431553e+07</td>\n",
       "      <td>-0.110833</td>\n",
       "      <td>6.948984e+07</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  country_code  population  total_lent_from_the_country  \\\n",
       "0           IN  1339180127                 2.012132e+06   \n",
       "1           NG   190886311                 2.013368e+05   \n",
       "2           MX   129163276                 1.340695e+06   \n",
       "\n",
       "   total_borrowed_from_the_country     net_loans  net_loans/pop  \\\n",
       "0                        8676050.0 -6.663918e+06      -0.004976   \n",
       "1                        1542950.0 -1.341613e+06      -0.007028   \n",
       "2                       15656225.0 -1.431553e+07      -0.110833   \n",
       "\n",
       "   population_above_poverty_line  \n",
       "0                   1.045900e+09  \n",
       "1                   5.726589e+07  \n",
       "2                   6.948984e+07  "
      ]
     },
     "execution_count": 43,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "country_loan_stat  = pd.merge(country_loan_stat, country_stat_know[[\"country_code\", \"population_above_poverty_line\"]], on=\"country_code\")\n",
    "country_loan_stat.head(3)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Calcolo il rapporto tra net_loans e population_above_poverty_line :"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 44,
   "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_code</th>\n",
       "      <th>population</th>\n",
       "      <th>total_lent_from_the_country</th>\n",
       "      <th>total_borrowed_from_the_country</th>\n",
       "      <th>net_loans</th>\n",
       "      <th>net_loans/pop</th>\n",
       "      <th>population_above_poverty_line</th>\n",
       "      <th>netloans_population_above_poverty_ratio</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>IN</td>\n",
       "      <td>1339180127</td>\n",
       "      <td>2.012132e+06</td>\n",
       "      <td>8676050.0</td>\n",
       "      <td>-6.663918e+06</td>\n",
       "      <td>-0.004976</td>\n",
       "      <td>1.045900e+09</td>\n",
       "      <td>-0.006371</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>NG</td>\n",
       "      <td>190886311</td>\n",
       "      <td>2.013368e+05</td>\n",
       "      <td>1542950.0</td>\n",
       "      <td>-1.341613e+06</td>\n",
       "      <td>-0.007028</td>\n",
       "      <td>5.726589e+07</td>\n",
       "      <td>-0.023428</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>MX</td>\n",
       "      <td>129163276</td>\n",
       "      <td>1.340695e+06</td>\n",
       "      <td>15656225.0</td>\n",
       "      <td>-1.431553e+07</td>\n",
       "      <td>-0.110833</td>\n",
       "      <td>6.948984e+07</td>\n",
       "      <td>-0.206009</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  country_code  population  total_lent_from_the_country  \\\n",
       "0           IN  1339180127                 2.012132e+06   \n",
       "1           NG   190886311                 2.013368e+05   \n",
       "2           MX   129163276                 1.340695e+06   \n",
       "\n",
       "   total_borrowed_from_the_country     net_loans  net_loans/pop  \\\n",
       "0                        8676050.0 -6.663918e+06      -0.004976   \n",
       "1                        1542950.0 -1.341613e+06      -0.007028   \n",
       "2                       15656225.0 -1.431553e+07      -0.110833   \n",
       "\n",
       "   population_above_poverty_line  netloans_population_above_poverty_ratio  \n",
       "0                   1.045900e+09                                -0.006371  \n",
       "1                   5.726589e+07                                -0.023428  \n",
       "2                   6.948984e+07                                -0.206009  "
      ]
     },
     "execution_count": 44,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "country_loan_stat['netloans_population_above_poverty_ratio'] = country_loan_stat[\"net_loans\"]/country_loan_stat[\"population_above_poverty_line\"]\n",
    "country_loan_stat.head(3)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Trovo lo stato con il rapporto net_loans e population_above_poverty_line maggiore : "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 45,
   "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_code</th>\n",
       "      <th>population</th>\n",
       "      <th>total_lent_from_the_country</th>\n",
       "      <th>total_borrowed_from_the_country</th>\n",
       "      <th>net_loans</th>\n",
       "      <th>net_loans/pop</th>\n",
       "      <th>population_above_poverty_line</th>\n",
       "      <th>netloans_population_above_poverty_ratio</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>US</td>\n",
       "      <td>324459463</td>\n",
       "      <td>5.188974e+08</td>\n",
       "      <td>27809375.0</td>\n",
       "      <td>4.910881e+08</td>\n",
       "      <td>1.513557</td>\n",
       "      <td>2.754661e+08</td>\n",
       "      <td>1.782753</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  country_code  population  total_lent_from_the_country  \\\n",
       "5           US   324459463                 5.188974e+08   \n",
       "\n",
       "   total_borrowed_from_the_country     net_loans  net_loans/pop  \\\n",
       "5                       27809375.0  4.910881e+08       1.513557   \n",
       "\n",
       "   population_above_poverty_line  netloans_population_above_poverty_ratio  \n",
       "5                   2.754661e+08                                 1.782753  "
      ]
     },
     "execution_count": 45,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "country_loan_stat[country_loan_stat['netloans_population_above_poverty_ratio']==country_loan_stat['netloans_population_above_poverty_ratio'].max()]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### 12)  For each year, compute the total amount of loans. Each loan that has planned expiration time and disburse time in different \n",
    "\n",
    "#### years must have its amount distributed proportionally to the number of days in each year. For example, a loan with disburse time \n",
    "\n",
    "#### December 1st, 2016, planned expiration time January 30th 2018, and amount 5000USD has an amount of 5000USD * 31 / \n",
    "#### (31+365+30) = 363.85 for 2016, 5000USD * 365 / (31+365+30) = 4284.04 for 2017, and 5000USD * 30 / (31+365+30) = 352.11 for 2018."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Inizio ripulendo il dataset loans_cleaned da eventuali valori Nan negli attributi \"disburse_time\" e \"planned_expiration_time\" : "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 46,
   "metadata": {},
   "outputs": [],
   "source": [
    "loans_cleaned=loans_cleaned.dropna(subset=['planned_expiration_time','disburse_time'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 47,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "1029468\n"
     ]
    }
   ],
   "source": [
    "print(len(loans_cleaned))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Creo il dataset contenente solo i loans in cui \"disburse_time\" e \"planned_expiration_time\" hanno lo stesso anno :"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 48,
   "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>loan_amount</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>658010</td>\n",
       "      <td>2014-01-09</td>\n",
       "      <td>2014-02-15</td>\n",
       "      <td>400.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>659347</td>\n",
       "      <td>2014-01-17</td>\n",
       "      <td>2014-02-21</td>\n",
       "      <td>625.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>659605</td>\n",
       "      <td>2014-01-15</td>\n",
       "      <td>2014-02-20</td>\n",
       "      <td>350.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>660240</td>\n",
       "      <td>2014-01-20</td>\n",
       "      <td>2014-02-21</td>\n",
       "      <td>125.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>661601</td>\n",
       "      <td>2014-01-10</td>\n",
       "      <td>2014-02-25</td>\n",
       "      <td>1600.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   loan_id disburse_time planned_expiration_time  loan_amount\n",
       "0   658010    2014-01-09              2014-02-15        400.0\n",
       "1   659347    2014-01-17              2014-02-21        625.0\n",
       "2   659605    2014-01-15              2014-02-20        350.0\n",
       "3   660240    2014-01-20              2014-02-21        125.0\n",
       "4   661601    2014-01-10              2014-02-25       1600.0"
      ]
     },
     "execution_count": 48,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "same_year = loans_cleaned[loans_cleaned['disburse_time'].dt.year == loans_cleaned['planned_expiration_time'].dt.year][['loan_id', \"disburse_time\", 'planned_expiration_time', 'loan_amount']].reset_index(drop=True)\n",
    "same_year.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 49,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "878857"
      ]
     },
     "execution_count": 49,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "len(same_year)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Calcolo l'ammontare dei prestiti per ogni anno (sul dataset same_year) : "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 50,
   "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>loans_amount_same_year</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2012</td>\n",
       "      <td>103880625.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2013</td>\n",
       "      <td>98372250.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2014</td>\n",
       "      <td>120456375.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2015</td>\n",
       "      <td>130943675.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2016</td>\n",
       "      <td>132997175.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>2017</td>\n",
       "      <td>144634400.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>2018</td>\n",
       "      <td>85300.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   year  loans_amount_same_year\n",
       "0  2012             103880625.0\n",
       "1  2013              98372250.0\n",
       "2  2014             120456375.0\n",
       "3  2015             130943675.0\n",
       "4  2016             132997175.0\n",
       "5  2017             144634400.0\n",
       "6  2018                 85300.0"
      ]
     },
     "execution_count": 50,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "loans_amount_same_year = same_year.groupby(same_year['disburse_time'].dt.year)['loan_amount'].sum().to_frame().reset_index()\n",
    "loans_amount_same_year = loans_amount_same_year.rename(columns= {\"disburse_time\" : \"year\", \"loan_amount\" : \"loans_amount_same_year\"})\n",
    "loans_amount_same_year"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Creo il dataset contenente solo i loans in cui \"disburse_time\" e \"planned_expiration_time\" hanno anni diversi :"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 51,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "150611"
      ]
     },
     "execution_count": 51,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "different_year = loans_cleaned[loans_cleaned['disburse_time'].dt.year != loans_cleaned['planned_expiration_time'].dt.year][['loan_id', \"disburse_time\", 'planned_expiration_time', 'loan_amount']]\n",
    "len(different_year)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Confronto le lunghezze dei tre dataset come controllo :"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 52,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "0\n"
     ]
    }
   ],
   "source": [
    "print(len(loans_cleaned) - len(same_year) - len(different_year))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Definisco la funzione con cui calcolare per ogni anno del prestito l'ammontare da computare ad ogni anno. \n",
    "La funzione richiede una certa capacità computazionale e di memoria (RAM), proprio per questo motivo ho diviso il set di dati in due. In realtà questa funzione porta al risultato finale anche con l'intero dataset iniziale (loans_cleaned)."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 53,
   "metadata": {},
   "outputs": [],
   "source": [
    "def func(disburse_time, planned_time, loan_amount):\n",
    "    total_cost=loan_amount\n",
    "    dict_map = {}\n",
    "    for year in range(disburse_time.year, planned_time.year+1):\n",
    "        if year==disburse_time.year:\n",
    "            dict_map[year] = (pd.to_datetime(datetime.date(year, 12, 31)) - disburse_time).days+1\n",
    "        elif year==planned_time.year:\n",
    "            dict_map[year] = (planned_time - pd.to_datetime(datetime.date(year-1, 12, 31))).days\n",
    "        else:\n",
    "            if year%4==0:\n",
    "                dict_map[year]=366\n",
    "            else:\n",
    "                dict_map[year]=365\n",
    "    \n",
    "    \n",
    "    dict_year_share = {year:total_cost*days/sum(dict_map.values()) for year,days in dict_map.items()}\n",
    "    return dict_year_share"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Applico ora la funzione al dataset different_year :"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 54,
   "metadata": {},
   "outputs": [],
   "source": [
    "result = different_year.apply(lambda x: pd.Series(func(x['disburse_time'], x['planned_expiration_time'],x['loan_amount'])), axis=1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 55,
   "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>2011</th>\n",
       "      <th>2012</th>\n",
       "      <th>2013</th>\n",
       "      <th>2014</th>\n",
       "      <th>2015</th>\n",
       "      <th>2016</th>\n",
       "      <th>2017</th>\n",
       "      <th>2018</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>22.727273</td>\n",
       "      <td>102.272727</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>49.484536</td>\n",
       "      <td>350.515464</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>108.050847</td>\n",
       "      <td>316.949153</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>173.360656</td>\n",
       "      <td>1001.639344</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>19.780220</td>\n",
       "      <td>280.219780</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   2011  2012        2013         2014  2015  2016  2017  2018\n",
       "0   NaN   NaN   22.727273   102.272727   NaN   NaN   NaN   NaN\n",
       "1   NaN   NaN   49.484536   350.515464   NaN   NaN   NaN   NaN\n",
       "2   NaN   NaN  108.050847   316.949153   NaN   NaN   NaN   NaN\n",
       "3   NaN   NaN  173.360656  1001.639344   NaN   NaN   NaN   NaN\n",
       "4   NaN   NaN   19.780220   280.219780   NaN   NaN   NaN   NaN"
      ]
     },
     "execution_count": 55,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "result = result.reset_index(drop=True)\n",
    "result.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 56,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "0\n"
     ]
    }
   ],
   "source": [
    "# confronto le lunghezze dei tre dataset come controllo\n",
    "\n",
    "print(len(loans_cleaned) - len(same_year) - len(result))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Estraggo l'insieme degli anni così da poter creare un nuovo df dove :\n",
    "- nella colonna \"year\", sono riportati tutti gli anni coinvolti nei prestiti; \n",
    "- nella colonna 'loans_amount_different_year' ,  il valore totale dei presti per ogni singolo anno :"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 57,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "['2011', '2012', '2013', '2014', '2015', '2016', '2017', '2018']"
      ]
     },
     "execution_count": 57,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "max_year=different_year[[\"disburse_time\",\"planned_expiration_time\"]].max().max().year\n",
    "min_year=different_year[[\"disburse_time\",\"planned_expiration_time\"]].min().min().year\n",
    "\n",
    "years=[str(year) for year in range(min_year,max_year+1)]\n",
    "years"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 58,
   "metadata": {},
   "outputs": [],
   "source": [
    "loans_amount_different_year = pd.DataFrame()\n",
    "diff_summary= pd.DataFrame(result.sum())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 59,
   "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>loans_amount_different_year</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2011</td>\n",
       "      <td>6.614928e+05</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2012</td>\n",
       "      <td>9.251024e+06</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2013</td>\n",
       "      <td>2.512557e+07</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2014</td>\n",
       "      <td>3.001850e+07</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2015</td>\n",
       "      <td>2.394231e+07</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>2016</td>\n",
       "      <td>2.081552e+07</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>2017</td>\n",
       "      <td>2.046922e+07</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>2018</td>\n",
       "      <td>4.877086e+06</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   year  loans_amount_different_year\n",
       "0  2011                 6.614928e+05\n",
       "1  2012                 9.251024e+06\n",
       "2  2013                 2.512557e+07\n",
       "3  2014                 3.001850e+07\n",
       "4  2015                 2.394231e+07\n",
       "5  2016                 2.081552e+07\n",
       "6  2017                 2.046922e+07\n",
       "7  2018                 4.877086e+06"
      ]
     },
     "execution_count": 59,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "loans_amount_different_year['year'] = diff_summary.index\n",
    "loans_amount_different_year['loans_amount_different_year'] = diff_summary.values\n",
    "loans_amount_different_year"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Unisco tutto in un unico dataset :"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 60,
   "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>loans_amount_different_year</th>\n",
       "      <th>loans_amount_same_year</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2011</td>\n",
       "      <td>6.614928e+05</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2012</td>\n",
       "      <td>9.251024e+06</td>\n",
       "      <td>103880625.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2013</td>\n",
       "      <td>2.512557e+07</td>\n",
       "      <td>98372250.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2014</td>\n",
       "      <td>3.001850e+07</td>\n",
       "      <td>120456375.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2015</td>\n",
       "      <td>2.394231e+07</td>\n",
       "      <td>130943675.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>2016</td>\n",
       "      <td>2.081552e+07</td>\n",
       "      <td>132997175.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>2017</td>\n",
       "      <td>2.046922e+07</td>\n",
       "      <td>144634400.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>2018</td>\n",
       "      <td>4.877086e+06</td>\n",
       "      <td>85300.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   year  loans_amount_different_year  loans_amount_same_year\n",
       "0  2011                 6.614928e+05                     NaN\n",
       "1  2012                 9.251024e+06             103880625.0\n",
       "2  2013                 2.512557e+07              98372250.0\n",
       "3  2014                 3.001850e+07             120456375.0\n",
       "4  2015                 2.394231e+07             130943675.0\n",
       "5  2016                 2.081552e+07             132997175.0\n",
       "6  2017                 2.046922e+07             144634400.0\n",
       "7  2018                 4.877086e+06                 85300.0"
      ]
     },
     "execution_count": 60,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "loans_amount_for_year = pd.merge(loans_amount_different_year, loans_amount_same_year, on = 'year', how='outer')\n",
    "loans_amount_for_year"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 61,
   "metadata": {},
   "outputs": [],
   "source": [
    "loans_amount_for_year = loans_amount_for_year.fillna(0)"
   ]
  },
  {
   "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>year</th>\n",
       "      <th>loans_amount_different_year</th>\n",
       "      <th>loans_amount_same_year</th>\n",
       "      <th>tot_loans</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2011</td>\n",
       "      <td>6.614928e+05</td>\n",
       "      <td>0.0</td>\n",
       "      <td>6.614928e+05</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2012</td>\n",
       "      <td>9.251024e+06</td>\n",
       "      <td>103880625.0</td>\n",
       "      <td>1.131316e+08</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2013</td>\n",
       "      <td>2.512557e+07</td>\n",
       "      <td>98372250.0</td>\n",
       "      <td>1.234978e+08</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2014</td>\n",
       "      <td>3.001850e+07</td>\n",
       "      <td>120456375.0</td>\n",
       "      <td>1.504749e+08</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2015</td>\n",
       "      <td>2.394231e+07</td>\n",
       "      <td>130943675.0</td>\n",
       "      <td>1.548860e+08</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>2016</td>\n",
       "      <td>2.081552e+07</td>\n",
       "      <td>132997175.0</td>\n",
       "      <td>1.538127e+08</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>2017</td>\n",
       "      <td>2.046922e+07</td>\n",
       "      <td>144634400.0</td>\n",
       "      <td>1.651036e+08</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>2018</td>\n",
       "      <td>4.877086e+06</td>\n",
       "      <td>85300.0</td>\n",
       "      <td>4.962386e+06</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   year  loans_amount_different_year  loans_amount_same_year     tot_loans\n",
       "0  2011                 6.614928e+05                     0.0  6.614928e+05\n",
       "1  2012                 9.251024e+06             103880625.0  1.131316e+08\n",
       "2  2013                 2.512557e+07              98372250.0  1.234978e+08\n",
       "3  2014                 3.001850e+07             120456375.0  1.504749e+08\n",
       "4  2015                 2.394231e+07             130943675.0  1.548860e+08\n",
       "5  2016                 2.081552e+07             132997175.0  1.538127e+08\n",
       "6  2017                 2.046922e+07             144634400.0  1.651036e+08\n",
       "7  2018                 4.877086e+06                 85300.0  4.962386e+06"
      ]
     },
     "execution_count": 62,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "loans_amount_for_year['tot_loans'] = loans_amount_for_year['loans_amount_different_year'] + loans_amount_for_year['loans_amount_same_year']\n",
    "loans_amount_for_year"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 63,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "-2.384185791015625e-07\n"
     ]
    }
   ],
   "source": [
    "print(loans_amount_for_year['tot_loans'].sum() - tot_loans)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "La differenza, trascurabilissima,  è dovuta a errori in fase di approssimizzazione nel calcolo dell'ammontare da computare di ogni prestito per ogni anno del prestito."
   ]
  },
  {
   "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.3"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}