Replace strings in one column which match another column

Question

I have a dataframe that looks like this (but for every US county)

Countyname	Neighbour County	Neighbour State
Autauga County, AL	Chilton County	AL
Autauga County, AL	Dallas County	AL
Baldwin County, AL	Escambia County	FL
Catron County, NM	Apache County	AZ

For all counties, if the neighbouring county is in the same state I want to replace the value in the Neighbour State column with a missing value NA, and if it's in a different state I want to keep it unchanged. I.e. I want to end up with something like this:

Countyname	Neighbour County	Neighbour State
Autauga County, AL	Chilton County	NA
Autauga County, AL	Dallas County	NA
Baldwin County, AL	Escambia County	FL
Catron County, NM	Apache County	AZ

I was thinking of looping through each row and if the column 'Countyname' contains the entry in the column 'Neighbour State', replace the entry with NA (e.g. for the first row if 'Autauga County, AL' contains 'AL', replace 'AL' with NA). How would I go about this (or is there a more efficient way to do it as this feels clunky)?

Consider substring-ing out the last two characters in CountyName and compare it using ifelse with Neighbor State. Give it a try and come back with specific issues. — Parfait
– Parfait, Commented Jul 3, 2021 at 16:43
"I was thinking of looping through each row" - R is a vectorized language, so its functions do this by default. — Phil
– Phil, Commented Jul 3, 2021 at 16:58
I agree with @Phil, loops especially for loops are mostly avoidable in R and that's the beauty of this language. — AnilGoyal
– AnilGoyal, Commented Jul 3, 2021 at 17:01

GuedesBF · Accepted Answer · 2021-07-04 02:30:19Z

1

You can use str_detect() and ifelse()

library(dplyr)
library(stringr)

df%>%
        mutate(Neighbour_State=ifelse(str_detect(Countyname, Neighbour_State), NA, Neighbour_State))

Or, preferably, str_detect and replace()

df%>%
        mutate(Neighbour_State=replace(Neighbour_State, str_detect(Countyname, Neighbour_State), NA))

#Or with pipes:

df%>%
        mutate(Neighbour_State=Neighbour_State%>%replace(., str_detect(Countyname, .), NA))

          Countyname Neighbour_County Neighbour_State
1 Autauga County, AL   Chilton County            <NA>
2 Autauga County, AL    Dallas County            <NA>
3 Baldwin County, AL  Escambia County              FL
4  Catron County, NM    Apache County              AZ

edited Jul 4, 2021 at 2:30

answered Jul 4, 2021 at 2:23

GuedesBF

9,9515 gold badges23 silver badges42 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Jack Salvatore Over a year ago

Thank you! I'm not sure why the suggestions from the other users were not working (the table was left unchanged), but using replace worked.

GuedesBF Over a year ago

Glad I could help. You can check this: stackoverflow.com/help/someone-answers

AnilGoyal · Accepted Answer · 2021-07-03 16:55:45Z

You may also use regex to find stateName from the county and use function na_if to replace values where it matches with the neighbour_county. Like this

regex '.*\\,\\s' will match everything upto a comma and a whitespace and replace it with '' i.e. nothing.
use mutate in combination with ifelse na_if replace case_when or any conditional function.

df <- read.table(header = T, text = "Countyname Neighbour_County    Neighbour_State
'Autauga County, AL'    'Chilton County'    AL
'Autauga County, AL'    'Dallas County' AL
'Baldwin County, AL'    'Escambia County'   FL
'Catron County, NM' 'Apache County' AZ")

library(dplyr, warn.conflicts = F)

df %>% 
  mutate(Neighbour_State = na_if(gsub('.*\\,\\s','', Countyname, perl = T), Neighbour_State))
#>           Countyname Neighbour_County Neighbour_State
#> 1 Autauga County, AL   Chilton County            <NA>
#> 2 Autauga County, AL    Dallas County            <NA>
#> 3 Baldwin County, AL  Escambia County              AL
#> 4  Catron County, NM    Apache County              NM

^{Created on 2021-07-03 by the reprex package (v2.0.0)}

user438383 · Accepted Answer · 2021-07-03 16:57:22Z

0

library(stringr)
dat %>% 
    dplyr::mutate(
        Neighbour.State = dplyr::if_else(
            str_trim(unlist(str_split(dat$Countyname[1], ","))[2]) == Neighbour.State, "NA", Neighbour.State)
        )

          Countyname Neighbour.County Neighbour.State
1 Autauga County, AL   Chilton County              NA
2 Autauga County, AL    Dallas County              NA
3 Baldwin County, AL  Escambia County              FL
4  Catron County, NM    Apache County              AZ

edited Jul 3, 2021 at 16:57

answered Jul 3, 2021 at 16:38

user438383

6,30710 gold badges32 silver badges51 bronze badges

Comments

MonJeanJean · Accepted Answer · 2021-07-03 18:48:29Z

0

You don't need loop for this. You can use dplyr package instead:

data <- data %>%
  mutate(`Neighbour State` = ifelse(substr(Countyname, nchar(Countyname)-1, nchar(Countyname))== `Neighbour State`, NA,`Neighbour State`))

edited Jul 3, 2021 at 18:48

answered Jul 3, 2021 at 16:30

MonJeanJean

2,9161 gold badge7 silver badges22 bronze badges

1 Comment

MonJeanJean Over a year ago

Ho yes, forgot substr was base R, I'll edit!

Collectives™ on Stack Overflow

Replace strings in one column which match another column

4 Answers 4

2 Comments

Comments

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

2 Comments

Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related