[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Rotate and categorize Data

Posted on 2011-10-13
3
Medium Priority
?
231 Views
Last Modified: 2012-05-12
Hi, I have a data file.
ASSAY_ID	GENOTYPE_ID	Sample
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

Thanks
0
Comment
Question by:zhshqzyc
  • 2
3 Comments
 
LVL 17

Accepted Solution

by:
Carlos Villegas earned 2000 total points
ID: 36962332
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.
    sr.ReadLine();

    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();
    }
}
myData.AcceptChanges();

// 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.
0
 

Author Closing Comment

by:zhshqzyc
ID: 36968673
Thanks.
0
 
LVL 17

Expert Comment

by:Carlos Villegas
ID: 36968691
Glad to help
0

Featured Post

Prep for the ITIL® Foundation Certification Exam

December’s Course of the Month is now available! Enroll to learn ITIL® Foundation best practices for delivering IT services effectively and efficiently.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

We all know that functional code is the leg that any good program stands on when it comes right down to it, however, if your program lacks a good user interface your product may not have the appeal needed to keep your customers happy. This issue can…
Introduction Hi all and welcome to my first article on Experts Exchange. A while ago, someone asked me if i could do some tutorials on object oriented programming. I decided to do them on C#. Now you may ask me, why's that? Well, one of the re…
Despite its rising prevalence in the business world, "the cloud" is still misunderstood. Some companies still believe common misconceptions about lack of security in cloud solutions and many misuses of cloud storage options still occur every day. …
Whether it be Exchange Server Crash Issues, Dirty Shutdown Errors or Failed to mount error, Stellar Phoenix Mailbox Exchange Recovery has always got your back. With the help of its easy to understand user interface and 3 simple steps recovery proced…
Suggested Courses

873 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