Merge and replace character columns

user_11697741 0
Viewed 51

I have a dataframe with 2 columns that I want to merge:

     Region             PA
1     Mbeya    Ruaha National Park
2     Mbeya    Ruaha National Park
3     Mbeya    Ruaha National Park
4     Mbeya    Ruaha National Park
5     Mbeya    Ruaha National Park
6     Mbeya    Ruaha National Park
7     Mbeya    NA
8     Mbeya    NA
9     Mbeya    NA
10    Mbeya    NA

This could be merged by either taking PA values and overwriting Region values in the rows, or replacing all NA's in PA with the values in Region for that row.

I've tried:

  Carcass.cleaned$New<-rowSums(Carcass.cleaned[, c("PA", "Region")], na.rm=T)
    Error in base::rowSums(x, na.rm = na.rm, dims = dims, ...) : 
      'x' must be numeric
    
    with(Carcass.cleaned,ifelse(is.na(PA),Region,PA))
    (returns list of numbers)
    
    and coalesce(Carcass.cleaned$PA, Carcass.cleaned$Region) 
    unite(Carcass.cleaned, new, PA:Region, sep='') 
 (both merge the columns names instead of replacing)
dplyr coalesce r merge
4 Answers

You can use a simple if else statement:

df$Region <- ifelse(is.na(df$PA), df$Region, df$PA)

Basically wherever PA is NA you leave Region untouched, and where PA has a value you overwrite the value in Region. Afterwords you can delete PA if you want

user_117475750

Try with mutate() from dplyr:

library(tidyr)
library(dplyr)
#Code
df <- df %>% group_by(Region) %>% 
  mutate(PA=ifelse(is.na(PA),Region,PA))

Output:

# A tibble: 10 x 2
# Groups:   Region [1]
   Region PA                 
   <chr>  <chr>              
 1 Mbeya  Ruaha National Park
 2 Mbeya  Ruaha National Park
 3 Mbeya  Ruaha National Park
 4 Mbeya  Ruaha National Park
 5 Mbeya  Ruaha National Park
 6 Mbeya  Ruaha National Park
 7 Mbeya  Mbeya              
 8 Mbeya  Mbeya              
 9 Mbeya  Mbeya              
10 Mbeya  Mbeya

Some data used:

#Data
df <- structure(list(Region = c("Mbeya", "Mbeya", "Mbeya", "Mbeya", 
"Mbeya", "Mbeya", "Mbeya", "Mbeya", "Mbeya", "Mbeya"), PA = c("Ruaha National Park", 
"Ruaha National Park", "Ruaha National Park", "Ruaha National Park", 
"Ruaha National Park", "Ruaha National Park", NA, NA, NA, NA)), row.names = c(NA, 
-10L), class = "data.frame")
user_20808480

We could use coalesce from dplyr

library(dplyr)
df %>%
   mutate(PA = coalesce(PA, Region))
#   Region                  PA
#1   Mbeya Ruaha National Park
#2   Mbeya Ruaha National Park
#3   Mbeya Ruaha National Park
#4   Mbeya Ruaha National Park
#5   Mbeya Ruaha National Park
#6   Mbeya Ruaha National Park
#7   Mbeya               Mbeya
#8   Mbeya               Mbeya
#9   Mbeya               Mbeya
#10  Mbeya               Mbeya

Or using fcoalesce in data.table

library(data.table)
setDT(df)[, PA := fcoalesce(PA, Region)]

data

df <- structure(list(Region = c("Mbeya", "Mbeya", "Mbeya", "Mbeya", 
"Mbeya", "Mbeya", "Mbeya", "Mbeya", "Mbeya", "Mbeya"), PA = c("Ruaha National Park", 
"Ruaha National Park", "Ruaha National Park", "Ruaha National Park", 
"Ruaha National Park", "Ruaha National Park", NA, NA, NA, NA)), row.names = c(NA, 
-10L), class = "data.frame")
user_37322710

You can replace for NA values in PA with corresponding Region value.

df$PA[is.na(df$PA)] <- df$Region[is.na(df$PA)]
df
#   Region                  PA
#1   Mbeya Ruaha National Park
#2   Mbeya Ruaha National Park
#3   Mbeya Ruaha National Park
#4   Mbeya Ruaha National Park
#5   Mbeya Ruaha National Park
#6   Mbeya Ruaha National Park
#7   Mbeya               Mbeya
#8   Mbeya               Mbeya
#9   Mbeya               Mbeya
#10  Mbeya               Mbeya
user_39629140
Related