Calculating while converting (array of objects) columns to row data

Question

I have the following array:

var test =[
{Date:"2020-06-01", Type:"ZT", Qlt:"A",line:100,acc:1000,tom:10000},
{Date:"2020-06-01", Type:"ZT", Qlt:"B",line:200,acc:2000,tom:20000},
{Date:"2020-06-01", Type:"ZT", Qlt:"C",line:300,acc:3000,tom:30000},
{Date:"2020-06-02", Type:"ZT", Qlt:"A",line:400,acc:4000,tom:40000},
{Date:"2020-06-02", Type:"ZT", Qlt:"B",line:500,acc:5000,tom:50000},
{Date:"2020-06-02", Type:"ZT", Qlt:"C",line:600,acc:6000,tom:60000}
]

What i'm trying to achieve is this result:

var result=[
{Date:"2020-06-01", Type:"ZT", QltA:100, QltB:200, QltC:300, totalQlt:600, totalAcc:6000, totalTom:60000}, 
{Date:"2020-06-02", Type:"ZT", QltA:400, QltB:500, QltC:600, totalQlt:1500, totalAcc:15000, totalTom:150000},
]

Date must be unique,
Qlt's value is always (A or B or C) so it is converted as key name QltA, QltB and QltC
All the other (line, acc and tom) are the sum of corresponding Qlts.

i've tried the reverse approach of this post Convert rows to column from javascript array but i didn't managed to get it work, moreover i had troubles of calculating the sum.

I know that it is something tricky here, it will be great if you could provide a solution and a sort explanation in order to understand the logic?

Any help will be appreciated, thank you.

Please post what you have tried and where you're having trouble. — pilchard
– pilchard, Commented Jul 2, 2021 at 11:57

Majed Badawi · Accepted Answer · 2021-07-02 12:11:31Z

1

const data = [
  { Date:"2020-06-01", Type:"ZT", Qlt:"A", line:100, acc:1000, tom:10000 },
  { Date:"2020-06-01", Type:"ZT", Qlt:"B", line:200, acc:2000, tom:20000 },
  { Date:"2020-06-01", Type:"ZT", Qlt:"C", line:300, acc:3000, tom:30000 },
  { Date:"2020-06-02", Type:"ZT", Qlt:"A", line:400, acc:4000, tom:40000 },
  { Date:"2020-06-02", Type:"ZT", Qlt:"B", line:500, acc:5000, tom:50000 },
  { Date:"2020-06-02", Type:"ZT", Qlt:"C", line:600, acc:6000, tom:60000 }
];

const result = [...
  // iterate over the list while updating a Map of Date-Items
  data.reduce((map, { Date, Type, Qlt, line, acc, tom }) => {
    // get value of Date from map if exists
    const prev = map.get(Date) || {};
    // set/update map values
    map.set(
      Date,
      { 
        ...prev,
        Date,
        Type,
        [`Qlt${Qlt}`]: (prev.line || 0 ) + line,
        totalQlt: (prev.totalQlt || 0) + line,
        totalAcc: (prev.totalAcc || 0) + acc,
        totalTom: (prev.totalTom || 0) + tom
      }
    );
    return map;
  }, new Map)
  // return updated items of same dates
  .values()
];

console.log(result);

answered Jul 2, 2021 at 12:11

Majed Badawi

28.5k4 gold badges30 silver badges56 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

George Gotsidis Over a year ago

interesting approach with map.set(), by updating on every iteration, thank you!

George Gotsidis Over a year ago

do you have any ideas/approach on the above update?

pilchard · Accepted Answer · 2021-07-02 12:19:51Z

This is just a group by operation by the Date property. Here implemented with Array#reduce().

You can replace the nullish assignment (??=) with an OR short circuit if you need.

a[Date] ??= { Date, Type, ...};
// or
a[Date] || (a[Date] = { Date, Type, ...});

const
  input = [{ Date: "2020-06-01", Type: "ZT", Qlt: "A", line: 100, acc: 1000, tom: 10000 }, { Date: "2020-06-01", Type: "ZT", Qlt: "B", line: 200, acc: 2000, tom: 20000 }, { Date: "2020-06-01", Type: "ZT", Qlt: "C", line: 300, acc: 3000, tom: 30000 }, { Date: "2020-06-02", Type: "ZT", Qlt: "A", line: 400, acc: 4000, tom: 40000 }, { Date: "2020-06-02", Type: "ZT", Qlt: "B", line: 500, acc: 5000, tom: 50000 }, { Date: "2020-06-02", Type: "ZT", Qlt: "C", line: 600, acc: 6000, tom: 60000 }],

  result = Object
    .values(input
      .reduce((a, { Date, Type, Qlt, line, acc, tom }) => {
        a[Date] ??= { Date, Type, totalQlt: 0, totalAcc: 0, totalTom: 0 };
        
        a[Date][`Qlt${Qlt}`] = line;
        a[Date].totalQlt += line;
        a[Date].totalAcc += acc;
        a[Date].totalTom += tom;
        return a;
      }, {})
    );

console.log(result)

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

EliStackOverFlow · Accepted Answer · 2021-07-02 18:16:02Z

Create set of dates
Add for each date(element in the set) an object
Fill the object with values from the input using for loop

var test =[
  {date:"2020-06-01", Type:"ZT", Qlt:"A",line:100,acc:1000,tom:10000},
  {date:"2020-06-01", Type:"ZT", Qlt:"B",line:200,acc:2000,tom:20000},
  {date:"2020-06-01", Type:"ZT", Qlt:"C",line:300,acc:3000,tom:30000},
  {date:"2020-06-02", Type:"ZT", Qlt:"A",line:400,acc:4000,tom:40000},
  {date:"2020-06-02", Type:"ZT", Qlt:"B",line:500,acc:5000,tom:50000},
  {date:"2020-06-02", Type:"ZT", Qlt:"C",line:600,acc:6000,tom:60000}
]
  
 function resOBj(date,Type,QltA,QltB,QltC,totalQlt,totalAcc,totalTom){
  this.date=date, 
  this.Type=Type,
  this.QltA=QltA, 
  this.QltB=QltB, 
  this.QltC=QltC, 
  this.totalQlt=totalQlt, 
  this.totalAcc=totalAcc, 
  this.totalTom=totalTom
}


convertData=(test)=>{
  if (test===undefined){
    return test
  }
  let result=[]
  let setDates= new Set()
  test.forEach(t => {
    setDates.add(t.date)
  })

  for(const value of setDates){
    result.push(new resOBj(value,"",0,0,0,0,0,0))  
    for (let i=0;i<test.length;i++){
      if (test[i].date===value){
        result[result.length-1].Type = test[i].Type
        result[result.length-1].QltA = test[i].Qlt==="A"?test[i].line:result[result.length-1].QltA
        result[result.length-1].QltB = test[i].Qlt==="B"?test[i].line:result[result.length-1].QltB
        result[result.length-1].QltC = test[i].Qlt==="C"?test[i].line:result[result.length-1].QltC
        result[result.length-1].totalQlt += test[i].line
        result[result.length-1].totalAcc += test[i].acc
        result[result.length-1].totalTom += test[i].tom
      }
     }
  } 
  return result
}

console.log(convertData(test))

Collectives™ on Stack Overflow

Calculating while converting (array of objects) columns to row data

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related