prague_presentation.Rmd

---
title: "R Notebook"
output: html_notebook
---
```{r}
#libraries
options(scipen = 999)
pacman::p_load(googlesheets,zoo,openxlsx,stringr,splitstackshape,plyr,dplyr,stringdist,fuzzyjoin,data.table,svMisc,tidyverse,rvest,RSelenium,RJDBC,rjson,jsonlite,sqldf,XML,methods)

#commit from RStudio

jdbcDriver = JDBC("oracle.jdbc.OracleDriver", classPath = "C:/Users/Cezary/Downloads/ojdbc6.jar")
#jdbcDriver =JDBC("oracle.jdbc.OracleDriver",classPath="C:/Users/cezar/Documents/R/ojdbc6.jar")
#jdbcDriver =JDBC("oracle.jdbc.OracleDriver",classPath="E:/Cezary/Documents/ojdbc6.jar")
PBL <- dbConnect(jdbcDriver, "jdbc:oracle:thin:@//pbl.ibl.poznan.pl:1521/xe", "IBL_SELECT", "CR333444")

pbl_writers <- RJDBC::dbReadTable(PBL,"PBL_TWORCY")
pbl_writers <- pbl_writers[1:100,]

#empty table
authority_nlp <- data.frame(pbl_id = character(), pbl_last_name = character(), pbl_first_name = character(), nlp_json = character(), stringsAsFactors=FALSE)
#for loop
#binman::rm_platform("phantomjs")
#wdman::selenium(retcommand = TRUE)
rD <- rsDriver(port=4444L,browser="chrome", chromever="76.0.3809.126")
#binman::list_versions("chromedriver")
remDr <- rD$client

x <- 1:length(pbl_writers$TW_TWORCA_ID)
for (i in x) {
  progress(match(i,x), max.value = length(x)) 
  url_1 <- "http://data.bn.org.pl/api/authorities.json?limit=100&name="
  url_2 <- tolower(pbl_writers$TW_NAZWISKO)[i]
  url_3 <- "+"
  url_4 <- tolower(pbl_writers$TW_IMIE)[i]
  url <- as.character(paste(url_1,url_2,url_3,url_4,sep = ""))
  tryCatch({
    remDr$navigate(url)
  webElem <- remDr$findElement(using = 'css selector', "body:nth-child(2) > pre:nth-child(1)")
  json_text <- webElem$getElementText()
  json_text <- as.character(json_text)
  }, error=function(e){
                   json_text <<- "brak danych CR"
})
  iteration <- cbind(pbl_writers$TW_TWORCA_ID[i], pbl_writers$TW_NAZWISKO[i], pbl_writers$TW_IMIE[i], json_text)
  authority_nlp <- rbind(authority_nlp,iteration)
}
```

```{r clearing the data}

authority_nlp$json_text <- as.character(authority_nlp$json_text)

nlp_people_authority <- authority_nlp %>%
  filter(nchar(json_text)>41)

bn_puste <- authority_nlp %>%
  filter(nchar(json_text)<=41)

nlp_json_full <- data.frame(pbl_id = character(), pbl_last_name = character(), pbl_first_name = character(), stringsAsFactors=FALSE)
x <- 1:length(nlp_people_authority$V1)
for (i in x) {
  progress(match(i,x), max.value = length(x)) 
  tryCatch({
  nlp_json <- fromJSON(nlp_people_authority$json_text[i])
  nlp_json <- nlp_json$authorities
  nlp_json2 <- nlp_json$marc
  nlp_json$viaf <- str_extract_all(nlp_json2$fields, "http:\\/\\/viaf\\.org\\/viaf\\/\\d+")
  
  nlp_json <- nlp_json %>%
    select(id,name,kind,title,viaf)
  }, error=function(e){
                     nlp_json <<- data.frame(id = "NLP lack of data",name = "NLP lack of data",kind = "NLP lack of data",title = "NLP lack of data",viaf = "NLP lack of data")
  })
  
  empty_table<- data.frame(matrix(ncol = 3,nrow = lengths(nlp_json)[1]))
  colnames(empty_table) <- c("pbl_id", "pbl_last_name", "pbl_first_name")
  empty_table$pbl_id <- nlp_people_authority$V1[i]
  empty_table$pbl_last_name <- nlp_people_authority$V2[i]
  empty_table$pbl_first_name <- nlp_people_authority$V3[i]
  nlp_json_iteration <- cbind(empty_table,nlp_json)
  nlp_json_full <- rbind(nlp_json_full,nlp_json_iteration)
}

nlp_json_full$viaf <- as.character(nlp_json_full$viaf)
nlp_json_full$viaf <- ifelse(nlp_json_full$viaf=="character(0)","",as.character(nlp_json_full$viaf))
nlp_json_full <- nlp_json_full %>%
  filter(title=="") %>%
  filter(kind=="person")
```

