Pivoting input data - is there a structure I can use?

Question

I have input data given as a list of objects:

Name: "Room A",
Time: "15:00",
Topic: "Some Topic"

Name: "Room A",
Time: "18:00",
Topic: "Some Other Topic"

Name: "Room B",
Time: "12:00",
Topic: "Some More Topic"

Name: "Room C",
Time: "13:00",
Topic: "Even More Topic"

and I have to create different tables from that. For a start, there should be a table where rows are based on time, and cols are based on the Name, but in the future, when the objects grow, I would like to be able to pivot around other properties.

I would like to code something like the following:

var structure = new SomeCSharpStructure(); // <------

for(int i=0;i<24) structure.AddRow(i+":00");

foreach(var name in inputData.Select(x=>x.Name).Distinct()) structure.AddColumn(name);

foreach(var data in inputData) {
    structure[data.Name][data.Time] = data.Topic; // or
    // structure.AddCell(data.Name,data.Time,data.Topic);
}

...

foreach(var row in structure.getRows()) foreach(var cell in row.getCell())
{
     PrintCellContent(cell);
}

although I am also open for other suggestions.

But right now I am searching for a structure that would support such an instantiation without spilling NullPointerExceptions or IndexOutOfRangeExceptions all over the place. Before I implement it myself, I'd like to make sure there is none available.

Does anyone know a structure that would suit my needs?

Answer 1

Instead of writing custom code that groups your data, you can use NReco.PivotData library that already implements pivot table calculations (I'm the author of this library, and it can be used for free). It provides PivotTable structure for accessing table values in terms of rows and columns:

var pvtData = new PivotData(new[] {"Name", "Time"}, new CountAggregatorFactory() );
pvtData.ProcessData( inputData, new ObjectMember().GetValue );
var pvtTbl = new PivotTable(new[] {"Name"}, new[] {"Time"}, pvtData);
var columns = pvtTbl.ColumnKeys;
var rows = pvtTbl.RowKeys;
var cellValue = pvtTbl[0,0].Value; // get first cell value

Some explanations:

• PivotData class build in-memory cube (multidimensional dataset) and performs data aggregation
• ObjectMember provides POCO-model property accessor for dimension values
• PivotTable class provides table model that may be used for pivot table rendering. It can be used for getting totals and subtotals.