# Empirical project 9


## Getting started in Python


Visit the ‘Getting started in Python’ page for help and advice on setting up a Python session to work with. Remember, you can run any page from this book as a *notebook* by downloading the relevant file from this [repository](https://github.com/aeturrell/core_python) and running it on your own computer. Alternatively, you can run pages online in your browser over at [Binder](https://mybinder.org/v2/gh/aeturrell/core_python/HEAD).


## Preliminary settings


Let's import the packages we'll need and also configure the settings we want:


```python
import pandas as pd
import matplotlib.pyplot as plt
import numpy as np
from pathlib import Path
import pingouin as pg
from lets_plot import *


LetsPlot.setup_html(no_js=True)
```


### *Python walk-through 9.1* Importing data into Python


Ensure that the Excel file you downloaded is in a sub-directory of your working directory called ‘data’.


Before importing the data, open it in Excel to look at its structure. You can see there are three tabs: ‘Data dictionary’, ‘All households’, and ‘Got loan’. We will import them into separate dataframes (`data_dict`, `all_hh`, and `got_l` respectively). We import the ‘Data dictionary’ so that we do not have to return to the Excel spreadsheet.


Also note that there are many empty cells, which is how missing data is coded in Excel (but not in Python). In the `pd.read_excel` function, the default setting is that empty cells are read as `NA`, so we don't need to specify this. Note that **pandas** may warn you about an ‘unknown extension’: an Excel file is actually a bundle of files tied up to look like one file, and **pandas** doesn't recognise one of the files in the bundle. Still, this warning won’t prevent us from importing the data we need from the worksheets.


```python
data_dict = pd.read_excel(
    Path("data/doing-economics-working-in-excel-project-9-datafile.xlsx"),
    sheet_name="Data dictionary",
)


all_hh = pd.read_excel(
    Path("data/doing-economics-working-in-excel-project-9-datafile.xlsx"),
    sheet_name="All households",
)


got_l = pd.read_excel(
    Path("data/doing-economics-working-in-excel-project-9-datafile.xlsx"),
    sheet_name="Got loan",
)
```




Now let’s look at the variable types `all_hh` and `got_l` using the `.info()` method.


```python
all_hh.info()
```


```
<class 'pandas.core.frame.DataFrame'>
RangeIndex: 5262 entries, 0 to 5261
Data columns (total 19 columns):
 #   Column              Non-Null Count  Dtype  
---  ------              --------------  -----  
 0   household_id2       5262 non-null   int64  
 1   got_loan            5250 non-null   object 
 2   rural               5262 non-null   object 
 3   hhsize              5260 non-null   float64
 4   region              5262 non-null   object 
 5   gender              5261 non-null   object 
 6   age                 5253 non-null   float64
 7   young_children      5262 non-null   int64  
 8   working_age_adults  5262 non-null   int64  
 9   max_education       5262 non-null   float64
 10  number_assets       5262 non-null   int64  
 11  loan_rejected       5259 non-null   object 
 12  rejection_source1   226 non-null    object 
 13  rejection_source2   48 non-null     object 
 14  loan_purpose        221 non-null    object 
 15  loan_purpose_other  52 non-null     object 
 16  did_not_apply       5223 non-null   object 
 17  reason_not_apply1   3592 non-null   object 
 18  reason_not_apply2   2015 non-null   object 
dtypes: float64(3), int64(4), object(12)
memory usage: 781.2+ KB
```


```python
got_l.info()
```


```
<class 'pandas.core.frame.DataFrame'>
RangeIndex: 1480 entries, 0 to 1479
Data columns (total 21 columns):
 #   Column               Non-Null Count  Dtype   
---  ------               --------------  -----   
 0   household_id2        1480 non-null   int64   
 1   got_loan             1470 non-null   category
 2   rural                1480 non-null   category
 3   hhsize               1478 non-null   float64 
 4   region               1480 non-null   category
 5   gender               1480 non-null   category
 6   age                  1479 non-null   float64 
 7   young_children       1480 non-null   int64   
 8   working_age_adults   1480 non-null   int64   
 9   max_education        1480 non-null   float64 
 10  number_assets        1480 non-null   int64   
 11  borrowed_from        1470 non-null   category
 12  borrowed_from_other  147 non-null    category
 13  loan_purpose         1444 non-null   category
 14  loan_startmonth      1476 non-null   category
 15  loan_startyear       1477 non-null   float64 
 16  loan_repaid          1478 non-null   category
 17  loan_endmonth        946 non-null    category
 18  loan_endyear         946 non-null    float64 
 19  loan_amount          1479 non-null   float64 
 20  loan_interest        1445 non-null   float64 
dtypes: category(10), float64(7), int64(4)
memory usage: 145.1 KB
```


It is important to ensure that all variables we expect to be numerical (numbers) have either `int` or `float` listed as their ‘Dtype’, and in this case, they are. You can see that there are many variables that are coded as `object` variables because they are text (for example `gender` or `region`), but since we can use these variables to group data by category, we will use `.astype("category")` to change them into categorical variables for later use.


Instead of converting each object variable to a factor variable individually, we can use the `select_dtypes` method to find all columns that are currently of type object and then convert those columns specifically.




```python
cols = all_hh.select_dtypes("object").columns
all_hh[cols] = all_hh[cols].astype("category")


cols = got_l.select_dtypes("object").columns
got_l[cols] = got_l[cols].astype("category")
```


Making use of `.info()` will now show you that the remaining object columns have been "recast" to be of data type "category".




### *Python walk-through 9.2* Creating summary tables


To get the proportions of households in each region living in large towns, small towns, or rural areas (encoded in the variable `rural`), we use the `pd.crosstab` function to create a cross-tabulation. Without any further options, `pd.crosstab` would produce counts of households in the respective regions and area types. However, we can pass a keyword argument, `normalize`, to turn the counts into proportions by rows or columns.


In the code below, we use `normalize="index"` to ensure that each row sums to one. We also use `.round(3`) to specify the number of decimal places that are saved in the output data frame.


Remember that if you ever want to know more about a function, you can hover over it in Visual Studio Code to see its options, including the keyword arguments it takes. Or you can run `help(function-name)`.


```
stab_one = pd.crosstab(all_hh["region"], all_hh["rural"], normalize="index").round(3)
stab_one
```


<div>
<style scoped>
    .dataframe tbody tr th:only-of-type {
        vertical-align: middle;
    }


```
.dataframe tbody tr th {
    vertical-align: top;
}


.dataframe thead th {
    text-align: right;
}
```


</style>
<table border="1" class="dataframe">
  <thead>
    <tr style="text-align: right;">
      <th>rural</th>
      <th>Large town (urban)</th>
      <th>Rural</th>
      <th>Small town (urban)</th>
    </tr>
    <tr>
      <th>region</th>
      <th></th>
      <th></th>
      <th></th>
    </tr>
  </thead>
  <tbody>
    <tr>
      <th>Addis Ababa</th>
      <td>1.000</td>
      <td>0.000</td>
      <td>0.000</td>
    </tr>
    <tr>
      <th>Afar</th>
      <td>0.096</td>
      <td>0.757</td>
      <td>0.147</td>
    </tr>
    <tr>
      <th>Amhara</th>
      <td>0.218</td>
      <td>0.669</td>
      <td>0.113</td>
    </tr>
    <tr>
      <th>Benshagul Gumuz</th>
      <td>0.000</td>
      <td>0.904</td>
      <td>0.096</td>
    </tr>
    <tr>
      <th>Diredwa</th>
      <td>0.468</td>
      <td>0.532</td>
      <td>0.000</td>
    </tr>
    <tr>
      <th>Gambelia</th>
      <td>0.115</td>
      <td>0.800</td>
      <td>0.085</td>
    </tr>
    <tr>
      <th>Harari</th>
      <td>0.273</td>
      <td>0.727</td>
      <td>0.000</td>
    </tr>
    <tr>
      <th>Oromia</th>
      <td>0.283</td>
      <td>0.607</td>
      <td>0.110</td>
    </tr>
    <tr>
      <th>SNNP</th>
      <td>0.183</td>
      <td>0.727</td>
      <td>0.090</td>
    </tr>
    <tr>
      <th>Somalie</th>
      <td>0.155</td>
      <td>0.755</td>
      <td>0.090</td>
    </tr>
    <tr>
      <th>Tigray</th>
      <td>0.367</td>
      <td>0.563</td>
      <td>0.070</td>
    </tr>
  </tbody>
