How Do I Pivot An Array Of Objects in JavaScript then display on HTML Table

Question

I have an array of objects which I need to convert to a table (kind of pivoting the data). Saying this I need to get another array of objects with unique titles which have nested arrays of objects with pairs of the values then add the content vertically and horizontally. (Please refer to the image) I have been able to generate an array of pivoted values but I will need to display them in a table and carry out some addition

Find here the Data

const data=[{
  "Id": 1,
  "Grade": "Grade A",
  "category": "Grains",
  "subCategory": "Maize",
  "sales": 1200
},
{
  "Id": 2,
  "Grade": "Grade B",
  "category": "Grains",
  "subCategory": "Wheat",
  "sales": 130
},
{
  "Id": 3,
  "Grade": "Grade B",
  "category": "Grains",
  "subCategory": "Banana",
  "sales": 1200
},
{
  "Id": 4,
  "Grade": "Grade C",
  "category": "Grains",
  "subCategory": "Apple",
  "sales": 1400
},

{
  "Id": 5,
  "Grade": "Grade A",
  "category": "Grains",
  "subCategory": "Maize",
  "sales": 1200
},
{
  "Id": 6,
  "Grade": "Grade B",
  "category": "Grains",
  "subCategory": "Wheat",
  "sales": 130
},
{
  "Id": 7,
  "Grade": "Grade B",
  "category": "Grains",
  "subCategory": "Banana",
  "sales": 1200
},
{
  "Id": 8,
  "Grade": "Grade C",
  "category": "Grains",
  "subCategory": "Apple",
  "sales": 1400
},
{
  "Id": 7,
  "Grade": "Grade B",
  "category": "Grains",
  "subCategory": "Banana",
  "sales": 1200
},
{
  "Id": 8,
  "Grade": "Grade C",
  "category": "Grains",
  "subCategory": "Apple",
  "sales": 1400
}]



let pivoted = data.reduce((prev, cur) => {
  let existing = prev.find(x => x.title === cur.title);

  if (existing)
    existing.values.push(cur)
  else
    prev.push({
      title: cur.title,
      values: [cur]
    });

  return prev;
}, []);

console.log(pivoted); 

Answer 1

If you are open to using a table library such as Tabulator, here is an example. I added couple more data points to show functionality. I am sure there is a simpler way but hope this helps.

const data = [{
    "Id": 1,
    "Grade": "Grade A",
    "category": "Grains",
    "subCategory": "Maize",
    "sales": 1200
  },
  {
    "Id": 2,
    "Grade": "Grade B",
    "category": "Grains",
    "subCategory": "Wheat",
    "sales": 130
  },
  {
    "Id": 3,
    "Grade": "Grade B",
    "category": "Fruits",
    "subCategory": "Banana",
    "sales": 1200
  },
  {
    "Id": 4,
    "Grade": "Grade C",
    "category": "Fruits",
    "subCategory": "Apple",
    "sales": 1400
  },

  {
    "Id": 5,
    "Grade": "Grade A",
    "category": "Grains",
    "subCategory": "Maize",
    "sales": 1200
  },
  {
    "Id": 6,
    "Grade": "Grade B",
    "category": "Grains",
    "subCategory": "Wheat",
    "sales": 130
  },
  {
    "Id": 7,
    "Grade": "Grade B",
    "category": "Fruits",
    "subCategory": "Banana",
    "sales": 1200
  },
  {
    "Id": 8,
    "Grade": "Grade C",
    "category": "Fruits",
    "subCategory": "Apple",
    "sales": 1400
  },
  {
    "Id": 7,
    "Grade": "Grade B",
    "category": "Fruits",
    "subCategory": "Banana",
    "sales": 1200
  },
  {
    "Id": 8,
    "Grade": "Grade C",
    "category": "Fruits",
    "subCategory": "Apple",
    "sales": 1400
  },
  {
    "Id": 9,
    "Grade": "Grade C",
    "category": "Fruits",
    "subCategory": "Banana",
    "sales": 1000
  },
  {
    "Id": 10,
    "Grade": "Grade A",
    "category": "Grains",
    "subCategory": "Wheat",
    "sales": 500
  }
];

var grades = new Set(data.map(i => i.Grade));  //Get list of grades
grades.forEach(grade => grades[grade] = 0); // Initialize grade amount to 0

// Insert grades into each data item
var tableData = data.map((item) => ({
  ...item,
  ...grades,
  [item.Grade]: item.sales
}));

// Group and sum each item in data by category, subCategory and grades
var pivotTable = [...tableData.reduce((r, o) => {
  const key = o.category + '-' + o.subCategory + '-' + Array.from(grades).join('-');
  const item = r.get(key) || Object.assign({}, o, grades);

  grades.forEach(grade => item[grade] += o[grade]);

  delete item.Grade;

  return r.set(key, item);
}, new Map).values()];

// Prep columns to add into Tabulator for each grade
var gradeCols = [];
grades.forEach(grade => {
  var col = {
    title: grade,
    field: grade,
    bottomCalc: "sum"
  }
  gradeCols.push(col);
});

// Hide repeating row cells
var pivotFormatter = (row) => {
  try {
    if (row.getPosition(true) != 0) {
      var data = row.getCells();
      var prev = row.getPrevRow().getCells();
      var pivotColumns = 2;

      for (i = 0; i < pivotColumns; i++) {
        if (data[i].getValue() == prev[i].getValue() && data[0].getValue() == prev[0].getValue()) {
          data[i].getElement().style.visibility = "hidden";
        }
      }
    }
  } catch (e) {}
}

// Tabulator table
var table = new Tabulator("#table", {
  height: '100%',
  layout: "fitColumns",
  headerSort: false,
  rowFormatter: (row) => pivotFormatter(row),
  columns: [{
      title: "Category",
      field: "category"
    },
    {
      title: "SubCategory",
      field: "subCategory",
    },
    ...gradeCols,
    {
      title: "Total",
      field: "total",
      mutator: (value, data, type, params, component) => Array.from(grades).reduce((acc, item) => acc + data[item], 0),
      bottomCalc: "sum"
    }
  ]
});

// Set tabulator data
table.setData(pivotTable);

html, body {font-size: 12px;}

.tabulator {font-family: -apple-system,BlinkMacSystemFont,Segoe UI,Helvetica,Arial,sans-serif,Apple Color Emoji,Segoe UI Emoji;}

<link href="https://cdnjs.cloudflare.com/ajax/libs/tabulator/4.9.3/css/bootstrap/tabulator_bootstrap4.min.css" rel="stylesheet"/>
<script src="https://cdnjs.cloudflare.com/ajax/libs/tabulator/4.9.3/js/tabulator.min.js"></script>
<div id="table"></div>