转换JSON数组的数据结构以堆积条形图 [英] Converting data structure of a JSON array for stacked bar chart

问题描述

此问题与我之前问过的问题有关:在JSON中使用d3v4堆叠的条形图.

This question is related to a previous question that I asked: Using JSON in d3v4 stacked bar chart.

背景:对于堆积的条形图，我试图代表一系列医院，这些医院显示每位患者的诊断所属的类别(每家医院)的总数.我通过使用SQL查询生成了JSON文件并将其导出.

Background: For the stacked bar chart, I am trying to represent a series of hospitals that show the total count of categories (for each hospital) that each patient's diagnosis belongs to. I produced my JSON file by using a SQL query and exported it.

问题:如何更改JSON数组数据结构以合并同一家医院的多个类别?

Question: How can I change my JSON array data structure to combine multiple categories of the same hospital?

我的堆叠条形图的JSON数组数据结构(一小部分)如下所示:

My JSON array data structure (a small portion) for the stacked bar chart looks like this:

[{
  "hospitalName": "hospital1",
  "category": "Injury & Poisoning",        
  "Females": "0",
  "Males": "4",
  "Unknown": "0",
  "count": "4"
},
{
  "hospitalName": "hospital1",
  "category": "Symptoms, Signs, & Ill-Defined Conditions",
  "Females": "1",
  "Males": "1",
  "Unknown": "0",
  "count": "2"
},
{
   "hospitalName": "hospital2",
   "category": "Mental Disorders",
    "Females": "0",
    "Males": "1",
    "Unknown": "0",
    "count": "1"
}]

所需的JSON数组数据结构:

The desired JSON array data structure:

[{
  "hospitalName": "hospital1",
  "Injury & Poisoning": "4",
  "Symptoms, Signs, & Ill-Defined Conditions": "2"        
  "Females": "1",  <--- the count of females is increased
  "Males": "5",    <--- the count of males is increased 
  "Unknown": "0",
  "count": "6"
},
{
   "hospitalName": "hospital2",
   "category": "Mental Disorders",
    "Females": "0",
    "Males": "1",
    "Unknown": "0",
    "count": "1"
}]

这可能是使用SQL查询生成的还是我可以使用PLSQL代替的?

Is this possible to produce using a SQL query or could I use PLSQL instead?

推荐答案

只需创建一个新字典，其关键字等于医院名称. 然后从字典中检索所有元素.

Just create a new dictionary with the key equal to the hospital name. And afterwards retrieve all the elements from the dictionary.

如果需要将计数作为字符串转换，就在将它们添加回数据列表之前进行转换.

If you need to have the counts as strings convert them just before adding them back to the data list.

var newdata = {};

data.forEach(element => {
    var name = element.hospitalName;
    var hospital = newdata[name];
    if (!hospital) {
        hospital = { hospitalName: name, Females: 0, Males: 0, Unknown: 0, count: 0};
        newdata[name] = hospital;
    }
    hospital[element.category] = +element.count;
    hospital.Females += +element.Females;
    hospital.Males   += +element.Males;
    hospital.Unknown += +element.Unknown;
    hospital.count   += +element.count;
});

data = [];

for (const key in newdata) {
    if (newdata.hasOwnProperty(key)) {
        data.push(newdata[key]);
    }
}

作为一个小型运行示例，其中包含您的巨大数据集.

As a small running example with your huge data set.

var data = [{
  "hospitalName": "hospital1",
  "category": "Injury & Poisoning",        
  "Females": "0",
  "Males": "4",
  "Unknown": "0",
  "count": "4"
},
{
  "hospitalName": "hospital1",
  "category": "Symptoms, Signs, & Ill-Defined Conditions",
  "Females": "1",
  "Males": "1",
  "Unknown": "0",
  "count": "2"
},
{
   "hospitalName": "hospital2",
   "category": "Mental Disorders",
    "Females": "0",
    "Males": "1",
    "Unknown": "0",
    "count": "1"
}];

var newdata = {};

data.forEach(element => {
    var name = element.hospitalName;
    var hospital = newdata[name];
    if (!hospital) {
        hospital = { hospitalName: name, Females: 0, Males: 0, Unknown: 0, count: 0};
        newdata[name] = hospital;
    }
    hospital[element.category] = +element.count;
    hospital.Females += +element.Females;
    hospital.Males   += +element.Males;
    hospital.Unknown += +element.Unknown;
    hospital.count   += +element.count;
});

data = [];

for (const key in newdata) {
    if (newdata.hasOwnProperty(key)) {
        data.push(newdata[key]);
    }
}

console.log(data);

这篇关于转换JSON数组的数据结构以堆积条形图的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！