Add new summary row for cols share the same vales of 2 different cols

Question

I have a matrix algorithm:

Input:

const input = [
    ['Camry', 'Toyota', 'Jan', 'Nowhere Town', '50'],
    ['Camry', 'Toyota', 'Feb', 'Nowhere Town', '70'],
    ['Camry', 'Toyota', 'Jan', 'Random City', '3000'],
    ['Prius', 'Toyota', 'Jan', 'Nowhere Town', '60'],
    ['Prius', 'Toyota', 'Jan', 'Random Town', '60'],
    ['Prius', 'Toyota', 'Mar', 'Nowhere Town', '50'],
    ['Civic', 'Honda', 'Jan', 'Nowhere Town', '10'],
    ['Civic', 'Honda', 'Feb', 'Nowhere Town', '10'],
    ['Civic', 'Honda', 'Mar', 'Random Town', '10'],
    ['Civic', 'Honda', 'Mar', 'Random Town', '20'],
]

Expected output:

const output = [
    ['S', 'Camry', 'Toyota', 'Jan', '3050'],
    ['D', 1, 'Camry', 'Nowhere Town', '50'],
    ['D', 2, 'Camry', 'Random City', '3000'],
    ['S', 'Camry', 'Toyota', 'Feb', '70'],
    ['D', 1, 'Camry', 'Nowhere Town', '70'],
    ['S', 'Prius', 'Toyota', 'Jan', '120'],
    ['D', 1, 'Prius', 'Nowhere Town', '60'],
    ['D', 2, 'Prius', 'Random Town', '60'],
    ['S', 'Prius', 'Toyota', 'Mar', '50'],
    ['D', 1, 'Prius', 'Nowhere Town', '50'],
    ['S', 'Civic', 'Honda', 'Jan', '10'],
    ['D', 1, 'Civic', 'Nowhere Town', '10'],
    ['S', 'Civic', 'Honda', 'Feb', '10'],
    ['D', 1, 'Civic', 'Nowhere Town', '10'],
    ['S', 'Civic', 'Honda', 'Mar', '20'],
    ['D', 1, 'Civic', 'Random Town', '10'],
    ['D', 2, 'Civic', 'Random Town', '10'],
]

In words: If rows contain the same Brand, the same Make and the same Month add a Summary Row on top with total sales and add listed order for each details row.

I have an old code:

const groupReport = arr => {
    let grouped = [].concat(...arr.reduce((acc, cur) => {
        var data = acc.get(cur[1]) || [['P', cur[1], '0']]
        data.push(['D', data.length, cur[0], cur[3], cur[4]])
        data[0][2] = (+data[0][2] + +cur[4]).toString()
        return acc.set(cur[0], data);
    }, new Map)
    .values()
    )
    return grouped
}

It doesn't work since it only compares one col (Brand), not Make and Month.

