CODEWITHSUNDEEP
rdataframejoindplyr
N08
N08

Reputation: 1315

Appending and overwriting when joining dataframes

I have the following three dataframes:

prim <- data.frame("t"=2007:2012,
                   "a"=1:6,
                   "b"=7:12)

secnd <- data.frame("t"=2012:2013,
                    "a"=c(5, 7))

third <- data.frame("t"=2012:2013,
                    "b"=c(11, 13))

I want to join secnd and third to prim in two steps. In the first step I join prim and secnd, where any existing elements in prim are overwritten by those in secnd, so we end up with:

     t  a  b
1 2007  1  7
2 2008  2  8
3 2009  3  9
4 2010  4 10
5 2011  5 11
6 2012  5 12
7 2013  7 NA

After this I want to join with third, where again existing elements are overwritten by those in third:

     t  a  b
1 2007  1  7
2 2008  2  8
3 2009  3  9
4 2010  4 10
5 2011  5 11
6 2012  5 11
7 2013  7 13

Is there a way to achieve this using dplyr or base R?

Upvotes: 1

Views: 87

Answers (3)

Parfait
Parfait

Reputation: 107652

Consider base R with a chain merge and ifelse calls, followed by final column cleanup:

final_df <- Reduce(function(x, y) merge(x, y, by="t", all=TRUE), list(prim, secnd, third))

final_df <- within(final_df, {
                      a.x <- ifelse(is.na(a.y), a.x, a.y)
                      b.x <- ifelse(is.na(b.y), b.x, b.y)      
                   })

final_df <- setNames(final_df[,1:3], c("t", "a", "b"))
final_df
#      t a  b
# 1 2007 1  7
# 2 2008 2  8
# 3 2009 3  9
# 4 2010 4 10
# 5 2011 5 11
# 6 2012 5 11
# 7 2013 7 13

Upvotes: 1

davsjob
davsjob

Reputation: 1960

Not very pretty. But seems to do the job

prim %>% 
  anti_join(secnd, by = "t") %>% 
  full_join(secnd, by = c("t", "a")) %>% 
  select(-b) %>% 
  left_join(prim %>% 
      anti_join(third, by = "t") %>% 
      full_join(third, by = c("t", "b")) %>% 
      select(-a))

gives

     t a  b
1 2007 1  7
2 2008 2  8
3 2009 3  9
4 2010 4 10
5 2011 5 11
6 2012 5 11
7 2013 7 13

Upvotes: 0

Wietze314
Wietze314

Reputation: 6020

By using dplyr you can do:

require(dplyr)

prim %>% full_join(secnd, by = 't') %>%
  full_join(third, by = 't') %>%
  mutate(a = coalesce(as.integer(a.y),a.x),
         b = coalesce(as.integer(b.y),b.x)) %>%
  select(t,a,b)

I added the as.integer function since you have different data types in your dataframes.

Upvotes: 4

Related Questions