Json行列转换

cch1010 2017-06-27 12:19:23

[{NO:1,YearMonth:201706,Area:"上海",CustomerID:"100101" ,Serial:"KMC",Count:10},
{NO:2,YearMonth:201706,Area:"上海",CustomerID:"100101" ,Serial:"KMG",Count:2},
{NO:3,YearMonth:201706,Area:"上海",CustomerID:"100102" ,Serial:"KMG",Count:2},
{NO:4,YearMonth:201706,Area:"上海",CustomerID:"100102" ,Serial:"MKF",Count:5},
{NO:5,YearMonth:201706,Area:"北京",CustomerID:"100101" ,Serial:"MKF",Count:1},
{NO:6,YearMonth:201706,Area:"北京",CustomerID:"100101" ,Serial:"MAX",Count:4},
{NO:7,YearMonth:201706,Area:"北京",CustomerID:"100103" ,Serial:"XKM",Count:4},
{NO:8,YearMonth:201706,Area:"上海",CustomerID:"100101" ,Serial:"KMC",Count:2}
]
转换为
[{YearMonth:201706,Area:"上海",CustomerID:"100101" ,KMC:12 ,KMG:2,MKF:0,MAX:0,XKM:0},
{YearMonth:201706,Area:"上海",CustomerID:"100102" ,KMC:0,KMG:2,MKF:5,MAX:0,XKM:0},
{YearMonth:201706,Area:"北京",CustomerID:"100101" ,KMC:0,KMG:0,MKF:1,MAX:4,XKM:0},
{YearMonth:201706,Area:"北京",CustomerID:"100103" ,KMC:0,KMG:0,MKF:0,MAX:0,XKM:4}
]

注：YearMonth,Area,CustomerID分组，Serial对应各自数字合计

...全文

358 2 打赏收藏转发到动态举报

写回复

用AI写文章

2 条回复

切换为时间正序

请发表友善的回复…

发表回复

cch1010 2017-06-27

打赏
举报

