Transpose list of objects

Question

How can I transpose the columns of a table like the one in the image below

For this format:

Mercado | Semana 5 | Semana 6
FUT AUD | 0.9986   | 1.0035
FUT CAD | 1.0000   | 1.0062
FUT CHF | 1.0059   | 1.0158

I tested some implementations I found on forums but used var to transpose, and as I need to display the data in a Listview and write to a CSV file, I need a pre-established object.

The number of weeks varies (according to what the user chooses), and there may be N weeks/week columns. It would be good to try to transpose from the list to a Datatable?

Answer 1

Step-by-step resolution:

Step 1:

I passed the list to a DataTable pre-formatted (equal to image of the question).

var dtable = new DataTable();
            dtable.Columns.Add("Mercado", Type.GetType("System.String"));
            dtable.Columns.Add("NSemana", Type.GetType("System.Int32"));
            dtable.Columns.Add("Variacao", Type.GetType("System.Decimal"));
            foreach(var linha in relatorio.Futuros.Semanas)
            {
               foreach(var registro in linha.MercadoDaSemana)
                {
                    dtable.Rows.Add(new object[] { registro.Mercado.Simbolo, linha.SemanaDoAno, registro.TotalVariacaoMercado });
                }
            }

Step 2:

I pivoted the DataTable with the following algorithm:

 public static class FormatarDados
{

    public static DataTable ParaPivotTable(DataTable table, string columnX, string columnY, string columnZ, string nullValue, bool sumValues)
    {
        DataTable returnTable = new DataTable();

        if(columnX == string.Empty) { columnX = table.Columns[0].ColumnName; }

        returnTable.Columns.Add(columnY);

        List<string> columnXValues = new List<string>();
        foreach(DataRow dr in table.Rows)
        {
            string columnXTemp = dr[columnX].ToString();
            if (!columnXValues.Contains(columnXTemp))
            {
                columnXValues.Add(columnXTemp);
                returnTable.Columns.Add(columnXTemp);
            }
        }

        if (columnY != string.Empty && columnZ != string.Empty)
        {
            List<string> columnYValues = new List<string>();
            foreach (DataRow dr in table.Rows)
            {
                if (!columnYValues.Contains(dr[columnY].ToString()))
                {
                    columnYValues.Add(dr[columnY].ToString());
                }
            }

            foreach (string columnYValue in columnYValues)
            {
                DataRow drReturn = returnTable.NewRow();
                drReturn[0] = columnYValue;
                DataRow[] rows = table.Select(columnY + "='" + columnYValue + "'");

                foreach (DataRow dr in rows)
                {
                    string rowColumnTitle = dr[columnX].ToString();

                    foreach (DataColumn dc in returnTable.Columns)
                    {
                        if (dc.ColumnName == rowColumnTitle)
                        {
                            if (sumValues)
                            {
                                try
                                {
                                    drReturn[rowColumnTitle] = Convert.ToDecimal(drReturn[rowColumnTitle]) + Convert.ToDecimal(dr[columnZ]);
                                }
                                catch
                                {
                                    drReturn[rowColumnTitle] = dr[columnZ];
                                }
                            }
                            else
                            {
                                drReturn[rowColumnTitle] = dr[columnZ];
                            }
                        }
                    }
                }
                returnTable.Rows.Add(drReturn);
            }
        }
        else
        {
            throw new Exception("Não existem colunas para fazer a inversão");
        }

        if (nullValue != string.Empty)
        {
            foreach(DataRow dr in returnTable.Rows)
            {
                foreach(DataColumn dc in returnTable.Columns)
                {
                    if(dr[dc.ColumnName].ToString() == string.Empty)
                    {
                        dr[dc.ColumnName] = nullValue;
                    }
                }
            }
        }
        return returnTable;
    }
}

Step 3:

I called the function ParaPivotTable:

  var tblRetorno = FormatarDados.ParaPivotTable(dtable, "NSemana", "Mercado", "Variacao", "-", false);

Step 4: *

I saved the data from the pivot table tblRetorno in a . csv:

CommonEngine.DataTableToCsv(tblRetorno, pathArquivo, ',');

*I used the Filehelpers.

The algorithm of step 2 was the second example of this post here.