Reshaping javascript array from wide to long format

Question

Javascript array inside Tableau WDC connector:

[{Code: "AFG", Country: "Afghanistan", 1950: 20.249, 1951: 21.352, 1952: 22.532, 1953: 23.557, 1954: 24.555}, {Code: "ALB", Country: "Albania", 1950: 8.097, 1951: 8.986, 1952: 10.058, 1953: 11.123, 1954: 12.246}]

Lets asume that this is the output of javascript array which I get inside Tableau Desktop over WDC connector:

Code Country        1950    1951    1952    1953    1954
AFG  Afghanistan    20,249  21,352  22,532  23,557  24,555
ALB  Albania        8,097   8,986   10,058  11,123  12,246

Tableau doesnt allow pivoting table if datasource is WDC, so I need help how to writte/upgrade javascript code (inside my wdc code) to get output like this:

output array:

[{Code: "AFG", Country: "Afghanistan", Year: 1950, Value: 20.249}, {Code: "AFG", Country: "Afghanistan", Year: 1951, Value: 21.352}, {...}, {Code: "ALB", Country: "Albania", Year: 1950, Value: 8.097}, {Code: "ALB", Country: "Albania", Year: 1951, Value: 8.986}, {...}]

and to get output in Tableau Desktop like this:

Code Country        Year    Value
AFG  Afghanistan    1950    20,249
AFG  Afghanistan    1951    21,352
AFG  Afghanistan    1952    22,532
AFG  Afghanistan    1953    23,557
AFG  Afghanistan    1954    24,555
ALB  Albania        1950    8,097
ALB  Albania        1951    8,986
ALB  Albania        1952    10,058
ALB  Albania        1953    11,123
ALB  Albania        1954    12,246

In python, that is simple function called melt, but as I'm new into javascript I dont know how to perform this task/write correct code.

Thanks for advice and help.

Kind regards

Unfortunately, JavaScript does not provide something like python pandas package, so there is no direct equivalent of melt function. You may need to write the processing code yourself :/ — Nikolay, Dec 16 '22 at 15:08

theemee · Accepted Answer · 2022-12-19T13:21:08.700

I would use the flatMap method:

const array = [{Code: "AFG", Country: "Afghanistan", 1950: 20.249, 1951: 21.352, 1952: 22.532, 1953: 23.557, 1954: 24.555}, {Code: "ALB", Country: "Albania", 1950: 8.097, 1951: 8.986, 1952: 10.058, 1953: 11.123, 1954: 12.246}];

const long = array.flatMap(c => // flatMap maps each value to an array and then flattens it to single array
  Object.keys(c)                // get the keys of each object
    .filter(k => parseInt(k))   // filter the keys that are numbers so only 1950, 1951 and others remain
    .map(year => ({             // map each year to separate object         
      Code: c.Code,
      Country: c.Country,
      Year: parseInt(year),
      Value: c[year]
    }))
);

console.log(long);

The result:

[
  { Code: 'AFG', Country: 'Afghanistan', Year: 1950, Value: 20.249 },
  { Code: 'AFG', Country: 'Afghanistan', Year: 1951, Value: 21.352 },
  { Code: 'AFG', Country: 'Afghanistan', Year: 1952, Value: 22.532 },
  { Code: 'AFG', Country: 'Afghanistan', Year: 1953, Value: 23.557 },
  { Code: 'AFG', Country: 'Afghanistan', Year: 1954, Value: 24.555 },
  { Code: 'ALB', Country: 'Albania', Year: 1950, Value: 8.097 },
  { Code: 'ALB', Country: 'Albania', Year: 1951, Value: 8.986 },
  { Code: 'ALB', Country: 'Albania', Year: 1952, Value: 10.058 },
  { Code: 'ALB', Country: 'Albania', Year: 1953, Value: 11.123 },
  { Code: 'ALB', Country: 'Albania', Year: 1954, Value: 12.246 }
]

UPDATE performance optimization

Using flatMap, filter and map produces a lot of intermediate arrays. This solution is much more performant and avoids useless alocations:

const array = [{Code: "AFG", Country: "Afghanistan", 1950: 20.249, 1951: 21.352, 1952: 22.532, 1953: 23.557, 1954: 24.555}, {Code: "ALB", Country: "Albania", 1950: 8.097, 1951: 8.986, 1952: 10.058, 1953: 11.123, 1954: 12.246}];

const long = [];

for (const c of array) {
  for (key in c) {
    const year = parseInt(key);
    if (year) {
      long.push({
        Code: c.Code,
        Country: c.Country,
        Year: year,
        Value: c[key]
      });
    }
  }
}

console.log(long);

dear @theemee I found your solution very usefull for my project, so I want to thank you for your time and knowledge :-) — Coding Gene, Dec 19 '22 at 12:09
@CodingGene I should probably say, that this is not the most performant solution because it creates a lot of intermediate arrays and if performance becomes an issue, I would use nested `for` loops instead. Tell me if you want me to include the other version — theemee, Dec 19 '22 at 12:18

Karan · Answer 2 · 2022-12-19T11:58:08.743

You can use combinations of flatMap, filter & Object.keys() like below and get your desired output.

let arr = [{Code: "AFG", Country: "Afghanistan", Year: 1950, Value: 20.249}, {Code: "AFG", Country: "Afghanistan", Year: 1951, Value: 21.352}, {Code: "ALB", Country: "Albania", Year: 1950, Value: 8.097}, {Code: "ALB", Country: "Albania", Year: 1951, Value: 8.986}];

// Function to convert array in desired format
function convertArray(arr) {
  // use flatMap to flatten the resultant array
  return arr.flatMap(x => {
    // Loop over keys from the object using Object.keys(x)
    // Ignore Code & Country keys using filter(k => k != "Code" && k != "Country")
    // Use .map() to format response array    
    return Object.keys(x)
      .filter(k => k != "Code" && k != "Country")
      .map(k => ({
        Code: x.Code, // set Code value
        Country: x.Country, // set Country value
        Year: +k, // set key value using +k so that it will convert key to number
        Value: +x[k] // set Value using +x[k] to it will converted to number
      }));
  });
}

// For testing write output in console
console.log(convertArray(arr))

Your code produces the wrong output: you need to use `k != "Code" && k != "Country"` instead of `||`. Also you don't transform the year key from string to number which will produce `Year: "1950"` rather than `Year: 1950`. And you used the wrong array in the snippet — theemee, Dec 19 '22 at 11:54
@theemee, Thanks for pointing that out mistakes. Corrected the code. I really appreciate your comment. — Karan, Dec 19 '22 at 11:59
dear @Karan thank you also for sharing your time and knowldge :-) Respect for both of you :-) — Coding Gene, Dec 19 '22 at 12:10

Reshaping javascript array from wide to long format

2 Answers2