DataTable

P

patrick

Well-known member
Joined
Dec 5, 2021
Messages
305
Programming Experience
1-3
Hello.
The conversion must be done as shown in the picture below.
I programmed the code below.
I don't like the code below.

How can I convert DataTable efficiently?

qe3.png



C#: 
DataRow[] rowKPS = originalDt.Select("subject = 'KPS'");
DataRow[] rowDPA = originalDt.Select("subject = 'DPA'");
DataRow[] rowESG = originalDt.Select("subject = 'ESG'");

DataTable dtConvertKPS = rowKPS.CopyToDataTable();
DataTable retKPS = GenerateTransposedTable(dtConvertKPS);

DataTable dtConvertDPA = rowDPA.CopyToDataTable();
DataTable retDPA = GenerateTransposedTable(dtConvertDPA);

retKPS.Merge(retDPA);

DataTable dtConvertESG = rowESG.CopyToDataTable();
DataTable retESG = GenerateTransposedTable(dtConvertESG);

retKPS.Merge(retESG);

private DataTable GenerateTransposedTable(DataTable inputTable)
{
     DataTable outputTable = new DataTable();
     outputTable.Columns.Add(inputTable.Columns[0].ColumnName.ToString());

     // Header row's second column onwards, 'inputTable's first column taken
     foreach (DataRow inRow in inputTable.Rows)
     {
         string newColName = inRow[0].ToString();
         outputTable.Columns.Add(newColName);
     }

     // Add rows by looping columns
     for (int rCount = 1; rCount <= inputTable.Columns.Count - 1; rCount++)
     {
         DataRow newRow = outputTable.NewRow();

         // First column is inputTable's Header row's second column
         newRow[0] = inputTable.Columns[rCount].ColumnName.ToString();
         for (int cCount = 0; cCount <= inputTable.Rows.Count - 1; cCount++)
         {
             string colValue = inputTable.Rows[cCount][rCount].ToString();
             newRow[cCount + 1] = colValue;
         }
         outputTable.Rows.Add(newRow);
     }

     return outputTable;
}
 
Last edited:
Sort by date Sort by votes
patrick said:
Hello.
The conversion must be done as shown in the picture below.
I programmed the code below.
I don't like the code below.

How can I convert DataTable efficiently?

View attachment 2971


C#: 
DataRow[] rowKPS = originalDt.Select("subject = 'KPS'");
DataRow[] rowDPA = originalDt.Select("subject = 'DPA'");
DataRow[] rowESG = originalDt.Select("subject = 'ESG'");

DataTable dtConvertKPS = rowKPS.CopyToDataTable();
DataTable retKPS = GenerateTransposedTable(dtConvertKPS);

DataTable dtConvertDPA = rowDPA.CopyToDataTable();
DataTable retDPA = GenerateTransposedTable(dtConvertDPA);

retKPS.Merge(retDPA);

DataTable dtConvertESG = rowESG.CopyToDataTable();
DataTable retESG = GenerateTransposedTable(dtConvertESG);

retKPS.Merge(retESG);

private DataTable GenerateTransposedTable(DataTable inputTable)
{
     DataTable outputTable = new DataTable();
     outputTable.Columns.Add(inputTable.Columns[0].ColumnName.ToString());

     // Header row's second column onwards, 'inputTable's first column taken
     foreach (DataRow inRow in inputTable.Rows)
     {
         string newColName = inRow[0].ToString();
         outputTable.Columns.Add(newColName);
     }

     // Add rows by looping columns
     for (int rCount = 1; rCount <= inputTable.Columns.Count - 1; rCount++)
     {
         DataRow newRow = outputTable.NewRow();

         // First column is inputTable's Header row's second column
         newRow[0] = inputTable.Columns[rCount].ColumnName.ToString();
         for (int cCount = 0; cCount <= inputTable.Rows.Count - 1; cCount++)
         {
             string colValue = inputTable.Rows[cCount][rCount].ToString();
             newRow[cCount + 1] = colValue;
         }
         outputTable.Rows.Add(newRow);
     }

     return outputTable;
}
Click to expand...