/* @jsonData json源数据 * @idField 条件列，允许多个 * @fromField 转为行数据的数据，一般为数值列，并输出合计列sumAmount * @toField 转为列字段的数据，一般为类型 * */ function CovertData(jsonData,idField,fromField, toField){ var result = [], curRecord =null, num; var fromFields = fromField.split(','); var idFields = idField.split(','); // 循环整个数组：[{...},{...},{...},...] for(var idx=0;idx<jsonData.length;idx++){ var feature = {}; for(var keytemp in jsonData[idx]) { for (var iKey = 0; iKey < idFields.length; iKey++) { if(keytemp == idFields[iKey]) feature[keytemp] = jsonData[idx][keytemp] } } num = findIdx(result,feature,true); if(num!=-1){ curRecord = result[num]; } else{ curRecord = {}; }; //遍历整个数组将 toField 列插入curRecord，默认值为0 for(var index=0;index<jsonData.length;index++) { if(!curRecord[jsonData[index][toField]]) { curRecord[jsonData[index][toField]] = 0; } //按idField总计列 if(!curRecord["sumAmount"]){ curRecord["sumAmount"]=0; } } // 循环每个json对象中的字段 for(var key in jsonData[idx]){ // 处理转换的数据内容 for(var i=0;i<fromFields.length;i++){ if(key == fromFields[i]){ curRecord["sumAmount"] +=jsonData[idx][key]; if(!curRecord[jsonData[idx][toField]]){ curRecord[jsonData[idx][toField]] = jsonData[idx][key]; //break; } else{ curRecord[jsonData[idx][toField]] += jsonData[idx][key]; } } } // 除数据内容外，只处理标识字段数据 for(var j=0;j< idFields.length;j++) { if(key == idFields[j]){ curRecord[key] = jsonData[idx][key]; } } } if(num==-1){ result.push(curRecord); } } return result; } function findIdx(array, feature, allFlag) { for(var index in array){ var cur = array[index]; if(feature instanceof Object){ var allRight = true; for(var key in feature){ var value = feature[key]; if(cur[key] == value && !allFlag) { return index; } if(allFlag && cur[key] != value){ allRight = false; break; } } if(allRight) { return index; } }else{ if(cur == feature){ return index; } } } return -1; } 调用方法示例： var jsonData = [ { NO: 1, YearMonth: 201706, Area: '上海', CustomerID: '100101', Serial: 'KMC', Count: 10 }, { NO: 2, YearMonth: 201706, Area: '上海', CustomerID: '100101', Serial: 'KMG', Count: 2 }, { NO: 3, YearMonth: 201706, Area: '上海', CustomerID: '100102', Serial: 'KMG', Count: 2 }, { NO: 4, YearMonth: 201706, Area: '上海', CustomerID: '100102', Serial: 'MKF', Count: 5 }, { NO: 5, YearMonth: 201706, Area: '北京', CustomerID: '100101', Serial: 'MKF', Count: 1 }, { NO: 6, YearMonth: 201706, Area: '北京', CustomerID: '100101', Serial: 'MAX', Count: 4 }, { NO: 7, YearMonth: 201706, Area: '北京', CustomerID: '100103', Serial: 'XKM', Count: 4 }, { NO: 8, YearMonth: 201706, Area: '上海', CustomerID: '100101', Serial: 'KMC', Count: 2 } ]; var fromField = 'Count', toField = 'Serial', idField = 'YearMonth,Area,CustomerID'; var resultData = CovertData(jsonData,idField,fromField, toField); console.log(result); 可以得到包含合计的结果： [{YearMonth:201706,Area:"上海",CustomerID:"100101" ,KMC:12 ,KMG:2,MKF:0,MAX:0,XKM:0,sumAmount:14}, {YearMonth:201706,Area:"上海",CustomerID:"100102" ,KMC:0,KMG:2,MKF:5,MAX:0,XKM:0,sumAmount:7}, {YearMonth:201706,Area:"北京",CustomerID:"100101" ,KMC:0,KMG:0,MKF:1,MAX:4,XKM:0,sumAmount:5}, {YearMonth:201706,Area:"北京",CustomerID:"100103" ,KMC:0,KMG:0,MKF:0,MAX:0,XKM:4,sumAmount:4} ] 如果是数据量很大的情况，效率比较低，求优化

jio可 2017-06-27

打赏
举报


var data = [
  {
    NO: 1,
    YearMonth: 201706,
    Area: '上海',
    CustomerID: '100101',
    Serial: 'KMC',
    Count: 10
  },
  {
    NO: 2,
    YearMonth: 201706,
    Area: '上海',
    CustomerID: '100101',
    Serial: 'KMG',
    Count: 2
  },
  {
    NO: 3,
    YearMonth: 201706,
    Area: '上海',
    CustomerID: '100102',
    Serial: 'KMG',
    Count: 2
  },
  {
    NO: 4,
    YearMonth: 201706,
    Area: '上海',
    CustomerID: '100102',
    Serial: 'MKF',
    Count: 5
  },
  {
    NO: 5,
    YearMonth: 201706,
    Area: '北京',
    CustomerID: '100101',
    Serial: 'MKF',
    Count: 1
  },
  {
    NO: 6,
    YearMonth: 201706,
    Area: '北京',
    CustomerID: '100101',
    Serial: 'MAX',
    Count: 4
  },
  {
    NO: 7,
    YearMonth: 201706,
    Area: '北京',
    CustomerID: '100103',
    Serial: 'XKM',
    Count: 4
  },
  {
    NO: 8,
    YearMonth: 201706,
    Area: '上海',
    CustomerID: '100101',
    Serial: 'KMC',
    Count: 2
  }
];
var area = {};//按地区存储
data.forEach(function (item) {
  if (!area[item.Area]) {
    area[item.Area] = [];
  }
  area[item.Area].push(item);
});
var o = {};//按消费者存储
for (var p in area) {
  area[p].forEach(function (item) {
    if(!o[item.CustomerID+"_"+item.Area]){
      o[item.CustomerID+"_"+item.Area] = [];
    }
	o[item.CustomerID+"_"+item.Area].push(item);
  });
}
//console.log(o)
var result = [];//最终结果
for(var k in o){
  var a = {KMC:0,KMG:0,MKF:0,MAX:0,XKM:0};
  o[k].forEach(function(ob){
      if(!a[ob.Serial]){
        a[ob.Serial] = ob.Count;
      }else{
        a[ob.Serial] += ob.Count;
      }
      a.YearMonth = ob.YearMonth;
      a.Area = ob.Area;
      a.CustomerID = ob.CustomerID;
  });
  result.push(a);
}
console.log(result);