transfer genome text file into excel file and select each cell to blast in NCBI

[khunohm]

I would like to know how to select each item of data from text file transfer to each column in excel file.

                     source          1..159662
                     /organism="Candidatus Carsonella ruddii PV"
                     /mol_type="genomic DNA"
                     /strain="PV"
                     /specific_host="Pachypsylla venusta"
                     /db_xref="taxon:387662"
     gene            1..1317
                     /locus_tag="CRP_001"
                     /db_xref="GeneID:4414829"
     CDS             1..1317
                     /locus_tag="CRP_001"
                     /codon_start=1
                     /transl_table=11
                     /product="tRNA modification GTPase"
                     /protein_id="YP_802398.1"
                     /db_xref="GI:116334903"
                     /db_xref="GeneID:4414829"
 

After that, how can I select each gene (in each cell) from excel file to blast (align) in NCBI database.

[bartonc]

I'm not sure if this is a python question. Are you using Visual Basic or Python?

I would like to know how to select each item of data from text file transfer to each column in excel file.

                     source          1..159662
                     /organism="Candidatus Carsonella ruddii PV"
                     /mol_type="genomic DNA"
                     /strain="PV"
                     /specific_host="Pachypsylla venusta"
                     /db_xref="taxon:387662"
     gene            1..1317
                     /locus_tag="CRP_001"
                     /db_xref="GeneID:4414829"
     CDS             1..1317
                     /locus_tag="CRP_001"
                     /codon_start=1
                     /transl_table=11
                     /product="tRNA modification GTPase"
                     /protein_id="YP_802398.1"
                     /db_xref="GI:116334903"
                     /db_xref="GeneID:4414829"
 

After that, how can I select each gene (in each cell) from excel file to blast (align) in NCBI database.

[khunohm]

I use python however I'm just the beginner of programing. Ok, I think my first problem is how can I select only each word that say "gene locus = xxx" from the data set in text file and make a new data set like this pattern => "gene locus1 =xxx", "gene locus2 =xxx", "gene locus3 =xxx", ......

I'm not sure if this is a python question. Are you using Visual Basic or Python?

[bartonc]

I use python however I'm just the beginner of programing. Ok, I think my first problem is how can I select only each word that say "gene locus = xxx" from the data set in text file and make a new data set like this pattern => "gene locus1 =xxx", "gene locus2 =xxx", "gene locus3 =xxx", ......

Did your post copy you text file format accurately? It looks a bit odd to me.

[bvdet]

I use python however I'm just the beginner of programing. Ok, I think my first problem is how can I select only each word that say "gene locus = xxx" from the data set in text file and make a new data set like this pattern => "gene locus1 =xxx", "gene locus2 =xxx", "gene locus3 =xxx", ......

One option that should be straightforward is to read each line in your data file, extract the text you need, and write the rusults to a tab delimited file formatted into rows and columns. This file can be imported directly into Excel. If you want us to help you with this, you should post a portion of a representative file and the format you require for the output (rows and columns of the spreadsheet).

[khunohm]

Thanks for the suggestion, here is part of my raw data from NCBI database.


gene 1..1317
/locus_tag="CRP_001"
/db_xref="GeneID:4414829"
CDS 1..1317
/locus_tag="CRP_001"
/codon_start=1
/transl_table=11
/product="tRNA modification GTPase"
/protein_id="YP_802398.1"
/db_xref="GI:116334903"
/db_xref="GeneID:4414829"
/translation="KNLKCFINKIVDNKDFSKNNYSDVKILFNKFSF"

gene 1314..2816
/locus_tag="CRP_002"
/db_xref="GeneID:4414830"
CDS 1314..2816
/locus_tag="CRP_002"
/codon_start=1
/transl_table=11
/product="glucose inhibited division protein A"
/protein_id="YP_802399.1"
/db_xref="GI:116334904"
/db_xref="GeneID:4414830"
/translation="KIKLFDNFYLFKLIIIMSKYYGYIKKKYFK"

gene 2785..3477
/locus_tag="CRP_003"
/db_xref="GeneID:4414831"
CDS 2785..3477
/locus_tag="CRP_003"
/codon_start=1
/transl_table=11
/product="F0F1-type ATP synthase A subunit"
/protein_id="YP_802400.1"
/db_xref="GI:116334905"
/db_xref="GeneID:4414831"
/translation="MVILKKNILNNFLNFKIIDLNLIILL"


And I need to transform it into the pattern as seen below.

