R - Using loops to search one variable with another and create new merged variable

user_16079343 0
Viewed 41

I am only good at basics in R coding and am not sure how to do complex loops. I have a large dataframe that has a column of addresses and a column of zipcodes (among other variables). There are many NA's in each column. The address column has incomplete data for some rows where the zip and state or just the zip is missing. All of my data is from the same state.

I need to clean the address data for geocoding later. I don't want to omit the NA rows because I will use the same dataframe for zipcode-only analysis as well (also I get lost when I make too many similar objects). I have a lot of NAs in Address where I still have a Zipcode value for the same row.

My idea for a solution is:

  1. for each row in df$Address, if it is not an NA
  2. and if df$Address doesn't end with df$Zipcode
  3. then paste df$Address combined with "MI " and df$Zipcode as new column value
  4. remove duplicate words (for the entries where it had address with state but missing zip only)

Minimum reproducible sample:

Address <- c("1234 address", "5678 address, MI", "9012 address, MI 12345", "1111 adrus")
Zipcode <- c("67890", "56780", "12345", "12345")
df <- data.frame(Address, Zipcode)

#example dataframe
Address                   Zipcode
1234 address               67890            
5678 address, MI           56780            
9012 address, MI 12345     12345            
1111 adrus                 12345

Here is my attempt. I tried with grepl() as well and got the same error.

for (i in 1:length(df$Address)) {
  if(!is.na(df$Address[i])) {
    if (!endsWith(df$Address, df$Zipcode)) {
       df$Address_cln<- paste(df$Address, df$Zipcode, sep="MI ")
    }
    else {dataZip$address_cln <- paste(dataZip$Address)
    }}}

I get this error: the condition has length > 1 and only the first element will be used . It pastes the unmodified df$Address into the new column df$Address_cln

I was trying to get the first 3 parts coded right before moving on to removing duplicate "MI" so that isn't included in my code.

I will try to fix the misspelled entries somehow later, since that's a big ask in addition to help with my merge loop above. Thanks for your help!

na dataframe r merge loops
3 Answers

We can check if the 'Address' have 5 digits at the end of the string, then return the 'Address' or else paste (str_c) with the 'Zipcode' column

library(dplyr)
library(stringr)
df %>%
  mutate(Address2 = case_when(str_detect(Address, '\\d{5}$') ~
    Address, str_detect(Address, 'MI$') ~ str_c(Address, Zipcode, 
     sep = ' '), TRUE ~ str_c(Address, Zipcode, sep = ', MI ')))

-output

#                 Address Zipcode               Address2
#1           1234 address   67890  1234 address MI 67890
#2       5678 address, MI   56780 5678 address, MI 56780
#3 9012 address, MI 12345   12345 9012 address, MI 12345
#4             1111 adrus   12345    1111 adrus, MI 12345
user_37322710

We could use some stringr function with regular expressions:

library(stringr)
library(dplyr)
df %>% 
  mutate(Address= str_remove(Address, "[0-9]*$"),
         Address= str_trim(str_remove(Address, ", MI"))) %>% 
  mutate(Address_new = str_c(Address, " MI ", Zipcode))

Output:

       Address Zipcode           Address_new
1 1234 address   67890 1234 address MI 67890
2 5678 address   56780 5678 address MI 56780
3 9012 address   12345 9012 address MI 12345
4   1111 adrus   12345   1111 adrus MI 12345
user_133216470

Using Base R, I have this solution for you, but be aware that i took the "paste" with the sep="MI" from your code, the 4th element of your address already have "MI", so this adds a dubble "MI" to the output, without any knowlegde my guess is that its a state abriviation and will most likely not be correct if the data covers multiple states.

Address <- c("1234 address", "5678 address, MI", "9012 address, MI 12345", "1111 adrus")
Zipcode <- c("67890", "56780", "12345", "12345")
df <- data.frame(Address, Zipcode)    

df$address_cln <- apply(df,MARGIN = 1, function(df_row) {
  if(!grepl(paste0(df_row[2],"$"), df_row[1])){
    return(paste(df_row[1], df_row[2], sep=" MI "))
  }else{
    return(df_row[1])
  }
})

df

         Address           Zipcode             address_cln
1           1234 address   67890     1234 address MI 67890
2       5678 address, MI   56780 5678 address, MI MI 56780
3 9012 address, MI 12345   12345    9012 address, MI 12345
4             1111 adrus   12345       1111 adrus MI 12345
user_36723740
Related