Can I double reduce?

Question

PROBLEM: I have a dataset in CSV format that has some key metrics I need to manipulate. I have regions, dates and sales volumes. I want to combine the similar regions together. I also want to combine the similar dates and add the sales volume per region per combined date.

SOLUTION:

const tableNoHeader = table
         .split('\n')
         .map(line => line.split(','))
         .slice(1)
         .reduce((region, row) => {
            region[row[10]] = region[row[10]] || [];
            region[row[10]].push({
              weekOf: row[5],
              sales : parseFloat(row[8]) >= 0 
                         && parseFloat(row[9]) >= 0 
                            ? parseFloat(row[8]) + parseFloat(row[9]) 
                            : ""
              });
              return region;
           }, {});

I’ve successfully used reduce to combine all the duplicate regions together but now within each region I have duplicate dates and I want to have those reduced(?) as well.

Not sure if that’s possible or how to do this effectively...

Here is some sample data from the CSV:

First Name, Last Name, Full Name, Key, Role, Date, Unit Type 1, Unit Type 2, Units Sales 1, Units Sales 2, Region, Position, Hire
John, Doe, John Doe, JohnDoe, Sales, 22-Feb-21, Commercial, Commercial, 12, 2, West, Associate, 2017
Jane, Doe, Jane Doe, JaneDoe, Sales, 22-Feb-21, Commercial, Commercial, 7, 22, Central, Associate, 2019
Jane, Doe, Jane Doe, JaneDoe, Sales, 01-Mar-21, Commercial, Commercial, 21, 1, Central, Associate, 2019
John, Doe, John Doe, JohnDoe, Sales, 01-Mar-21, Commercial, Commercial, 4, 8, Central, Associate, 2012
Jake, Doe, Jake Doe, JakeDoe, Sales, 22-Feb-21, Commercial, Commercial, 2, 2, West, Associate, 2019

Any feedback is appreciated!

Answer 1

So, as Kaiido reminded me of the idea of placing a second reduce after the first, here is a second double proposition:

the first One idea is about changing the result structure (anything is objects) and ther is no needs of a second reduce

//                0           1          2          3    4     5     6            7            8              9              10      11        12
const csvData = `First Name, Last Name, Full Name, Key, Role, Date, Unit Type 1, Unit Type 2, Units Sales 1, Units Sales 2, Region, Position, Hire
John, Doe, John Doe, JohnDoe, Sales, 22-Feb-21, Commercial, Commercial, 12, 2, West, Associate, 2017
Jane, Doe, Jane Doe, JaneDoe, Sales, 22-Feb-21, Commercial, Commercial, 7, 22, Central, Associate, 2019
Jane, Doe, Jane Doe, JaneDoe, Sales, 01-Mar-21, Commercial, Commercial, 21, 1, Central, Associate, 2019
John, Doe, John Doe, JohnDoe, Sales, 01-Mar-21, Commercial, Commercial, 4, 8, Central, Associate, 2012
Jake, Doe, Jake Doe, JakeDoe, Sales, 22-Feb-21, Commercial, Commercial, 2, 2, West, Associate, 2019'`

const tableNoHeader = csvData
  .split('\n').slice(1) 
  .map(line =>
    {  //  0          1         2         3    4     5       6          7          8            9            10      11        12
    let [ FirstName, LastName, FullName, Key, Role, weekOf, UnitType1, UnitType2, UnitsSales1, UnitsSales2, region, Position, Hire]
        = line.split(/, |,/)
    return ({ region, weekOf, sales : (+UnitsSales1) + (+UnitsSales2) })
    })     
  .reduce((res,{region,weekOf,sales}) =>
    {
    res[region] = res[region] || {}
    res[region][weekOf] = res[region][weekOf] || 0
    res[region][weekOf] += sales
    return res
    }, {})
console.log(  tableNoHeader )

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

this second solution is the same, but with a second retuce to transform the previous result to the same structure of my first post, but have nothing to do with an idea of grouping values.

