Transform CSV data into JSON format for proper structuring

Question

Transform CSV data into JSON format for proper structuring

I am currently working with CSV data that includes a column 'characteristic' with three types and a 'value' column containing the numerical values for each characteristic. I am looking to restructure this data so that each characteristic becomes a separate column and its corresponding values fall directly under it.

Here is an example of the current structure: https://i.sstatic.net/3R8eh.png

And this is the desired structure: https://i.sstatic.net/9dVHK.png

I manually changed the structure in the example, but the actual table is thousands of lines long, so I am looking for a way to programmatically achieve this restructuring.

The reason for this restructuring is that I want to convert the CSV data to JSON format, where each row will 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
      }
    ]

Is there a way to automatically transform my current CSV structure (as shown in the first screenshot) into the JSON format I need, without manual intervention? I have searched extensively but have not found a solution. I am open to suggestions, but ideally, I would like to use JavaScript for this task. Thank you.

javascript json csv

Answer 1

Answer №1

Here is a specialized JSFiddle that I created for your convenience, tailored to your specific needs.

JavaScript

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

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

var csvData = "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 csvRows = csvData.split("\n");
var countries = [];

if(csvRows.length > 0){
    var headerRow = csvRows[0];
    var columns = headerRow.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 < csvRows.length; i++) {
        var row = csvRows[i];
        var columns = row.split(",");

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

        var country = findCountryByName(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));

The resulting output provided by the code is as follows:

[{"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

Here is a specialized JSFiddle that I created for your convenience, tailored to your specific needs.

JavaScript

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

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

var csvData = "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 csvRows = csvData.split("\n");
var countries = [];

if(csvRows.length > 0){
    var headerRow = csvRows[0];
    var columns = headerRow.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 < csvRows.length; i++) {
        var row = csvRows[i];
        var columns = row.split(",");

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

        var country = findCountryByName(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));

The resulting output provided by the code is as follows:

[{"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 3

Answer №2

If you're looking to transform your CSV file into a JSON format, I recommend using an internet application. Once you have the JSON data, you can easily manipulate it using Javascript to meet your specific requirements.

Answer 4

If you're looking to transform your CSV file into a JSON format, I recommend using an internet application. Once you have the JSON data, you can easily manipulate it using Javascript to meet your specific requirements.

Transform CSV data into JSON format for proper structuring

Answer №1

Answer №2

Similar questions

Guide on building a JSON hierarchy starting with a single parent node and several offspring nodes

Could you provide some advice for creating a Single Page website?

The Bootstrap datepicker functions properly when used in plain HTML, but encounters issues when implemented within the .html()

Encountering an issue with receiving "undefined" values while utilizing WordPress post metadata in AngularJS

What are the steps to storing numerical inputs as variables using jQuery and then using them to perform calculations in order to update the input numbers

When employing useEffect, clearInterval cannot halt the execution of SetInterval

What is the best way to utilize the history prop in conjunction with other props?

ng-model fails to synchronize with HTML

Converting PPT files to PDF or PNG using Node.js

What steps can be taken to determine the cause of an abrupt closure of a websocket client?

Cancel all asynchronous requests

Using plain JavaScript, adding an element to an array by pushing the value of a string variable in an index position rather than pushing the

Why is the result of this specific JavaScript code a string?

Troubleshooting the Hover Effect of Buttons in Next.js when Using Tailwind CSS for Dynamic Color Changes

What sets apart assigning a React ref using a callback versus setting it directly?

What is the best way to emphasize colors in search results?

Fetching Results from Promise

Guide to rearranging the sequence of items in a selected jQuery collection

Show only the image source (img src) and do not display the audio and video sources using jQuery

Accessing array values depending on DOM response