How to split and create single objects in json?

Question

The following

$.ajax({
    url: "https://raw.githubusercontent.com/CSSEGISandData/COVID-19/master/csse_covid_19_data/csse_covid_19_time_series/time_series_19-covid-Confirmed.csv",
    success: function(csv) {
        const output = Papa.parse(csv, {
          header: true, // Convert rows to Objects using headers as properties
        });
        if (output.data) {
          console.log(output.data);
        } else {
          console.log(output.errors);
        }
    },
    error: function(jqXHR, textStatus, errorThrow){
        console.log(textStatus);
    }
});

<script src="https://cdnjs.cloudflare.com/ajax/libs/jquery/3.3.1/jquery.min.js"></script>
<script src="https://cdnjs.cloudflare.com/ajax/libs/PapaParse/5.1.0/papaparse.min.js"></script>

Gives

[
  {
    "Province/State": "Anhui",
    "Country/Region": "Mainland China",
    "Lat": "31.8257",
    "Long": "117.2264",
    "1/22/20": "1",
    "1/23/20": "9",
    "1/24/20": "15",
    "1/25/20": "39",
    "1/26/20": "60",
    "1/27/20": "70",
    "1/28/20": "106",
    "1/29/20": "152",
    "1/30/20": "200",
    "1/31/20": "237",
    "2/1/20": "297",
    "2/2/20": "340",
    "2/3/20": "408",
    "2/4/20": "480",
    "2/5/20": "530"
  },
  {
    "Province/State": "Beijing",
    "Country/Region": "Mainland China",
    "Lat": "40.1824",
    "Long": "116.4142",
    "1/22/20": "14",
    "1/23/20": "22",
    "1/24/20": "36",
    "1/25/20": "41",
    "1/26/20": "68",
    "1/27/20": "80",
    "1/28/20": "91",

But the dates are single objects that I need and the number next to it too, so I'd need something like

{
  "Province/State": "Beijing",
  "Country/Region": "Mainland China",
   "Lat": "40.1824",
   "Long": "116.4142",
   "cases": [
    {
      "date": "1/28/20",
       "people": "91",
      ],
      "date": "1/29/20",
       "people": "99",
      ],
      "date": "1/30/20",
       "people": "101",
      ],
    },

Literally I'm looking for a properly formatted json with single objects

@JeffVdovjak I need every date and every number next to it as single objects, like a well formatted json to read — rob.m, Mar 08 '20 at 00:57
@rob.m can you post an example of the desired output with 2 or more dates? It's not clear what you really want — blex, Mar 08 '20 at 00:59
According to the csv, the date is not a "single object". There are many dates, hence you're receiving a large object with each date column's value. You would need another function/step to transform the JSON objects to gather all the date keys and make an array of `infectionStats: [{ date: "1/22/20", count: 22 }. date: "1/23/20", count: 25 }]` or remove all the dates except the most recent one (using regex to collect all the dates for a given province's JSON and then only output the most recent date, in case you have missing data and can't simply hard code it to the last date of "3/7/20"). — mirage, Mar 08 '20 at 01:00
Your most recent edit: `"date": {["1/22/20", "people": "22]"}, "date": {["1/23/20", "people": "45]"},` is not valid JSON. A key ("date") cannot be repeated, it would have to be an array type: `dates: [{date: "mm/dd/yyyy", count: N}]` — mirage, Mar 08 '20 at 01:01
@blex updated the example in the question, maybe I didn't write the json formatting well but that should give you an idea — rob.m, Mar 08 '20 at 01:01
@mirage exactly, look at my updated example in the question, maybe I didn't write the json formatting well but that should give you an idea — rob.m, Mar 08 '20 at 01:02
Can you show your desired output structure in valid Javascript syntax? (`{["1/22/20", "people": "22]"},` is not valid, nor are repeated keys) — CertainPerformance, Mar 08 '20 at 01:05
@CertainPerformance updated the example, maybe that's a nice way to format it? — rob.m, Mar 08 '20 at 01:08
`[ people: "121" ],` isn't valid syntax either... did you mean for that to be an object? — CertainPerformance, Mar 08 '20 at 01:10
@CertainPerformance how about now? And do that for each city — rob.m, Mar 08 '20 at 01:12

blex · Accepted Answer · 2020-03-08T02:07:13.723

4

You cannot have multiple properties with the same name like this:

{
  "date": {["1/22/20", "people": "22]"},
  "date": {["1/23/20", "people": "45]"}
}

Plus, ["people": "45"] is not valid JSON. Only the last one declared would exist in the end. But you could do this:

{
   "Province/State": "Beijing",
   "Country/Region": "Mainland China",
   "Lat": "40.1824",
   "Long": "116.4142",
   "dataset":[
     {"date": "1/22/20", "people": 22},
     {"date": "1/23/20", "people": 45}
   ]
}

$.ajax({
    url: "https://raw.githubusercontent.com/CSSEGISandData/COVID-19/master/csse_covid_19_data/csse_covid_19_time_series/time_series_19-covid-Confirmed.csv",
    success: function(csv) {
        const output = Papa.parse(csv, {
          header: true, // Convert rows to Objects using headers as properties
          dynamicTyping: true, // Convert some fields to Numbers automatically
        });
        if (output.data) {
          const formatted = output.data.map(area => {
            const obj = { dataset: [] };

            Object.keys(area).forEach(key => {
              if (/^\d+\/\d+\/\d+$/.test(key)) {
                obj.dataset.push({ date: key, people: area[key] });
              } else {
                obj[key] = area[key];
              }
            });

            return obj;
          });
          
          document.body.innerHTML = `<pre>${JSON.stringify(formatted, 0, 2)}</pre>`;
        } else {
          console.log(output.errors);
        }
    },
    error: function(jqXHR, textStatus, errorThrow){
        console.log(textStatus);
    }
});

<script src="https://cdnjs.cloudflare.com/ajax/libs/jquery/3.3.1/jquery.min.js"></script>
<script src="https://cdnjs.cloudflare.com/ajax/libs/PapaParse/5.1.0/papaparse.min.js"></script>

edited Mar 08 '20 at 02:07

answered Mar 08 '20 at 01:13

blex

24,941
5
39
72

thank you! Yes indeed the json example i have updated reflects what you did indeed. The snipped isn't running tho – rob.m Mar 08 '20 at 01:14
yup works indeed https://jsfiddle.net/0xn1c9d5/1/ – rob.m Mar 08 '20 at 01:21
1

@rob.m It was not working because StackOverflow's console emulator was really slow to display this large Array, apparently. I replaced the `console.log` to make it work – blex Mar 08 '20 at 01:34
1

Rather than rely on knowing all the non-date keys, you could use `isNaN( Date.parse(key) )` in an if statement to treat date keys differently than non-date keys. Then if they added something like "Quarantine Status" : true/false to the data, you would not miss it. – Jeff Vdovjak Mar 08 '20 at 02:00
@JeffVdovjak You're right! – blex Mar 08 '20 at 02:07
@blex your piece now doesn't include the first part with location and longitude etc tho – rob.m Mar 08 '20 at 02:10
@rob.m It does, scroll down a bit ;) – blex Mar 08 '20 at 02:10
@blex oh isee, lol thanks a lot – rob.m Mar 08 '20 at 02:11
@blex I'm wondering if we should do something regardless of the naming which would read all keys and create the objects – rob.m Mar 08 '20 at 02:12
@rob.m I edited my answer to do that, after Jeff's comment. Maybe refresh this page to see the changes? Or this is not what you mean? – blex Mar 08 '20 at 02:13
@blex oh ok, brilliant – rob.m Mar 08 '20 at 02:13
@blex but actually the example Jeff did i don't think it'll be happening, because each case (death, confirmed, recovered and eventual quarantine) they're splitting them into different cvs https://github.com/CSSEGISandData/COVID-19/tree/master/csse_covid_19_data/csse_covid_19_time_series – rob.m Mar 08 '20 at 02:15
@rob.m So, you want to combine these three CSVs into a single Array? – blex Mar 08 '20 at 02:18
Let us [continue this discussion in chat](https://chat.stackoverflow.com/rooms/209235/discussion-between-rob-m-and-blex). – rob.m Mar 08 '20 at 02:18

CertainPerformance · Answer 2 · 2020-03-08T01:14:38.367

You want to separate out the comma-separated lines into

(1) labels for the object (items 0-4)

(2) number of people (items 5+)

When iterating over a line, slice the label values off first, then create a "prototype" object for the labels. Then iterate over the people and push an object to the output for each. To get the day label to use, take the index of the "people" being iterated over, and look it that index (plus 4) on the array of labels:

jQuery.ajax({
  url: "https://raw.githubusercontent.com/CSSEGISandData/COVID-19/master/csse_covid_19_data/csse_covid_19_time_series/time_series_19-covid-Confirmed.csv",
  type: 'get',
  dataType: 'text',
  success: function(data) {
    const lines = data.split('\n');
    const labelKeys = lines.shift().split(',');
    const output = [];
    for (const line of lines) {
      const cases = [];
      const items = line.split(',');
      const labelValues = items.slice(0, 4);
      const peopleArr = items.slice(4);
      const doc = {};
      for (let i = 0; i < 4; i++) {
        doc[labelKeys[i]] = labelValues[i];
      }
      peopleArr.forEach((people, i) => {
        const date = labelKeys[i + 4];
        cases.push({ date, people });
      });
      output.push({ ...doc, cases });
    }
    console.log(output.slice(0, 4));
  }
});

<script src="https://cdnjs.cloudflare.com/ajax/libs/jquery/3.3.1/jquery.min.js"></script>

No split (see [previous question asked a couple of minutes ago](https://stackoverflow.com/a/60583614/1913729)) — blex, Mar 08 '20 at 01:03

score 2 · Answer 3 · answered Mar 08 '20 at 01:57

If you're not sure if the data format will include key changes then you should not rely on knowing all of the keys (ie. maybe they add "quarantined: true" to the JSON response). Instead you can check to see if a key is a date value or not using isNaN( Date.parse(key) ).

The following code won't "miss" added key:value pairs.

$.ajax({
    url: "https://raw.githubusercontent.com/CSSEGISandData/COVID-19/master/csse_covid_19_data/csse_covid_19_time_series/time_series_19-covid-Confirmed.csv",
    success: function(csv) {
        const output = Papa.parse(csv, {
          header: true, // Convert rows to Objects using headers as properties
        });
        if (output.data) {
        const covidArray = [];
        output.data.forEach( function(item,index){
          let covid = new Object;
          covid.Cases = [];
          for(let key in item) {
            // Check if the key is a date or not
            if(isNaN(Date.parse(item[key]))){
              covid[key] = item[key];
            } else {
              covid.Cases.push( { "date" : key, "people" : item[key] } );
            }
          }
          covidArray.push(covid);
        });

        // The whole array reformatted
        console.log(covidArray);

        } else {
          console.log(output.errors);
        }
    },
    error: function(jqXHR, textStatus, errorThrow){
        console.log(textStatus);
    }
});

what about printing all keys regardless of their naming? I mean making it general and working regardless of the keys? — rob.m, Mar 08 '20 at 02:07

score 1 · Answer 4 · answered Mar 08 '20 at 01:17

You can postprocess the CSV data by putting anything that looks like a date into a dates array instead:

for (const row of output.data) {
  row.cases = []
  for (const [key, value] of Object.entries(row)) {
    if (key.match(/^\d+\/\d+\/\d+$/)) { // Is of format #/#/#
      delete row[key]
      row.cases.push({ date: key, people: Number(value) })
    }
  }
}

Afterwards, output.data will have the format you want.

EulersIdentity · Answer 5 · 2020-03-08T01:37:45.320

You can re-construct the object and use regex to check if the property name is a date format, and do the manipulation accordingly:

$.ajax({
  url: "https://raw.githubusercontent.com/CSSEGISandData/COVID-19/master/csse_covid_19_data/csse_covid_19_time_series/time_series_19-covid-Confirmed.csv",
  success: function(csv) {
    const output = Papa.parse(csv, {
      header: true, // Convert rows to Objects using headers as properties
    });
    if (output.data) {
      //console.log(output.data);
      output.data = output.data.slice(0, 3); // ONLY DO 3 entries here in the demo because it is a huge array
      let newData = [];
      output.data.forEach(place => {
        const newPlace = {};
        newPlace.cases = [];
        for (p in place) {
          if ((/^\d+\/\d+\/\d+$/).test(p)) {
            newPlace.cases.push({
              date: p, count: place[p]
            });
          } else {
            newPlace[p] = place[p];
          }
        }
        newData.push(newPlace);
      });
      console.log(newData);
    } else {
      console.log(output.errors);
    }
  },
  error: function(jqXHR, textStatus, errorThrow) {
    console.log(textStatus);
  }
});

<script src="https://cdnjs.cloudflare.com/ajax/libs/jquery/3.3.1/jquery.min.js"></script>
<script src="https://cdnjs.cloudflare.com/ajax/libs/PapaParse/5.1.0/papaparse.min.js"></script>

@rob.m actually, if 3, 4 people all trying to work out an answer while you are editing your question, it might be good to at least give an upvote to all the answers that worked. Instead of leaving them 0 for reading you question edit while at the same time to write an answer for 10 minutes — EulersIdentity, Mar 08 '20 at 01:34

How to split and create single objects in json?

5 Answers5

Linked