Convert csv to json where values from different columns are placed in arrays (using javascript)

Question

Let's suppose I have a CSV file like this:

name, region, year, population, income
"USA", "America", 2000, 111, 444
"USA", "America", 2001, 222, 555
"USA", "America", 2002, 333, 666
"Germany", "Europe", 2000, 777, 888

I was wondering if it's possible to convert it to a JSON document in this format:

[{"name": "USA", 
"region": "America",
"population": [
    [2000, 111],
    [2001, 222],
    [2002, 333]
 ],
"income": [
     [2000, 444],
     [2001, 555],
     [2002, 666]
]
},
{"name": "Germany", 
"region": "Europe",
"population": [
    [2000, 777]
 ],
"income": [
     [2000, 888]
]
}]

At first I used the code posted here to convert the CSV to a simple JSON document:

//var csv is the CSV file with headers
function csvJSON(csv){
    var lines=csv.split("\n");
    var result = [];
    var headers=lines[0].split(",");

    for(var i=1;i<lines.length;i++){
        var obj = {};
        var currentline=lines[i].split(",");

        for(var j=0;j<headers.length;j++){
            obj[headers[j]] = currentline[j];
        }
        result.push(obj);
    }

    //return result; //JavaScript object
    return JSON.stringify(result); //JSON
};

var textCSV = "name,region,year,population,income\n" +
"USA,America,2000,111,444\n" +
"USA,America,2001,222,555\n" +
"USA,America,2002,333,666\n" +
"Germany,Europe,2000,777,888";

var runFunc = csvJSON(textCSV);
console.log(runFunc);

And got the following JSON as result:

[{"name":"USA","region":"America","year":"2000","population":"111","income":"444"},{"name":"USA","region":"America","year":"2001","population":"222","income":"555"},{"name":"USA","region":"America","year":"2002","population":"333","income":"666"},{"name":"Germany","region":"Europe","year":"2000","population":"777","income":"888"}]

I need some tips on where to go from here to achieve the format specified where I have year + population and then year + income in arrays and everything is in a single {}.

Possible duplicate of [converting CSV/XLS to JSON?](https://stackoverflow.com/questions/662859/converting-csv-xls-to-json) — Taki, Apr 18 '18 at 13:58
@Taki the solutions specified there would generate one {} for each row in my CSV file. I specified a JSON format where I'd like to have only one {} for all the data and would "merge" year + population in arrays, as well as year + income. — Geralt, Apr 18 '18 at 14:02
Yes, it is possible. However, you're going to need more that a straight conversion to get the format you're looking for. What have you tried so far? We're here to help, not do it for you. — Ghostrydr, Apr 18 '18 at 14:05
You are pushing into `result` on each line read. You should push only if `name` and `region` form a new key. — RaphaMex, Apr 18 '18 at 15:14
Will your `name` and `region` always be 'USA' and 'America'? — Ghostrydr, Apr 25 '18 at 15:19
@Ghostrydr No. Each country needs to be in separate {}s, the region is not important. I've updated my question showing the results I expect to get and the one I actually get with different name values. — Geralt, Apr 26 '18 at 14:43

score 0 · Answer 1 · answered Apr 18 '18 at 13:59

//var csv is the CSV file with headers
function csvJSON(csv){

  var lines=csv.split("\n");

  var result = [];

  var headers=lines[0].split(",");

  for(var i=1;i<lines.length;i++){

      var obj = {};
      var currentline=lines[i].split(",");

      for(var j=0;j<headers.length;j++){
          obj[headers[j]] = currentline[j];
      }

      result.push(obj);

  }

  //return result; //JavaScript object
  return JSON.stringify(result); //JSON
}

ref: csvJson gist

Convert csv to json where values from different columns are placed in arrays (using javascript)

1 Answers1