Writing processed data into excel using CSV Python
Question: I'm trying to write some data into the excel spreadsheet using CSV. I'm
writing a motif finder, reading the input from fasta and outputs to excel. But
I'm having a hard time writing the data in a correct format.

My desired result in the excel is like below..

    
    
    SeqName     M1      Hits    M2          Hits
    Seq1        MN[A-Z] 3       V[A-Z]R[ML] 2
    Seq2        MN[A-Z] 0       V[A-Z]R[ML] 5
    Seq3        MN[A-Z] 1       V[A-Z]R[ML] 0
    

I have generated correct results but I just don't know how to put them in a
correct format like above.

This is the code that I have so far.

    
    
    import re
    from Bio import SeqIO
    import csv
    import collections
    
    def SearchMotif(f1, motif, f2="motifs.xls"):
        with open(f1, 'r') as fin, open(f2,'wb') as fout:
            # This makes SeqName static and everything else mutable thus, when more than 1 motifs are searched,
            # they can be correctly placed into excel.
            writer = csv.writer(fout, delimiter = '\t')
            motif_fieldnames = ['SeqName']
            writer_dict = csv.DictWriter(fout,delimiter = '\t' ,fieldnames=motif_fieldnames)
            for i in range(0,len(motif),1):
                motif_fieldnames.append('M%d' %(i+1))
                motif_fieldnames.append('Hits')
            writer_dict.writeheader()
    
    # Reading input fasta file for processing.
        fasta_name = []
        for seq_record in SeqIO.parse(f1,'fasta'):
            sequence = repr(seq_record.seq) # re-module only takes string
            fasta_name.append(seq_record.name)
            print sequence            **********
            for j in motif:
                motif_name = j
                print motif_name       **********
                number_count = len(re.findall(j,sequence))
                print number_count     **********
                writer.writerow([motif_name])
    
    
        for i in fasta_name:
            writer.writerow([i]) # [] makes it fit into one column instead of characters taking each columns
    

The print statement that have the asterisks ********** generates this...where
number is the number of Hits and difference sequences are seq1, seq2 ...and so
on.

    
    
    Seq('QIKDLLVSSSTDLDTTLVLVNAIYFKGMWKTAFNAEDTREMPFHVTKQESKPVQ...LTS', SingleLetterAlphabet())
    PA[A-Z]
    0
    Y[A-Z]L[A-Z]
    0
    Seq('SFNVATLPAESSSTDLDTTVLLPDEPAEVSDLERIETEWTNMKILELPFAPQMK...VSS', SingleLetterAlphabet())
    PA[A-Z]
    2
    Y[A-Z]L[A-Z]
    0
    Seq('PAESIYFKIEKTYNLT', SingleLetterAlphabet())
    PA[A-Z]
    1
    Y[A-Z]L[A-Z]
    1
    


Answer: You can write your data to a Pandas DataFrame, and then use the DataFrame's
to_csv method to export it to a CSV. There is also a to_excel method. Pandas
won't let you have multiple columns with the same name, like your "Hits"
column. However, you can work around that by putting the column names you want
in the first row and using the header=False option when you export.

"import pandas as pd", then replace your code starting at "fasta_name = []"
with this:

    
    
    column_names = ['SeqName']
    for i, m in enumerate(motif):
        column_names += ['M'+str(i), 'Hits'+str(i)]
    
    df = pd.DataFrame(columns=column_names)
    
    for row, seq_record in enumerate(SeqIO.parse(f1, 'fasta')):
        sequence = repr(seq_record.name)
        df.loc[row, 'SeqName'] = sequence
        for i, j in enumerate(motif):
            df.loc[row, 'M'+str(i)] = j
            df.loc[row, 'Hits'+str(i)] = len(re.findall(j, sequence))
    
    df.to_csv(index=False)
    

