Pivot/Transform json data columns to create new rows in javascript

Question

I have a json file in the following format:

const data = [
   {category: "A", country: "UK", name: "United Kingdom", country_id: "01", 2015: 5, 2016: 56, 2017: 10},
   {category: "B", country: "UK", name: "United Kingdom", country_id: "01", 2015: 4, 2016: 10, 2017: 10},
   {category: "C", country: "UK", name: "United Kingdom", country_id: "01", 2015: 10, 2016: 7, 2017: 45},
   {category: "A", country: "PO", name: "Poland", country_id: "02", 2015: 9, 2016: 14, 2017: 10},
   {category: "B", country: "PO", name: "Poland", country_id: "02", 2015: 10, 2016: 40, 2017: 0},
   {category: "C", country: "PO", name: "Poland", country_id: "02", 2015: 60, 2016: 30, 2017: 74},
   {category: "A", country: "CZ", name: "Czech Republic", country_id: "03", 2015: 30, 2016: 20, 2017: 10},
   {category: "B", country: "CZ", name: "Czech Republic", country_id: "03", 2015: 15, 2016: 28, 2017: 1},
   {category: "C", country: "CZ", name: "Czech Republic", country_id: "03", 2015: 16, 2016: 10, 2017: 2}
  ]

and I want to pivot the data to get the following format:

move 2015, 2016 and 2017 into a property named year
create a, b and c properties from category property values that will contain the different values.
have a line/object per country and year and any other ordinal categories I would like to keep.

const data = [
   {country: "UK", name: "United Kingdom", country_id: "01", year: "2015", "a": 5 , "b": 4, "c": 10},
   {country: "UK", name: "United Kingdom", country_id: "01", year: "2016", "a": 56 , "b": 10, "c": 7},
   {country: "UK", name: "United Kingdom", country_id: "01", year: "2017", "a": 10 , "b": 10, "c": 45},
   {country: "PO", name: "Poland", country_id: "02", year: "2015", "a": 9 , "b": 10, "c": 80},
   {country: "PO", name: "Poland", country_id: "02", year: "2016", "a": 14 , "b": 40, "c": 30},
   {country: "PO", name: "Poland", country_id: "02", year: "2017", "a": 10 , "b": 0, "c": 74},
   {country: "CZ", name: "Czech Republic", country_id: "03", year: "2015", "a": 30 , "b": 15, "c": 16},
   {country: "CZ", name: "Czech Republic", country_id: "03", year: "2016", "a": 20 , "b": 28, "c": 1},
   {country: "CZ", name: "Czech Republic", country_id: "03", year: "2017", "a": 10 , "b": 1, "c": 2}

I tried writing a for loop inside a map method but I am unable to create a, b and c properties.

ProfDFrancis · Accepted Answer · 2020-12-30 02:09:54Z

The rotation is done on the 3 lines commented 'Rotation'

To be able to do this we need to be able to access multiple rows of the original dataset. The strategy here builds us up to be able to do that.

Step 1. Get lists of the unique country_ids, years and categories

There are several ways to do this, and I have shown the easiest to understand method, which is to convert to a Set (which automatically removes duplicates) and then back to an Array for convenience of use.

Step 2. Move from a simple array, into an object

Instead of the rows being simply in sequence 0...8, we now have them in a 3x3 grid, addressible by country and category.

Step 3. Construct the desired output

Now within each country, we can extract all the data for a chosen year, by "plucking" the values for this year from the three different categories in the original data.

const data = [
   {category: "A", country: "UK", name: "United Kingdom", country_id: "01", 2015: 5, 2016: 56, 2017: 10},
   {category: "B", country: "UK", name: "United Kingdom", country_id: "01", 2015: 4, 2016: 10, 2017: 10},
   {category: "C", country: "UK", name: "United Kingdom", country_id: "01", 2015: 10, 2016: 7, 2017: 45},
   {category: "A", country: "PO", name: "Poland", country_id: "02", 2015: 9, 2016: 14, 2017: 10},
   {category: "B", country: "PO", name: "Poland", country_id: "02", 2015: 10, 2016: 40, 2017: 0},
   {category: "C", country: "PO", name: "Poland", country_id: "02", 2015: 60, 2016: 30, 2017: 74},
   {category: "A", country: "CZ", name: "Czech Republic", country_id: "03", 2015: 30, 2016: 20, 2017: 10},
   {category: "B", country: "CZ", name: "Czech Republic", country_id: "03", 2015: 15, 2016: 28, 2017: 1},
   {category: "C", country: "CZ", name: "Czech Republic", country_id: "03", 2015: 16, 2016: 10, 2017: 2}
  ]


// Step 1. Extract the unique country_id, category Ids and years

const country_ids = Array(...new Set(data.map((x) => x.country_id)));
const categories = Array(...new Set(data.map((x) => x.category)));
const years = ["2015","2016","2017"];

// Step 2. Convert the source data into an object so that you can conveniently read off particular rows, in terms of COUNTRY_ID and CATEGORY

const sourceRows = {};
data.forEach((row) => {
  if (!sourceRows[row.country_id]) {
    sourceRows[row.country_id] = {};
  }
  sourceRows[row.country_id][row.category] = row;
});

// You can visualise the output here with this, if you want:
//     console.log(sourceRows)


// Step 3. Create destination array, and poke a row into it for each country & year.

const destination = [];
country_ids.forEach((country_id) => {
  years.forEach((year) => {
    const sourceRow = sourceRows[country_id][categories[0]];
    const destRow = {
      country_id: country_id,
      name: sourceRow.name,
      country: sourceRow.country,
      year: year,
      a: sourceRows[country_id]["A"][year], // Rotation
      b: sourceRows[country_id]["B"][year], // Rotation
      c: sourceRows[country_id]["C"][year]  // Rotation
    };
    destination.push(destRow);
  });
});

console.log(destination);

aligumustosun · Accepted Answer · 2020-12-30 01:17:11Z

Not the best solution, but this works. At the and, I made a workaround for the duplicates. You could use ...rest parameter to initialize years array if there will be new data for other years.

let newData = [];
let countries = data.map(({country})=> country)
let categories = data.map(({category})=> category)
let years = [2015,2016,2017];

countries.forEach(country => {
    let countryData = data.filter(({country:c}) => c==country);
    let yearData = {2015:{},2016:{},2017:{}};
    years.forEach(year => {
        categories.forEach(category => {
            yearData[year][category] = countryData.find(({category:cat}) => cat==category)[year]
       })
   })
   let {name,country_id}= data.find(({country:c}) => c == country);

   Object.entries(yearData).forEach(([year,categories]) => {
        newData.push({country,name,country_id,year, ...categories})
   })
   newData = newData.filter((data,i) => i%9<3)
   console.log(newData)
})

Collectives™ on Stack Overflow

Pivot/Transform json data columns to create new rows in javascript

2 Answers 2

The rotation is done on the 3 lines commented 'Rotation'

Step 1. Get lists of the unique country_ids, years and categories

Step 2. Move from a simple array, into an object

Step 3. Construct the desired output

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

The rotation is done on the 3 lines commented 'Rotation'

Step 1. Get lists of the unique country_ids, years and categories

Step 2. Move from a simple array, into an object

Step 3. Construct the desired output

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related