exer_merge

dat_answer.csv

dat_nba.csv

dat_ncaa.csv

merge_ex.R

# setup -------------------------------------------------------------------

setwd("~")
getwd()
options(stringsAsFactors = FALSE)
dat_nba = read.csv(here::here('/dat_nba.csv'))
dat_ncaa = read.csv(here::here('/dat_ncaa.csv'))

str(dat_nba)
head(dat_nba)
head(dat_ncaa)


dat_nba_in = dat_nba
dat_ncaa_in = dat_ncaa


View(dat_nba_in)
View(dat_ncaa)

# deterministic -----------------------------------------------------------

# basically a sql join
# https://www.w3schools.com/sql/

library(dplyr)

dplyr::full_join(x=dat_nba_in,
                 by="name",
                 y=dat_ncaa_in) %>% 
  select(contains('name'),contains('source'),
         contains('high'),contains('college')) %>% 
  View()

# try to join by= "name" and "school_college"
dplyr::full_join(x=dat_nba_in,
                 
                 by=c("name"),
                 
                 y=dat_ncaa_in) %>% 
  select(contains('name'),contains('source'),
         contains('high'),contains('college')) %>% 
  View()



# approximate -------------------------------------------------------------

library(fuzzyjoin)

stringdist_full_join(x=dat_nba_in,
                     by="name",
                     y=dat_ncaa_in,
                     max_dist=0.1,
                     method='jw',
                     distance_col = "distance") %>% 
  select(contains('name'),contains('source'),
         contains('high'),contains('college'),
         contains('distance')) %>% 
  View()

# try changing max_dist=0.5,

stringdist_full_join(x=dat_nba_in,
                     by="name",
                     y=dat_ncaa_in,
                     
                     max_dist= ,
                     
                     method='jw',
                     distance_col = "distance") %>% 
  select(contains('name'),contains('source'),
         contains('high'),contains('college'),
         contains('distance')) %>% 
  View()

# try using fuzzy on "name" and "school_college"
stringdist_full_join(x=dat_nba_in,
                     y=dat_ncaa_in,
                     by=c("name","school_college"),
                     max_dist=0.5,
                     method='jw',
                     distance_col = "distance") %>% 
  select(contains('name'),contains('source'),
         contains('high'),contains('college'),
         contains('distance')) %>% 
  View()

stringdist_full_join(x=dat_nba_in,
                     y=dat_ncaa_in,
                     by=c("name","school_high"),
                     max_dist=0.5,
                     method='jw',
                     distance_col = "distance") %>% 
  select(contains('name'),contains('source'),
         contains('high'),contains('college'),
         contains('distance')) %>% 
  View()

# try "name","school_college","school_high"

stringdist_full_join(x=dat_nba_in,
                     y=dat_ncaa_in,
                     
                     by=c("name"),
                     
                     max_dist=0.7,
                     method='jw',
                     distance_col = "distance") %>% 
  select(contains('name'),contains('source'),
         contains('high'),contains('college'),
         contains('distance')) %>% 
  View()

# probabilistic -----------------------------------------------------------

# library(devtools)
# install_github("kosukeimai/fastLink",dependencies=TRUE)

library(fastLink)

# quick umbrella function
?fastLink()


# more control, specific functions under the hood
# define pattern features + estimate + filter + lookup

dfA = dat_nba_in
dfB = dat_ncaa_in

names(dfA)

# patterns aka 'features / predictors'
?gammaCKpar
feat_1 <- gammaCKpar(dfA$name, dfB$name,
                     # cut.p=0.7,
                     cut.p=0.5,
                     cut.a=0.8)

glimpse(feat_1)

?gammaCK2par
feat_1_ck2 <- gammaCK2par(dfA$name, dfB$name,cut.a=0.5)

# try different threshold values of cut.a
feat_2 <- gammaCK2par(dfA$school_high, dfB$school_high,cut.a=0.5)
feat_3 <- gammaCK2par(dfA$school_college, dfB$school_college,cut.a=0.5)

# try gammaCKpar() for feat_2 and feat_3

ls_feat_1 = list(feat_1)
ls_feat_12 = list(feat_1,feat_2)
ls_feat_123 = list(feat_1,feat_2,feat_3)

tableCounts(ls_feat_1, nobs.a = nrow(dfA), nobs.b = nrow(dfB))
tableCounts(ls_feat_12, nobs.a = nrow(dfA), nobs.b = nrow(dfB))
tableCounts(ls_feat_123, nobs.a = nrow(dfA), nobs.b = nrow(dfB))

tc_feat_123 <- tableCounts(ls_feat_123, nobs.a = nrow(dfA), nobs.b = nrow(dfB))
tc_feat_12 <- tableCounts(ls_feat_12, nobs.a = nrow(dfA), nobs.b = nrow(dfB))
tc_feat_1 <- tableCounts(ls_feat_1, nobs.a = nrow(dfA), nobs.b = nrow(dfB))

# estimate 'importance/relevance' parameters of features

# just use name feature
em <- emlinkMARmov(patterns=tc_feat_1,
                   nobs.a = nrow(dfA), 
                   nobs.b = nrow(dfB)
                   )

matchesLink(gammalist=ls_feat_1, 
            nobs.a = nrow(dfA), 
            nobs.b = nrow(dfB),
            em = em, thresh = .2) %>% 
  getMatches(
    dfA = dat_nba_in, 
    dfB = dat_ncaa_in, 
    fl.out = .,
    combine.dfs=FALSE
  ) %>% View()

# josh smith missing college but oak hill
# josh smith college but missing high school

emlinkMARmov(patterns=tc_feat_12,
             nobs.a = nrow(dfA), 
             nobs.b = nrow(dfB)) %>%
  summary(.)

emlinkMARmov(patterns=tc_feat_123,
             nobs.a = nrow(dfA), 
             nobs.b = nrow(dfB)) %>%
  summary(.)

emlinkMARmov(patterns=tc_feat_123,
             nobs.a = nrow(dfA), 
             nobs.b = nrow(dfB)) %>% 
  matchesLink(em = .,
              gammalist=ls_feat_123,
              nobs.a = nrow(dfA), 
              nobs.b = nrow(dfB),
              thresh = .2) %>% 
  getMatches(fl.out = .,
             dfA = dat_nba_in, 
             dfB = dat_ncaa_in,
             combine.dfs=FALSE) %>% 
View()

# for this example, probably should only rely on name
# need to clean high school and univeristy, to reliable use


# misc scrub b4 fun --------------------------------------------------------------

# example of string scrub / parse
# if string is address

# step 0) install libpostal library first
# https://github.com/openvenues/libpostal

# step 1) install the r binding / wrapper
# devtools::install_github("ironholds/poster")

library(poster)

?normalise_addr

parse_addr("926 Broxton Ave, Los Angeles, CA 90024")
parse_addr("926Broxton Ave, Los Angeles, CA 90024")

paste(house_number("781 st Street"),road("781 st Street"))

?regex

sub(x="781ststreet",
    pattern="([0-9]+)([a-z])", 
    replacement="\\1 \\2",
    ignore.case = TRUE,
    perl = FALSE,
    fixed = FALSE, useBytes = FALSE)


sub(x="926Broxton Ave, Los Angeles, CA 90024",
    pattern="([0-9]+)([a-z])", 
    replacement="\\1 \\2",
    ignore.case = TRUE,
    perl = FALSE,
    fixed = FALSE, useBytes = FALSE)