Managing numerical data in a CSV file using JavaScript and Google Visualization Table

Question

Managing numerical data in a CSV file using JavaScript and Google Visualization Table

A JavaScript snippet provided below will load a CSV file named numericalData.csv, which contains headers in the first row and numerical values starting from the second row. The data is then displayed using a Google Visualization Table.

I am looking to convert these numerical values into either float or integer types because currently they are being treated as strings. This causes issues with sorting functionality when displaying the data (refer to the screenshot below).

   
google.load('visualization', '1', {packages:['table']});
google.setOnLoadCallback(drawTable);

function drawTable() {            
    var csvData = loadFile("numericalData.csv", ",");
    var csvParsedData = CSVToArray(csvData);

    var data = google.visualization.arrayToDataTable(csvParsedData);

    var table = new google.visualization.Table(document.getElementById('table_div'));
    
    table.draw(data, {allowHtml: true, showRowNumber: true});
}

javascript google-visualization

Answer 1

Answer №1

If the array csvParsedData contains strings instead of numbers, this is what you might expect to see. To resolve this issue, ensure that your CSV does not have quoted numbers. If they are not quoted and the CSVtoArray function is not parsing numbers correctly, you can iterate through your data using either parseInt or parseFloat on each value in the csvParsedData array. Below is a simplified example:

// parse all values in csvParsedData as int's
for (var i = 0; i < csvParsedData.length; i++) {
    for (var j = 0; j < csvParsedData[i].length; j++) {
        csvParsedData[i][j] = parseInt(csvParsedData[i][j]);
    }
}

[edit: updated code for handling different data types]

Here is some code that will parse data types appropriately:

// columns is an array of data types corresponding to the data types of each column in the CSV array
var columns = ['string', 'int', 'float', 'int', 'int', 'float'];
// parse all values in csvParsedData as the appropriate data type
for (var i = 0; i < csvParsedData.length; i++) {
    for (var j = 0; j < csvParsedData[i].length; j++) {
        switch columns[j] {
            case 'int':
                csvParsedData[i][j] = parseInt(csvParsedData[i][j]);
                break;
            case 'float':
                csvParsedData[i][j] = parseFloat(csvParsedData[i][j]);
                break;
            case 'string':
                break;
            default:
                // should never fire
        }
    }
}

Add other data types as needed.

Answer 2

If the array csvParsedData contains strings instead of numbers, this is what you might expect to see. To resolve this issue, ensure that your CSV does not have quoted numbers. If they are not quoted and the CSVtoArray function is not parsing numbers correctly, you can iterate through your data using either parseInt or parseFloat on each value in the csvParsedData array. Below is a simplified example:

// parse all values in csvParsedData as int's
for (var i = 0; i < csvParsedData.length; i++) {
    for (var j = 0; j < csvParsedData[i].length; j++) {
        csvParsedData[i][j] = parseInt(csvParsedData[i][j]);
    }
}

[edit: updated code for handling different data types]

Here is some code that will parse data types appropriately:

// columns is an array of data types corresponding to the data types of each column in the CSV array
var columns = ['string', 'int', 'float', 'int', 'int', 'float'];
// parse all values in csvParsedData as the appropriate data type
for (var i = 0; i < csvParsedData.length; i++) {
    for (var j = 0; j < csvParsedData[i].length; j++) {
        switch columns[j] {
            case 'int':
                csvParsedData[i][j] = parseInt(csvParsedData[i][j]);
                break;
            case 'float':
                csvParsedData[i][j] = parseFloat(csvParsedData[i][j]);
                break;
            case 'string':
                break;
            default:
                // should never fire
        }
    }
}

Add other data types as needed.

Answer 3

Answer №2

My array is currently N rows by 5 columns with the asgallant fix already in place. I now have a requirement to include a 6th column containing only strings below it.

The structure should resemble:

Value1 | Value2 | Value3 | Value4 | Value5 | String  |
1.234      3.5     4.2      2.1      2.2      Banana

However, my current code shows:

 Value1 | Value2 | Value3 | Value4 | Value5 |
  1.234    3.5     4.2      2.1      2.2

Here's the existing code snippet:

 var csvParsedData = CSVToArray(csvData);
// convert all values in csvParsedData to integers
for (var i = 0; i < csvParsedData.length; i++) {
    for (var j = 0; j < csvParsedData[i].length; j++) {
        csvParsedData[i][j] = parseFloat(csvParsedData[i][j]);
    }
}

//data = google.visualization.arrayToDataTable(csvParsedData);
var data = new google.visualization.DataTable();
data.addColumn('number','Value1');
data.addColumn('number','Value2');
data.addColumn('number','Value3');
data.addColumn('number','Value4');
data.addColumn('number','Value5');
for (var j = 0; j < csvParsedData.length-1; j++){
    data.addRows([csvParsedData[j]]);
}

Answer 4

My array is currently N rows by 5 columns with the asgallant fix already in place. I now have a requirement to include a 6th column containing only strings below it.

The structure should resemble:

Value1 | Value2 | Value3 | Value4 | Value5 | String  |
1.234      3.5     4.2      2.1      2.2      Banana

However, my current code shows:

 Value1 | Value2 | Value3 | Value4 | Value5 |
  1.234    3.5     4.2      2.1      2.2

Here's the existing code snippet:

 var csvParsedData = CSVToArray(csvData);
// convert all values in csvParsedData to integers
for (var i = 0; i < csvParsedData.length; i++) {
    for (var j = 0; j < csvParsedData[i].length; j++) {
        csvParsedData[i][j] = parseFloat(csvParsedData[i][j]);
    }
}

//data = google.visualization.arrayToDataTable(csvParsedData);
var data = new google.visualization.DataTable();
data.addColumn('number','Value1');
data.addColumn('number','Value2');
data.addColumn('number','Value3');
data.addColumn('number','Value4');
data.addColumn('number','Value5');
for (var j = 0; j < csvParsedData.length-1; j++){
    data.addRows([csvParsedData[j]]);
}

Managing numerical data in a CSV file using JavaScript and Google Visualization Table

Answer №1

Answer №2

Similar questions

Guide to inserting text into an html element upon clicking an ASP:Button

Nextjs introduces an innovative "OnThisDay" functionality, leveraging getServerSideProps and Date.now() to provide real-time information

Issue: The npm module 'moment' cannot be located

Tips for saving an image link when displaying an image in VueJS using the 'require' method

"Adding an Image to Another Image in HTML or JavaScript: A Step-by-Step

Restrict the frequency of requests per minute using Supertest

Transmitting Several Pictures at Once Through WhatsApp-WebJS

Using directive to access service values directly

What is the most secure approach for defining the file path of an HTML file within an express.js application?

Engaging with Electron through an HTML file

rectangle/positionOffset/position().top/any type of element returning a position value of 0 within the container

Would it be a security risk to share the Stripe charge ID and connected account ID with clients on the frontend?

There appears to be an issue with Google Analytics not generating __utm.gif requests, yet no error messages are

Implementing a Javascript solution to eliminate the # from a URL for seamless operation without #

The not-found.js file in Next.js is displaying an empty page rather than showing a 404 error message

What is the best way to transfer a JavaScript variable through a query string from one HTML page to another?

Ajax sending numerous requests to the API

Having difficulty pinpointing and deleting an added element using jQuery or JavaScript

Tips for integrating Chart.js into my AngularJS application?

Ember.js encountering an "Access-Control-Allow-Origin" error with Ajax