Rotate and categorize Data

Posted on 2011-10-13
Last Modified: 2012-05-12
Hi, I have a data file.
31870626_K3326X	A	2151
38965796_F195I	T	2151
44157843	C	2151
44158537	GA	2151
44159094	G	2151
44159321_R229G	C	2151
44160442_S171S	T	2151
44160589_S122S	C	2151
44160704_G84E	G	2151
44161086	A	2151
44280740_G114A	G	2151
44565468_E28K	G	2151
44591539_Y274N	T	2151
44601246_R494X	C	2151
44601955_R523W	C	2151
45949690_R87H	G	2151
76706436_P98L	C	2151
91024724_S706X	C	2151
31870626_K3326X	A	6939
38965796_F195I	T	6939
44157843	C	6939
44158537	G	6939
44159094	G	6939
44159321_R229G	C	6939
44160442_S171S	T	6939
44160589_S122S	C	6939
44160704_G84E	GA	6939
44161086	A	6939
44280740_G114A	G	6939
44565468_E28K	G	6939
44591539_Y274N	T	6939
44601246_R494X	C	6939
44601955_R523W	C	6939
45949690_R87H	G	6939
76706436_P98L	C	6939
91024724_S706X	C	6939
31870626_K3326X	A	7448
38965796_F195I	T	7448
44157843	C	7448
44158537	G	7448
44159094	G	7448
44159321_R229G	C	7448
44160442_S171S	T	7448
44160589_S122S	CT	7448
44160704_G84E	G	7448
44161086	A	7448
44280740_G114A	G	7448
44565468_E28K	G	7448
44591539_Y274N	T	7448
44601246_R494X	C	7448
44601955_R523W	C	7448
45949690_R87H	G	7448
76706436_P98L	C	7448
91024724_S706X	C	7448

Open in new window

I want to rotate and catagorize it. I used to use excel to do it. Just wonder is there a quick way by C#?
The expect result:
Sample ID	31870626_K3326X	38965796_F195I	44157843	44158537	44159094	44159321_R229G	44160442_S171S	44160589_S122S	44160704_G84E	44161086	44280740_G114A	44565468_E28K	44591539_Y274N	44601246_R494X	44601955_R523W	45949690_R87H	76706436_P98L	91024724_S706X
2151	A	T	C	GA	G	C	T	C	G	A	G	G	T	C	C	G	C	C
6939	A	T	C	G	G	C	T	C	GA	A	G	G	T	C	C	G	C	C

Open in new window

Question by:zhshqzyc
    LVL 17

    Accepted Solution

    Hello, I did this example for you, it uses a DataTable to achieve what you need:
    // Sample data
    StringBuilder sbText = new StringBuilder();
    sbText.AppendLine("ASSAY_ID GENOTYPE_ID Sample");
    sbText.AppendLine("31870626_K3326X	A	2151");
    sbText.AppendLine("38965796_F195I	T	2151");
    sbText.AppendLine("44157843	C	2151");
    sbText.AppendLine("44158537	GA	2151");
    sbText.AppendLine("44159094	G	2151");
    sbText.AppendLine("44159321_R229G	C	2151");
    sbText.AppendLine("44160442_S171S	T	2151");
    sbText.AppendLine("31870626_K3326X	A	6939");
    sbText.AppendLine("38965796_F195I	T	6939");
    sbText.AppendLine("44157843	C	6939");
    sbText.AppendLine("44158537	G	6939");
    sbText.AppendLine("44159094	G	6939");
    sbText.AppendLine("44159321_R229G	C	6939");
    sbText.AppendLine("44160442_S171S	T	6939");
    DataTable myData = new DataTable();
    myData.Columns.Add("Sample", typeof(int));
    myData.PrimaryKey = new DataColumn[] { myData.Columns["Sample"] };
    // Replace the System.IO.StringReader with a System.IO.StreamReader to read directly from your file.
    using (System.IO.StringReader sr = new System.IO.StringReader(sbText.ToString()))
        //To skip the first line assuming that your have columns names.
        string line = sr.ReadLine();
        while (line != null)
            string[] textRowData = line.Split('\t');
            string assayId = textRowData[0];
            string genotypeId = textRowData[1];
            int sampleId = int.Parse(textRowData[2]);
            if (myData.Columns[assayId] == null)
                myData.Columns.Add(assayId, typeof(string));
            DataRow myRow = myData.Rows.Find(sampleId);
            if (myRow == null)
                myRow = myData.Rows.Add(sampleId);
            myRow[assayId] = genotypeId;
            line = sr.ReadLine();
    // Now you can insert the content of myData in your Excel file

    Open in new window

    Be careful, remember that Excel has a finite number of columns allowed, I'm not validating it.

    I hope this helps.

    Author Closing Comment

    LVL 17

    Expert Comment

    by:Carlos Villegas
    Glad to help

    Featured Post

    How your wiki can always stay up-to-date

    Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
    - Increase transparency
    - Onboard new hires faster
    - Access from mobile/offline

    Join & Write a Comment

    This article is for Object-Oriented Programming (OOP) beginners. An Interface contains declarations of events, indexers, methods and/or properties. Any class which implements the Interface should provide the concrete implementation for each Inter…
    It was really hard time for me to get the understanding of Delegates in C#. I went through many websites and articles but I found them very clumsy. After going through those sites, I noted down the points in a easy way so here I am sharing that unde…
    Hi everyone! This is Experts Exchange customer support.  This quick video will show you how to change your primary email address.  If you have any questions, then please Write a Comment below!
    Illustrator's Shape Builder tool will let you combine shapes visually and interactively. This video shows the Mac version, but the tool works the same way in Windows. To follow along with this video, you can draw your own shapes or download the file…

    731 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    16 Experts available now in Live!

    Get 1:1 Help Now