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

Gregg g@@@powe|| @end|ng |rom protonm@||@com
Wed Nov 18 17:03:52 CET 2020


I will do that...

Thanks again Jeff.

r/
Gregg Powell




‐‐‐‐‐‐‐ Original Message ‐‐‐‐‐‐‐
On Wednesday, November 18, 2020 8:36 AM, Jeff Newmiller <jdnewmil using dcn.davis.ca.us> wrote:

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


-------------- 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/13bdf277/attachment.sig>


More information about the R-help mailing list