This is a test version of Biostars. For the public version, visit https://www.biostars.org.
Compare data from two different table using R

I want to compare data two different table like that using R.

Table 1  
X1     
a;b
c
d;e

Table 2
Y1
a    uuu   uu
b    vvv   vv
c    xxx   xx
d    yyy   yy
 e    zzz   zz

 Ouptput Table 3
X1     Y2
a;b    uuu|uu<\br>vvv|vv
c      xxx|xx
d;e    yyy|yy<\br>zzz|zz
r merge
• 5,173 views • link
updated by zx8754 zx8754 • written by ersan • ersan

2 answers

Here is the dplyr way. Make sure that you provide column names as given in t1 and t2

    library(tidyverse)
    t1 <- tibble(X1 = c("a;b","c","d;e"))
    t2 <- tibble(Y1 =letters[1:5] , Y2 = c("uuu","vvv","xxx","yyy","zzz") ,Y3 =c("uu","vv","xx","yy","zz"))


    t3 <- t1 %>% 
      mutate(mm = map(X1, ~(strsplit(.,split = ";"))[[1]] %>% as_tibble)) %>% 
      mutate(nn  = map(mm , function(.){
        col_binds <- left_join(.,t2, by = c("value" = "Y1")) %>% 
          unite(col = "comb",sep = "|" , Y2:Y3)
        row_binds <- col_binds %>% 
          summarise(value  = paste0(value , collapse = ";")  , times = paste0(comb , collapse = "<\br>"))
        return(row_binds)
      })) %>% 
      select(nn) %>%
      unnest() %>% 
      dplyr::rename(X1 = value , Y2 = times) 

## output
t3
# A tibble: 3 x 2
  X1    Y2                 
  <chr> <chr>              
1 a;b   "uuu|uu<\br>vvv|vv"
2 c     xxx|xx             
3 d;e   "yyy|yy<\br>zzz|zz"
• 1 views • link
written by Chirag Parsania Chirag Parsania

Very thanks Chirag, i am receiving an error : Error in mutate_impl(.data, dots) : Evaluation error: NA/NaN argument.

• 1 views • link
written by ersan • ersan

I am receiving another error: 

Evaluation error: Can't join on 'Y1' x 'value' because of incompatible types (integer / character).
• 1 views • link
written by ersan • ersan

What is the datatype of column Y1 in t2 ? In the sample data you provided, Y1 is character. Error suggest that your Y1 is integer. can you upload full data, if possible ? Without data its hard to predict.

• 1 views • link
written by Chirag Parsania Chirag Parsania

I changed datatype of Y1 integer to character. I am now giving error 

Error in mutate_impl(.data, dots) : 
  Evaluation error: `by` can't contain join column `Y1` which is missing from RHS.
• 1 views • link
written by ersan • ersan

Without any error, it is working perfectly fine in my computer 

t1 <- read_delim("~/Downloads/X1.txt" ,delim = "\t") %>%filter(X1 !=".") ## remove the lines containing dots
t2 <- read_delim("~/Downloads/Y.txt" ,delim = "\t") %>% mutate_all(as.character) ## convert all cols to char

t3 <- t1 %>% 
  mutate(mm = map(X1, ~(strsplit(.,split = ";"))[[1]] %>% as_tibble)) %>% 
  mutate(nn  = map(mm , function(.){
    col_binds <- left_join(.,t2, by = c("value" = "Y1")) %>% 
      unite(col = "comb",sep = "|" , Y2:Y3)
    row_binds <- col_binds %>% 
      summarise(value  = paste0(value , collapse = ";")  , times = paste0(comb , collapse = "<\br>"))
    return(row_binds)
  })) %>% 
  select(nn) %>%
  unnest() %>% 
  dplyr::rename(X1 = value , Y2 = times) 

> t3
# A tibble: 8,300 x 2
   X1     Y2                                                       
   <chr>  <chr>                                                    
 1 616126 Immunodeficiency 38 with basal ganglia calcification|[AR]
 2 616126 Immunodeficiency 38 with basal ganglia calcification|[AR]
 3 616126 Immunodeficiency 38 with basal ganglia calcification|[AR]
 4 616126 Immunodeficiency 38 with basal ganglia calcification|[AR]
 5 616126 Immunodeficiency 38 with basal ganglia calcification|[AR]
 6 616126 Immunodeficiency 38 with basal ganglia calcification|[AR]
 7 615120 Myasthenic syndrome, congenital, 8|[AR]                  
 8 615120 Myasthenic syndrome, congenital, 8|[AR]                  
 9 615120 Myasthenic syndrome, congenital, 8|[AR]                  
10 615120 Myasthenic syndrome, congenital, 8|[AR]                  
# ... with 8,290 more rows
• 1 views • link
written by Chirag Parsania Chirag Parsania

I notice many duplicated rows. adding t3 %>% unique() will give you unique rows

• 1 views • link
written by Chirag Parsania Chirag Parsania

I notice many duplicated rows. adding t3 %>% unique() will give you unique rows

• 0 views • link
written by Chirag Parsania Chirag Parsania

I is worked at me, too. It is wonderfull. Thank you very very much!..

• 1 views • link
written by ersan • ersan

Could I add per X1 to Y2? Output like that:

  X1    Y2                 
  <chr> <chr>              
1 a;b   aa|uuu|uu<\br>bb|vvv|vv
2 c     cc|xxx|xx             
3 d;e   dd|yyy|yy<\br>ee|zzz|zz
• 1 views • link
written by ersan • ersan

I found a way. It is working. Thanks for all.

summarise(value  = paste0(value , collapse = ";")  , times = paste0(str_split(value, ";") comb, collapse = '<br />'))
• 1 views • link
written by ersan • ersan

Hello, The combined column approach is by far the fasted way, and it makes good since. It is a bit surprising ,how much worse the Vectorize and outer functions are. Moreover, I am a bit concerned about potential issues with the paste method and doing comparisons on those results. if you facing any peoblem regading lenovo support uk , you can visit this site , https://www.pcsupportnumber.co.uk/lenovo-support-uk/ .

• 0 views • link
written by jollymethew99 • jollymethew99

Log in to answer this question.

More posts like this