<p>Read the manuals for <code>data.table::merge</code> and <code>base::merge</code>.</p>

<p>This (wrong syntax):</p>

<pre><code>unique(merge(data1,data, x.by = data1<span class="math-container">$Position, y.by = data$</span>Position))
</code></pre>

<p>Should work fine as below:</p>

<pre><code>merge(data1, data, by = "Position")
</code></pre>

<p>Regarding: </p>

<blockquote>
  <p>"losing a big part of big data frame"`</p>
</blockquote>

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

<pre><code>merge(data1, data, by = "Position", all.x = TRUE)
</code></pre>

CC BY-SA 4.0
2020-01-08T10:33:08.700Z
2020-01-08T10:33:08.700Z
zx8754
<p>If you have issue with memory and dealing with large object, maybe <code>data.table</code> is the way to go (<a href="https://github.com/Rdatatable/data.table/wiki" rel="nofollow noreferrer">https://github.com/Rdatatable/data.table/wiki</a>): </p>

<p>Let's take two dummy data.frame:</p>

<pre class="lang-r prettyprint-override"><code>dfA &lt;- data.frame(Chrom = rep("chr1",150),
                  Pos = 1:150,
                  ref = rep("A",150))
dfB &lt;- data.frame(Chrom = rep("chr1",10),
                  Pos = 11:20,
                  Vaf = rep("VAF", 10))
</code></pre>

<p>The big data.frame <code>dfA</code>:</p>

<pre class="lang-r prettyprint-override"><code>&gt; 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
</code></pre>

<p>and the small dataset:</p>

<pre class="lang-r prettyprint-override"><code>&gt; 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
</code></pre>

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

<pre class="lang-r prettyprint-override"><code>library(data.table)
setDT(dfA)
setDT(dfB)
dfA[dfB, on = c("Chrom","Pos"), vaf := i.Vaf]
</code></pre>

<p>And now, your data.frame <code>dfA</code> looks like:</p>

<pre class="lang-r prettyprint-override"><code>&gt; dfA[5:25]
    Chrom Pos ref  vaf
 1:  chr1   5   A &lt;NA&gt;
 2:  chr1   6   A &lt;NA&gt;
 3:  chr1   7   A &lt;NA&gt;
 4:  chr1   8   A &lt;NA&gt;
 5:  chr1   9   A &lt;NA&gt;
 6:  chr1  10   A &lt;NA&gt;
 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 &lt;NA&gt;
18:  chr1  22   A &lt;NA&gt;
19:  chr1  23   A &lt;NA&gt;
20:  chr1  24   A &lt;NA&gt;
21:  chr1  25   A &lt;NA&gt;
    Chrom Pos ref  vaf
</code></pre>

<p>As you see, we incorporate the column <code>Vaf</code> from the small dataframe into the big dataframe.</p>

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

<pre class="lang-r prettyprint-override"><code>colnames(data) &lt;- gsub("Chrom", "Chromosome", colnames(data))
</code></pre>

<p>then,</p>

<pre class="lang-r prettyprint-override"><code>setDT(data1)
setDT(data)
data1[data, on = c("Chromosome","Position"), i_TumorVAF_WU := i.VAF]
</code></pre>

<p>Does it look what you are trying to obtain ?</p>

CC BY-SA 4.0
2020-01-01T02:05:33.300Z
2020-01-01T02:11:24.293Z
2020-01-01T02:11:24.293Z
dc37
<p>I have a big data frame like this</p>

<pre><code>&gt; dim(data1)
[1] 474303    128
&gt; 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
&gt;
</code></pre>

<p>I have another dataframe like this</p>

<pre><code>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
</code></pre>

<p>I want to create another column named <strong><em>i_TumorVAF_WU</em></strong> 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</p>

<p>Can you help please?</p>

<pre><code>    &gt; unique(merge(data1,data,x.by=data1<span class="math-container">$Position,y.by=data$</span>Position))
</code></pre>

<p>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</p>

CC BY-SA 4.0
2019-12-31T21:49:10.410Z
Use dplyr and close some stuff to free up RAM
Devon Ryan
<r><wgs><maf><snv>
Merging matched parts of two dataframes
# Merging matched parts of two dataframes

(Asked by: username_0 on 2019-12-31)

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

**Tags:** r, wgs, maf, snv
> **Comments:**
> **username_1**: Use dplyr and close some stuff to free up RAM  

---

## 2 answers

### Answer by username_3 on 2020-01-01. Score: 3 | Accepted answer

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 
 2:  chr1   6   A 
 3:  chr1   7   A 
 4:  chr1   8   A 
 5:  chr1   9   A 
 6:  chr1  10   A 
 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 
18:  chr1  22   A 
19:  chr1  23   A 
20:  chr1  24   A 
21:  chr1  25   A 
    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 by username_2 on 2020-01-08. Score: 1

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)


---

