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

What is preventing me from loading js and css files on my web pages?

Creating a Timeless Banner with the Magic of `background:url()`

Transform every key and value into an array

Unable to establish a new pathway in the index.js file of a Node.js and Express website running on Heroku

Error: Missing provider for MatBottomSheetRef

Using the history.push() method from the Action Creator will update the URL without actually redirecting to a new page

Express callback delaying with setTimeout

Make the textarea larger and bring it to the forefront when it is selected

Create a setup page upon initial login using express.js

Error encountered during file download with AXIOS: TypeError - 'validateStatus' in blob cannot be searched using 'in' operator

Using asynchronous file uploading with jQuery and ASP.NET for a seamless user experience

What is the best way to create a sliding animation on a div that makes it disappear?

Collapse the sidebar using React when clicked

Create static HTML files using an Express server

Elements with v-for directive failing to display

Is it possible to center the image and resize it even when the window is resized?

`CSS Content Placeholder Issue When Used Alongside JavaScript`

Unable to use global modules in NestJS without importing them

Is it possible to animate the innerHTML of a div using CSS?

What causes addEventListener to not return a value?