Excel search and find functions in R

Posted on: November 11, 2017 Posted by: Guy Manova Comments: 0

Excel search and find functions in R

Excel search and find functions in R

In this short tutorial we’ll see how to use the grepl function in base R and/or the stringr package to mimic Excel search and find functions in R.

# pre-session options

rm(list = ls())
#getwd()
#setwd(“C:\\Users\\User\\Documents”)

# load some packages

if (!require(stringr)) install.packages(‘stringr’)
library(stringr)
if (!require(dplyr)) install.packages(‘dplyr’)
library(dplyr)

# load the dataset and write to csv to open in Excel

campaigns <- read.csv(“https://www.dropbox.com/s/gv83asx8qeuog0z/google%20campaigns.csv?dl=1”)
write.csv(campaigns, “campaigns.csv”)

in Excel

use search() as an interim step to parse text:

excel search and find functions in R

excel search and find functions in R

excel search and find functions in R

 

In R

# first lets see how a basic search works, and how to use it with/out case recognition:

grepl(“ios”,”iOS_Search_UK”) # yields 0 (FALSE) – the equivalent of “FIND()”
grepl(“ios”,tolower(“iOS_Search_UK”)) # yields 1 (TRUE) – the equivalent of “SEARCH()”

 

# and you can simply pass the ignore.case argument to handle case sensitivness:

grepl(“ios”,”iOS_Search_UK”, ignore.case = TRUE) # yields 1 (TRUE)

excel search and find functions in R

# Now let’s create a new metadata column of which phone type:

campaigns$PhoneType <- ifelse(grepl(“ios”,campaigns$Campaign, ignore.case = TRUE), “iPhone”, “Android”)

excel search and find functions in R

# lets see how this can look with stringr and dplyr, which are very popular tidyverse packages:

campaigns$PhoneType = NULL

str_detect(tolower(campaigns$Campaign), “ios”)

excel search and find functions in R

campaigns <- campaigns %>%
mutate(PhoneType =
if_else(str_detect(tolower(campaigns$Campaign), “ios”),”iPhone”, “Android”))

excel search and find functions in R

# in case you actually want to know the index place in the vector where the expression
# starts, like Find() or Search() in excel, you can use the str_locate() function
# which outputs a MATRIX of the start position and end position. so assuming you want the
# start position:

str_locate(campaigns$Campaign, “iOS”)

excel search and find functions in R

# now this is a MATRIX, so we can extract the “start” column, and add it to the
# dataset as a new column:

campaigns$OS_Start <- str_locate(campaigns$Campaign, “iOS”)[,1]

excel search and find functions in R

 

 

Leave a Reply:

Your email address will not be published. Required fields are marked *