+ Reply to Thread
Results 1 to 2 of 2

How to calculate frequency transition matrix in Excel?

  1. #1
    Registered User
    Join Date
    03-27-2012
    Location
    Singapore
    MS-Off Ver
    Excel 2007
    Posts
    1

    How to calculate frequency transition matrix in Excel?

    Hello folks,
    I have to calculate a frequency transition matrix in Excel. This is a Hidden Markov Model (HMM) problem.

    I have the following inputs:

    1. Multiple DNA sequences

    ATGGGCTCCTCCGCCATCACCGTGAGCTTCCTCCTCTTTCTGGCATTTCAGCTCCCAGGGCAAACAGGAGCAAATCCCGTGTATGGCTCTGTGTCCAATGCAGACCTGATGGATTTCAAGTAAAAG ATGGTGAGAAAATGGGCCCTGCTCCTGCCCATGCTGCTCTGCGGCCTGACTGGTCCCGCACACCTCTTCCAGCCAAGCCTGGTGCTGGAGATGGCCCAGGTCCTCTTGGATAACTACTGCTTCCCAGAGAACCTGATGGGGATGCAGGGAGCCATCGAGCAGGCCATCAAAAGTCAGGAGATTCTGTCTATCTCAGACCCTCAGACTCTGGCCCATGTGTTGACAGCTGGGGTGCAGAGCTCCTTGAATGACCCTCGCCTGGTCATCTCCTATGAGCCCAGCACCCTCGAGGCCCCTCCGCGAGCTCCAGCAGTCACGAACCTCACACTAGAGGAAATCATCGCAGGGCTGCAGGA ATGCCCGACGCCACACTGCCCGCCTGCTTCCTCAGCCTGCTGGCCTTCACCTCTGCTTGCTACTTCCAGAACTGCCCAAGGGGCGGCAAGAGGGCCATGTCCGACCTGGAGCTGAGACAGTAGCAG

    (Each sequence begins on a new line and begins with ATG).

    2. State transition matrix

    State A T G C End
    0 1 -1 -1 -1 -1
    1 -1 2 -1 -1 -1
    2 -1 -1 3 -1 -1
    3 4 7 6 5 -1
    4 8 11 10 9 -1
    5 8 11 10 9 -1
    6 8 11 10 9 -1
    7 12 11 13 9 -1
    8 14 17 16 15 -1
    9 14 17 16 15 -1
    10 14 17 16 15 -1
    11 14 17 16 15 -1
    12 18 17 19 15 -1
    13 18 17 16 15 -1
    14 4 7 6 5 20
    15 4 7 6 5 20
    16 4 7 6 5 20
    17 4 7 6 5 20
    18 -1 -1 -1 -1 20
    19 -1 -1 -1 -1 20
    20 -1 -1 -1 -1 -1

    The output that I need to get is a frequency transition matrix.

    For example:
    ATGGGCTCCT

    A->T is state 1->2 ==> count:1

    T->G is state 2->3 ==> count:1

    G->G is state 3->6 ==> count:1

    G->G is state 6->10==> count:1

    G->C is state 10->15==>count:1

    I have to compute this for each sequence.

    State 0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20
    0
    1 1
    2 1
    3 1
    4
    5
    6 1
    7
    8
    9
    10 1
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20

    Does anybody have an idea on how to proceed? I know how to do it manually but I have a large number of sequences and each sequence is extremely long!
    Please help!
    Last edited by sally123; 03-27-2012 at 05:14 AM.

  2. #2
    Forum Expert dilipandey's Avatar
    Join Date
    12-05-2011
    Location
    Dubai, UAE
    MS-Off Ver
    1997 - 2016
    Posts
    8,191

    Re: How to calculate frequency transition matrix in Excel?

    Hi Sally,

    Welcome to the forum.

    I would suggest you to explore FREQUENCY function of Excel and also It would be better if you could share a sample file. Thanks.

    Regards,
    DILIPandey

    <click on below 'star' if this helps>
    DILIPandey, Excel rMVP
    +919810929744 (India), +971528225509 (Dubai), [email protected]

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1