</table>
</div>
```


Let’s use a similar approach to calculate the percentage of households with women as head of the family (encoded in the variable `gender`).


```
stab_two = all_hh["gender"].value_counts(normalize=True).round(3)
stab_two
```


```
gender
Male      0.696
Female    0.304
Name: proportion, dtype: float64
```


As shown, 30.4% of households have a head of the family who is a woman.


We need to provide summary statistics for a range of variables. Most of these variables are numeric variables, but one, `gender`, is a categorical variable. We can distinguish these types using the `.select_dtypes` method.


There are a few different ways to get summary statistics on a data frame. `pandas` has a built-in method called `.describe`, which produces different information depending on whether the given columns are numeric or not. Here it is running on just numeric columns:


```
all_hh.select_dtypes("number").describe().round(2)
```


<div>
<style scoped>
    .dataframe tbody tr th:only-of-type {
        vertical-align: middle;
    }


    .dataframe tbody tr th {
        vertical-align: top;
    }


    .dataframe thead th {
        text-align: right;
    }
</style>
<table border="1" class="dataframe">
  <thead>
    <tr style="text-align: right;">
      <th></th>
      <th>household_id2</th>
      <th>hhsize</th>
      <th>age</th>
      <th>young_children</th>
      <th>working_age_adults</th>
      <th>max_education</th>
      <th>number_assets</th>
    </tr>
  </thead>
  <tbody>
    <tr>
      <th>count</th>
      <td>5.262000e+03</td>
      <td>5260.00</td>
      <td>5253.00</td>
      <td>5262.00</td>
      <td>5262.00</td>
      <td>5262.00</td>
      <td>5262.00</td>
    </tr>
    <tr>
      <th>mean</th>
      <td>5.759796e+16</td>
      <td>4.58</td>
      <td>44.18</td>
      <td>1.89</td>
      <td>2.58</td>
      <td>7.53</td>
      <td>14.90</td>
    </tr>
    <tr>
      <th>std</th>
      <td>3.938921e+16</td>
      <td>2.40</td>
      <td>15.61</td>
      <td>1.71</td>
      <td>1.52</td>
      <td>7.28</td>
      <td>17.23</td>
    </tr>
    <tr>
      <th>min</th>
      <td>1.010109e+16</td>
      <td>1.00</td>
      <td>3.00</td>
      <td>0.00</td>
      <td>0.00</td>
      <td>0.00</td>
      <td>0.00</td>
    </tr>
    <tr>
      <th>25%</th>
      <td>3.051509e+16</td>
      <td>3.00</td>
      <td>32.00</td>
      <td>0.00</td>
      <td>2.00</td>
      <td>2.00</td>
      <td>5.00</td>
    </tr>
    <tr>
      <th>50%</th>
      <td>4.150101e+16</td>
      <td>4.00</td>
      <td>42.00</td>
      <td>2.00</td>
      <td>2.00</td>
      <td>6.00</td>
      <td>9.00</td>
    </tr>
    <tr>
      <th>75%</th>
      <td>7.110409e+16</td>
      <td>6.00</td>
      <td>55.00</td>
      <td>3.00</td>
      <td>3.00</td>
      <td>10.00</td>
      <td>18.00</td>
    </tr>
    <tr>
      <th>max</th>
      <td>1.501021e+17</td>
      <td>16.00</td>
      <td>99.00</td>
      <td>10.00</td>
      <td>10.00</td>
      <td>30.00</td>
      <td>203.00</td>
    </tr>
  </tbody>
</table>
</div>




If we use it on a categorical variable, `gender`, we get information relevant to that type of data:


```python
all_hh["gender"].describe()
```


```
count     5261
unique       2
top       Male
freq      3662
Name: gender, dtype: object
```


A more powerful way to summarise a dataframe is provided by a package called **skimpy** (which you can install by running `pip install skimpy` in the command line). Here is skimpy's `skim` function applied to `all_hh`:


```
from skimpy import skim
skim(all_hh)
```


<pre style="white-space:pre;overflow-x:auto;line-height:normal;font-family:Menlo,'DejaVu Sans Mono',consolas,'Courier New',monospace">╭──────────────────────────────────────────────── skimpy summary ─────────────────────────────────────────────────╮
│ <span style="font-style: italic">         Data Summary         </span> <span style="font-style: italic">      Data Types       </span> <span style="font-style: italic">       Categories        </span>                                │
│ ┏━━━━━━━━━━━━━━━━━━━┳━━━━━━━━┓ ┏━━━━━━━━━━━━━┳━━━━━━━┓ ┏━━━━━━━━━━━━━━━━━━━━━━━┓                                │
│ ┃<span style="color: #008080; text-decoration-color: #008080; font-weight: bold"> dataframe         </span>┃<span style="color: #008080; text-decoration-color: #008080; font-weight: bold"> Values </span>┃ ┃<span style="color: #008080; text-decoration-color: #008080; font-weight: bold"> Column Type </span>┃<span style="color: #008080; text-decoration-color: #008080; font-weight: bold"> Count </span>┃ ┃<span style="color: #008080; text-decoration-color: #008080; font-weight: bold"> Categorical Variables </span>┃                                │
│ ┡━━━━━━━━━━━━━━━━━━━╇━━━━━━━━┩ ┡━━━━━━━━━━━━━╇━━━━━━━┩ ┡━━━━━━━━━━━━━━━━━━━━━━━┩                                │
│ │ Number of rows    │ 5262   │ │ category    │ 12    │ │ got_loan              │                                │
│ │ Number of columns │ 19     │ │ int64       │ 4     │ │ rural                 │                                │
│ └───────────────────┴────────┘ │ float64     │ 3     │ │ region                │                                │
│                                └─────────────┴───────┘ │ gender                │                                │
│                                                        │ loan_rejected         │                                │
│                                                        │ rejection_source1     │                                │
│                                                        │ rejection_source2     │                                │
│                                                        │ loan_purpose          │                                │
│                                                        │ loan_purpose_other    │                                │
│                                                        │ did_not_apply         │                                │
│                                                        │ reason_not_apply1     │                                │
│                                                        │ reason_not_apply2     │                                │
│                                                        └───────────────────────┘                                │
│ <span style="font-style: italic">                                                    number                                                    </span>  │
│ ┏━━━━━━━━━━━━━━━━━━━┳━━━━┳━━━━━━┳━━━━━━━━━┳━━━━━━━━━┳━━━━━━━┳━━━━━━━━━┳━━━━━━━━━┳━━━━━━━━━┳━━━━━━━━━┳━━━━━━━━┓  │
│ ┃<span style="font-weight: bold"> column_name       </span>┃<span style="font-weight: bold"> NA </span>┃<span style="font-weight: bold"> NA % </span>┃<span style="font-weight: bold"> mean    </span>┃<span style="font-weight: bold"> sd      </span>┃<span style="font-weight: bold"> p0    </span>┃<span style="font-weight: bold"> p25     </span>┃<span style="font-weight: bold"> p50     </span>┃<span style="font-weight: bold"> p75     </span>┃<span style="font-weight: bold"> p100    </span>┃<span style="font-weight: bold"> hist   </span>┃  │
│ ┡━━━━━━━━━━━━━━━━━━━╇━━━━╇━━━━━━╇━━━━━━━━━╇━━━━━━━━━╇━━━━━━━╇━━━━━━━━━╇━━━━━━━━━╇━━━━━━━━━╇━━━━━━━━━╇━━━━━━━━┩  │
│ │ <span style="color: #af87ff; text-decoration-color: #af87ff">household_id2    </span> │ <span style="color: #008080; text-decoration-color: #008080"> 0</span> │ <span style="color: #008080; text-decoration-color: #008080">   0</span> │ <span style="color: #008080; text-decoration-color: #008080">5.8e+16</span> │ <span style="color: #008080; text-decoration-color: #008080">3.9e+16</span> │ <span style="color: #008080; text-decoration-color: #008080">1e+16</span> │ <span style="color: #008080; text-decoration-color: #008080">3.1e+16</span> │ <span style="color: #008080; text-decoration-color: #008080">4.2e+16</span> │ <span style="color: #008080; text-decoration-color: #008080">7.1e+16</span> │ <span style="color: #008080; text-decoration-color: #008080">1.5e+17</span> │ <span style="color: #008000; text-decoration-color: #008000">▇▆▆ ▁▃</span> │  │
│ │ <span style="color: #af87ff; text-decoration-color: #af87ff">hhsize           </span> │ <span style="color: #008080; text-decoration-color: #008080"> 2</span> │ <span style="color: #008080; text-decoration-color: #008080">0.04</span> │ <span style="color: #008080; text-decoration-color: #008080">    4.6</span> │ <span style="color: #008080; text-decoration-color: #008080">    2.4</span> │ <span style="color: #008080; text-decoration-color: #008080">    1</span> │ <span style="color: #008080; text-decoration-color: #008080">      3</span> │ <span style="color: #008080; text-decoration-color: #008080">      4</span> │ <span style="color: #008080; text-decoration-color: #008080">      6</span> │ <span style="color: #008080; text-decoration-color: #008080">     16</span> │ <span style="color: #008000; text-decoration-color: #008000"> ▇▇▆▁ </span> │  │
│ │ <span style="color: #af87ff; text-decoration-color: #af87ff">age              </span> │ <span style="color: #008080; text-decoration-color: #008080"> 9</span> │ <span style="color: #008080; text-decoration-color: #008080">0.17</span> │ <span style="color: #008080; text-decoration-color: #008080">     44</span> │ <span style="color: #008080; text-decoration-color: #008080">     16</span> │ <span style="color: #008080; text-decoration-color: #008080">    3</span> │ <span style="color: #008080; text-decoration-color: #008080">     32</span> │ <span style="color: #008080; text-decoration-color: #008080">     42</span> │ <span style="color: #008080; text-decoration-color: #008080">     55</span> │ <span style="color: #008080; text-decoration-color: #008080">     99</span> │ <span style="color: #008000; text-decoration-color: #008000"> ▇▇▅▂ </span> │  │
│ │ <span style="color: #af87ff; text-decoration-color: #af87ff">young_children   </span> │ <span style="color: #008080; text-decoration-color: #008080"> 0</span> │ <span style="color: #008080; text-decoration-color: #008080">   0</span> │ <span style="color: #008080; text-decoration-color: #008080">    1.9</span> │ <span style="color: #008080; text-decoration-color: #008080">    1.7</span> │ <span style="color: #008080; text-decoration-color: #008080">    0</span> │ <span style="color: #008080; text-decoration-color: #008080">      0</span> │ <span style="color: #008080; text-decoration-color: #008080">      2</span> │ <span style="color: #008080; text-decoration-color: #008080">      3</span> │ <span style="color: #008080; text-decoration-color: #008080">     10</span> │ <span style="color: #008000; text-decoration-color: #008000"> ▇▆▂▁ </span> │  │
│ │ <span style="color: #af87ff; text-decoration-color: #af87ff">working_age_adult</span> │ <span style="color: #008080; text-decoration-color: #008080"> 0</span> │ <span style="color: #008080; text-decoration-color: #008080">   0</span> │ <span style="color: #008080; text-decoration-color: #008080">    2.6</span> │ <span style="color: #008080; text-decoration-color: #008080">    1.5</span> │ <span style="color: #008080; text-decoration-color: #008080">    0</span> │ <span style="color: #008080; text-decoration-color: #008080">      2</span> │ <span style="color: #008080; text-decoration-color: #008080">      2</span> │ <span style="color: #008080; text-decoration-color: #008080">      3</span> │ <span style="color: #008080; text-decoration-color: #008080">     10</span> │ <span style="color: #008000; text-decoration-color: #008000"> ▃▇▂▁ </span> │  │
│ │ <span style="color: #af87ff; text-decoration-color: #af87ff">s                </span> │    │      │         │         │       │         │         │         │         │        │  │
│ │ <span style="color: #af87ff; text-decoration-color: #af87ff">max_education    </span> │ <span style="color: #008080; text-decoration-color: #008080"> 0</span> │ <span style="color: #008080; text-decoration-color: #008080">   0</span> │ <span style="color: #008080; text-decoration-color: #008080">    7.5</span> │ <span style="color: #008080; text-decoration-color: #008080">    7.3</span> │ <span style="color: #008080; text-decoration-color: #008080">    0</span> │ <span style="color: #008080; text-decoration-color: #008080">      2</span> │ <span style="color: #008080; text-decoration-color: #008080">      6</span> │ <span style="color: #008080; text-decoration-color: #008080">     10</span> │ <span style="color: #008080; text-decoration-color: #008080">     30</span> │ <span style="color: #008000; text-decoration-color: #008000">▇▆▃▁ ▁</span> │  │
│ │ <span style="color: #af87ff; text-decoration-color: #af87ff">number_assets    </span> │ <span style="color: #008080; text-decoration-color: #008080"> 0</span> │ <span style="color: #008080; text-decoration-color: #008080">   0</span> │ <span style="color: #008080; text-decoration-color: #008080">     15</span> │ <span style="color: #008080; text-decoration-color: #008080">     17</span> │ <span style="color: #008080; text-decoration-color: #008080">    0</span> │ <span style="color: #008080; text-decoration-color: #008080">      5</span> │ <span style="color: #008080; text-decoration-color: #008080">      9</span> │ <span style="color: #008080; text-decoration-color: #008080">     18</span> │ <span style="color: #008080; text-decoration-color: #008080">    200</span> │ <span style="color: #008000; text-decoration-color: #008000">  ▇▁  </span> │  │
│ └───────────────────┴────┴──────┴─────────┴─────────┴───────┴─────────┴─────────┴─────────┴─────────┴────────┘  │
│ <span style="font-style: italic">                                                   category                                                   </span>  │
│ ┏━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━┳━━━━━━━━━━━━━┳━━━━━━━━━━━━━━━┳━━━━━━━━━━━━━━━━━━┳━━━━━━━━━━━━━━━━┓  │
│ ┃<span style="font-weight: bold"> column_name                              </span>┃<span style="font-weight: bold"> NA          </span>┃<span style="font-weight: bold"> NA %          </span>┃<span style="font-weight: bold"> ordered          </span>┃<span style="font-weight: bold"> unique         </span>┃  │
│ ┡━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━╇━━━━━━━━━━━━━╇━━━━━━━━━━━━━━━╇━━━━━━━━━━━━━━━━━━╇━━━━━━━━━━━━━━━━┩  │
│ │ <span style="color: #af87ff; text-decoration-color: #af87ff">got_loan                                </span> │ <span style="color: #008080; text-decoration-color: #008080">         12</span> │ <span style="color: #008080; text-decoration-color: #008080">         0.23</span> │ <span style="color: #00d7ff; text-decoration-color: #00d7ff">False           </span> │ <span style="color: #008080; text-decoration-color: #008080">             3</span> │  │
│ │ <span style="color: #af87ff; text-decoration-color: #af87ff">rural                                   </span> │ <span style="color: #008080; text-decoration-color: #008080">          0</span> │ <span style="color: #008080; text-decoration-color: #008080">            0</span> │ <span style="color: #00d7ff; text-decoration-color: #00d7ff">False           </span> │ <span style="color: #008080; text-decoration-color: #008080">             3</span> │  │
│ │ <span style="color: #af87ff; text-decoration-color: #af87ff">region                                  </span> │ <span style="color: #008080; text-decoration-color: #008080">          0</span> │ <span style="color: #008080; text-decoration-color: #008080">            0</span> │ <span style="color: #00d7ff; text-decoration-color: #00d7ff">False           </span> │ <span style="color: #008080; text-decoration-color: #008080">            11</span> │  │
│ │ <span style="color: #af87ff; text-decoration-color: #af87ff">gender                                  </span> │ <span style="color: #008080; text-decoration-color: #008080">          1</span> │ <span style="color: #008080; text-decoration-color: #008080">         0.02</span> │ <span style="color: #00d7ff; text-decoration-color: #00d7ff">False           </span> │ <span style="color: #008080; text-decoration-color: #008080">             3</span> │  │
│ │ <span style="color: #af87ff; text-decoration-color: #af87ff">loan_rejected                           </span> │ <span style="color: #008080; text-decoration-color: #008080">          3</span> │ <span style="color: #008080; text-decoration-color: #008080">         0.06</span> │ <span style="color: #00d7ff; text-decoration-color: #00d7ff">False           </span> │ <span style="color: #008080; text-decoration-color: #008080">             3</span> │  │
│ │ <span style="color: #af87ff; text-decoration-color: #af87ff">rejection_source1                       </span> │ <span style="color: #008080; text-decoration-color: #008080">       5036</span> │ <span style="color: #008080; text-decoration-color: #008080">        95.71</span> │ <span style="color: #00d7ff; text-decoration-color: #00d7ff">False           </span> │ <span style="color: #008080; text-decoration-color: #008080">            10</span> │  │
│ │ <span style="color: #af87ff; text-decoration-color: #af87ff">rejection_source2                       </span> │ <span style="color: #008080; text-decoration-color: #008080">       5214</span> │ <span style="color: #008080; text-decoration-color: #008080">        99.09</span> │ <span style="color: #00d7ff; text-decoration-color: #00d7ff">False           </span> │ <span style="color: #008080; text-decoration-color: #008080">             9</span> │  │
│ │ <span style="color: #af87ff; text-decoration-color: #af87ff">loan_purpose                            </span> │ <span style="color: #008080; text-decoration-color: #008080">       5041</span> │ <span style="color: #008080; text-decoration-color: #008080">         95.8</span> │ <span style="color: #00d7ff; text-decoration-color: #00d7ff">False           </span> │ <span style="color: #008080; text-decoration-color: #008080">             9</span> │  │
│ │ <span style="color: #af87ff; text-decoration-color: #af87ff">loan_purpose_other                      </span> │ <span style="color: #008080; text-decoration-color: #008080">       5210</span> │ <span style="color: #008080; text-decoration-color: #008080">        99.01</span> │ <span style="color: #00d7ff; text-decoration-color: #00d7ff">False           </span> │ <span style="color: #008080; text-decoration-color: #008080">            15</span> │  │
│ │ <span style="color: #af87ff; text-decoration-color: #af87ff">did_not_apply                           </span> │ <span style="color: #008080; text-decoration-color: #008080">         39</span> │ <span style="color: #008080; text-decoration-color: #008080">         0.74</span> │ <span style="color: #00d7ff; text-decoration-color: #00d7ff">False           </span> │ <span style="color: #008080; text-decoration-color: #008080">             3</span> │  │
│ │ <span style="color: #af87ff; text-decoration-color: #af87ff">reason_not_apply1                       </span> │ <span style="color: #008080; text-decoration-color: #008080">       1670</span> │ <span style="color: #008080; text-decoration-color: #008080">        31.74</span> │ <span style="color: #00d7ff; text-decoration-color: #00d7ff">False           </span> │ <span style="color: #008080; text-decoration-color: #008080">            11</span> │  │
│ │ <span style="color: #af87ff; text-decoration-color: #af87ff">reason_not_apply2                       </span> │ <span style="color: #008080; text-decoration-color: #008080">       3247</span> │ <span style="color: #008080; text-decoration-color: #008080">        61.71</span> │ <span style="color: #00d7ff; text-decoration-color: #00d7ff">False           </span> │ <span style="color: #008080; text-decoration-color: #008080">            11</span> │  │
│ └──────────────────────────────────────────┴─────────────┴───────────────┴──────────────────┴────────────────┘  │
╰────────────────────────────────────────────────────── End ──────────────────────────────────────────────────────╯
</pre>


Now we can see lots of information on both the categorical and numeric data types!




**Python walk-through 9.3** Making frequency tables for loan applications and outcomes


The easiest way to make a frequency table is to use the `pd.crosstab` function. Note that we use the `dropna=False` option (and "All" minus the sum of "No" and "Yes" gives the number of NAs), and the `margins=True option` to give those totals.


```
stab_three = pd.crosstab(
    all_hh["did_not_apply"], all_hh["loan_rejected"], dropna=False, margins=True
)
stab_three
```


<div>
<style scoped>
    .dataframe tbody tr th:only-of-type {
        vertical-align: middle;
    }


    .dataframe tbody tr th {
        vertical-align: top;
    }


    .dataframe thead th {
        text-align: right;
    }
</style>
<table border="1" class="dataframe">
  <thead>
    <tr style="text-align: right;">
      <th>loan_rejected</th>
      <th>No</th>
      <th>Yes</th>
      <th>All</th>
    </tr>
    <tr>
      <th>did_not_apply</th>
      <th></th>
      <th></th>
      <th></th>
    </tr>
  </thead>
  <tbody>
    <tr>
      <th>Applied</th>
      <td>1363</td>
      <td>201</td>
      <td>1565</td>
    </tr>
    <tr>
      <th>Did not apply</th>
      <td>3632</td>
      <td>24</td>
      <td>3658</td>
    </tr>
    <tr>
      <th>All</th>
      <td>5032</td>
      <td>227</td>
      <td>5262</td>
    </tr>
  </tbody>
</table>
</div>


Now we will do some data cleaning and re-examine the table. We:


- exclude the households that indicated that they did not apply for a loan, but also indicated that they were refused a loan. This step results in excluding more than 10% of households that indicated that they were refused a loan, because their answer is nonsensical. We will use the `~` operator, which means "not" (when applied to the logic that follows).
- We will also remove all observations that have missing data for any of these two questions, by using the implicit default value of `dropna=True`.


```
all_hh_c = all_hh.loc[
    ~(
        (all_hh["loan_rejected"] == "Yes")
        & (all_hh["did_not_apply"] == "Did not apply")
    ),
    :,
].copy()
stab_four = pd.crosstab(
    all_hh_c["did_not_apply"], all_hh_c["loan_rejected"], margins=True, normalize="all"
).round(3)
stab_four