```{r reconciliation based on years of life}
#PBL - years of life
pbl_writers <- dbGetQuery(PBL,
                          "select tw.tw_tworca_id, tw.tw_rok_urodzin||'-'||tw.tw_rok_zgonu \"years\"
                          from pbl_tworcy tw")
pbl_writers <- pbl_writers[1:100,]
pbl_writers <- pbl_writers %>%
  filter(years!="-")

nlp_json_full <- merge(nlp_json_full,pbl_writers,by.x = "pbl_id",by.y = "TW_TWORCA_ID", all.x = TRUE)
nlp_json_full <- nlp_json_full %>%
  select(1:3,pbl_years = years, 4:6, 8)

pbl_bn <- nlp_json_full

#----------------------------------------------------

x <- 1:length(pbl_bn$pbl_id)
for (i in x) {
  progress(match(i,x), max.value = length(x)) 
  pbl_bn$is_date[i] <- grepl(pbl_bn$pbl_years[i],pbl_bn$name[i])
}
pbl_bn$is_date <- ifelse(is.na(pbl_bn$is_date),0,as.integer(pbl_bn$is_date))
pbl_bn <- pbl_bn %>%
  mutate(pbl_name = paste(pbl_last_name,pbl_first_name,sep = ", ")) %>%
  mutate(identical_name = pbl_name==name)
pbl_bn$pbl_name <- NULL
pbl_bn <- pbl_bn %>%
  mutate(is_the_same = ifelse(is_date==1,"yes",NA))
pbl_bn <- pbl_bn %>%
  group_by(pbl_id) %>%
  arrange(pbl_id, is_the_same)

#unique identifier
pbl_bn %$%  
    { pbl_id == dplyr::lag(pbl_id, 1) } %>% 
    as.numeric() %>% 
    {.} -> pbl_bn$same
pbl_bn$same[1] <- 0

pbl_bn <- pbl_bn %>%
  mutate(czy2 = is_the_same) %>%
  group_by(pbl_id) %>%
  fill(czy2)

pbl_bn <- pbl_bn %>%
  mutate(is_the_same = ifelse(is.na(is_the_same)&czy2=="yes","no",ifelse(is_the_same=="yes"&czy2=="yes","yes",NA)))

test <- pbl_bn %>%
  select(-is_date,-same,-czy2,-identical_name) %>%
  filter(is_the_same!="no"|is.na(is_the_same))

count <- as.data.frame(table(test$pbl_id))

test <- merge(x=test,y=count, by.x = "pbl_id", by.y = "Var1")
test <- test %>%
  mutate(is_the_same = ifelse(is.na(is_the_same)&Freq==1,"rather yes",ifelse(is_the_same=="yes","yes",NA)))
pbl_bn <- test %>%
  select(-Freq) %>%
  arrange(pbl_id)
```

