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

Question

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 is there a name for it?
• Or is there a pseudocode algo I can follow?

Answer 1

Based on two simple helper functions, group and mapObj, this might do it for you:

const pivot = ([key, ...keys], combine = (x) => x) => (xs) =>
  key == undefined
    ? combine (xs)
    : mapObj (pivot (keys, combine)) (group (key) (xs))

Using a straightforward recursion on the list of keys we want to gather and nest, we could then call pivot (['CAT1', 'CAT2', 'CAT3']) (input) to get a result like this:

{
  "UP": {
    "LIGHT-BLUE": {
      "INLINE": [
        {"VAL": "651"},
        {"VAL": "683"}
      ]
    },
    "MILD-GREEN": {
      "STRANGE": [
        {"VAL": "189"}
      ],
      // ...
    },
    // ...
  },
  // ...
}

And by passing it an additional parameter which explains what we want to do with the innermost values, we could call pivot (['CAT1', 'CAT2', 'CAT3'], sumBy ('VAL')) (input) to get your requested structure of

{
  "UP": {
    "LIGHT-BLUE": {
      "INLINE": 1334
    },
    "MILD-GREEN": {
      "STRANGE": 189,
      // ...
    },
    // ...
  },
  // ...
}

All these functions are in this snippet:

const group = (key) => (xs) => 
  xs .reduce ((a, {[key]: k, ...rest}) => ({...a, [k]: [...(a[k] || []), rest]}), {})

const sumBy = (key) => (xs) =>
  xs .reduce ((a, x) => a + Number (x [key]), 0)

const mapObj = (fn) => (o) =>
  Object .fromEntries (Object .entries (o) .map (([k, v]) => [k, fn(v)]))

const pivot = ([key, ...keys], combine = (x) => x) => (xs) =>
  key == undefined
    ? combine (xs)
    : mapObj (pivot (keys, combine)) (group (key) (xs))

const input = [{ "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" }];

console .log (pivot (['CAT1', 'CAT2', 'CAT3']) (input))
console .log (pivot (['CAT1', 'CAT2', 'CAT3'], sumBy ('VAL')) (input))

.as-console-wrapper {max-height: 100% !important; top: 0}

group takes a property name and a list of objects and groups them by the value of that property, removing that property from their internals. For example,

group ('foo') ([
  {foo: 'a', bar: 'x', baz: 'p'}, 
  {foo: 'b', bar: 'y', baz: 'p'}, 
  {foo: 'a', bar: 'w', baz: 'm'}
])

will return

{
  a: [{bar: 'x', baz: 'p'}, {bar: 'w', baz: 'm'}], 
  b: [{bar: 'y', baz: 'p'}]
}

mapObj is similar to array mapping, applying the function to the value at every key in the object. For instance,

map (n => n * n) ({a: 1, b: 2, c: 3, d: 4, e: 5})

yields

{a: 1, b: 4, c: 9, d: 16, e: 25}

and sumBy just sums up the properties matching a given key, so that

sumBy ('count') ([{id: 'a', count: 3}, {id: 'b', count: 11}, {id: 'c', count: 8}])

yields

22

The main function, pivot simply groups on the first key, then uses, mapObject to recursively call pivot with the remaining keys on each of the values in the resulting object. When there are no keys left, we call the combine function on the innermost structures. combine defaults to the identity function.

Two minor notes:

• In practice, I have a version of map that handles arrays and objects; I would probably use that in this function instead of mapObj

• group is much less effienct than might be desired. If this turns out to be a bottleneck in an application, I would look to replace it with something that mutates the reduce accumulator rather than creating a new one at every step. But I would stick with this simplicity unless and until I can prove it's an actual performance drag.

Answer 2

Here is an example using Array.prototype.reduce() that accommodates m-levels.

Using destructuring we make an initial separation between the value and the remaining keys that will be used as branches. We retrieve the Object.values() of these branches, as the keys are not used. The last two of these values will be used to construct the inner-most array of the branch, and we use a second .reduce() call, seeded with the outer accumulator, to build or retrieve the required branch. Finally we construct the inner array on the returned terminal object and push the leaf object into it.

Note the use of Logical nullish assignment (??=) which you may need to replace with an OR short-circuit for compatibility

const input = [{ "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" }];

const pivot = input.reduce((acc, { VAL, ...cats }) => {
  const branches = Object.values(cats);
  const [leafKey, leaf] = branches.splice(-2);

  const terminal = branches.reduce((_acc, branch) => (
    _acc[branch] ??= {}
  ), acc);

  (terminal[leafKey] ??= []).push({ [leaf]: VAL });
  
  return acc;
}, {});

console.log(pivot)

.as-console-wrapper { max-height: 100% !important; top: 0; }

Edit

The above snippet works on the input given but is fragile in a number of ways.

Its primary weakness is that it relies on the ordering of object properties, and assumes that this order will remain consistent across objects which even with recent standardization of ordering, is not reliable, especially between objects. To fix this it would be best to explicitly define the branch ordering.

Aside from this the expected structure requires that there be at least 2 levels for meaningful results, so at a minimum you could check for this and return early if not met.

const input = [{ "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" }];

function pivot(arr, { path, value }) {
  if (path.length < 2) {
    console.log('Pivot called with too few path elements');
    return
  }
  return arr.reduce((acc, { [value]: val, ...rest }) => {
    const branches = path.map(k => rest[k]);
    const [leafKey, leaf] = branches.splice(-2);

    const terminal = branches.reduce((_acc, branch) => (
      _acc[branch] ??= {}
    ), acc);

    (terminal[leafKey] ??= []).push({ [leaf]: val });

    return acc;
  }, {});
}

console.log(pivot(input, { path: ['CAT1', 'CAT2', 'CAT3'], value: 'VAL' }))

.as-console-wrapper { max-height: 100% !important; top: 0; }

To better understand the inner .reduce() call you can rewrite it as a .forEach() with an external accumulator

let terminal = acc;
branches.forEach(branch => {
  if (!(branch in terminal)) {
    terminal[branch] = {};
  }
  terminal = terminal[branch];
})

---

Hard coded

You could also simply hardcode the structure if it is unlikely to change (also an answer to your question on how to expand the linked duplicate). To do this you simply need to assert that every level exists before pushing the final value.

const input = [{ "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" }];

const pivot = input
  .reduce((acc, { CAT1, CAT2, CAT3, VAL }) => {
  
    acc[CAT1] = acc[CAT1] || {};
    acc[CAT1][CAT2] = acc[CAT1][CAT2] || [];
    
    acc[CAT1][CAT2].push({ [CAT3]: VAL });

    return acc;
  }, {});

console.log(pivot)

.as-console-wrapper { max-height: 100% !important; top: 0; }