CODEWITHSUNDEEP
rmergeleft-joindata.table
nehiljain
nehiljain

Reputation: 689

Data.Table Merge - Result is larger than input Datatables

I have two data tables. df1, ref_df

Internal Structures are as follows: 

Classes ‘data.table’ and 'data.frame':  10153986 obs. of  18 variables:
 $ chr_no        : chr  "1" "1" "1" "1" ...
 $ pos           : int  238 324 340 353 355 357 380 420 435 571 ...
 $ ref           : chr  "C" "A" "G" "T" ...
 $ id            : logi  NA NA NA NA NA NA ...
 $ alt           : chr  NA NA NA NA ...
 $ af            : num  NA NA NA NA 0.807 NA NA 0.877 NA 0.868 ...
 $ cases_hom     : int  NA NA NA NA 50 NA NA 58 NA 59 ...
 $ cases_het     : int  NA NA NA NA 15 NA NA 7 NA 6 ...
 $ cases_count   : int  NA NA NA NA 115 NA NA 123 NA 124 ...
 $ controls_hom  : int  NA NA NA NA 48 NA NA 55 NA 56 ...
 $ controls_het  : int  NA NA NA NA 13 NA NA 6 NA 5 ...
 $ controls_count: int  NA NA NA NA 109 NA NA 116 NA 117 ...
 $ cc_trend      : num  NA NA NA NA 0.812 ...
 $ cc_geno       : num  NA NA NA NA NA NA NA NA NA NA ...
 $ cc_all        : num  NA NA NA NA 0.492 ...
 $ cc_dom        : num  NA NA NA NA 0.491 ...
 $ cc_rec        : num  NA NA NA NA 1 NA NA 1 NA 1 ...
 $ cmh_p_val     : num  0.9267 0.0672 0.0279 0.3939 0.2522 ...
 - attr(*, ".internal.selfref")=<externalptr> 


Classes ‘data.table’ and 'data.frame':  9915916 obs. of  5 variables:
 $ chr_no  : chr  "10" "10" "10" "10" ...
 $ pos     : int  86 126 148 208 232 396 413 413 454 1173 ...
 $ snp_name: chr  "rs459413697" "rs446265986" "rs460495236" "rs437891922" ...
 $ ref     : chr  "G" "G" "T" "G" ...
 $ alt     : chr  "C,T" "A,T" "C,G" "T" ...
 - attr(*, ".internal.selfref")=<externalptr

I perform a Left Outer Join all.x = TRUE as :

merge(x = df1, y = ref_df, all.x = T,
                     by = c("chr_no" , "pos" , "ref"), suffixes=c(".study", ".ref"))

The resulting data table is 

> dim(result_data)
[1] 10154765       20
> sum(duplicated(df1))
[1] 0
> sum(duplicated(ref_df))
[1] 0

So I am not sure what is happening. I have looked into https://github.com/Rdatatable/data.table/issues/508 I am using the latest data.table 1.9.5.

Upvotes: 0

Views: 87

Answers (1)

BICube
BICube

Reputation: 4681

Try 

>>sum(duplicated(df1[, c("chr_no", "pos", "ref")]))

This will give you the total number of duplicates based on the joining keys. 

>>table(duplicated(df1[, c("chr_no", "pos", "ref")]))

This will give you the total number of duplicates and non-duplicates records based on the joining keys.

Similarly for the other dataframe, 

>>table(duplicated(ref_df[, c("chr_no", "pos", "ref")]))

Upvotes: 2

Related Questions