```{r reconciliation based on date of death in PBL bibliographical records}
pbl_bn <- pbl_bn %>%
  mutate(lp = 1:length(pbl_id))

writers_search <- pbl_bn %>%
  filter(is.na(is_the_same)) %>%
  select(pbl_id) %>%
  unique()

pbl_death <- dbGetQuery(PBL,
                          "select tw.tw_tworca_id,z.za_adnotacje
                          from pbl_tworcy tw
                          join IBL_OWNER.pbl_zapisy_tworcy zt on zt.zatw_tw_tworca_id=tw.tw_tworca_id
                          join IBL_OWNER.pbl_zapisy z on zt.zatw_za_zapis_id=z.za_zapis_id
                          and z.za_rz_rodzaj1_id=44")
pbl_death <- sqldf("select *
              from pbl_death a
              where a.TW_TWORCA_ID in (select b.pbl_id from writers_search b)")
pbl_death <- pbl_death %>%
  filter(!is.na(ZA_ADNOTACJE)) %>%
  mutate(rok = str_extract(ZA_ADNOTACJE,"\\d{4}")) %>%
  select(TW_TWORCA_ID,rok) %>%
  unique() %>%
  filter(!is.na(rok))
pbl_death$rok <- paste("-",pbl_death$rok,sep = "")

#--------------------------------------------------------------------------

test <- merge(x=pbl_bn, y=pbl_death, by.x = "pbl_id",by.y = "TW_TWORCA_ID")

x <- 1:length(test$pbl_id)
for (i in x) {
  progress(match(i,x), max.value = length(x)) 
  test$is_date[i] <- grepl(test$rok[i],test$name[i])
}

test <- test %>%
  group_by(pbl_id) %>%
  mutate(grupa = paste(is_date,collapse = ",")) %>%
  ungroup()
test$strzal_w_grupie <- grepl("TRUE",test$grupa)
test <- test %>%
  filter(strzal_w_grupie==TRUE) %>%
  select(-grupa)
test <- test %>%
  select(lp,is_date) %>%
  ungroup()
test <- test %>%
  select(lp,is_date)

pbl_bn <- merge(pbl_bn,test,by = "lp", all.x = TRUE)
pbl_bn <- pbl_bn %>%
  mutate(is_the_same = ifelse(!is.na(is_the_same),as.character(is_the_same),ifelse(is_date==TRUE,"yes",ifelse(is_date==FALSE,"no",NA))))
pbl_bn <- pbl_bn %>%
  select(-is_date)
```