I can't code this.
 
Upvote 0 Downvote
You might, maybe doing a very important logical mathematical reasoning effort, but, I understand that it would be very hard for a begginer. Also, that's my specialty, the reasoning. So, glad to help you, a bit more.
I know that often here the members say that this Forum is not a code writting service, but, well, I think we all know that this code I wrote for @patrick would be almost impossible for him, so I hope the others don't get angry with me. Also, patrick, I ask you to try to understand the code (you can ask me whatever about it) so the Forum's goal is fulfilled, that is that, the begginers and everyone here learns and improves his/her skills.
In my Winforms example, first you need to load the original, then load the converted, by clicking the buttons; note that the Column headers are not cells, but the Column names. See the image with the result of your original DataTable.
The code:
C#: 
using System.Data;

namespace WinFormsAppDataTableTranspose
{
    public partial class Form1 : Form
    {
        private DataTable original;

        public Form1()
        {
            InitializeComponent();
        }

        private void button1_Click(object sender, EventArgs e)
        {
            original = new DataTable();
            original.Columns.Add("Day", Type.GetType("System.String"));
            original.Columns.Add("Subject", Type.GetType("System.String"));
            original.Columns.Add("English", Type.GetType("System.Int32"));
            original.Columns.Add("Chinese", Type.GetType("System.Int32"));
            original.Columns.Add("Japanese", Type.GetType("System.Int32"));
            original.Columns.Add("Russian", Type.GetType("System.Int32"));
            DataRow newRow;
            newRow = original.NewRow();
            newRow.ItemArray = new object[] { "202309", "KPS", 10, 20, 30, 50 };
            original.Rows.Add(newRow);
            newRow = original.NewRow();
            newRow.ItemArray = new object[] { "202309", "DPA", 20, 50, 20, 10 };
            original.Rows.Add(newRow);
            newRow = original.NewRow();
            newRow.ItemArray = new object[] { "202309", "ESG", 30, 80, 10, -30 };
            original.Rows.Add(newRow);
            newRow = original.NewRow();
            newRow.ItemArray = new object[] { "202310", "KPS", 40, 110, 0, -70 };
            original.Rows.Add(newRow);
            newRow = original.NewRow();
            newRow.ItemArray = new object[] { "202310", "DPA", 50, 140, -10, -110 };
            original.Rows.Add(newRow);
            newRow = original.NewRow();
            newRow.ItemArray = new object[] { "202310", "ESG", 60, 170, -20, -150 };
            original.Rows.Add(newRow);
            newRow = original.NewRow();
            newRow.ItemArray = new object[] { "202311", "KPS", 70, 200, -30, -190 };
            original.Rows.Add(newRow);
            newRow = original.NewRow();
            newRow.ItemArray = new object[] { "202311", "DPA", 80, 230, -40, -230 };
            original.Rows.Add(newRow);
            newRow = original.NewRow();
            newRow.ItemArray = new object[] { "202311", "ESG", 90, 260, -50, -270 };
            original.Rows.Add(newRow);
            dataGridView1.DataSource = original;
        }

        private void button2_Click(object sender, EventArgs e)
        {
            dataGridView1.DataSource = ConvertTransposed(original);
        }

        private DataTable ConvertTransposed(DataTable original)
        {
            DataTable converted = new DataTable();
            converted.Columns.Add("Subject", Type.GetType("System.String"));
            converted.Columns.Add("Category", Type.GetType("System.String"));
            for (int i = 0; i < original.Rows.Count; i += 3)
                converted.Columns.Add(original.Rows[i][0].ToString());
            DataRow newRow;
            for (int i = 0; i < 12; i++)
            {
                newRow = converted.NewRow();
                newRow.ItemArray = new object[] { i % 4 == 0 ? original.Rows[i / 4][1].ToString() : "",
                                                  original.Columns[2 + i % 4].ColumnName,
                                                  original.Rows[i / 4][2 + i % 4],
                                                  original.Rows[i / 4 + 3][2 + i % 4],
                                                  original.Rows[i / 4 + 6][2 + i % 4]
                };
                converted.Rows.Add(newRow);
            }
            return converted;
        }
    }
}

