Merging matched parts of two dataframes

Question

I have a big data frame like this

> dim(data1)
[1] 474303    128
> head(data1[1:2,1:6])
   Tumor_Sample_Barcode Chromosome Position
1:                   A1       chr1       15772155
2:                   A1       chr1       16054813
   End_Position Reference_Allele Tumor_Seq_Allele2
1:     15772155                G                 A
2:     16054813                G                 A
>

I have another dataframe like this

Chrom   Position    Ref Alt VAF
chr1    2716062 C   A   0.323
chr1    3095112 T   G   0.365
chr1    3342771 C   G   0.123
chr1    3526376 C   T   0.365
chr1    4121415 T   G   0.225
chr1    4215706 G   A   0.125
chr1    4304014 T   G   0.286
chr1    4304016 A   C   0.302
chr1    4388809 A   C   0.135

I want to create another column named i_TumorVAF_WU in my big data frame in which VAF column comes from small data frame. Please notice that VAF values should be matched with Position in big data because small data coming from big data

Can you help please?

    > unique(merge(data1,data,x.by=data1$Position,y.by=data$Position))

Returns a weird output with losing a big part of big data frame. I just want to add corresponding VAF values to the big data by keeping all the information in big data file

Answer 1

If you have issue with memory and dealing with large object, maybe data.table is the way to go (https://github.com/Rdatatable/data.table/wiki):

Let's take two dummy data.frame:

dfA <- data.frame(Chrom = rep("chr1",150),
                  Pos = 1:150,
                  ref = rep("A",150))
dfB <- data.frame(Chrom = rep("chr1",10),
                  Pos = 11:20,
                  Vaf = rep("VAF", 10))

The big data.frame dfA:

> head(dfA)
  Chrom Pos ref
1  chr1   1   A
2  chr1   2   A
3  chr1   3   A
4  chr1   4   A
5  chr1   5   A
6  chr1   6   A

and the small dataset:

> dfB
   Chrom Pos Vaf
1   chr1  11 VAF
2   chr1  12 VAF
3   chr1  13 VAF
4   chr1  14 VAF
5   chr1  15 VAF
6   chr1  16 VAF
7   chr1  17 VAF
8   chr1  18 VAF
9   chr1  19 VAF
10  chr1  20 VAF

Now, using data.table that is more efficient and less memory consuming for the manipulation of dataframe, you can do:

library(data.table)
setDT(dfA)
setDT(dfB)
dfA[dfB, on = c("Chrom","Pos"), vaf := i.Vaf]

And now, your data.frame dfA looks like:

> dfA[5:25]
    Chrom Pos ref  vaf
 1:  chr1   5   A <NA>
 2:  chr1   6   A <NA>
 3:  chr1   7   A <NA>
 4:  chr1   8   A <NA>
 5:  chr1   9   A <NA>
 6:  chr1  10   A <NA>
 7:  chr1  11   A  VAF
 8:  chr1  12   A  VAF
 9:  chr1  13   A  VAF
10:  chr1  14   A  VAF
11:  chr1  15   A  VAF
12:  chr1  16   A  VAF
13:  chr1  17   A  VAF
14:  chr1  18   A  VAF
15:  chr1  19   A  VAF
16:  chr1  20   A  VAF
17:  chr1  21   A <NA>
18:  chr1  22   A <NA>
19:  chr1  23   A <NA>
20:  chr1  24   A <NA>
21:  chr1  25   A <NA>
    Chrom Pos ref  vaf

As you see, we incorporate the column Vaf from the small dataframe into the big dataframe.

With your example, I will first rename the colnames of your small dataframe in order to have the perfect match with the big dataframe:

colnames(data) <- gsub("Chrom", "Chromosome", colnames(data))

then,

setDT(data1)
setDT(data)
data1[data, on = c("Chromosome","Position"), i_TumorVAF_WU := i.VAF]

Does it look what you are trying to obtain ?

Answer 2

Read the manuals for data.table::merge and base::merge.

This (wrong syntax):

unique(merge(data1,data, x.by = data1$Position, y.by = data$Position))

Should work fine as below:

merge(data1, data, by = "Position")

Regarding:

"losing a big part of big data frame"`

It is only returning rows when there is a match on Position, if you need to return the full dataset, then use:

merge(data1, data, by = "Position", all.x = TRUE)