```{r reconciliation based on works in pbl and nlp}
#uploading NLP database
#bn_ks <- read.csv2("C:/Users/cezar/Desktop/bn_data_ks_table.csv", encoding = "Windows-1250", header = TRUE, stringsAsFactors = FALSE)
bn_ks <- read.csv2("C:/Users/Cezary/Desktop/bn_data_ks_table.csv", encoding = "Windows-1250", header = TRUE, stringsAsFactors = FALSE)
bn_ks_light <- bn_ks %>%
  filter(X100!="") %>%
  select(X009,X100,X245)
#remove(bn_ks)
marc_field100 <- bn_ks_light %>%
  select(X009,X100)

marc_field100$X100<-str_remove_all(marc_field100$X100,"^\\|")
marc_field100 <- mutate(marc_field100,
               indicator = str_replace_all(marc_field100$X100,"(^.*?)(\\$.*)","\\1"))
subfield_list<- str_extract_all(bn_ks_light$X100,"\\$.")
subfield_list<- unique(unlist(subfield_list))
empty_table<- data.frame(matrix(ncol = length(subfield_list),nrow = lengths(marc_field100)[1]))
colnames(empty_table) <-subfield_list

marc_field100<-cbind(marc_field100,empty_table)

subfield_list_char <- paste("(",subfield_list,")",sep = "")
subfield_list_char <- str_replace_all(subfield_list_char,"\\$","\\\\$")

x <- 1:length(subfield_list)

for (i in x) {
  progress(match(i,x), max.value = length(x))
  marc_field100$X100 <- str_replace(marc_field100$X100,subfield_list_char[i],"|\\1")
}
for (i in x) {
  progress(match(i,x), max.value = length(x))
  subfield_list_char2 <- str_replace_all(subfield_list,"\\$","\\\\$")
  string_a <- "(^)(.*?\\|"
  string_b <- subfield_list_char2[i]
  string_c <- ")(.*?)(\\,{0,1})((\\|\\$)(.*)|$)"
  string <- paste(string_a,string_b,string_c,sep = "")
  marc_field100[,i+3] <- ifelse(grepl(subfield_list_char2[i],marc_field100$X100),str_replace_all(gsub(string,"\\3",marc_field100$X100),"\\${2}.", "~"),NA)
}

marc_field100$`$d` <- ifelse(substr(marc_field100$`$d`,1,1)!="(",paste("(",marc_field100$`$d`,sep = ""),ifelse(is.na(marc_field100$`$d`),NA,as.character(marc_field100$`$d`)))
marc_field100$`$d` <- str_replace(marc_field100$`$d`,";",")")
marc_field100$`$d` <- str_replace(marc_field100$`$d`,"\\)\\.",")")
marc_field100$`$a` <- str_replace(marc_field100$`$a`,"([a-zaáàâãäăāåąæeéèêëěēėęiíìîïīįioóòôõöőøœuúùûüűūůyýcćčçdďđđgģğkķlłļnńñňņŋrřsśšşsßtťŧþţ8zżźž])(\\.)$","\\1")
marc_field100 <- marc_field100 %>%
  mutate(name = ifelse(!is.na(`$d`),paste(`$a`,`$d`,sep = " "),`$a`)) %>%
  select(X009,name)

marc_field245 <- bn_ks_light %>%
  select(X009,X245)

marc_field245$X245<-str_remove_all(marc_field245$X245,"^\\|")
marc_field245 <- mutate(marc_field245,
               indicator = str_replace_all(marc_field245$X245,"(^.*?)(\\$.*)","\\1"))
subfield_list<- str_extract_all(bn_ks_light$X245,"\\$.")
subfield_list<- unique(unlist(subfield_list))
empty_table<- data.frame(matrix(ncol = length(subfield_list),nrow = lengths(marc_field245)[1]))
colnames(empty_table) <-subfield_list

marc_field245<-cbind(marc_field245,empty_table)

subfield_list_char <- paste("(",subfield_list,")",sep = "")
subfield_list_char <- str_replace_all(subfield_list_char,"\\$","\\\\$")

x <- 1:length(subfield_list)

for (i in x) {
  progress(match(i,x), max.value = length(x))
  marc_field245$X245 <- str_replace(marc_field245$X245,subfield_list_char[i],"|\\1")
}
for (i in x) {
  progress(match(i,x), max.value = length(x))
  subfield_list_char2 <- str_replace_all(subfield_list,"\\$","\\\\$")
  string_a <- "(^)(.*?\\|"
  string_b <- subfield_list_char2[i]
  string_c <- ")(.*?)(\\,{0,1})((\\|\\$)(.*)|$)"
  string <- paste(string_a,string_b,string_c,sep = "")
  marc_field245[,i+3] <- ifelse(grepl(subfield_list_char2[i],marc_field245$X245),str_replace_all(gsub(string,"\\3",marc_field245$X245),"\\${2}.", "~"),NA)
}
marc_field245$`$a` <- str_replace(marc_field245$`$a`,"..$","")
marc_field245 <- marc_field245 %>%
  select(X009,title = `$a`)

bn_ks_light <- merge(bn_ks_light,marc_field100,by = "X009",all.x = TRUE)
bn_ks_light <- merge(bn_ks_light,marc_field245,by = "X009",all.x = TRUE)

bn_ks_light <- bn_ks_light %>%
  select(X009,name,title) %>%
  unique()

plist <- pbl_bn %>%
  filter(is.na(is_the_same)) %>%
  mutate(name_single = str_replace_all(name,"([A-ZAÁÀÂÃÄĂĀÅĄÆEÉÈÊËĚĒĖĘIÍÌÎÏĪĮIOÓÒÔÕÖŐØŒUÚÙÛÜŰŪůYÝCĆČçDĎĐĐGĢĞKĶLŁĻNŃÑŇŅŊRŘSŚŠŞSßTŤŦÞŢ8ZŻŹŽ][a-zaáàâãäăāåąæeéèêëěēėęiíìîïīįioóòôõöőøœuúùûüűūůyýcćčçdďđđgģğkķlłļnńñňņŋrřsśšşsßtťŧþţ8zżźž-]*){0,1}([A-ZAÁÀÂÃÄĂĀÅĄÆEÉÈÊËĚĒĖĘIÍÌÎÏĪĮIOÓÒÔÕÖŐØŒUÚÙÛÜŰŪůYÝCĆČçDĎĐĐGĢĞKĶLŁĻNŃÑŇŅŊRŘSŚŠŞSßTŤŦÞŢ8ZŻŹŽ][a-zaáàâãäăāåąæeéèêëěēėęiíìîïīįioóòôõöőøœuúùûüűūůyýcćčçdďđđgģğkķlłļnńñňņŋrřsśšşsßtťŧþţ8zżźž]*,)","\\|\\1\\2"))
plist <- plist %>%
  mutate(name_single = str_replace_all(name_single,"(\\) )([A-ZAÁÀÂÃÄĂĀÅĄÆEÉÈÊËĚĒĖĘIÍÌÎÏĪĮIOÓÒÔÕÖŐØŒUÚÙÛÜŰŪůYÝCĆČçDĎĐĐGĢĞKĶLŁĻNŃÑŇŅŊRŘSŚŠŞSßTŤŦÞŢ8ZŻŹŽ])","\\1\\|\\2"))

plist <- cSplit(plist,"name_single",sep = "|",direction = "long")
plist <- plist %>%
  filter(name_single!="")

test <- merge(x=plist,y=bn_ks_light,by.x = "name_single",by.y = "name")

#przygotowano danych pbl
ks_pbl <- dbGetQuery(PBL,
                          "select tw.tw_tworca_id, tw.tw_nazwisko||', '||tw.tw_imie \"osoba\",z.za_tytul
                          from pbl_writers tw
                          join IBL_OWNER.pbl_zapisy_tworcy zt on zt.zatw_tw_tworca_id=tw.tw_tworca_id
                          join IBL_OWNER.pbl_zapisy z on zt.zatw_za_zapis_id=z.za_zapis_id
                          and z.za_rz_rodzaj1_id in (1,741)")

writers_search <- pbl_bn %>%
  filter(is.na(is_the_same)) %>%
  select(pbl_id) %>%
  unique()
ks_pbl <- sqldf("select *
              from ks_pbl a
              where a.TW_TWORCA_ID in (select b.pbl_id from writers_search b)")
test <- merge(x=test,y=ks_pbl,by.x = "pbl_id",by.y = "TW_TWORCA_ID")

test$title_simple <- str_replace_all(str_to_lower(test$title), "\\W", "")
#test$title_simple <- iconv(test$title_simple, from ="UTF-8", to="ASCII//TRANSLIT")
test$ZA_TYTUL_simple <- str_replace_all(str_to_lower(test$ZA_TYTUL), "\\W", "")

x <- 1:length(test$pbl_id)
for (i in x) {
  progress(match(i,x), max.value = length(x)) 
  test$jest_hit[i] <- grepl(test$title_simple[i],test$ZA_TYTUL_simple[i])
}
efekt_full <- test %>%
  filter(jest_hit==TRUE) %>%
  select(1:12) %>%
  unique()
efekt_full$is_the_same <- "yes"
efekt <- efekt_full %>%
  select(pbl_id) %>%
  unique()

test2 <- pbl_bn
test2 <- sqldf("select *
              from test2 a
              where a.pbl_id in (select b.pbl_id from efekt b)")
efekt_light <- efekt_full %>%
  select(lp,hit = is_the_same)

test2 <- merge(test2,efekt_light,by = "lp",all.x = TRUE)

test2$is_the_same <- ifelse(test2$hit=="yes","yes","no")
test2$is_the_same[is.na(test2$is_the_same)] <- "no"
test2 <- test2 %>%
  select(lp,hit = is_the_same)
pbl_bn <- merge(pbl_bn,test2,by = "lp",all = TRUE)
pbl_bn <- pbl_bn %>%
  mutate(is_the_same = ifelse(!is.na(is_the_same),as.character(is_the_same),ifelse(hit=="yes","yes",ifelse(hit=="no","no",NA)))) %>%
  select(-hit)
pbl_bn <- pbl_bn %>%
  unique()
```