```


<div>
<style scoped>
    .dataframe tbody tr th:only-of-type {
        vertical-align: middle;
    }
.dataframe tbody tr th {
    vertical-align: top;
}
.dataframe thead th {
    text-align: right;
}
</style>
<table border="1" class="dataframe">
  <thead>
    <tr style="text-align: right;">
      <th>loan_rejected</th>
      <th>No</th>
      <th>Yes</th>
      <th>All</th>
    </tr>
    <tr>
      <th>did_not_apply</th>
      <th></th>
      <th></th>
      <th></th>
    </tr>
  </thead>
  <tbody>
    <tr>
      <th>Applied</th>
      <td>0.262</td>
      <td>0.039</td>
      <td>0.301</td>
    </tr>
    <tr>
      <th>Did not apply</th>
      <td>0.699</td>
      <td>0.000</td>
      <td>0.699</td>
    </tr>
    <tr>
      <th>All</th>
      <td>0.961</td>
      <td>0.039</td>
      <td>1.000</td>
    </tr>
  </tbody>
</table>
</div>


In the code above, we used the classic syntax for accessing rows and columns, `.loc[rows, columns]`. Because we wanted all columns, we used `:`. We also used `.copy()` to create a new set of data called `all_hh_c`. Without this step, any modifications we made to `all_hh_c` would also be applied to `all_hh`.




**Python walk-through 9.4** Creating variables to classify households


Let’s first create the `hh_status` variable. We set the values of `hh_status` to `"not applied"`, then use logical indexing to change all entries where households applied for a loan (`all_hh_c["did_not_apply"] == "Applied"`) and were accepted (`all_hh_c["loan_rejected"] == "No"`) to `"successful"`, and change all entries where households who were denied (`all_hh_c["loan_rejected"] == "Yes"`) to `"denied"`.


```
# This is the default category and creates the new column
all_hh_c["hh_status"] = "not applied"


all_hh_c.loc[
    (all_hh_c["did_not_apply"] == "Applied") & (all_hh_c["loan_rejected"] == "No"),
    "hh_status",
] = "successful"
all_hh_c.loc[
    (all_hh_c["did_not_apply"] == "Applied") & (all_hh_c["loan_rejected"] == "Yes"),
    "hh_status",
] = "denied"


# Change from a string variable to a categorical variable
all_hh_c["hh_status"] = all_hh_c["hh_status"].astype("category")
```


Let's have a look at the frequencies of the different outcomes:


```python
all_hh_c["hh_status"].value_counts()
```


```
hh_status
not applied    3674
successful     1363
denied          201
Name: count, dtype: int64
```


Now we will use the same steps to make the `discouraged_borrower` variable.


```
# This is the default category and creates the new column
all_hh_c["discouraged_borrower"] = "No"


all_hh_c.loc[
    (all_hh_c["reason_not_apply1"] == "Believe Would Be Refused"),
    "discouraged_borrower",
] = "Yes"
all_hh_c.loc[
    (all_hh_c["reason_not_apply2"] == "Believe Would Be Refused"),
    "discouraged_borrower",
] = "Yes"


# Change from a string variable to a categorical variable
all_hh_c["discouraged_borrower"] = all_hh_c["discouraged_borrower"].astype("category")


all_hh_c["discouraged_borrower"].value_counts()
```


```
discouraged_borrower
No     4649
Yes     589
Name: count, dtype: int64
```


To make the `credit_constrained` variable, we use the `.cat.categories` property to check all the possible answers to the `reason_not_apply1` variable. We store these answers in the object `sel_ans`. (To convert from an index, a special type of **pandas** object, to a simple Python list, we put the expression on the right-hand side within a `list(..)`.)


```python
sel_ans = list(all_hh_c["reason_not_apply1"].cat.categories)
sel_ans
```


```
['Believe Would Be Refused',
 'Do Not Know Any Lender',
 'Do Not Like To Be In Debt',
 'Fear Not Be Able To Pay',
 'Have Adequate Farm',
 'Inadequate Collateral',
 'No Farm Or Business',
 'Other (Specify)',
 'Too Expensive',
 'Too Much Trouble']
```


Of these reasons, only reasons 4 (`Have Adequate Farm`) and 7 (`Other`) do not lead to a conclusion that a household is credit constrained, so we remove them from `sel_ans`. Note that this numbering starts from 0.


```
sel_ans.remove("Have Adequate Farm")
sel_ans.remove("Other (Specify)")
sel_ans
```


```
['Believe Would Be Refused',
 'Do Not Know Any Lender',
 'Do Not Like To Be In Debt',
 'Fear Not Be Able To Pay',
 'Inadequate Collateral',
 'No Farm or Business',
 'Too Expensive',
 'Too Much Trouble']
```


Households that did not provide any reasons are classified as not credit constrained. We'll take this as our default category and use it to create the column.


```python
all_hh_c["credit_constrained"] = "No"


all_hh_c.loc[all_hh_c["reason_not_apply1"].isin(sel_ans), "credit_constrained"] = "Yes"
all_hh_c.loc[all_hh_c["reason_not_apply2"].isin(sel_ans), "credit_constrained"] = "Yes"


# Turn `credit_constrained` into a categorical variable
all_hh_c["credit_constrained"] = all_hh_c["credit_constrained"].astype("category")
all_hh_c["credit_constrained"].value_counts()
```


```
credit_constrained
Yes    3020
No     2218
Name: count, dtype: int64
```


The use of `.isin` in the selection criterion is a very useful programming technique that you can use to select data according to a list of variables. In this case, `sel_ans` contains all the answers that we associate with a credit constrained household.


`all_hh_c["reason_not_apply1"].isin(sel_ans)` gives an outcome of `True` if the variable taken by an entry in the column `reason_not_apply1` is one of the values in `sel_ans`. In that case, it sets the value of `credit_constrained` to ‘Yes’ for those observations.


```python
stab_four = pd.crosstab(
    all_hh_c["credit_constrained"],
    all_hh_c["discouraged_borrower"],
    margins=True,
    normalize="all",
).round(3)
stab_four
```


<div>
<style scoped>
    .dataframe tbody tr th:only-of-type {
        vertical-align: middle;
    }


.dataframe tbody tr th {
    vertical-align: top;
}


.dataframe thead th {
    text-align: right;
}
</style>
<table border="1" class="dataframe">
  <thead>
    <tr style="text-align: right;">
      <th>discouraged_borrower</th>
      <th>No</th>
      <th>Yes</th>
      <th>All</th>
    </tr>
    <tr>
      <th>credit_constrained</th>
      <th></th>
      <th></th>
      <th></th>
    </tr>
  </thead>
  <tbody>
    <tr>
      <th>No</th>
      <td>0.423</td>
      <td>0.000</td>
      <td>0.423</td>
    </tr>
    <tr>
      <th>Yes</th>
      <td>0.464</td>
      <td>0.112</td>
      <td>0.577</td>
    </tr>
    <tr>
      <th>All</th>
      <td>0.888</td>
      <td>0.112</td>
      <td>1.000</td>
    </tr>
  </tbody>
</table>
</div>


Let's look at the frequencies of the different reasons to not apply (`reason_not_apply1` and `reason_not_apply2`), in descending order.


```python
all_hh_c["reason_not_apply1"].value_counts(normalize=True).round(3)
```


```
reason_not_apply1
Do Not Like To Be In Debt    0.190
Have Adequate Farm           0.185
Fear Not Be Able To Pay      0.170
Believe Would Be Refused     0.116
No Farm Or Business          0.101
Do Not Know Any Lender       0.068
Too Expensive                0.050
Inadequate Collateral        0.046
Other (Specify)              0.041
Too Much Trouble             0.033
Name: proportion, dtype: float64
```


```python
all_hh_c["reason_not_apply2"].value_counts(normalize=True).round(3)
```


```
reason_not_apply2
Fear Not Be Able To Pay      0.276
Do Not Like To Be In Debt    0.244
Inadequate Collateral        0.087
Believe Would Be Refused     0.086
Too Expensive                0.067
Do Not Know Any Lender       0.063
Too Much Trouble             0.061
Have Adequate Farm           0.050
No Farm Or Business          0.039
Other (Specify)              0.024
Name: proportion, dtype: float64
```


### *Python walk-through 9.5* Making frequency tables to compare proportions


Some of the data is in the `all_hh` dataset, while the rest is in the `got_l` dataset, both of which we imported in Python walk-through 9.1. We will combine that information into one new dataset called `loan_data`, which we then use to produce the table.


```python
sel_all_hh_c = all_hh_c.loc[all_hh_c["hh_status"].isin(["successful", "denied"])]


pd.crosstab(
    sel_all_hh_c["loan_purpose"], sel_all_hh_c["hh_status"], margins=True, dropna=False
).round(3)
```


<div>
<style scoped>
    .dataframe tbody tr th:only-of-type {
        vertical-align: middle;
    }


    .dataframe tbody tr th {
        vertical-align: top;
    }


    .dataframe thead th {
        text-align: right;
    }
</style>
<table border="1" class="dataframe">
  <thead>
    <tr style="text-align: right;">
      <th>hh_status</th>
      <th>denied</th>
      <th>not applied</th>
      <th>successful</th>
      <th>All</th>
    </tr>
    <tr>
      <th>loan_purpose</th>
      <th></th>
      <th></th>
      <th></th>
      <th></th>
    </tr>
  </thead>
  <tbody>
    <tr>
      <th>10</th>
      <td>3</td>
      <td>0</td>
      <td>0</td>
      <td>3</td>
    </tr>
    <tr>
      <th>Business Start-Up Capital</th>
      <td>51</td>
      <td>0</td>
      <td>0</td>
      <td>51</td>
    </tr>
    <tr>
      <th>Expanding Business</th>
      <td>27</td>
      <td>0</td>
      <td>1</td>
      <td>28</td>
    </tr>
    <tr>
      <th>Other (Specify)</th>
      <td>50</td>
      <td>0</td>
      <td>0</td>
      <td>50</td>
    </tr>
    <tr>
      <th>Purchase Agricultural Inputs For Food Crop</th>
      <td>41</td>
      <td>0</td>
      <td>0</td>
      <td>41</td>
    </tr>
    <tr>
      <th>Purchase House/Lease Land</th>
      <td>6</td>
      <td>0</td>
      <td>0</td>
      <td>6</td>
    </tr>
    <tr>
      <th>Purchase Inputs For Other Crops</th>
      <td>12</td>
      <td>0</td>
      <td>0</td>
      <td>12</td>
    </tr>
    <tr>
      <th>Purchase Non-Farm Inputs</th>
      <td>5</td>
      <td>0</td>
      <td>0</td>
      <td>5</td>
    </tr>
    <tr>
      <th>All</th>
      <td>201</td>
      <td>0</td>
      <td>1363</td>
      <td>1564</td>
    </tr>
  </tbody>
</table>
</div>


This table reveals a particular feature of the data: it doesn't contain as much useful information as we'd like! Most successful loans actually have ‘NA’ in the box for loan purpose (except one, which has "Expanding Business"). The cross-tab above shows us that there were 1,363 successful loans but only one with a purpose.


There is more useful information on loan purpose in the `got_l` data, so we will extract the `loan_purpose` variable for unsuccessful households from the `all_hh_c` dataset, and the equivalent information for successful loaners from the `got_l` dataset.


```python
# Select unsuccessful households from all_hh_c
loan_no = all_hh_c.loc[all_hh_c["hh_status"] == "denied", ["loan_purpose", "hh_status"]]


