萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 腳本程式專題 >> JavaScript

JavaScript 實現 JSON 資料行轉列(透視表)完整函數與測試

前言

後端回傳的 JSON 通常為「長格式」(每筆一列),前端圖表庫(如 ECharts、Highcharts)常需「寬格式」(每欄一個序列)。本文提供通用的 CovertData 函數,將依據 idField 分組,將 fromField 指定的多個數值欄位,依 toField 的值轉為多欄位(如 公司-指標)。

核心函數

/**
 * JSON 行轉列(透視)
 * @param {Array} jsonData  原始資料陣列
 * @param {string} idField  分組鍵(如 'yearmonth')
 * @param {string} fromField 要轉為欄位的數值欄,逗號分隔(如 'ppq,spq')
 * @param {string} toField   欄位名稱來源欄位(如 'company')
 * @returns {Array} 轉換後陣列,每物件包含 idField 及動態欄位
 */
function CovertData(jsonData, idField, fromField, toField) {
    const result = [];
    const fromFields = fromField.split(',');

    // 輔助:尋找 result 中 idField 等於 value 的索引
    const findIdx = (arr, col, val) => arr.findIndex(o => o[col] === val);

    for (const row of jsonData) {
        const idx = findIdx(result, idField, row[idField]);
        const curRecord = idx !== -1 ? result[idx] : {};

        for (const key in row) {
            // 處理數值欄位
            for (const f of fromFields) {
                if (key === f) {
                    curRecord[`${row[toField]}-${f}`] = row[key];
                    break;
                }
            }
            // 保留分組鍵
            if (key === idField) curRecord[key] = row[key];
        }

        if (idx === -1) result.push(curRecord);
    }
    return result;
}

測試代碼(JsTestDriver 風格)

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'}
        ];
        this.fromField = 'ppq,spq';
        this.toField   = 'company';
        this.idField   = 'yearmonth';
        this.resultData = CovertData(this.jsonData, this.idField, this.fromField, this.toField);
    },

    "test store has columns": function() {
        const find = (arr, col, val) => arr.findIndex(o => o[col] === val);
        const m1 = this.resultData[find(this.resultData, 'yearmonth', 201201)];
        const m2 = this.resultData[find(this.resultData, 'yearmonth', 201202)];
        const m3 = this.resultData[find(this.resultData, 'yearmonth', 201203)];
        const m4 = this.resultData[find(this.resultData, 'yearmonth', 201204)];

        assertEquals(4, this.resultData.length);
        assertEquals('23', m1['dfsoft-ppq']); assertEquals('29', m1['vcom-ppq']);
        assertEquals('33', m2['dfsoft-ppq']); assertEquals('34', m2['vcom-ppq']);
        assertEquals('43', m3['dfsoft-ppq']); assertEquals('48', m3['vcom-ppq']);
        assertEquals('53', m4['dfsoft-ppq']); assertEquals('52', m4['vcom-ppq']);

        assertEquals('27', m1['dfsoft-spq']); assertEquals('26', m1['vcom-spq']);
        assertEquals('38', m2['dfsoft-spq']); assertEquals('38', m2['vcom-spq']);
        assertEquals('49', m3['dfsoft-spq']); assertEquals('43', m3['vcom-spq']);
        assertEquals('51', m4['dfsoft-spq']); assertEquals('59', m4['vcom-spq']);
    }
});

輸出結果範例

[
  { yearmonth: 201201, 'dfsoft-ppq': 23, 'dfsoft-spq': 27, 'vcom-ppq': 29, 'vcom-spq': 26 },
  { yearmonth: 201202, 'dfsoft-ppq': 33, 'dfsoft-spq': 38, 'vcom-ppq': 34, 'vcom-spq': 38 },
  { yearmonth: 201203, 'dfsoft-ppq': 43, 'dfsoft-spq': 49, 'vcom-ppq': 48, 'vcom-spq': 43 },
  { yearmonth: 201204, 'dfsoft-ppq': 53, 'dfsoft-spq': 51, 'vcom-ppq': 52, 'vcom-spq': 59 }
]

應用場景

  • 圖表多序列資料準備(如各城市每月用電量)。
  • 表格匯出 Excel 時的樞紐分析預處理。
  • 前端資料重組,減少後端 API 複雜度。
copyright © 萬盛學電腦網 all rights reserved