Need LINQ help: Create pivot table of date and location

Hello all,

I am having a bit of trouble trying to find a solution or at least a start to a solution for this datatable manipulation. I’m starting with a datatable that looks something like this
startingtable
Where I have a list of locations and dates followed by a lot of other information(it wont be taken into account in this, I wanted to mention it however). I am looking find a way to count the number of the same dates per location and create a datatable that looks similar to this
outputtable
I know that I could loop through the table row by row and and create a table similar to my output by storing counts but I want to avoid that if possible since there will be a large number of rows and there could be a lot of different dates and locations.

I also know that a similar task can be done in excel quite easily using the pivot table function which is what is being done in the process right now (I will not need the grand total columns).
excelpivottable

I’m hoping someone can point me in the right direction and help me create a LINQ function that could do a similar thing. Thank you all!

c# code to make pivot table with Linq Method.

DataTable dt = new DataTable();
dt.Columns.Add("Key");
dt.Columns.Add("Value");

dt.Rows.Add(new[] {"US","18"});
dt.Rows.Add(new[] {"EN","17"});
dt.Rows.Add(new[] {"RU","16"});
dt.Rows.Add(new[] {"RU","16"});
dt.Rows.Add(new[] {"US","18"});
dt.Rows.Add(new[] {"US","15"});
dt.Rows.Add(new[] {"FR","15"});

DataTable newdt = new DataTable();
newdt.Columns.Add("Key");
List keyArr = dt.AsEnumerable()
				.Select(r => r[0].ToString()).Distinct().ToList();
keyArr.ForEach(v => newdt.Rows.Add(new[]{v}));
List valueArr = dt.AsEnumerable()
				.Select(r => r[1].ToString()).Distinct().OrderBy(r=>r).ToList();
valueArr.ForEach(v => {
		DataColumn c = new DataColumn(v,typeof(int));
		c.DefaultValue = 0;
		newdt.Columns.Add(c);
	}
);

var group = dt.AsEnumerable().GroupBy(r => r[0], r => r[1]).ToList();

Dictionary<string,ValueTuple[]> pivot = dt.AsEnumerable().GroupBy(r => r[0], r => r[1])
	.ToDictionary(g => g.Key.ToString(), g => g.GroupBy(sg => sg).Select(sg => (sg.Key.ToString(),sg.Count())).ToArray());

foreach(DataRow row in newdt.Rows)
{
	foreach(var t in pivot[row["Key"].ToString()])
	{
		row[t.Item1] = t.Item2;
	}
}

Result
Source Table => Initialized Target Table => Result Table
(Printed by Linq Pad)
pivot

1 Like

@JosephNehl - If you are just looking to create a pivot table , then you can use studiox. Not sure about your full requirement.

I have something like this, I had a workflow created in StudioX before. So first I switched the profile to StudioX and Opened that Workflow. Once WF is loaded, i swithced the profile again to Studio.

After that, I just repointed out my input file again then tried running, boom it ran like a champ.

Workflow

Output - After ran from Studio.

1 Like

Thank you both for your solutions! In the meanwhile I also created a workflow that manipulates and creates a new table with information needed using some linq pieced together. I will examine both of these options as well and possibly change my solution to one of these if it is working better. I actually did not know that you could create a flow in studiox and change the profile to studio pro and keep the same workflow! That’s very good information to have going forward. Thank you both again!

@JosephNehl - Glad it helped you… Thanks

This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.