A loan bridge consists of three components, left-bank loan (loan A), bridge loan (loan B), and right-bank loan (loan C). Bridges can be compound and linked together to form a multiple-path bridge
Format
loan bridge dataframe with various top loan, middle loan, and bottom loan attributes.
- bridge_id
multiple-path compound bridge id, this is not the id for each row, but for a group of rows that are linked together as a multiple-path joint/compound bridge
- br_type
one of eight multiple-path joint/compound bridge types, see issue-19-Multiple-path bridges
- br_type_id
multiple-path compound bridge id, counter with each of the eight path types
- hhid_Num
household id
- hh_loan_id_nd_1t2
non-duplicate loan id, sequential for all loans, top loan of bridge, loan set A
- hh_loan_id_nd_paired_1t2
non-duplicate loan id, sequential for all loans, middle loan of bridge, loan set B
- hh_loan_id_nd_paired_2t3
non-duplicate loan id, sequential for all loans, bottom loan of bridge, loan set C
- hh_br_la_n
count within household each loan a id occurrences, top loan of bridge
- hh_br_lb_n
count within household each loan b id occurrences, middle loan of bridge
- hh_br_lc_n
count within household each loan c id occurrences, bottom loan of bridge
- loan_start_1t2
loan start month, top loan of bridge
- loan_end_1t2
loan end month, top loan of bridge
- loan_start_paired_1t2
loan start month, middle loan of bridge
- loan_end_paired_1t2
loan end month, middle loan of bridge
- loan_start_paired_2t3
loan start month, bottom loan of bridge
- loan_end_paired_2t3
loan end month, bottom loan of bridge
- forinfm4_1t2
name of categorical variable defining loan group types, top loan of bridge
- forinfm4_paired_1t2
name of categorical variable defining loan group types, middle loan of bridge
- forinfm4_paired_2t3
name of categorical variable defining loan group types, bottom loan of bridge
- loan_principal_1t2
loan principal aggregating over individual loans with the same dates and lender type, top loan of bridge
- loan_principal_paired_1t2
loan principal aggregating over individual loans with the same dates and lender type, middle loan of bridge
- loan_principal_paired_2t3
loan principal aggregating over individual loans with the same dates and lender type, bottom loan of bridge
- loan_interest_monthly_1t2
monthly interest rate given aggregated interests and principals, top loan of bridge
- loan_interest_monthly_paired_1t2
monthly interest rate given aggregated interests and principals, middle loan of bridge
- loan_interest_monthly_paired_2t3
monthly interest rate given aggregated interests and principals, bottom loan of bridge
- ll_g12l
G12L: start of B - start of A
- ll_g12m
G12M: end of A - start of B
- ll_g12r
G12R: end of B - end of A
- ll_g23l
G23L: start of C - start of B
- ll_g23m
G23M: end of B - start of C
- ll_g23r
G23R: end of C - end of B
- ll_gw1
GW1: loan A gap
- ll_gw2
GW2: loan B gap
- ll_gw3
GW3: loan C gap
- ll_grv
River width (GRV) = G12R - G23M = G23L - G12M
- ll_gab
Abutment width (GAB) = G12M + G23M, Left-abutment width = G12M, Right-abutment width = G23M
- ll_gap
Approach width (GAP) = G12L + G23R, Left-approach width = G12L, Right-approach width = G23R
- ll_gtt
Total bridge length, loan_end_paired_2t3 - loan_start_1t2
- bl_lender_type
Lender type requirement, to be a bridge loan, by definition loan A and loan B can not be from the same lender, and loan B and loan C can not be from the same lender. Filter out loans that can not possibly be bridge, because it is from the same lender type in connected pieces.
if_else((forinfm4_1t2 != forinfm4_paired_1t2) & (forinfm4_paired_1t2 != forinfm4_paired_2t3), TRUE, FALSE))- bl_bridge_informal
Lender type informal as bridge.
bl_bridge_informal = if_else(forinfm4_1t2 %in% c("BAAC-Commercial", "Village-Fund") & forinfm4_paired_1t2 %in% c("Informal", "Quasi-formal") & forinfm4_paired_2t3 %in% c("BAAC-Commercial", "Village-Fund"), TRUE, FALSE))- bl_loan_size
Loan size based filter, relative loan size filter, bridge loan size is smaller than the loans it is connecting together.
bl_loan_size = if_else((loan_principal_1t2 >= loan_principal_paired_1t2) & (loan_principal_paired_2t3 >= loan_principal_paired_1t2), TRUE, FALSE))- bl_loan_dura_a
Duration a requirement.
if_else((ll_gw1 > ll_gw2) & (ll_gw3 > ll_gw2), TRUE, FALSE))- bl_loan_dura_b
Duration b requirement.
bl_loan_dura_b = if_else((ll_gw1 >= 11) & (ll_gw3 >= 11), TRUE, FALSE)- bl_loan_dura_c
Duration c requirement.
if_else(ll_gap > (ll_gab + ll_grv)*fl_approach_rela_bridge, TRUE, FALSE))
Examples
library(dplyr)
#>
#> Attaching package: ‘dplyr’
#> The following objects are masked from ‘package:stats’:
#>
#> filter, lag
#> The following objects are masked from ‘package:base’:
#>
#> intersect, setdiff, setequal, union
library(tidyr)
data(tstm_loans_bridges_type)
summary(tstm_loans_bridges_type)
#> bridge_id br_type br_type_id hhid_Num
#> Min. : 1 Length:131071 Min. : 1 Min. : 70203
#> 1st Qu.: 7916 Class :character 1st Qu.:1132 1st Qu.:271339
#> Median :12743 Mode :character Median :1993 Median :490325
#> Mean :12239 Mean :2235 Mean :386555
#> 3rd Qu.:16291 3rd Qu.:3365 3rd Qu.:490635
#> Max. :22183 Max. :5258 Max. :531026
#>
#> hh_loan_id_nd_1t2 hh_loan_id_nd_paired_1t2 hh_loan_id_nd_paired_2t3
#> Min. : 3 Min. : 4 Min. : 5
#> 1st Qu.: 6463 1st Qu.: 6468 1st Qu.: 6479
#> Median :10355 Median :10363 Median :10379
#> Mean :10196 Mean :10201 Mean :10207
#> 3rd Qu.:14213 3rd Qu.:14218 3rd Qu.:14222
#> Max. :19585 Max. :19586 Max. :19587
#>
#> hh_br_la_n hh_br_lb_n hh_br_lc_n loan_start_1t2
#> Min. : 1 Min. : 1.00 Min. : 1.00 Min. : 0.0
#> 1st Qu.: 8 1st Qu.: 8.00 1st Qu.: 8.00 1st Qu.: 46.0
#> Median : 14 Median : 15.00 Median : 15.00 Median : 74.0
#> Mean : 19 Mean : 21.48 Mean : 20.04 Mean : 73.1
#> 3rd Qu.: 24 3rd Qu.: 27.00 3rd Qu.: 24.00 3rd Qu.:101.0
#> Max. :139 Max. :206.00 Max. :149.00 Max. :160.0
#>
#> loan_end_1t2 loan_start_paired_1t2 loan_end_paired_1t2
#> Min. : 0.00 Min. : 0.00 Min. : 0.0
#> 1st Qu.: 64.00 1st Qu.: 58.00 1st Qu.: 76.0
#> Median : 88.00 Median : 83.00 Median :100.0
#> Mean : 87.95 Mean : 83.38 Mean :101.7
#> 3rd Qu.:115.00 3rd Qu.:111.00 3rd Qu.:130.0
#> Max. :160.00 Max. :160.00 Max. :160.0
#>
#> loan_start_paired_2t3 loan_end_paired_2t3 forinfm4_1t2
#> Min. : 0 Min. : 0.0 Length:131071
#> 1st Qu.: 72 1st Qu.: 86.0 Class :character
#> Median : 94 Median :112.0 Mode :character
#> Mean : 97 Mean :111.7
#> 3rd Qu.:125 3rd Qu.:144.0
#> Max. :160 Max. :160.0
#>
#> forinfm4_paired_1t2 forinfm4_paired_2t3 loan_principal_1t2
#> Length:131071 Length:131071 Min. : 0
#> Class :character Class :character 1st Qu.: 3000
#> Mode :character Mode :character Median : 11000
#> Mean : 24630
#> 3rd Qu.: 20000
#> Max. :2000000
#> NA's :3147
#> loan_principal_paired_1t2 loan_principal_paired_2t3 loan_interest_monthly_1t2
#> Min. : 0 Min. : 0 Min. :0.000
#> 1st Qu.: 4200 1st Qu.: 4500 1st Qu.:0.002
#> Median : 13000 Median : 13000 Median :0.004
#> Mean : 25585 Mean : 26333 Mean : Inf
#> 3rd Qu.: 20000 3rd Qu.: 20000 3rd Qu.:0.006
#> Max. :2000000 Max. :2000000 Max. : Inf
#> NA's :9226 NA's :18322 NA's :3312
#> loan_interest_monthly_paired_1t2 loan_interest_monthly_paired_2t3
#> Min. :0.000 Min. :0.000
#> 1st Qu.:0.002 1st Qu.:0.002
#> Median :0.004 Median :0.004
#> Mean : Inf Mean : Inf
#> 3rd Qu.:0.006 3rd Qu.:0.006
#> Max. : Inf Max. : Inf
#> NA's :9292 NA's :18335
#> ll_g12l ll_g12m ll_g12r ll_g23l
#> Min. : 0.00 Min. : 0.000 Min. : 0.00 Min. : 0.00
#> 1st Qu.: 3.00 1st Qu.: 0.000 1st Qu.: 5.00 1st Qu.: 5.00
#> Median : 8.00 Median : 3.000 Median : 10.00 Median : 10.00
#> Mean : 10.29 Mean : 4.565 Mean : 13.72 Mean : 13.62
#> 3rd Qu.: 12.00 3rd Qu.: 7.000 3rd Qu.: 12.00 3rd Qu.: 12.00
#> Max. :158.00 Max. :142.000 Max. :156.00 Max. :159.00
#>
#> ll_g23m ll_g23r ll_gw1 ll_gw2
#> Min. : 0.000 Min. : 0.00 Min. : 0.00 Min. : 0.00
#> 1st Qu.: 0.000 1st Qu.: 2.00 1st Qu.: 10.00 1st Qu.: 11.00
#> Median : 3.000 Median : 8.00 Median : 12.00 Median : 12.00
#> Mean : 4.671 Mean : 10.05 Mean : 14.85 Mean : 18.29
#> 3rd Qu.: 7.000 3rd Qu.: 12.00 3rd Qu.: 13.00 3rd Qu.: 15.00
#> Max. :144.000 Max. :155.00 Max. :159.00 Max. :159.00
#>
#> ll_gw3 ll_grv ll_gab ll_gap
#> Min. : 0.00 Min. : -1.00 Min. : 0.000 Min. : 0.00
#> 1st Qu.: 9.00 1st Qu.: 1.00 1st Qu.: 3.000 1st Qu.: 10.00
#> Median : 12.00 Median : 5.00 Median : 8.000 Median : 15.00
#> Mean : 14.72 Mean : 9.05 Mean : 9.237 Mean : 20.34
#> 3rd Qu.: 13.00 3rd Qu.: 11.00 3rd Qu.: 12.000 3rd Qu.: 23.00
#> Max. :158.00 Max. :155.00 Max. :159.000 Max. :159.00
#>
#> ll_gtt bl_lender_type bl_bridge_informal bl_loan_size
#> Min. : 0.00 Mode :logical Mode :logical Mode :logical
#> 1st Qu.: 23.00 FALSE:74345 FALSE:115088 FALSE:68574
#> Median : 29.00 TRUE :56726 TRUE :15983 TRUE :44618
#> Mean : 38.62 NA's :17879
#> 3rd Qu.: 45.00
#> Max. :159.00
#>
#> bl_loan_dura_a bl_loan_dura_b bl_loan_dura_c
#> Mode :logical Mode :logical Mode :logical
#> FALSE:109191 FALSE:62973 FALSE:82412
#> TRUE :21880 TRUE :68098 TRUE :48659
#>
#>
#>
#>
print(tstm_loans_bridges_type)
#> # A tibble: 131,071 × 44
#> # Groups: br_type, br_type_id [22,183]
#> bridge_id br_type br_type_id hhid_Num hh_loan_id_nd_1t2
#> <int> <chr> <int> <dbl> <dbl>
#> 1 20578 NA 2 70204 34
#> 2 20577 NA 1 70204 33
#> 3 20579 NA 3 70204 40
#> 4 20580 NA 4 70204 45
#> 5 20581 NA 5 70206 71
#> 6 20582 NA 6 70212 119
#> 7 20583 NA 7 70213 128
#> 8 20584 NA 8 70216 149
#> 9 20585 NA 9 70219 175
#> 10 20586 NA 10 70219 176
#> # ℹ 131,061 more rows
#> # ℹ 39 more variables: hh_loan_id_nd_paired_1t2 <dbl>,
#> # hh_loan_id_nd_paired_2t3 <dbl>, hh_br_la_n <int>, hh_br_lb_n <int>,
#> # hh_br_lc_n <int>, loan_start_1t2 <dbl>, loan_end_1t2 <dbl>,
#> # loan_start_paired_1t2 <dbl>, loan_end_paired_1t2 <dbl>,
#> # loan_start_paired_2t3 <dbl>, loan_end_paired_2t3 <dbl>, forinfm4_1t2 <chr>,
#> # forinfm4_paired_1t2 <chr>, forinfm4_paired_2t3 <chr>, …
tb_tally <- PrjThaiHFID::tstm_loans_bridges_type %>%
ungroup() %>%
distinct(br_type, br_type_id) %>%
group_by(br_type) %>%
summarize(br_type_n = n())
print(tb_tally)
#> # A tibble: 8 × 2
#> br_type br_type_n
#> <chr> <int>
#> 1 --- 693
#> 2 --= 1602
#> 3 -=- 1881
#> 4 -== 5094
#> 5 =-- 1663
#> 6 =-= 4385
#> 7 ==- 5258
#> 8 NA 1607