# Select loan purpose for successful households from gotL
loan_yes = got_l.loc[got_l["got_loan"] == "Yes", ["loan_purpose"]]
loan_yes["hh_status"] = "successful"


# combine the data through concatenation
loan_data = pd.concat([loan_yes, loan_no])


# let's look at the values
pd.crosstab(
    loan_data["loan_purpose"], loan_data["hh_status"], normalize="columns"
).round(3)
```


<div>
<style scoped>
    .dataframe tbody tr th:only-of-type {
        vertical-align: middle;
    }
.dataframe tbody tr th {
    vertical-align: top;
}
.dataframe thead th {
    text-align: right;
}
</style>
<table border="1" class="dataframe">
  <thead>
    <tr style="text-align: right;">
      <th>hh_status</th>
      <th>denied</th>
      <th>successful</th>
    </tr>
    <tr>
      <th>loan_purpose</th>
      <th></th>
      <th></th>
    </tr>
  </thead>
  <tbody>
    <tr>
      <th>10</th>
      <td>0.015</td>
      <td>0.000</td>
    </tr>
    <tr>
      <th>Business Start-Up Capital</th>
      <td>0.262</td>
      <td>0.154</td>
    </tr>
    <tr>
      <th>Expanding Business</th>
      <td>0.138</td>
      <td>0.081</td>
    </tr>
    <tr>
      <th>For Consumption And Personal Expenses</th>
      <td>0.000</td>
      <td>0.201</td>
    </tr>
    <tr>
      <th>Other (Specify)</th>
      <td>0.256</td>
      <td>0.027</td>
    </tr>
    <tr>
      <th>Purchase Agricultural Inputs For Food Crop</th>
      <td>0.210</td>
      <td>0.300</td>
    </tr>
    <tr>
      <th>Purchase House/Lease Land</th>
      <td>0.031</td>
      <td>0.023</td>
    </tr>
    <tr>
      <th>Purchase Inputs For Other Crops</th>
      <td>0.062</td>
      <td>0.098</td>
    </tr>
    <tr>
      <th>Purchase Non-Farm Inputs</th>
      <td>0.026</td>
      <td>0.115</td>
    </tr>
  </tbody>
</table>
</div>


### *Python walk-through 9.6* Calculating differences in household characteristics


Here we show how to use the `mean` function to get average characteristics conditional on `"hh_status"`.


```python
# Show the number of observations in each category
all_hh_c["hh_status"].value_counts()
```


```
hh_status
not applied    3674
successful     1363
denied          201
Name: count, dtype: int64
```


Now let's look at mean household size conditional on credit status:


```python
all_hh_c.groupby("hh_status").mean(numeric_only=True)["hhsize"].round(2)
```


```
hh_status
denied         4.82
not applied    4.46
successful     4.87
Name: hhsize, dtype: float64
```


What about the mean `max_education` of household head by credit status?


```python
all_hh_c.groupby("hh_status").mean(numeric_only=True)["max_education"].round(2)
```


```
hh_status
denied         8.00
not applied    7.61
successful     7.26
Name: max_education, dtype: float64
```


As before, we can use cross-tabs to see the number of observations in each category:


```python
pd.crosstab(
    all_hh_c["rural"],
    all_hh_c["hh_status"],
).round(3)
```


<div>
<style scoped>
    .dataframe tbody tr th:only-of-type {
        vertical-align: middle;
    }


```
.dataframe tbody tr th {
    vertical-align: top;
}


.dataframe thead th {
    text-align: right;
}
```


</style>
<table border="1" class="dataframe">
  <thead>
    <tr style="text-align: right;">
      <th>hh_status</th>
      <th>denied</th>
      <th>not applied</th>
      <th>successful</th>
    </tr>
    <tr>
      <th>rural</th>
      <th></th>
      <th></th>
      <th></th>
    </tr>
  </thead>
  <tbody>
    <tr>
      <th>Large town (urban)</th>
      <td>56</td>
      <td>1096</td>
      <td>332</td>
    </tr>
    <tr>
      <th>Rural</th>
      <td>128</td>
      <td>2272</td>
      <td>903</td>
    </tr>
    <tr>
      <th>Small town (urban)</th>
      <td>17</td>
      <td>306</td>
      <td>128</td>
    </tr>
  </tbody>
</table>
</div>


To get even more breakdowns, you can add more variables to the `groupby`.


Let's see an example with mean household size by the other variables, rural and credit status.


```python
all_hh_c.groupby(["hh_status", "rural"]).mean(numeric_only=True)["hhsize"].round(2)
```


```
hh_status    rural             
denied       Large town (urban)    3.57
             Rural                 5.47
             Small town (urban)    4.06
not applied  Large town (urban)    3.44
             Rural                 4.98
             Small town (urban)    4.27
successful   Large town (urban)    3.84
             Rural                 5.35
             Small town (urban)    4.12
Name: hhsize, dtype: float64
```


Or we could make a table showing the number of working-age adults by the rural and credit variables:


```python
all_hh_c.groupby(["hh_status", "rural"]).mean(numeric_only=True)[
    "working_age_adults"
].round(2)
```


```
hh_status    rural             
denied       Large town (urban)    2.36
             Rural                 2.88
             Small town (urban)    3.18
not applied  Large town (urban)    2.30
             Rural                 2.58
             Small town (urban)    2.75
successful   Large town (urban)    2.42
             Rural                 2.91
             Small town (urban)    2.54
Name: working_age_adults, dtype: float64
```


### *Python walk-through 9.7* Calculating confidence intervals and adding them to a chart


To repeat the same set of calculations for a list of variables, first we create a list of these variables (called `sel_var`).


```python
sel_var = [
    "age",
    "max_education",
    "number_assets",
    "hhsize",
    "young_children",
    "working_age_adults",
]
```


Now we use the `age` variable as an example, removing the 'did not apply' (`"not applied"`) entries.


```python
stats_5 = (
    all_hh_c.groupby("hh_status")["age"]
    .agg({"mean", "count", "std"})
    .drop("not applied")
    .round(2)
)
stats_5
```


<div>
<style scoped>
    .dataframe tbody tr th:only-of-type {
        vertical-align: middle;
    }
.dataframe tbody tr th {
    vertical-align: top;
}
.dataframe thead th {
    text-align: right;
}
</style>
<table border="1" class="dataframe">
  <thead>
    <tr style="text-align: right;">
      <th></th>
      <th>mean</th>
      <th>count</th>
      <th>std</th>
    </tr>
    <tr>
      <th>hh_status</th>
      <th></th>
      <th></th>
      <th></th>
    </tr>
  </thead>
  <tbody>
    <tr>
      <th>denied</th>
      <td>41.21</td>
      <td>201</td>
      <td>12.85</td>
    </tr>
    <tr>
      <th>successful</th>
      <td>43.37</td>
      <td>1361</td>
      <td>14.27</td>
    </tr>
  </tbody>
</table>
</div>


Now we use the `ttest` function from the **pingouin** package to calculate the difference between the successful group (`sel_success`) and the denied borrowers (`sel_denied`).


```python
import pingouin as pg


# Select the age variable (aka sel_var[0]) for successful and
# denied borrowers
sel_success = all_hh_c.loc[all_hh_c["hh_status"] == "successful", sel_var[0]]
sel_denied = all_hh_c.loc[all_hh_c["hh_status"] == "denied", sel_var[0]]


# do the t-test. Default confidence is 0.95, but include it here to be explicit
pg.ttest(x=sel_success, y=sel_denied, confidence=0.95).round(3)
```


<div>
<style scoped>
    .dataframe tbody tr th:only-of-type {
        vertical-align: middle;
    }
.dataframe tbody tr th {
    vertical-align: top;
}
.dataframe thead th {
    text-align: right;
}
</style>
<table border="1" class="dataframe">
  <thead>
    <tr style="text-align: right;">
      <th></th>
      <th>T</th>
      <th>dof</th>
      <th>alternative</th>
      <th>p-val</th>
      <th>CI95%</th>
      <th>cohen-d</th>
      <th>BF10</th>
      <th>power</th>
    </tr>
  </thead>
  <tbody>
    <tr>
      <th>T-test</th>
      <td>2.186</td>
      <td>278.073</td>
      <td>two-sided</td>
      <td>0.03</td>
      <td>[0.21, 4.09]</td>
      <td>0.153</td>
      <td>0.872</td>
      <td>0.525</td>
    </tr>
  </tbody>
</table>
</div>


The output of this test indicates whether the ages of the two groups are statistically different. (Here, they are.)


We will now do this for all variables of interest and save the difference in means and the confidence interval values in a dataframe so we can plot this information.


To make this easier, we'll write a function that:


- takes the name of the variable of interest as function
- selects successful and unsuccessful applicants, and performs a t-test on that variable (like we already did for age)
- returns the answers (the difference in means and the upper and lower confidence limits) in a format useful for populating a dataframe of results.


We will create an empty dataframe to populate with this information and then run the whole task in a loop.


```python
def get_ttest_and_mean_for_variable(dataframe_variable, selected_variable):
    """Given a dataframe with loan statuses encoded by a "hh_status" column
    with values of "successful" and "denied", and columns that have other
    relevant characteristics, this function returns the mean difference between
    the successful and denied households according to the characteristic given
    by the 'selected_variable'.


    Args:
        dataframe_name (pandas dataframe): Data containing loan outcomes.
        selected_variable (string): Name of other characteristic.


    Returns:
        list (floats): Mean difference, low limit conf int, high limit conf int
    """
    # Select the variable for successful and
    # denied borrowers
    sel_success = dataframe_variable.loc[
        dataframe_variable["hh_status"] == "successful", selected_variable
    ]
    sel_denied = dataframe_variable.loc[
        dataframe_variable["hh_status"] == "denied", selected_variable
    ]


    # Do the t-test. Default confidence is 0.95, but include it here to be explicit
    pg.ttest(x=sel_success, y=sel_denied, confidence=0.95).round(3)


    mean_difference = sel_success.mean() - sel_denied.mean()
    mean_low, mean_high = (
        pg.ttest(x=sel_success, y=sel_denied, confidence=0.95)
        .round(3)["CI95%"]
        .explode()
        .to_list()
    )
    return mean_difference, mean_low, mean_high




# create an empty dataframe for the results
data_to_plot = pd.DataFrame()


# Now we can loop through the variables
for this_variable in sel_var:
    mean_diff, low, high = get_ttest_and_mean_for_variable(all_hh_c, this_variable)
    temp_data = pd.DataFrame.from_dict(
        {
            "var_name": this_variable,
            "mean_difference": mean_diff,
            "conf_low": low,
            "conf_high": high,
        },
        orient="index",
    ).T
    data_to_plot = pd.concat([temp_data, data_to_plot], axis=0)


# give different rows different index numbers (dropping old index)
data_to_plot = data_to_plot.reset_index(drop=True)
data_to_plot.head()
```


<div>
<style scoped>
    .dataframe tbody tr th:only-of-type {
        vertical-align: middle;
    }
.dataframe tbody tr th {
    vertical-align: top;
}
.dataframe thead th {
    text-align: right;
}
</style>
<table border="1" class="dataframe">
  <thead>
    <tr style="text-align: right;">
      <th></th>
      <th>var_name</th>
      <th>mean_difference</th>
      <th>conf_low</th>
      <th>conf_high</th>
    </tr>
  </thead>
  <tbody>
    <tr>
      <th>0</th>
      <td>working_age_adults</td>
      <td>-0.003468</td>
      <td>-0.22</td>
      <td>0.21</td>
    </tr>
    <tr>
      <th>1</th>
      <td>young_children</td>
      <td>-0.135106</td>
      <td>-0.4</td>
      <td>0.13</td>
    </tr>
    <tr>
      <th>2</th>
      <td>hhsize</td>
      <td>0.046946</td>
      <td>-0.3</td>
      <td>0.4</td>
    </tr>
    <tr>
      <th>3</th>
      <td>number_assets</td>
      <td>1.421393</td>
      <td>-1.13</td>
      <td>3.97</td>
    </tr>
    <tr>
      <th>4</th>
      <td>max_education</td>
      <td>-0.73457</td>
      <td>-1.89</td>
      <td>0.42</td>
    </tr>
  </tbody>
</table>
</div>


Now we can plot the chart using **lets-plot**.


```python
# Rename the rows in `var_name` so the y-axis labels will look neater
data_to_plot = data_to_plot.replace(
    {
        "age": "Age",
        "max_education": "Max education",
        "number_assets": "Number of assets",
        "hhsize": "Household size",
        "young_children": "Young children",
        "working_age_adults": "Working-age adults"
    }
)


