Instead, learn how to use the merge function, or perhaps the dplyr::left_join function. VLOOKUP is really not necessary.
On November 18, 2020 7:11:49 AM PST, Gregg via R-help <r-help@r-project.org> wrote: >Thanks Andrew and Mitch for your help. > >With your assistance, I was able to sort this out. > >Since I have to do this type of thing of often, and since there is no >existing package/function (yet) that makes this easy, if ever I get to >the point were I develop enough skill to build and submit a new >package, a simple little VLOOKUP(like) function contained in a package >would be of great use. > >r/ >Gregg > > > > >‐‐‐‐‐‐‐ Original Message ‐‐‐‐‐‐‐ >On Monday, November 16, 2020 1:56 PM, Gregg via R-help ><r-help@r-project.org> wrote: > >> PROBLEM: I am trying to replicate something like a VLOOKUP in R but >am having no success - need a bit of help. >> > >> GIVEN DATA SET (data.table): (looks something like this, but much >bigger) >> > >> NAME TOTALAUTH ASSIGNED_COMPANY >> ABERDEEN PROVING GROUND 1 NA >> ADELPHI LABORATORY CENTER 1 NA >> CARLISLE BARRACKS 1 NA >> DETROIT ARSENAL 1 NA >> DUGWAY PROVING GROUND 1 NA >> FORT A P HILL 1 NA >> FORT BELVOIR 1 NA >> FORT BENNING 1 NA >> FORT BLISS 1 NA >> FORT BRAGG 1 NA >> FORT BUCHANAN 1 NA >> > >> I am trying to update the values in the ASSIGNED_COMPANY column from >NAs to a value that matches based on the "key" word like below. >> > >> NAME TOTALAUTH ASSIGNED_COMPANY >> ABERDEEN PROVING GROUND 1 NEC Aberdeen >> ADELPHI LABORATORY CENTER 1 NEC Adelphi >> CARLISLE BARRACKS 1 NEC Carlise >> DETROIT ARSENAL 1 NEC Detroit >> DUGWAY PROVING GROUND 1 NEC Dugway >> FORT A P HILL 1 NEC AP Hill >> FORT BELVOIR 1 NEC Belvoir >> FORT BENNING 1 NEC Benning >> FORT BLISS 1 NEC Bliss >> FORT BRAGG 1 NEC Bragg >> FORT BUCHANAN 1 NEC Buchanon >> > >> In a nutshell, for instance....... >> > >> I want to search for the keyword "ABERDEEN" in the NAME column, and >for every row where it exists, I want to update the NA in the >ASSIGNED_COMPANY column to "NEC Aberdeen" >> > >> I want to search for the keyword "ADELPHI" in the NAME column, and >for every row where it exists, I want to update the NA in the >ASSIGNED_COMPANY column to "NEC ADELPHI" >> > >> ....... and so on for every value in the NAME column - so in the end >a I have matching names in the ASSIGNED_COMPANY column. >> > >> I can use an if statement because it is not vectorized. >> > >> If I use an ifelse statement, the "else" rewrites any changes with "" >> > >> Something so simple should not be difficult. >> > >> Some of the methods I attempted to use are below along with the >errors I get... >> > >> ###################CODE####################################### >> > >> library(data.table) >> library(dplyr) >> library(stringr) >> > >> VLOOKUP_inR <- data.table::fread("DATASET_TESTINGONLY.csv") >> > >> #METHOD 1 FAILS >> VLOOKUP_inR %>% dplyr::rename_if(grepl("ADELPHI", VLOOKUP_inR$NAME, >useBytes = TRUE), "NEC Adelphi") >> > >> Error in get(.x, .env, mode = "function") : >> > >> object 'NEC Adelphi' of mode 'function' was not found >> > >> #METHOD 2 FAILS >> if(stringr::str_detect(VLOOKUP_inR$NAME, "ADELPHI")) { >> VLOOKUP_inR$ASSIGNED_COMPANY == "NEC Adelphi" >> } >> > >> Warning message: >> In if (stringr::str_detect(VLOOKUP_inR$NAME, "ADELPHI")) { : >> the condition has length > 1 and only the first element will be used >> > >> #METHOD 3 FAILS >> ifelse(stringr::str_detect(ASIP_combined_location_tally$NAME, >"ADELPHI"), ASIP_combined_location_tally$ASSIGNED_COMPANY == >ASIP_combined_location_tally$ASSIGNED_COMPANY) >> > >> Error in >ifelse(stringr::str_detect(ASIP_combined_location_tally$NAME, : >> > >> argument "no" is missing, with no default >> > >> #METHOD4 FAILS >> VLOOKUP_inR_matching <- VLOOKUP_inR %>% mutate(ASSIGNED_COMPANY = >ifelse(grepl(pattern = 'ABERDEEN', x = NAME), 'NEC Aberdeen', '')) >> VLOOKUP_inR_matching <- VLOOKUP_inR_matching %>% >mutate(ASSIGNED_COMPANY = ifelse(grepl(pattern = 'ADELPHI', x = NAME), >'NEC Adelphi', '')) >> > >> VLOOKUP_inR_matching <- VLOOKUP_inR_matching %>% >mutate(ASSIGNED_COMPANY = ifelse(grepl(pattern = 'CARLISLE', x = NAME), >'NEC Carlisle Barracks', '')) >> VLOOKUP_inR_matching <- VLOOKUP_inR_matching %>% >mutate(ASSIGNED_COMPANY = ifelse(grepl(pattern = 'DETROIT', x = NAME), >'NEC Detroit Arsenal', '')) >> VLOOKUP_inR_matching <- VLOOKUP_inR_matching %>% >mutate(ASSIGNED_COMPANY = ifelse(grepl(pattern = 'BELVOIR', x = NAME), >'NEC Fort Belvoir', '')) >> > >> -----------the 4th method just over writes all previous changers back >to "" >> > >> >###################################################################### >> > >> Any help offered would be so very greatly appreciated. >> > >> Thanks you. >> > >> r/ >> gregg powell >> AZ >> > >> R-help@r-project.org mailing list -- To UNSUBSCRIBE and more, see >> https://stat.ethz.ch/mailman/listinfo/r-help >> PLEASE do read the posting guide >http://www.R-project.org/posting-guide.html >> and provide commented, minimal, self-contained, reproducible code. -- Sent from my phone. Please excuse my brevity. ______________________________________________ R-help@r-project.org mailing list -- To UNSUBSCRIBE and more, see https://stat.ethz.ch/mailman/listinfo/r-help PLEASE do read the posting guide http://www.R-project.org/posting-guide.html and provide commented, minimal, self-contained, reproducible code.