Rotate and categorize Data

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
zhshqzycAsked:
Who is Participating?
 
Carlos VillegasConnect With a Mentor Full Stack .NET DeveloperCommented:
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
 
zhshqzycAuthor Commented:
Thanks.
0
 
Carlos VillegasFull Stack .NET DeveloperCommented:
Glad to help
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.