Joining tables in R based on multiple but incomplete ID columns by using ifelse statements

user_6041904 0
Viewed 26

I have the following problem. For a project I have (pseudonymized) student data from a number of different schools. I combined the different files into one big data frame. Something like the following table, which contains a few (unique) identifiers (e.g. student number, name_code):

table1<-tribble(~Name_code, ~Student_number,    ~School,    ~Other_variables_I_measured,
    "Name_1", 123456, "A",  0.360,
    "Name_2", 234567, "A",  0.813,
    "Name_3", 345678, "A",  0.518,
    "Name_4", 456789, "A",  0.048,
    "Name_5", 567900, "A",  0.096,
    "Name_6", 679011, "B",  0.319,
    "Name_7", 790122, "B",  0.704,
    "Name_8", 901233, "B",  0.574,
    "Name_9", 112344, "B",  0.662,
    "Name_10", 123455, "B", 0.178)

(table1)

Name_code   Student_number  School  Other_variables_I_measured
Name_1      123456             A    0.360
Name_2      234567             A    0.813
Name_3      345678             A    0.518
Name_4      456789             A    0.048
Name_5      567900             A    0.096
Name_6      679011             B    0.319
Name_7      790122             B    0.704
Name_8      901233             B    0.574
Name_9      112344             B    0.662
Name_10     123455             B    0.178

At the end of the academic year the schools provided additional data (GPA, retention, etc.), however, depending on the school, the individual student reports might only include one of the identifiers, which makes it hard to link the information to table 1 as each of the identifiers includes missings when I combine the grade data. E.g. reports of school A might only include the student number, reports of school B only the (recoded) name.

table2<-tribble(~Student_number,    ~Name_code, ~GPA,   ~School,
    123456,     NA,     8,  "A",
    234567,     NA,     9,  "A",
    345678,     NA,     7,  "A",
    456789,     NA,     8,  "A",
    567900,     NA,     7,  "A",
    NA,     "Name_6",   4,  "B",
    NA,     "Name_7",   5,  "B",
    NA,     "Name_8",   4,  "B",
    NA,     "Name_9",   5,  "B",
    NA,     "Name_10",  7,  "B")

(table2)

Student_number  Name_code   GPA School
123456             NA         8    A
234567             NA         9    A
345678             NA         7    A
456789             NA         8    A
567900             NA         7    A
  NA             Name_6       4    B
  NA             Name_7       5    B
  NA             Name_8       4    B
  NA             Name_9       5    B
  NA             Name_10      7    B

Is there a way to join table 1 and 2 based the different, complete ID values? (= joining by ID variable 1, and if missing, use ID variable 2 instead)

Some pseudo code like:
    dplyr::left_join(table1, table2, by= (ifelse(!is.na(Student_number), "Student_number", "Name_code"))))

which should produce table3

Name_code   Student_number  School  Other_variables_I_measured  GPA
Name 1      123456            A      0.360                  8
Name 2      234567            A      0.813                  9
Name 3      345678            A      0.518                  7
Name 4      456789            A      0.048                  8
Name 5      567900            A      0.096                  7
Name 6      679011            B      0.319                  4
Name 7      790122            B      0.704                  5
Name 8      901233            B      0.574                  4
Name 9      112344            B      0.662                  5
Name 10     123455            B      0.178                  7

What would be the easiest way to do this? Or are there even functions that can circumvent the ifelse part completely?

data-wrangling conditional-statements left-join if-statement r
1 Answers

The problem is that left_join seems to impose an AND condition on the keys if several are provided. For example this does not give the correct output.

colnames(table1) <-  paste0(colnames(table1),1)
colnames(table2) <-  paste0(colnames(table2),2)
left_join(table1, table2, by = c("Name_code1" = "Name_code2", "Student_number1" = "Student_number2"))
 # A tibble: 10 x 10
   Name_code1 Student_number1 School1 Other_variables_I_measured1 namematch match GPA2.x School2.x GPA2.y School2.y
   <chr>                <dbl> <chr>                         <dbl>     <int> <int>  <dbl> <chr>      <dbl> <chr>    
 1 Name_1              123456 A                             0.36         NA     1      8 A             NA NA       
 2 Name_2              234567 A                             0.813        NA     2      9 A             NA NA

Therefore, what I would do is:

table1$match <- match(table1$Name_code1, table2$Name_code2)
table1$match <- ifelse(!is.na(table1$match), table1$match, match(table1$Student_number1, table2$Student_number2))

table1[,c("GPA2", "School2")] <- table2[table1$match,c("GPA2", "School2")]
table1
# A tibble: 10 x 8
   Name_code1 Student_number1 School1 Other_variables_I_measured1 namematch match  GPA2 School2
   <chr>                <dbl> <chr>                         <dbl>     <int> <int> <dbl> <chr>  
 1 Name_1              123456 A                             0.36         NA     1     8 A      
 2 Name_2              234567 A                             0.813        NA     2     9 A

Here the column table1$match returns the corresponding row number in table2 for all rows in table1.

user_52242360
Related