I would strongly suggest changing this to an array of objects (with a structure like, e.g., `{"Model": "Camry", "Make": "Toyota", "Month": "Jan", "Price?": 3050 }` Note I also changed the final value there to a number, since it appears you want to do math on them. Then you can use the technique outlined in https://stackoverflow.com/questions/37481539/sum-of-array-object-property-values-in-new-array-of-objects-in-javascript to do what you're looking for. — Heretic Monkey, Jun 13 '18 at 20:21
Thank you for your suggestion, the answer of that question does add the sum of each row butt it doesn't add the row on top. Do you know how to do that also? — Viet, Jun 13 '18 at 20:27
Combine with [Group array of object nesting some of the keys with specific names](https://stackoverflow.com/q/48425797) — Heretic Monkey, Jun 13 '18 at 20:37

score 1 · Accepted Answer · answered Jun 13 '18 at 20:29

You could use a combined key and a destructed array.

var array = [['Camry', 'Toyota', 'Jan', 'Nowhere Town', '50'], ['Camry', 'Toyota', 'Feb', 'Nowhere Town', '70'], ['Camry', 'Toyota', 'Jan', 'Random City', '3000'], ['Prius', 'Toyota', 'Jan', 'Nowhere Town', '60'], ['Prius', 'Toyota', 'Jan', 'Random Town', '60'], ['Prius', 'Toyota', 'Mar', 'Nowhere Town', '50'], ['Civic', 'Honda', 'Jan', 'Nowhere Town', '10'], ['Civic', 'Honda', 'Feb', 'Nowhere Town', '10'], ['Civic', 'Honda', 'Mar', 'Random Town', '10'], ['Civic', 'Honda', 'Mar', 'Random Town', '20']],
    result = [].concat(...array
        .reduce((m, [brand, make, month, town, amount]) => {
            var key = [brand, make, month].join('|'),
                data = m.get(key) || [['S', brand, make, month, '0']];

            data.push(['D', data.length, brand, town, amount]);
            data[0][4] = (+data[0][4] + +amount).toString();
            return m.set(key, data);
        }, new Map)
        .values()
    );

console.log(result.map(a => a.join(' ')));

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

Thanks, @Nina Scholz! You saved me again! May I connect with you on LinkedIn or Twitter? — Viet, Jun 15 '18 at 16:23

score 1 · Answer 2 · answered Jun 13 '18 at 20:37

You can use reduce to create object by brand, make and month and then use spread syntax and Object.values to get array of arrays.

const input = [['Camry', 'Toyota', 'Jan', 'Nowhere Town', '50'],['Camry', 'Toyota', 'Feb', 'Nowhere Town', '70'],['Camry', 'Toyota', 'Jan', 'Random City', '3000'],['Prius', 'Toyota', 'Jan', 'Nowhere Town', '60'],['Prius', 'Toyota', 'Jan', 'Random Town', '60'],['Prius', 'Toyota', 'Mar', 'Nowhere Town', '50'],['Civic', 'Honda', 'Jan', 'Nowhere Town', '10'],['Civic', 'Honda', 'Feb', 'Nowhere Town', '10'],['Civic', 'Honda', 'Mar', 'Random Town', '10'],['Civic', 'Honda', 'Mar', 'Random Town', '20'],]

const obj = input.reduce((r, [brand, make, month, city, value]) => {
  let key = `${brand}|${make}|${month}`;
  if(!r[key]) r[key] = [
    ['S', brand,  make, month, +value],
    ["D", 1, make, city, value]
  ]
  else {
    r[key][0][4] += +value;
    let prev = r[key].slice(-1)[0]
    r[key].push(["D",  prev[1] + 1, make, city, value]);
  }
  return r;
}, {})

const result = [].concat(...Object.values(obj))

console.log(result)

Thanks, @Nenad Vracar! May I connect with you on LinkedIn or Twitter? — Viet, Jun 15 '18 at 16:23

Daniel Gimenez · Answer 3 · 2018-06-14T14:20:17.333

The following breaks does the input in two steps. It heavily utilizes es6 features Map and the spread operator.

The first step converts the items into groups by generating a key value pair and then adding each item to a Map of arrays of items keyed by the generated key.

The second step takes the values from each group to create a header, and them creates a new array with the header as the first item. The groups are then combined into a single array.

const input = [['Camry', 'Toyota', 'Jan', 'Nowhere Town', '50'], ['Camry', 'Toyota', 'Feb', 'Nowhere Town', '70'], ['Camry', 'Toyota', 'Jan', 'Random City', '3000'], ['Prius', 'Toyota', 'Jan', 'Nowhere Town', '60'], ['Prius', 'Toyota', 'Jan', 'Random Town', '60'],    ['Prius', 'Toyota', 'Mar', 'Nowhere Town', '50'],    ['Civic', 'Honda', 'Jan', 'Nowhere Town', '10'], ['Civic', 'Honda', 'Feb', 'Nowhere Town', '10'], ['Civic', 'Honda', 'Mar', 'Random Town', '10'], ['Civic', 'Honda', 'Mar', 'Random Town', '20']]

const group = new Map();
input.map(x => [ x.slice(0,3).join('::'), ['D', ...x.slice(0,4), +x[4]] ])
  .forEach(([key, val]) => (group.get(key) || group.set(key, []).get(key)).push(val));

const result = [].concat(... Array.from(group.values())
  .map(x => [['S', ...x[0].slice(1,4), x.reduce((a, c) => a + c[5], 0)], 
   ...x.map(y => [...y.slice(0, 3), ... y.slice(4,6)])]));

console.log(result.map(x => x.toString()));

Add new summary row for cols share the same vales of 2 different cols

3 Answers3

Linked