Answers.Body: <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>
  
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2020-01-08T10:33:08.700Z
Answers.LastActivityDate: 2020-01-08T10:33:08.700Z
Answers.UserDisplayName: zx8754
Answers.Body: <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>
  
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2020-01-01T02:05:33.300Z
Answers.LastActivityDate: 2020-01-01T02:11:24.293Z
Answers.LastEditDate: 2020-01-01T02:11:24.293Z
Answers.UserDisplayName: dc37
Body: <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>
  
Comments.ContentLicense: CC BY-SA 4.0
Comments.CreationDate: 2019-12-31T21:49:10.410Z
Comments.Text: Use dplyr and close some stuff to free up RAM
Comments.UserDisplayName: Devon Ryan
Tags: <r><wgs><maf><snv>
Title: Merging matched parts of two dataframes
ThreadText: # 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:
  
  ```r
  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`:
  
  ```r
  > 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:
  
  ```r
  > 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:
  
  ```r
  library(data.table)
  setDT(dfA)
  setDT(dfB)
  dfA[dfB, on = c("Chrom","Pos"), vaf := i.Vaf]
  
  ```
  
  And now, your data.frame `dfA` looks like:
  
  ```r
  > 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:
  
  ```r
  colnames(data) <- gsub("Chrom", "Chromosome", colnames(data))
  
  ```
  
  then,
  
  ```r
  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)
  
  ```
  
  
  ---
  
  