[R] - Trying to replicate VLOOKUP in R - help needed

[Jeff Newmiller]

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 using 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 using 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 using 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.