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

[Andrew Robinson]

Hi Gregg,

it's not clear from your context if all of ASSIGNED _COMPANY is NA or what the classes of the objects are.  Try the following ideas, none of which are tested.  I assume that the data set is called location.

location$ASSIGNED_COMPANY <- as.character(location$NAME)

is.a.FORT <- substr(location$ASSIGNED_COMPANY, 1, 4) == "FORT"

location$ASSIGNED_COMPANY[!is.a.FORT] <-
  sapply(location$ASSIGNED_COMPANY[!is.a.FORT],
  function(x) strsplit(x)[[1]][[1]]) # retains first name if not a fort

location$ASSIGNED_COMPANY[is.a.FORT] <-
  substr(location$ASSIGNED_COMPANY[is.a.FORT], 6,
  nchar(location$ASSIGNED _COMPANY[is.a.FORT])) # Strips FORT from Forts

substr(location$ASSIGNED_COMPANY, 2, nchar(location$ASSIGNED_COMPANY)) <-
  tolower(substr(location$ASSIGNED _COMPANY, 2,
  nchar(location$ASSIGNED _COMPANY))) # lower case word

location$ASSIGNED_COMPANY <- paste("NEC", location$ASSIGNED_COMPANY)

or you can just do

location$ASSIGNED_COMPANY[location$NAME == "ABERDEEN PROVING GROUND"] <- "NEC Aberdeen"

for each option ....

Cheers,

Andrew

--
Andrew Robinson
Director, CEBRA and Professor of Biosecurity,
School/s of BioSciences and Mathematics & Statistics
University of Melbourne, VIC 3010 Australia
Tel: (+61) 0403 138 955
Email: apro using unimelb.edu.au
Website: https://researchers.ms.unimelb.edu.au/~apro@unimelb/

I acknowledge the Traditional Owners of the land I inhabit, and pay my respects to their Elders.
On Nov 17, 2020, 8:05 AM +1100, 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









	[[alternative HTML version deleted]]