Answers.Body: <p>You could try something like this :</p>
  
  <p>First you should work with the same class of object : <code>data.table</code> for fast merging operation</p>
  
  <pre><code># Turning a to a data.table
  setDT(a)
  # b is already a data.table 
  </code></pre>
  
  <p>Then you might have to set the keys of both table : </p>
  
  <pre><code># I added chr in case you need to match the same position on a different chromosome.
  setkey(a, chr, position)
  setkey(b, chr, position)
  </code></pre>
  
  <p>Finally perform the joining operation :</p>
  
  <pre><code>a[b, nomatch=0]
  </code></pre>
  
  <p>Because you didn't provide the expected output, it might need some tuning !</p>
  
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2019-12-18T16:42:05.250Z
Answers.Text: Thank you I added the output of your command in my main post. I obtained error please can you have a look?
Answers.UserDisplayName: Zizogolu
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2019-12-18T18:00:07.087Z
Answers.Text: This error means the type of your chromosome columns don't match. You can check the type of all columns with `str(a)`. I suggest you keep the the integer format of the `b` table. This should update `a` column from factor to integer : `a[, chr := as.integer(chr)]`. It may work then !
Answers.UserDisplayName: Paul Endymion
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2019-12-18T16:11:37.850Z
Answers.LastActivityDate: 2019-12-18T18:24:08.743Z
Answers.LastEditDate: 2019-12-18T18:24:08.743Z
Answers.UserDisplayName: Paul Endymion
Body: <p>I have two files like</p>
  
  <pre><code>&gt; head(a)
    chr position
  1   1   136962
  2   1   562020
  3   1   672948
  &gt; class(a)
  [1] "data.frame"
  &gt;
  </code></pre>
  
  <p>And</p>
  
  <pre><code>&gt; dim(b)
  [1] 1855235      10
  &gt; head(b[,1:3])
              SNP chr position
  1:  rs62635286          1    13116
  2:  rs75454623          1    14930
  3:    rs806731          1    30923
  4: rs200943160          1    49298
  5: rs116400033          1    51479
  6: rs141149254          1    54490
  &gt; class(b)
  [1] "data.table" "data.frame"
  &gt;
  </code></pre>
  
  <p>I want to find corresponding SNP in file b matched to position in file a so I did like this that gives memory allocation error. </p>
  
  <pre><code>&gt; unique(merge(a,b,x.by=a<span class="math-container">$position,y.by=b$</span>position)) 
  Error: cannot allocate vector of size 137.2 Gb
  </code></pre>
  
  <p>I also tried rstudio cloud but uploading the bigger file never completes</p>
  
  <p>Can you help me with task please?</p>
  
  <p>The expected output would be something like</p>
  
  <pre><code>rs62635286  1   13116
  rs376723915 1   91515
  rs147061536 1   92858
  rs62642131  1   135982
  rs371474651 1   158006
  rs201293782 1   665401
  rs138476838 1   668374
  rs1401137   1   2063094
  rs3128293   1   2065339
  </code></pre>
  
  <blockquote>
    <p>EDITED</p>
  </blockquote>
  
  <pre><code>&gt; setDT(a)
  &gt; setkey(a, chr, position)
  &gt; setkey(b, chr, position)
  Error in setkeyv(x, cols, verbose = verbose, physical = physical) : 
    some columns are not in the data.table: chr,position
  &gt; setkey(b, Chromosome, Position)
  &gt; a[b, nomatch=0]
  Error in bmerge(i, x, leftcols, rightcols, roll, rollends, nomatch, mult,  : 
    Incompatible join types: x.chr (factor) and i.Chromosome (integer). Factor columns must join to factor or character columns.
  &gt;
  </code></pre>
  
Tags: <r>
Title: Merging files gives memory allocation error
ThreadText: # Merging files gives memory allocation error
  
  (Asked by: username_0 on 2019-12-18)
  
  I have two files like
  
  ```
  > head(a)
    chr position
  1   1   136962
  2   1   562020
  3   1   672948
  > class(a)
  [1] "data.frame"
  >
  
  ```
  
  And
  
  ```
  > dim(b)
  [1] 1855235      10
  > head(b[,1:3])
              SNP chr position
  1:  rs62635286          1    13116
  2:  rs75454623          1    14930
  3:    rs806731          1    30923
  4: rs200943160          1    49298
  5: rs116400033          1    51479
  6: rs141149254          1    54490
  > class(b)
  [1] "data.table" "data.frame"
  >
  
  ```
  
  I want to find corresponding SNP in file b matched to position in file a so I did like this that gives memory allocation error.
  
  ```
  > unique(merge(a,b,x.by=a$position,y.by=b$position)) 
  Error: cannot allocate vector of size 137.2 Gb
  
  ```
  
  I also tried rstudio cloud but uploading the bigger file never completes
  
  Can you help me with task please?
  
  The expected output would be something like
  
  ```
  rs62635286  1   13116
  rs376723915 1   91515
  rs147061536 1   92858
  rs62642131  1   135982
  rs371474651 1   158006
  rs201293782 1   665401
  rs138476838 1   668374
  rs1401137   1   2063094
  rs3128293   1   2065339
  
  ```
  
  > EDITED
  
  ```
  > setDT(a)
  > setkey(a, chr, position)
  > setkey(b, chr, position)
  Error in setkeyv(x, cols, verbose = verbose, physical = physical) : 
    some columns are not in the data.table: chr,position
  > setkey(b, Chromosome, Position)
  > a[b, nomatch=0]
  Error in bmerge(i, x, leftcols, rightcols, roll, rollends, nomatch, mult,  : 
    Incompatible join types: x.chr (factor) and i.Chromosome (integer). Factor columns must join to factor or character columns.
  >
  
  ```
  
  **Tags:** r
  
  
  ---
  
  ## 1 answers
  
  ### Answer by username_1 on 2019-12-18. Score: 2 | Accepted answer
  
  You could try something like this :
  
  First you should work with the same class of object : `data.table` for fast merging operation
  
  ```
  # Turning a to a data.table
  setDT(a)
  # b is already a data.table 
  
  ```
  
  Then you might have to set the keys of both table :
  
  ```
  # I added chr in case you need to match the same position on a different chromosome.
  setkey(a, chr, position)
  setkey(b, chr, position)
  
  ```
  
  Finally perform the joining operation :
  
  ```
  a[b, nomatch=0]
  
  ```
  
  Because you didn't provide the expected output, it might need some tuning !
  
  > **Comments:**
  > **username_0**: Thank you I added the output of your command in my main post. I obtained error please can you have a look?  
  > **username_1**: This error means the type of your chromosome columns don't match. You can check the type of all columns with `str(a)`. I suggest you keep the the integer format of the `b` table. This should update `a` column from factor to integer : `a[, chr := as.integer(chr)]`. It may work then !  
  
  ---
  
  