How to check your result when using dplyr Joins?

Question

Is there a way to find out about "extra" rows when using Joins?

I'm working with two tables with name data with the following structure:

table1 (6211 observations)

# A tibble: 6 x 2
  name       count
        
1 surname1       1
2 surname4       1
3 surname5       1



table2 (611 observations)

# A tibble: 6 x 2
  name         dummy_var
         
1 surname1        1
2 surname2        1
3 surname3        1

In table 2 all 611, names share a common trait, so they have a dummy_var column where the value is 1. Now I want to join this table to the much larger data frame with 6211 people, but only where the names match.

I would then get:


  name       count  dummy_var
         
1 surname1       1    1
2 surname4       1    NA
3 surname5       1    NA

Which is what I want (or I would prefer to get 0 instead of NA's -- but that's not part of the question here).

Now this is the code I actually use:

table_join <- table1 %>% 
  left_join(table2, "name")

It seems to kinda work, however in table_join we have 6233 obs instead of the expected 6211!

The question: Now what I'd want to know what the best way / practice is for examining what has gone wrong.

Can I get a sample of which observations are a part of those extra 22 observations in my new table somehow? It seems like It would be useful in these kinda situations.

SOLUTION:

So I think I understand now the problem. Williams tip was useful. Table2 had a bunch of duplicates -- after they were removed, the problem disappeared. This is how you remove duplicates:

table2 <- table2 %>% distinct(name)

I don't think any of the answers at moment are able to extract the "extra" line. Maybe you can't since R is just doing what I tell it to do? I guess the best way is to develop som intuition to what can go wrong and work with that. Thanks for the replies!

user_13321647 · Answer

I guess this is a scenario for anti_join. It returns all rows from x where there are not matching values in y, keeping just columns from x. (and you can extend it vice versa).

table_join <- table1 %>% 
  anti_join(table2, "name")

Output:

# A tibble: 2 x 2
  name     count
      
1 surname4     1
2 surname5     1

data:

table1 <- structure(list(name = c("surname1", "surname4", "surname5"), 
    count = c(1, 1, 1)), row.names = c(NA, -3L), class = c("tbl_df", 
"tbl", "data.frame"))

table2 <- structure(list(name = c("surname1", "surname2", "surname3"), 
    dumm_var = c(1, 1, 1)), row.names = c(NA, -3L), class = c("tbl_df", 
"tbl", "data.frame"))

user_2884859 · Answer

One possibility see if there are more than one dummy_var allocated to one common surname in table2. Check if

table2 %>% group_by(name) %>% filter(n() > 1)

results in any row. IMHO there should be at least 22 such names. See this example

table1 <- read.table(text = "name var
                 sname1 1
                 sname4 1
                 sname5 1", header = T)

table2 <- read.table(text = "name dvar
                 sname1 1
                 sname2 1
                 sname3 1
                 sname1 2", header = T)
table2

table1 %>% left_join(table2)
Joining, by = "name"
    name var dvar
1 sname1   1    1
2 sname1   1    2
3 sname4   1   NA
4 sname5   1   NA

Now see

table2 %>% group_by(name) %>% filter(n() >1)
# A tibble: 2 x 2
# Groups:   name [1]
  name    dvar
    
1 sname1     1
2 sname1     2

How to check your result when using dplyr Joins?

2 Answers