Javascript: transpose json data

Question

I have following json data and want to transpose with the revenue as the value of individual years:

Current data:

[{"year": 2017, "month": "Jan", "revenue": 2000},
{"year": 2017, "month": "Feb", "revenue": 3000},
{"year": 2017, "month": "Mar", "revenue": 1000},
{"year": 2016, "month": "Jan", "revenue": 5000}, 
{"year": 2016, "month": "Feb", "revenue": 4000}, 
{"year": 2016, "month": "Mar" "revenue": 2000}]

Expected output:

[{Month: "Jan", "2017": 2000, "2016": 5000},
{Month: "Feb", "2017": 3000, "2016": 4000},
{Month: "Mar", "2017": 1000, "2016": 2000}]

I've been trying different ways but no success. Also in future I might have months different than Jan-Mar so any solution which isn't restricted to three months.

_I've been trying different ways but no success_ you should post that code too — Muhammad Usman, May 04 '18 at 22:13
Please add the way you tried that got you the closest to what you want as a [mcve] — Luca Kiebel, May 04 '18 at 22:13
This may help you get started: [What is the most efficient method to groupby on a JavaScript array of objects?](https://stackoverflow.com/questions/14446511/what-is-the-most-efficient-method-to-groupby-on-a-javascript-array-of-objects) — Jonathan Lonowski, May 04 '18 at 22:16

Mark · Accepted Answer · 2018-05-04T22:28:26.837

A basic plan is to make a new object keyed to the month and add each year as you go through. recduce() makes it pretty concise:

let dates = [{"year": 2017, "month": "Jan", "revenue": 2000}, {"year": 2017, "month": "Feb", "revenue": 3000}, {"year": 2017, "month": "Mar", "revenue": 1000}, {"year": 2016, "month": "Jan", "revenue": 5000},  {"year": 2016, "month": "Feb", "revenue": 4000}, {"year": 2016, "month": "Mar", "revenue": 2000}]

// You only want an array of values, so just return 
// Object.values()
let d = Object.values(dates.reduce((a, c) =>{
    // either use the existing entry or create a new one
    // with month already set
    (a[c.month] || (a[c.month] = {Month: c.month}))[c.year] = c.revenue
    return a
}, {}))

console.log(d)

:

Thanx a lot. Apart from accepting it as accepted answer, I wish I could also give u a hug :) — Hannan, May 04 '18 at 23:05

Orelsanpls · Answer 2 · 2018-05-04T22:32:03.860

You can use ES6 syntax Array.reduce to build up your array using the data you have.

const values = [{
    year: 2017,
    month: "Jan",
    revenue: 2000,
  },
  {
    year: 2017,
    month: "Feb",
    revenue: 3000,
  },
  {
    year: 2017,
    month: "Mar",
    revenue: 1000,
  },
  {
    year: 2016,
    month: "Jan",
    revenue: 5000,
  },
  {
    year: 2016,
    month: "Feb",
    revenue: 4000,
  },
  {
    year: 2016,
    month: "Mar",
    revenue: 2000,
  }
];

// Use destructuration to access each data
const transformedValues = values.reduce((tmp, {
  month,
  year,
  revenue,
}) => {
  // Look for the month in tmp
  const monthInTmp = tmp.find(y => y.Month === month);

  // If it do not exist, push it
  if (!monthInTmp) {
    tmp.push({
      Month: month,

      // use the following syntax [var] to use a dynamical key
      [year]: revenue,
    });

    return tmp;
  }

  // if it exists update it
  // handle the case when we have two revenue in same year
  if (monthInTmp[year]) {
    monthInTmp[year] += revenue;
  
    return tmp;
  }
  
  monthInTmp[year] = revenue;

  return tmp;
}, []);

console.log(transformedValues );

Javascript: transpose json data

2 Answers2