The package r2dii.match helps you to match counterparties from a loanbook to companies in a physical-asset database. Each section below shows you how.
Setup
We use the package r2dii.match to access the most important functions you’ll learn about. We also use example datasets from the package r2dii.data, and optional but convenient functions from the packages dplyr and readr.
library(dplyr, warn.conflicts = FALSE)
library(r2dii.data)
library(r2dii.match)Format input data loanbook and asset-based company data (abcd)
We need two datasets: a “loanbook” and an “asset-based company
dataset” (abcd). These should be formatted like: loanbook_demo
and abcd_demo
(from the r2dii.data
package).
A note on sector classification: Matches are preferred when the
sector from the loanbook matches the sector from the
abcd. The loanbook sector is determined
internally using the sector_classification_system and
sector_classification_direct_loantaker columns. Currently,
we only allow a couple specific values for
sector_classification_system:
If you would like to use a different classification system, please raise an issue in r2dii.data and we can incorporate it.
loanbook_demo
#> # A tibble: 283 × 13
#> id_loan id_direct_loantaker name_direct_loantaker id_ultimate_parent
#> <chr> <chr> <chr> <chr>
#> 1 L1 C294 Vitale Group UP15
#> 2 L2 C293 Moen-Moen UP84
#> 3 L3 C292 Rowe-Rowe UP288
#> 4 L4 C299 Fadel-Fadel UP54
#> 5 L5 C305 Ring AG & Co. KGaA UP104
#> 6 L6 C304 Kassulke-Kassulke UP83
#> 7 L7 C227 Morissette Group UP134
#> 8 L8 C303 Barone s.r.l. UP163
#> 9 L9 C301 Werner Werner AG & Co. KGaA UP138
#> 10 L10 C302 De rosa s.r.l. UP32
#> # ℹ 273 more rows
#> # ℹ 9 more variables: name_ultimate_parent <chr>, loan_size_outstanding <dbl>,
#> # loan_size_outstanding_currency <chr>, loan_size_credit_limit <dbl>,
#> # loan_size_credit_limit_currency <chr>, sector_classification_system <chr>,
#> # sector_classification_direct_loantaker <chr>, lei_direct_loantaker <chr>,
#> # isin_direct_loantaker <chr>
abcd_demo
#> # A tibble: 4,972 × 12
#> company_id name_company lei sector technology production_unit year
#> <chr> <chr> <chr> <chr> <chr> <chr> <int>
#> 1 175 Giordano, Giordano … 1850… power hydrocap MW 2020
#> 2 175 Giordano, Giordano … 1850… power hydrocap MW 2021
#> 3 175 Giordano, Giordano … 1850… power hydrocap MW 2022
#> 4 175 Giordano, Giordano … 1850… power hydrocap MW 2023
#> 5 175 Giordano, Giordano … 1850… power hydrocap MW 2024
#> 6 175 Giordano, Giordano … 1850… power hydrocap MW 2025
#> 7 526 Vitali, Vitali e Vi… 8871… cement integrate… tonnes per year 2020
#> 8 526 Vitali, Vitali e Vi… 8871… cement integrate… tonnes per year 2021
#> 9 526 Vitali, Vitali e Vi… 8871… cement integrate… tonnes per year 2022
#> 10 526 Vitali, Vitali e Vi… 8871… cement integrate… tonnes per year 2023
#> # ℹ 4,962 more rows
#> # ℹ 5 more variables: production <dbl>, emission_factor <dbl>,
#> # plant_location <chr>, is_ultimate_owner <lgl>, emission_factor_unit <chr>If you want to use loanbook_demo and
abcd_demo as template to create your own datasets, do
this:
- Write loanbook_demo.csv and abcd_demo.csv with:
# Writting to current working directory
loanbook_demo %>%
readr::write_csv(path = "loanbook_demo.csv")
abcd_demo %>%
readr::write_csv(path = "abcd_demo.csv")- For each dataset, replace our demo data with your data.
- Save each dataset as, for example, your_loanbook.csv and your_abcd.csv.
- Read your datasets back into R with:
# Reading from current working directory
your_loanbook <- readr::read_csv("your_loanbook.csv")
your_abcd <- readr::read_csv("your_abcd.csv")Here we continue to use the *_demo datasets, pretending
they contain the data of your own.
# WARNING: Skip this to avoid overwriting your data with our demo data
your_loanbook <- loanbook_demo
your_abcd <- abcd_demoScore the goodness of the match between the loanbook and abcd datasets
match_name() scores the match between names in a
loanbook dataset (lbk) and names in an asset-based company dataset
(abcd). The names come from the columns
name_direct_loantaker and name_ultimate_parent
of the loanbook dataset, and from the column name_company
of the a asset-based company dataset. In the loan book data set, it is
possible to optionally add any number of
name_intermediate_parent_* columns, where *
indicates the level up the corporate tree from
direct_loantaker.
The raw names are internally transformed applying best-practices commonly used in name matching algorithms, such as:
- Remove special characters.
- Replace language specific characters.
- Abbreviate certain names to reduce their importance in the matching.
- Removing corporate suffixes when necessary.
- Spell out numbers to increase their importance.
The similarity is then scored between the internally-transformed
names of the loanbook against the abcd. (For more information on the
scoring algorithm used, see: stringdist::stringsim()).
match_name(your_loanbook, your_abcd)
#> # A tibble: 326 × 22
#> id_loan id_direct_loantaker name_direct_loantaker id_ultimate_parent
#> <chr> <chr> <chr> <chr>
#> 1 L1 C294 Vitale Group UP15
#> 2 L3 C292 Rowe-Rowe UP288
#> 3 L5 C305 Ring AG & Co. KGaA UP104
#> 4 L6 C304 Kassulke-Kassulke UP83
#> 5 L6 C304 Kassulke-Kassulke UP83
#> 6 L7 C227 Morissette Group UP134
#> 7 L7 C227 Morissette Group UP134
#> 8 L8 C303 Barone s.r.l. UP163
#> 9 L9 C301 Werner Werner AG & Co. KGaA UP138
#> 10 L9 C301 Werner Werner AG & Co. KGaA UP138
#> # ℹ 316 more rows
#> # ℹ 18 more variables: name_ultimate_parent <chr>, loan_size_outstanding <dbl>,
#> # loan_size_outstanding_currency <chr>, loan_size_credit_limit <dbl>,
#> # loan_size_credit_limit_currency <chr>, sector_classification_system <chr>,
#> # sector_classification_direct_loantaker <chr>, lei_direct_loantaker <chr>,
#> # isin_direct_loantaker <chr>, id_2dii <chr>, level <chr>, sector <chr>,
#> # sector_abcd <chr>, name <chr>, name_abcd <chr>, score <dbl>, …match_name() defaults to scoring matches between name
strings that belong to the same sector. Using
by_sector = FALSE removes this limitation – increasing
computation time, and the number of potentially incorrect matches to
manually validate.
match_name(your_loanbook, your_abcd, by_sector = FALSE) %>%
nrow()
#> [1] 656
# Compare
match_name(your_loanbook, your_abcd, by_sector = TRUE) %>%
nrow()
#> [1] 326min_score allows you to minimum threshold
score.
matched <- match_name(your_loanbook, your_abcd, min_score = 0.9)
range(matched$score)
#> [1] 0.9007692 1.0000000Maybe overwrite matches
If you are happy with the matching coverage achieved, proceed to the
next step. Otherwise, you can manually add matches, not found
automatically by match_name(). To do this, manually inspect
the abcd and find a company you would like to match to your
loanbook. Once a match is found, use excel to write a .csv file similar
to overwrite_demo,
where:
-
levelindicates the level that the manual match should be added to (e.g.direct_loantaker) -
id_2diiis the id of the loanbook company you would like to match (from the output ofmatch_name()) -
nameis the abcd company you would like to manually link to -
sectoroptionally you can also overwrite the sector. -
sourcethis can be used later to determine where all manual matches came from.
matched <- match_name(
your_loanbook, your_abcd,
min_score = 0.9, overwrite = overwrite_demo
)
#> Warning: You should only overwrite a sector at the level of the 'direct
#> loantaker' (DL). If you overwrite a sector at the level of the 'ultimate
#> parent' (UP) you consequently overwrite all children of that sector,
#> which most likely is a mistake.Notice the warning.
Validate matches
For information on validating matches, please see the documentation
for prioritize()
(?r2dii.match::prioritize)
Prioritize validated matches by level
The validated dataset may have multiple matches per loan. Consider
the case where a loan is given to “Acme Power USA”, a subsidiary of
“Acme Power Co.”. There may be both “Acme Power USA” and “Acme Power
Co.” in the abcd, and so there could be two valid matches
for this loan. To get the best match only, use prioritize()
– it picks rows where score is 1 and level per
loan is of highest priority():
# Pretend we validated the matched dataset
valid_matches <- matched
some_interesting_columns <- c("id_2dii", "level", "score")
valid_matches %>%
prioritize() %>%
select(all_of(some_interesting_columns))
#> # A tibble: 175 × 3
#> id_2dii level score
#> <chr> <chr> <dbl>
#> 1 DL129 direct_loantaker 1
#> 2 DL144 direct_loantaker 1
#> 3 DL270 direct_loantaker 1
#> 4 DL86 direct_loantaker 1
#> 5 DL5 direct_loantaker 1
#> 6 DL80 direct_loantaker 1
#> 7 DL150 direct_loantaker 1
#> 8 DL3 direct_loantaker 1
#> 9 DL65 direct_loantaker 1
#> 10 DL79 direct_loantaker 1
#> # ℹ 165 more rowsBy default, highest priority refers to the most granular match
(direct_loantaker). The default priority is set internally
via prioritize_levels().
prioritize_level(matched)
#> [1] "direct_loantaker" "ultimate_parent"You may use a different priority. One way to do that is to pass a
function to priority. For example, use rev to
reverse the default priority.
matched %>%
prioritize(priority = rev) %>%
select(all_of(some_interesting_columns))
#> # A tibble: 175 × 3
#> id_2dii level score
#> <chr> <chr> <dbl>
#> 1 UP190 ultimate_parent 1
#> 2 UP101 ultimate_parent 1
#> 3 UP39 ultimate_parent 1
#> 4 UP63 ultimate_parent 1
#> 5 UP224 ultimate_parent 1
#> 6 UP132 ultimate_parent 1
#> 7 UP12 ultimate_parent 1
#> 8 UP20 ultimate_parent 1
#> 9 UP134 ultimate_parent 1
#> 10 UP127 ultimate_parent 1
#> # ℹ 165 more rows