Regards
Pablo
EDIT: I had to edit the code because there was a little failure. As you may see, this was a bit hard even for me.
 

Attachments

  • datatable.png
    datatable.png
    10.1 KB · Views: 21
Last edited:
Upvote 0 Downvote
Pablo said:
You might, maybe doing a very important logical mathematical reasoning effort, but, I understand that it would be very hard for a begginer. Also, that's my specialty, the reasoning. So, glad to help you, a bit more.
I know that often here the members say that this Forum is not a code writting service, but, well, I think we all know that this code I wrote for @patrick would be almost impossible for him, so I hope the others don't get angry with me. Also, patrick, I ask you to try to understand the code (you can ask me whatever about it) so the Forum's goal is fulfilled, that is that, the begginers and everyone here learns and improve his/her skills.
In my Winforms example, first you need to load the original, then load the converted, by clicking the buttons; note that the Column headers are not cells, but the Column names. See the image with the result of your original DataTable.
The code:
C#: 
using System.Data;

namespace WinFormsAppDataTableTranspose
{
    public partial class Form1 : Form
    {
        private DataTable original;

        public Form1()
        {
            InitializeComponent();
        }

        private void button1_Click(object sender, EventArgs e)
        {
            original = new DataTable();
            original.Columns.Add("Day", Type.GetType("System.String"));
            original.Columns.Add("Subject", Type.GetType("System.String"));
            original.Columns.Add("English", Type.GetType("System.Int32"));
            original.Columns.Add("Chinese", Type.GetType("System.Int32"));
            original.Columns.Add("Japanese", Type.GetType("System.Int32"));
            original.Columns.Add("Russian", Type.GetType("System.Int32"));
            DataRow newRow;
            newRow = original.NewRow();
            newRow.ItemArray = new object[] { "202309", "KPS", 10, 20, 30, 50 };
            original.Rows.Add(newRow);
            newRow = original.NewRow();
            newRow.ItemArray = new object[] { "202309", "DPA", 20, 50, 20, 10 };
            original.Rows.Add(newRow);
            newRow = original.NewRow();
            newRow.ItemArray = new object[] { "202309", "ESG", 30, 80, 10, -30 };
            original.Rows.Add(newRow);
            newRow = original.NewRow();
            newRow.ItemArray = new object[] { "202310", "KPS", 40, 110, 0, -70 };
            original.Rows.Add(newRow);
            newRow = original.NewRow();
            newRow.ItemArray = new object[] { "202310", "DPA", 50, 140, -10, -110 };
            original.Rows.Add(newRow);
            newRow = original.NewRow();
            newRow.ItemArray = new object[] { "202310", "ESG", 60, 170, -20, -150 };
            original.Rows.Add(newRow);
            newRow = original.NewRow();
            newRow.ItemArray = new object[] { "202311", "KPS", 70, 200, -30, -190 };
            original.Rows.Add(newRow);
            newRow = original.NewRow();
            newRow.ItemArray = new object[] { "202311", "DPA", 80, 230, -40, -230 };
            original.Rows.Add(newRow);
            newRow = original.NewRow();
            newRow.ItemArray = new object[] { "202311", "ESG", 90, 260, -50, -270 };
            original.Rows.Add(newRow);
            dataGridView1.DataSource = original;
        }

        private void button2_Click(object sender, EventArgs e)
        {
            dataGridView1.DataSource = ConvertTransposed(original);
        }

