Add new summary row for cols share the same vales of 2 different cols and handle time difference

Question

I have this 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', 'Camry1', 'Toyota', 'Feb', '70'],
    ['D', 1, 'Camry1', 'Nowhere Town', '70'],
    ['S', 'Prius', 'Toyota', 'Jan', '120'],
    ['D', 1, 'Prius', 'Nowhere Town', '60'],
    ['D', 2, 'Prius', 'Random Town', '60'],
    ['S', 'Prius1', 'Toyota', 'Mar', '50'],
    ['D', 1, 'Prius1', 'Nowhere Town', '50'],
    ['S', 'Civic', 'Honda', 'Jan', '10'],
    ['D', 1, 'Civic', 'Nowhere Town', '10'],
    ['S', 'Civic1', 'Honda', 'Feb', '10'],
    ['D', 1, 'Civic1', 'Nowhere Town', '10'],
    ['S', 'Civic2', 'Honda', 'Mar', '30'],
    ['D', 1, 'Civic2', 'Random Town', '10'],
    ['D', 2, 'Civic2', 'Random Town', '20'],
]

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.

Add an extra number string (1, 2, ...) behind the Make for both S and D rows if the Month is later than the first Month in the table.

This question is similar to my old question here but this one requires extra logic to handle the Month difference.

This is the old code I use:

const groupReport = arr => {
    const result = [].concat(...arr
        .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()
    )
    return result
}

The old code returns this result:

const oldOutput = [
    ['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', '30'],
    ['D', 1, 'Civic', 'Random Town', '10'],
    ['D', 2, 'Civic', 'Random Town', '20'],
]

How can I improve the old code to handle the new logic, or is there a new approach I can take?

Answer 1

You can aggregate a lookup hash and then loop through until you reach month data and generate required output (ideally recursively, but I did it iteratively):

var 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'],
]

var dataObj = input.reduce((all, [brand, make, month, city, count]) => {

    if (!all.hasOwnProperty(brand)) all[brand] = {};

    if (!all[brand].hasOwnProperty(make)) all[brand][make] = {};

    if (!all[brand][make].hasOwnProperty(month)) all[brand][make][month] = [];

    all[brand][make][month].push({city, count});

    return all;

}, {});


var result = Object.keys(dataObj).reduce((all, brand) => {

    Object.keys(dataObj[brand]).forEach(make => {

        Object.keys(dataObj[brand][make]).forEach((month, j) => {

            const id = j || '';

            dataObj[brand][make][month]
                .reduce((final, {city, count}, i) => {

                    final[0][4] += Number(count);
                    final.push(['D', i + 1, `${brand}${id}`, make, month, count]);
                    return final;

                },[['S', `${brand}${id}`, make, month, 0]])
                .forEach(item => all.push(item));

        });

    });

    return all;

}, []);

console.log(result);

Answer 2

Your pair of questions nicely shows how the "expected input -> output" kind of questions that return fancy oneliners from people that like code puzzles (I'm guilty of this myself) are often not that constructive... Whenever a requirement changes, it's hard to figure out what's going on!

---

I'll try to explain what you can do to create a program that will last:

Your two row types

Start out by defining what a header is, what a row is, and what information you need to construct them:

const header = (model, make, month, monthIndex, totalCost) =>
  ["D", model + (monthIndex || ""), make, month, totalCost + ""];

const row = (model, make, month, monthIndex, carIndex, cost) =>
  ["S", carIndex + 1, model + (monthIndex || ""), make, month, cost];

Grouping in to sections

Now, we need to group our cars by model, make and month to get to the right amount of headers and their required information. We'll use a groupBy utility that you can reuse throughout your code and is not specific to this program. The basics:

• Input:
  • A function that returns a string for an element: a -> string
  • A list of elements [a]
• Output:
  • An object with elements grouped by their string keys: { key: [a] }

Many libraries implement a groupBy, but you can also define one yourself.

A nested table

Now that we have a groupBy we can create a tree out of your rows, which looks like this:

const tableTree = { "Camry": { "Toyota": { "Jan": [ /* car rows */ ] } } };

You can write a converter for this data structure by reducing the object's entries, and it should become a bit more clear what the logic is and how to change it.

Flattening the nested table

You can loop over this tree like so:

Object.values(tableTree)
  .forEach(models => Object.values(models)
    .forEach(months => Object.values(months)
      .forEach(cars => { /* create rows */ }))
    )
  );

Running example:

Putting it all together:

const { map, groupBy, elAt, prop, sum } = utils();

// APP
// Properties:
const getModel = prop(0);
const getMake = prop(1);
const getMonth = prop(2);
const getCost = prop(4);

// Groupers:
const groupByModel = groupBy(getModel);
const groupByMake = groupBy(getMake);
const groupByMonth = groupBy(getMonth);


// Leveled grouper:
const makeTable = carInput => 
  map (map (groupByMonth)) 
      (map (groupByMake) 
           (groupByModel(carInput)));

// Get to the cars and make Sections
const flattenTable = table => {
  const rows = [];
  
  Object.values(table)
    .forEach(models => Object.values(models)
      .forEach(months => Object.values(months)
        .forEach((cars, mNr) => 
          rows.push(...Section(cars, mNr))
      )
    )
  );
          
  return rows;
};

// Row types (these will be so much nicer is you use objects...):
const Header = (model, make, month, totalCost, mNr) => 
  ["D", model + (mNr || ""), make, month, totalCost + ""];
  
const Row = mNr => (car, cNr) =>
  ["S", cNr + 1, getModel(car) + (mNr || ""), getMake(car), getMonth(car), getCost(car)];

const Section = (cars, mNr) => {
  const [c] = cars;
  const tCost = cars.map(getCost).reduce(sum);
  return [
    Header(getModel(c), getMake(c), getMonth(c), tCost, mNr),
    ...cars.map(Row(mNr))
  ];
};


// Test data:
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'],
];


// Run App:
console.log(
  flattenTable(makeTable(input))
    .map(row => `[${row.join(", ")}]`));


function utils() { 
  return {
    groupBy: getKey => xs => xs
      .map(x => [getKey(x), x])
      .reduce(
        (gs, [k, x]) => Object.assign(
          gs, 
          { [k]: (gs[k] || []).concat([x]) }
        ),
        {}
      ),
    map: f => obj => Object.entries(obj)
      .reduce(
        (o, [k, v]) => Object.assign(o, { [k]: f(v) }),
        {}
      ),
    prop: k => o => o[k],
    sum: (x, y) => +x + +y
  }
};