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

CC BY-SA 4.0
2023-06-19T07:04:40.510Z
2023-06-20T15:07:37.557Z
2023-06-20T15:07:37.557Z
haci
<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>

CC BY-SA 4.0
2023-06-21T03:05:31.180Z
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.
gringer
CC BY-SA 4.0
2023-06-19T23:20:38.723Z
2023-06-19T23:20:38.723Z
gringer
<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>

CC BY-SA 4.0
2023-06-21T03:39:15.283Z
2023-06-21T03:44:46.767Z
2023-06-21T03:44:46.767Z
Deb
<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>

CC BY-SA 4.0
2023-06-19T23:15:30.883Z
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.
gringer
<r><python><bash><bed><tsv>
How to join multiple large .tsv (> 20 GB, bed file) files by two identifier?
# 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
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.
#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.
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), 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.


---