        private DataTable ConvertTransposed(DataTable original)
        {
            DataTable converted = new DataTable();
            converted.Columns.Add("Subject", Type.GetType("System.String"));
            converted.Columns.Add("Category", Type.GetType("System.String"));
            for (int i = 0; i < original.Rows.Count; i += 3)
                converted.Columns.Add(original.Rows[i][0].ToString());
            DataRow newRow;
            for (int i = 0; i < 12; i++)
            {
                newRow = converted.NewRow();
                newRow.ItemArray = new object[] { i % 4 == 0 ? original.Rows[i / 4 * 3][1].ToString() : "",
                                                  original.Columns[2 + i % 4].ColumnName,
                                                  original.Rows[(i % 4) / 4 * 3][2 + i % 4],
                                                  original.Rows[((i % 4) / 4 + 1) * 3][2 + i % 4],
                                                  original.Rows[((i % 4) / 4 + 2) * 3][2 + i % 4]
                };
                converted.Rows.Add(newRow);
            }
            return converted;
        }
    }
}

Regards
Pablo
Click to expand...

Thank you very much.

I am currently studying to understand the code you provided.

I'm studying your code, and if I don't understand your code, I'll ask a question.
 
Upvote 0 Downvote
You're welcome!
Please see I edited the code a few minutes ago because there was a little logical failure. Now it is OK.
Happy to help.
Regards
Pablo
 
Upvote 0 Downvote
pablo, your code may have been easier to understand if you'd given names variables to all the modulo/multiplication calculations that built the object array. Even to an experienced programmer that's just one big block of magic numbers

Transposing isn't hard; it's just running two loops that manipulate x and y variables, and reading [x][y] from the source but using them the other way round [y][x] when writing the destination. It seems you have a lot of hardicded numbers that could perhaps be done away with
 
Last edited:
Upvote 0 Downvote
You mean that before having the original DataTable you gave us first, you have one with the rows unordered?
I have just added the sorting functionality to the code.
Hope this helps and is useful for your learning.
Pablo

C#: 
using System.Data;

namespace WinFormsAppDataTableTranspose
{
    public partial class Form1 : Form
    {
        private DataTable original;

        public Form1()
        {
            InitializeComponent();
        }

        private void button1_Click(object sender, EventArgs e)
        {
            original = new DataTable();
            original.Columns.Add("Day", Type.GetType("System.String"));
            original.Columns.Add("Subject", Type.GetType("System.String"));
            original.Columns.Add("English", Type.GetType("System.Int32"));
            original.Columns.Add("Chinese", Type.GetType("System.Int32"));
            original.Columns.Add("Japanese", Type.GetType("System.Int32"));
            original.Columns.Add("Russian", Type.GetType("System.Int32"));
            DataRow newRow;
            newRow = original.NewRow();
            newRow.ItemArray = new object[] { "202309", "KPS", 10, 20, 30, 50 };
            original.Rows.Add(newRow);
            newRow = original.NewRow();
            newRow.ItemArray = new object[] { "202311", "ESG", 90, 260, -50, -270 };
            original.Rows.Add(newRow);
            newRow = original.NewRow();
            newRow.ItemArray = new object[] { "202309", "DPA", 20, 50, 20, 10 };
            original.Rows.Add(newRow);
            newRow = original.NewRow();
            newRow.ItemArray = new object[] { "202310", "DPA", 50, 140, -10, -110 };
            original.Rows.Add(newRow);
            newRow = original.NewRow();
            newRow.ItemArray = new object[] { "202310", "KPS", 40, 110, 0, -70 };
            original.Rows.Add(newRow);
            newRow = original.NewRow();
            newRow.ItemArray = new object[] { "202311", "DPA", 80, 230, -40, -230 };
            original.Rows.Add(newRow);
            newRow = original.NewRow();
            newRow.ItemArray = new object[] { "202309", "ESG", 30, 80, 10, -30 };
            original.Rows.Add(newRow);
            newRow = original.NewRow();
            newRow.ItemArray = new object[] { "202310", "ESG", 60, 170, -20, -150 };
            original.Rows.Add(newRow);
            newRow = original.NewRow();
            newRow.ItemArray = new object[] { "202311", "KPS", 70, 200, -30, -190 };
            original.Rows.Add(newRow);
            dataGridView1.DataSource = original;
        }