(
    ggplot(
        data_to_plot.reset_index(),
        aes(
            x="var_name",
            y="mean_difference",
            fill="var_name",
        ),
    )
    + geom_bar(stat="identity", show_legend=False, color="black", alpha=0.6)
    + geom_errorbar(
        aes(ymin="conf_low", ymax="conf_high", color="var_name"),
        size=2,
        show_legend=False,
    )
    + labs(
        y="Mean difference", x="Variable name"
    )
    + coord_flip()
)
```


![png](empirical_project_9_files/empirical_project_9_WT7_1.png)


**Figure 9.2** Bar chart showing difference in household characteristics for successful and denied borrowers.




### *Python walk-through 9.8* Calculating conditional means


We are interested in the means of a range of variables for different subgroups. Two subgroups are mutually exclusive (`hh_status` == "successful" and `hh_status` == "denied"), while the others (`credit_constrained` == "yes" and `discouraged_borrower` == "yes") are partially overlapping subgroups of the data. Our strategy is to create a temporary dataframe (`sel_all_hh_c`) that only contains the relevant observations and the relevant variables. Then we can calculate the required means using the built-in `.mean` method of **pandas**.


```python
# variables we are interested in
sel_var = [
    "age",
    "max_education",
    "number_assets",
    "hhsize",
    "young_children",
    "working_age_adults",
]


sel_all_hh_c = all_hh_c.loc[all_hh_c["hh_status"] == "successful", sel_var]


print(f"Successful, n = {len(sel_all_hh_c)}")
```


```
Successful, n = 1363
```


Now we get the means of each column:


```python
sel_all_hh_c.mean(axis="index").round(2)
```


```
age                   43.37
max_education          7.26
number_assets         15.88
hhsize                 4.87
young_children         2.09
working_age_adults     2.75
dtype: float64
```


We can perform the same operation with `denied`:


```python
sel_all_hh_c = all_hh_c.loc[all_hh_c["hh_status"] == "denied", sel_var]


print(f"Denied, n = {len(sel_all_hh_c)}")
```


```
Denied, n = 201
```


You can re-calculate the conditional means based on this dimension too:


```python
sel_all_hh_c.mean(axis="index").round(2)
```


```
age                   41.21
max_education          8.00
number_assets         14.46
hhsize                 4.82
young_children         2.22
working_age_adults     2.76
dtype: float64
```


You can use similar methods to look at discouraged and credit constrained households.




### *Python walk-through 9.9* Data cleaning and summarising loan characteristics


We start by cleaning up the loan dates. We have information on start month and year as well as end month and year. Let's look at these in turn. The structure of the dataframe `got_l.info()` indicates that the start and end year are numeric variables, but the months are factor variables with month names (for example 'April').


Let's first look at the years by creating a scatterplot.


```python
(
    ggplot(got_l, aes(x="loan_endyear", y="loan_startyear"))
    + geom_point(size=6)
    + labs(
        title="Loan start and end year", y="Loan start year", x="Loan end year"
    )
    + scale_x_continuous(format="d")
    + scale_y_continuous(format="d")
)
```


![png](empirical_project_9_files/empirical_project_9_WT9_1.png)
 
**Figure 9.3** Scatterplot showing loan start and end year.


We can see that there are three observations that have very low start or end year values (less than 500), which does not make sense. We will replace these with `pd.NA`, but leave the original data untouched and create a new dataset called `got_l_c`, where the ‘c’ indicates cleaned data.


```python
got_l_c = got_l.copy()
got_l_c.loc[
    (got_l_c["loan_startyear"] < 500) | (got_l_c["loan_endyear"] < 500),
    ["loan_startyear", "loan_endyear"],
] = pd.NA
```


```python
(
    ggplot(got_l_c, aes(x="loan_endyear", y="loan_startyear"))
    + geom_point()
    + labs(
        title="Loan start and end year", y="Loan start year", x="Loan end year"
    )
    + scale_x_continuous(format="d")
    + scale_y_continuous(format="d")
)
```


![png](empirical_project_9_files/empirical_project_9_WT9_2.png)


**Figure 9.4** Revised scatterplot showing loan start and end year without outliers.


In the top-left corner, there is a loan with the start year (2006) after the end year (2003). Clearly this data entry is incorrect, so we should remove this observation when analysing loan periods. However, we wait until we have combined the years with the months as there may be more observations with this issue.


Also, we can only see a small number of points because there are many identical observations (for example, a start year of 2006 and end year of 2006). To see these points you can add `position=position_jitter()` to the **lets-plot** plotting command. Hover your mouse over the function within an integrated development environment to see what `position_jitter()` does.


```python
(
    ggplot(got_l_c, aes(x="loan_endyear", y="loan_startyear"))
    + geom_point(position=position_jitter())
    + labs(
        title="Loan start and end year", y="Loan start year", x="Loan end year"
    )
    + scale_x_continuous(format="d")
    + scale_y_continuous(format="d")
)
```


** Figure here ** 
![png](empirical_project_9_files/empirical_project_9_WT9_3.png)


**Figure 9.5** Revised scatterplot of loan start and end year, with jitter added to data points.


Now let’s look at the values in `loan_startmonth`. We'll keep any null (`NA`) values because we're interested in missing observations here.


```python
got_l_c["loan_startmonth"].value_counts(dropna=False)
```


```
loan_startmonth
January      176
June         156
September    146
February     145
May          141
December     133
July         115
November     115
October      106
April         95
March         85
August        63
NaN            4
Name: count, dtype: int64
```


This dtaa all looks fine apart from the `NaN` (not a number) entries; what about the end months?


```python
got_l_c["loan_endmonth"].value_counts(dropna=False)
```


```
loan_endmonth
NaN          534
February     170
March        155
April        136
June          94
May           89
August        53
January       52
December      49
October       39
September     37
July          34
November      33
Pagume         5
Name: count, dtype: int64
```


Two things are noteworthy here: there are many `NaN` entries, and there is an entry called ‘Pagume’. As described in the task, ‘Pagume’ can be approximated by September. Let's recode that accordingly, and recode the `NaNs` to ‘Missing’.


```python
got_l_c.loc[got_l_c["loan_endmonth"] == "Pagume", "loan_endmonth"] = "September"
for col in ["loan_startmonth", "loan_endmonth"]:
    # add a 'missing' category
    got_l_c[col] = got_l_c[col].cat.add_categories("missing")
    # fill na with 'missing'
    got_l_c[col] = got_l_c[col].fillna("missing")


for col in ["loan_startyear", "loan_endyear"]:
    # first convert to integer to remove .0 at end
    # then to string, with missing replacing nans
    got_l_c[col] = got_l_c[col].astype("Int64").astype("string").fillna("missing")
```


You can check that the end months now have sensible entries for the valid rows.


Let's now calculate the length of the loan; in other words, the number of days between start and end day. **pandas** has very powerful functionality for dates and times. Our first step is to create a new variable that combines months and years together. We can do this by ‘casting’ (using `.astype`) columns as strings and then using `pd.to_datetime`. We have to coerce any `NaN` values, otherwise we will get an error.


Here's an example (you may get a ‘user warning’ from Python, which you can ignore):


```python
pd.to_datetime(
    got_l_c["loan_startmonth"].astype("str") + "-" + got_l_c["loan_startyear"],
    errors="coerce",
)
```


```
0      2004-03-01
1      2006-11-01
2      2006-11-01
3      2005-06-01
4      2005-06-01
          ...    
1475   2005-08-01
1476   2006-09-01
1477   2006-01-01
1478   2005-07-01
1479   2006-12-01
Length: 1480, dtype: datetime64[ns]
```


Note that it is assumed that we're counting from the first day of each month. `pd.to_datetime` is converting whatever we feed it to the closest *datetime* that is similar. A *datetime* is a special type of variable in computer science: it encodes year, month, day, hours, minutes and seconds. Timing is important, so this variable type is incredibly useful!


`pd.to_datetime` will always make a best guess based on what you put in. See what happens if you try putting in a string like ‘15-March-2004’ instead. If you *don't* want your date to be at the start of the month, you can 'add' the month end on to the date using `+ pd.offsets.MonthEnd()`.


Now let's put the months into the dataframe (using start month as the default):


```python
got_l_c["loan_start_datetime"] = pd.to_datetime(
    got_l_c["loan_startmonth"].astype("str") + "-" + got_l_c["loan_startyear"],
    errors="coerce",
)
got_l_c["loan_end_datetime"] = pd.to_datetime(
    got_l_c["loan_endmonth"].astype("str") + "-" + got_l_c["loan_endyear"],
    errors="coerce",
)
```


Let's assess how much missing data we have (as a proportion of the categories we're interested in):


```python
summary_nans = (
    got_l_c.isna()
    .agg(["sum", "count"])
    .loc[:, ["loan_start_datetime", "loan_end_datetime"]]
    .T
)
summary_nans["pct"] = 100 * summary_nans["sum"] / summary_nans["count"]
summary_nans.round(2)
```


<div>
<style scoped>
    .dataframe tbody tr th:only-of-type {
        vertical-align: middle;
    }
.dataframe tbody tr th {
    vertical-align: top;
}
.dataframe thead th {
    text-align: right;
}
</style>
<table border="1" class="dataframe">
  <thead>
    <tr style="text-align: right;">
      <th></th>
      <th>sum</th>
      <th>count</th>
      <th>pct</th>
    </tr>
  </thead>
  <tbody>
    <tr>
      <th>loan_start_datetime</th>
      <td>7</td>
      <td>1480</td>
      <td>0.47</td>
    </tr>
    <tr>
      <th>loan_end_datetime</th>
      <td>537</td>
      <td>1480</td>
      <td>36.28</td>
    </tr>
  </tbody>
</table>
</div>


So we now have start and end dates in datetime formats. We now compute the difference:


```python
got_l_c["loan_length"] = got_l_c["loan_end_datetime"] - got_l_c["loan_start_datetime"]
got_l_c["loan_length"].head()
```


```
0         NaT
1         NaT
2   -153 days
3    273 days
4    245 days
Name: loan_length, dtype: timedelta64[ns]
```


Note the following:


- We are missing some loans that didn't have start or end dates, which have appeared as 'NaT', i.e. 'Not a Time'.
- Some loan lengths are negative because the recorded end date is before the start date. (It could be that the two dates were switched when the data was entered into the system.)


These data problems are unfortunate but a common feature of real-life empirical work, and you will have to be on the lookout for them!


As required in Question 1, we will create two variants of the `loan_length` variable: one where we assign missing values to all observations that have negative `loan_length` (`loan_length_na`), and one where we assume that the problem was the switching of start and end date, so we transform all loan lengths to positive values (`loan_length_abs`).


```python
# create the missing values version
got_l_c["loan_length_na"] = got_l_c["loan_length"].copy()
# set anything less than 0 days to ‘NaT’
got_l_c.loc[got_l_c["loan_length_na"] < pd.Timedelta(0, "d"), "loan_length_na"] = pd.NaT


# create the absolute version
got_l_c["loan_length_abs"] = got_l_c["loan_length"].copy()
# set anything less than 0 days to its reverse
got_l_c.loc[
    got_l_c["loan_length_abs"] < pd.Timedelta(0, "d"), "loan_length_abs"
] = -got_l_c.loc[got_l_c["loan_length_abs"] < pd.Timedelta(0, "d"), "loan_length_abs"]
```


Now we can create the `long_term` variable and look at the number of long-term loans.


```python
got_l_c.loc[got_l_c["loan_length_abs"].isna(), "long_term"] = pd.NA
got_l_c.loc[got_l_c["loan_length_abs"] > pd.Timedelta(365, "d"), "long_term"] = True
got_l_c.loc[got_l_c["loan_length_abs"] < pd.Timedelta(365, "d"), "long_term"] = False


got_l_c["long_term"].value_counts(dropna=False)
```


```
long_term
NaN      742
False    523
True     215
Name: count, dtype: int64
```


We therefore have about 23% loans that are long-term (only looking at loans for which we do have date information).


### *Python walk-through 9.10* Making summary tables and calculating correlations


To make summary tables, we use the `skim` function from the **skimpy** package.


```python
from skimpy import skim


