javascript object array to json m-level pivot table-style data format

What I am trying to do is create a pivot table-style data format from a javascript array of objects.

I've tried to research this but I'm struggling I think in part because I'm not using the right language to describe what I'm looking for.

Here is some background

Often I work with data in excel, and the pivot table functionality.

I wrote a simple VBA macro to save an excel table into a json file as an array of objects.

So here is such a table:

And I convert this into something like the following. This part is easy for me.

let objArr = [
 {"CAT1":"UP","CAT2":"LIGHT-BLUE","CAT3":"INLINE","VAL":"651"},
 {"CAT1":"UP","CAT2":"LIGHT-BLUE","CAT3":"INLINE","VAL":"683"},
 {"CAT1":"UP","CAT2":"MILD-GREEN","CAT3":"STRANGE","VAL":"189"},
 {"CAT1":"UP","CAT2":"MILD-GREEN","CAT3":"INSIDE","VAL":"113"},
 {"CAT1":"LEFT","CAT2":"HOT-PINK","CAT3":"INSIDE-OUT","VAL":"899"},
 {"CAT1":"LEFT","CAT2":"HOT-PINK","CAT3":"INSIDE-OUT","VAL":"901"},
 {"CAT1":"LEFT","CAT2":"BORON-COLOR","CAT3":"FLAT","VAL":"345"},
 {"CAT1":"LEFT","CAT2":"BORON-COLOR","CAT3":"OUTLINE","VAL":"678"},
 {"CAT1":"LEFT","CAT2":"PALE-BLUE","CAT3":"MOST-SHAPE","VAL":"611"},
 {"CAT1":"LEFT","CAT2":"PALE-BLUE","CAT3":"DARK","VAL":"942"},
 {"CAT1":"UP","CAT2":"LIGHT-RED","CAT3":"ROUND","VAL":"769"}
]

So I can work with the array of objects in javascript and that's all quite nice and useful. But I'm having a hard time turning it into what I need for certain projects.

Here's a pivot table in excel with the data from the first table:

So whatever process took place in excel to turn table 1 into table 2, I want to replicate that but in javascript, going from the array of objects above, into something like this:

pivotArr = [
 {
  "LEFT":[
   "BORON-COLOR":[
    "FLAT":345,
    "OUTLINE":678
   ],
   "HOT-PINK":[
    "INSIDE-OUT":1800
   ],
   "PALE-BLUE":[
    "DARK":942,
    "MOST-SHAPE":611
   ]
  ],
  "UP":[
   "LIGHT-BLUE":[
    "INLINE":1334
   ],
   "LIGHT-RED":[
    "ROUND":769
   ],
   "MILD-GREEN":[
    "INSIDE":113,
    "STRANGE":189
   ]
  ]
 }
]

I'm imagining something like this:

let pivotArr = convertObjArr2PivotArr(objArr, key0, key1, ....)

In the above case the array of object is objArr , and key0="CAT1", key1="CAT2" etc.

What I have tried:

Now, if I only had 1 category, it would be easy.

Here's something that's not quite what I want, but it's almost there:

function groupObjArr(objArr, key, val) {
  let obj = {};
  for (let i = 0; i < objArr.length; i++) { 
   obj[objArr[i][key]] = (obj[objArr[i][key]] + parseFloat(objArr[i][val]) || parseFloat(objArr[i][val]));
  }
  return obj;
}

The hard part is aggregating the data by an arbitrary number of levels, m

If I have a known number of levels, I can scale it up. Ie. Always 2 levels/nodes/keys etc (I'm thinking of this like a tree).

But I may have m levels, I don't know how to do that.

I've tried grouping row-by-row, and also grouping a whole column (key), and moving left (to bigger groups), and also starting from the biggest group, and adding subgroups.

Each time I try this I get more confused.

• Is there an established algorithm for this type of procedure?
• Or isthere a name for it?
• Or is there a pseudocode algo I can follow?

Kindly,

from Recent Questions - Stack Overflow https://ift.tt/3dcUZzU
https://ift.tt/3rCoEaY