restructure CSV data to create correct format in JSON

Question

I'm working with some CSV data. Right now the CSV has a column called 'characteristic' which is one of three types, and a column called 'value', which contains the numerical value for the characteristic. I'd like to change the structure of the data so that the columns are the characteristics themselves, and the values fall directly under those columns. Here are screenshots of the tables, for clarity:

Currently:

What I'd like:

I changed things manually to give an example. The actual table I'll need to change is thousands of lines, so I'm hoping I can do this programmatically in some way.

The reason I need to restructure is that I need to transform the CSV to JSON, and the JSON needs to look like this:

    [
      {
        "country":"afghanistan",
        "iso3":"afg",
        "first_indicator":3,
        "second_indicator":5,
        "third_indicator":3
      },
      {
        "country":"united states",
        "iso3":"usa",
        "first_indicator":8,
        "second_indicator":6,
        "third_indicator":7
      },
      {
        "country":"china",
        "iso3":"chn",
        "first_indicator":6,
        "second_indicator":0.7,
        "third_indicator":2
      }
    ]

So - is there any way to take my CSV as it is now (first screenshot), and transform it to the JSON I want, without doing it all manually? I've done a lot of searching, and I think maybe I just don't know what to search for. Ideally I would use javascript for this, but any suggestions welcome. Thank you.

Answer 1

I made a JSFiddle for you, something like this should be what you want.

JavaScript

function Country(name, short){
    this["country"] = name;
    this["iso3"] = short;
}

function getCountryByName(name) {
    for(var i = 0; i < countries.length; i++){
        var country = countries[i];
        if(country["country"] == name){
            return country;   
        }
    }
    return null;
}

var csv = "country,shortname,characteristics,value\nafghanistan,afg,first_characteristic,3\nunited states,usa,first_characteristic,8\nchina,chn,first_characteristic,6\nafghanistan,afg,second_characteristic,5\nunited states,usa,second_characteristic,6\nchina,chn,second_characteristic,0.7\nafghanistan,afg,third_characteristic,3\nunited states,usa,third_characteristic,7\nchina,chn,third_characteristic,2"

var rows = csv.split("\n");
var countries = [];

if(rows.length > 0){
    var header = rows[0];
    var columns = header.split(",");
    var countryIndex = columns.indexOf("country");
    var shortnameIndex = columns.indexOf("shortname");
    var characteristicsIndex = columns.indexOf("characteristics");
    var valueIndex = columns.indexOf("value");

    for(var i = 1; i < rows.length; i++) {
        var row = rows[i];
        var columns = row.split(",");

        var name = columns[countryIndex];
        var short = columns[shortnameIndex];
        var characteristic = columns[characteristicsIndex];
        var value = columns[valueIndex];

        var country = getCountryByName(name);
        if(!country){
            country = new Country(name, short);   
            countries.push(country);
        }

        country[characteristic.replace("characteristic", "indicator")] = +value;
    }
}

console.log(countries);
console.log(JSON.stringify(countries));

Output from the last line is this:

[{"country":"afghanistan","iso3":"afg","first_indicator":"3","second_indicator":"5","third_indicator":"3"},
{"country":"united states","iso3":"usa","first_indicator":"8","second_indicator":"6","third_indicator":"7"},
{"country":"china","iso3":"chn","first_indicator":"6","second_indicator":"0.7","third_indicator":"2"}]

Answer 2

My suggestion is to convert the CSV to JSON first. You can use an online tool. When you have the JSON you can write a Javascript code to modify the JSON in the format you want.