圖表元件通常要「一欄一個序列」,但後端回傳的往往是「一列一筆紀錄」。本文示範如何把兩家公司、四個月份的長格式 JSON,轉成月份為列、公司為欄的寬格式,讓 Ext 圖表可以直接吃。
月份 鄭州-電量 新鄉-電量 安陽-電量
201201 33 29 23
201202 35 26 25
201203 34 27 24
201204 36 28 26
201205 34.3 28.8 24.3
function CovertData(jsonData, idField, fromField, toField){
var result = [], curRecord = null, num;
var fromFields = fromField.split(',');
for(var idx=0; idx<jsonData.length; idx++){
num = findIdx(result, idField, jsonData[idx][idField]);
if(num != -1){
curRecord = result[num];
}
else{
curRecord = {};
};
for(var key in jsonData[idx]){
for(var i=0; i<fromFields.length; i++){
if(key == fromFields[i]){
curRecord[jsonData[idx][toField]+'-' + fromFields[i]] = jsonData[idx][key];
break;
}
}
if(key == idField){
curRecord[key] = jsonData[idx][key];
}
}
if(num == -1){
result.push(curRecord);
}
}
return result;
}
function findIdx(jsonData, columnName, value){
for(var idx = 0; idx<jsonData.length; idx++){
if(jsonData[idx][columnName] == value)
return idx;
}
return -1;
}
idField 是作為列索引的欄位(月份),fromField 是要轉成欄的數值欄位清單(用逗號分隔),toField 則是拿來當欄名標籤的欄位(公司)。核心邏輯是:遇到相同 idField 就併入同一筆,並以「toField值-fromField值」組出新的 key。
TestCase("Test json data row to column",{
setUp:function(){
this.jsonData = [{yearmonth:201201,ppq:23,spq:27,company:'dfsoft'},
{yearmonth:201202,ppq:33,spq:38,company:'dfsoft'},
{yearmonth:201203,ppq:43,spq:49,company:'dfsoft'},
{yearmonth:201204,ppq:53,spq:51,company:'dfsoft'},
{yearmonth:201201,ppq:29,spq:26,company:'vcom'},
{yearmonth:201202,ppq:34,spq:38,company:'vcom'},
{yearmonth:201203,ppq:48,spq:43,company:'vcom'},
{yearmonth:201204,ppq:52,spq:59,company:'vcom'}];
var fromField = 'ppq,spq', toField = 'company', idField = 'yearmonth';
this.resultData = CovertData(this.jsonData,idField,fromField, toField);
},
"test store has columns":function(){
var m1 = this.resultData[findIdx(this.resultData,'yearmonth',201201)];
assertEquals(4,this.resultData.length);
assertEquals('23',m1['dfsoft-ppq']);
assertEquals('29',m1['vcom-ppq']);
assertEquals('27',m1['dfsoft-spq']);
assertEquals('26',m1['vcom-spq']);
}
})
findIdx 每次都要線性掃過整個陣列,資料量大時是 O(n²)。用 Map 建立索引可降到 O(n),再用 reduce 收斂:
function pivot(rows, idField, valueFields, labelField) {
const map = new Map();
for (const r of rows) {
if (!map.has(r[idField])) map.set(r[idField], { [idField]: r[idField] });
const row = map.get(r[idField]);
for (const f of valueFields) row[`${r[labelField]}-${f}`] = r[f];
}
return [...map.values()];
}
程式碼短了一半,效能也更好。另外,多數圖表庫(含 ECharts、Chart.js)本身支援直接傳入長格式資料並指定 series 欄位,先確認一下元件能力,往往不必自己轉。