//                0           1          2          3    4     5     6            7            8              9              10      11        12
const csvData = `First Name, Last Name, Full Name, Key, Role, Date, Unit Type 1, Unit Type 2, Units Sales 1, Units Sales 2, Region, Position, Hire
John, Doe, John Doe, JohnDoe, Sales, 22-Feb-21, Commercial, Commercial, 12, 2, West, Associate, 2017
Jane, Doe, Jane Doe, JaneDoe, Sales, 22-Feb-21, Commercial, Commercial, 7, 22, Central, Associate, 2019
Jane, Doe, Jane Doe, JaneDoe, Sales, 01-Mar-21, Commercial, Commercial, 21, 1, Central, Associate, 2019
John, Doe, John Doe, JohnDoe, Sales, 01-Mar-21, Commercial, Commercial, 4, 8, Central, Associate, 2012
Jake, Doe, Jake Doe, JakeDoe, Sales, 22-Feb-21, Commercial, Commercial, 2, 2, West, Associate, 2019'`

const tableNoHeader = csvData
  .split('\n').slice(1) 
  .map(line =>
    {  //  0          1         2         3    4     5       6          7          8            9            10      11        12
    let [ FirstName, LastName, FullName, Key, Role, weekOf, UnitType1, UnitType2, UnitsSales1, UnitsSales2, region, Position, Hire]
        = line.split(/, |,/)
    return ({ region, weekOf, sales : (+UnitsSales1) + (+UnitsSales2) })
    })     
  .reduce((res,{region,weekOf,sales},i,{[i+1]:nxt}) =>
    {
    res[region] = res[region] || {}
    res[region][weekOf] = res[region][weekOf] || 0
    res[region][weekOf] += sales
    if (nxt) return res
    else return Object.keys(res).reduce((x,rg)=>
      {
      x[rg] = Object.entries(res[rg]).map(([k,v])=>({weekOf:k,sales:v}))
      return x
      } ,{})
    }, {})

console.log( tableNoHeader )

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

Answer 2

I think this is it ?

//                0           1          2          3    4     5     6            7            8              9              10      11        12
const csvData = `First Name, Last Name, Full Name, Key, Role, Date, Unit Type 1, Unit Type 2, Units Sales 1, Units Sales 2, Region, Position, Hire
John, Doe, John Doe, JohnDoe, Sales, 22-Feb-21, Commercial, Commercial, 12, 2, West, Associate, 2017
Jane, Doe, Jane Doe, JaneDoe, Sales, 22-Feb-21, Commercial, Commercial, 7, 22, Central, Associate, 2019
Jane, Doe, Jane Doe, JaneDoe, Sales, 01-Mar-21, Commercial, Commercial, 21, 1, Central, Associate, 2019
John, Doe, John Doe, JohnDoe, Sales, 01-Mar-21, Commercial, Commercial, 4, 8, Central, Associate, 2012
Jake, Doe, Jake Doe, JakeDoe, Sales, 22-Feb-21, Commercial, Commercial, 2, 2, West, Associate, 2019'`

const tableNoHeader = csvData
  .split('\n').slice(1) 
  .map(line =>
    {  //  0          1         2         3    4     5       6          7          8            9            10      11        12
    let [ FirstName, LastName, FullName, Key, Role, weekOf, UnitType1, UnitType2, UnitsSales1, UnitsSales2, region, Position, Hire]
        = line.split(/, |,/)
    return ({ region, weekOf, sales : (+UnitsSales1) + (+UnitsSales2) })
    })     
  .reduce((res,{region,weekOf,sales}) =>
    {
    res[region] = res[region] || []
    let regWeek = res[region].find(x=>x.weekOf === weekOf )
    if (!regWeek) res[region].push({ weekOf, sales })
    else          regWeek.sales += sales
    return res
    }, {})
console.log( tableNoHeader )

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

Answer 3

To add a cumulation per date: before your push, we need to check if row[5] is already listed as a weekOf in the region...

const tableNoHeader = table
          .split('\n')
          .map(line => line.split(','))
          .slice(1)
          .reduce((region, row) => {
            const region1 = region[row[10]] || [],
            sale1 = parseFloat(row[8]) >= 0 && parseFloat(row[9]) >= 0 ? parseFloat(row[8]) + parseFloat(row[9]) : "";

            for(var i = 0; i < region1.length; i++) {
              if region1[i].weekOf === row[5] {
                region1[i].sales += sale1; // make sure it's numeric if not ""
                region1[i].sales = region1[i].sales ? + region1[i].sales : "";
                break;
              }
            }
            if(i === region1.length) region1.push({
              weekOf: row[5],
              sales: sale1
            });
            region[row[10]] = region1;
            return region;
          }, {});