This is a test version of Biostars. For the public version, visit https://www.biostars.org.
Compare two protein FASTA files and give a excel that show header with the same sequence

Dear All, I have two files file1.fasta file2.fasta. Both contain some identical sequences but different headers. I want to know the correspondence relationship between the headers of the two fasta files and may be output in the two columns of a EXCEL file.

1.fasta red rectangle is the header i want

2.fasta rectangle is the header i want

blast fasta
• 2,931 views • link
updated by shenwei356 shenwei356 • written by robert • robert

Assuming that both the fastas are flattened,

$ join -1 2 -2 2 <(seqkit -w 0 fx2tab a.fa ) <(seqkit -w 0 fx2tab b.fa) -o 1.1,2.1
• 0 views • link
written by cpad0112 cpad0112

2 answers

Seems you need the relationship of two versions of genome annotations.

Why save in EXCEL file? Tab-delimited plain text files with extension .xls can be recognized by MS EXCEL too.

Anyway, csvtk could export .xlsx files. For windows, please replace \ with ^ in the code below (ref: Split long commands in multiple lines through Windows batch file).

# 1) sequence id -> sequence / seq hash

seqkit seq --remove-gaps --lower-case file1.fasta \
    | seqkit fx2tab --seq-hash \
    | csvtk cut -Ht -f 1,4 \
    > file1.fasta.hash

seqkit seq --remove-gaps --lower-case file2.fasta \
    | seqkit fx2tab --seq-hash \
    | csvtk cut -Ht -f 1,4 \
    > file2.fasta.hash

# 2) join two tables according to the seq hash, and convert the result to XLSX

csvtk join -Ht -f 2 file1.fasta.hash file2.fasta.hash \
    | csvtk cut -Ht -f 1,3 \
    | csvtk add-header -Ht -n file1,file2 \
    | csvtk csv2xlsx -t -o result.xlsx
• 0 views • link
written by shenwei356 shenwei356

Thank you for such detailed reply, I am very grateful for it. Could you tell me how to run this code? I am in windows10 system and I have conda and docker installed. should I install seqkit in python or docker before running this code?

I am currently working on metabolic network reconstruction of an eukaryote microorganism. But I don't have coding experience, and my current network and KEGG metabolic information was in excel format so I want it in excel format. I could use Vlookup to map my existing network to the genome and incorporate data from multiple sources. I know it's a dummy way, but I don't know other ways to do this.

• 0 views • link
written by robert • robert

If you want the headers of a fasta:

grep ">" file.fasta > file.txt

Then copy the contents of the file.txt into excel

• 0 views • link
written by Mark Mark

Log in to answer this question.

More posts like this