1
$\begingroup$

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

Improve this question
$\endgroup$
1
  • 1
    $\begingroup$ 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. $\endgroup$
    – gringer ♦
    Jun 19 at 23:15

3 Answers 3

Reset to default
4
$\begingroup$

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.

Improve this answer
$\endgroup$
2
$\begingroup$

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.

Improve this answer
$\endgroup$
1
  • $\begingroup$ 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. $\endgroup$
    – gringer ♦
    Jun 21 at 3:05
0
$\begingroup$

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.

Improve this answer
$\endgroup$

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.