Hello!
In case this question is already answered I apologize in advance.
I have 2 very big tsv files with some values of one column from one file matching the values of one column from the other file. Based on that I want to make a new tsv file.
1st file
ERZ871266.fasta_contig1 unclassified (taxid 0)
ERZ871266.fasta_contig2 Aeromicrobium choanae (taxid 1736691)
ERZ871266.fasta_contig4 Clostridioides difficile (taxid 1496)
ERZ871266.fasta_contig6 unclassified (taxid 0)
........
2nd file
/home/results/ERZ940766.fasta ERZ871266.fasta_contig1
/home/results/ERZ940766.fasta ERZ871266.fasta_contig2
/home/results/ERZ940766.fasta ERZ871266.fasta_contig3
/home/results/ERZ940766.fasta ERZ871266.fasta_contig4
/home/results/ERZ940766.fasta ERZ871266.fasta_contig5
/home/results/ERZ940766.fasta ERZ871266.fasta_contig6
........
What I want to do is the following:
ERZ871266.fasta_contig1 unclassified (taxid 0) /home/results/ERZ940766.fasta
ERZ871266.fasta_contig2 Aeromicrobium choanae (taxid 1736691) /home/results/ERZ940766.fasta
ERZ871266.fasta_contig4 Clostridioides difficile (taxid 1496) /home/results/ERZ940766.fasta
ERZ871266.fasta_contig6 unclassified (taxid 0) /home/results/ERZ940766.fasta
........
Thanking you in advance!
•
written
by
blackadder •
Either switch to R + merge/dplyr like Mamta says if you want to investigate why certain lines are not matching, or run tests based on known scenarios.
To start off, knock out the -a params and replace file1 and file2 with <(sort -k1,1 file1) and <(sort -k 2,2 file2). --nocheck-order doesn't negate the need for the content to be sorted, it just asks join to skip the check.
Note: Use the 101010 button to format as code. You're using the double quote button that quotes content - it doesn't format content in monospace + highlight keywords (which is what code formatting is).
join -t $'\t' <(sort -k1,1 file1) <(sort -k2,2 file2) -1 1 -2 2 > output
This worked! Thank you very much !
Log in to answer this question.
What have you tried? A simple search on Stack Overflow will reveal multiple ways of doing this.
Yes, you are right. I forgot to mention what i did.
I played mostly with the join command but I am getting an error saying that my files are not sorted (even though I used the sort command beforehand based on the specific columns). I also don't have duplicates in my files
Show us what you did as well as the exact error you face.
join ... <(sort ... file1) <(sort ... file2)with the appropriate params should work.Initially I tried:
and I got the following error:
Then I tried with the
--nocheck-orderWith that I got an output but the file had missing values. For example:
Maybe it has to do with the fact that not all values are matching?
you can use dplyr join function in R (e.g left_join( ))
Why do you recommend
left_joinwhen file2 seemingly has more values and if anything,inner_joinshould be preferred when criteria are unclear?Edited..depends on how OP wants to join the data and retain columns.