        private void button2_Click(object sender, EventArgs e)
        {
            dataGridView1.DataSource = ConvertTransposed(original);
        }

        private DataTable ConvertTransposed(DataTable original)
        {
            DataTable converted = new DataTable();
            converted.Columns.Add("Subject", Type.GetType("System.String"));
            converted.Columns.Add("Category", Type.GetType("System.String"));
            for (int i = 0; i < original.Rows.Count; i += 3)
                converted.Columns.Add(original.Rows[i][0].ToString(), Type.GetType("System.Int32"));
            DataRow newRow;
            for (int i = 0; i < 12; i++)
            {
                newRow = converted.NewRow();
                newRow.ItemArray = new object[] { i % 4 == 0 ? original.Rows[i / 4][1].ToString() : "",
                                                  original.Columns[2 + i % 4].ColumnName,
                                                  original.Rows[i / 4][2 + i % 4],
                                                  original.Rows[i / 4 + 3][2 + i % 4],
                                                  original.Rows[i / 4 + 6][2 + i % 4]
                };
                converted.Rows.Add(newRow);
            }
            return converted;
        }

        private void button3_Click(object sender, EventArgs e)
        {
            original = Sorted(original);
            dataGridView1.DataSource = original;
        }

        private DataTable Sorted(DataTable original)
        {
            DataTable table = original.AsEnumerable().OrderBy(r => r["Subject"]).ThenBy(r => r["Day"]).CopyToDataTable();
            DataTable sorted = original.Clone();
            for (int i = 0; i < 9; i++)
            {
                sorted.Rows.Add(table.Rows[i % 3 == 0 ? 6 + i / 3 : (i % 3 == 1 ? 0 + i / 3 : 3 + i / 3)].ItemArray);
            }
            return sorted;
        }
    }
}
 
Upvote 0 Downvote
Hello, @cjard,
I have just seen your message. You ask for it, you get it, here's the variables in the row building:
C#: 
            string subject, category;
            string day202309, day202310, day202311;
            for (int i = 0; i < 12; i++)
            {
                newRow = converted.NewRow();
                subject = i % 4 == 0 ? original.Rows[i / 4][1].ToString() : "";
                category = original.Columns[2 + i % 4].ColumnName;
                day202309 = original.Rows[i / 4][2 + i % 4].ToString();
                day202310 = original.Rows[i / 4 + 3][2 + i % 4].ToString();
                day202311 = original.Rows[i / 4 + 6][2 + i % 4].ToString();
                newRow.ItemArray = new object[] { subject, category, day202309, day202310, day202311 };
                converted.Rows.Add(newRow);
            }

It is not an exact transposing, like v[y][x] = z[x][y], and I think (just my opinion) that the code is the shortest possible. As I commented before, I'm good at logical reasoning and algorithms. Anyway, any correction or suggestion that anyone of you want to make me, I will take it and thank it to you, as whenever you help me in programming matters in which I am less experienced.
@patrick, you can replace this piece of code I wrote here, so it would be easier to read, as cjard proposed.
Pablo
 
Upvote 0 Downvote
Pablo said:
Hello, @cjard,
I have just seen your message. You ask for it, you get it, here's the variables in the row building:
C#: 
            string subject, category;
            string day202309, day202310, day202311;
            for (int i = 0; i < 12; i++)
            {
                newRow = converted.NewRow();
                subject = i % 4 == 0 ? original.Rows[i / 4][1].ToString() : "";
                category = original.Columns[2 + i % 4].ColumnName;
                day202309 = original.Rows[i / 4][2 + i % 4].ToString();
                day202310 = original.Rows[i / 4 + 3][2 + i % 4].ToString();
                day202311 = original.Rows[i / 4 + 6][2 + i % 4].ToString();
                newRow.ItemArray = new object[] { subject, category, day202309, day202310, day202311 };
                converted.Rows.Add(newRow);
            }

