将javascript数组从宽格式调整为长格式

Question

Tableau WDC连接器中的Javascript数组:

[{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}]

让我们假设这是javascript数组的输出，我通过WDC连接器在Tableau Desktop中得到:

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不允许透视表，如果数据源是WDC，所以我需要帮助如何编写/升级javascript代码(在我的WDC代码)，以获得这样的输出:

输出数组:

[{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}, {...}]

并在Tableau Desktop中获得如下输出:

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

在python中，这是一个名为melt的简单函数，但由于我是javascript的新手，我不知道如何执行此任务/编写正确的代码。

谢谢你的建议和帮助。

亲切的问候

Answer 1

我将使用flatMap方法:

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);

结果:

[
{ 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性能优化

使用flatMap、filter和map生成大量中间数组。这种解决方案性能更高，并且避免了无用的分配:

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);

Answer 2

您可以使用flatMap,filter&Object.keys()像下面一样，得到你想要的输出。

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))