0

I have been trying with merge and joint options from dplyr package, but I can't get the type of merging I'm looking for.

An example, considering the data.frame:

df1 <- data.frame(Country=c(rep("AFG",3),rep("AUS",3)), Category=(rep(c("a","b","c"),2)), value=c(1:6), othr=c(10:15))

and another data.frame:

df2 <- data.frame(Country=c(rep("AFG",2)), Category=c("a","b"), value=c(7,8))

The desirable output would be something like this:

   Country Category value othr
1     AFG        a     7   10
2     AFG        b     8   11
3     AFG        c     3   12
4     AUS        a     4   13
5     AUS        b     5   14
6     AUS        c     6   15

i.e., df1$value is replaced in AFG-a and AFG-b by df2$value. I want to include this operation in a loop making many changes based on "Country" and "Category" columns (in my real data there are more than two columns to be matched)

Thanks a lot!

Pablo
  • 79
  • 9

1 Answers1

1

With dplyr, we could use coalesce:

library(dplyr)

df1 %>% 
  full_join(df2, by = c('Country', 'Category'), suffix = c('', '.1')) %>%
  mutate_if(is.numeric, as.numeric) %>%
  mutate(value = coalesce(value.1, value)) %>% 
  select(-value.1)

Which returns:

  Country Category value othr
1     AFG        a     7   10
2     AFG        b     8   11
3     AFG        c     3   12
4     AUS        a     4   13
5     AUS        b     5   14
6     AUS        c     6   15

With data.table, we could do:

library(data.table)

dt1 <- setDT(df1)
dt2 <- setDT(df2)

dt1[dt2, on = c("Country", "Category"), value := i.value]

Returns:

   Country Category value othr
1:     AFG        a     7   10
2:     AFG        b     8   11
3:     AFG        c     3   12
4:     AUS        a     4   13
5:     AUS        b     5   14
6:     AUS        c     6   15

Data:

df1 <- data.frame(
  Country = c(rep("AFG", 3), rep("AUS", 3)),
  Category = (rep(c("a", "b", "c"), 2)),
  value = c(1:6),
  othr = c(10:15),
  stringsAsFactors = FALSE
)

df2 <- data.frame(
  Country = c(rep("AFG", 2)),
  Category = c("a", "b"),
  value = c(7, 8),
  stringsAsFactors = FALSE
)
tyluRp
  • 4,678
  • 2
  • 17
  • 36
  • Thanks tyluRo for your help. It works fine, however, I got another problem when applying your solution to my data [https://stackoverflow.com/questions/49198437/replacing-values-in-a-data-table-according-to-multiple-columns-names-r] – Pablo Mar 09 '18 at 16:52