Converting data structure of a JSON array for stacked bar chart

Question

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

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.

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

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"
}]

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"
}]

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

rioV8 · Accepted Answer · 2018-07-30T20:25:16.583

1

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

edited Jul 30 '18 at 20:25

answered Jul 30 '18 at 20:10

rioV8

24,506
3
32
49

Had the mouse over the submit button, but you beat me to it, nice answer. – Andrew Reid Jul 30 '18 at 20:14
Hmm... So, I could add this onto my d3.json url then? – LSRain Jul 30 '18 at 20:15
Yes, I have added a small running example. – rioV8 Jul 30 '18 at 20:26
I see. Thank you for your help. I didn't know that you could use a dictionary to do that. I will use this method and test to see if it works on creating the bars for my stacked bar chart. – LSRain Jul 30 '18 at 20:27
In running the provided code, it works. It seems like that I have to change my columns and keys variables especially to splice the correct column data. – LSRain Jul 30 '18 at 20:53
I just realized that in looking at the converted data structure that the amount of genders such as female, male, and unknown belongings to the specific category is lost due to the aggregation. How could I distinguish that the certain amount of genders belong to a specific category? – LSRain Jul 31 '18 at 22:03
change the following line: `hospital[element.category] = element;`. If not the solution specify in more detail what you want. – rioV8 Jul 31 '18 at 23:23
If you don't mind, I have another question that I want to ask. How would I be able to access certain properties of my data? Would I use "hospital" or "element"? I'm sorry that I'm asking as I am relatively new to D3. – LSRain Aug 01 '18 at 21:03
It is not a D3 question but a JavaScript question. There are a lot of good books and websites where you can learn all about the JavaScript datastructures. – rioV8 Aug 01 '18 at 21:42
I see. So, how I can fetch my data properties? – LSRain Aug 01 '18 at 22:22
Okay. This is my last question to you, @rioV8 I changed the category into like this: "hospital.category += d.category + "," " In checking the output on the console, the list of categories are listed but it has an "undefined" value in the beginning. I used String() and JSON. stringify but none of the worked. How could I convert the object's property intro string? – LSRain Aug 02 '18 at 18:27
1

hospital.category = hospital.category ? (hospital.category + "," + d.category) : d.category; – rioV8 Aug 02 '18 at 20:03
Thank you again @rioV8 This actually resolves my problem in trying to access each category (for a different question). The issue that I had before was that it created separate categories which I realized it would be difficult to get the values especially for displaying them on a tooltip. – LSRain Aug 02 '18 at 20:13

Converting data structure of a JSON array for stacked bar chart

1 Answers1

Linked