Answers.Body: <p>If you are open to a GNU/Linux solution, the following should be a good start:</p>
  <pre><code>awk '{printf(&quot;%s-%s %s %s %s\n&quot;, <span class="math-container">$1, $</span>2, <span class="math-container">$1, $</span>2, <span class="math-container">$3, $</span>4);}' ref_f.bed | LC_ALL=C sort -k 1 &gt; sorted_modified_ref_f.txt
  awk '{printf(&quot;%s-%s %s %s %s %s %s %s\n&quot;, <span class="math-container">$1, $</span>2, <span class="math-container">$1, $</span>2, <span class="math-container">$3, $</span>4, <span class="math-container">$5, $</span>6);}' file_1.bed | LC_ALL=C sort -k 1 &gt; sorted_modified_file_1.txt
  join sorted_modified_ref_f.txt sorted_modified_file_1.txt
  </code></pre>
  <p>The <code>awk</code> commands are creating a &quot;first column&quot; that is a combination of columns 1 and 2 and this is required because <code>join</code> &quot;joins&quot; on a single column.</p>
  
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2023-06-19T07:04:40.510Z
Answers.LastActivityDate: 2023-06-20T15:07:37.557Z
Answers.LastEditDate: 2023-06-20T15:07:37.557Z
Answers.UserDisplayName: haci
Answers.Body: <p>For joining / merging / intersecting bed files, you're better off using <a href="https://bedtools.readthedocs.io/en/latest/index.html" rel="nofollow noreferrer">bedtools</a>.</p>
  <p>It's unclear what you're trying to do with these steps, but I'm guessing it's something like the following:</p>
  <blockquote>
  <p>The bedtools sub-commands include:</p>
  <pre><code>[ Genome arithmetic ]
      intersect     Find overlapping intervals in various ways.
      window        Find overlapping intervals within a window around an &gt; interval.
      ...
      merge         Combine overlapping/nearby intervals into a single interval.
  </code></pre>
  </blockquote>
  <p>If you're interested in combining multiple lines of different files in R, have a look at the <code>map_dfr</code> function (reference <a href="https://purrr.tidyverse.org/reference/map_dfr.html" rel="nofollow noreferrer">here</a>), which does a row-wise concatenation of the results of a function mapped to a list.</p>
  
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2023-06-21T03:05:31.180Z
Answers.Text: If you've found a solution to your problem that works for you (or *almost* works), feel free to add your own answer to your question. It's easier to work on and improve answers when it's done that way.
Answers.UserDisplayName: gringer
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2023-06-19T23:20:38.723Z
Answers.LastActivityDate: 2023-06-19T23:20:38.723Z
Answers.UserDisplayName: gringer
Answers.Body: <p>Thanks for all the responses, however, I got a relatable outcome from (<a href="https://unix.stackexchange.com/questions/113898/how-to-merge-two-files-based-on-the-matching-of-two-columns">https://unix.stackexchange.com/questions/113898/how-to-merge-two-files-based-on-the-matching-of-two-columns</a>) by using</p>
  <p><code>$ awk 'NR==FNR {h[$2] = $6; next} {print $1,$2,$3,h[$2]}' file_1 ref_f &gt; out_1</code></p>
  <p>For <code>file_1</code> (<code>NR==FNR</code> is only true for the first file argument). Save column 6 in hash-array using column 2 as key: <code>h[$2] = $6</code>. Then for <code>ref_f</code> and output all three columns <code>$1,$2,$3</code>, appending the corresponding saved column from hash-array <code>h[$2]</code>.</p>
  <p>However, it does not print <code>col4</code> and <code>col5</code> from <code>file_1</code> and must be repeated for each subsequent file. I hope that the answer could be improved further.</p>
  
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2023-06-21T03:39:15.283Z
Answers.LastActivityDate: 2023-06-21T03:44:46.767Z
Answers.LastEditDate: 2023-06-21T03:44:46.767Z
Answers.UserDisplayName: Deb
Body: <p><em>This question has also been asked on <a href="https://www.biostars.org/p/9566771/" rel="nofollow noreferrer">Biostars</a></em></p>
  <p>I wonder how can I join multiple large .tsv files (over 20 GB each) by using two identifiers (columns) which I used to do using the <code>full_join</code> function in <code>dplyr</code> package for comparatively smaller files. All files are in <code>bed format</code>, where ref_f.bed will be mapped by the other files by col1 and col2.</p>
  <pre class="lang-r prettyprint-override"><code>#merging the files
  dflist &lt;- list(ref_f, file_1, file_2, file_3,....file_n)
  
  Test_out &lt;- dflist %&gt;% reduce(full_join, by=c(&quot;chr&quot;, &quot;start&quot;))
  </code></pre>
  <pre><code>head ref_f.bed
  chr1 1 100 .
  chr1 101 200 .
  chr1 201 300 .
  chr1 301 400 .
  chr1 401 500 .
  chr1 501 600 .
  chr1 601 700 .
  chr1 701 800 .
  chr1 801 900 .
  
  head file_1.bed
  chr1 10101 10200 10158 10177 Scerevisiae-UniPROBE-Tbf1.UP00299
  chr1 10101 10200 10158 10177 Scerevisiae-UniPROBE-Tbf1.UP00299
  chr1 10101 10200 10164 10183 Scerevisiae-UniPROBE-Tbf1.UP00299
  chr1 10101 10200 10164 10183 Scerevisiae-UniPROBE-Tbf1.UP00299
  chr1 10101 10200 10175 10194 Scerevisiae-UniPROBE-Tbf1.UP00299
  chr1 10101 10200 10175 10194 Scerevisiae-UniPROBE-Tbf1.UP00299
  chr1 10101 10200 10175 10194 Scerevisiae-UniPROBE-Tbf1.UP00299
  chr1 10101 10200 10181 10200 Scerevisiae-UniPROBE-Tbf1.UP00299
  chr1 10101 10200 10181 10200 Scerevisiae-UniPROBE-Tbf1.UP00299
     
  head file_2.bed
  chr1 10101 10200 10158 10177 Scerevisiae-UniPROBE-Tbf1.UP00299
  chr1 10101 10200 10158 10177 Scerevisiae-UniPROBE-Tbf1.UP00299
  chr1 10101 10200 10164 10183 Scerevisiae-UniPROBE-Tbf1.UP00299
  chr1 10101 10200 10164 10183 Scerevisiae-UniPROBE-Tbf1.UP00299
  chr1 10101 10200 10175 10194 Scerevisiae-UniPROBE-Tbf1.UP00299
  chr1 10101 10200 10175 10194 Scerevisiae-UniPROBE-Tbf1.UP00299
  chr1 10101 10200 10175 10194 Scerevisiae-UniPROBE-Tbf1.UP00299
  chr1 10101 10200 10181 10200 Scerevisiae-UniPROBE-Tbf1.UP00299
  chr1 10101 10200 10181 10200 Scerevisiae-UniPROBE-Tbf1.UP00299
  
  head file_3.bed
  chr1 29101 29200 29111 29124 Hsapiens-HOCOMOCOv10-AP2D_HUMAN.H10MO.D
  chr1 29101 29200 29132 29152 Scerevisiae-UniPROBE-Cha4.UP00300
  chr1 29101 29200 29116 29133 Scerevisiae-UniPROBE-Ecm22.UP00305
  chr1 29101 29200 29126 29143 Scerevisiae-UniPROBE-Hal9.UP00315
  chr1 29101 29200 29126 29143 Scerevisiae-UniPROBE-Hal9.UP00315
  chr1 29101 29200 29106 29125 Scerevisiae-UniPROBE-Rpn4.UP00272
  chr1 29101 29200 29110 29130 Scerevisiae-UniPROBE-Rsc30.UP00279
  chr1 29101 29200 29112 29132 Scerevisiae-UniPROBE-Rsc30.UP00279
  chr1 29101 29200 29133 29152 Scerevisiae-UniPROBE-Stp2.UP00338
  </code></pre>
  <p>R needs to import all files before performing the task because the file size for more than 20 files surpasses 20 GB, making this task appear difficult for R. This is the first time I have encountered this kind of issue, and I previously used this method in R for merging smaller size files. Therefore, I'm seeking a better solution to this issue.</p>
  <p>It would be a great assistance if anyone kindly advised how to overcome this issue.</p>
  <p>Thanks</p>
  
Comments.ContentLicense: CC BY-SA 4.0
Comments.CreationDate: 2023-06-19T23:15:30.883Z
Comments.Text: Can you please give more information about what you're trying to do with these join functions? It's likely that there's a better way to solve your problem that doesn't involve the intermediate step that you're asking about.
Comments.UserDisplayName: gringer
Tags: <r><python><bash><bed><tsv>
Title: How to join multiple large .tsv (> 20 GB, bed file) files by two identifier?
ThreadText: # How to join multiple large .tsv (> 20 GB, bed file) files by two identifier?
  
  (Asked by: username_0 on 2023-06-19)
  
  *This question has also been asked on [Biostars](https://www.biostars.org/p/9566771/)*
  
  I wonder how can I join multiple large .tsv files (over 20 GB each) by using two identifiers (columns) which I used to do using the `full_join` function in `dplyr` package for comparatively smaller files. All files are in `bed format`, where ref\_f.bed will be mapped by the other files by col1 and col2.
  
  ```r
  #merging the files
  dflist <- list(ref_f, file_1, file_2, file_3,....file_n)
  
  Test_out <- dflist %>% reduce(full_join, by=c("chr", "start"))
  
  ```
  
  ```
  head ref_f.bed
  chr1 1 100 .
  chr1 101 200 .
  chr1 201 300 .
  chr1 301 400 .
  chr1 401 500 .
  chr1 501 600 .
  chr1 601 700 .
  chr1 701 800 .
  chr1 801 900 .
  
  head file_1.bed
  chr1 10101 10200 10158 10177 Scerevisiae-UniPROBE-Tbf1.UP00299
  chr1 10101 10200 10158 10177 Scerevisiae-UniPROBE-Tbf1.UP00299
  chr1 10101 10200 10164 10183 Scerevisiae-UniPROBE-Tbf1.UP00299
  chr1 10101 10200 10164 10183 Scerevisiae-UniPROBE-Tbf1.UP00299
  chr1 10101 10200 10175 10194 Scerevisiae-UniPROBE-Tbf1.UP00299
  chr1 10101 10200 10175 10194 Scerevisiae-UniPROBE-Tbf1.UP00299
  chr1 10101 10200 10175 10194 Scerevisiae-UniPROBE-Tbf1.UP00299
  chr1 10101 10200 10181 10200 Scerevisiae-UniPROBE-Tbf1.UP00299
  chr1 10101 10200 10181 10200 Scerevisiae-UniPROBE-Tbf1.UP00299
     
  head file_2.bed
  chr1 10101 10200 10158 10177 Scerevisiae-UniPROBE-Tbf1.UP00299
  chr1 10101 10200 10158 10177 Scerevisiae-UniPROBE-Tbf1.UP00299
  chr1 10101 10200 10164 10183 Scerevisiae-UniPROBE-Tbf1.UP00299
  chr1 10101 10200 10164 10183 Scerevisiae-UniPROBE-Tbf1.UP00299
  chr1 10101 10200 10175 10194 Scerevisiae-UniPROBE-Tbf1.UP00299
  chr1 10101 10200 10175 10194 Scerevisiae-UniPROBE-Tbf1.UP00299
  chr1 10101 10200 10175 10194 Scerevisiae-UniPROBE-Tbf1.UP00299
  chr1 10101 10200 10181 10200 Scerevisiae-UniPROBE-Tbf1.UP00299
  chr1 10101 10200 10181 10200 Scerevisiae-UniPROBE-Tbf1.UP00299
  
  head file_3.bed
  chr1 29101 29200 29111 29124 Hsapiens-HOCOMOCOv10-AP2D_HUMAN.H10MO.D
  chr1 29101 29200 29132 29152 Scerevisiae-UniPROBE-Cha4.UP00300
  chr1 29101 29200 29116 29133 Scerevisiae-UniPROBE-Ecm22.UP00305
  chr1 29101 29200 29126 29143 Scerevisiae-UniPROBE-Hal9.UP00315
  chr1 29101 29200 29126 29143 Scerevisiae-UniPROBE-Hal9.UP00315
  chr1 29101 29200 29106 29125 Scerevisiae-UniPROBE-Rpn4.UP00272
  chr1 29101 29200 29110 29130 Scerevisiae-UniPROBE-Rsc30.UP00279
  chr1 29101 29200 29112 29132 Scerevisiae-UniPROBE-Rsc30.UP00279
  chr1 29101 29200 29133 29152 Scerevisiae-UniPROBE-Stp2.UP00338
  
  ```
  
  R needs to import all files before performing the task because the file size for more than 20 files surpasses 20 GB, making this task appear difficult for R. This is the first time I have encountered this kind of issue, and I previously used this method in R for merging smaller size files. Therefore, I'm seeking a better solution to this issue.
  
  It would be a great assistance if anyone kindly advised how to overcome this issue.
  
  Thanks
  
  **Tags:** r, python, bash, bed, tsv
  
  > **Comments:**
  > **username_1**: Can you please give more information about what you're trying to do with these join functions? It's likely that there's a better way to solve your problem that doesn't involve the intermediate step that you're asking about.  
  
  ---
  
  ## 3 answers
  
  ### Answer by username_2 on 2023-06-19. Score: 4
  
  If you are open to a GNU/Linux solution, the following should be a good start:
  
  ```
  awk '{printf("%s-%s %s %s %s\n", $1, $2, $1, $2, $3, $4);}' ref_f.bed | LC_ALL=C sort -k 1 > sorted_modified_ref_f.txt
  awk '{printf("%s-%s %s %s %s %s %s %s\n", $1, $2, $1, $2, $3, $4, $5, $6);}' file_1.bed | LC_ALL=C sort -k 1 > sorted_modified_file_1.txt
  join sorted_modified_ref_f.txt sorted_modified_file_1.txt
  
  ```
  
  The `awk` commands are creating a "first column" that is a combination of columns 1 and 2 and this is required because `join` "joins" on a single column.
  
  
  ---
  
  ### Answer by username_1 on 2023-06-19. Score: 2
  
  For joining / merging / intersecting bed files, you're better off using [bedtools](https://bedtools.readthedocs.io/en/latest/index.html).
  
  It's unclear what you're trying to do with these steps, but I'm guessing it's something like the following:
  
  > The bedtools sub-commands include:
  >
  > ```
  > [ Genome arithmetic ]
  >     intersect     Find overlapping intervals in various ways.
  >     window        Find overlapping intervals within a window around an > interval.
  >     ...
  >     merge         Combine overlapping/nearby intervals into a single interval.
  >
  > ```
  
  If you're interested in combining multiple lines of different files in R, have a look at the `map_dfr` function (reference [here](https://purrr.tidyverse.org/reference/map_dfr.html)), which does a row-wise concatenation of the results of a function mapped to a list.
  
  > **Comments:**
  > **username_1**: If you've found a solution to your problem that works for you (or *almost* works), feel free to add your own answer to your question. It's easier to work on and improve answers when it's done that way.  
  
  ---
  
  ### Answer by username_0 on 2023-06-21. Score: 0
  
  Thanks for all the responses, however, I got a relatable outcome from (<https://unix.stackexchange.com/questions/113898/how-to-merge-two-files-based-on-the-matching-of-two-columns>) by using
  
  `$ awk 'NR==FNR {h[$2] = $6; next} {print $1,$2,$3,h[$2]}' file_1 ref_f > out_1`
  
  For `file_1` (`NR==FNR` is only true for the first file argument). Save column 6 in hash-array using column 2 as key: `h[$2] = $6`. Then for `ref_f` and output all three columns `$1,$2,$3`, appending the corresponding saved column from hash-array `h[$2]`.
  
  However, it does not print `col4` and `col5` from `file_1` and must be repeated for each subsequent file. I hope that the answer could be improved further.
  
  
  ---
  
  