0

I have a dataframe like this:

library(tidyverse)
a <- tibble(x=c("mother","father","brother","brother"),y=c("a","b","c","d"))
b <- tibble(x=c("mother","father","brother","brother"),z=c("e","f","g","h"))

I want to join these dataframes so that each "brother" occurs only once

I have tried fulljoin 

 ab <- full_join(a,b,by="x")

and obtained this:

    # A tibble: 6 x 3
  x       y     z    
  <chr>   <chr> <chr>
1 mother  a     e    
2 father  b     f    
3 brother c     g    
4 brother c     h    
5 brother d     g    
6 brother d     h

What I need is this:

ab <- tibble(x=c("mother","father","brother1","brother2"),y=c("a","b","c","d"),z=c("e","f","g","h"))

# A tibble: 4 x 3
  x        y     z    
  <chr>    <chr> <chr>
1 mother   a     e    
2 father   b     f    
3 brother1 c     g    
4 brother2 d     h
zx8754
  • 52,746
  • 12
  • 114
  • 209
Renat
  • 35
  • 5

2 Answers2

3

Using dplyr you could do something like the following, which adds an extra variable person to identify each person within each group in x, and then joins by x and person:

library(dplyr)

a %>% 
    group_by(x) %>% 
    mutate(person = 1:n()) %>%
    full_join(b %>% 
                  group_by(x) %>%
                  mutate(person = 1:n()),
              by = c("x", "person")
              ) %>% 
    select(x, person, y, z)

Which returns:

# A tibble: 4 x 4
# Groups:   x [3]
  x       person y     z    
  <chr>    <int> <chr> <chr>
1 mother       1 a     e    
2 father       1 b     f    
3 brother      1 c     g    
4 brother      2 d     h
1

Unfortunatelly, the first and second brotherare indistinguisheable form each other! How would R know that you want to join them that way, and not the reverse?

I would try to "remove duplicates" in the original data.frames by adding the "1" and "2" identifiers there.

I don't know tidyverse syntax, but if you never get more than two repetitions, you may want to try

a <- c("A", "B", "C", "C") 
a[duplicated(a)] <- paste0(a[duplicated(a)], 2)
David
  • 371
  • 2
  • 13
  • These two are like elder brother and younger brother, in that order. I need to put 1 to upper one and 2 to lower one. – Renat Jul 04 '19 at 10:32