#2022 01 17 #TH Mapping all addresses #Convert to one of 5 outcomes: #Overseas #University #Government #Industry #Unknown #Using If statements to convert, and the TH_Univ, TH_Govt and TH_Indust Scripts #Required packages library(stringr) library(stringi) #install.packages(DataCombine) library(DataCombine) setwd("C:/Users/Ed/OneDrive/PhD/R/2022_Ag7") #start_time <- Sys.time() #optional #source("rs2021_12_08_Reorder_and_Clean_All.R") #source("rs_2021_11_09_Combing_Addresses.R") #reorder_end_time <- Sys.time() source("2022_01_31_TH_Univ_Ag7.R") #univ_time_end_time <- Sys.time() source("2022_01_31_TH_Govt_Ag7.R") #govt_time_end_time <- Sys.time() source("2022_01_31_TH_Indust_Ag7.R") #indust_time_end_time <- Sys.time() #total_end_time <- Sys.time() TH_List_Ag7 <- do.call("cbind", list(Combined_Addresses_Ag7, university_ag7_th, government_ag7_th, industry_ag7_th)) colnames(TH_List_Ag7, do.NULL = FALSE) #Extra columns take on name from previous script #university #government #industry #Duplicate WoS_All_Record and Convert to data frame for FindReplace instead of a Dplyr tibble (https://github.com/lyons7/emojidictionary/issues/1) Ag7_TH <- as.data.frame(Ag7_All) #Error Fixes #Fix of a record that threw an invalid regular expression because of a missing ) - record 96462 - fix using https://stackoverflow.com/questions/27721008/how-do-i-deal-with-special-characters-like-in-my-regex Only one occurance so use sub instead of gsub #Error was "(UFJF, Juiz De Fora, Brazil;", which required editing the CSV file to correct (2013, Savedrecs(18), line 498) by adding the required ) in the original record. #Error in gsub(pattern = replaceData[i, from], replacement = replaceData[i, : invalid regular expression 'CREAF, Cerdanyola Del Valles (18193, Catalonia, Spain', reason 'Missing ')'' Corrected in Reorder and clean #Use gsub to find all occurences of * #WoS_TH <- gsub("\\*|, , ,|, ,", "",WoS_TH) #WoS_TH <- as.data.frame(WoS_TH) #WoS_TH <- gsub(", , ,|, ,", "", WoS_TH) #WoS_TH <- as.data.frame(WoS_TH) #Find Other Australian State Data TH_List_Ag7$States <- str_detect(Combined_Addresses_Ag7$Short_Address, "Australia, Vic|Australia, Qld|Australia, SA|Australia, Tas|Australia, WA|Australia, ACT|Australia, NT", negate = FALSE) #Moved TH LIst to after Address Cleanup TH_List_Ag7$TH <- ifelse(TH_List_Ag7$Australian == 0, "Overseas", ifelse(TH_List_Ag7$States == 1, "Other_State", ifelse(TH_List_Ag7$university == 1, "University", ifelse(TH_List_Ag7$government == 1, "Government", ifelse(TH_List_Ag7$industry == 1, "Industry", "Unknown"))))) # Create replacements data frame Ag7_TH_Match <- data.frame(from = Combined_Addresses_Ag7$Full_Address, to = TH_List_Ag7$TH) #12998 # Create replacements data frame TH_Match_Ag7 <- data.frame(from = Combined_Addresses_Ag7$Full_Address, to = TH_List_Ag7$TH) TH_Match_Ag7 <- na.omit(TH_Match_Ag7) #TH_Match <- TH_Match[-1237257,, drop=FALSE] #Regex problem thrown up again with ( and ) TH_Match_Ag7$from <- gsub("\\), Penrith", ", Penrith",TH_Match_Ag7$from) TH_Match_Ag7$from <- gsub("\\), Shanghai", ", Shanghai",TH_Match_Ag7$from) TH_Match_Ag7$from <- gsub("\\) El", " El",TH_Match_Ag7$from) TH_Match_Ag7$from <- gsub("\\), Rua", ", Rua",TH_Match_Ag7$from) TH_Match_Ag7$from <- gsub("\\*", "",TH_Match_Ag7$from) TH_Match_Unique_Ag7 <- unique(TH_Match_Ag7) TH_Match_Ag7_From <- TH_Match_Unique_Ag7$from TH_Match_Ag7_To <- TH_Match_Unique_Ag7$to TH_Match__Ag7From_DF <- as.data.frame(TH_Match_Ag7_From) TH_Match__Ag7To_DF <- as.data.frame(TH_Match_Ag7_To) Ag7_TH_D<- as.data.frame(Ag7_TH$Ag7_D) colnames(Ag7_TH_D) <- c("Addresses") Ag7_TH_D$Addresses <- gsub("\\), Penrith", ", Penrith",Ag7_TH_D$Addresses) Ag7_TH_D$Addresses <- gsub("\\), Shanghai", ", Shanghai",Ag7_TH_D$Addresses) Ag7_TH_D$Addresses <- gsub("\\) El", " El",Ag7_TH_D$Addresses) Ag7_TH_D$Addresses <- gsub("\\), Rua", ", Rua",Ag7_TH_D$Addresses) Ag7_TH_D$Addresses <- gsub("\\*", "",Ag7_TH_D$Addresses) #Replace patterns and return full data frame #https://www.rdocumentation.org/packages/DataCombine/versions/0.2.21/topics/FindReplace #https://www.r-bloggers.com/2013/12/three-quick-and-simple-data-cleaning-helper-functions-december-2013/ #install.packages('data.table') #library('data.table') #TH_Match_DT <- as.data.table(TH_Match_Unique) #setorder(TH_Match_DT, cols = - "from") #setkey(TH_Match_DT, from) #frst_F <- Sys.time() #Matched <- FindReplace(data = WoS_TH_D, Var = "Addresses", TH_Match_DT, # from = "from", to = "to", exact = FALSE, vector = TRUE) #Kept returning NA; found error in row in TH_Match[-c(1237257),] #fret_F <- Sys.time() #https://stackoverflow.com/questions/33949945/replace-multiple-strings-in-one-gsub-or-chartr-statement-in-r/33950155 library(stringi) #stringi_st <- Sys.time() Ag7_TH_V <- Ag7_TH_D$Addresses #TH_Match_From_v <- as.vector(TH_Match$from) #TH_Match_To_v <- as.vector(TH_Match$to) Ag7_TH_M <- stri_replace_all_fixed(Ag7_TH_V, pattern = TH_Match_Ag7_From, replacement = TH_Match_Ag7_To, vectorise_all = FALSE) #stringi_et <- Sys.time() #stringi_elapsed <- (stringi_et-stringi_st) #View(stringi_elapsed) Ag7_TH_M <- as.data.frame(Ag7_TH_M) colnames(Ag7_TH_M) <- c("Addresses") #Bring across to full list WoS_All_Record #https://stackoverflow.com/questions/25372082/create-column-based-on-presence-of-string-pattern-and-ifelse Ag7_TH_M$TH_U <- ifelse(grepl("University",Ag7_TH_M$Addresses),'1','0') Ag7_TH_M$TH_I <- ifelse(grepl("Industry",Ag7_TH_M$Addresses),'1','0') Ag7_TH_M$TH_G <- ifelse(grepl("Government",Ag7_TH_M$Addresses),'1','0') Ag7_TH_M$TH_F <- ifelse(grepl("Overseas",Ag7_TH_M$Addresses),'1','0') Ag7_TH_M$TH_S <- ifelse(grepl("Other_State",Ag7_TH_M$Addresses),'1','0') Ag7_TH_M$TH_UK <- ifelse(grepl("Unknown",Ag7_TH_M$Addresses),'1','0') #Join to original dataset, and remove Unknowns Ag7_TH_TH <- cbind(Ag7_TH, Ag7_TH_M) Ag7_TH_M_TH_SS <- subset(Ag7_TH_TH, TH_UK == 0) #Example subsequent subset: #WoS_All_Record_TH_SS_2009 <- subset(WoS_All_Record_SS$Pub_year == "2009") #TH Calc