skim(got_l_c)
```


<pre style="white-space:pre;overflow-x:auto;line-height:normal;font-family:Menlo,'DejaVu Sans Mono',consolas,'Courier New',monospace">╭───────────────────────────────────── skimpy summary ──────────────────────────────────────╮
│ <span style="font-style: italic">         Data Summary         </span> <span style="font-style: italic">      Data Types       </span> <span style="font-style: italic">       Categories        </span>          │
│ ┏━━━━━━━━━━━━━━━━━━━┳━━━━━━━━┓ ┏━━━━━━━━━━━━━┳━━━━━━━┓ ┏━━━━━━━━━━━━━━━━━━━━━━━┓          │
│ ┃<span style="color: #008080; text-decoration-color: #008080; font-weight: bold"> dataframe         </span>┃<span style="color: #008080; text-decoration-color: #008080; font-weight: bold"> Values </span>┃ ┃<span style="color: #008080; text-decoration-color: #008080; font-weight: bold"> Column Type </span>┃<span style="color: #008080; text-decoration-color: #008080; font-weight: bold"> Count </span>┃ ┃<span style="color: #008080; text-decoration-color: #008080; font-weight: bold"> Categorical Variables </span>┃          │
│ ┡━━━━━━━━━━━━━━━━━━━╇━━━━━━━━┩ ┡━━━━━━━━━━━━━╇━━━━━━━┩ ┡━━━━━━━━━━━━━━━━━━━━━━━┩          │
│ │ Number of rows    │ 1480   │ │ category    │ 10    │ │ got_loan              │          │
│ │ Number of columns │ 27     │ │ float64     │ 5     │ │ rural                 │          │
│ └───────────────────┴────────┘ │ int64       │ 4     │ │ region                │          │
│                                │ timedelta64 │ 3     │ │ gender                │          │
│                                │ string      │ 2     │ │ borrowed_from         │          │
│                                │ datetime64  │ 2     │ │ borrowed_from_other   │          │
│                                │ bool        │ 1     │ │ loan_purpose          │          │
│                                └─────────────┴───────┘ │ loan_startmonth       │          │
│                                                        │ loan_repaid           │          │
│                                                        │ loan_endmonth         │          │
│                                                        └───────────────────────┘          │
│ <span style="font-style: italic">                                         number                                         </span>  │
│ ┏━━━━━━━━━━┳━━━━┳━━━━━━┳━━━━━━━━━┳━━━━━━━━━┳━━━━━━━┳━━━━━━━┳━━━━━━━┳━━━━━━━━━━┳━━━━━━━━┓  │
│ ┃<span style="font-weight: bold"> column_n </span>┃<span style="font-weight: bold"> NA </span>┃<span style="font-weight: bold"> NA % </span>┃<span style="font-weight: bold"> mean    </span>┃<span style="font-weight: bold"> sd      </span>┃<span style="font-weight: bold"> p0    </span>┃<span style="font-weight: bold"> p25   </span>┃<span style="font-weight: bold"> p75   </span>┃<span style="font-weight: bold"> p100     </span>┃<span style="font-weight: bold"> hist   </span>┃  │
│ ┃<span style="font-weight: bold"> ame      </span>┃    ┃      ┃         ┃         ┃       ┃       ┃       ┃          ┃        ┃  │
│ ┡━━━━━━━━━━╇━━━━╇━━━━━━╇━━━━━━━━━╇━━━━━━━━━╇━━━━━━━╇━━━━━━━╇━━━━━━━╇━━━━━━━━━━╇━━━━━━━━┩  │
│ │ <span style="color: #af87ff; text-decoration-color: #af87ff">househol</span> │ <span style="color: #008080; text-decoration-color: #008080"> 0</span> │ <span style="color: #008080; text-decoration-color: #008080">   0</span> │ <span style="color: #008080; text-decoration-color: #008080">4.7e+16</span> │ <span style="color: #008080; text-decoration-color: #008080">3.5e+16</span> │ <span style="color: #008080; text-decoration-color: #008080">1e+16</span> │ <span style="color: #008080; text-decoration-color: #008080">3e+16</span> │ <span style="color: #008080; text-decoration-color: #008080">7e+16</span> │ <span style="color: #008080; text-decoration-color: #008080"> 1.5e+17</span> │ <span style="color: #008000; text-decoration-color: #008000">█▄▄  ▁</span> │  │
│ │ <span style="color: #af87ff; text-decoration-color: #af87ff">d_id2   </span> │    │      │         │         │       │       │       │          │        │  │
│ │ <span style="color: #af87ff; text-decoration-color: #af87ff">hhsize  </span> │ <span style="color: #008080; text-decoration-color: #008080"> 2</span> │ <span style="color: #008080; text-decoration-color: #008080">0.14</span> │ <span style="color: #008080; text-decoration-color: #008080">      5</span> │ <span style="color: #008080; text-decoration-color: #008080">    2.3</span> │ <span style="color: #008080; text-decoration-color: #008080">    1</span> │ <span style="color: #008080; text-decoration-color: #008080">    3</span> │ <span style="color: #008080; text-decoration-color: #008080">    6</span> │ <span style="color: #008080; text-decoration-color: #008080">      15</span> │ <span style="color: #008000; text-decoration-color: #008000"> ▇█▇▃ </span> │  │
│ │ <span style="color: #af87ff; text-decoration-color: #af87ff">age     </span> │ <span style="color: #008080; text-decoration-color: #008080"> 1</span> │ <span style="color: #008080; text-decoration-color: #008080">0.07</span> │ <span style="color: #008080; text-decoration-color: #008080">     43</span> │ <span style="color: #008080; text-decoration-color: #008080">     14</span> │ <span style="color: #008080; text-decoration-color: #008080">    4</span> │ <span style="color: #008080; text-decoration-color: #008080">   33</span> │ <span style="color: #008080; text-decoration-color: #008080">   52</span> │ <span style="color: #008080; text-decoration-color: #008080">      99</span> │ <span style="color: #008000; text-decoration-color: #008000"> ▆█▄▁ </span> │  │
│ │ <span style="color: #af87ff; text-decoration-color: #af87ff">young_ch</span> │ <span style="color: #008080; text-decoration-color: #008080"> 0</span> │ <span style="color: #008080; text-decoration-color: #008080">   0</span> │ <span style="color: #008080; text-decoration-color: #008080">    2.2</span> │ <span style="color: #008080; text-decoration-color: #008080">    1.7</span> │ <span style="color: #008080; text-decoration-color: #008080">    0</span> │ <span style="color: #008080; text-decoration-color: #008080">    1</span> │ <span style="color: #008080; text-decoration-color: #008080">    3</span> │ <span style="color: #008080; text-decoration-color: #008080">       9</span> │ <span style="color: #008000; text-decoration-color: #008000">█▅▇▁▁ </span> │  │
│ │ <span style="color: #af87ff; text-decoration-color: #af87ff">ildren  </span> │    │      │         │         │       │       │       │          │        │  │
│ │ <span style="color: #af87ff; text-decoration-color: #af87ff">working_</span> │ <span style="color: #008080; text-decoration-color: #008080"> 0</span> │ <span style="color: #008080; text-decoration-color: #008080">   0</span> │ <span style="color: #008080; text-decoration-color: #008080">    2.8</span> │ <span style="color: #008080; text-decoration-color: #008080">    1.5</span> │ <span style="color: #008080; text-decoration-color: #008080">    0</span> │ <span style="color: #008080; text-decoration-color: #008080">    2</span> │ <span style="color: #008080; text-decoration-color: #008080">    4</span> │ <span style="color: #008080; text-decoration-color: #008080">      10</span> │ <span style="color: #008000; text-decoration-color: #008000"> ▂█▂▁ </span> │  │
│ │ <span style="color: #af87ff; text-decoration-color: #af87ff">age_adul</span> │    │      │         │         │       │       │       │          │        │  │
│ │ <span style="color: #af87ff; text-decoration-color: #af87ff">ts      </span> │    │      │         │         │       │       │       │          │        │  │
│ │ <span style="color: #af87ff; text-decoration-color: #af87ff">max_educ</span> │ <span style="color: #008080; text-decoration-color: #008080"> 0</span> │ <span style="color: #008080; text-decoration-color: #008080">   0</span> │ <span style="color: #008080; text-decoration-color: #008080">    7.1</span> │ <span style="color: #008080; text-decoration-color: #008080">    6.6</span> │ <span style="color: #008080; text-decoration-color: #008080">    0</span> │ <span style="color: #008080; text-decoration-color: #008080">    3</span> │ <span style="color: #008080; text-decoration-color: #008080">    9</span> │ <span style="color: #008080; text-decoration-color: #008080">      30</span> │ <span style="color: #008000; text-decoration-color: #008000">██▃▁ ▁</span> │  │
│ │ <span style="color: #af87ff; text-decoration-color: #af87ff">ation   </span> │    │      │         │         │       │       │       │          │        │  │
│ │ <span style="color: #af87ff; text-decoration-color: #af87ff">number_a</span> │ <span style="color: #008080; text-decoration-color: #008080"> 0</span> │ <span style="color: #008080; text-decoration-color: #008080">   0</span> │ <span style="color: #008080; text-decoration-color: #008080">     16</span> │ <span style="color: #008080; text-decoration-color: #008080">     19</span> │ <span style="color: #008080; text-decoration-color: #008080">    0</span> │ <span style="color: #008080; text-decoration-color: #008080">    6</span> │ <span style="color: #008080; text-decoration-color: #008080">   19</span> │ <span style="color: #008080; text-decoration-color: #008080">     200</span> │ <span style="color: #008000; text-decoration-color: #008000">  █▁  </span> │  │
│ │ <span style="color: #af87ff; text-decoration-color: #af87ff">ssets   </span> │    │      │         │         │       │       │       │          │        │  │
│ │ <span style="color: #af87ff; text-decoration-color: #af87ff">loan_amo</span> │ <span style="color: #008080; text-decoration-color: #008080"> 1</span> │ <span style="color: #008080; text-decoration-color: #008080">0.07</span> │ <span style="color: #008080; text-decoration-color: #008080">  27000</span> │ <span style="color: #008080; text-decoration-color: #008080"> 780000</span> │ <span style="color: #008080; text-decoration-color: #008080">    1</span> │ <span style="color: #008080; text-decoration-color: #008080">  400</span> │ <span style="color: #008080; text-decoration-color: #008080"> 3500</span> │ <span style="color: #008080; text-decoration-color: #008080">30000000</span> │ <span style="color: #008000; text-decoration-color: #008000">  █   </span> │  │
│ │ <span style="color: #af87ff; text-decoration-color: #af87ff">unt     </span> │    │      │         │         │       │       │       │          │        │  │
│ │ <span style="color: #af87ff; text-decoration-color: #af87ff">loan_int</span> │ <span style="color: #008080; text-decoration-color: #008080">35</span> │ <span style="color: #008080; text-decoration-color: #008080">2.36</span> │ <span style="color: #008080; text-decoration-color: #008080">   1800</span> │ <span style="color: #008080; text-decoration-color: #008080">  36000</span> │ <span style="color: #008080; text-decoration-color: #008080">    0</span> │ <span style="color: #008080; text-decoration-color: #008080">    0</span> │ <span style="color: #008080; text-decoration-color: #008080">  400</span> │ <span style="color: #008080; text-decoration-color: #008080"> 1300000</span> │ <span style="color: #008000; text-decoration-color: #008000">  █   </span> │  │
│ │ <span style="color: #af87ff; text-decoration-color: #af87ff">erest   </span> │    │      │         │         │       │       │       │          │        │  │
│ └──────────┴────┴──────┴─────────┴─────────┴───────┴───────┴───────┴──────────┴────────┘  │
│ <span style="font-style: italic">                                        category                                        </span>  │
│ ┏━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━┳━━━━━━━━━━┳━━━━━━━━━━━━┳━━━━━━━━━━━━━━┳━━━━━━━━━━━━┓  │
│ ┃<span style="font-weight: bold"> column_name                      </span>┃<span style="font-weight: bold"> NA       </span>┃<span style="font-weight: bold"> NA %       </span>┃<span style="font-weight: bold"> ordered      </span>┃<span style="font-weight: bold"> unique     </span>┃  │
│ ┡━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━╇━━━━━━━━━━╇━━━━━━━━━━━━╇━━━━━━━━━━━━━━╇━━━━━━━━━━━━┩  │
│ │ <span style="color: #af87ff; text-decoration-color: #af87ff">got_loan                        </span> │ <span style="color: #008080; text-decoration-color: #008080">      10</span> │ <span style="color: #008080; text-decoration-color: #008080">      0.68</span> │ <span style="color: #00d7ff; text-decoration-color: #00d7ff">False       </span> │ <span style="color: #008080; text-decoration-color: #008080">         3</span> │  │
│ │ <span style="color: #af87ff; text-decoration-color: #af87ff">rural                           </span> │ <span style="color: #008080; text-decoration-color: #008080">       0</span> │ <span style="color: #008080; text-decoration-color: #008080">         0</span> │ <span style="color: #00d7ff; text-decoration-color: #00d7ff">False       </span> │ <span style="color: #008080; text-decoration-color: #008080">         3</span> │  │
│ │ <span style="color: #af87ff; text-decoration-color: #af87ff">region                          </span> │ <span style="color: #008080; text-decoration-color: #008080">       0</span> │ <span style="color: #008080; text-decoration-color: #008080">         0</span> │ <span style="color: #00d7ff; text-decoration-color: #00d7ff">False       </span> │ <span style="color: #008080; text-decoration-color: #008080">        11</span> │  │
│ │ <span style="color: #af87ff; text-decoration-color: #af87ff">gender                          </span> │ <span style="color: #008080; text-decoration-color: #008080">       0</span> │ <span style="color: #008080; text-decoration-color: #008080">         0</span> │ <span style="color: #00d7ff; text-decoration-color: #00d7ff">False       </span> │ <span style="color: #008080; text-decoration-color: #008080">         2</span> │  │
│ │ <span style="color: #af87ff; text-decoration-color: #af87ff">borrowed_from                   </span> │ <span style="color: #008080; text-decoration-color: #008080">      10</span> │ <span style="color: #008080; text-decoration-color: #008080">      0.68</span> │ <span style="color: #00d7ff; text-decoration-color: #00d7ff">False       </span> │ <span style="color: #008080; text-decoration-color: #008080">        11</span> │  │
│ │ <span style="color: #af87ff; text-decoration-color: #af87ff">borrowed_from_other             </span> │ <span style="color: #008080; text-decoration-color: #008080">    1333</span> │ <span style="color: #008080; text-decoration-color: #008080">     90.07</span> │ <span style="color: #00d7ff; text-decoration-color: #00d7ff">False       </span> │ <span style="color: #008080; text-decoration-color: #008080">        16</span> │  │
│ │ <span style="color: #af87ff; text-decoration-color: #af87ff">loan_purpose                    </span> │ <span style="color: #008080; text-decoration-color: #008080">      36</span> │ <span style="color: #008080; text-decoration-color: #008080">      2.43</span> │ <span style="color: #00d7ff; text-decoration-color: #00d7ff">False       </span> │ <span style="color: #008080; text-decoration-color: #008080">         9</span> │  │
│ │ <span style="color: #af87ff; text-decoration-color: #af87ff">loan_startmonth                 </span> │ <span style="color: #008080; text-decoration-color: #008080">       0</span> │ <span style="color: #008080; text-decoration-color: #008080">         0</span> │ <span style="color: #00d7ff; text-decoration-color: #00d7ff">False       </span> │ <span style="color: #008080; text-decoration-color: #008080">        13</span> │  │
│ │ <span style="color: #af87ff; text-decoration-color: #af87ff">loan_repaid                     </span> │ <span style="color: #008080; text-decoration-color: #008080">       2</span> │ <span style="color: #008080; text-decoration-color: #008080">      0.14</span> │ <span style="color: #00d7ff; text-decoration-color: #00d7ff">False       </span> │ <span style="color: #008080; text-decoration-color: #008080">         3</span> │  │
│ │ <span style="color: #af87ff; text-decoration-color: #af87ff">loan_endmonth                   </span> │ <span style="color: #008080; text-decoration-color: #008080">       0</span> │ <span style="color: #008080; text-decoration-color: #008080">         0</span> │ <span style="color: #00d7ff; text-decoration-color: #00d7ff">False       </span> │ <span style="color: #008080; text-decoration-color: #008080">        13</span> │  │
│ └──────────────────────────────────┴──────────┴────────────┴──────────────┴────────────┘  │
│ <span style="font-style: italic">                                        datetime                                        </span>  │
│ ┏━━━━━━━━━━━━━━━━━━━━━━━━━━┳━━━━━━┳━━━━━━━━━┳━━━━━━━━━━━━━━┳━━━━━━━━━━━━━━┳━━━━━━━━━━━━┓  │
│ ┃<span style="font-weight: bold"> column_name              </span>┃<span style="font-weight: bold"> NA   </span>┃<span style="font-weight: bold"> NA %    </span>┃<span style="font-weight: bold"> first        </span>┃<span style="font-weight: bold"> last         </span>┃<span style="font-weight: bold"> frequency  </span>┃  │
│ ┡━━━━━━━━━━━━━━━━━━━━━━━━━━╇━━━━━━╇━━━━━━━━━╇━━━━━━━━━━━━━━╇━━━━━━━━━━━━━━╇━━━━━━━━━━━━┩  │
│ │ <span style="color: #af87ff; text-decoration-color: #af87ff">loan_start_datetime     </span> │ <span style="color: #008080; text-decoration-color: #008080">   7</span> │ <span style="color: #008080; text-decoration-color: #008080">   0.47</span> │ <span style="color: #800000; text-decoration-color: #800000"> 1996-09-01 </span> │ <span style="color: #800000; text-decoration-color: #800000"> 2006-12-01 </span> │ <span style="color: #af87ff; text-decoration-color: #af87ff">None      </span> │  │
│ │ <span style="color: #af87ff; text-decoration-color: #af87ff">loan_end_datetime       </span> │ <span style="color: #008080; text-decoration-color: #008080"> 537</span> │ <span style="color: #008080; text-decoration-color: #008080">  36.28</span> │ <span style="color: #800000; text-decoration-color: #800000"> 2003-10-01 </span> │ <span style="color: #800000; text-decoration-color: #800000"> 2012-09-01 </span> │ <span style="color: #af87ff; text-decoration-color: #af87ff">None      </span> │  │
│ └──────────────────────────┴──────┴─────────┴──────────────┴──────────────┴────────────┘  │
│ <span style="font-style: italic">                                         string                                         </span>  │
│ ┏━━━━━━━━━━━━━━━━━━━━━━━━━┳━━━━━━┳━━━━━━━━━┳━━━━━━━━━━━━━━━━━━━━━━━┳━━━━━━━━━━━━━━━━━━━┓  │
│ ┃<span style="font-weight: bold"> column_name             </span>┃<span style="font-weight: bold"> NA   </span>┃<span style="font-weight: bold"> NA %    </span>┃<span style="font-weight: bold"> words per row         </span>┃<span style="font-weight: bold"> total words       </span>┃  │
│ ┡━━━━━━━━━━━━━━━━━━━━━━━━━╇━━━━━━╇━━━━━━━━━╇━━━━━━━━━━━━━━━━━━━━━━━╇━━━━━━━━━━━━━━━━━━━┩  │
│ │ <span style="color: #af87ff; text-decoration-color: #af87ff">loan_startyear         </span> │ <span style="color: #008080; text-decoration-color: #008080">   0</span> │ <span style="color: #008080; text-decoration-color: #008080">      0</span> │ <span style="color: #008080; text-decoration-color: #008080">                    1</span> │ <span style="color: #008080; text-decoration-color: #008080">             1480</span> │  │
│ │ <span style="color: #af87ff; text-decoration-color: #af87ff">loan_endyear           </span> │ <span style="color: #008080; text-decoration-color: #008080">   0</span> │ <span style="color: #008080; text-decoration-color: #008080">      0</span> │ <span style="color: #008080; text-decoration-color: #008080">                    1</span> │ <span style="color: #008080; text-decoration-color: #008080">             1480</span> │  │
│ └─────────────────────────┴──────┴─────────┴───────────────────────┴───────────────────┘  │
│ <span style="font-style: italic">                                          bool                                          </span>  │
│ ┏━━━━━━━━━━━━━━━━━━━━━━━━━━━━━┳━━━━━━━━━━━━━┳━━━━━━━━━━━━━━━━━━━━━━━━┳━━━━━━━━━━━━━━━━━┓  │
│ ┃<span style="font-weight: bold"> column_name                 </span>┃<span style="font-weight: bold"> true        </span>┃<span style="font-weight: bold"> true rate              </span>┃<span style="font-weight: bold"> hist            </span>┃  │
│ ┡━━━━━━━━━━━━━━━━━━━━━━━━━━━━━╇━━━━━━━━━━━━━╇━━━━━━━━━━━━━━━━━━━━━━━━╇━━━━━━━━━━━━━━━━━┩  │
│ │ <span style="color: #af87ff; text-decoration-color: #af87ff">long_term                  </span> │ <span style="color: #008080; text-decoration-color: #008080">        957</span> │ <span style="color: #008080; text-decoration-color: #008080">                  0.65</span> │ <span style="color: #008000; text-decoration-color: #008000">    ▄    █     </span> │  │
│ └─────────────────────────────┴─────────────┴────────────────────────┴─────────────────┘  │
│ <span style="font-style: italic">                                      timedelta64                                       </span>  │
│ ┏━━━━━━━━━━━━━━━━━┳━━━━━┳━━━━━━━┳━━━━━━━━━━━━━━━━━┳━━━━━━━━━━━━━━━━━━┳━━━━━━━━━━━━━━━━━┓  │
│ ┃<span style="font-weight: bold"> column_name     </span>┃<span style="font-weight: bold"> NA  </span>┃<span style="font-weight: bold"> NA %  </span>┃<span style="font-weight: bold"> mean            </span>┃<span style="font-weight: bold"> median           </span>┃<span style="font-weight: bold"> max             </span>┃  │
│ ┡━━━━━━━━━━━━━━━━━╇━━━━━╇━━━━━━━╇━━━━━━━━━━━━━━━━━╇━━━━━━━━━━━━━━━━━━╇━━━━━━━━━━━━━━━━━┩  │
│ │ <span style="color: #af87ff; text-decoration-color: #af87ff">loan_length    </span> │ <span style="color: #008080; text-decoration-color: #008080">537</span> │ <span style="color: #008080; text-decoration-color: #008080">36.28</span> │ <span style="color: #008080; text-decoration-color: #008080">279 days 09:52:</span> │ <span style="color: #008080; text-decoration-color: #008080">      -1066 days</span> │ <span style="color: #008080; text-decoration-color: #008080">      4748 days</span> │  │
│ │                 │     │       │ <span style="color: #008080; text-decoration-color: #008080">   29.522799576</span> │ <span style="color: #008080; text-decoration-color: #008080">       +00:00:00</span> │ <span style="color: #008080; text-decoration-color: #008080">       00:00:00</span> │  │
│ │ <span style="color: #af87ff; text-decoration-color: #af87ff">loan_length_na </span> │ <span style="color: #008080; text-decoration-color: #008080">709</span> │ <span style="color: #008080; text-decoration-color: #008080">47.91</span> │ <span style="color: #008080; text-decoration-color: #008080">384 days 01:57:</span> │ <span style="color: #008080; text-decoration-color: #008080"> 0 days 00:00:00</span> │ <span style="color: #008080; text-decoration-color: #008080">      4748 days</span> │  │
│ │                 │     │       │ <span style="color: #008080; text-decoration-color: #008080">   39.922178988</span> │                  │ <span style="color: #008080; text-decoration-color: #008080">       00:00:00</span> │  │
│ │ <span style="color: #af87ff; text-decoration-color: #af87ff">loan_length_abs</span> │ <span style="color: #008080; text-decoration-color: #008080">537</span> │ <span style="color: #008080; text-decoration-color: #008080">36.28</span> │ <span style="color: #008080; text-decoration-color: #008080">348 days 15:23:</span> │ <span style="color: #008080; text-decoration-color: #008080"> 0 days 00:00:00</span> │ <span style="color: #008080; text-decoration-color: #008080">      4748 days</span> │  │
│ │                 │     │       │ <span style="color: #008080; text-decoration-color: #008080">   51.601272536</span> │                  │ <span style="color: #008080; text-decoration-color: #008080">       00:00:00</span> │  │
│ └─────────────────┴─────┴───────┴─────────────────┴──────────────────┴─────────────────┘  │
╰─────────────────────────────────────────── End ───────────────────────────────────────────╯
</pre>


```python
got_l_c["loan_length_abs"].head()
```


```
0        NaT
1        NaT
2   153 days
3   273 days
4   245 days
Name: loan_length_abs, dtype: timedelta64[ns]
```


It can be helpful to look at loan amounts and interest rate graphically, for example in a scatterplot. We'll use the **lets-plot** package for that.


```python
(
    ggplot(got_l_c, aes(x="loan_amount", y="loan_interest"))
    + geom_point(size=3)
    + labs(
        title="Loan start and end year", x="Loan amount", y="Loan interest rate"
    )
)
```


![png](empirical_project_9_files/empirical_project_9_WT10_1.png)


**Figure 9.6** Scatterplot showing loan amounts and interest payments.


One large loan (top-right corner) dominates this graph. Let's exclude observations with a loan amount larger than 200,000 from the plotted area of the graph.


```python
(
    ggplot(got_l_c, aes(x="loan_amount", y="loan_interest"))
    + geom_point(size=3)
    + labs(
        title="Loan start and end year", x="Loan amount", y="Loan interest rate"
    )
    + ylim(0, 3e4)
    + xlim(0, 2e5)
)
```
 
![png](empirical_project_9_files/empirical_project_9_WT10_2.png)
 
**Figure 9.7** Revised scatterplot showing loan amounts and interest payments without outliers.


Interestingly we can see many zero-interest loans. Now we will calculate the interest rate as `loan_interest`/`loan_amount`.


```python
got_l_c["interest_rate"] = got_l_c["loan_interest"] / got_l_c["loan_amount"]
got_l_c["interest_rate"].describe()
```


```
count    1445.000000
mean        0.256616
std         5.262957
min         0.000000
25%         0.000000
50%         0.000000
75%         0.166667
max       200.000000
Name: interest_rate, dtype: float64
```


The maximum interest rate is 200 (in other words 20,000%), which does not make sense and could be due to a data entry error. Making another scatterplot can also identify extreme values for loan amounts:


```python
(
    ggplot(got_l_c, aes(x="loan_amount", y="interest_rate"))
    + geom_point(size=3)
    + labs(
        title="Loan amounts and interest rates", x="Loan amount", y="Loan interest rate"
    )
)
```


![png](empirical_project_9_files/empirical_project_9_WT10_3.png)


**Figure 9.8** Scatterplot identifying extreme values for loan amounts. 


Let's make another scatterplot, excluding the observation with the extremely high interest rate and only looking at small loan amounts (less than 1,000).


```python
(
    ggplot(got_l_c, aes(x="loan_amount", y="interest_rate"))
    + geom_point(size=3)
    + labs(
        title="Loan amounts and interest rates", x="Loan amount", y="Loan interest rate"
    )
    + xlim(0, 1e3)
    + ylim(0, 5)
)
```


![png](empirical_project_9_files/empirical_project_9_WT10_4.png)


**Figure 9.9** Scatterplot excluding extremely high interest rates and including only small loan amounts.


Again we can see that there are many zero-interest loans. From the summary statistics above, we can see that the median interest rate is 0, which implies that at least 50% of loans have a zero interest rate. The following code calculates that percentage precisely.


```python
num_zero_interest_rate = (
    100 * (got_l_c["interest_rate"] == 0).sum() / got_l_c["interest_rate"].count()
)