```{r reconciliation based on works in pbl and VIAF}
nlp_with_viaf <- pbl_bn %>%
  filter(is.na(is_the_same)) %>%
  select(id,name,viaf) %>%
  filter(viaf!="") %>%
  unique()

#VIAF XML extracion
tabela_viaf <- data.frame(id = character(), name = character(), gender = character(), VIAF = character(), Zawod = character(), Linki = character(), Tworczosc = character(), stringsAsFactors=FALSE)

x <- 1:length(nlp_with_viaf$id)

for (i in x) {
  progress(i, max.value = length(x))
  tryCatch({
  poczatek_url <- nlp_with_viaf$viaf[i]
  konoc_url <- "/viaf.xml"
  url <- paste(poczatek_url, konoc_url, sep = "")
  xml <- xmlParse(url)
  
  tryCatch({
  a <- setNames(xmlToDataFrame(nodes = getNodeSet(xml, "//ns1:VIAFCluster/ns1:fixed/ns1:gender")),"gender")
  a2 <- paste(unlist(a), collapse = "~")
  }, error=function(e){
                      a2 <<- "no data (CR)"
  })
  tryCatch({
    e <- setNames(xmlToDataFrame(nodes = getNodeSet(xml, "//ns1:VIAFCluster/ns1:occupation/ns1:data/ns1:text")),"Zawod")
  e2 <- paste(unlist(e), collapse = "~")
  }, error=function(e){
                      e2 <<- "no data (CR)"
  })
  tryCatch({
    f <- setNames(xmlToDataFrame(nodes = getNodeSet(xml, "//ns1:VIAFCluster/ns1:xLinks/ns1:xLink")),"Linki")[1]
  f2 <- paste(unlist(f), collapse = "~")
  }, error=function(e){
                      f2 <<- "no data (CR)"
  })
  tryCatch({
    g <- setNames(xmlToDataFrame(nodes = getNodeSet(xml, "//ns1:VIAFCluster/ns1:titles/ns1:work/ns1:title")),"Tworczosc")
  g2 <- paste(unlist(g), collapse = "~")
  }, error=function(e){
                      g2 <<- "no data (CR)"
  })
  total_i <- data.frame(id = nlp_with_viaf$id[i], name = nlp_with_viaf$name[i], gender = a2, VIAF = nlp_with_viaf$viaf[i], Zawod = e2, Linki = f2, Tworczosc = g2)
  
  tabela_viaf <- rbind(tabela_viaf,total_i)
  }, error=function(e){NULL})
}

tabela_viaf$tworczosc_nazwa <- str_replace_all(str_to_lower(tabela_viaf$Tworczosc), "\\W", "")
tabela_viaf$tworczosc_nazwa <- iconv(tabela_viaf$tworczosc_nazwa, from ="UTF-8", to="ASCII//TRANSLIT")

#--------------------------------------------------------------------

#PBL_books
pbl_ksiazki <- dbGetQuery(PBL,
                          "select tw.tw_tworca_id, z.za_zapis_id, z.za_tytul
                          from pbl_tworcy tw
                          join IBL_OWNER.pbl_zapisy_tworcy zt on zt.zatw_tw_tworca_id=tw.tw_tworca_id
                          join pbl_zapisy z on zt.zatw_za_zapis_id=z.za_zapis_id
                          where z.za_rz_rodzaj1_id=1")
pbl_ksiazki$ZA_TYTUL <- ifelse(substr(pbl_ksiazki$ZA_TYTUL,nchar(pbl_ksiazki$ZA_TYTUL),nchar(pbl_ksiazki$ZA_TYTUL))=="]",str_replace(pbl_ksiazki$ZA_TYTUL,"(^.*?)(\\[.*$)","\\1"),ifelse(substr(pbl_ksiazki$ZA_TYTUL,1,1)=="[",str_replace(pbl_ksiazki$ZA_TYTUL,"(^.*?\\])(.*$)","\\2"),as.character(pbl_ksiazki$ZA_TYTUL)))

pbl_ksiazki <- sqldf("select *
               from pbl_ksiazki a
               where a.TW_TWORCA_ID in 
               (select b.pbl_id
               from pbl_bn b)")
pbl_ksiazki$tytul_nazwa <- str_replace_all(str_to_lower(pbl_ksiazki$ZA_TYTUL), "\\W", "")
pbl_ksiazki$tytul_nazwa <- iconv(pbl_ksiazki$tytul_nazwa, from ="UTF-8", to="ASCII//TRANSLIT")

#SQL
merge_ksiazek <- sqldf("select * 
                       from tabela_viaf a
                       inner join pbl_ksiazki b on a.tworczosc_nazwa like ('%'||b.tytul_nazwa||'%')")
merge_ksiazek <- merge_ksiazek %>%
  filter(ZA_TYTUL!="")

id_bn_id_pbl <- pbl_bn %>%
  select(pbl_id,id,name,viaf) %>%
  filter(!is.na(viaf)) %>%
  unique() %>%
  select(pbl_id,id) %>%
  unique()

merge_ksiazek <- merge(x = merge_ksiazek, y = id_bn_id_pbl, by = "id", all.x = TRUE)

test <- merge_ksiazek %>%
  filter(TW_TWORCA_ID==pbl_id)
test <- test %>%
  select(id,name,gender,VIAF,Zawod,Linki,TW_TWORCA_ID) %>%
  unique()
test_full <- test
test <- test %>%
  select(pbl_id = TW_TWORCA_ID,id)
test$match <- paste(test$pbl_id,test$id,sep = "|")
test$hit <- "yes"
test <- test %>%
  select(match,hit)
pbl_bn$match <- paste(pbl_bn$pbl_id,pbl_bn$id,sep = "|")
pbl_bn <- merge(pbl_bn,test,by = "match",all.x = TRUE)
pbl_bn <- pbl_bn %>%
  mutate(is_the_same = ifelse(!is.na(is_the_same),as.character(is_the_same),ifelse(hit=="yes","yes",ifelse(hit=="no","no",NA))))
test3 <- pbl_bn
test3 <- test3 %>%
  mutate(hit = ifelse(is.na(hit),is_the_same,as.character(hit))) %>%
  mutate(is_the_same = hit) %>%
  select(-hit,-match)

test3 <- test3 %>%
  mutate(czy2 = is_the_same) %>%
  arrange(pbl_id,is_the_same) %>%
  group_by(pbl_id) %>%
  fill(czy2)

test4 <- test3 %>%
  mutate(is_the_same = ifelse(is.na(is_the_same)&czy2=="yes","no",ifelse(is_the_same=="yes"&czy2=="yes","yes",ifelse(is_the_same=="rather yes"&czy2=="rather yes","rather yes", ifelse(is_the_same=="no"&czy2=="no","no",NA)))))

pbl_bn <- test4 %>%
  select(-czy2) %>%
  filter(is.na(is_the_same)|is_the_same!="no")

```