It is not an exact transposing, like v[y][x] = z[x][y], and I think (just my opinion) that the code is the shortest possible. As I commented before, I'm good at logical reasoning and algorithms. Anyway, any correction or suggestion that anyone of you want to make me, I will take it and thank it to you, as whenever you help me in programming matters in which I am less experienced.
@patrick, you can replace this piece of code I wrote here, so it would be easier to read, as cjard proposed.
Pablo
Click to expand...

I'm still analyzing your code.
 
Upvote 0 Downvote
Pablo said:
here's the variables in the row building
Click to expand...

I was also thinking about the maths operations you do.. essentially you're saving on running a loop of 3 that contains a loop of 4 (or vice versa), which is 12 operations, by doing a single loop of 12 and using divide and modulo to simulate nested loops. That's probably well over-engineered for a newbie to understand

Consider:

C#: 
for(int x = 0; x < 3; x++){
  for(int y = 0; y < 4; y++){
    Console.WriteLine($"{x},{y}");
  }
}

for(int n = 0; n < 12; n++){
  int x = n/4;
  int y = n%4;
  Console.WriteLine($"{x},{y}");
}

Both these print out

0,0
0,1
0,2
0,3
1,0
1,1
1,2
1,3
2,0
2,1
2,2
2,3

But which do you think is easier for a newbie to understand?
 
Upvote 0 Downvote

“Programs must be written for people to read, and only incidentally for machines to execute.”​

-Harold Abelson
Click to expand...
 
Upvote 0 Downvote
patrick said:
Hello.
The conversion must be done as shown in the picture below.
Click to expand...
By the way, it's an orderby on subject, not a groupby. People tend to mix the terms up, but the transposition is that days colmun values are becoming column, and columns are becoming rows. Rows are being ordered by Subject to clump subjects together, but people tend to use "group" for "clump". I'd recommend not actually removing the subject data from the row, just hide it if the row above (after ordering) is the same subject
 
Upvote 0 Downvote
Pablo said:
Hello, @cjard,
I have just seen your message. You ask for it, you get it, here's the variables in the row building:
C#: 
            string subject, category;
            string day202309, day202310, day202311;
            for (int i = 0; i < 12; i++)
            {
                newRow = converted.NewRow();
                subject = i % 4 == 0 ? original.Rows[i / 4][1].ToString() : "";
                category = original.Columns[2 + i % 4].ColumnName;
                day202309 = original.Rows[i / 4][2 + i % 4].ToString();
                day202310 = original.Rows[i / 4 + 3][2 + i % 4].ToString();
                day202311 = original.Rows[i / 4 + 6][2 + i % 4].ToString();
                newRow.ItemArray = new object[] { subject, category, day202309, day202310, day202311 };
                converted.Rows.Add(newRow);
            }

It is not an exact transposing, like v[y][x] = z[x][y], and I think (just my opinion) that the code is the shortest possible. As I commented before, I'm good at logical reasoning and algorithms. Anyway, any correction or suggestion that anyone of you want to make me, I will take it and thank it to you, as whenever you help me in programming matters in which I am less experienced.
@patrick, you can replace this piece of code I wrote here, so it would be easier to read, as cjard proposed.
Pablo
Click to expand...


[i / 4 + 3][2 + i % 4]
Click to expand...
[ i/4 + 3] <== 4 is English, Chines, Japanese, Russian , 3 is Day
[2+ i%4] <== 4 is English, Chines, Japanese, Russian , 2 is English Column
Did I understand this correctly? Thank you for teaching me.
 
Upvote 0 Downvote

Share this page

Latest posts

Back
Top Bottom