print(f"The number of loans with a rate of zero is {num_zero_interest_rate.round(2)}%")
```


```
The number of loans with a rate of zero is 50.52%
```


Now let's calculate statistics conditional on whether a loan is long term or not. Before we do this, we will remove the observation with the very extreme interest rate (20,000%) from our `got_l_c` dataset (but not from the original `got_l` dataset). That observation has a loan amount of 1 and an interest payment of 200, which is probably a data entry mistake. There is another extreme observation (with a loan amount of 30,000,000), but there is no indication that this observation is misrecorded as there is a significant interest payment for this loan.


```python
got_l_c = got_l_c.loc[got_l_c["interest_rate"] < 200, :]
got_l_c.groupby("long_term")["interest_rate"].agg(
    ["mean", "std", "min", "max", "median", "count"]
).round(2)
```


<div>
<style scoped>
    .dataframe tbody tr th:only-of-type {
        vertical-align: middle;
    }


.dataframe tbody tr th {
    vertical-align: top;
}
.dataframe thead th {
    text-align: right;
}
</style>
<table border="1" class="dataframe">
  <thead>
    <tr style="text-align: right;">
      <th></th>
      <th>mean</th>
      <th>std</th>
      <th>min</th>
      <th>max</th>
      <th>median</th>
      <th>count</th>
    </tr>
    <tr>
      <th>long_term</th>
      <th></th>
      <th></th>
      <th></th>
      <th></th>
      <th></th>
      <th></th>
    </tr>
  </thead>
  <tbody>
    <tr>
      <th>False</th>
      <td>0.09</td>
      <td>0.18</td>
      <td>0.0</td>
      <td>1.00</td>
      <td>0.00</td>
      <td>512</td>
    </tr>
    <tr>
      <th>True</th>
      <td>0.19</td>
      <td>0.27</td>
      <td>0.0</td>
      <td>2.24</td>
      <td>0.14</td>
      <td>211</td>
    </tr>
  </tbody>
</table>
</div>


Both the mean and median interest rate are higher for long-term loans. You can adapt the code above to calculate statistics for the `loan_amount` variable.


We now calculate correlations between interest rates and household characteristics. We store the correlation coefficients in a matrix (array of rows and columns) called `m_corr`.


```python
m_corr = got_l_c.loc[
    ~got_l_c["interest_rate"].isna(),
    [
        "age",
        "max_education",
        "number_assets",
        "hhsize",
        "young_children",
        "working_age_adults",
        "interest_rate",
    ],
].corr()