No., gene, locus_tag, protein_id, GeneID,
1, 1..1317, CRP_001, YP_802398.1, 4414829,
2, 1314..2816, CRP_002, YP_802399.1, 4414830,
3, 2785..3477, CRP_003, YP_802400.1, 4414831,
4, ................ .............. .................. ...........

If it becomes like this pattern, I will be able to convert it into excel file.

[bvdet]

Thanks for the suggestion, here is part of my raw data from NCBI database.


gene 1..1317
/locus_tag="CRP_001"
/db_xref="GeneID:4414829"
CDS 1..1317
/locus_tag="CRP_001"
/codon_start=1
/transl_table=11
/product="tRNA modification GTPase"
/protein_id="YP_802398.1"
/db_xref="GI:116334903"
/db_xref="GeneID:4414829"
/translation="KNLKCFINKIVDNKDFSKNNYSDVKILFNKFSF"

gene 1314..2816
/locus_tag="CRP_002"
/db_xref="GeneID:4414830"
CDS 1314..2816
/locus_tag="CRP_002"
/codon_start=1
/transl_table=11
/product="glucose inhibited division protein A"
/protein_id="YP_802399.1"
/db_xref="GI:116334904"
/db_xref="GeneID:4414830"
/translation="KIKLFDNFYLFKLIIIMSKYYGYIKKKYFK"

gene 2785..3477
/locus_tag="CRP_003"
/db_xref="GeneID:4414831"
CDS 2785..3477
/locus_tag="CRP_003"
/codon_start=1
/transl_table=11
/product="F0F1-type ATP synthase A subunit"
/protein_id="YP_802400.1"
/db_xref="GI:116334905"
/db_xref="GeneID:4414831"
/translation="MVILKKNILNNFLNFKIIDLNLIILL"


And I need to transform it into the pattern as seen below.

No., gene, locus_tag, protein_id, GeneID,
1, 1..1317, CRP_001, YP_802398.1, 4414829,
2, 1314..2816, CRP_002, YP_802399.1, 4414830,
3, 2785..3477, CRP_003, YP_802400.1, 4414831,
4, ................ .............. .................. ...........

If it becomes like this pattern, I will be able to convert it into excel file.

This is not elegant, but see if it works for you:

import os
 
def geneData(fn, fOut):
    f = open(fn, 'r')
    line_no = 1
    lineLst = []
    itemLst = []
    labelLst = ['No.', 'gene', 'locus_tag', 'protein_id', 'GeneID']
    protien_id = False
    for line in f:
        if 'gene' in line:
            itemLst += [str(line_no), line.split()[1]]
        elif 'locus_tag' in line:
            itemLst.append(line.split('=')[1].strip('"\n'))
        elif 'protein_id' in  line:
            itemLst.append(line.split('=')[1].strip('"\n'))
            protien_id = True
        elif 'GeneID' in line and protien_id == True:
            itemLst.append(line.split(':')[1].strip('"\n'))
            lineLst.append(itemLst)
            itemLst = []
            line_no += 1
            protien_id = False
    f.close()
    f = open(fOut, 'w')
    f.write(','.join(labelLst)+'\n')
    for line in lineLst:
        f.write(','.join(line)+'\n')
    f.close()           
    return labelLst+lineLst
 
 
if __name__ == '__main__':
    geneData('your_in_file', 'your_out_file')

[khunohm]

Thank you very much. I will try and learn it from your script.

This is not elegant, but see if it works for you:

import os
 
def geneData(fn, fOut):
    f = open(fn, 'r')
    line_no = 1
    lineLst = []
    itemLst = []
    labelLst = ['No.', 'gene', 'locus_tag', 'protein_id', 'GeneID']
    protien_id = False
    for line in f:
        if 'gene' in line:
            itemLst += [str(line_no), line.split()[1]]
        elif 'locus_tag' in line:
            itemLst.append(line.split('=')[1].strip('"\n'))
        elif 'protein_id' in  line:
            itemLst.append(line.split('=')[1].strip('"\n'))
            protien_id = True
        elif 'GeneID' in line and protien_id == True:
            itemLst.append(line.split(':')[1].strip('"\n'))
            lineLst.append(itemLst)
            itemLst = []
            line_no += 1
            protien_id = False
    f.close()
    f = open(fOut, 'w')
    f.write(','.join(labelLst)+'\n')
    for line in lineLst:
        f.write(','.join(line)+'\n')
    f.close()           
    return labelLst+lineLst
 
 
if __name__ == '__main__':
    geneData('your_in_file', 'your_out_file')