```{r removing people with non-philological descriptors}
deskryptory <- gs_read(gs_title("deskryptory"), ws = "deskryptory")
deskryptory <- deskryptory %>%
  filter(czy_zostaje=="nie")
deskryptory$deskryptory <- paste("(",deskryptory$deskryptory,")",sep = "")
test <- sqldf("select * 
                from pbl_bn a
                join deskryptory b on a.name like ('%'||b.deskryptory||'%')")
test <- test %>%
  mutate(is_the_same = ifelse(!is.na(is_the_same),as.character(is_the_same),czy_zostaje)) %>%
  mutate(czy_liczba = grepl("\\d+",name))
test <- test %>%
  filter(czy_liczba==FALSE) %>%
  filter(is_the_same=="nie") %>%
  select(lp,czy2 = is_the_same)
pbl_bn <- merge(pbl_bn,test,by = "lp",all.x = TRUE)
pbl_bn <- pbl_bn %>%
  mutate(test = ifelse(is_the_same %in% c("yes","no","rather yes"),as.character(is_the_same),ifelse(czy2=="nie","no",NA)))
pbl_bn <- pbl_bn %>%
  mutate(is_the_same = test) %>%
  select(-czy2,-test) %>%
  unique() %>%
  filter(is_the_same!="no"|is.na(is_the_same))

test <- pbl_bn %>%
  filter(is.na(is_the_same))

count <- as.data.frame(table(test$pbl_id))

test <- merge(x=test,y=count, by.x = "pbl_id", by.y = "Var1")
test <- test %>%
  mutate(is_the_same = ifelse(is.na(is_the_same)&Freq==1,"rather yes",NA))
test <- test %>%
  filter(!is.na(is_the_same)) %>%
  select(lp,czy2 = is_the_same)

pbl_bn <- merge(pbl_bn,test,by = "lp",all.x = TRUE)
pbl_bn <- pbl_bn %>%
  mutate(test = ifelse(is_the_same %in% c("yes","no","rather yes"),as.character(is_the_same),ifelse(czy2=="rather yes","rather yes",NA)))

pbl_bn <- pbl_bn %>%
  mutate(is_the_same = test) %>%
  select(-czy2,-test) %>%
  unique() %>%
  filter(is_the_same!="no"|is.na(is_the_same))
```