m_corr["interest_rate"].round(4)
```


```
age                   0.0258
max_education        -0.0841
number_assets        -0.0474
hhsize                0.1050
young_children        0.1022
working_age_adults    0.0466
interest_rate         1.0000
Name: interest_rate, dtype: float64
```


### *Python walk-through 9.11* Creating summary tables of means


First we use the `pd.crosstab` method to create the table with the variable `borrowed_from`.


```python
stab_10 = pd.crosstab(
    got_l_c["borrowed_from"],
    got_l_c["rural"],
    margins=True,
    normalize="columns",
).round(4)
stab_10
```


<div>
<style scoped>
    .dataframe tbody tr th:only-of-type {
        vertical-align: middle;
    }
.dataframe tbody tr th {
    vertical-align: top;
}
.dataframe thead th {
    text-align: right;
}
</style>
<table border="1" class="dataframe">
  <thead>
    <tr style="text-align: right;">
      <th>rural</th>
      <th>Large town (urban)</th>
      <th>Rural</th>
      <th>Small town (urban)</th>
      <th>All</th>
    </tr>
    <tr>
      <th>borrowed_from</th>
      <th></th>
      <th></th>
      <th></th>
      <th></th>
    </tr>
  </thead>
  <tbody>
    <tr>
      <th>Bank (Commercial)</th>
      <td>0.0188</td>
      <td>0.0039</td>
      <td>0.0000</td>
      <td>0.0070</td>
    </tr>
    <tr>
      <th>Employer</th>
      <td>0.0408</td>
      <td>0.0020</td>
      <td>0.0103</td>
      <td>0.0111</td>
    </tr>
    <tr>
      <th>Grocery/Local Merchant</th>
      <td>0.0815</td>
      <td>0.0471</td>
      <td>0.1031</td>
      <td>0.0585</td>
    </tr>
    <tr>
      <th>Microfinance Institution</th>
      <td>0.1912</td>
      <td>0.2797</td>
      <td>0.2680</td>
      <td>0.2592</td>
    </tr>
    <tr>
      <th>Money Lender (Katapila)</th>
      <td>0.0031</td>
      <td>0.0481</td>
      <td>0.0206</td>
      <td>0.0362</td>
    </tr>
    <tr>
      <th>NGO</th>
      <td>0.0125</td>
      <td>0.0471</td>
      <td>0.0515</td>
      <td>0.0397</td>
    </tr>
    <tr>
      <th>Neighbour</th>
      <td>0.1066</td>
      <td>0.1158</td>
      <td>0.0722</td>
      <td>0.1108</td>
    </tr>
    <tr>
      <th>Other (Specify)</th>
      <td>0.0564</td>
      <td>0.1207</td>
      <td>0.0412</td>
      <td>0.1010</td>
    </tr>
    <tr>
      <th>Relative</th>
      <td>0.4859</td>
      <td>0.3150</td>
      <td>0.4330</td>
      <td>0.3610</td>
    </tr>
    <tr>
      <th>Religious Institution</th>
      <td>0.0031</td>
      <td>0.0206</td>
      <td>0.0000</td>
      <td>0.0153</td>
    </tr>
  </tbody>
</table>
</div>


Note that in all settings, most loans come from relatives. To create the table with `borrowed_from_other`, substitute this variable name in the above command.


Let's take a quick look at the loan lengths we expect (the mean) when looking at the cross-tab between `rural` and `borrowed_from`:


```python
tab_10 = got_l_c.groupby(["borrowed_from", "rural"])["loan_length_abs"].agg("mean")
tab_10.head()
```


```
borrowed_from      rural             
Bank (commercial)  Large town (urban)   1814 days 04:48:00
                   Rural                 619 days 00:00:00
                   Small town (urban)                  NaT
Employer           Large town (urban)    602 days 00:00:00
                   Rural                 289 days 12:00:00
Name: loan_length_abs, dtype: timedelta64[ns]
```


We don't need the extra information on hours in the last column, so let's instead simplify the time information to only use full days. And we'll "unstack" the data to make it wider in format too, making it a bit more readable.


```python
tab_10.dt.days.unstack()
```


<div>
<style scoped>
    .dataframe tbody tr th:only-of-type {
        vertical-align: middle;
    }
.dataframe tbody tr th {
    vertical-align: top;
}
.dataframe thead th {
    text-align: right;
}
</style>
<table border="1" class="dataframe">
  <thead>
    <tr style="text-align: right;">
      <th>rural</th>
      <th>Large town (urban)</th>
      <th>Rural</th>
      <th>Small town (urban)</th>
    </tr>
    <tr>
      <th>borrowed_from</th>
      <th></th>
      <th></th>
      <th></th>
    </tr>
  </thead>
  <tbody>
    <tr>
      <th>Bank (Commercial)</th>
      <td>1814.0</td>
      <td>619.0</td>
      <td>NaN</td>
    </tr>
    <tr>
      <th>Employer</th>
      <td>602.0</td>
      <td>289.0</td>
      <td>NaN</td>
    </tr>
    <tr>
      <th>Grocery/Local Merchant</th>
      <td>165.0</td>
      <td>258.0</td>
      <td>175.0</td>
    </tr>
    <tr>
      <th>Microfinance Institution</th>
      <td>712.0</td>
      <td>410.0</td>
      <td>509.0</td>
    </tr>
    <tr>
      <th>Money Lender (Katapila)</th>
      <td>365.0</td>
      <td>331.0</td>
      <td>365.0</td>
    </tr>
    <tr>
      <th>NGO</th>
      <td>372.0</td>
      <td>395.0</td>
      <td>236.0</td>
    </tr>
    <tr>
      <th>Neighbour</th>
      <td>124.0</td>
      <td>186.0</td>
      <td>296.0</td>
    </tr>
    <tr>
      <th>Other (Specify)</th>
      <td>273.0</td>
      <td>371.0</td>
      <td>806.0</td>
    </tr>
    <tr>
      <th>Relative</th>
      <td>237.0</td>
      <td>216.0</td>
      <td>392.0</td>
    </tr>
    <tr>
      <th>Religious Institution</th>
      <td>1461.0</td>
      <td>343.0</td>
      <td>NaN</td>
    </tr>
  </tbody>
</table>
</div>


### Extension: Investigating sources of finance associated with zero-interest loans


We previously saw that a large percentage of loans have a zero interest rate. Here we investigate whether particular sources of finance are responsible for these interest rates. The code we use is very similar to the code above, but instead of calculating the mean of a variable, we calculate the mean of a Boolean (true/false) variable ((`interest_rate==0`)). This will deliver the proportion of `True` observations, in other words, loans where the interest rate was equal to zero.


```python
tab_11 = (
    got_l_c.assign(rate_of_zero=lambda x: x["interest_rate"] == 0)
    .groupby(["borrowed_from", "rural"], dropna=False)
    .agg(prop_0_interest=("rate_of_zero", "mean"))
    .unstack()
)


tab_11.round(2)
```


<div>
<style scoped>
    .dataframe tbody tr th:only-of-type {
        vertical-align: middle;
    }
.dataframe tbody tr th {
    vertical-align: top;
}
.dataframe thead tr th {
    text-align: left;
}
.dataframe thead tr:last-of-type th {
    text-align: right;
}
</style>
<table border="1" class="dataframe">
  <thead>
    <tr>
      <th></th>
      <th colspan="3" halign="left">prop_0_interest</th>
    </tr>
    <tr>
      <th>rural</th>
      <th>Large town (urban)</th>
      <th>Rural</th>
      <th>Small town (urban)</th>
    </tr>
    <tr>
      <th>borrowed_from</th>
      <th></th>
      <th></th>
      <th></th>
    </tr>
  </thead>
  <tbody>
    <tr>
      <th>NaN</th>
      <td>0.00</td>
      <td>0.57</td>
      <td>1.00</td>
    </tr>
    <tr>
      <th>Bank (Commercial)</th>
      <td>0.00</td>
      <td>0.00</td>
      <td>NaN</td>
    </tr>
    <tr>
      <th>Employer</th>
      <td>0.69</td>
      <td>0.50</td>
      <td>1.00</td>
    </tr>
    <tr>
      <th>Grocery/Local Merchant</th>
      <td>1.00</td>
      <td>0.81</td>
      <td>1.00</td>
    </tr>
    <tr>
      <th>Microfinance Institution</th>
      <td>0.08</td>
      <td>0.04</td>
      <td>0.04</td>
    </tr>
    <tr>
      <th>Money Lender (Katapila)</th>
      <td>0.00</td>
      <td>0.02</td>
      <td>0.50</td>
    </tr>
    <tr>
      <th>NGO</th>
      <td>0.25</td>
      <td>0.08</td>
      <td>0.40</td>
    </tr>
    <tr>
      <th>Neighbour</th>
      <td>1.00</td>
      <td>0.76</td>
      <td>1.00</td>
    </tr>
    <tr>
      <th>Other (Specify)</th>
      <td>0.22</td>
      <td>0.17</td>
      <td>0.00</td>
    </tr>
    <tr>
      <th>Relative</th>
      <td>0.96</td>
      <td>0.82</td>
      <td>0.98</td>
    </tr>
    <tr>
      <th>Religious Institution</th>
      <td>1.00</td>
      <td>0.19</td>
      <td>NaN</td>
    </tr>
  </tbody>
</table>
</div>


In both urban and rural settings, a high proportion of loans granted by local merchants, neighbours, and relatives are zero interest (possibly because these people have a close relationship with the borrower so there is a lower chance of default).


We will use the same technique to determine the proportion of loans that go to households that report having a woman as the household head.


```python
tab_12 = (
    got_l_c.assign(headed_by_woman=lambda x: x["gender"] == "Female")
    .groupby(["borrowed_from", "rural"], dropna=False)
    .agg(prop_women=("headed_by_woman", "mean"))
    .unstack()
)


tab_12.round(2)
```


<div>
<style scoped>
    .dataframe tbody tr th:only-of-type {
        vertical-align: middle;
    }
.dataframe tbody tr th {
    vertical-align: top;
}
.dataframe thead tr th {
    text-align: left;
}
.dataframe thead tr:last-of-type th {
    text-align: right;
}
</style>
<table border="1" class="dataframe">
  <thead>
    <tr>
      <th></th>
      <th colspan="3" halign="left">prop_women</th>
    </tr>
    <tr>
      <th>rural</th>
      <th>Large town (urban)</th>
      <th>Rural</th>
      <th>Small town (urban)</th>
    </tr>
    <tr>
      <th>borrowed_from</th>
      <th></th>
      <th></th>
      <th></th>
    </tr>
  </thead>
  <tbody>
    <tr>
      <th>NaN</th>
      <td>1.00</td>
      <td>0.43</td>
      <td>1.00</td>
    </tr>
    <tr>
      <th>Bank (Commercial)</th>
      <td>0.00</td>
      <td>0.50</td>
      <td>NaN</td>
    </tr>
    <tr>
      <th>Employer</th>
      <td>0.31</td>
      <td>0.00</td>
      <td>0.00</td>
    </tr>
    <tr>
      <th>Grocery/Local Merchant</th>
      <td>0.38</td>
      <td>0.19</td>
      <td>0.50</td>
    </tr>
    <tr>
      <th>Microfinance Institution</th>
      <td>0.41</td>
      <td>0.13</td>
      <td>0.31</td>
    </tr>
    <tr>
      <th>Money Lender (Katapila)</th>
      <td>0.00</td>
      <td>0.27</td>
      <td>0.50</td>
    </tr>
    <tr>
      <th>NGO</th>
      <td>0.25</td>
      <td>0.31</td>
      <td>0.20</td>
    </tr>
    <tr>
      <th>Neighbour</th>
      <td>0.35</td>
      <td>0.25</td>
      <td>0.43</td>
    </tr>
    <tr>
      <th>Other (Specify)</th>
      <td>0.39</td>
      <td>0.19</td>
      <td>0.25</td>
    </tr>
    <tr>
      <th>Relative</th>
      <td>0.40</td>
      <td>0.21</td>
      <td>0.29</td>
    </tr>
    <tr>
      <th>Religious Institution</th>
      <td>1.00</td>
      <td>0.24</td>
      <td>NaN</td>
    </tr>
  </tbody>
</table>
</div>