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

[Gregg]

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.


-------------- next part --------------
A non-text attachment was scrubbed...
Name: signature.asc
Type: application/pgp-signature
Size: 509 bytes
Desc: OpenPGP digital signature
URL: <https://stat.ethz.ch/pipermail/r-help/attachments/20201118/3038769b/attachment.sig>